BigQuery

We publish our databases to Google BigQuery through BigQuery sharing. Each one is a listing of its own: 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. Every listing shows us the email address of each account that queries it.

The listings

Each is in the US multi-region, and with EU after its name in the EU one, since BigQuery doesn't join across regions. They come under the license you hold for each database: contact us to add one.

Looking up an address

Name each dataset after its listing when you subscribe, vpndetection_vpn_ip for VPN IP. Each table keyed by address has a function of the same name, which returns the row covering an address as a STRUCT, or NULL when there's none. Enriching a table of your own is one more column, and the functions of every database you hold combine in one query:

SELECT l.*,
    vpndetection_vpn_ip.vpn_ip_v1(l.ip).provider AS vpn,
    vpndetection_tor_ip.tor_ip_v1(l.ip).confidence AS tor
FROM mydataset.logins AS l

Where several rows cover an address, the function 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, provider, confidence
FROM vpndetection_vpn_ip.lookup_vpn_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 VPN IP take a few seconds.

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 range adds the columns its functions use: start_ip_bytes, end_ip_bytes and join_key. A provider catalog is a plain table beside it.

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

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. A subscription always reads the current table, so there's nothing to refresh on your side.