如何从Google Data Studio调用BigQuery存储过程并传递参数展示结果

我明白你的问题了——Data Studio确实不支持直接调用BigQuery存储过程，因为它的自定义查询要求返回可直接可视化的结果集，而存储过程主要是用来执行数据操作（比如建表、更新），不会直接输出数据。下面给你两个可行的解决方案，其中第二个更适合需要动态传递参数的场景：

方案一：使用计划查询预先生成结果表（适合固定参数场景） #

如果你的查询参数是固定的，不需要用户在报表中动态调整，可以先通过计划查询定时执行存储过程生成结果表，再让Data Studio读取这个表：

1. 修改存储过程支持参数
   先把你现有的存储过程改成带参数的版本，方便传入目标值：

   CREATE OR REPLACE PROCEDURE test.testproc(v_target STRING, v_target_column_name STRING)
   BEGIN
     DECLARE stop INT64 DEFAULT 30;
     DECLARE max_counter INT64;
     DECLARE min_counter INT64;
     DECLARE v_exists bool;
   
     CREATE OR REPLACE TABLE test.poc_sttm_resp AS 
     SELECT 
       ROW_NUMBER() OVER() as counter,
       'N' as flag,
       source,
       source_column_name,
       target,
       target_column_name 
     FROM test.test_sttm 
     WHERE target = v_target AND target_column_name = v_target_column_name;
   
     LOOP
       SET max_counter = (SELECT max(counter) FROM test.poc_sttm_resp);
       SET min_counter = (SELECT min(counter) FROM test.poc_sttm_resp WHERE flag = 'N');
       SET v_exists = EXISTS(
         SELECT s.source 
         FROM test.test_sttm s 
         INNER JOIN (
           SELECT source,source_column_name FROM test.poc_sttm_resp WHERE counter = min_counter 
         ) r 
         ON s.target = r.source AND s.target_column_name = r.source_column_name
       );
   
       IF stop = 0 OR min_counter IS NULL THEN LEAVE; END IF;
   
       IF v_exists THEN 
         INSERT INTO test.poc_sttm_resp 
         SELECT 
           ROW_NUMBER() OVER() + max_counter as counter,
           'N' as flag,
           s.source,
           s.source_column_name,
           s.target,
           s.target_column_name 
         FROM test.test_sttm s 
         INNER JOIN (
           SELECT source,source_column_name FROM test.poc_sttm_resp WHERE counter = (SELECT min(counter) FROM test.poc_sttm_resp WHERE flag = 'N')
         ) r 
         ON s.target = r.source AND s.target_column_name = r.source_column_name; 
       END IF;
   
       UPDATE test.poc_sttm_resp SET flag = 'Y' WHERE counter = min_counter;
       SET stop = stop - 1;
     END LOOP;
   END;
   

2. 创建计划查询执行存储过程
   在BigQuery中创建一个计划查询，执行存储过程并传入固定参数：

   CALL test.testproc('你的目标值', '你的目标列名');
   

   设置计划查询的执行频率（比如每小时、每天），确保结果表是最新的。

3. Data Studio连接结果表
   在Data Studio中添加BigQuery数据源，直接选择test.poc_sttm_resp表，即可可视化其中的数据。

方案二：将存储过程逻辑改为表值函数（推荐，支持动态参数） #

如果需要用户在Data Studio报表中动态选择参数并实时获取结果，推荐把递归逻辑改成表值函数——表值函数可以直接返回结果集，并且支持在Data Studio中传递参数：

1. 创建递归表值函数
   用递归CTE重写你的存储过程逻辑，生成一个直接返回结果的函数：

   CREATE OR REPLACE FUNCTION test.testfunc(v_target STRING, v_target_column_name STRING)
   RETURNS TABLE (
     counter INT64,
     flag STRING,
     source STRING,
     source_column_name STRING,
     target STRING,
     target_column_name STRING
   )
   LANGUAGE sql
   AS $$
   WITH RECURSIVE cte AS (
     -- 初始数据：匹配目标的记录
     SELECT 
       ROW_NUMBER() OVER() AS counter,
       'N' AS flag,
       source,
       source_column_name,
       target,
       target_column_name,
       1 AS depth  -- 控制递归深度，替代原存储过程的stop变量
     FROM test.test_sttm 
     WHERE target = v_target AND target_column_name = v_target_column_name
     
     UNION ALL
     
     -- 递归步骤：找到关联的源记录
     SELECT 
       (SELECT MAX(counter) FROM cte) + ROW_NUMBER() OVER() AS counter,
       'N' AS flag,
       s.source,
       s.source_column_name,
       s.target,
       s.target_column_name,
       c.depth + 1 AS depth
     FROM cte c
     INNER JOIN test.test_sttm s 
       ON s.target = c.source AND s.target_column_name = c.source_column_name
     WHERE c.depth < 30  -- 限制递归深度，对应原stop=30
   )
   SELECT 
     counter,
     -- 模拟原存储过程的flag逻辑：初始记录处理后标记为Y
     CASE WHEN (SELECT MIN(depth) FROM cte WHERE counter = c.counter) = 1 THEN 'Y' ELSE 'N' END AS flag,
     source,
     source_column_name,
     target,
     target_column_name
   FROM cte;
   $$;
   

2. Data Studio中调用函数并传递参数
   在Data Studio的自定义查询中，使用以下SQL调用函数，并添加参数：

   SELECT * FROM test.testfunc(@target_param, @target_col_param);
   

   然后在Data Studio中创建两个参数target_param和target_col_param，用户就可以在报表界面选择参数值，实时获取递归查询的结果了。

为什么直接调用存储过程会报错？ #

Data Studio的BigQuery连接器要求自定义查询必须返回一个可以直接用于可视化的结果集，而存储过程的核心是执行DDL/DML操作（比如建表、插入数据），不会直接输出数据，因此直接调用call functions.testproc();会触发报错。

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