Vectorized IP Intelligence: Joining 1 Billion Log Rows Against ipscope Parquet

#Why row-by-row .mmdb lookups bottleneck analytical SIEM workloads

In real-time edge proxies, API gateways, and transactional services, nothing beats a memory-mapped .mmdb binary radix tree. Resolving a single incoming client IP in 500 nanoseconds via mmap is ideal when requests arrive one at a time over the network.

However, data engineering and security operations (SecOps) teams face a completely different access pattern when performing retrospective threat hunting, compliance auditing, or CDN traffic analysis:

• You have 1 billion historical firewall, VPC Flow, or Cloudflare access log rows stored in Apache Parquet or Iceberg tables on S3/R2.
• You want to enrich every row with physical_country_code, declared_country_code, asn, is_vpn, is_proxy, and abuse_velocity_score, and aggregate the top attacking ASNs over a 30-day window.

If you register a Python or Java User-Defined Function (UDF) inside Spark, Trino, or Snowflake to call an .mmdb reader row-by-row across 1 billion records, you break the query engine's vectorized execution pipeline. The engine has to materialize scalar values out of columnar SIMD registers, cross a language runtime boundary 1 billion times, and give up all Parquet row-group min/max zone-map pruning.

That is why every ipscope database subscription automatically compiles and delivers three synchronized artifact formats on every release (Daily, Weekly, or Monthly):

1. *ipscope-.mmdb**: Open MMDB v2.0 binary radix tree for real-time edge and application servers.
2. *ipscope-.parquet**: ZSTD-compressed, physical-range-indexed columnar Apache Parquet for vectorized OLAP and SIEM engines (DuckDB, ClickHouse, Apache Spark, Snowflake, BigQuery, Trino, Polars).
3. *ipscope-.csv.gz**: RFC 4180 gzipped CSV for traditional relational COPY pipelines (PostgreSQL, MySQL).

This engineering guide details the physical schema of ipscope-*.parquet, explains how our compiler embeds pre-computed integer boundary columns (ip_start_int, ip_end_int) for O(log N) row-group skipping, and provides copy-pasteable SQL recipes for DuckDB and ClickHouse.

#Physical layout and schema of ipscope-*.parquet

In a nested JSON or .mmdb record, fields are grouped into sub-maps (geo, network, connection, security, security_extensions). For analytical SQL engines, unpacking nested STRUCT columns during a 1-billion-row scan wastes CPU cycles.

Our Parquet compiler flattens all licensed intelligence families into top-level, strongly typed physical columns, physically sorted by (ip_version, network_start_bin) and compressed with ZSTD (level 9) + dictionary encoding:

DESCRIBE SELECT * FROM read_parquet('/var/lib/ipscope/ipscope-complete.parquet');

Why column projection + ZSTD + Run-Length Encoding (RLE) is so fast

In ipscope-complete.parquet, boolean columns (is_vpn, is_hosting, is_mobile, is_virtual_location) use Parquet Run-Length / Bit-Packing Hybrid Encoding, and low-cardinality string columns (country_code, timezone, organization, connection_type, anonymizer_type) use Dictionary Encoding.

Furthermore, when a SIEM query only needs cidr, physical_country_code, asn, is_vpn, and abuse_velocity_score, the Parquet reader only reads the byte pages for those 5 columns from disk and completely skips reading city, latitude, longitude, subdivision, and timezone. Across 35.3 million prefixes, projecting 5 columns scans less than 18 MB of compressed bytes.

#Recipe 1: Instant ad-hoc threat hunting with DuckDB (zero database setup)

DuckDB is an in-process vectorized SQL OLAP engine that reads Parquet files directly from disk or S3/HTTP without loading them into a server first.

Querying global VPN and scanner concentration by ASN and physical country

-- Run directly via: duckdb -c "..."
SELECT
  asn,
  organization,
  physical_country_code AS physical_country,
  declared_country_code AS registered_country,
  COUNT(*) AS total_prefixes,
  SUM(CASE WHEN is_vpn THEN 1 ELSE 0 END) AS vpn_prefixes,
  SUM(CASE WHEN is_proxy THEN 1 ELSE 0 END) AS proxy_prefixes,
  ROUND(AVG(abuse_velocity_score), 1) AS avg_abuse_velocity
FROM read_parquet('/var/lib/ipscope/ipscope-complete.parquet')
WHERE (is_vpn = true OR is_proxy = true OR is_known_scanner = true)
GROUP BY
  asn,
  organization,
  physical_country_code,
  declared_country_code
ORDER BY vpn_prefixes DESC, avg_abuse_velocity DESC
LIMIT 15;

Enriching 50 million access log rows in DuckDB using ipv4_start_u32 range joins

Because the ipscope compiler pre- flattens non-overlapping canonical leaves and stores ipv4_start_u32 and ipv4_end_u32 sorted in strictly ascending order, DuckDB's IEJoin / AsOf Range Join (BETWEEN ipv4_start_u32 AND ipv4_end_u32) resolves millions of log rows in seconds using binary search over Parquet row-group min/max statistics:

-- Convert dotted-quad IPv4 string to uint32 and join against non-overlapping ipscope leaves
WITH parsed_logs AS (
  SELECT
    request_id,
    timestamp,
    client_ip,
    -- Convert '198.51.100.7' -> 3325256711 (uint32)
    (
      (CAST(split_part(client_ip, '.', 1) AS UBIGINT) << 24) |
      (CAST(split_part(client_ip, '.', 2) AS UBIGINT) << 16) |
      (CAST(split_part(client_ip, '.', 3) AS UBIGINT) << 8)  |
       CAST(split_part(client_ip, '.', 4) AS UBIGINT)
    )::UINTEGER AS ip_u32
  FROM read_parquet('s3://my-secops-lake/Logs/2026-10-28/*.parquet')
  WHERE strpos(client_ip, ':') = 0
),
ipscope_v4 AS (
  SELECT
    ipv4_start_u32,
    ipv4_end_u32,
    cidr,
    physical_country_code,
    declared_country_code,
    is_virtual_location,
    asn,
    organization,
    connection_type,
    is_vpn,
    is_proxy,
    is_tor_exit,
    anonymizer_type,
    abuse_velocity_score
  FROM read_parquet('/var/lib/ipscope/ipscope-complete.parquet')
  WHERE ip_version = 4
)
SELECT
  l.request_id,
  l.timestamp,
  l.client_ip,
  i.cidr AS matched_network,
  i.physical_country_code,
  i.declared_country_code,
  i.is_virtual_location,
  i.asn,
  i.organization,
  i.connection_type,
  i.anonymizer_type,
  i.abuse_velocity_score
FROM parsed_logs l
ASOF JOIN ipscope_v4 i
  ON l.ip_u32 >= i.ipv4_start_u32
WHERE l.ip_u32 <= i.ipv4_end_u32;

Look closely at why ASOF JOIN ... ON l.ip_u32 >= i.ipv4_start_u32 WHERE l.ip_u32 <= i.ipv4_end_u32 is a game-changer for analytical SQL:

• Standard inequality joins (ON l.ip_u32 >= i.ipv4_start_u32 AND l.ip_u32 <= i.ipv4_end_u32) often degrade to O(N * M) nested-loop comparisons in SQL engines that lack specialized interval trees.
• Because the ipscope compiler outputs a disjoint, non-overlapping partition of the address space sorted by ipv4_start_u32, finding the prefix containing l.ip_u32 is mathematically equivalent to finding the single row with the largest ipv4_start_u32 <= l.ip_u32 (ASOF JOIN) and checking l.ip_u32 <= i.ipv4_end_u32.
• DuckDB executes ASOF JOIN in O(N log M) time using vectorized merge/binary search!

#Recipe 2: Sub-second enrichment of 1 billion rows in ClickHouse (ip_trie Dictionary)

If your observability or SIEM stack runs on ClickHouse, you can load ipscope-complete.parquet directly into ClickHouse's native in-memory ip_trie dictionary structure.

ClickHouse's ip_trie layout builds a compact bitwise radix tree in RAM for both IPv4 and IPv6 CIDRs, allowing ClickHouse queries to enrich 1 billion log rows at memory-bandwidth speeds (> 40 million lookups per second per node):

-- 1. Create a staging table backed by your downloaded ipscope Parquet file
CREATE OR REPLACE TABLE ipscope_staging
ENGINE = MergeTree
ORDER BY (ip_version, cidr)
AS SELECT *
FROM file('/var/lib/clickhouse/user_files/ipscope-complete.parquet', Parquet);

-- 2. Create the in-memory ip_trie dictionary over both IPv4 and IPv6 CIDRs
CREATE OR REPLACE DICTIONARY ipscope_dict
(
  cidr String,
  country_code String,
  declared_country_code String,
  physical_country_code String,
  is_virtual_location UInt8,
  city String,
  asn UInt32,
  organization String,
  connection_type String,
  is_vpn UInt8,
  is_proxy UInt8,
  is_tor_exit UInt8,
  anonymizer_type String,
  abuse_velocity_score UInt8
)
PRIMARY KEY cidr
SOURCE(CLICKHOUSE(TABLE 'ipscope_staging'))
LAYOUT(IP_TRIE())
LIFETIME(MIN 3600 MAX 7200);

Once ipscope_dict is loaded, enriching any IPv4 or IPv6 column in your ClickHouse http_access_logs table requires a single vectorized dictGet call:

SELECT
  dictGet('ipscope_dict', 'physical_country_code', toIPv6(client_ip)) AS physical_country,
  dictGet('ipscope_dict', 'declared_country_code', toIPv6(client_ip)) AS declared_country,
  dictGet('ipscope_dict', 'asn', toIPv6(client_ip)) AS asn,
  dictGet('ipscope_dict', 'organization', toIPv6(client_ip)) AS org,
  dictGet('ipscope_dict', 'anonymizer_type', toIPv6(client_ip)) AS anonymizer,
  count() AS request_volume,
  countIf(status_code >= 400) AS error_volume
FROM default.http_access_logs
WHERE event_date >= today() - 7
GROUP BY
  physical_country,
  declared_country,
  asn,
  org,
  anonymizer
HAVING anonymizer != 'none'
ORDER BY request_volume DESC
LIMIT 25;

#Recipe 3: Loading ipscope-*.csv.gz into PostgreSQL inet / gist tables

For teams running PostgreSQL 15+ with the native inet and cidr data types, every database subscription also includes ipscope-*.csv.gz. By indexing the cidr column with PostgreSQL's built-in gist (cidr inet_ops) operator class, PostgreSQL can execute subnet containment joins (client_ip <<= i.cidr) directly inside transactional SQL queries:

CREATE TABLE IF NOT EXISTS ipscope_blocks (
  cidr cidr PRIMARY KEY,
  ip_version smallint NOT NULL,
  country_code char(2),
  declared_country_code char(2),
  physical_country_code char(2),
  is_virtual_location boolean NOT NULL,
  asn bigint,
  organization text,
  connection_type text,
  is_vpn boolean NOT NULL,
  is_proxy boolean NOT NULL,
  is_tor_exit boolean NOT NULL,
  anonymizer_type text,
  abuse_velocity_score smallint
);

-- Fast GiST radix index for IP containment (<<= and >>=)
CREATE INDEX IF NOT EXISTS idx_ipscope_blocks_gist
  ON ipscope_blocks USING gist (cidr inet_ops);

#Benchmark: 100 Million Log Rows Enrichment Across Engines

We benchmarked enriching a 100-million-row CDN access log (72% IPv4, 28% IPv6) on an 8-vCPU (32 GB RAM) instance across four execution strategies:

#Automating daily Parquet updates with ETag and SHA-256 verification

You can pull the latest Parquet build from the same /v1/mmdb/releases/latest/download endpoint by passing ?format=parquet:

curl -fsSL \
  -H "Authorization: Bearer ${IPSCOPE_DOWNLOAD_KEY}" \
  -o "/var/lib/ipscope/ipscope-complete.parquet.tmp" \
  "https://api.ipscope.io/v1/mmdb/releases/latest/download?format=parquet"

mv -f "/var/lib/ipscope/ipscope-complete.parquet.tmp" "/var/lib/ipscope/ipscope-complete.parquet"

#Frequently asked questions (FAQ)

Do I have to pay extra to get .parquet and .csv.gz alongside .mmdb?

No. Every ipscope database subscription (IPScope-Country, IPScope-Privacy, IPScope-Complete, and any custom SKU built in the Custom Database Builder) automatically includes all three formats (?format=mmdb, ?format=parquet, and ?format=csv) compiled from the exact same atomic snapshot version.

Are the CIDR rows in ipscope-*.parquet overlapping or non-overlapping?

In our compiled .parquet and .csv.gz artifacts, the compiler flattens the radix tree into a disjoint, non-overlapping set of leaf prefixes. If a /16 supernet has a /24 subnet inside it with different city or security metadata, the /16 is split around the /24 into exact canonical sub-ranges so that any single IP address matches at most one row in the Parquet file. This guarantees that SQL joins never duplicate your log rows!