3

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_column_name varchar2, p_data_type varchar2) return varchar2 is
    var varchar2(150);
    sydt varchar2(30);
    pragma autonomous_transaction;
    begin
    select to_char(sysdate,'HH24_MI_SS') into sydt from dual;
    dbms_output.put_line(sydt);
    var :='create table '||p_table_name||'_'||sydt||' ( '||p_column_name||' '||p_data_type||')';
    dbms_output.put_line(var);
      execute immediate var;  
      commit;
      return 'Table Created Successfully';
    end;

Is it possible to Achieve this in BigQuery functions?

Seth
  • 6,514
  • 5
  • 49
  • 58
Madhu Alle
  • 49
  • 1
  • 1
  • 4
  • Please see [ask] and [The perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). – Paul Roub May 14 '18 at 16:32

4 Answers4

4

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(HOUR from CURRENT_TIMESTAMP()),'_',EXTRACT(MINUTE from CURRENT_TIMESTAMP()),'_',EXTRACT(SECOND from CURRENT_TIMESTAMP()));
SET qry = 'create table '||p_dataset_name||'.'||p_table_name||'_'||sydt||' ('||p_column_name||' '||p_data_type||')';
EXECUTE IMMEDIATE qry;
Soumendra Mishra
  • 3,483
  • 1
  • 12
  • 38
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.

Seth
  • 6,514
  • 5
  • 49
  • 58
0

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

Community
  • 1
  • 1
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • I want to build SQL statements(not Parameterized Queries) dynamically at run time. Is there any way to create in BigQuery? – Madhu Alle May 15 '18 at 10:11
  • You have to explain better about your question (giving some examples). I guess you are asking about [client libraries for Bigquery](https://cloud.google.com/bigquery/docs/reference/libraries). Have a read into this [documentation](https://cloud.google.com/bigquery/create-simple-app-api#running-the-query). Not sure if this is what you are searching. – enle lin May 16 '18 at 15:59
0

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 )

Simon Bloor
  • 9
  • 1