TPC-H Data Vault Q2 — Minimum Cost Supplier

TPC-H Data Vault benchmark · all TPC-H Data Vault queries

Find supplier with minimum cost for parts in region (Data Vault)

Representative SQL

Rendered in the benchmark’s native SQL with default parameters (no dialect translation available).

SELECT
  ss.s_acctbal,
  ss.s_name,
  sn.n_name,
  hp.p_partkey,
  sp.p_mfgr,
  ss.s_address,
  ss.s_phone,
  ss.s_comment
FROM hub_part AS hp
JOIN sat_part AS sp
  ON hp.hk_part = sp.hk_part AND sp.load_end_dts IS NULL
JOIN link_part_supplier AS lps
  ON hp.hk_part = lps.hk_part
JOIN hub_supplier AS hs
  ON lps.hk_supplier = hs.hk_supplier
JOIN sat_supplier AS ss
  ON hs.hk_supplier = ss.hk_supplier AND ss.load_end_dts IS NULL
JOIN sat_partsupp AS sps
  ON lps.hk_part_supplier = sps.hk_part_supplier AND sps.load_end_dts IS NULL
JOIN link_supplier_nation AS lsn
  ON hs.hk_supplier = lsn.hk_supplier
JOIN hub_nation AS hn
  ON lsn.hk_nation = hn.hk_nation
JOIN sat_nation AS sn
  ON hn.hk_nation = sn.hk_nation AND sn.load_end_dts IS NULL
JOIN link_nation_region AS lnr
  ON hn.hk_nation = lnr.hk_nation
JOIN hub_region AS hr
  ON lnr.hk_region = hr.hk_region
JOIN sat_region AS sr
  ON hr.hk_region = sr.hk_region AND sr.load_end_dts IS NULL
WHERE
  sp.p_size = 15
  AND sp.p_type LIKE '%BRASS'
  AND sr.r_name = 'EUROPE'
  AND sps.ps_supplycost = (
    SELECT
      MIN(sps2.ps_supplycost)
    FROM link_part_supplier AS lps2
    JOIN sat_partsupp AS sps2
      ON lps2.hk_part_supplier = sps2.hk_part_supplier AND sps2.load_end_dts IS NULL
    JOIN hub_supplier AS hs2
      ON lps2.hk_supplier = hs2.hk_supplier
    JOIN link_supplier_nation AS lsn2
      ON hs2.hk_supplier = lsn2.hk_supplier
    JOIN hub_nation AS hn2
      ON lsn2.hk_nation = hn2.hk_nation
    JOIN link_nation_region AS lnr2
      ON hn2.hk_nation = lnr2.hk_nation
    JOIN hub_region AS hr2
      ON lnr2.hk_region = hr2.hk_region
    JOIN sat_region AS sr2
      ON hr2.hk_region = sr2.hk_region AND sr2.load_end_dts IS NULL
    WHERE
      lps2.hk_part = hp.hk_part AND sr2.r_name = 'EUROPE'
  )
ORDER BY
  ss.s_acctbal DESC,
  sn.n_name,
  ss.s_name,
  hp.p_partkey
LIMIT 100

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def q2_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q2")
    col, lit = ctx.col, ctx.lit
    size = params.get("size", 15)
    type_suffix = params.get("type_suffix", "BRASS")
    region = params.get("region", "EUROPE")

    hp = _strip_audit_columns(ctx.get_table("hub_part"))
    sp = _strip_audit_columns(ctx.get_table("sat_part").filter(col("load_end_dts").is_null()))
    lps = _strip_audit_columns(ctx.get_table("link_part_supplier"))
    hs = _strip_audit_columns(ctx.get_table("hub_supplier"))
    ss = _strip_audit_columns(ctx.get_table("sat_supplier").filter(col("load_end_dts").is_null()))
    sps = _strip_audit_columns(ctx.get_table("sat_partsupp").filter(col("load_end_dts").is_null()))
    lsn = _strip_audit_columns(ctx.get_table("link_supplier_nation"))
    hn = _strip_audit_columns(ctx.get_table("hub_nation"))
    sn = _strip_audit_columns(ctx.get_table("sat_nation").filter(col("load_end_dts").is_null()))
    lnr = _strip_audit_columns(ctx.get_table("link_nation_region"))
    sr = _strip_audit_columns(ctx.get_table("sat_region").filter(col("load_end_dts").is_null()))

    supplier_region = (
        hs.join(ss, left_on="hk_supplier", right_on="hk_supplier")
        .join(lsn, left_on="hk_supplier", right_on="hk_supplier")
        .join(hn, left_on="hk_nation", right_on="hk_nation")
        .join(sn, left_on="hk_nation", right_on="hk_nation")
        .join(lnr, left_on="hk_nation", right_on="hk_nation")
        .join(sr, left_on="hk_region", right_on="hk_region")
        .filter(col("r_name") == lit(region))
    )

    part_supplier = (
        hp.join(sp, left_on="hk_part", right_on="hk_part")
        .filter((col("p_size") == lit(size)) & col("p_type").str.ends_with(type_suffix))
        .join(lps, left_on="hk_part", right_on="hk_part")
        .join(sps, left_on="hk_part_supplier", right_on="hk_part_supplier")
        .join(supplier_region, left_on="hk_supplier", right_on="hk_supplier")
    )

    min_cost = part_supplier.group_by("p_partkey").agg(
        col("ps_supplycost").min().alias("min_cost"),
    )

    return (
        part_supplier.join(min_cost, left_on="p_partkey", right_on="p_partkey")
        .filter(col("ps_supplycost") == col("min_cost"))
        .select("s_acctbal", "s_name", "n_name", "p_partkey", "p_mfgr", "s_address", "s_phone", "s_comment")
        .sort([("s_acctbal", "desc"), ("n_name", "asc"), ("s_name", "asc"), ("p_partkey", "asc")])
        .limit(100)
    )

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import DataVault

bench = DataVault(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries[2])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('datavault', '2', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/datavault/queries.py