0

I have a procedure saved in a dataset (project.dataset_x.sp_test) and this procedure have statments that create tables in the same dataset of the procedure (project.dataset_x.d_test).

The goal is to change all the datasets dataset_x to dataset_y dynamically.

Example:

CHANGE

Create or replace procedure `project.dataset_x.sp_test`() 

BEGIN

Create or replace table `project.dataset_x.d_test` AS
Select 
1 a id,
'aaaa' as name
FROM `project.dataset_x.d_init`

END

TO

Create or replace procedure `project.dataset_y.sp_test`() 

BEGIN

Create or replace table `project.dataset_y.d_test` AS
Select 
1 a id,
'aaaa' as name
FROM `project.dataset_y.d_init`

END

Which is the best approach to create a scrip on Bigquery to change the datasets dynamically?

Joao Simoes
  • 39
  • 2
  • 1
    could you not have one of the procedure arguments be the desired dataset, then create a statement to be executed via an execute immediately? – Daniel Zagales Jan 12 '22 at 23:43

1 Answers1

2

Try the following:

CREATE OR REPLACE PROCEDURE project.independent_dataset.sp_test (dataset STRING)

BEGIN 

    EXECUTE IMMEDIATE 
    format("""
        CREATE OR REPLACE TABLE `project.%s.d_test` 
        AS 
        SELECT 1 AS id, 'aaaa' AS name FROM `elzagales.%s.d_init`"""
        , dataset, dataset);

END;

CALL project.independent_dataset.sp_test('dataset_y');

Using the above you do not have to create multiple procedures but can still achieve the desire results

Daniel Zagales
  • 2,948
  • 2
  • 5
  • 18
  • Thanks for the suggestion. Is it possible create a script without using the execute immediate? Because the procedure have more than one statment and if we use the execute i have to change all of them. I'm using a dynamic dataset to deploy procedures from a dev enviroment to a prd enviroment – Joao Simoes Jan 13 '22 at 09:04
  • You could potentially handle that by passing in a list of procedures you would like to promote up and within a for loop query the information schema to generate the appropriate ddl. With that said there are likely better ways to handle that process of promoting DDL through environments. – Daniel Zagales Jan 13 '22 at 13:37