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`'}.

can you share job id to look at specific details

Mosha Pasumansky
– Mosha Pasumansky

2017-03-22 04:48:15 +00:00
Commented Mar 22, 2017 at 4:48 — Mosha Pasumansky
– Mosha Pasumansky, Commented Mar 22, 2017 at 4:48
@MoshaPasumansky job id: job_lIaunDu3LHrgwm_KDOoxwmH39xw

MJK
– MJK

2017-03-23 16:41:54 +00:00
Commented Mar 23, 2017 at 16:41 — MJK
– MJK, Commented Mar 23, 2017 at 16:41
I also need project name please

Mosha Pasumansky
– Mosha Pasumansky

2017-03-23 16:48:04 +00:00
Commented Mar 23, 2017 at 16:48 — Mosha Pasumansky
– Mosha Pasumansky, Commented Mar 23, 2017 at 16:48
@MoshaPasumansky did you get project Id?

MJK
– MJK

2017-03-23 17:07:31 +00:00
Commented Mar 23, 2017 at 17:07 — MJK
– MJK, Commented Mar 23, 2017 at 17:07
Thanks, I got it - answer posted.

Mosha Pasumansky
– Mosha Pasumansky

2017-03-23 17:12:16 +00:00
Commented Mar 23, 2017 at 17:12 — Mosha Pasumansky
– Mosha Pasumansky, Commented Mar 23, 2017 at 17:12

Community · Accepted Answer · 2021-10-07 08:14:10Z

11

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

edited Oct 7, 2021 at 8:14

CommunityBot

11 silver badge

answered Mar 23, 2017 at 17:11

Mosha Pasumansky

14.1k5 gold badges36 silver badges57 bronze badges

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

2 Comments

MJK Over a year ago

yes, it works. I changed into 'useLegacySql': 'false'. Thank you so much

Jonny Brooks Over a year ago

@MJK you should mark this as an answered question if it worked.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related