GCP Bigquery - query parameters in federated queries-CodePudding

I am failing to implement the solution proposed in this question.

My sql code:

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

I get the following error

invalid table-valued function external_query Failed to get query schema from MySQL server. Error: MysqlErrorCode(1142): SELECT command denied to user 'XXX@'cloudsqlproxy~' for table 'XXX' at [1:22] at [1:1]

If I hard code the value of bq_last_id like this, it works fine.

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

EDIT 1: I printed the statement before execute statement and it looks like this, and that looks fine to me I guess.

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

EDIT 2: Finally I had to insert data back so using the answer below and a one more bracket I was able to do it this way.

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

Thanks.

CodePudding user response：

Thanks to your update, here the issue: You query your Cloud SQL instance with a BQ table in reference. That can't work. You must evaluate the max(id) before adding it to your query, like that

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