Export Google BigQuery data to Python Pandas dataframe

Question

I've been researching how to export BigQuery data into Pandas. There are two methods:

Export the file to a CVS and load it - https://cloud.google.com/bigquery/exporting-data-from-bigquery
Directly pull the data into a pandas frame. This doesn't seem to work but here is the method - pandas.io.gbq.read_gbq(query, project_id=None, index_col=None, col_order=None, reauth=False) . It appears gbq has been discontinued?

Could someone please suggest the best and most efficient way to go about this?

Thank you.

What version of pandas are you using? It looks like it still exists in 0.15.0: pandas.pydata.org/pandas-docs/stable/generated/… — Jordan Tigani
– Jordan Tigani, Commented Oct 21, 2014 at 22:19
Yes that's precisely what i'm using. Still no success. Any other suggestions? — BlackHat
– BlackHat, Commented Oct 25, 2014 at 1:57
I've developed a python package (with 100% test coverage): google-pandas-load.readthedocs.io/en/latest that follows the first method. — augustin-barillec
– augustin-barillec, Commented Dec 5, 2019 at 9:41

AzCollin · Accepted Answer · 2014-10-30 04:01:31Z

The gbq.read_gbq method definitely works in pandas .15.0-1 as I just upgraded from .14.0-1 to check (Windows 7). If you are using Python, I would definitely recommend this for getting data into a dataframe from Google BigQuery as it is something I use for almost all my analysis work.

It is hard to say how to overcome your issue without more information, but I would start with checking if the authentication flow is completing in your browser that is logged into your Google account and then troubleshoot from there. There is a deprecation warning on your first authentication flow (oauth2client.tools.run), but everything does still work.

Other than that, I would try following the examples here: http://pandas-docs.github.io/pandas-docs-travis/io.html#io-bigquery

FYI, in the current dev branch, an option for Gcloud authentication is being added to make headless authentication more convenient.

Rishi · Accepted Answer · 2022-02-09 04:19:53Z

2

You can use the google cloud library and store it to dataframe

from google.cloud import bigquery
import os

#Your credentials to google cloud
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]=r"C:\YourPath\to\credentials.json"

# Construct a BigQuery client object.
client = bigquery.Client()

#Select Your table in BQ
query = """
    SELECT *
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
"""
query_job = client.query(query)  # Make an API request

result = query_job.to_dataframe()  # Stores your query results to dataframe

answered Feb 9, 2022 at 4:19

Rishi

861 silver badge9 bronze badges

Collectives™ on Stack Overflow

Export Google BigQuery data to Python Pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related