BigQuery存储过程中引用临时表报错求助

问题场景 #

在BigQuery存储过程中创建临时表tt_orders后，引用时出现两种报错：

• 未加数据集限定符：Invalid value: Table "tt_orders" must be qualified with a dataset (e.g. dataset.table). at [..get_order_report:121:17]
• 添加数据集限定符后：Not found: Table :.tt_orders was not found in location US at [..get_order_report:121:17]

存储过程执行流程：设置变量→创建临时表tt_orders→基于tt_orders创建临时表tt_Names→创建其余4个临时表→关联所有表返回报表结果。

出错的伪代码如下：

CREATE TEMP TABLE tt_Names AS (
    SELECT tmp.OrderID AS OrderId
        ,ROW_NUMBER()OVER (PARTITION BY d.OrderId ORDER BY dc.DateCreated) AS RowNum 
        ,dc.Name 
    FROM tt_orders tmp
        INNER JOIN `<dataset>.Table1` d ON tmp.OrderId=d.OrderId
    INNER JOIN `<dataset>.Table2` dc ON dc.SecondaryID = d.SecondaryID
        LEFT OUTER JOIN (SELECT ID FROM UNNEST(FilteredIds) AS ID) tc ON d.SecondaryId=tc.ID 
    WHERE tc.ID IS NOT NULL);

解决方法 #

BigQuery存储过程中引用会话临时表时，需要明确指定临时表类型，避免解析器混淆临时表与永久表，有两种可行方式：

方式1：使用TEMP关键字限定临时表 #

在引用临时表的FROM子句中添加TEMP关键字，明确告知解析器这是临时表：

CREATE TEMP TABLE tt_Names AS (
    SELECT tmp.OrderID AS OrderId
        ,ROW_NUMBER()OVER (PARTITION BY d.OrderId ORDER BY dc.DateCreated) AS RowNum 
        ,dc.Name 
    FROM TEMP tt_orders tmp
        INNER JOIN `<dataset>.Table1` d ON tmp.OrderId=d.OrderId
    INNER JOIN `<dataset>.Table2` dc ON dc.SecondaryID = d.SecondaryID
        LEFT OUTER JOIN (SELECT ID FROM UNNEST(FilteredIds) AS ID) tc ON d.SecondaryId=tc.ID 
    WHERE tc.ID IS NOT NULL);

方式2：使用session.前缀引用临时表 #

会话临时表的完整命名格式为session.<table_name>，直接使用该格式也可正确识别：

CREATE TEMP TABLE tt_Names AS (
    SELECT tmp.OrderID AS OrderId
        ,ROW_NUMBER()OVER (PARTITION BY d.OrderId ORDER BY dc.DateCreated) AS RowNum 
        ,dc.Name 
    FROM session.tt_orders tmp
        INNER JOIN `<dataset>.Table1` d ON tmp.OrderId=d.OrderId
    INNER JOIN `<dataset>.Table2` dc ON dc.SecondaryID = d.SecondaryID
        LEFT OUTER JOIN (SELECT ID FROM UNNEST(FilteredIds) AS ID) tc ON d.SecondaryId=tc.ID 
    WHERE tc.ID IS NOT NULL);

注意事项 #

• 确保tt_orders是通过CREATE TEMP TABLE语句在存储过程中提前创建的，且处于同一会话生命周期内；
• 存储过程内的临时表均为会话级，会话结束后会自动销毁，无需手动清理。

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