如何避免BigQuery存储过程中临时表的命名冲突？

一、使用存储过程专属的本地临时表 #

BigQuery支持创建作用域仅限存储过程内部的本地临时表，语法上只需在表名前加#前缀。这种表与会话级临时表完全隔离，不会产生命名冲突，且存储过程执行结束后会自动清理，无需手动删除。

修改后的存储过程代码示例：

CREATE OR REPLACE PROCEDURE `example-project.example_dataset.example_sproc`()
BEGIN
  -- 创建存储过程专属本地临时表
  CREATE TEMP TABLE #example_table AS SELECT 'example_data' AS example_column;
  -- 后续业务逻辑使用该临时表
  SELECT * FROM #example_table;
  -- 无需手动DROP，存储过程结束后自动销毁
END;

-- 会话级临时表正常创建
CREATE TEMP TABLE example_table AS SELECT 'other_example_data' AS other_example_column;
-- 调用存储过程不再触发命名冲突错误
CALL `example-project.example_dataset.example_sproc`();

二、用CREATE OR REPLACE覆盖（需注意外部表数据保留） #

如果存储过程内部的临时表不需要与外部会话表共存，可在存储过程内使用CREATE OR REPLACE TEMP TABLE替代CREATE TEMP TABLE。该方式会先替换同名临时表，适合临时表仅在存储过程内使用的场景：

CREATE OR REPLACE PROCEDURE `example-project.example_dataset.example_sproc`()
BEGIN
  CREATE OR REPLACE TEMP TABLE example_table AS SELECT 'example_data' AS example_column;
  -- 执行临时表相关操作
  DROP TABLE example_table;
END;

注意：若外部会话的example_table在存储过程调用后仍需使用，该方法会导致原有数据丢失，需谨慎选用。

三、动态生成唯一临时表名 #

通过生成唯一标识符（如结合GENERATE_UUID()）创建临时表，彻底避免命名冲突。该方式灵活性高，但代码相对复杂：

CREATE OR REPLACE PROCEDURE `example-project.example_dataset.example_sproc`()
BEGIN
  DECLARE temp_table_name STRING;
  -- 生成无连字符的UUID作为唯一表名前缀
  SET temp_table_name = CONCAT('temp_', REPLACE(GENERATE_UUID(), '-', ''));
  -- 动态创建临时表
  EXECUTE IMMEDIATE CONCAT('CREATE TEMP TABLE `', temp_table_name, '` AS SELECT ''example_data'' AS example_column;');
  -- 动态使用临时表
  EXECUTE IMMEDIATE CONCAT('SELECT * FROM `', temp_table_name, '`;');
  -- 动态清理临时表
  EXECUTE IMMEDIATE CONCAT('DROP TABLE `', temp_table_name, '`;');
END;

四、场景化选择CTE或临时表 #

CTE的作用域天然局限于存储过程内部，适合数据量较小、逻辑简单的场景；当数据量较大或需要多次复用中间结果时，优先选择存储过程专属的本地临时表（#前缀），既能保证隔离性，又保留临时表的性能优势。

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