SQL查询可正常执行但存储过程报错：数据集未找到

问题根源 #

错误提示Dataset myporject-dev:streaming was not found in location EU的核心原因是：存储过程执行时默认使用其所在数据集（myporject-dev2）的位置（EU），但myporject-dev.streaming数据集实际位于其他位置。

单独执行SQL查询时，BigQuery会自动适配源数据集的位置；但存储过程是绑定到创建它的数据集位置执行的，当两个数据集位置不一致时，就会触发找不到数据集的错误。

解决方案 #

1. 先确认各数据集的实际位置 #

通过以下SQL查询涉及数据集的位置：

-- 查询myporject-dev.streaming的位置
SELECT location FROM `myporject-dev`.`INFORMATION_SCHEMA`.`SCHEMATA` WHERE schema_name = 'streaming';

-- 查询myporject-dev2的位置
SELECT location FROM `myporject-dev2`.`INFORMATION_SCHEMA`.`SCHEMATA` WHERE schema_name = 'myporject-dev2';

-- 查询dm_data_quality的位置
SELECT location FROM `myporject-dev2`.`INFORMATION_SCHEMA`.`SCHEMATA` WHERE schema_name = 'dm_data_quality';

2. 修正存储过程（三选一） #

方案一：创建存储过程时指定匹配位置

将存储过程的执行位置设置为与myporject-dev.streaming一致的位置：

CREATE OR REPLACE PROCEDURE myporject-dev2.scan_streaming_testing_table_raw()
OPTIONS(location='US') -- 替换为myporject-dev.streaming的实际位置
BEGIN
    INSERT INTO dm_data_quality.test_streaming_raw_tab_mon(monitor_triggered, detect_row_count, id, ingestion_timestamp, _uuid)
    SELECT CURRENT_TIMESTAMP() as monitor_triggered, count(1) as detect_row_count
        ,min(id) as id, min(ingestion_timestamp) as ingestion_timestamp, min(_uuid) as _uuid
    FROM `myporject-dev.streaming.testing_table_raw` as A
    WHERE ingestion_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 day) AND CURRENT_TIMESTAMP();
END;

方案二：在查询中显式指定源表位置

在源表名称后添加@位置后缀，强制指定数据源位置：

CREATE OR REPLACE PROCEDURE myporject-dev2.scan_streaming_testing_table_raw()
BEGIN
    INSERT INTO dm_data_quality.test_streaming_raw_tab_mon(monitor_triggered, detect_row_count, id, ingestion_timestamp, _uuid)
    SELECT CURRENT_TIMESTAMP() as monitor_triggered, count(1) as detect_row_count
        ,min(id) as id, min(ingestion_timestamp) as ingestion_timestamp, min(_uuid) as _uuid
    FROM `myporject-dev.streaming.testing_table_raw@US` as A -- 替换为实际位置
    WHERE ingestion_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 day) AND CURRENT_TIMESTAMP();
END;

方案三：统一所有数据集位置

若业务允许，将myporject-dev2、myporject-dev.streaming、dm_data_quality迁移到同一位置，从根源上避免位置不匹配问题。

内容的提问来源于stack exchange，提问作者JIE COLIN REN