CODEWITHSUNDEEP
linuxbashgoogle-bigquerygcloud
activelearner
activelearner

Reputation: 7745

BigQuery Command Line - How to use parameters in the query string?

I am writing a shell script which involves BigQuery commands to query an existing table and save the results to a destination table.

However, since my script will be run periodically, I have a parameter for the date for which the query should run.

For example, my script looks like this:

DATE_FORMATTED=$(date +%Y%m%d)

bq query --destination_table=Desttables.abc_$DATE_FORMATTED "select hits_eventInfo_eventLabel from TABLE_DATE_RANGE([mydata.table_],TIMESTAMP($DATE_FORMATTED),TIMESTAMP($DATE_FORMATTED)) where customDimensions_index = 4"

I get the following error:

Error in query string: Error processing job 'pro-cn:bqjob_r5437894379_1': FROM clause with table wildcards matches no table

How else can I pass the variable $DATE_FORMATTED to the TABLE_DATE_RANGE function from BigQuery in order to help execute my query?

Upvotes: 0

Views: 2129

Answers (2)

Yosua Simanjuntak
Yosua Simanjuntak

Reputation: 17

Use double quotes "" + single quote ''. For example, in your case:

TIMESTAMP("'$DATE_FORMATTED'")

OR 

select "'$variable'" as dummy from your_table

Upvotes: 1

Javier Ramirez
Javier Ramirez

Reputation: 3988

You are probably missing the single quotes around the $DATE_FORMATTED value inside the TIMESTAMP functions. Without the quotes it's going to be defaulting to the EPOCH time.

Try with: 

TIMESTAMP('$DATE_FORMATTED'),TIMESTAMP('$DATE_FORMATTED')

Upvotes: 0

Related Questions