Skip to content

Commit 5073db1

Browse files
authored
Add imdb SQL benchmark (apache#22680)
## 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? Imdb sql benchmark. ## Are these changes tested? Yes `BENCH_NAME=imdb IMDB_FILE_TYPE=csv cargo bench --bench sql` `BENCH_NAME=imdb IMDB_FILE_TYPE=parquet cargo bench --bench sql` Note that the IMDB_FILE_TYPE=csv will OOM on most systems because csv doesn't infer statistics and thus won't get scan predicates and dynamic filters pushed into DataSourceExec. This results in queries such a 16a doing joining large tables/intermediates before enough of the selective filters have reduced the data size to not OOM (tested on a 96GB system). Setting PARTITION=1 does not solve the issue. ## Are there any user-facing changes? no
1 parent 11caa4c commit 5073db1

117 files changed

Lines changed: 6110 additions & 4 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎benchmarks/benches/sql.rs‎

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -82,7 +82,7 @@ struct EnvParser {
8282
subgroup: Option<String>,
8383

8484
#[arg(env = "BENCH_QUERY")]
85-
query: Option<i32>,
85+
query: Option<String>,
8686
}
8787

8888
pub fn sql(c: &mut Criterion) {
@@ -306,9 +306,14 @@ fn filter_benchmarks(
306306
if let Some(subgroup) = &args.subgroup {
307307
val.retain(|bench| bench.subgroup().eq_ignore_ascii_case(subgroup));
308308
}
309-
if let Some(query_number) = &args.query {
310-
let padded = format!("Q{query_number:0>2}");
311-
val.retain(|bench| bench.name().eq_ignore_ascii_case(&padded));
309+
if let Some(query) = &args.query {
310+
// Accept `1`, `01`, `6a`, `Q06a`, ... case-insensitively.
311+
// Bench names are canonical, e.g. `Q01`, `Q06a`.
312+
let q = query.trim_start_matches(['Q', 'q']);
313+
let split = q.find(|c: char| !c.is_ascii_digit()).unwrap_or(q.len());
314+
let (num, suffix) = q.split_at(split);
315+
let normalized = format!("Q{num:0>2}{suffix}");
316+
val.retain(|bench| bench.name().eq_ignore_ascii_case(&normalized));
312317
}
313318
(key, val)
314319
})
Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
name Q01a
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(mc.note) AS production_note,
15+
MIN(t.title) AS movie_title,
16+
MIN(t.production_year) AS movie_year
17+
FROM company_type AS ct,
18+
info_type AS it,
19+
movie_companies AS mc,
20+
movie_info_idx AS mi_idx,
21+
title AS t
22+
WHERE ct.kind = 'production companies'
23+
AND it.info = 'top 250 rank'
24+
AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
25+
AND (mc.note LIKE '%(co-production)%'
26+
OR mc.note LIKE '%(presents)%')
27+
AND ct.id = mc.company_type_id
28+
AND t.id = mc.movie_id
29+
AND t.id = mi_idx.movie_id
30+
AND mc.movie_id = mi_idx.movie_id
31+
AND it.id = mi_idx.info_type_id;
32+
33+
result sql_benchmarks/imdb/results/01a.csv
34+
35+
cleanup sql_benchmarks/imdb/init/cleanup.sql
Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
name Q01b
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(mc.note) AS production_note,
15+
MIN(t.title) AS movie_title,
16+
MIN(t.production_year) AS movie_year
17+
FROM company_type AS ct,
18+
info_type AS it,
19+
movie_companies AS mc,
20+
movie_info_idx AS mi_idx,
21+
title AS t
22+
WHERE ct.kind = 'production companies'
23+
AND it.info = 'bottom 10 rank'
24+
AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
25+
AND t.production_year BETWEEN 2005 AND 2010
26+
AND ct.id = mc.company_type_id
27+
AND t.id = mc.movie_id
28+
AND t.id = mi_idx.movie_id
29+
AND mc.movie_id = mi_idx.movie_id
30+
AND it.id = mi_idx.info_type_id;
31+
32+
result sql_benchmarks/imdb/results/01b.csv
33+
34+
cleanup sql_benchmarks/imdb/init/cleanup.sql
Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
name Q01c
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(mc.note) AS production_note,
15+
MIN(t.title) AS movie_title,
16+
MIN(t.production_year) AS movie_year
17+
FROM company_type AS ct,
18+
info_type AS it,
19+
movie_companies AS mc,
20+
movie_info_idx AS mi_idx,
21+
title AS t
22+
WHERE ct.kind = 'production companies'
23+
AND it.info = 'top 250 rank'
24+
AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
25+
AND (mc.note LIKE '%(co-production)%')
26+
AND t.production_year >2010
27+
AND ct.id = mc.company_type_id
28+
AND t.id = mc.movie_id
29+
AND t.id = mi_idx.movie_id
30+
AND mc.movie_id = mi_idx.movie_id
31+
AND it.id = mi_idx.info_type_id;
32+
33+
result sql_benchmarks/imdb/results/01c.csv
34+
35+
cleanup sql_benchmarks/imdb/init/cleanup.sql
Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
name Q01d
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(mc.note) AS production_note,
15+
MIN(t.title) AS movie_title,
16+
MIN(t.production_year) AS movie_year
17+
FROM company_type AS ct,
18+
info_type AS it,
19+
movie_companies AS mc,
20+
movie_info_idx AS mi_idx,
21+
title AS t
22+
WHERE ct.kind = 'production companies'
23+
AND it.info = 'bottom 10 rank'
24+
AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
25+
AND t.production_year >2000
26+
AND ct.id = mc.company_type_id
27+
AND t.id = mc.movie_id
28+
AND t.id = mi_idx.movie_id
29+
AND mc.movie_id = mi_idx.movie_id
30+
AND it.id = mi_idx.info_type_id;
31+
32+
result sql_benchmarks/imdb/results/01d.csv
33+
34+
cleanup sql_benchmarks/imdb/init/cleanup.sql
Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
name Q02a
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(t.title) AS movie_title
15+
FROM company_name AS cn,
16+
keyword AS k,
17+
movie_companies AS mc,
18+
movie_keyword AS mk,
19+
title AS t
20+
WHERE cn.country_code ='[de]'
21+
AND k.keyword ='character-name-in-title'
22+
AND cn.id = mc.company_id
23+
AND mc.movie_id = t.id
24+
AND t.id = mk.movie_id
25+
AND mk.keyword_id = k.id
26+
AND mc.movie_id = mk.movie_id;
27+
28+
result sql_benchmarks/imdb/results/02a.csv
29+
30+
cleanup sql_benchmarks/imdb/init/cleanup.sql
Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
name Q02b
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(t.title) AS movie_title
15+
FROM company_name AS cn,
16+
keyword AS k,
17+
movie_companies AS mc,
18+
movie_keyword AS mk,
19+
title AS t
20+
WHERE cn.country_code ='[nl]'
21+
AND k.keyword ='character-name-in-title'
22+
AND cn.id = mc.company_id
23+
AND mc.movie_id = t.id
24+
AND t.id = mk.movie_id
25+
AND mk.keyword_id = k.id
26+
AND mc.movie_id = mk.movie_id;
27+
28+
result sql_benchmarks/imdb/results/02b.csv
29+
30+
cleanup sql_benchmarks/imdb/init/cleanup.sql
Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
name Q02c
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(t.title) AS movie_title
15+
FROM company_name AS cn,
16+
keyword AS k,
17+
movie_companies AS mc,
18+
movie_keyword AS mk,
19+
title AS t
20+
WHERE cn.country_code ='[sm]'
21+
AND k.keyword ='character-name-in-title'
22+
AND cn.id = mc.company_id
23+
AND mc.movie_id = t.id
24+
AND t.id = mk.movie_id
25+
AND mk.keyword_id = k.id
26+
AND mc.movie_id = mk.movie_id;
27+
28+
result sql_benchmarks/imdb/results/02c.csv
29+
30+
cleanup sql_benchmarks/imdb/init/cleanup.sql
Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
name Q02d
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(t.title) AS movie_title
15+
FROM company_name AS cn,
16+
keyword AS k,
17+
movie_companies AS mc,
18+
movie_keyword AS mk,
19+
title AS t
20+
WHERE cn.country_code ='[us]'
21+
AND k.keyword ='character-name-in-title'
22+
AND cn.id = mc.company_id
23+
AND mc.movie_id = t.id
24+
AND t.id = mk.movie_id
25+
AND mk.keyword_id = k.id
26+
AND mc.movie_id = mk.movie_id;
27+
28+
result sql_benchmarks/imdb/results/02d.csv
29+
30+
cleanup sql_benchmarks/imdb/init/cleanup.sql
Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
name Q03a
2+
group imdb
3+
4+
echo Loading imdb tables
5+
6+
load sql_benchmarks/imdb/init/load_${IMDB_FILE_TYPE:-parquet}.sql
7+
8+
assert I
9+
SELECT COUNT(*) > 0 from title;
10+
----
11+
true
12+
13+
run
14+
SELECT MIN(t.title) AS movie_title
15+
FROM keyword AS k,
16+
movie_info AS mi,
17+
movie_keyword AS mk,
18+
title AS t
19+
WHERE k.keyword LIKE '%sequel%'
20+
AND mi.info IN ('Sweden',
21+
'Norway',
22+
'Germany',
23+
'Denmark',
24+
'Swedish',
25+
'Denish',
26+
'Norwegian',
27+
'German')
28+
AND t.production_year > 2005
29+
AND t.id = mi.movie_id
30+
AND t.id = mk.movie_id
31+
AND mk.movie_id = mi.movie_id
32+
AND k.id = mk.keyword_id;
33+
34+
result sql_benchmarks/imdb/results/03a.csv
35+
36+
cleanup sql_benchmarks/imdb/init/cleanup.sql

0 commit comments

Comments
 (0)