Trouble passing array variable to BigQuery query from shell

Question

I'm trying to run parametrized queries from a shell script and I need to be able to provide parameters.

This is the command I'm trying to run :

bq --location=US query --use_legacy_sql=False \
--parameter='gender::M' \
--parameter='states:ARRAY<STRING>:["WA", "WI", "WV", "WY"]' \
'SELECT name, sum(number) as count
FROM `bigquery-public-data.usa_names.usa_1910_2013`
WHERE gender = @gender
AND state IN UNNEST(@states)
GROUP BY name
ORDER BY count DESC
LIMIT 10;'

If you copy/paste as it is in a shell, it works. Now, this is what I'm trying to run :

In a file script.sh

runQuery(){

    states="'$1'"

    bq --location=US query --use_legacy_sql=False --parameter='gender::M' --parameter=$states \
        'SELECT name, sum(number) as count
        FROM `bigquery-public-data.usa_names.usa_1910_2013`
        WHERE gender = @gender
        AND state IN UNNEST(@states)
        GROUP BY name
        ORDER BY count DESC
        LIMIT 10;'
    }

runQuery 'states:ARRAY<STRING>:["WA", "WI", "WV", "WY"]'

But this doesn't work.

Here's the error log :

I am pretty sure it has to do with the fact that the parameter has both single quotes and double quotes and at some points something goes wrong when processing 'states:ARRAY:["WA", "WI", "WV", "WY"]' but I just can't find how to solve this.

Thanks in advance !

CARREAU Clément · Accepted Answer · 2018-09-07 12:02:29Z

1

Found the error :

Changed runQuery 'states:ARRAY<STRING>:["WA", "WI", "WV", "WY"]'

To runQuery 'states:ARRAY<STRING>:["WA","WI","WV","WY"]'

Note that blank spaces were removed from the array.

answered Sep 7, 2018 at 12:02

CARREAU Clément

7175 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trouble passing array variable to BigQuery query from shell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related