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

Question

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?

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
– 4ae1e1, Commented Nov 12, 2015 at 19:35

Nimantha · Accepted Answer · 2020-02-19 17:13:33Z

1

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

TIMESTAMP("'$DATE_FORMATTED'")

OR

select "'$variable'" as dummy from your_table

edited Feb 19, 2020 at 17:13

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Feb 8, 2020 at 6:07

Yosua Simanjuntak

172 bronze badges

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

Comments

Nimantha · Accepted Answer · 2020-02-19 16:49:25Z

0

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

edited Feb 19, 2020 at 16:49

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Nov 12, 2015 at 20:05

Javier Ramirez

3,6891 gold badge28 silver badges38 bronze badges

2 Comments

activelearner Over a year ago

I tried with single quotes as well - got the same error.

Javier Ramirez Over a year ago

also add the dashes to your string DATE_FORMATTED=$(date +%Y-%m-%d)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related