CODEWITHSUNDEEP
google-bigquery
Renier Botha
Renier Botha

Reputation: 980

Populate an array in BigQuery using results from EXECUTE IMMEDIATE

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.

Upvotes: 1

Views: 2220

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

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);

Upvotes: 1

Related Questions