Skip to content

Commit 0911bf3

Browse files
authored
Add benchmark derived from TPC-H (apache#355)
1 parent befd967 commit 0911bf3

29 files changed

Lines changed: 1027 additions & 2 deletions

‎benchmarks/tpch/.gitignore‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
data
2+
results.csv

‎benchmarks/tpch/README.md‎

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,78 @@
1+
<!---
2+
Licensed to the Apache Software Foundation (ASF) under one
3+
or more contributor license agreements. See the NOTICE file
4+
distributed with this work for additional information
5+
regarding copyright ownership. The ASF licenses this file
6+
to you under the Apache License, Version 2.0 (the
7+
"License"); you may not use this file except in compliance
8+
with the License. You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing,
13+
software distributed under the License is distributed on an
14+
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15+
KIND, either express or implied. See the License for the
16+
specific language governing permissions and limitations
17+
under the License.
18+
-->
19+
20+
# DataFusion Python Benchmarks Derived from TPC-H
21+
22+
## Create Release Build
23+
24+
From repo root:
25+
26+
```bash
27+
maturin develop --release
28+
```
29+
30+
Note that release builds take a really long time, so you may want to temporarily comment out this section of the
31+
root Cargo.toml when frequently building.
32+
33+
```toml
34+
[profile.release]
35+
lto = true
36+
codegen-units = 1
37+
```
38+
39+
## Generate Data
40+
41+
```bash
42+
./tpch-gen.sh 1
43+
```
44+
45+
## Run Benchmarks
46+
47+
```bash
48+
python tpch.py ./data ./queries
49+
```
50+
51+
A summary of the benchmark timings will be written to `results.csv`. For example:
52+
53+
```csv
54+
setup,1.4
55+
q1,2978.6
56+
q2,679.7
57+
q3,2943.7
58+
q4,2894.9
59+
q5,3592.3
60+
q6,1691.4
61+
q7,3003.9
62+
q8,3818.7
63+
q9,4237.9
64+
q10,2344.7
65+
q11,526.1
66+
q12,2284.6
67+
q13,1009.2
68+
q14,1738.4
69+
q15,1942.1
70+
q16,499.8
71+
q17,5178.9
72+
q18,4127.7
73+
q19,2056.6
74+
q20,2162.5
75+
q21,8046.5
76+
q22,754.9
77+
total,58513.2
78+
```

‎benchmarks/tpch/create_tables.sql‎

Lines changed: 119 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,119 @@
1+
-- Schema derived from TPC-H schema under the terms of the TPC Fair Use Policy.
2+
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.
3+
4+
CREATE EXTERNAL TABLE customer (
5+
c_custkey INT NOT NULL,
6+
c_name VARCHAR NOT NULL,
7+
c_address VARCHAR NOT NULL,
8+
c_nationkey INT NOT NULL,
9+
c_phone VARCHAR NOT NULL,
10+
c_acctbal DECIMAL(15, 2) NOT NULL,
11+
c_mktsegment VARCHAR NOT NULL,
12+
c_comment VARCHAR NOT NULL,
13+
c_extra VARCHAR NOT NULL,
14+
)
15+
STORED AS CSV
16+
WITH HEADER ROW DELIMITER '|'
17+
LOCATION '$PATH/customer.csv';
18+
19+
CREATE EXTERNAL TABLE lineitem (
20+
l_orderkey INT NOT NULL,
21+
l_partkey INT NOT NULL,
22+
l_suppkey INT NOT NULL,
23+
l_linenumber INT NOT NULL,
24+
l_quantity DECIMAL(15, 2) NOT NULL,
25+
l_extendedprice DECIMAL(15, 2) NOT NULL,
26+
l_discount DECIMAL(15, 2) NOT NULL,
27+
l_tax DECIMAL(15, 2) NOT NULL,
28+
l_returnflag VARCHAR NOT NULL,
29+
l_linestatus VARCHAR NOT NULL,
30+
l_shipdate DATE NOT NULL,
31+
l_commitdate DATE NOT NULL,
32+
l_receiptdate DATE NOT NULL,
33+
l_shipinstruct VARCHAR NOT NULL,
34+
l_shipmode VARCHAR NOT NULL,
35+
l_comment VARCHAR NOT NULL,
36+
l_extra VARCHAR NOT NULL,
37+
)
38+
STORED AS CSV
39+
WITH HEADER ROW DELIMITER '|'
40+
LOCATION '$PATH/lineitem.csv';
41+
42+
CREATE EXTERNAL TABLE nation (
43+
n_nationkey INT NOT NULL,
44+
n_name VARCHAR NOT NULL,
45+
n_regionkey INT NOT NULL,
46+
n_comment VARCHAR NOT NULL,
47+
n_extra VARCHAR NOT NULL,
48+
)
49+
STORED AS CSV
50+
WITH HEADER ROW DELIMITER '|'
51+
LOCATION '$PATH/nation.csv';
52+
53+
CREATE EXTERNAL TABLE orders (
54+
o_orderkey INT NOT NULL,
55+
o_custkey INT NOT NULL,
56+
o_orderstatus VARCHAR NOT NULL,
57+
o_totalprice DECIMAL(15, 2) NOT NULL,
58+
o_orderdate DATE NOT NULL,
59+
o_orderpriority VARCHAR NOT NULL,
60+
o_clerk VARCHAR NOT NULL,
61+
o_shippriority INT NULL,
62+
o_comment VARCHAR NOT NULL,
63+
o_extra VARCHAR NOT NULL,
64+
)
65+
STORED AS CSV
66+
WITH HEADER ROW DELIMITER '|'
67+
LOCATION '$PATH/orders.csv';
68+
69+
CREATE EXTERNAL TABLE part (
70+
p_partkey INT NOT NULL,
71+
p_name VARCHAR NOT NULL,
72+
p_mfgr VARCHAR NOT NULL,
73+
p_brand VARCHAR NOT NULL,
74+
p_type VARCHAR NOT NULL,
75+
p_size INT NULL,
76+
p_container VARCHAR NOT NULL,
77+
p_retailprice DECIMAL(15, 2) NOT NULL,
78+
p_comment VARCHAR NOT NULL,
79+
p_extra VARCHAR NOT NULL,
80+
)
81+
STORED AS CSV
82+
WITH HEADER ROW DELIMITER '|'
83+
LOCATION '$PATH/part.csv';
84+
85+
CREATE EXTERNAL TABLE partsupp (
86+
ps_partkey INT NOT NULL,
87+
ps_suppkey INT NOT NULL,
88+
ps_availqty INT NOT NULL,
89+
ps_supplycost DECIMAL(15, 2) NOT NULL,
90+
ps_comment VARCHAR NOT NULL,
91+
ps_extra VARCHAR NOT NULL,
92+
)
93+
STORED AS CSV
94+
WITH HEADER ROW DELIMITER '|'
95+
LOCATION '$PATH/partsupp.csv';
96+
97+
CREATE EXTERNAL TABLE region (
98+
r_regionkey INT NOT NULL,
99+
r_name VARCHAR NOT NULL,
100+
r_comment VARCHAR NOT NULL,
101+
r_extra VARCHAR NOT NULL,
102+
)
103+
STORED AS CSV
104+
WITH HEADER ROW DELIMITER '|'
105+
LOCATION '$PATH/region.csv';
106+
107+
CREATE EXTERNAL TABLE supplier (
108+
s_suppkey INT NOT NULL,
109+
s_name VARCHAR NOT NULL,
110+
s_address VARCHAR NOT NULL,
111+
s_nationkey INT NOT NULL,
112+
s_phone VARCHAR NOT NULL,
113+
s_acctbal DECIMAL(15, 2) NOT NULL,
114+
s_comment VARCHAR NOT NULL,
115+
s_extra VARCHAR NOT NULL,
116+
)
117+
STORED AS CSV
118+
WITH HEADER ROW DELIMITER '|'
119+
LOCATION '$PATH/supplier.csv';

‎benchmarks/tpch/queries/q1.sql‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
-- Benchmark Query 1 derived from TPC-H query 1 under the terms of the TPC Fair Use Policy.
2+
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.
3+
select
4+
l_returnflag,
5+
l_linestatus,
6+
sum(l_quantity) as sum_qty,
7+
sum(l_extendedprice) as sum_base_price,
8+
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
9+
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
10+
avg(l_quantity) as avg_qty,
11+
avg(l_extendedprice) as avg_price,
12+
avg(l_discount) as avg_disc,
13+
count(*) as count_order
14+
from
15+
lineitem
16+
where
17+
l_shipdate <= date '1998-12-01' - interval '68 days'
18+
group by
19+
l_returnflag,
20+
l_linestatus
21+
order by
22+
l_returnflag,
23+
l_linestatus;

‎benchmarks/tpch/queries/q10.sql‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
-- Benchmark Query 10 derived from TPC-H query 10 under the terms of the TPC Fair Use Policy.
2+
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.
3+
select
4+
c_custkey,
5+
c_name,
6+
sum(l_extendedprice * (1 - l_discount)) as revenue,
7+
c_acctbal,
8+
n_name,
9+
c_address,
10+
c_phone,
11+
c_comment
12+
from
13+
customer,
14+
orders,
15+
lineitem,
16+
nation
17+
where
18+
c_custkey = o_custkey
19+
and l_orderkey = o_orderkey
20+
and o_orderdate >= date '1993-07-01'
21+
and o_orderdate < date '1993-07-01' + interval '3' month
22+
and l_returnflag = 'R'
23+
and c_nationkey = n_nationkey
24+
group by
25+
c_custkey,
26+
c_name,
27+
c_acctbal,
28+
c_phone,
29+
n_name,
30+
c_address,
31+
c_comment
32+
order by
33+
revenue desc limit 20;

‎benchmarks/tpch/queries/q11.sql‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
-- Benchmark Query 11 derived from TPC-H query 11 under the terms of the TPC Fair Use Policy.
2+
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.
3+
select
4+
ps_partkey,
5+
sum(ps_supplycost * ps_availqty) as value
6+
from
7+
partsupp,
8+
supplier,
9+
nation
10+
where
11+
ps_suppkey = s_suppkey
12+
and s_nationkey = n_nationkey
13+
and n_name = 'ALGERIA'
14+
group by
15+
ps_partkey having
16+
sum(ps_supplycost * ps_availqty) > (
17+
select
18+
sum(ps_supplycost * ps_availqty) * 0.0001000000
19+
from
20+
partsupp,
21+
supplier,
22+
nation
23+
where
24+
ps_suppkey = s_suppkey
25+
and s_nationkey = n_nationkey
26+
and n_name = 'ALGERIA'
27+
)
28+
order by
29+
value desc;

‎benchmarks/tpch/queries/q12.sql‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
-- Benchmark Query 12 derived from TPC-H query 12 under the terms of the TPC Fair Use Policy.
2+
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.
3+
select
4+
l_shipmode,
5+
sum(case
6+
when o_orderpriority = '1-URGENT'
7+
or o_orderpriority = '2-HIGH'
8+
then 1
9+
else 0
10+
end) as high_line_count,
11+
sum(case
12+
when o_orderpriority <> '1-URGENT'
13+
and o_orderpriority <> '2-HIGH'
14+
then 1
15+
else 0
16+
end) as low_line_count
17+
from
18+
orders,
19+
lineitem
20+
where
21+
o_orderkey = l_orderkey
22+
and l_shipmode in ('FOB', 'SHIP')
23+
and l_commitdate < l_receiptdate
24+
and l_shipdate < l_commitdate
25+
and l_receiptdate >= date '1995-01-01'
26+
and l_receiptdate < date '1995-01-01' + interval '1' year
27+
group by
28+
l_shipmode
29+
order by
30+
l_shipmode;

‎benchmarks/tpch/queries/q13.sql‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
-- Benchmark Query 13 derived from TPC-H query 13 under the terms of the TPC Fair Use Policy.
2+
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.
3+
select
4+
c_count,
5+
count(*) as custdist
6+
from
7+
(
8+
select
9+
c_custkey,
10+
count(o_orderkey)
11+
from
12+
customer left outer join orders on
13+
c_custkey = o_custkey
14+
and o_comment not like '%express%requests%'
15+
group by
16+
c_custkey
17+
) as c_orders (c_custkey, c_count)
18+
group by
19+
c_count
20+
order by
21+
custdist desc,
22+
c_count desc;

‎benchmarks/tpch/queries/q14.sql‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
-- Benchmark Query 14 derived from TPC-H query 14 under the terms of the TPC Fair Use Policy.
2+
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.
3+
select
4+
100.00 * sum(case
5+
when p_type like 'PROMO%'
6+
then l_extendedprice * (1 - l_discount)
7+
else 0
8+
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
9+
from
10+
lineitem,
11+
part
12+
where
13+
l_partkey = p_partkey
14+
and l_shipdate >= date '1995-02-01'
15+
and l_shipdate < date '1995-02-01' + interval '1' month;

‎benchmarks/tpch/queries/q15.sql‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
-- Benchmark Query 15 derived from TPC-H query 15 under the terms of the TPC Fair Use Policy.
2+
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.
3+
create view revenue0 (supplier_no, total_revenue) as
4+
select
5+
l_suppkey,
6+
sum(l_extendedprice * (1 - l_discount))
7+
from
8+
lineitem
9+
where
10+
l_shipdate >= date '1996-08-01'
11+
and l_shipdate < date '1996-08-01' + interval '3' month
12+
group by
13+
l_suppkey;
14+
select
15+
s_suppkey,
16+
s_name,
17+
s_address,
18+
s_phone,
19+
total_revenue
20+
from
21+
supplier,
22+
revenue0
23+
where
24+
s_suppkey = supplier_no
25+
and total_revenue = (
26+
select
27+
max(total_revenue)
28+
from
29+
revenue0
30+
)
31+
order by
32+
s_suppkey;
33+
drop view revenue0;

0 commit comments

Comments
 (0)