AMPLab Q3a — Document Analysis

AMPLab benchmark · all AMPLab queries

Text analysis on documents with keyword matching via CASE WHEN

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  url,
  LENGTH(contents) AS content_length,
  CASE WHEN contents LIKE '%web%' THEN 1 ELSE 0 END AS has_keyword1,
  CASE WHEN contents LIKE '%data%' THEN 1 ELSE 0 END AS has_keyword2
FROM documents
WHERE
  LENGTH(contents) > 1000
ORDER BY
  content_length DESC
LIMIT 100

Representative DataFrame

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

def q3a_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters("Q3a")
    keyword1 = params.get("keyword1", "web")
    keyword2 = params.get("keyword2", "data")
    min_content_length = params.get("min_content_length", 1000)
    limit_rows = params.get("limit_rows", 100)

    documents = ctx.get_table("documents")
    col = ctx.col
    lit = ctx.lit

    return (
        documents.with_columns(col("contents").str.len_chars().alias("content_length"))
        .filter(col("content_length") > lit(min_content_length))
        .with_columns(
            ctx.when(col("contents").str.contains(keyword1)).then(lit(1)).otherwise(lit(0)).alias("has_keyword1"),
            ctx.when(col("contents").str.contains(keyword2)).then(lit(1)).otherwise(lit(0)).alias("has_keyword2"),
        )
        .select("url", "content_length", "has_keyword1", "has_keyword2")
        .sort("content_length", descending=True)
        .limit(limit_rows)
    )

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 AMPLab

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('amplab', '3a', family="expression").source)  # or family="pandas"

Source

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