How to Query Parquet Files with DuckDB

Create a tiny archive you can reproduce

You have Parquet exports of historical events and need to answer a SQL question without loading them into a database server. DuckDB can read Parquet directly. Start with a local fixture before adding remote storage or an application gateway. The queries here are DuckDB SQL, not ColdCTL commands.

Use a DuckDB CLI or a client with the same SQL support. Run the fixture below in a disposable working directory where tutorial_events.parquet does not already exist. It creates three illustrative events, including an explicit null payload, and writes one local Parquet file. Dates use a stated UTC convention but are stored as TIMESTAMP values, not an automatic timezone conversion.

CREATE TEMP TABLE tutorial_events AS
SELECT * FROM (VALUES
  (1::BIGINT, TIMESTAMP '2024-06-01 10:00:00', 'login', 'ok'),
  (2::BIGINT, TIMESTAMP '2024-06-01 11:00:00', 'logout', NULL),
  (3::BIGINT, TIMESTAMP '2024-07-01 09:00:00', 'login', 'ok')
) AS t(id, created_at, event_type, payload);
COPY tutorial_events TO 'tutorial_events.parquet'
  (FORMAT PARQUET, COMPRESSION ZSTD);

Read a file and inspect its schema

Start by checking the schema and a few rows. read_parquet returns a relation you can select from. DESCRIBE checks column names and types before you rely on assumptions inherited from the source database. A small fixture is an appropriate place to use SELECT *; on a wide real archive, select the fields needed by the question.

The fixture contains three rows. Ordering by id makes its expected output deterministic. A successful read proves that this file can be opened by this reader; it does not verify the completeness of a production export.

DESCRIBE SELECT * FROM read_parquet('tutorial_events.parquet');
SELECT *
FROM read_parquet('tutorial_events.parquet')
ORDER BY id;

Use projection and half-open time filters

Select specific columns and use a half-open interval: include the beginning and exclude the next boundary. That avoids inventing a last possible timestamp for a day or month. Match the stored time convention; a filter is only meaningful when export and query interpret time consistently.

This query returns fixture row 1. DuckDB can push projections and filters into the Parquet reader, although skipped work depends on metadata and layout. LIMIT bounds the returned rows, not necessarily all I/O or CPU required to find them.

SELECT id, created_at, event_type
FROM read_parquet('tutorial_events.parquet')
WHERE created_at >= TIMESTAMP '2024-06-01 00:00:00'
  AND created_at < TIMESTAMP '2024-06-02 00:00:00'
  AND event_type = 'login'
ORDER BY id
LIMIT 100;

Aggregate before exporting a large result

If the question is how many events of each type occurred, return the grouped answer rather than every event. This example yields login = 2 and logout = 1 because those are the fixture’s actual input values, not measured benchmark results.

A narrow aggregate can still scan many rows. Before allowing an endpoint to run arbitrary queries, define time, memory, concurrency and result-size limits independently of SQL result shape.

SELECT event_type, count(*) AS events
FROM read_parquet('tutorial_events.parquet')
GROUP BY event_type
ORDER BY event_type;

Read multiple files without counting the same export twice

A glob can read a directory of batch files; an explicit list is useful when a manifest has already selected the completed batches. Paths in this example are illustrative and must exist. Do not pass both a glob and an overlapping explicit file into the same read.

Keep temporary files, duplicate retries and superseded exports out of the accepted file set. Schema compatibility is a separate issue: union_by_name can align compatible missing or reordered columns, but it does not resolve contradictory business meanings or guarantee safe conversion for incompatible types.

SELECT event_type, count(*) AS events
FROM read_parquet([
  'archive/audit_events/batch-0001.parquet',
  'archive/audit_events/batch-0002.parquet'
], union_by_name = true)
GROUP BY event_type;
-- Alternative file selection:
-- read_parquet('archive/audit_events/batch-*.parquet')

Use Hive partitions to narrow the file set

Hive-style paths encode partition keys as directory names such as year=2024/month=6. DuckDB can expose those keys as columns and use matching filters to select partitions. This is distinct from skipping row groups inside a selected file.

The next example writes a separate fixture directory and then queries only June. It returns two events. The derived year and month directory values follow the fixture’s timestamp convention. Avoid a high-cardinality partition per identifier; fewer meaningful groups are easier to catalog and manage.

COPY (
  SELECT *, year(created_at) AS year, month(created_at) AS month
  FROM tutorial_events
) TO 'tutorial_partitioned'
  (FORMAT PARQUET, PARTITION_BY (year, month));
SELECT count(*) AS june_events
FROM read_parquet('tutorial_partitioned/year=*/month=*/*.parquet',
                  hive_partitioning = true)
WHERE year = 2024 AND month = 6;

Configure object storage access explicitly

For S3 reads, DuckDB uses its httpfs extension and storage authentication. The example uses a temporary, scoped secret with a credential chain so access comes from an existing authorized environment or role rather than a key copied into SQL. INSTALL needs extension-download access unless the extension is already available in the deployment.

Replace the bucket and prefix with your own authorized archive. S3-compatible endpoints such as MinIO require appropriate endpoint, region and connection settings. Azure Blob uses a different extension and secret configuration; do not assume this S3 example configures every destination.

INSTALL httpfs;
LOAD httpfs;
INSTALL aws;
LOAD aws;
CREATE SECRET archive_read (
  TYPE s3,
  PROVIDER credential_chain,
  SCOPE 's3://your-bucket/archive/'
);
SELECT id, created_at, event_type
FROM read_parquet('s3://your-bucket/archive/audit_events/*.parquet')
WHERE event_type = 'login'
LIMIT 100;

Inspect the plan and account for data layout

EXPLAIN shows how a query is planned. EXPLAIN ANALYZE executes it and reports execution information, so use it with a bounded, authorized dataset. Compare projected columns and filters in the scan rather than inferring efficiency from a small returned result.

Selective time filters benefit when file selection and row-group ranges match the query. Scattered values can limit statistics-based skipping. Remote request overhead, many tiny files, broad globs and large result transfers can dominate the workflow. Record reader version and layout when comparing trials; this tutorial makes no latency or throughput claim.

EXPLAIN
SELECT id, event_type
FROM read_parquet('tutorial_events.parquet')
WHERE created_at < TIMESTAMP '2024-07-01 00:00:00';

Keep SQL retrieval separate from restoration

A query answers a question against retained values. Restoring those records into an operational database also needs a compatible schema, a type mapping and an identifier-conflict policy. Test those requirements separately instead of treating a successful SELECT as proof of database recovery.

For a repeatable local check of this tutorial, the fixture count is 3, the June count is 2, the filtered identifier is 1, and the null-payload count is 1. These assertions exercise correctness only; they are not performance measurements. A production archive additionally needs its own manifest, integrity evidence and access controls.

SELECT count(*) AS null_payloads
FROM read_parquet('tutorial_events.parquet')
WHERE payload IS NULL;

• Why Parquet is useful for historical database data →

Apply this to an archive you control

Start with the right file set, inspect schema, select needed columns, constrain the time range and check the plan. Put authorization and resource limits around a shared query service. SQL over files can keep infrequent historical access separate from a continuously running copy of the production database.

ColdCTL Archive Gateway embeds DuckDB to query local, S3 and Azure Blob archives on customer-operated infrastructure. ColdCTL implements PostgreSQL, MySQL 8.4 and MongoDB 8.0 archival through separate connectors, with local Parquet, S3 and Azure Blob destinations, verification and explicitly authorized source deletion. Its customer-hosted Archive Gateway supports browser retrieval and bounded downloads. Engine-specific limits and production qualification still apply. Review the development scope before deployment. The tutorial’s standalone DuckDB queries can be used independently.

• Searchable archive architecture and current status →