BigQuery table not found error on location

Question

i'm trying to run below script but i keep getting error that the dataset isn't found. The problem is caused by $date on the Select Query, how do I fix this? My goal is to copy the table from another dataset with data matching based on the date.


#!/bin/bash
date="20160805"
until [[ $date > 20160807 ]];
    do
    bq query --use_legacy_sql=false --destination_table="google_analytics.ga_sessions_${date}" 'SELECT g.* FROM `10241241.ga_sessions_$date` g, UNNEST (hits) as hits where hits.page.hostname="www.googlemerchandisestore.com" '
    date=$(date +'%Y%m%d' -d "$date + 1 day")
done

Below error:

BigQuery error in query operation: Error processing job 'test-247020:bqjob_r6a2d68fbc6d04a34_000001722edd8043_1': Not found: Table test-247020:10241241.ga_sessions_ was not found in location EU

BigQuery error in query operation: Error processing job 'test-247020:bqjob_r5c42006229434f72_000001722edd85ae_1': Not found: Table test-247020:10241241.ga_sessions_ was not found in location EU

BigQuery error in query operation: Error processing job 'test-247020:bqjob_r6114e0d3e72b6646_000001722edd8960_1': Not found: Table test-247020:10241241.ga_sessions_ was not found in location EU

Alexey Klishin Alexey Klishin · Accepted Answer · 2020-05-19T21:53:45

The problem is that you are using single quotes for the query, therefore bash doesn't replace $date with value of parameter. You need to keep double quotes for the query string:

date="20160805"
until [[ $date > 20160807 ]];
    do
    bq query --use_legacy_sql=false --destination_table="google_analytics.ga_sessions_${date}" "SELECT g.* FROM \`10241241.ga_sessions_$date\` g, UNNEST (hits) as hits where hits.page.hostname=\"www.googlemerchandisestore.com\" "
    date=$(date +'%Y%m%d' -d "$date + 1 day")
done

BigQuery table not found error on location

1 Answers