0
%%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.

Louis
  • 77
  • 1
  • 6
  • Note the semi-colons in the query: this is a **script** and that is what triggers the error. Normal queries work just fine. – Louis Apr 15 '20 at 18:29

2 Answers2

0

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Alexandre Moraes
  • 3,892
  • 1
  • 6
  • 13
  • What you mention all works fine. The problem with my query is that it is a query script (check the semi-columns) and that is what triggers the error. The error just says: "error". Thanks for taking the time though. – Louis Apr 15 '20 at 18:27
  • @Louis, what do you mean by query script ? Is it inside a python file ? Do you want to run these commands inside a Notebook in CLI? – Alexandre Moraes Apr 16 '20 at 06:22
  • BigQuery scripting enables you to send multiple statements to BigQuery in one request. If you look carefully you will see I have 2 SELECT statements in my code which are separated with a semi-column. Can you get that to run in your notebooks? https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting – Louis Apr 20 '20 at 13:09
  • @Louis, in the Select statements you provided I do no understand what you want to do with 2 select statements displayed in that way, because when you write them like that it seems they are from 2 different tables. Regarding BigQuery scripting, bigquery allows sending multiple statements inside a _loop or some conditions conditions_, which I do not see in your code above. Can you elaborate more on what you are trying to do so I can update my answer and help you. – Alexandre Moraes Apr 27 '20 at 08:24
  • I just need to run a script in a notebook. What it does is it sets variables from query results and then runs a few queries in sequence. The above is just the most simple example to illustrate scripts are not working and asking if I need to do a special configuraiton in my notebook to support that, and if so, what do I need to do. – Louis Apr 28 '20 at 18:42
  • @Louis, to run a script in a notebook you just need to make sure your environment is set as mentioned in the documentation, [1](https://cloud.google.com/datalab/docs/quickstart#before-you-begin). Then you have have to have the correct syntax according with the [documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting). The query you provided has the wrong syntax. – Alexandre Moraes Apr 29 '20 at 11:41
0
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()
Julio Urquidi
  • 1
  • 1