Source code for apache_beam.testing.benchmarks.tpcds.tpcds_queries

#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#    http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

"""TPC-DS SQL query loader and table identifier extraction."""

import os
import re
from typing import Dict
from typing import List
from typing import Optional
from typing import Set

from apache_beam.testing.benchmarks.tpcds.tpcds_schemas import TPCDS_BUILD_DIR
from apache_beam.testing.benchmarks.tpcds.tpcds_schemas import get_all_table_names

TPCDS_QUERIES_DIR = os.path.join(TPCDS_BUILD_DIR, "queries")

# The 13 canonical multi-table TPC-DS star/snowflake aggregate queries.
CANONICAL_13_QUERIES: List[str] = [
    "3", "7", "13", "15", "19", "26", "42", "43", "48", "52", "55", "91", "96"
]

# TPC-DS queries supported by Beam Calcite SQL (returning > 0 rows).
# Together with KNOWN_FAILING_QUERIES, this partitions all 103 TPC-DS queries.
# yapf: disable
SUPPORTED_QUERIES: List[str] = [
    "1", "3", "4", "7", "9", "10", "11", "12", "15", "17",
    "19", "20", "21", "26", "31", "32", "33", "34", "35", "38",
    "40", "42", "43", "45", "46", "49", "50", "52", "53", "55",
    "56", "59", "60", "61", "63", "65", "66", "68", "69", "71",
    "73", "74", "75", "76", "79", "82", "84", "87", "88", "89",
    "90", "92", "93", "96", "97", "98", "99",
]
# yapf: enable

# Known failing TPC-DS queries on Beam Calcite SQL with their expected error substring.
# Used by `--check_regression` and `:sdks:python:apache_beam:testing:benchmarks:tpcds:checkAllQueries`
# to verify that all SUPPORTED_QUERIES succeed on PrismRunner and that the remaining known
# failing queries fail with their expected diagnostic without regressing.
# pylint: disable=line-too-long
KNOWN_FAILING_QUERIES: Dict[str, str] = {
    "query2": "UnsupportedOperationException: Only support column reference or struct field access in conjunction clause",
    "query5": "Error while applying rule BeamBasicAggregationRule",
    "query6": "VolcanoPlanner timeout (correlated subquery + SINGLE_VALUE join permutation search)",
    "query8": "UnsupportedOperationException: Only support column reference or struct field access in conjunction clause",
    "query13": "UnsupportedOperationException: Operator OR is not supported in join condition",
    "query14a": "Error while applying rule BeamBasicAggregationRule",
    "query14b": "UnsupportedOperationException: Aggregator [SINGLE_VALUE] is not supported",
    "query16": "UnsupportedOperationException: Does not support COUNT DISTINCT",
    "query18": "Error while applying rule BeamBasicAggregationRule",
    "query22": "Error while applying rule BeamBasicAggregationRule",
    "query23a": "UnsupportedOperationException: Aggregator [SINGLE_VALUE] is not supported",
    "query23b": "UnsupportedOperationException: Aggregator [SINGLE_VALUE] is not supported",
    "query24a": "VolcanoPlanner timeout (>12-table join permutation search)",
    "query24b": "VolcanoPlanner timeout (>12-table join permutation search)",
    "query25": "Returned 0 rows",
    "query27": "Error while applying rule BeamBasicAggregationRule",
    "query28": "ParseException: Unable to parse query",
    "query29": "Returned 0 rows",
    "query30": "UnsupportedOperationException: Cross join with a residual (non-equi) condition is not supported yet.",
    "query36": "Error while applying rule BeamBasicAggregationRule",
    "query37": "Returned 0 rows",
    "query39a": "Returned 0 rows",
    "query39b": "Returned 0 rows",
    "query41": "ParseException: Unable to parse query",
    "query44": "UnsupportedOperationException: Aggregator [SINGLE_VALUE] is not supported",
    "query47": "IllegalArgumentException: DECIMAL NOT NULL is not nullable",
    "query48": "UnsupportedOperationException: Operator OR is not supported in join condition",
    "query51": "IllegalArgumentException: DECIMAL NOT NULL is not nullable",
    "query54": "UnsupportedOperationException: Aggregator [SINGLE_VALUE] is not supported",
    "query57": "Returned 0 rows",
    "query58": "UnsupportedOperationException: Aggregator [SINGLE_VALUE] is not supported",
    "query62": "Error while applying rule JoinPushExpressionsRule",
    "query64": "VolcanoPlanner timeout (36-table join permutation search)",
    "query67": "Error while applying rule BeamBasicAggregationRule",
    "query70": "Error while applying rule BeamBasicAggregationRule",
    "query72": "ParseException: Unable to parse query",
    "query77": "Error while applying rule BeamBasicAggregationRule",
    "query78": "CalcFn failed to evaluate",
    "query80": "Error while applying rule BeamBasicAggregationRule",
    "query81": "UnsupportedOperationException: Cross join with a residual (non-equi) condition is not supported yet.",
    "query83": "Error decoding input stream with coder",
    "query85": "Returned 0 rows",
    "query86": "Error while applying rule BeamBasicAggregationRule",
    "query91": "VolcanoPlanner timeout (7-table join permutation search)",
    "query94": "Error while applying rule JoinPushExpressionsRule",
    "query95": "Error while applying rule JoinPushExpressionsRule",
}
# pylint: enable=line-too-long

# Subset of KNOWN_FAILING_QUERIES where Calcite's VolcanoPlanner explores exponential join
# permutations (7 to 36 table joins) before failing with CannotPlanException.
PLANNER_TIMEOUT_QUERIES: Set[str] = {
    "query6", "query24a", "query24b", "query64", "query91"
}


def _require_queries_dir(queries_dir: Optional[str] = None) -> str:
  target_dir = queries_dir or TPCDS_QUERIES_DIR
  if not os.path.isdir(target_dir):
    raise RuntimeError(
        f"TPC-DS queries directory not found at {target_dir}. "
        "Running TPC-DS as a standalone apache-beam module is not supported; "
        "run via Gradle (:sdks:python:apache_beam:testing:benchmarks:tpcds:copyTpcdsResources) "
        "to populate tpcds/build.")
  return target_dir


[docs] def get_all_query_names() -> List[str]: """Returns all 103 TPC-DS query names (query1 .. query99 including a/b variants).""" names: List[str] = [] split_queries = {14, 23, 24, 39} for i in range(1, 100): if i in split_queries: names.append(f"query{i}a") names.append(f"query{i}b") else: names.append(f"query{i}") return names
[docs] def normalize_query_name(q: str) -> str: """Normalizes '3', '03', 'q03', 'query3' -> 'query3'.""" cleaned = q.strip().lower() m = re.match(r"^(?:query|q)?0*(\d+[ab]?)$", cleaned) if m: return f"query{m.group(1)}" return cleaned
[docs] def parse_query_selection(queries_arg: str) -> List[str]: """Parses a --queries argument ('canonical13', 'supported', 'all', or comma-separated list).""" cleaned = queries_arg.strip().lower() if cleaned in ("canonical13", "canonical"): return [normalize_query_name(q) for q in CANONICAL_13_QUERIES] if cleaned == "supported": return [normalize_query_name(q) for q in SUPPORTED_QUERIES] if cleaned == "all": return get_all_query_names() all_valid = set(get_all_query_names()) result = [] for part in re.split(r"[\s,]+", queries_arg.strip()): if not part: continue qname = normalize_query_name(part) if qname not in all_valid: raise ValueError(f"Unsupported TPC-DS query name: {part!r} ({qname})") result.append(qname) return result
[docs] def read_query(query_name: str, queries_dir: Optional[str] = None) -> str: """Reads a TPC-DS SQL query by name (e.g. 'query3' or '3') from tpcds/build/queries.""" qname = normalize_query_name(query_name) target_dir = _require_queries_dir(queries_dir) candidate = os.path.join(target_dir, f"{qname}.sql") if not os.path.isfile(candidate): raise RuntimeError( f"Could not find SQL file for {qname} at {candidate}. " "Run via Gradle (:sdks:python:apache_beam:testing:benchmarks:tpcds:copyTpcdsResources)." ) with open(candidate, "r", encoding="utf-8") as f: return _strip_sql_comments(f.read())
def _strip_sql_comments(sql: str) -> str: lines = [] for line in sql.splitlines(): if line.lstrip().startswith("--"): continue lines.append(line) return "\n".join(lines).strip()
[docs] def get_query_tables(query_sql: str) -> List[str]: """Extracts the TPC-DS table names referenced in the given SQL query.""" tokens: Set[str] = set( re.findall(r"\b[a-zA-Z_][a-zA-Z0-9_]*\b", query_sql.lower())) return [t for t in get_all_table_names() if t.lower() in tokens]