如何在BigQuery表函数中动态指定表名与过滤参数？

你的需求无法通过BigQuery表函数（TVF）实现，因为表函数要求逻辑是确定性的，不支持EXECUTE IMMEDIATE这类动态SQL操作，也不允许使用SET变量等过程化语句。正确的实现方式是使用BigQuery存储过程，以下是完整解决方案：

正确的存储过程代码 #

CREATE OR REPLACE PROCEDURE `my_dataset.my_procedure_name`(
  num_days INT64, 
  fqn_org_table STRING
)
BEGIN
  -- 拼接动态SQL，安全替换表名和参数
  DECLARE result_query STRING;
  SET result_query = FORMAT("""
    WITH report_cst_t AS (
        SELECT
            DATE(start) as day,
            entity_id,
            conn_sub_type
        FROM `%s` AS oa
        CROSS JOIN UNNEST(oa.connection_sub_type) AS conn_sub_type
        WHERE
            DATE(start) > DATE_SUB(CURRENT_DATE(), INTERVAL %d DAY)
            AND oa.entity_id IN ('my-very-long-id')
    ),
    cst AS (
        SELECT * FROM
            (SELECT day, entity_id, conn_sub_type FROM report_cst_t)
            PIVOT (COUNT(*) AS connection_sub_type FOR conn_sub_type IN ('cat1', 'cat2','cat3'))
    )
    SELECT
        cst.day,
        cst.entity_id,
        cst.connection_sub_type_cat1 AS cst_cat1,
        cst.connection_sub_type_cat2 AS cst_cat2,
        cst.connection_sub_type_cat3 AS cst_cat3
    FROM cst
    ORDER BY 1, 2 ASC
  """, fqn_org_table, num_days);

  -- 执行动态SQL并返回结果
  EXECUTE IMMEDIATE result_query;
END;

关键修改说明 #

1. 过程化结构：用BEGIN/END包裹所有逻辑，解决之前缺少结构的语法报错
2. 安全动态拼接：使用FORMAT函数替换表名和参数，避免SQL注入风险
3. 语法修正：修复原代码中PIVOT部分的错误引用（原代码错误使用report_cst_t.conn_sub_type，应直接用字段名conn_sub_type）
4. 参数传递：将num_days作为整数参数直接嵌入SQL，fqn_org_table替换为目标全限定表名

调用方式 #

直接执行存储过程即可获取结果：

CALL `my_dataset.my_procedure_name`(7, 'your-project.your-dataset.your-table');

替代方案：无存储过程的动态SQL调用 #

如果不需要复用逻辑，也可以直接在查询脚本中执行动态SQL：

DECLARE num_days INT64 DEFAULT 7;
DECLARE fqn_table STRING DEFAULT 'your-project.your-dataset.your-table';
DECLARE query_str STRING;

SET query_str = FORMAT("""
  WITH report_cst_t AS (
      SELECT DATE(start) as day, entity_id, conn_sub_type
      FROM `%s` AS oa
      CROSS JOIN UNNEST(oa.connection_sub_type) AS conn_sub_type
      WHERE DATE(start) > DATE_SUB(CURRENT_DATE(), INTERVAL %d DAY)
        AND oa.entity_id IN ('my-very-long-id')
  ),
  cst AS (
      SELECT * FROM (SELECT day, entity_id, conn_sub_type FROM report_cst_t)
      PIVOT (COUNT(*) AS connection_sub_type FOR conn_sub_type IN ('cat1', 'cat2','cat3'))
  )
  SELECT day, entity_id,
         connection_sub_type_cat1 AS cst_cat1,
         connection_sub_type_cat2 AS cst_cat2,
         connection_sub_type_cat3 AS cst_cat3
  FROM cst ORDER BY 1,2 ASC
""", fqn_table, num_days);

EXECUTE IMMEDIATE query_str;

内容的提问来源于stack exchange，提问作者TPPZ