We use the google-cloud-bigquery python library to query Bigquery and process the results in our python script. The processing portion transforms the data and enriches it and in the end creates JSON objects.
This is how we use the BQ library in our script (simplified):
import google.cloud.bigquery
client = bigquery.Client()
query = "SELECT col1,col2,... FROM <table>"
queryjob = client.query(query)
result_set = queryjob.result(page_size=50000)
for page in result_set.pages:
transform_records()
In general and for moderate sized tables, this works just fine. However, we run into a performance issue when querying a table that returns 11 mio records of ~3,5 GB size in total. Even if we leave out the processing, just fetching the pages takes ~ 80 minutes (we did not really observe any significant differences when running it locally or in a VM / cluster that resides in the same region as the bigquery dataset).
Any ideas on how to reduce the loading time?
What we tried:
- Varying the page size: The obvious assumption that larger pagesizes hence less pages reduce http overhead holds true. However we noticed that setting page size to above 8.500 did not have any effect (the max number of records returned by the API per page were ~8.500). Still this does only account for improvement in range of a few percent of loading time
- Iterating over the result set records instead of pages: Gave us roughly same performance
- Separating the data loading and the processing from each other by putting the loading portion into a background thread, using a multiprocessing queue for sharing the data with the processing workers - obviously no impact on the pure time spent on receiving the data from BQ
- Trying to fetch multiple pages in parallel - we think this could help reducing the loading time drastically, but did not manage to do so
What we did not try:
- Using the BQ storage API, or rather a method that fetches data from BQ using this API (i.e. result_set.to_arrow_iterable / to_dataframe_iterable): We like to avoid the mess of having to deal with data type conversions, as the output of the processing part will be a JSON object
- Using the BQ Rest API directly w/o comfort that the bigquery lib offers in order to be able to fetch multiple pages of the result set simultaneously: This seems somewhat complicated and we are not even sure if the API itself allows for this simultaneous access of pages
- Exporting the data to GCS first by using client.extract_table-method: We used this approach in other use cases and are aware that fetching data from GCS is way faster. However, as we get acceptable performance for most of our source tables, we'd rather avoid this extra step of exporting to GCS