Read Primitives — Basic Unpivot

Read Primitives benchmark · all Read Primitives queries

Unpivot part dimensions into rows

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  p_partkey,
  'p_size' AS dimension_name,
  CAST(p_size AS DECIMAL(15, 2)) AS dimension_value
FROM part
WHERE
  p_partkey <= 100
UNION ALL
SELECT
  p_partkey,
  'p_retailprice' AS dimension_name,
  p_retailprice AS dimension_value
FROM part
WHERE
  p_partkey <= 100
ORDER BY
  p_partkey,
  dimension_name

Representative DataFrame

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

def unpivot_basic_expression_impl(ctx: DataFrameContext) -> Any:
    part = ctx.get_table("part")
    col = ctx.col
    lit = ctx.lit

    filtered = part.filter(col("p_partkey") <= lit(100)).select(
        "p_partkey",
        col("p_size").cast(float).alias("p_size"),
        col("p_retailprice").alias("p_retailprice"),
    )

    return filtered.melt(
        id_vars=["p_partkey"],
        value_vars=["p_size", "p_retailprice"],
        variable_name="dimension_name",
        value_name="dimension_value",
    ).sort(["p_partkey", "dimension_name"])

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 ReadPrimitives

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('read_primitives', 'unpivot_basic', family="expression").source)  # or family="pandas"

Source

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