How should I avoid name collisions between temporary tables in BigQuery sprocs?

Question

Sometimes it's useful to create a temporary table within a BigQuery stored procedure, in order to store an intermediate result.

However, I've run into an error due to a name collision between a temporary table in a sproc and one in the session calling it. For example the code

CREATE OR REPLACE PROCEDURE `example-project.example_dataset.example_sproc`()
BEGIN
  CREATE TEMP TABLE example_table AS SELECT 'example_data' AS example_column;
  -- Other commands using example_table would go here
  DROP TABLE example_table
END;

CREATE TEMP TABLE example_table AS SELECT 'other_example_data' AS other_example_column;
CALL `example-project.example_dataset.example_sproc`();

fails with the following error.

Already Exists: Table example-project:_scriptca02ec717db5bb7157d6ca3097152631de389011.example_table at [example-project.example_dataset.example_sproc:2:3]

I want to be able to call sprocs without worrying about the names of temporary tables used internally to them. Is there a way to make the temporary tables created within a sproc have a scope that prevents them from being accessed outside it? Or is there a better way to do the whole thing?

In some cases I could avoid the temporary table by using a WITH statement to create a CTE, which I believe would be local to the sproc. But there are performance differnces between CTEs and temporary tables, so I would like to have the option of using either.

Answer 1

You can create your table if not exists, the syntax is

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] TABLE [ IF NOT EXISTS ]
table_name
[(
  column[, ...]
)]
[DEFAULT COLLATE collate_specification]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]

See https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language

So, your code would be modified to

CREATE TEMP TABLE example_table IF NOT EXISTS AS SELECT 'other_example_data' AS other_example_column;
CALL `example-project.example_dataset.example_sproc`();

EDIT

If the table to be created would be located in a different schema, that can be solved by referring said schema in the create command, writing schema_name.table_name. Yet, if you do not have the necessary privileges to edit a stored procedure, then you can wrap a conditional around calling it, the condition would be looking like

IF EXISTS(SELECT 1 FROM schema.products_a WHERE product_id = target_product_id) THEN
    ...
END IF;