TPC-H Data Vault Q5 — Local Supplier Volume

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

Revenue from orders in same nation within region (Data Vault)

Representative SQL

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

SELECT
  sn.n_name,
  SUM(sl.l_extendedprice * (
    1 - sl.l_discount
  )) AS revenue
FROM hub_region AS hr
JOIN sat_region AS sr
  ON hr.hk_region = sr.hk_region AND sr.load_end_dts IS NULL
JOIN link_nation_region AS lnr
  ON hr.hk_region = lnr.hk_region
JOIN hub_nation AS hn
  ON lnr.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_customer_nation AS lcn
  ON hn.hk_nation = lcn.hk_nation
JOIN hub_customer AS hc
  ON lcn.hk_customer = hc.hk_customer
JOIN link_order_customer AS loc
  ON hc.hk_customer = loc.hk_customer
JOIN hub_order AS ho
  ON loc.hk_order = ho.hk_order
JOIN sat_order AS so
  ON ho.hk_order = so.hk_order AND so.load_end_dts IS NULL
JOIN link_lineitem AS ll
  ON ho.hk_order = ll.hk_order
JOIN sat_lineitem AS sl
  ON ll.hk_lineitem_link = sl.hk_lineitem_link AND sl.load_end_dts IS NULL
JOIN link_supplier_nation AS lsn
  ON hn.hk_nation = lsn.hk_nation
JOIN hub_supplier AS hs
  ON lsn.hk_supplier = hs.hk_supplier AND ll.hk_supplier = hs.hk_supplier
WHERE
  sr.r_name = 'ASIA'
  AND so.o_orderdate >= CAST('1994-01-01' AS DATE)
  AND so.o_orderdate < CAST('1994-01-01' AS DATE) + INTERVAL '1' YEAR
GROUP BY
  sn.n_name
ORDER BY
  revenue DESC

Representative DataFrame

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

def q5_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q5")
    col, lit = ctx.col, ctx.lit
    region = params.get("region", "ASIA")
    start_date = params.get("start_date", date(1994, 1, 1))
    end_date = params.get("end_date", date(1995, 1, 1))

    sr = _strip_audit_columns(ctx.get_table("sat_region").filter(col("load_end_dts").is_null()))
    lnr = _strip_audit_columns(ctx.get_table("link_nation_region"))
    sn = _strip_audit_columns(ctx.get_table("sat_nation").filter(col("load_end_dts").is_null()))
    lcn = _strip_audit_columns(ctx.get_table("link_customer_nation"))
    loc = _strip_audit_columns(ctx.get_table("link_order_customer"))
    so = _strip_audit_columns(ctx.get_table("sat_order").filter(col("load_end_dts").is_null()))
    ll = _strip_audit_columns(ctx.get_table("link_lineitem"))
    sl = _strip_audit_columns(ctx.get_table("sat_lineitem").filter(col("load_end_dts").is_null()))
    lsn = _strip_audit_columns(ctx.get_table("link_supplier_nation"))

    nations = (
        sr.filter(col("r_name") == lit(region))
        .join(lnr, left_on="hk_region", right_on="hk_region")
        .join(sn, left_on="hk_nation", right_on="hk_nation")
    )

    cust_orders = (
        nations.join(lcn, left_on="hk_nation", right_on="hk_nation")
        .join(loc, left_on="hk_customer", right_on="hk_customer")
        .join(so, left_on="hk_order", right_on="hk_order")
        .filter((col("o_orderdate") >= lit(start_date)) & (col("o_orderdate") < lit(end_date)))
        .join(ll, left_on="hk_order", right_on="hk_order")
        .join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link")
    )

    df = cust_orders.join(lsn, left_on="hk_nation", right_on="hk_nation", suffix="_lsn").filter(
        col("hk_supplier") == col("hk_supplier_lsn")
    )

    df = df.with_columns((col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("revenue"))
    result = df.group_by("n_name").agg(
        col("revenue").sum().alias("revenue"),
    )
    return result.sort([("revenue", "desc")])

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[5])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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