I'm trying to call external_query with dynamically created query string in BigQuery Web UI.
DECLARE extquery STRING;
SET extquery = "SELECT * FROM mytable;";
SELECT * FROM EXTERNAL_QUERY("my-external-conn", extquery);
This script gives an error
Query error: Invalid table-valued function EXTERNAL_QUERY
Connection argument in EXTERNAL_QUERY must be a literal string or query parameter
Is it impossible to do in BigQuery? What is the right way to pass query as a parameter?
As error states - "Connection argument in EXTERNAL_QUERY must be a literal string or query parameter"
So, "must be a literal string" version would be
SELECT * FROM EXTERNAL_QUERY("my-external-conn", "SELECT * FROM mytable;");
It is more complicated with "must be a query parameter"
Note: query parameters and script variables are quite different animals - you can check details in respective docs - but to clarify my point : extquery in SET extquery = "SELECT * FROM mytable;"; is not a parameter but rather script variable
Meantime, BigQuery UI (neither Classic nor Console) do not support Parameterized Queries, but you still can make it using CLI, API or clients of your choice
Below example is for CLI
bq query \
--use_legacy_sql=false \
--parameter='extquery::SELECT * FROM mytable;' \
'SELECT * FROM EXTERNAL_QUERY("my-external-conn", @extquery)'
You can wrap the whole query into a dynamic query, and use execute_immediate to show results:
declare query_string string;
set query_string = "SELECT * FROM EXTERNAL_QUERY('my-external-conn', \""||extquery||"\")";
execute immediate query_string;
