Skip to content

Commit 0dc6528

Browse files
Omega359AdamGS
authored andcommitted
Add clickbench extended SQL benchmark (apache#22804)
## 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? clickbench_extended sql benchmark ## Are these changes tested? Yes `BENCH_NAME=clickbench_extended cargo bench --bench sql` `BENCH_NAME=clickbench_extended CLICKBENCH_TYPE=partitioned cargo bench --bench sql` ## Are there any user-facing changes? No
1 parent 2e26f24 commit 0dc6528

13 files changed

Lines changed: 241 additions & 0 deletions

File tree

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
name Q00
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT COUNT(DISTINCT "SearchPhrase"), COUNT(DISTINCT "MobilePhone"), COUNT(DISTINCT "MobilePhoneModel")
16+
FROM hits;
17+
18+
result sql_benchmarks/clickbench_extended/results/q00.csv
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
name Q01
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT COUNT(DISTINCT "HitColor"), COUNT(DISTINCT "BrowserCountry"), COUNT(DISTINCT "BrowserLanguage")
16+
FROM hits;
17+
18+
result sql_benchmarks/clickbench_extended/results/q01.csv
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
name Q02
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT "BrowserCountry", COUNT(DISTINCT "SocialNetwork"), COUNT(DISTINCT "HitColor"), COUNT(DISTINCT "BrowserLanguage"), COUNT(DISTINCT "SocialAction") FROM hits GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
16+
17+
result sql_benchmarks/clickbench_extended/results/q02.csv
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
name Q03
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT "SocialSourceNetworkID", "RegionID", COUNT(*), AVG("Age"), AVG("ParamPrice"), STDDEV("ParamPrice") as s, VAR("ParamPrice") FROM hits GROUP BY "SocialSourceNetworkID", "RegionID" HAVING s IS NOT NULL ORDER BY s DESC LIMIT 10;
16+
17+
result sql_benchmarks/clickbench_extended/results/q03.csv
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
name Q04
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT "ClientIP", "WatchID", COUNT(*) c, MIN("ResponseStartTiming") tmin, MEDIAN("ResponseStartTiming") tmed, MAX("ResponseStartTiming") tmax FROM hits WHERE "JavaEnable" = 0 GROUP BY "ClientIP", "WatchID" HAVING c > 1 ORDER BY tmed DESC LIMIT 10;
16+
17+
result sql_benchmarks/clickbench_extended/results/q04.csv
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
name Q05
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT "ClientIP", "WatchID", COUNT(*) c, MIN("ResponseStartTiming") tmin, APPROX_PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY "ResponseStartTiming") tp95, MAX("ResponseStartTiming") tmax FROM 'hits' WHERE "JavaEnable" = 0 GROUP BY "ClientIP", "WatchID" HAVING c > 1 ORDER BY tp95 DESC LIMIT 10;
16+
17+
result sql_benchmarks/clickbench_extended/results/q05.csv
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
name Q06
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT COUNT(*) AS ShareCount FROM hits WHERE "IsMobile" = 1 AND "MobilePhoneModel" LIKE 'iPhone%' AND "SocialAction" = 'share' AND "SocialSourceNetworkID" IN (5, 12) AND "ClientTimeZone" BETWEEN -5 AND 5 AND regexp_match("Referer", '\/campaign\/(spring|summer)_promo') IS NOT NULL AND CASE WHEN split_part(split_part(CAST("URL" AS STRING), 'resolution=', 2), '&', 1) ~ '^\d+$' THEN split_part(split_part(CAST("URL" AS STRING), 'resolution=', 2), '&', 1)::INT ELSE 0 END > 1920 AND levenshtein(CAST("UTMSource" AS STRING), CAST("UTMCampaign" AS STRING)) < 3;
16+
17+
result sql_benchmarks/clickbench_extended/results/q06.csv
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
name Q07
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT "WatchID", MIN("ResolutionWidth") as wmin, MAX("ResolutionWidth") as wmax, SUM("IsRefresh") as srefresh FROM hits GROUP BY "WatchID" ORDER BY "WatchID" DESC LIMIT 10;
16+
17+
result sql_benchmarks/clickbench_extended/results/q07.csv
Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
name Q08
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
-- Must set for ClickBench hits_partitioned dataset. See https://github.com/apache/datafusion/issues/16591
16+
-- set datafusion.execution.parquet.binary_as_string = true
17+
SELECT "RegionID", "UserAgent", "OS", AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ResponseStartTiming")) as avg_response_time, AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ConnectTiming")) as avg_latency FROM hits GROUP BY "RegionID", "UserAgent", "OS" ORDER BY avg_latency DESC limit 10;
18+
19+
result sql_benchmarks/clickbench_extended/results/q08.csv
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
name Q09
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT MAX(len) FROM (
16+
SELECT LENGTH(FIRST_VALUE("URL" ORDER BY "EventTime")) as len
17+
FROM hits
18+
GROUP BY "UserID"
19+
);
20+
21+
result sql_benchmarks/clickbench_extended/results/q09.csv

0 commit comments

Comments
 (0)