1

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.

Oscar Cunningham
  • 111
  • 3
  • This seems like a feature, not a bug -- if you use `example_table` both outside and inside the procedure it's highly likely you intended to share the data, and having `example_table` in the sproc shadow the outer declaration would just be confusing. For comparison, the way SQL Server handles temp table scope is a big mess depending on when a table was created and what schema it has that leads to quite unintuitive results. Your best bet is to ensure table names are reasonably descriptive so a clash is improbable; if you need guarantees, tack something like a (truncated) GUID onto the name. – Jeroen Mostert Feb 20 '23 at 12:19

1 Answers1

0

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;
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • 1
    The OP's question seems to deal with tables that have different schemas. Using `IF NOT EXISTS` would just suppress the initial error, but then cause failures (mysterious or otherwise) when the queries hit tables that don't contain the data they expect. (And even then the `IF NOT EXISTS` has to go *inside* the sproc, not the calling session.) – Jeroen Mostert Feb 20 '23 at 12:33
  • @JeroenMostert Right. And in the worse case I could be calling somebody else's sproc which I don't have permission to edit. – Oscar Cunningham Feb 20 '23 at 12:35
  • @JeroenMostert for me it was not clear from the question that we are dealing with different schemas. In that case, building a dynamic query in order to check whether a given table exists in a given schema makes sense. – Lajos Arpad Feb 20 '23 at 12:42
  • @JeroenMostert by the way, you can use the `IF NOT EXISTS` syntax even on other schemas, yet, the asker pointed out that sometimes he/she not necessarily has the privileges to change the stored procedures. – Lajos Arpad Feb 20 '23 at 12:45
  • To be clear here I'm using "table schema" to mean "table structure". If BigQuery has a "schema" feature that serves as a namespace for tables (I'm not very familiar with it), that's *not* what I mean. – Jeroen Mostert Feb 20 '23 at 12:51
  • @JeroenMostert I see. The question was about avoiding name collisions, so that's what I was focusing on. – Lajos Arpad Feb 20 '23 at 12:53