BigQuery invalid table name error when using Standard SQL in BigQuery API's

Question

I am trying query the table and store the result in another BigQuery table using python BigQuery API. But when I use standard SQL in query part it throws invalid table name error. How to use standard SQL in BigQuery API? I am using airflow BigQuery hoooks

'configuration': {
        'query': {
            'destinationTable': {
                'tableId': u 'our_table_name',
                'datasetId': 'our_dataset_id',
                'projectId': 'our_project_id'
            },
            'useLegacySql': False,
            'allowLargeResults': True,
            'writeDisposition': 'WRITE_TRUNCATE',
            'query': u'SELECT * FROM `projectID.datasetId.tablename`',
            
        }
    }

Exception: BigQuery job failed. Final error was: {u'reason': u'invalid', u'message': u'Invalid table name: `projectId:datasetId.tableId`', u'location': u'`projectId:datasetId.tableId`'}.

Answer 1

The error is confusing, but the root cause is that this query was interpreted as Legacy SQL, not as Standard SQL. In JSON (unlike, say, in Python), boolean literals true and false must be lowercase, per JSON standard Section 3:

A JSON value MUST be an object, array, number, or string, or one of
the following three literal names:

  false null true

The literal names MUST be lowercase. No other literal names are
allowed.

So if you change

        `'useLegacySql': False,`

to

        `'useLegacySql': false,`

it should work