CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Yogesh Devi
Yogesh Devi

Reputation: 689

How to use query parameters in GCP BigQuery federated queries

I have a gcp based environment. I use standard SQL scripting in gcp BigQuery and federated query to cloudsql MySql. Federated query selects data from cloudsql mysql database. I need to select data from cloudsql mysql database based on condition that depends on data in BigQuery. I use variables in standard sql scriping in gcp bigquery to store the value that I select from bigquery. I want to value of this variable in the where clause of mysql query. See following example where I select a date from BigQuery and store it in a variable "BQ_LAST_DATETIME".

    DECLARE BQ_LAST_DATETIME DATETIME
    SET BQ_LAST_DATETIME = (select max(date_created) from bq_my_dataset.bq_my_table);

Since I am using bigquery federated query to read data out of cloudsql database (https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries) as shown below and I want to use value that I stored in the variable "BQ_LAST_DATETIME" in the mysql query where clause

   SELECT * FROM EXTERNAL_QUERY("my-gcp-project.my-region.my-connection2-cloudsql", "select * from mysqlschema.mysql_table where where date_created = @BQ_LAST_DATETIME;" );

Please note that in above query I have used "@BQ_LAST_DATETIME" as a placeholder to show what I want to achieve. I am not sure if I can directly use bigquery scripting variable as query parameter in the "external" query part of federated query. Any suggestions on how to achieve parametrization of external queries in federated query, or if you know how I could achieve effect similar to what my intent is?

I actually tried following as depicted . I used bigquery scripting variable as query parameter in the "external" query part of federated query. only nuance here is that since the I was dealing with dates I performed a cast and also since the date variable actually is treated as a string I formatted it back to date using mysql STR_TO_DATE as follows

    DECLARE BQ_LAST_DATETIME DATETIME
    SET BQ_LAST_DATETIME = (select max(date_created) from bq_my_dataset.bq_my_table);
    SET BQ_LAST_DATE= CAST(BQ_LAST_DATETIME  AS DATE);
    SELECT * FROM EXTERNAL_QUERY("my-gcp-project.my-region.my-connection2-cloudsql", "select * from mysqlschema.mysql_table where where date_created =  STR_TO_DATE(@BQ_LAST_DATE,'%Y-%m-%d') ;" );

While this query is accepted by parser it is NOT giving expected result. Basically the value of the variable @BQ_LAST_DATE does not seem to get to MySQL query as expected.

Does anyone know what am I missing ?

Thanks a lot for your help

Upvotes: 5

Views: 4724

Answers (2)

Soumendra Mishra
Soumendra Mishra

Reputation: 3653

You can try EXECUTE IMMEDIATE:

DECLARE BQ_LAST_DATETIME STRING;
DECLARE DSQL STRING;
SET BQ_LAST_DATETIME = 'SELECT max(date_created) from bq_my_dataset.bq_my_table';
SET DSQL = '"select * from mysqlschema.mysql_table where date_created = (' || BQ_LAST_DATETIME || ')"';
EXECUTE IMMEDIATE 'SELECT * FROM EXTERNAL_QUERY("my-gcp-project.my-region.my-connection2-cloudsql",' || DSQL || ');'

Upvotes: 8

Yogesh Devi
Yogesh Devi

Reputation: 689

So I figured - it works almost as depicted .. that means you can directly use bigquery scripting variable as query parameter in the "external" query part of federated query. only nuance here is that since the I was dealing with dates I performed a cast and also since the date variable actually is treated as a string I had to format it back to date using mysql STR_TO_DATE as follows

enter code here
 DECLARE DATE_FROM_BIGQUERY_DATA DATETIME
 SET BQ_LAST_DATETIME = (select max(date_created) from bq_my_dataset.bq_my_table);
 SET LAST_DATE= CAST(LAST_DATETIME  AS DATE);
SELECT * FROM EXTERNAL_QUERY("my-gcp-project.my-region.my-connection2-cloudsql", "select * from mysqlschema.mysql_table where where date_created =  STR_TO_DATE(@BQ_LAST_DATE,'%Y-%m-%d') ;" );

Upvotes: -1

Related Questions