TPC-H Data Vault Q7 — Volume Shipping

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

Value of goods shipped between nations (Data Vault)

Representative SQL

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

SELECT
  supp_nation,
  cust_nation,
  l_year,
  SUM(volume) AS revenue
FROM (
  SELECT
    n1.n_name AS supp_nation,
    n2.n_name AS cust_nation,
    EXTRACT(YEAR FROM sl.l_shipdate) AS l_year,
    sl.l_extendedprice * (
      1 - sl.l_discount
    ) AS volume
  FROM link_lineitem AS ll
  JOIN sat_lineitem AS sl
    ON ll.hk_lineitem_link = sl.hk_lineitem_link AND sl.load_end_dts IS NULL
  JOIN hub_supplier AS hs
    ON ll.hk_supplier = hs.hk_supplier
  JOIN link_supplier_nation AS lsn
    ON hs.hk_supplier = lsn.hk_supplier
  JOIN hub_nation AS hn1
    ON lsn.hk_nation = hn1.hk_nation
  JOIN sat_nation AS n1
    ON hn1.hk_nation = n1.hk_nation AND n1.load_end_dts IS NULL
  JOIN hub_order AS ho
    ON ll.hk_order = ho.hk_order
  JOIN link_order_customer AS loc
    ON ho.hk_order = loc.hk_order
  JOIN hub_customer AS hc
    ON loc.hk_customer = hc.hk_customer
  JOIN link_customer_nation AS lcn
    ON hc.hk_customer = lcn.hk_customer
  JOIN hub_nation AS hn2
    ON lcn.hk_nation = hn2.hk_nation
  JOIN sat_nation AS n2
    ON hn2.hk_nation = n2.hk_nation AND n2.load_end_dts IS NULL
  WHERE
    (
      (
        n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY'
      )
      OR (
        n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'
      )
    )
    AND sl.l_shipdate BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
) AS shipping
GROUP BY
  supp_nation,
  cust_nation,
  l_year
ORDER BY
  supp_nation,
  cust_nation,
  l_year

Representative DataFrame

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

def q7_expression_impl(ctx: DataFrameContext) -> Any:

    params = get_parameters("Q7")
    col, lit = ctx.col, ctx.lit
    nation1 = params.get("nation1", "FRANCE")
    nation2 = params.get("nation2", "GERMANY")

    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"))
    sn = _strip_audit_columns(ctx.get_table("sat_nation").filter(col("load_end_dts").is_null()))
    loc = _strip_audit_columns(ctx.get_table("link_order_customer"))
    lcn = _strip_audit_columns(ctx.get_table("link_customer_nation"))

    lineitem = ll.join(sl, left_on="hk_lineitem_link", right_on="hk_lineitem_link").filter(
        (col("l_shipdate") >= lit(date(1995, 1, 1))) & (col("l_shipdate") <= lit(date(1996, 12, 31)))
    )

    supp_nation = lsn.join(sn, left_on="hk_nation", right_on="hk_nation").rename(
        {"n_name": "supp_nation", "hk_nation": "hk_supp_nation"}
    )

    cust_nation_base = loc.join(lcn, left_on="hk_customer", right_on="hk_customer")
    cust_nation = cust_nation_base.join(sn, left_on="hk_nation", right_on="hk_nation").rename(
        {"n_name": "cust_nation", "hk_nation": "hk_cust_nation"}
    )

    df = (
        lineitem.join(supp_nation, left_on="hk_supplier", right_on="hk_supplier")
        .join(cust_nation, left_on="hk_order", right_on="hk_order")
        .filter(
            ((col("supp_nation") == lit(nation1)) & (col("cust_nation") == lit(nation2)))
            | ((col("supp_nation") == lit(nation2)) & (col("cust_nation") == lit(nation1)))
        )
    )

    result = (
        df.with_columns(col("l_shipdate").dt.year().alias("l_year"))
        .with_columns((col("l_extendedprice") * (lit(1) - col("l_discount"))).alias("revenue"))
        .group_by("supp_nation", "cust_nation", "l_year")
        .agg(
            col("revenue").sum().alias("revenue"),
        )
    )
    return result.sort("supp_nation", "cust_nation", "l_year")

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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