Query Your Data Lake in Postgres

Query external Parquet, CSV, JSON, Apache Iceberg, Delta Lake, and HuggingFace datasets directly inside PostgreSQL via pg_duckdb and join them with Postgres tables.

Because pg_mooncake (v0.3.0) embeds DuckDB v2.0.0 via pg_duckdb 1.2.0, you can query external files in your data lake (local disk, S3, Cloudflare R2, GCS, HTTP, or HuggingFace hf:// datasets) directly from PostgreSQL without loading them into heap tables first.

Supported External Formats

Apache Parquet (duckdb.read_parquet)
CSV / TSV (duckdb.read_csv)
JSON / NDJSON (duckdb.read_json)
Apache Iceberg (duckdb.iceberg_scan)
Delta Lake (duckdb.delta_scan)

v0.2 / v0.3 (duckdb.*) vs Legacy v0.1 (mooncake.*) Functions

In pg_mooncake v0.2 and v0.3.0, external file readers are provided directly by pg_duckdb (duckdb.read_parquet, duckdb.read_csv, duckdb.iceberg_scan, duckdb.delta_scan). For the legacy v0.1 mooncake.read_parquet syntax, see v0.1 Query External Files.

1. Querying Parquet Files (`duckdb.read_parquet`)

Use duckdb.read_parquet and specify the expected output columns in the AS (...) clause:

-- Query a single Parquet file or wildcard glob pattern on S3
SELECT
    user_id,
    event_type,
    created_at
FROM duckdb.read_parquet('s3://my-bucket/events/*.parquet') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at >= '2026-01-01';

You can also query Hive-partitioned directory structures:

SELECT *
FROM duckdb.read_parquet('s3://my-bucket/events/year=2026/month=*/day=*/*.parquet') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
);

2. Querying CSV Files (`duckdb.read_csv`)

SELECT
    user_id,
    event_type,
    created_at
FROM duckdb.read_csv('s3://my-bucket/events.csv') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at >= '2026-01-01';

3. Querying External Iceberg & Delta Lake Tables

First install and load the iceberg or delta DuckDB extension via duckdb.install_extension:

SELECT duckdb.install_extension('iceberg');
SELECT duckdb.install_extension('delta');

-- Query an external Apache Iceberg table
SELECT
    user_id,
    event_type,
    created_at
FROM duckdb.iceberg_scan('s3://my-bucket/iceberg_events/metadata/v1.metadata.json') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at >= '2026-01-01';

-- Query an external Delta Lake table
SELECT
    user_id,
    event_type,
    created_at
FROM duckdb.delta_scan('s3://my-bucket/delta_events') AS (
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
)
WHERE created_at >= '2026-01-01';

4. Joining External Files with PostgreSQL Tables

You can join external Parquet, Iceberg, or Delta Lake datasets directly with regular PostgreSQL heap tables or USING mooncake columnstore mirrors in a single SQL query:

SELECT
    u.username,
    u.email,
    e.event_type,
    e.created_at
FROM users u
JOIN duckdb.read_parquet('s3://my-bucket/events.parquet') AS e(
    user_id INT,
    event_type TEXT,
    created_at TIMESTAMP
) ON u.id = e.user_id
WHERE e.created_at >= '2026-01-01';

5. Querying HuggingFace Datasets (`hf://`)

DuckDB supports reading public HuggingFace datasets directly via the hf://datasets/<org>/<repo>/<path> URI scheme:

SELECT id, message, timestamp
FROM duckdb.read_parquet(
    'hf://datasets/OpenLeecher/lmsys_chat_1m_clean/data/train-00000-of-00004.parquet'
) AS (
    id INT,
    message TEXT,
    timestamp TIMESTAMP
)
LIMIT 20;

Query Your Data Lake in Postgres

Supported External Formats

1. Querying Parquet Files (duckdb.read_parquet)

2. Querying CSV Files (duckdb.read_csv)

3. Querying External Iceberg & Delta Lake Tables

4. Joining External Files with PostgreSQL Tables

5. Querying HuggingFace Datasets (hf://)

1. Querying Parquet Files (`duckdb.read_parquet`)

2. Querying CSV Files (`duckdb.read_csv`)

5. Querying HuggingFace Datasets (`hf://`)