GCP Bigquery -联邦查询中的查询参数

Question

我未能实现在这个solution中提出的question。

我的sql代码：

declare bq_last_id string;
declare external_sql string;
set bq_last_id = 'select max(id) from bq_dataset.bq_table';
set external_sql = '"select * from mysql_table where id > ('|| bq_last_id ||')"';
execute immediate 'select * from external_query("my-gcp-project.my-region.my-connection-name",'|| external_sql || ');'

我得到以下错误

无效表值函数external_query无法从MySQL服务器获取查询架构。错误: MysqlErrorCode(1142)：SELECT命令拒绝用户'XXX@'cloudsqlproxy~‘，表'XXX’在1:22处1:1

如果我像这样硬编码bq_last_id的值，它就能正常工作。

set external_sql = '"select * from mysql_table where id > 123456"';

编辑1:我在execute语句之前打印了这个语句，它看起来是这样的，我认为这很好。

select * from external_query("my-gcp-project.my-region.my-connection-name", "select * from mysql_table where id > (select max(id) from bq_dataset.bq_table)");

编辑2:最后，我不得不将数据插入回，所以使用下面的答案和另一个括号，我能够这样做。

declare bq_last_id int64; # Change the type here
declare external_sql string;
set bq_last_id = (select max(id) from bq_dataset.bq_table); # put the query in parenthesis to force BQ to evaluate it
set external_sql = '"select * from mysql_table where id > ('|| bq_last_id ||')"';
execute immediate 'insert into bq_dataset.bq_table (select * from external_query("my-gcp-project.my-region.my-connection-name",'|| external_sql || '))'

谢谢。

Answer 1

多亏了您的更新，这里的问题是:您使用引用的BQ表查询Cloud实例。那是行不通的。在将max(id)添加到查询之前，必须计算它，如下所示

declare bq_last_id int64; # Change the type here
declare external_sql string;
set bq_last_id = (select max(id) from bq_dataset.bq_table); # put the query in parenthesis to force BQ to evaluate it
set external_sql = '"select * from mysql_table where id > ('|| bq_last_id ||')"';
execute immediate 'select * from external_query("my-gcp-project.my-region.my-connection-name",'|| external_sql || ');'