Populate an array in BigQuery using results from EXECUTE IMMEDIATE

Question

I'm using BigQuery's DynamicSQL to dynamically construct queries so that I can re-use code.

In my use case, I'm trying to build a re-usable SQL script to get meta data of all tables in a project.

Where I'm getting stuck is trying to populate an array with the results of an EXECUTE IMMEDIATE command.

Here is a simple snippet which illustrates my problem:

DECLARE project_name string;
DECLARE schema_query string;
DECLARE schemas ARRAY<string>;


SET project_name = 'my_project';

-- Get all the schemas in a project 
set schema_query = format("""
select schema_name from `%s.INFORMATION_SCHEMA.SCHEMATA`""", 
project_name
);

-- Add the schemas to an array
-- THIS COMMAND FAILS
set schemas = ARRAY(execute immediate schema_query)

One way to get the desired result, but which is not dynamic, is:

SET schemas = ARRAY(select schema_name from `my_project.INFORMATION_SCHEMA.SCHEMATA`);

So I want to be able to pass project_name as a parameter and then run the dynamic sql query, which returns a single column of type string, and save the results in an array of strings.

Mikhail Berlyant · Accepted Answer · 2022-07-19 18:42:25Z

1

Consider below approach

declare project string; 
declare schemas array<string>; 
set project = 'my_project';

execute immediate 'select array_agg(schema_name) from `' || project || '.INFORMATION_SCHEMA.SCHEMATA`' into schemas;

select * from unnest(schemas);

answered Jul 19, 2022 at 18:42

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Renier Botha Over a year ago

Thank you @Mikhail ! The array_agg method combined with the execute immediate .. into .. approach worked.

Collectives™ on Stack Overflow

Populate an array in BigQuery using results from EXECUTE IMMEDIATE

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related