CODEWITHSUNDEEP
pythonpandasgoogle-bigquerygoogle-cloud-datalab
goose
goose

Reputation: 2642

Datalab: How to export Big Query standard SQL query to dataframe?

Within a datalab notebook it's possible to export BiqQuery results directly into a dataframe. I have an example that works for legacy SQL and I need to adjust to make it work for standard SQL. I can't find a syntax that works for this. Here's what I think I need to write:

import datalab.bigquery as bq
import pandas as pd

%%sql --module data_name -d standard
SELECT COUNT(*) AS count FROM `project.dataset.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20170126' AND '20170126'

my_data_frame = bq.Query(data_name).to_dataframe()

Something must be wrong with this syntax as I get the error:

Exception: invalid: Invalid table name: `project.dataset.table.ga_sessions_*`

How can I fix this to work for Standard SQL?

Upvotes: 2

Views: 1587

Answers (2)

QuinRiva
QuinRiva

Reputation: 729

Note that this %%sql magic has been supplanted by the %%bq magic, and standard sql is now the default:

Migration guide

Upvotes: 0

Willian Fuks
Willian Fuks

Reputation: 11777

Maybe you need to make it explicit that you want to use Standard SQL in datalab.

Let us know if this works for you (make sure your branch is up-to-date with the master branch):

my_data_frame = bq.Query(data_name).to_dataframe(dialect='standard')

Upvotes: 5

Related Questions