Skip to content
InternetData

IP address lookups in BigQuery: a million in 3 seconds

BigQuery refused a BETWEEN join of 100,000 IP addresses to ranges. A join key at four levels looked up a million in 3.1 seconds.

Published 6 min read

On this page
  1. Four ways, measured
  2. A key BigQuery can hash
  3. With the InternetData Open listing
  4. What it costs
  5. How we measured

We matched random IPv4 addresses to VPN IP's 12,767,039 ranges in BigQuery on October 16, 2026. The join most people write first, ip BETWEEN start AND end, took 11.2 seconds and 5.7 hours of slot time for 10,000 addresses, and BigQuery refused it at 100,000. Our table function, which joins on a key before it tests the range, looked up a million addresses in 3.1 seconds and billed 367 MB, about $0.002.

In 2013 someone asked on Stack Overflow how to make a BigQuery GeoIP query faster. A join could only test = then, so their query paired every address with every range on a constant and filtered afterwards. A join takes BETWEEN now, and BigQuery still runs it that way.

Four ways, measured

LookupAddressesWall timeBilledSlot time
The range join below10,00011.2 s154 MB20,382 s
The same range join100,000refused at 180.9 snothing258,120 s
lookup_vpn_ip_v1, the table function1,000,0003.1 s367 MB34 s
vpn_ip_v1(ip), the scalar function1,000,0006.8 s932 MB90 s

Here's the range join as it ran at 10,000 addresses, in our own project with VPN IP's dataset as the default. ips draws random IPv4 addresses with a first octet from 1 to 222, and the LENGTH test keeps them off IPv6 ranges, whose bytes compare with an IPv4 address's too:

sql
WITH ips AS (
  SELECT FORMAT('%d.%d.%d.%d',
      CAST(1 + FLOOR(RAND() * 222) AS INT64),
      CAST(FLOOR(RAND() * 256) AS INT64),
      CAST(FLOOR(RAND() * 256) AS INT64),
      CAST(FLOOR(RAND() * 256) AS INT64)) AS ip
  FROM UNNEST(GENERATE_ARRAY(1, 10000))
)
SELECT COUNT(*) AS matches
FROM ips
JOIN vpn_ip_v1 AS v
  ON NET.SAFE_IP_FROM_STRING(ips.ip) BETWEEN v.start_ip_bytes AND v.end_ip_bytes
  AND LENGTH(v.start_ip_bytes) = 4

It found 44 matches. Its query plan has the join as CROSS EACH WITH EACH and the BETWEEN as a filter after it: each address paired with each of VPN IP's 12,762,048 IPv4 ranges, 127.6 billion pairs. At 100,000 addresses BigQuery stopped it after three minutes:

Query exceeded resource limits. This query used 217193 CPU seconds but would charge only 147M Analysis bytes. This exceeds the ratio supported by the on-demand pricing model. Please consider moving this workload to a capacity-based pricing model, which does not have this limit. 217193 CPU seconds were used, and this query must use less than 37600 CPU seconds.

The limit counts CPU seconds, a different measure from the slot time in the table. On-demand pricing bills the bytes a query reads, and caps the CPU it may spend per byte. A range join reads two columns, 154 MB here, and spends its CPU comparing them. The refused query billed nothing.

A key BigQuery can hash

A join on = runs as a hash join, which pairs an address only with the rows that share its key. Give each range a key and each address the same one, and BETWEEN only has to sort out the rows under it. Google Cloud's 2019 post masks each address at every prefix length from /9 to /32 and joins on the network, which fits a table where every row is one CIDR network. IPinfo's 2023 guide truncates each address to its /16 for IPv4 and its /34 for IPv6, then filters with BETWEEN.

With a single key length, a range wider than the key is stored once for every key it spans: Bogon IP's 16 IPv4 ranges become 9,050 rows keyed at /16 and 2,315,270 at /24, and its 64 IPv6 ranges 3,816,895,859 rows at /32. Ranges narrower than the key share one. On October 16, every VPN IP range in IPv4 fit inside a /16, but a /16 key held 361 of them on average and 45,138 in the fullest, and an address there was tested against all 45,138.

Our tables file each range once, in a join_key column: the network of the finest of /24, /16, /8 and /0 that holds the whole range, or of /64, /48, /32, /16 and /0 for IPv6. A lookup tries an address's network at each level, four keys for IPv4 and five for IPv6. VPN IP's keys held 5.6 ranges on average and 249 in the fullest on October 16, and Bogon IP stays at 80 rows.

Diagram of a lookup of 10.1.2.3 in Bogon IP as built on October 16, 2026: of its four keys, 10.1.2.0/24 and 10.1.0.0/16 have no ranges filed, 10.0.0.0/8 holds the private-use range 10.0.0.0 to 10.255.255.255, which contains the address, and 0.0.0.0/0 holds multicast and reserved space, which don't.

To key a range table of your own, take the finest level at which both ends share a network. This recomputes the keys of Bogon IP, one of the free tables below, from its ranges, and matches ours on all 80 rows:

sql
SELECT start_ip, end_ip, join_key,
  FORMAT('%s/%d', NET.IP_TO_STRING(NET.IP_TRUNC(s, lvl)), lvl) AS my_key
FROM (
  SELECT *, (
      SELECT MAX(l)
      FROM UNNEST(IF(LENGTH(s) = 4, [24, 16, 8, 0], [64, 48, 32, 16, 0])) AS l
      WHERE NET.IP_TRUNC(s, l) = NET.IP_TRUNC(e, l)) AS lvl
  FROM (
    SELECT *, start_ip_bytes AS s, end_ip_bytes AS e
    FROM internetdata_open.bogon_ip_v1
  )
)

A lookup then joins each address's candidate keys and keeps the ranges that hold it:

sql
SELECT a.ip, r.start_ip, r.end_ip, r.name
FROM (
  SELECT ip, NET.SAFE_IP_FROM_STRING(ip) AS b
  FROM UNNEST(['10.1.2.3', '8.8.8.8', '::1']) AS ip
) AS a
CROSS JOIN UNNEST(IF(LENGTH(a.b) = 4, [24, 16, 8, 0], [64, 48, 32, 16, 0])) AS l
JOIN internetdata_open.bogon_ip_v1 AS r
  ON r.join_key = FORMAT('%s/%d', NET.IP_TO_STRING(NET.IP_TRUNC(a.b, l)), l)
  AND a.b BETWEEN r.start_ip_bytes AND r.end_ip_bytes

It returns the private-use range for 10.1.2.3, nothing for 8.8.8.8, and three nested ranges for ::1. Its plan joins on the key with an INNER HASH JOIN, as the table function's does.

With the InternetData Open listing

Our BigQuery listings come keyed this way, with a lookup function for each table keyed by address. InternetData Open is free under CC BY-SA 4.0 and holds eleven databases: ASN, IP ASN, IP Country, IP Timezone, IP Country Flag, IP Country Currency, Anycast IP, Bogon IP, Bogon ASN, Tor IP and Relay IP, each described in Free IP databases. In the BigQuery console, open Sharing, search for InternetData and subscribe to InternetData Open, or to InternetData Open EU for data in the EU, since BigQuery doesn't join across regions. Name the dataset it adds internetdata_open and this post's queries on the Open tables run as printed. The listing shows us the email address of each account that queries it.

Each table keyed by address has a function of its name, which returns the row covering an address as a STRUCT, the narrowest where several do:

sql
SELECT ip,
    internetdata_open.tor_ip_v1(ip).confidence AS tor,
    internetdata_open.bogon_ip_v1(ip).name AS bogon
FROM UNNEST(['185.220.101.1', '10.1.2.3', '::ffff:10.1.2.3', '::1',
    'not an address']) AS ip
iptorbogon
185.220.101.1high
10.1.2.3Private-use networks
::ffff:10.1.2.3Private-use networks
::1Node-scope unicast loopback address
not an address

A mapped address is looked up as the IPv4 it carries, and a value that isn't an address is a miss. For every covering row, the table function with a lookup_ prefix takes a table with an ip column and returns a row per match:

sql
SELECT ip, name
FROM internetdata_open.lookup_bogon_ip_v1((
    SELECT ip FROM UNNEST(['::1', '::ffff:10.1.2.3']) AS ip))
ipname
::1IPv4-compatible addresses
::1Node-scope unicast loopback address
::1Unallocated: outside global unicast (2000::/3)
::ffff:10.1.2.3Private-use networks

On a table of your own, select its addresses in place of the list, as the BigQuery docs page does with mydataset.logins. Each column is described on its database's docs page: Tor IP's confidence, Bogon IP's name.

What it costs

Your queries run and bill in your own project, and the tables cost you nothing to store. Each is rebuilt the day its database changes, daily for most, and a subscription always reads the current one, so it never ages the way a downloaded copy does (what an old copy misses).

A function reads every row of its table, however few addresses you pass it. The scalar reads every column: VPN IP's billed 932 MB for a million addresses, and a dry run of one address estimated the same that day. The table function reads the key, the range and the columns you select: 367 MB billed to count matches, and 377 MB estimated with provider. At $6.25 per TiB, a million lookups cost $0.002 through the table function and $0.005 through the scalar, and BigQuery's first TiB each month is free. Tor IP and Bogon IP are small enough that a query bills BigQuery's 10 MB minimum for each.

Most of our other databases are listings of their own, under the license you hold for each (the list). Talk to us to add one in BigQuery.

How we measured

Everything ran on October 16, 2026, in BigQuery's US multi-region at on-demand rates, against VPN IP as built that day: 12,767,039 ranges, 12,762,048 of them IPv4, in a 932 MB table (its columns). We ran in the project our tables are published from, with VPN IP's dataset as the default, so vpn_ip_v1 needs no prefix; in your project it sits in the dataset you named when you subscribed. The four queries on the Open tables ran again on October 18 from a subscription in another project, as an account with no access to the project our tables are published from, and returned the rows printed here. Each row of the first table is one run of one query, none served from cache. The function rows put a million addresses from the same ips into lookup_vpn_ip_v1, counting the 4,318 rows it returned, and into vpn_ip_v1(ip), counting the 4,239 addresses it didn't return NULL for, each run drawing its own addresses. Wall time is the job's start to its end, and slot time the job's total. A key's size counts the ranges filed under it: under a single /16 key, a range in every /16 it spans.

To license VPN IP or another database, see pricing or talk to us.

Written by

  • Uman Shahzad

    Founder & CEO

    Uman has worked on IP data for over eight years, including as lead engineer at IPinfo. He built systems and practices that IP data companies now use to run at scale and keep their data accurate.

Get new posts by email

Or follow along in a feed reader.

RSS feed

Put this data to work.

Everything these posts report on is in the API. Look up any address, AS or domain free with no key, or get every field on a paid plan.

00 · Unlimited

Unlimited lookups, no sign-up.

Paste it into a terminal. Any address answers with its country, network, company and hostname, with no key and no daily or monthly cap. ASes and domains answer too.

curl https://api.internetdata.io/8.8.8.8
{
"ip": "8.8.8.8",
"is_bogon": false,
"is_tor": false,
"is_relay": false,
"is_anycast": true,
"is_carrier": false,
"is_satellite": false,
"is_ssh": false,
"is_smtp": false,
"is_web": true,
"is_dns_resolver": true,
"is_bittorrent": false,
"hostname": "dns.google",
"location": {
"continent": "NA",
"continent_name": "North America",
"country": "US",
"country_name": "United States",
"timezone": "America/Los_Angeles",
"currency_code": "USD",
"currency_name": "Dollar",
"currency_symbol": "$",
"flag_emoji": "🇺🇸",
"flag_unicode": "U+1F1FA U+1F1F8"
},
"asn": {
"asn": 15169,
"name": "Google LLC",
"domain": "google.com",
"type": "hosting",
"prefix": "8.8.8.0/24"
},
"company": {
"name": "Google LLC",
"domain": "google.com"
},
"tor": {},
"relay": {},
"abuse": {
"email": "network-abuse@google.com"
}
}
01 · Every field

All of the data on a paid plan.

VPN, proxy, hosting and CDN flags, each naming the operator.
Location to the city, with coordinates and an accuracy radius.
WHOIS records, the whole abuse contact and the services an address runs.
Up to 1,000 addresses, ASes and domains in one request.

A free API key answers every field for its first 100 lookups each day, with no card.