CODEWITHSUNDEEP
shellgoogle-cloud-platformgoogle-bigquery
CARREAU Clément
CARREAU Clément

Reputation: 717

Trouble passing array variable to BigQuery query from shell

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 :

enter image description here

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 !

Upvotes: 0

Views: 264

Answers (1)

CARREAU Cl&#233;ment
CARREAU Cl&#233;ment

Reputation: 717

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.

Upvotes: 1

Related Questions