Is there any way to run a BigQuery script from Google Cloud Datalab?

Question

%%bq query
SELECT 2;
SELECT 3;

Will result in a error.

I also tried a more sophisticated approach like a few variations of:

import google.datalab.bigquery as bq
bq.Query(sql='''
SELECT 2;
SELECT 3;
''').execute()

And it will not work. I think the problem is the python API does not know how to deal with the multiple result sets returned.

Answer 1

While it is hard to know the reason for your error without seeing the message you are getting, I was able to perform your query with some slightly differences. However, I must stress some points in advance.

First, check if you have to check if the Google Compute Engine and Cloud Source Repositories APIs and if you project is set correctly, here.

I followed the this tutorial in the documentation to install Gcloud in my local terminal, but you can choose the other ways to install it here, in case if you need it. Then, to set up DataLab I followed the QuickStart provided by google.

To use BigQuery in the notebook, you should import the module google.datalab.bigquery. Afterwards, in you command line I just added the UNION ALL statement so the values are displayed in 2 different lines, as follows:

query_test = bq.Query('Select 3 UNION ALL Select 2 ')

There won't be any displayed results, to make sure the results will be retrieved you have to use the methods execute() and result() to run the the above query. In addition, you could also, as an option, set cache turned off, so the results you are retrieving are not from any other previous run. You can do it as following:

output_options = bq.QueryOutput.table(use_cache=False)
result = query_test.execute(output_options=output_options).result()
result

And the output:

f0_
3
2

Another way of executing the same query and retrieving the same result as above is using %%bq, as you mentioned. You can perform the same query as below:

%%bq query
select 2 union all select 3

I encourage you to have a look at datalab/docs/tutorials/BigQuery/BigQuery/ in your notebook instance, there are significant usage examples and explanations and at the Working with Notebooks documentation.

Answer 2

import pandas as pd
from google.cloud import bigquery
client = bigquery.Client(project = 'project-name')
sql = """ SELECT * FROM TABLE """
pd_df = client.query(sql).to_dataframe()