Debugging a bigquery federated query with variables

Question

so i'm trying to build an incremental update system for my tables in bigquery with source as Cloud SQL MySQL database. For that I am using federated queries and I'd like to query data that has been updated after a certain timestamp.

I have based my query on this question's answer: How to use query parameters in GCP BigQuery federated queries

To obtain:

DECLARE BQ_LAST_DATETIME STRING;
DECLARE DSQL STRING;
SET BQ_LAST_DATETIME = "SELECT MAX(updated_at) FROM `dataset.table`";
SET DSQL = '"SELECT * FROM mysql_table WHERE updated_at > ('|| BQ_LAST_DATETIME ||')"';
EXECUTE IMMEDIATE 'SELECT * FROM EXTERNAL_QUERY("mygcpproject.region.database",'|| DSQL||');'

THIS did not work because the bigquery query to set BQ_LAST_DATETIME was being run in mysql hence not finding the table.

so I found out that you have to evaluate the first SELECT, which led to this:

DECLARE BQ_LAST_DATETIME TIMESTAMP;
DECLARE DSQL STRING;
SET BQ_LAST_DATETIME = (SELECT MAX(updated_at) FROM `dataset.table`);
SET DSQL = '"SELECT * FROM mysql_table WHERE updated_at > ('|| BQ_LAST_DATETIME ||')"';
EXECUTE IMMEDIATE 'SELECT * FROM EXTERNAL_QUERY("mygcpproject.region.database",'|| DSQL||');'

This did not work either because the query will have something like:

SELECT * FROM mysql_table WHERE updated_at > 16-09-2022 15:25:00

where it should be:

SELECT * FROM mysql_table WHERE updated_at > '16-09-2022 15:25:00'

Does anybody know how to do that ? Thanks a lot for your help

Jaytiger · Answer

You may try one of followings:

use an escaped quote(\') instead of ( and ).

SET DSQL = '"SELECT * FROM mysql_table WHERE updated_at > \''|| BQ_LAST_DATETIME ||'\'"';

use a formatted string with triple quotes """ in FORMAT() function.

EXECUTE IMMEDIATE FORMAT("""
  SELECT * FROM EXTERNAL_QUERY(
    "mygcpproject.region.database",
    "SELECT * FROM mysql_table WHERE updated_at > '%s'"
  );
""", STRING(BQ_LAST_DATETIME));

Debugging a bigquery federated query with variables

1 Answers