google-big-queryHow do I use Google BigQuery to analyze IP addresses?

Google BigQuery is a powerful cloud-based analytics tool. It can be used to analyze IP addresses by querying a database of IP address data.

For example, the following code can be used to query the IP address data from the MaxMind GeoLite2 database:

SELECT *
FROM `bigquery-public-data.maxmind_geolite2.ipv4_blocks`
WHERE network LIKE '123.45.67.%'

This query will return data for all IP addresses that match the specified network (in this example, all IP addresses beginning with 123.45.67.).

The output of this query will include the following columns:

network - the IP address range
geoname_id - the GeoName ID of the geographic location associated with this IP address range
registered_country_geoname_id - the GeoName ID of the country associated with this IP address range
represented_country_geoname_id - the GeoName ID of the country associated with this IP address range
is_anonymous_proxy - whether this IP address is associated with an anonymous proxy
is_satellite_provider - whether this IP address is associated with a satellite provider

Using this data, it is possible to analyze the geographic location, country, and other attributes associated with a given IP address.

Helpful links