New In DuckDB 2.0, the dsdgen data generator was updated to match the TPC-DS version 4 tools. As a result, the generated data differs slightly from the data generated by DuckDB v1.x. See the related PR for more details.

The tpcds extension implements the data generator and queries for the TPC-DS benchmark.

Installing and Loading

The tpcds extension will be transparently autoloaded on first use from the official extension repository. If you would like to install and load it manually, run:

INSTALL AND LOAD tpcds;

Usage

To generate data for scale factor 1, use:

CALL dsdgen(sf = 1);

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

PRAGMA tpcds(8);

Listing Queries

To list all 99 queries, run:

FROM tpcds_queries();

This function returns a table with columns query_nr and query.

Scale Factor-Dependent Query Parameters

New This feature was introduced in DuckDB 2.0.

The tpcds_queries function and the tpcds pragma take an optional scale factor, either as a positional argument or as the sf named parameter:

FROM tpcds_queries(sf = 10);
PRAGMA tpcds(9, sf = 10);

The returned queries use the substitution parameters that the official TPC-DS query generator produces for the given scale factor. Only query 9 has substitution parameters that depend on the scale factor. Without a scale factor, the queries use the parameters for scale factor 1. The scale factor must be greater than 0 and at most 100000.

Listing Expected Answers

To produce the expected results for all queries on scale factors 1 and 10, run:

FROM tpcds_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-DS without any data by setting the scale factor to 0:

CALL dsdgen(sf = 0);

Data Generator Parameters

The data generator function dsdgen has the following parameters:

Pre-Generated Datasets

Pre-generated DuckDB databases for TPC-DS are available for download. Note that these were generated with DuckDB v1.x. DuckDB v2.x generates slightly different TPC-DS files, compatible with TPC-DS v4.

• tpcds-sf10.db (2.9 GB)
• tpcds-sf30.db (7.7 GB)
• tpcds-sf100.db (26.6 GB)
• tpcds-sf300.db (79.3 GB)

Limitations

The tpcds(query_id) function runs a fixed TPC-DS query with pre-defined bind parameters (a.k.a. substitution parameters). Apart from adjusting them to the scale factor, it is not possible to change the query parameters using the tpcds extension.