I have a table in BigQuery containing my customers' IP addresses, and I want to understand their geographical distribution.
Is there any way I can retrieve geolocation information for these IP addresses, like city, state, and country using SQL?
CodePudding user response:
There was a post about doing it efficiently using Geolite2 in GCP blog:
The query used Felipe fh-bigquery
dataset, that you'll need to recreate using Geolite2 source.
CodePudding user response:
To retrieve geolocation information for an IP Address or identify its physical location you need to use a Geolocation database.
There is a service called MaxMind that provides a free/lite version of the Geolocation database, that you can use.
You can then write a function in Python to use this database and retrieve the geolocation information like city state and country for the specified IP.
In a recent release, BigQuery announced the support of Remote Functions that can be used to wrap Python functions into a SQL function that can be used in your queries.
I wrote a step-by-step tutorial on how to achieve that.