504 Deadline Exceeded error when downloading BQ query results to Python dataframe

user_4914988 0
Viewed 1615

I'm using Python to run a query on a BigQuery dataset and then put the results into a Python dataset. The query runs OK; I can see a temporary table is created for the results in the dataset in BQ, but when using the query client's to_dataset method, it falls over on the 504 Deadline Exceeded error

client = bigquery.Client( credentials=credentials, project= projectID )
dataset = client.dataset('xxx')
table_ref =  dataset.table('xxx')
JobConfig = bigquery.QueryJobConfig(destination = table_ref) 
client.delete_table(table_ref, not_found_ok=True)
QueryJob = client.query(queryString, location='EU', job_config=JobConfig)
QueryJob.result()
results = client.list_rows(table_ref, timeout =100).to_dataframe()

It all runs fine until the last line. I've added a timeout argument to the list_rows method, but it hasn't helped. I'm running this on a Windows virtual machine, with Python 3.8 installed.
(I've also tested the same code on my laptop and it worked just fine - don't know what's different.)

google-bigquery virtual-machine dataframe python
3 Answers

Take a look at: https://github.com/googleapis/python-bigquery-storage/issues/4

it's a known bug in Windows, the "solution" is to:

import google.cloud.bigquery_storage_v1.client
from functools import partialmethod

# Set a two hours timeout
google.cloud.bigquery_storage_v1.client.BigQueryReadClient.read_rows = partialmethod(google.cloud.bigquery_storage_v1.client.BigQueryReadClient.read_rows, timeout=3600*2)

Providing that you'll use:

bqClient = bigquery.Client(credentials=credentials, project=project_id)
bq_storage_client = bigquery_storage_v1.BigQueryReadClient(credentials=credentials)
raw_training_data = bqClient.query(SOME_QUERY).to_arrow(bqstorage_client=bq_storage_client).to_pandas()
user_18389050

If you can use pandas try this :

import pandas as pd
df = pd.read_gbq("select * from `xxx.xxx`", dialect='standard', use_bqstorage_api=True)

To be able to use use_bqstorage_api you have to set it up on GCP. Read more about that in documentation

user_16836260

This link has helped me : https://googleapis.dev/python/bigquery/latest/usage/pandas.html

My working code is :

credentials, your_project_id = google.auth.default(scopes=["https://www.googleapis.com/auth/cloud-platform"])
bqclient = bigquery.Client(credentials=credentials, project=your_project_id)
query_string = """SELECT..."""
df = bqclient.query(query_string).to_dataframe()

Hope it will help you guys

user_106342080
Related