如何在GCP BigQuery联邦查询中使用查询参数

Question

我有一个基于gcp的环境。我在gcp BigQuery中使用标准SQL脚本，在cloudsql MySql中使用联邦查询。联邦查询从cloudsql mysql数据库中选择数据。我需要选择数据从cloudsql mysql数据库的基础上，取决于在BigQuery数据的条件。我在gcp bigquery的标准sql脚本中使用变量来存储我从bigquery中选择的值。我想在mysql查询的where子句中这个变量的值。请参阅下面的示例，我从BigQuery中选择一个日期并将其存储在变量"bq_last_datetime"中。

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

因为我使用bigquery联邦查询从cloudsql数据库(https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries)读取数据，如下所示，我想使用存储在变量"BQ_LAST_DATETIME"中的值mysql查询where子句

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

请注意，在上面的查询中我使用了"@BQ_LAST_DATETIME"作为一个占位符来展示我想要实现的目标。我不确定是否可以直接使用bigquery脚本变量作为"外部"中的查询参数。联邦查询的查询部分。关于如何在联邦查询中实现外部查询的参数化，您有什么建议吗?或者您是否知道如何实现与我的意图相似的效果?

我实际上尝试如下所示。我使用了bigquery脚本变量作为查询参数在"外部"联邦查询的查询部分。这里唯一的细微差别是，由于I处理的是日期，我执行了强制转换，而且由于日期变量实际上被视为字符串，我使用mysql STR_TO_DATE将其格式化为日期，如下所示

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') ;" );

当解析器接受这个查询时，它没有给出预期的结果。基本上，变量@BQ_LAST_DATE的值似乎没有像预期的那样到达MySQL查询。

有谁知道我错过了什么吗?

谢谢你的帮助

Answer 1

您可以尝试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 || ');'

Answer 2

所以我想-它几乎像描述的那样工作。这意味着你可以直接使用bigquery脚本变量作为查询参数在"外部"联邦查询的查询部分。这里唯一的细微差别是，由于I处理的是日期，我执行了强制转换，而且由于日期变量实际上被视为字符串，我必须使用mysql STR_TO_DATE将其格式化为日期，如下所示

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') ;" );