Skip to content

Commit 0d3fb77

Browse files
Omega359alamb
andauthored
Add h2o SQL benchmark (apache#22660)
## Which issue does this PR close? Part of apache#21706 ## Rationale for this change Continue work on sql benchmark migration. ## What changes are included in this PR? h2o sql benchmark ## Are these changes tested? Yes `BENCH_NAME=h2o BENCH_SUBGROUP=groupby H2O_BENCH_SIZE=small H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=groupby H2O_BENCH_SIZE=small H2O_FILE_TYPE=parquet cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=groupby H2O_BENCH_SIZE=medium H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=groupby H2O_BENCH_SIZE=medium H2O_FILE_TYPE=parquet cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=join H2O_BENCH_SIZE=small H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=join H2O_BENCH_SIZE=small H2O_FILE_TYPE=parquet cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=join H2O_BENCH_SIZE=medium H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=join H2O_BENCH_SIZE=medium H2O_FILE_TYPE=parquet cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=window H2O_BENCH_SIZE=small H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=window H2O_BENCH_SIZE=small H2O_FILE_TYPE=parquet cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=window H2O_BENCH_SIZE=medium H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=window H2O_BENCH_SIZE=medium H2O_FILE_TYPE=parquet cargo bench --bench sql` I was unable to run the following because of limited memory: `BENCH_NAME=h2o BENCH_SUBGROUP=groupby H2O_BENCH_SIZE=big H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=groupby H2O_BENCH_SIZE=big H2O_FILE_TYPE=parquet cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=join H2O_BENCH_SIZE=big H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=join H2O_BENCH_SIZE=big H2O_FILE_TYPE=parquet cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=window H2O_BENCH_SIZE=big H2O_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=h2o BENCH_SUBGROUP=window H2O_BENCH_SIZE=big H2O_FILE_TYPE=parquet cargo bench --bench sql` ## Are there any user-facing changes? No --------- Co-authored-by: Andrew Lamb <[email protected]>
1 parent 1465d6f commit 0d3fb77

45 files changed

Lines changed: 709 additions & 0 deletions

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
subgroup groupby
2+
3+
name Q01
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id1, SUM(v1) AS v1
17+
FROM x
18+
GROUP BY id1;
19+
20+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q01.csv
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
subgroup groupby
2+
3+
name Q02
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id1, id2, SUM(v1) AS v1
17+
FROM x
18+
GROUP BY id1, id2;
19+
20+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q02.csv
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
subgroup groupby
2+
3+
name Q03
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id3, SUM(v1) AS v1, AVG(v3) AS v3
17+
FROM x
18+
GROUP BY id3;
19+
20+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q03.csv
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
subgroup groupby
2+
3+
name Q04
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id4, AVG(v1) AS v1, AVG(v2) AS v2, AVG(v3) AS v3
17+
FROM x
18+
GROUP BY id4;
19+
20+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q04.csv
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
subgroup groupby
2+
3+
name Q05
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id6, SUM(v1) AS v1, SUM(v2) AS v2, SUM(v3) AS v3
17+
FROM x
18+
GROUP BY id6;
19+
20+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q05.csv
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
subgroup groupby
2+
3+
name Q06
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id4, id5, MEDIAN(v3) AS median_v3, STDDEV(v3) AS sd_v3
17+
FROM x
18+
GROUP BY id4, id5;
19+
20+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q06.csv
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
subgroup groupby
2+
3+
name Q07
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id3, MAX(v1) - MIN(v2) AS range_v1_v2
17+
FROM x
18+
GROUP BY id3;
19+
20+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q07.csv
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
subgroup groupby
2+
3+
name Q08
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id6, largest2_v3 FROM
17+
(
18+
SELECT id6, v3 AS largest2_v3, ROW_NUMBER() OVER (PARTITION BY id6 ORDER BY v3 DESC) AS order_v3
19+
FROM x WHERE v3 IS NOT NULL
20+
) sub_query WHERE order_v3 <= 2;
21+
22+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q08.csv
Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
subgroup groupby
2+
3+
name Q09
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id2, id4, POWER(CORR(v1, v2), 2) AS r2
17+
FROM x
18+
GROUP BY id2, id4;
19+
20+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q09.csv
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
subgroup groupby
2+
3+
name Q10
4+
group h2o
5+
6+
echo Loading ${H2O_BENCH_SIZE:-small} groupby ${H2O_FILE_TYPE:-csv} h2o data
7+
8+
load sql_benchmarks/h2o/init/load_groupby_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 FROM x
12+
----
13+
true
14+
15+
run
16+
SELECT id1, id2, id3, id4, id5, id6, SUM(v3) AS v3, COUNT(*) AS count FROM x GROUP BY id1, id2, id3, id4, id5, id6;
17+
18+
result sql_benchmarks/h2o/results/groupby/${H2O_BENCH_SIZE:-small}/q10.csv

0 commit comments

Comments
 (0)