The tpch extension implements the data generator and queries for the TPC-H benchmark.

Installing and Loading

The tpch extension is shipped by default in some DuckDB builds, otherwise it will be transparently autoloaded on first use. If you would like to install and load it manually, run:

INSTALL tpch;
LOAD tpch;

Usage

Generating Data

To generate data for scale factor 1, use:

CALL dbgen(sf = 1);

Calling dbgen does not clean up existing TPC-H tables. To clean up existing tables, use DROP TABLE before running dbgen:

DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS lineitem;
DROP TABLE IF EXISTS nation;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS part;
DROP TABLE IF EXISTS partsupp;
DROP TABLE IF EXISTS region;
DROP TABLE IF EXISTS supplier;

Running a Query

To run a query, e.g., query 4, use:

PRAGMA tpch(4);

Listing Queries

To list all 22 queries, run:

FROM tpch_queries();

This function returns a table with columns query_nr and query.

Listing Expected Answers

To produced the expected results for all queries on scale factors 0.01, 0.1, and 1, run:

FROM tpch_answers();

This function returns a table with columns query_nr, scale_factor, and answer.

Generating the Schema

It's possible to generate the schema of TPC-H without any data by setting the scale factor to 0:

CALL dbgen(sf = 0);

Data Generator Parameters

The data generator function dbgen has the following parameters:

Resource Usage of the Data Generator

Generating TPC-H data sets for large scale factors takes a significant amount of time. Additionally, when the generation is done in a single step, it requires a large amount of memory. The following table gives an estimate on the resources required to produce DuckDB database files containing the generated TPC-H data set using 128 threads.

The numbers shown above were achieved by running the dbgen function in a single step, for example:

CALL dbgen(sf = 300);

If you have a limited amount of memory available, you can run the dbgen function in steps. For example, you may generate SF300 in 10 steps:

CALL dbgen(sf = 300, children = 10, step = 0);
CALL dbgen(sf = 300, children = 10, step = 1);
...
CALL dbgen(sf = 300, children = 10, step = 9);

Limitation

The tpch(⟨query_id⟩) function runs a fixed TPC-H query with pre-defined bind parameters (a.k.a. substitution parameters). It is not possible to change the query parameters using the tpch extension. To run the queries with the parameters prescribed by the TPC-H benchmark, use a TPC-H framework implementation.