Load a huge data from BigQuery to python/pandas/dask

Question

I read other similar threads and searched Google to find a better way but couldn't find any workable solution.

I have a large large table in BigQuery (assume inserting 20 million rows per day). I want to have around 20 million rows of data with around 50 columns in python/pandas/dask to do some analysis. I have tried using bqclient, panda-gbq and bq storage API methods but it takes 30 min to have 5 millions rows in python. Is there any other way to do so? Even any Google service available to do similar job?

I've developed a python package (with 100% test coverage): [google-pandas-load.readthedocs.io/en/latest](https://google-pandas-load.readthedocs.io/en/latest/) that downloads data quickly. — augustin-barillec, Feb 15 '20 at 16:10

score 6 · Accepted Answer · answered Mar 07 '19 at 02:43

6

Instead of querying, you can always export stuff to cloud storage -> download locally -> load into your dask/pandas dataframe:

Export + Download:

bq --location=US extract --destination_format=CSV --print_header=false 'dataset.tablename' gs://mystoragebucket/data-*.csv &&  gsutil -m cp gs://mystoragebucket/data-*.csv /my/local/dir/

Load into Dask:

>>> import dask.dataframe as dd
>>> df = dd.read_csv("/my/local/dir/*.csv")

Hope it helps.

answered Mar 07 '19 at 02:43

khan

7,005
15
48
70

trying it, the total files size is around 45G – MT467 Mar 07 '19 at 17:43
yeah, also dask is much better doing read_json from google storage, so no need of copying on local disk. also please update csv with json, csv was not working – MT467 Mar 07 '19 at 19:36
do you know if dask distributed splits the task of read_csv() across workers or not? – MT467 Apr 16 '19 at 22:17
It probably does. You have to check its documentation. – khan Nov 29 '20 at 10:43
If file size is important using PARQUET would probably help. – Micah Kornfield Nov 17 '21 at 10:21
pandas is not viable if row size is in billions. – Nguai al Jun 12 '22 at 15:57

score 2 · Answer 2 · answered Mar 07 '19 at 00:38

2

First, you should profile your code to find out what is taking the time. Is it just waiting for big-query to process your query? Is it the download of data> What is your bandwidth, what fraction do you use? Is it parsing of that data into memory?

Since you can make SQLAlchemy support big-query ( https://github.com/mxmzdlv/pybigquery ), you could try to use dask.dataframe.read_sql_table to split your query into partitions and load/process them in parallel. In case big-query is limiting the bandwidth on a single connection or to a single machine, you may get much better throughput by running this on a distributed cluster.

Experiment!

answered Mar 07 '19 at 00:38

mdurant

27,272
5
45
74

Downvoting since the suggestion is going to run `n` scans of the bq table – Maximilian Apr 22 '20 at 00:52
Not if you partitioning matches the sharding model of the data – mdurant Apr 22 '20 at 12:28
Yes good point; really worth mentioning these points, though. – Maximilian Apr 22 '20 at 17:23

score 1 · Answer 3 · answered Mar 06 '19 at 23:11

1

Some options:

Try to do aggregations etc. in BigQuery SQL before exporting (a smaller table) to Pandas.
Run your Jupyter notebook on Google Cloud, using a Deep Learning VM on a high-memory machine in the same region as your BigQuery dataset. That way, network overhead is minimized.

answered Mar 06 '19 at 23:11

Lak

3,876
20
34

data in bigquery is not clean enough to do any sort of aggregations! regarding the second option, the script will be a daily etl job and we want to cut the cost too. That type of vm instant is too expensive for us!! – MT467 Mar 06 '19 at 23:15
"data in bigquery is not clean enough to do any sort of aggregations" This is hard to believe. Plenty of people use BigQuery to clean up data prior to performing aggregations. Since you haven't showed us what your data looks like, though, it's hard to provide advice. – Elliott Brossard Mar 07 '19 at 04:06
@elliott we want to not use bigquery (because of cost) to clean data if that makes more sense. – MT467 Mar 07 '19 at 04:25
If each row is 50 kB in size, 20 million rows would mean that the data is 1 TB in size, so scanning and cleaning up the entire table each day would cost $5. Are the rows very large? – Elliott Brossard Mar 07 '19 at 04:29

score 0 · Answer 4 · answered Mar 07 '19 at 01:45

Probably you want to export the data to Google Cloud Storage first, and then download the data to your local machine and load it. Here are the steps you need to take:

Create an intermediate table which will contain the data you want to export. You can do select and store to the intermediate table.
Export the intermediate table to Google Cloud Storage, to JSON/Avro/Parquet format.
Download your exported data and load to your python app.

Besides downloading the data to your local machine, you can leverage the processing using PySpark and SparkSQL. After you export the data to Google Cloud Storage, you can spin up a Cloud Dataproc cluster and load the data from Google Cloud Storage to Spark, and do analysis there.

You can read the example here

https://cloud.google.com/dataproc/docs/tutorials/bigquery-connector-spark-example

and you can also spin up Jupyter Notebook in the Dataproc cluster

https://cloud.google.com/dataproc/docs/tutorials/jupyter-notebook

Hope this helps.

score 0 · Answer 5 · answered Nov 15 '21 at 19:38

0

A couple years late, but we're developing a new dask_bigquery library to help easily move back and forth between BQ and Dask dataframes. Check it out and let us know what you think!

answered Nov 15 '21 at 19:38

bnaul

17,288
4
32
30

I only see the connection piece. What I need to know is to implement BQ SQL to dask. thanks – Nguai al Jul 23 '22 at 20:34

Load a huge data from BigQuery to python/pandas/dask

5 Answers5