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

0
votes

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?

2
linuxbashgoogle-bigquerygcloud
The parameter expansion here looks right (if your timestamp really is things like 20151112 rather than an actual POSIX timestamp). Are you sure the error is due to the parameter expansion? Doesn't look that way to me. The problem seems to be what's said in the error message: "FROM clause with table wildcards matches no table". - 4ae1e1

2 Answers

1
votes

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

TIMESTAMP("'$DATE_FORMATTED'")

OR 

select "'$variable'" as dummy from your_table
0
votes

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