How to query a BigQuery table from DataLab notebook using google.datalab.bigquery?

Question

I have the following code in DataLab notebook:

import google.datalab.bigquery as bq

%bq query
SELECT count(id) FROM 'rtda.tweets'

It returns the error:

invalidQuery: Syntax error: Unexpected string literal 'rtda.tweets' at [1:23]

The BiqQuery table schema is correctly returned the by bigquery schema command:

%bigquery schema --table "rtda.tweets"

name        type        mode        description
created_at  TIMESTAMP   NULLABLE    
id          INTEGER     NULLABLE    
id_str      STRING      NULLABLE    
(...)

In BigQuery console I am able to successfully execute the query:

SELECT count(id) FROM [rtda.tweets];

The problem is that I am not able to execute this command in DataLab notebook.

score 3 · Accepted Answer · answered May 01 '17 at 16:14

The query shown above is defined using legacy SQL however standard SQL is the preferred SQL dialect for querying data stored in BigQuery according to the BigQuery docs. According to the datalab to google.datalab Migration Guide, Legacy SQL is no longer supported in google.datalab. Can you convert your queries to Standard SQL?

For Standard SQL, change the quotes ' around the table name to backticks

%bq query

SELECT count(id) FROM `rtda.tweets`

The reason this works in the BigQuery console is that the console still supports legacy SQL whereas google.datalab does not.

Since this table name doesn't use any special characters - you can drop backticks and use just SELECT count(id) FROM rtda.tweets — Mosha Pasumansky, May 01 '17 at 17:33

How to query a BigQuery table from DataLab notebook using google.datalab.bigquery?

1 Answers1