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

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命令拒绝在[1:22]为表"XXX"的用户"XXX@'cloudsqlproxy~"[1:1]

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

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

编辑1:我在执行语句之前打印了语句，它看起来像这样，我想这对我来说很好。

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表查询您的云SQL实例。这行不通。在将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 || ');'