# MMDB, CSV & Parquet

> How the three database file formats are structured and which one to pick for your workload.

Every database subscription unlocks three synchronized artifacts for each snapshot: `.mmdb` for real-time application lookups, `.csv.gz` for relational bulk loading, and `.parquet` for analytical warehouses.

| Format | Extension | Best for | Typical lookup / query speed |
| --- | --- | --- | --- |
| **MMDB** | `.mmdb` | Application servers, edge workers, NGINX / Envoy | `< 1 µs` via `mmap` |
| **Parquet** | `.parquet` | ClickHouse, DuckDB, Snowflake, BigQuery, Spark | Vectorized columnar scan |
| **CSV** | `.csv.gz` | PostgreSQL `COPY`, MySQL, custom ETL pipelines | Sequential stream |

## MMDB (MaxMind DB binary format v2.0)

A binary radix tree mapping IPv4 and IPv6 prefixes to nested dictionaries that match the [REST API response format](https://ipscope.io/docs/getting-started/response-format) (`geo`, `network`, `connection`, `security`, `security_extensions`). Opens directly in `libmaxminddb` and every standard MaxMind reader without code changes.

## Columnar Parquet & flattened CSV columns

In `.csv.gz` and `.parquet`, nested objects are flattened with snake_case column names and `cidr` as the first column:

```text CSV header (Enterprise Mega)
cidr,ip_version,country_code,declared_country_code,physical_country_code,is_virtual_location,location_type,geo_confidence,subdivision,city,postal_code,latitude,longitude,accuracy_radius_km,timezone,asn,organization,connection_type,is_hosting,is_residential,is_mobile,is_business,is_vpn,is_tor_exit,is_proxy,anonymizer_type,security_confidence,anonymizer_reason,threat_level,abuse_velocity
198.51.100.0/24,4,US,US,US,false,physical,0.94,Virginia,Ashburn,20147,39.0438,-77.4874,10,America/New_York,64501,"Example Cloud LLC",hosting,true,false,false,false,true,false,false,vpn,0.96,vpn_handshake_confirmed,medium,low
```

## Querying Parquet directly with DuckDB or ClickHouse

```sql DuckDB
SELECT country_code, count(*) AS prefixes
FROM read_parquet('ipscope-complete.parquet')
WHERE is_vpn = true
GROUP BY country_code
ORDER BY prefixes DESC
LIMIT 10;
```

```sql PostgreSQL
CREATE TABLE ipscope_blocks (
  network cidr PRIMARY KEY,
  country_code text,
  asn integer,
  organization text,
  is_vpn boolean
);
CREATE INDEX ON ipscope_blocks USING gist (network inet_ops);

SELECT country_code, asn, is_vpn
FROM ipscope_blocks
WHERE network >>= inet '198.51.100.7'
ORDER BY masklen(network) DESC
LIMIT 1;
```