CODEWITHSUNDEEP
sqlselectgoogle-bigquery
Timogavk
Timogavk

Reputation: 869

How to declare a variable in BigQuery and set it as an argument for EXTERNAL_QUERY?

I have some external data base connection in my BigQuery project How can I declare a variable to set it as an argument for EXTERNAL_QUERY I tried to write this

DECLARE req STRING DEFAULT 'SELECT * FROM tracker'; SELECT * FROM EXTERNAL_QUERY("tdv3.eu.tracker", req)

But if I run it like

SELECT * FROM EXTERNAL_QUERY("tdv3.eu.tracker", '''SELECT * FROM tracker''')

it works well. Where I'm wrong and how to fix it?

Upvotes: 1

Views: 20142

Answers (3)

Soumendra Mishra
Soumendra Mishra

Reputation: 3653

You can use EXECUTE IMMEDIATE:

DECLARE INPUT STRING;
DECLARE DSQL STRING;
SET INPUT = "'CSV'";
SET DSQL = 'SELECT * FROM EXTERNAL_QUERY("tdv3.eu.tracker", "SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE=' || INPUT || '")';
EXECUTE IMMEDIATE DSQL;

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

You can make it work by using EXECUTE IMMEDIATE as in below example

DECLARE req STRING;
SET req = '"SELECT * FROM tracker"';
EXECUTE IMMEDIATE 'SELECT * FROM EXTERNAL_QUERY("tdv3.eu.tracker",' || req || ');'

Upvotes: 5

GMB
GMB

Reputation: 222412

BQ does not support passing a script variable to EXTERNAL_QUERY.

You must either use a literal string (that is, passing the query string directly to the query), or use a parameter (the error message that you are getting should mention that).

Such a parameterized query would look like:

SELECT * FROM EXTERNAL_QUERY("tdv3.eu.tracker", @query);

Or using a positionnal parameter:

SELECT * FROM EXTERNAL_QUERY("tdv3.eu.tracker", ?);

Note that query parameters are supported in bq CLI, API or from your application client, but not by the Console of Classic UI.

Upvotes: 0

Related Questions