Loading BigQuery tables from large pandas DataFrames

user_13148121 0
Viewed 3503

I am trying to load a relatively large pandas dataframe df into a Google BigQuery table table_ref using the official python google-cloud-bigquery client library.

So far I have tried two different approaches:

1) load the table directly from the dataframe in memory

client = bigquery.Client()
client.load_table_from_dataframe(df, table_ref)

2) save the dataframe to a parquet file in Google Cloud Storage at the uri parquet_uri and load the table from that file: 

df.to_parquet(parquet_uri)
client = bigquery.Client()
client.load_table_from_uri(parquet_uri, table_ref)

Both approaches lead to the same error:

google.api_core.exceptions.BadRequest: 400 Resources exceeded during query execution: UDF out of memory.; Failed to read Parquet file [...]. This might happen if the file contains a row that is too large, or if the total size of the pages loaded for the queried columns is too large.

The dataframe df has 3 columns and 184 million rows. When saved to parquet file format, it occupies 1.64 GB.

Is there any way to upload such a dataframe into a BigQuery table using the official python client library?

Thank you in advance,

Giovanni

parquet pandas google-bigquery google-cloud-platform python
3 Answers

I was able to upload the large df to BigQuery by splitting it into a few chunks and loading-appending each of them to a table in BigQuery, e.g.:

client = bigquery.Client()
for df_chunk in np.array_split(df, 5):
    job_config = bigquery.LoadJobConfig()
    job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
    job = client.load_table_from_dataframe(df_chunk, table_id, job_config=job_config)
    job.result()
user_131481210

if your parquet file is already loaded on Google Cloud Storage, you can load directly into BigQuery, without a python script:

bq load \
--source_format=PARQUET \
dataset.table \
"gs://mybucket/00/*.parquet","gs://mybucket/01/*.parquet"

where:

  • mybucket is the bucket you loaded the parquet file.
  • dataset.table is your table

In this way BigQuery detects automatically the schema.

BigQuery supports the following compression codecs for data blocks in Parquet files:

  • Snappy GZip
  • LZO_1C
  • LZO_1X

You can read more at this link: https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-parquet

user_41428420

Parquet is columnar data format, which means that loading data requires reading all columns. In parquet, columns are divided into pages. BigQuery keeps entire uncompressed pages for each column in memory while reading data from them. If the input file contains too many columns, BigQuery workers can hit Out of Memory errors. If you think about increasing the alocated memory for queries, you need to read about Bigquery slots.

user_126928090
Related