BigQuery

We publish our databases to Google BigQuery through BigQuery sharing. Subscribing adds a read-only dataset to a project of yours, rebuilt from the same build as the downloads, and you query it like your own tables.

The Open listing

InternetData Open holds our Open databases, free under CC BY-SA 4.0:

In the BigQuery console, open Sharing, search for InternetData and pick the listing in the region your data lives in: InternetData Open for the US multi-region, InternetData Open EU for the EU one, since BigQuery doesn't join across regions. Click Subscribe and name the dataset it adds. The examples below call it internetdata_open.

Credit InternetData with a link to internetdata.io wherever you use the data, and share what you build from it under the same license. The listing shows us the email address of each account that queries it.

Looking up an address

Each table keyed by address has a function of the same name. Pass it an address and it returns the row covering it as a STRUCT, or NULL when there's none:

SELECT internetdata_open.tor_ip_v1('185.220.101.1').confidence

Enriching a table of your own is one more column:

SELECT l.*, internetdata_open.tor_ip_v1(l.ip).confidence AS tor
FROM mydataset.logins AS l

Where several rows cover an address, as nested ranges do in anycast_ip_v1 and bogon_ip_v1, it returns the narrowest. For every one of them, call the table function named after the table with a lookup_ prefix. It takes a table with an ip column and returns a row per match:

SELECT ip, name
FROM internetdata_open.lookup_bogon_ip_v1((SELECT ip FROM mydataset.logins))

Both take IPv4 and IPv6, look up an IPv4-mapped address (::ffff:1.2.3.4) as the IPv4 it carries, and treat a value that isn't an address as a miss. A million lookups against a table of 18 million ranges take about 6 seconds and read 1.3 GB.

ip_sample holds several hundred addresses from the Open tables, picked again every day, to try the functions on before you bring your own:

SELECT ip,
    internetdata_open.ip_country_flag_v1(ip).flag_emoji AS flag,
    internetdata_open.tor_ip_v1(ip).confidence AS tor
FROM internetdata_open.ip_sample

Tables

Each database is one table named after its database ID, with the same rows and columns as its CSV download. An empty cell is NULL. A table keyed by address adds the columns its functions use:

A table's description gives its row count, the date of its data and its license, and every column has a description of its own.

For any other database in BigQuery, under the license you hold for it, contact us.

Costs and updates

Your queries run and bill in your own project, at BigQuery's usual rates. The tables cost you nothing to store.

Each table is rebuilt the day its database changes: daily for most, weekly for the rest. A subscription always reads the current table, so there's nothing to refresh on your side.