Skip to content

Commit 73e3c2a

Browse files
authored
chore: Add primary key constraints for TPC-H, TPC-DS (apache#22646)
## Which issue does this PR close? - Closes apache#22595. ## Rationale for this change The TPC-DS and TPC-H specifications define primary keys, but we previously did not include those constraints when defining the TPC-DS and TPC-H schemas. Including the constraints enables the query optimizer to generate better plans (e.g., by leveraging FDs); it also makes the benchmark setup closer to a realistic TPC-DS/H benchmark run. To enable this, we need to fix `MemTable::load`: `MemTable` could be constructed `with_constraints`, but those constraints were not attached to the table returned by `MemTable::load`. There is some duplication here: we define two copies of the primary keys of both TPC-DS and TPC-H, because `benchmarks` and `test-utils` can't easily share code. This could be improved but I'll defer that for now. ## What changes are included in this PR? * Fix `MemTable::load` to include constraints on the newly loaded table * Refactor `MemTable::load` to use `collect_partitioned` * Add unit tests for new `MemTable::load` behavior * Add TPC-DS and TPC-H primary keys to `benchmarks` * Add TPC-DS and TPC-H primary keys to `test-utils` * Add TPC-H primary keys to SLT schema definitions ## Are these changes tested? New tests added for `MemTable::load` constraint behavior. SLT fixtures updated for change in TPC schemas and plans. ## Are there any user-facing changes? No.
1 parent d167450 commit 73e3c2a

33 files changed

Lines changed: 422 additions & 207 deletions

‎benchmarks/src/tpcds/run.rs‎

Lines changed: 64 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@ use std::sync::Arc;
2121

2222
use crate::util::{BenchmarkRun, CommonOpt, QueryResult, print_memory_stats};
2323

24+
use arrow::datatypes::Schema;
2425
use arrow::record_batch::RecordBatch;
2526
use arrow::util::pretty::{self, pretty_format_batches};
2627
use datafusion::datasource::file_format::parquet::ParquetFormat;
@@ -34,7 +35,7 @@ use datafusion::physical_plan::{collect, displayable};
3435
use datafusion::prelude::*;
3536
use datafusion_common::instant::Instant;
3637
use datafusion_common::utils::get_available_parallelism;
37-
use datafusion_common::{DEFAULT_PARQUET_EXTENSION, plan_err};
38+
use datafusion_common::{Constraint, Constraints, DEFAULT_PARQUET_EXTENSION, plan_err};
3839

3940
use clap::Args;
4041
use log::info;
@@ -71,6 +72,61 @@ pub const TPCDS_TABLES: &[&str] = &[
7172
"web_site",
7273
];
7374

75+
static TPCDS_PRIMARY_KEYS: &[(&str, &[&str])] = &[
76+
("call_center", &["cc_call_center_sk"]),
77+
("catalog_page", &["cp_catalog_page_sk"]),
78+
("catalog_returns", &["cr_item_sk", "cr_order_number"]),
79+
("catalog_sales", &["cs_item_sk", "cs_order_number"]),
80+
("customer", &["c_customer_sk"]),
81+
("customer_address", &["ca_address_sk"]),
82+
("customer_demographics", &["cd_demo_sk"]),
83+
("date_dim", &["d_date_sk"]),
84+
("household_demographics", &["hd_demo_sk"]),
85+
("income_band", &["ib_income_band_sk"]),
86+
(
87+
"inventory",
88+
&["inv_date_sk", "inv_item_sk", "inv_warehouse_sk"],
89+
),
90+
("item", &["i_item_sk"]),
91+
("promotion", &["p_promo_sk"]),
92+
("reason", &["r_reason_sk"]),
93+
("ship_mode", &["sm_ship_mode_sk"]),
94+
("store", &["s_store_sk"]),
95+
("store_returns", &["sr_item_sk", "sr_ticket_number"]),
96+
("store_sales", &["ss_item_sk", "ss_ticket_number"]),
97+
("time_dim", &["t_time_sk"]),
98+
("warehouse", &["w_warehouse_sk"]),
99+
("web_page", &["wp_web_page_sk"]),
100+
("web_returns", &["wr_item_sk", "wr_order_number"]),
101+
("web_sales", &["ws_item_sk", "ws_order_number"]),
102+
("web_site", &["web_site_sk"]),
103+
];
104+
105+
/// Get the constraints for a TPC-DS table. Only primary keys are returned;
106+
/// TPC-DS also defines foreign keys, but those are currently unsupported.
107+
fn table_constraints(table: &str, schema: &Schema) -> Constraints {
108+
let columns = TPCDS_PRIMARY_KEYS
109+
.iter()
110+
.find(|(name, _)| *name == table)
111+
.map(|(_, columns)| *columns)
112+
.unwrap_or_else(|| unimplemented!("unknown TPC-DS table: {table}"));
113+
114+
Constraints::new_unverified(vec![primary_key(schema, columns)])
115+
}
116+
117+
fn primary_key(schema: &Schema, column_names: &[&str]) -> Constraint {
118+
let indices = column_names
119+
.iter()
120+
.map(|column_name| {
121+
schema.index_of(column_name).unwrap_or_else(|_| {
122+
panic!("primary key column '{column_name}' not found in schema")
123+
})
124+
})
125+
.collect();
126+
127+
Constraint::PrimaryKey(indices)
128+
}
129+
74130
/// Get the SQL statements from the specified query file
75131
pub fn get_query_sql(base_query_path: &str, query: usize) -> Result<Vec<String>> {
76132
if query > 0 && query < 100 {
@@ -327,7 +383,9 @@ impl RunOpt {
327383
.with_file_extension(DEFAULT_PARQUET_EXTENSION)
328384
.with_target_partitions(target_partitions)
329385
.with_collect_stat(state.config().collect_statistics());
386+
330387
let schema = options.infer_schema(&state, &table_path).await?;
388+
let constraints = table_constraints(table, schema.as_ref());
331389

332390
if self.common.debug {
333391
println!(
@@ -347,9 +405,11 @@ impl RunOpt {
347405
.with_listing_options(options)
348406
.with_schema(schema);
349407

350-
Ok(Arc::new(ListingTable::try_new(config)?.with_cache(
351-
ctx.runtime_env().cache_manager.get_file_statistic_cache(),
352-
)))
408+
let provider = ListingTable::try_new(config)?
409+
.with_constraints(constraints)
410+
.with_cache(ctx.runtime_env().cache_manager.get_file_statistic_cache());
411+
412+
Ok(Arc::new(provider))
353413
}
354414

355415
fn iterations(&self) -> usize {

‎benchmarks/src/tpch/mod.rs‎

Lines changed: 37 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@
2020
use arrow::datatypes::SchemaBuilder;
2121
use datafusion::{
2222
arrow::datatypes::{DataType, Field, Schema},
23-
common::plan_err,
23+
common::{Constraint, Constraints, plan_err},
2424
error::Result,
2525
};
2626
use std::fs;
@@ -138,6 +138,42 @@ pub fn get_tpch_table_schema(table: &str) -> Schema {
138138
}
139139
}
140140

141+
static TPCH_PRIMARY_KEYS: &[(&str, &[&str])] = &[
142+
("region", &["r_regionkey"]),
143+
("nation", &["n_nationkey"]),
144+
("part", &["p_partkey"]),
145+
("supplier", &["s_suppkey"]),
146+
("partsupp", &["ps_partkey", "ps_suppkey"]),
147+
("customer", &["c_custkey"]),
148+
("orders", &["o_orderkey"]),
149+
("lineitem", &["l_orderkey", "l_linenumber"]),
150+
];
151+
152+
/// Get the constraints for a TPC-H table. Only primary keys are returned; TPC-H
153+
/// also defines foreign keys, but those are currently unsupported.
154+
fn table_constraints(table: &str, schema: &Schema) -> Constraints {
155+
let columns = TPCH_PRIMARY_KEYS
156+
.iter()
157+
.find(|(name, _)| *name == table)
158+
.map(|(_, columns)| *columns)
159+
.unwrap_or_else(|| unimplemented!("unknown TPC-H table: {table}"));
160+
161+
Constraints::new_unverified(vec![primary_key(schema, columns)])
162+
}
163+
164+
fn primary_key(schema: &Schema, column_names: &[&str]) -> Constraint {
165+
let indices = column_names
166+
.iter()
167+
.map(|column_name| {
168+
schema.index_of(column_name).unwrap_or_else(|_| {
169+
panic!("primary key column '{column_name}' not found in schema")
170+
})
171+
})
172+
.collect();
173+
174+
Constraint::PrimaryKey(indices)
175+
}
176+
141177
/// Get the SQL statements from the specified query file
142178
pub fn get_query_sql(query: usize) -> Result<Vec<String>> {
143179
get_query_sql_for_scale_factor(query, 1.0)

‎benchmarks/src/tpch/run.rs‎

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@ use std::sync::Arc;
2020

2121
use super::{
2222
TPCH_QUERY_END_ID, TPCH_QUERY_START_ID, TPCH_TABLES, get_query_sql_for_scale_factor,
23-
get_tbl_tpch_table_schema, get_tpch_table_schema,
23+
get_tbl_tpch_table_schema, get_tpch_table_schema, table_constraints,
2424
};
2525
use crate::util::{BenchmarkRun, CommonOpt, QueryResult, print_memory_stats};
2626

@@ -324,12 +324,15 @@ impl RunOpt {
324324
.with_file_extension(extension)
325325
.with_target_partitions(target_partitions)
326326
.with_collect_stat(state.config().collect_statistics());
327+
327328
let schema = match table_format {
328329
"parquet" => options.infer_schema(&state, &table_path).await?,
329330
"tbl" => Arc::new(get_tbl_tpch_table_schema(table)),
330331
"csv" => Arc::new(get_tpch_table_schema(table)),
331332
_ => unreachable!(),
332333
};
334+
let constraints = table_constraints(table, schema.as_ref());
335+
333336
let options = if self.sorted {
334337
let key_column_name = schema.fields()[0].name();
335338
options
@@ -342,9 +345,11 @@ impl RunOpt {
342345
.with_listing_options(options)
343346
.with_schema(schema);
344347

345-
Ok(Arc::new(ListingTable::try_new(config)?.with_cache(
346-
ctx.runtime_env().cache_manager.get_file_statistic_cache(),
347-
)))
348+
let provider = ListingTable::try_new(config)?
349+
.with_constraints(constraints)
350+
.with_cache(ctx.runtime_env().cache_manager.get_file_statistic_cache());
351+
352+
Ok(Arc::new(provider))
348353
}
349354

350355
fn iterations(&self) -> usize {

‎datafusion/catalog/src/memory/table.rs‎

Lines changed: 19 additions & 61 deletions
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,6 @@ use arrow::datatypes::{DataType, Field, Schema, SchemaRef};
3232
use arrow::record_batch::RecordBatch;
3333
use datafusion_common::error::Result;
3434
use datafusion_common::{Constraints, DFSchema, SchemaExt, not_impl_err, plan_err};
35-
use datafusion_common_runtime::JoinSet;
3635
use datafusion_datasource::memory::{MemSink, MemorySourceConfig};
3736
use datafusion_datasource::sink::DataSinkExec;
3837
use datafusion_datasource::source::DataSourceExec;
@@ -44,13 +43,12 @@ use datafusion_physical_expr::{
4443
use datafusion_physical_plan::repartition::RepartitionExec;
4544
use datafusion_physical_plan::stream::RecordBatchStreamAdapter;
4645
use datafusion_physical_plan::{
47-
DisplayAs, DisplayFormatType, ExecutionPlan, ExecutionPlanProperties, Partitioning,
48-
PlanProperties, common,
46+
DisplayAs, DisplayFormatType, ExecutionPlan, Partitioning, PlanProperties,
47+
collect_partitioned,
4948
};
5049
use datafusion_session::Session;
5150

5251
use async_trait::async_trait;
53-
use futures::StreamExt;
5452
use log::debug;
5553
use parking_lot::Mutex;
5654
use tokio::sync::RwLock;
@@ -145,68 +143,28 @@ impl MemTable {
145143
state: &dyn Session,
146144
) -> Result<Self> {
147145
let schema = t.schema();
148-
let constraints = t.constraints();
149-
let exec = t.scan(state, None, &[], None).await?;
150-
let partition_count = exec.output_partitioning().partition_count();
151-
152-
let mut join_set = JoinSet::new();
153-
154-
for part_idx in 0..partition_count {
155-
let task = state.task_ctx();
156-
let exec = Arc::clone(&exec);
157-
join_set.spawn(async move {
158-
let stream = exec.execute(part_idx, task)?;
159-
common::collect(stream).await
160-
});
161-
}
162-
163-
let mut data: Vec<Vec<RecordBatch>> =
164-
Vec::with_capacity(exec.output_partitioning().partition_count());
165-
166-
while let Some(result) = join_set.join_next().await {
167-
match result {
168-
Ok(res) => data.push(res?),
169-
Err(e) => {
170-
if e.is_panic() {
171-
std::panic::resume_unwind(e.into_panic());
172-
} else {
173-
unreachable!();
174-
}
175-
}
176-
}
177-
}
146+
let constraints = t.constraints().cloned().unwrap_or_default();
178147

179-
let mut exec = DataSourceExec::new(Arc::new(MemorySourceConfig::try_new(
180-
&data,
181-
Arc::clone(&schema),
182-
None,
183-
)?));
184-
if let Some(cons) = constraints {
185-
exec = exec.with_constraints(cons.clone());
186-
}
187-
188-
if let Some(num_partitions) = output_partitions {
148+
let exec = t.scan(state, None, &[], None).await?;
149+
let data = collect_partitioned(exec, state.task_ctx()).await?;
150+
151+
// Optionally repartition the collected batches.
152+
let data = if let Some(num_partitions) = output_partitions {
153+
let source = DataSourceExec::new(Arc::new(MemorySourceConfig::try_new(
154+
&data,
155+
Arc::clone(&schema),
156+
None,
157+
)?));
189158
let exec = RepartitionExec::try_new(
190-
Arc::new(exec),
159+
Arc::new(source),
191160
Partitioning::RoundRobinBatch(num_partitions),
192161
)?;
162+
collect_partitioned(Arc::new(exec), state.task_ctx()).await?
163+
} else {
164+
data
165+
};
193166

194-
// execute and collect results
195-
let mut output_partitions = vec![];
196-
for i in 0..exec.properties().output_partitioning().partition_count() {
197-
// execute this *output* partition and collect all batches
198-
let task_ctx = state.task_ctx();
199-
let mut stream = exec.execute(i, task_ctx)?;
200-
let mut batches = vec![];
201-
while let Some(result) = stream.next().await {
202-
batches.push(result?);
203-
}
204-
output_partitions.push(batches);
205-
}
206-
207-
return MemTable::try_new(Arc::clone(&schema), output_partitions);
208-
}
209-
MemTable::try_new(Arc::clone(&schema), data)
167+
MemTable::try_new(schema, data).map(|table| table.with_constraints(constraints))
210168
}
211169
}
212170

‎datafusion/core/benches/sql_planner.rs‎

Lines changed: 13 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -133,15 +133,23 @@ fn create_context() -> SessionContext {
133133

134134
/// Register the table definitions as a MemTable with the context and return the
135135
/// context
136-
#[expect(clippy::needless_pass_by_value)]
137136
fn register_defs(ctx: SessionContext, defs: Vec<TableDef>) -> SessionContext {
138-
defs.iter().for_each(|TableDef { name, schema }| {
137+
for TableDef {
138+
name,
139+
schema,
140+
constraints,
141+
} in defs
142+
{
139143
ctx.register_table(
140-
name,
141-
Arc::new(MemTable::try_new(Arc::new(schema.clone()), vec![vec![]]).unwrap()),
144+
&name,
145+
Arc::new(
146+
MemTable::try_new(Arc::new(schema), vec![vec![]])
147+
.unwrap()
148+
.with_constraints(constraints),
149+
),
142150
)
143151
.unwrap();
144-
});
152+
}
145153
ctx
146154
}
147155

0 commit comments

Comments
 (0)