execute variable values Google BigQuery

Question

I am trying to execute the value of the variable, but I can't find documentation about it in Google BigQuery.

DECLARE SQL STRING;

SELECT
  SQL = 
  CONCAT(
    "CREATE TABLE IF NOT EXISTS first.rdds_", 
    REPLACE(CAST(T.actime AS STRING), " 00:00:00+00", ""),
    " PARTITION BY actime ",
    " CLUSTER BY id ",
    " OPTIONS( ",
    " partition_expiration_days=365 ",
    " ) ",
    " AS ",
    "SELECT * ",
    "FROM first.rdds AS rd ",
    "WHERE rd.actime = ",
    "'", CAST(T.actime AS STRING), "'",
    " AND ",
    "EXISTS ( ",
    "SELECT 1 ",
    "FROM first.rdds_load AS rd_load ",
    "WHERE rd_load.id= rd.id ",
    ")"

  ) AS SQ
FROM (
  SELECT DISTINCT actime
  FROM first.rdds AS rd
  WHERE EXISTS (
    SELECT 1
    FROM first.rdds_load AS rd_load
    WHERE rd_load.id= rd.id
  )
) T;

My variable will have many rows with scripted for create tables and I need to execute this variable.

In SQL Server for to execute variable is:

EXEC(@variable);

How to I execute SQL variable in Google BigQuery?

EDIT:

I did new test with version beta:

Using array, all rows in one result (ARRAY_AGG):

DECLARE SQL ARRAY<STRING>;

SET SQL = (
SELECT
  CONCAT(
    "CREATE TABLE IF NOT EXISTS first.rdds_", 
    REPLACE(CAST(T.actime AS STRING), " 00:00:00+00", ""),
    " PARTITION BY actime ",
    " CLUSTER BY id ",
    " OPTIONS( ",
    " partition_expiration_days=365 ",
    " ) ",
    " AS ",
    "SELECT * ",
    "FROM first.rdds AS rd ",
    "WHERE rd.actime = ",
    "'", CAST(T.actime AS STRING), "'",
    " AND ",
    "EXISTS ( ",
    "SELECT 1 ",
    "FROM first.rdds_load AS rd_load ",
    "WHERE rd_load.id= rd.id ",
    ")"
  )
) AS SQ

FROM (
  SELECT DISTINCT actime
  FROM first.rdds AS rd
  WHERE EXISTS (
    SELECT 1
    FROM first.rdds_load AS rd_load
    WHERE rd_load.id= rd.id
  )
) T
);

My result:

One row with all instructions. But I can't running this with all instructions

BigQuery does not support this in pure SQL, but you can implement this in any client of your choice — Mikhail Berlyant
– Mikhail Berlyant, Commented Oct 16, 2019 at 19:59

Yun Zhang · Accepted Answer · 2020-05-20 12:47:22Z

2

Update: as of 5/20/2020, BigQuery released dynamic SQL feature for you to achieve the goal.

Dynamic SQL is now available as a beta release in all BigQuery regions. Dynamic SQL lets you generate and execute SQL statements dynamically at runtime. For more information, see EXECUTE IMMEDIATE. x

================

BigQuery does not support this (Dynamic SQL) in pure SQL, but you can implement this in any client of your choice

edited May 20, 2020 at 12:47

Yun Zhang

5,6032 gold badges14 silver badges30 bronze badges

answered Oct 16, 2019 at 23:07

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

Comments

justbeez · Accepted Answer · 2019-10-17 17:43:51Z

While Mikhail is correct that this historically hasn't been supported in BigQuery, the very new beta release of BigQuery Scripting should let you accomplish similar results: https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting

In this case, you would need to use SET to assign the value of the variable, and there isn't an EXEC statement at this time, but there is support for conditionals, loops, variables, etc.

To recreate your example, you could store the results of a query against either your first.rdds_load table, then use WHILE to loop over those results. Within that loop, you can run a normal CREATE TABLE if it doesn't already exist. I'm thinking something along these lines based on your example . . .

DECLARE results ARRAY<STRING>;
DECLARE i INT64 DEFAULT 1;
DECLARE cnt INT64 DEFAULT 0;

SET results = ARRAY(
    SELECT
        DISTINCT AS VALUE
            CAST(actime AS STRING)
    FROM
        first.rdds AS rd
    WHERE
        EXISTS (
            SELECT
                1
            FROM
                first.rdds_load AS rd_load
            WHERE
                rd_load.id = rd.id
        )
);
SET cnt = ARRAY_LENGTH(results);

WHILE i <= cnt DO
    /* Body of CREATE TABLE goes here; you can access the rows from the query above using results[ORDINAL(i)] as you loop through*/
END WHILE;

There's also support for stored procedures, which can be executed via CALL with passed arguments, which may work in your case as well (if you need to abstract the creation logic used by many scripts).

(I would argue that this scripting support is superior to building and executing strings, since you'll still get SQL validation and such for your query.)

As always with beta features, use with caution in production—but for what it's worth, thus far my experience has been incredibly stable.

Collectives™ on Stack Overflow

execute variable values Google BigQuery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related