Does Bigquery Support "execute Immediate" Command To Run Dynamic Query?

I can code like this in Oracle to create tables dynamically using 'execute immediate 'sql query..'' command. create or replace function make_a_table1(p_table_name varchar2, p

Solution 1:

Yes, BigQuery supports EXECUTE IMMEDIATE command to run dynamic query.

DECLARE p_dataset_name STRING DEFAULT'mydataset';
DECLARE p_table_name STRING DEFAULT'demo';
DECLARE p_column_name STRING DEFAULT'c1';
DECLARE p_data_type STRING DEFAULT'string';
DECLARE sydt STRING;
DECLARE qry STRING;
SET sydt = CONCAT(EXTRACT(HOURfromCURRENT_TIMESTAMP()),'_',EXTRACT(MINUTEfromCURRENT_TIMESTAMP()),'_',EXTRACT(SECONDfromCURRENT_TIMESTAMP()));
SET qry ='create table '||p_dataset_name||'.'||p_table_name||'_'||sydt||' ('||p_column_name||' '||p_data_type||')';
EXECUTE IMMEDIATE qry;

Solution 2:

To run a dynamic query in SQL, you need to:

1. Construct the query using string operations and functions.
2. Execute the constructed string as a query.

BigQuery supports #1, but it does not have an EXEC statement for #2 as of this writing.

If you want to run dynamic queries in BigQuery, you will have to construct the string in a cloud function (or similar environment) and then send the query to BigQuery via the API.

Solution 3:

You will have to use an external call through an API. I use Python for this. Both parameter base queries and Dynamic queries. Big query at the moment as far as i know does not support variables or an execute sql command, using SQL directly ( like TSQL )

Solution 4:

There are two "modes" you can run BigQuery query in - interactive and batch .

By default, BigQuery runs interactive queries, which means that the query is executed as soon as possible. Interactive queries count towards your concurrent rate limit and your daily limit.

BigQuery also offers batch queries. BigQuery queues each batch query on your behalf, and starts the query as soon as idle resources are available, usually within a few minutes. If BigQuery hasn't started the query within 24 hours, BigQuery changes the job priority to interactive. Batch queries don't count towards your concurrent rate limit, which can make it easier to start many queries at once.

Both are available in WebUI, Command line, API and Clients