Make it easier to run TPCH queries with datafusion-cli

Is your feature request related to a problem or challenge?

TPC-H is an important and well studied benchmark. It is used for testing many database optimizations and is well known and widely studied. TPCH This is especially important for academic research projects and classes. For example see the CMU optimizer class (see #14373)

Generating this dataset today is quite a pain as it requires compiling and running a very old c program dbgen.c (see XXX)

DuckDB has a TPCH extension that makes it very easy to create the dataset and queries: https://duckdb.org/docs/extensions/tpch.html

I also think this is another reason why DuckDB is so popular with Academic research as it lowers the barrier to getting this dataset

Today, to generate this dataset, the DataFusion bench.sh program

bench.sh runs tpdh dbgen from this container: https://github.com/scalytics/TPCH-Docker/pkgs/container/tpch-docker

And the eventual code is here: https://github.com/scalytics/TPCH-Docker/tree/main/data/tpch/2.18.0_rc2/dbgen

This setup is non ideal for several reasons:

It requires docker, and takes quite a while to run
It makes CSV files which are almost never what is used in practice (people use parquet, etc)
It isn't part of datafusion-cli

Describe the solution you'd like

I would like it to be very easy to create data and run TPCH queries in datafusion-cli

Describe alternatives you've considered

Idea 1: Instructions + Precalculate the Data

The idea here would be to precalculate the data and find somewhere to host it (I am sure the ASF has places to host files, but we would need to research what the limits are, etc).

Here is an example repo: https://github.com/aleaugustoplus/tpch-data (maybe we can do the same or even use that one)

Then we would provide instructions / a script on how to download and use the files with datafusion-cli

Ideally we would provide the data in parquet format

Idea 2: Integrate the dbgen function into `datafusion-cli` (like DuckDB)

The idea here would be to implement some/all of the syntax from DuckDB: https://duckdb.org/docs/extensions/tpch.html

That would mean a command like this

CALL dbgen(sf = 1);

That would create the 8 TPCH tables, along with command to show the queries and answers

The trick here is that dbgen is some ancient c program and there is no Rust version I could find. It is critical that that data is exactly the same.

Transliterating dbgen.c from C to Rust might be a fun project (and maybe someone could figure out how to make it parallel while they are at it)

Additional context

No response

If you prefer Java to C, CMU-DB's BenchBase project does implement support for generating and loading TPC-H data in parallel: https://github.com/cmu-db/benchbase/tree/main/src/main/java/com/oltpbenchmark/benchmarks/tpch

Another alternative that I explored is using DuckDB to generate the data, exporting that as Parquet, and then ingesting it into DataFusion (schema may require fixing):

./duckdb data/tpch.db -c "INSTALL tpch; LOAD tpch; CALL dbgen(sf = 1); EXPORT DATABASE './data/' (FORMAT PARQUET);"

But personally I think native integration makes for the best user experience.

@lmwnshn

Thanks @lmwnshn -- the Java implementation might be easier to transliterate to Rust...

Also BTW I am pretty sure other rust data projects would be interested in a Rust implementation of the TPCH data generator, so I bet we could get some others to help

@alamb

Hey @alamb @lmwnshn I've been actually following the CMU 15-799 course (nights and weekend's mostly) and started working on a Rust port of the benchbase Java implementation after seieng this discussion, but I am also looking at Trino's TPCH generator and DuckDB's generator.

I ported most of the randomness logic, I tried to keep it compatible (bug for bug), I am currently working on sanity checks for the RNG stuff but I am seeing some discrepancies .

Example (my implementation) :

+-------------+--------------+-----------------------------------------------------------------------------------+
| r_regionkey | r_name       | r_comment                                                                         |
+-------------+--------------+-----------------------------------------------------------------------------------+
| 0           | AFRICA       | e. blithely special packages boost finally bold, quiet pains. furiously regular   |
|             |              | instructions cajole furiously! fina                                               |
+-------------+--------------+-----------------------------------------------------------------------------------+
| 1           | AMERICA      | counts. ironic, even ideas use                                                    |
+-------------+--------------+-----------------------------------------------------------------------------------+
| 2           | ASIA         | , ironic platelets. regular, qu                                                   |
+-------------+--------------+-----------------------------------------------------------------------------------+
| 3           | EUROPE       | . slyly express dolphins use carefully. even                                      |
+-------------+--------------+-----------------------------------------------------------------------------------+
| 4           | MIDDLE EAST  | n foxes. slowly unusual deposits might cajole blithely special theodolites.       |
|             |              | evenly express deposits sleep ca                                                  |
+-------------+--------------+-----------------------------------------------------------------------------------+

Duck DB's output after running INSTALL tpch; LOAD tpch; CALL dbgen(sf = 1);select * from region.

┌─────────────┬─────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ r_regionkey │   r_name    │                                                      r_comment                                                      │
│    int32    │   varchar   │                                                       varchar                                                       │
├─────────────┼─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│           0 │ AFRICA      │ ar packages. regular excuses among the ironic requests cajole fluffily blithely final requests. furiously express p │
│           1 │ AMERICA     │ s are. furiously even pinto bea                                                                                     │
│           2 │ ASIA        │ c, special dependencies around                                                                                      │
│           3 │ EUROPE      │ e dolphins are furiously about the carefully                                                                        │
│           4 │ MIDDLE EAST │  foxes boost furiously along the carefully dogged tithes. slyly regular orbits according to the special epit        │
└─────────────┴─────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Apache Trino's output by running the following test

    @Test
    public void testRegionsGenerator() {
        var regionsGenerator = new RegionGenerator();

        for (var region : regionsGenerator) {
            System.out.println(region);
        }
    }

  Command line suite

Region[rowNumber=0, regionKey=0, name=AFRICA, comment=lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to ]
Region[rowNumber=1, regionKey=1, name=AMERICA, comment=hs use ironic, even requests. s]
Region[rowNumber=2, regionKey=2, name=ASIA, comment=ges. thinly even pinto beans ca]
Region[rowNumber=3, regionKey=3, name=EUROPE, comment=ly final courts cajole furiously final excuse]
Region[rowNumber=4, regionKey=4, name=MIDDLE EAST, comment=uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl]

My guess is that the comments being random is either a side effect or a bug I am trying to confirm which before I add support for the remaining tables.

My goal is to potentially donate it to the datafusion-contrib organization and then keep maintaining it there this way we can coordinate how to integrate it into datafusion as an extension. I am keeping the repo private for now until I finish a 0.1.0 release but can invite you and others on request.

take

@clflushopt

My goal is to potentially donate it to the datafusion-contrib organization and then keep maintaining it there this way we can coordinate how to integrate it into datafusion as an extension. I am keeping the repo private for now until I finish a 0.1.0 release but can invite you and others on request.

AMAZING! THank you so much @clflushopt -- this is really cool to hear. I think it would make a really big difference

@alamb

Hey @alamb as of today I have a fully working implementation that matches Apache Trino and OLTPBenchmark's, I found the issue I mentionned in the message above which was due to how insertion order for maps behaves. For now the implementation is very slow (I focused more on compatibility at the cost of code quality and performance) but I have a plan to fix and cleanup everything so I should have a v0.1.0 out in a couple of weeks.

I invited you to the repo in case you wanted to take a look or give me some feedback, hopefully I'll have this open-sourced sometime next week once I clean it up a bit.

Hey @alamb as of today I have a fully working implementation that matches Apache Trino and OLTPBenchmark's, I found the issue I mentionned in the message above which was due to how insertion order for maps behaves. For now the implementation is very slow (I focused more on compatibility at the cost of code quality and performance) but I have a plan to fix and cleanup everything so I should have a v0.1.0 out in a couple of weeks.

I invited you to the repo in case you wanted to take a look or give me some feedback, hopefully I'll have this open-sourced sometime next week once I clean it up a bit.

AMAZING! I will definitely check it out. Hopefully I can do so today or tomorrow

BTW I suspect other members of the datafusion community would be interested to if you wanted to make your repo open for all (I don't really know your rationale for keeping it closed, but that is of course your choice)

@alamb

@alamb Hey yeah sorry it just by habit I like to complete things before "releasing" them, but just made it open !

For anyone following this issue I have a full port here https://github.com/clflushopt/tpchgen-rs and I am working on completing a first release (I have issues to track that milestone if you want to keep an eye on).

@clflushopt

@clflushopt this is awesome. Once you release I will likely add this to dft (from a quick glance it looked like there was a lib and a cli - i would probably use the lib)

@clflushopt

@clflushopt Nice work! Re: randomness, the TPC-H spec has a "qualification database" (dataset) with specific "query validation" tests (instantiating the SQL queries with specific parameters) that should produce the "sample output" (reference outcome). I think producing identical output on the validation queries is a good enough target.

@lmwnshn

@lmwnshn

@clflushopt has some very cool ideas for testing in tpchdbgen-rs

Specifically we verified that the output data (for SF 0.001 and SF 0.01) is byte-for-byte identical with dbgen
See

Add instructions for verifying checked in results datafusion-contrib/tpcgen-rs#13
feat: Introduce gzipped test fixtures and steps to reproduce. datafusion-contrib/tpcgen-rs#11 (comment) (I found a bug!)
feat: Introduce end-to-end conformance integration tests. datafusion-contrib/tpcgen-rs#16

Good to see this rust generator. We have adopted it in our database projection for benchmarking.

sounds good to me!
SELECT * FROM lineitem(1.0) makes sense
SELECT 1 FROM tpchgen(1.0) looks a bit odd but i cant think of a better alternative

I think we could use a user defined TABLE function:

https://datafusion.apache.org/library-user-guide/adding-udfs.html#adding-a-user-defined-table-function

So that would look something like

SELECT * from tpch.lineitem(1.0)
SELECT * from tpch.orders(1.0)
...

I think these table functions would be useful as their own crate

In terms of a command to create all the tables, perhaps we can make another table function like

SELECT * from tpchgen(1.0)

And have it return a struct with a status. Perhaps like

Header
`{ output_location: "/foo/bar/tpch", format: "parquet", scale_factor: "1.0", tables: 8, bytes: 123456 }`

(this table function internally would use the tpch.lineitem function defined above

@alamb

I was stuck trying to decide between a scalar function or a table function for tpchgen(sf) I really like your suggestion @alamb thanks for unblocking. I'll have a v0.1.0 done sooner now !

@alamb

Hey @alamb @kevinjqliu I have individual TPCH table generators working fine https://github.com/clflushopt/datafusion-tpch/blob/main/src/lib.rs and I am working on introducing a single table function as discussed, this is a WIP PR datafusion-contrib/datafusion-tpch#1

@clflushopt

Thansk @clflushopt -- I'll try and check this out tomorrow or Tuesday

@alamb

Hey @alamb following suggestions from @kevinjqliu I am happy to say that https://github.com/clflushopt/datafusion-tpch provides a ux on par with duckdb and what we discussed in this thread. I am open to suggestions with regards to improvements especially perf-wise since it currently doesn't leverage the same parallel approach we used in tpchgen-cli. Aslo @matthewmturner @lmwnshn if you wish to chime in feel free to open an issue, I can publish the package later this week in crates.io.

@clflushopt

This is great, thanks @clflushopt

I couldn't find a way to use datafusion to write multiple parquet files, but i think this is a limitation with datafusion's COPY command.

@clflushopt

Sorry for not having a chance to test this work earlier @clflushopt

I really look forward to checking it out and will try to do so later this week.

@alamb

@alamb hey since I'll start dedicating more time to work on porting TPCDS into tpchgen let me know if there's anything missing from https://github.com/clflushopt/datafusion-tpch and how I can help move this issue further I'd love to get this ticket closed since we've done much of the work already :D

@matthewmturner

I have been thinking about this one a lot and I am sorry I haven't written thus up before now. I was trying to collect my thoughts.

I feel the core isssue is a tension between

datafusion-cli as a easy to use, fully pre-integrated tool for querying files (locally and remotely)
Using datafusion-cli as a testing vehicle for DataFusion development itself.
Using datafusion-cli as an example of how to integrate various DataFusion features (like aws s3, etc)

datafusion-cli already has quite a few features that are outside the core usecase of testing DataFusion (e.g. aws s3 auth support)

The more I think about it, tntegrating easy to use tpch functions into datafusion-cli feels like it is part of the first and thus maybe doesn't actually belong in the datafusion repository's datafuson-cli itself after all

Some possible paths forward (not mutually exclusive)

Document how to use tpchgen-rs to create TPCH data that can be queried by datafusion-cli (somewhere in the cli docs) but don't actually add more code to datafusion-cli
Move the https://github.com/clflushopt/datafusion-tpch repo into the datafusion-contrib github organization so it is more discoverable
Actually implement the datafusion tpch functions from https://github.com/clflushopt/datafusion-tpch into the core datafusion repository (along with a dependency on tpchgen-rs).
Create a new repository for a datafusion-cli++ (probably need a better name) with the explicit goal of being a fully pre-integrated CLI experience like duckddb

I have been dreaming about the datafusion-cli++ idea for a while now too. I think it would be really cool technically to build a tool that was able to query remote sources really quickly and easily (aka kind of a daft like experience) -- think caching parquet metadata, catalog, iceberg, etc. But I get ahead of myself and I haven't convinced myself this can be done in a reaonable amount of time

@matthewmturner is working on something similar in https://github.com/datafusion-contrib/datafusion-dft but that also includes other things such as as tui (in fact he seems to be using tpchgen as well: datafusion-contrib/datafusion-dft#331 :) )

It pleases me to no end that the duckdb people now use our tpchgen-rs tool to generate the TPCH data set https://duckdb.org/2025/10/09/benchmark-results-14-lts (when the original reason we did that was b/c people were using duckdb to make the dataset)

i think with the current state of tpchgen-cli this is now pretty easy to run tpch queries with datafusion-cli so I am going to claim this is closed

tpchgen-cli --format=parquet --parts=10

Then you can just run the queries like this:

andrewlamb@Andrews-MacBook-Pro-3:~/Downloads$ datafusion-cli
DataFusion CLI v52.1.0
> select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
        l_shipdate <= date '1998-09-02'
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty     | sum_base_price  | sum_disc_price    | sum_charge          | avg_qty   | avg_price    | avg_disc | count_order |
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| A            | F            | 37734107.00 | 56586554400.73  | 53758257134.8700  | 55909065222.827692  | 25.522005 | 38273.129734 | 0.049985 | 1478493     |
| N            | F            | 991417.00   | 1487504710.38   | 1413082168.0541   | 1469649223.194375   | 25.516471 | 38284.467760 | 0.050093 | 38854       |
| N            | O            | 74476040.00 | 111701729697.74 | 106118230307.6056 | 110367043872.497010 | 25.502226 | 38249.117988 | 0.049996 | 2920374     |
| R            | F            | 37719753.00 | 56568041380.90  | 53741292684.6040  | 55889619119.831932  | 25.505793 | 38250.854626 | 0.050009 | 1478870     |
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
4 row(s) fetched.
Elapsed 0.109 seconds.

adding on to what Andrew said, you can also use the uv ecosystem to get tpchgen-cli directly:

uvx tpchgen-cli --help

adding on to what Andrew said, you can also use the uv ecosystem to get tpchgen-cli directly:
uvx tpchgen-cli --help

😍

Navigation Menu

Repository navigation

Make it easier to run TPCH queries with datafusion-cli #14608

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Idea 1: Instructions + Precalculate the Data

Idea 2: Integrate the dbgen function into `datafusion-cli` (like DuckDB)

Additional context

18 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Make it easier to run TPCH queries with datafusion-cli #14608

Description

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Idea 1: Instructions + Precalculate the Data

Idea 2: Integrate the dbgen function into datafusion-cli (like DuckDB)

Additional context

Activity

lmwnshn commented on Feb 11, 2025

alamb commented on Feb 11, 2025

clflushopt commented on Mar 9, 2025

clflushopt commented on Mar 9, 2025

alamb commented on Mar 9, 2025

clflushopt commented on Mar 10, 2025

alamb commented on Mar 10, 2025

clflushopt commented on Mar 10, 2025

clflushopt commented on Mar 11, 2025

matthewmturner commented on Mar 11, 2025

lmwnshn commented on Mar 13, 2025

alamb commented on Mar 13, 2025

niebayes commented on Mar 24, 2025

18 remaining items

kevinjqliu commented on May 3, 2025

alamb commented on May 5, 2025

clflushopt commented on May 7, 2025

clflushopt commented on May 24, 2025

alamb commented on May 25, 2025

clflushopt commented on Jun 6, 2025

kevinjqliu commented on Jun 7, 2025

alamb commented on Jun 8, 2025

clflushopt commented on Jun 28, 2025

alamb commented on Jun 29, 2025

alamb commented on Oct 21, 2025

alamb commented on Mar 10, 2026

kevinjqliu commented on Mar 10, 2026

alamb commented on Mar 10, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Idea 2: Integrate the dbgen function into `datafusion-cli` (like DuckDB)