Search code examples
google-bigquery

External_query with dynamic query - Error: Connection argument in EXTERNAL_QUERY must be a literal string or query parameter

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?

Solution

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