Load a huge data from dask dataframe to bigquery

1
votes

I read other similar threads and searched Google to find a better way but couldn't find any solution that is acceptable in terms of performance.

I have a very large dask dataframe with 100 partitions and 57 columns. I want to move it to a bigquery table. I have tried 2 ways so far: 

 dd.compute() # to do compute and then use pandas gbq
 pd.io.gbq.to_gbq(df, BQ_DATASET_NAME_TEMP + "." + BQ_EVENT_TEMP_TB, BQ_PROJECT_NAME, if_exists='append')

It takes so long (more than 2 hours)

I also tried to use:

dd.to_json('gs://dask_poc/2018-04-18/output/data-*.json')

to move dask dataframe to GCS and then move files to bigquery. It take 3 hours to only move dataframe to GCS. Just thought there should be a better way to do it. Any thought?

1
pythongoogle-bigquerybigdatadaskdask-distributed
What is the size of your data? As for the to_gbq method, I think it does not process in parallel the job so this is probably already best performance you'd get. - Willian Fuks
@WillianFuks 40 million rows, 57 columns - MT467

1 Answers

0
votes

Your best format for this kind of operation is very likely parquet (df.to_parquet), and then send these files. You could try to use df.map_partitions to send the pieces in parallel using to_gbq, but the basic procedure there is slow, and I don't know to what extent BigQuery will allow simultaneous block writes to a table.