Pagination should allow fetching pages in batch of N pages.

[yantrikskaran]

Currently I am facing an issue where I have to process big number of records coming as part of query result. The results are available in multiple pages.

When I am trying to process the row from result I notice that it takes around 10 seconds to load next page. I am able to process one page in a sec or 2 and then I have to wait for 10 seconds for next page. It is time consuming.

I cannot load entire result in memory as the response can be more than 10 GB and we start hitting various QUOTA limits.

It there a way i can load batch of 100 pages in memory at once and start my process and in the background next batch of 100 pages are loaded during processing on first batch of pages.

[plamut]

Just to clarify, which client library is this question about? BigQuery?

[yantrikskaran]

Just to clarify, which client library is this question about? BigQuery?

yes it is regarding Big Query and I am using google.cloud.bigquery.client.

Just add more details - below is the snippet of my code.
    client = bigquery.Client()
    select_query = client.query(query)
    result = select_query.result()
    for row in result:
         print(row)    

After printing around 1000 records I can see next record is has a gap of 10 sec. Looks like each page has around 1000 records and when it is fetching next page it is take time.

LOGS - Check below logs.

2020-05-18 03:33:36,380 MainThread (ProcessId : 7056) INFO create_tfRecords.py:(78) Completed writing to file
2020-05-18 03:33:36,380 MainThread (ProcessId : 7056) INFO util.py:(86) Memory Used By Process : 1000.03 MB
2020-05-18 03:33:47,691 MainThread (ProcessId : 7056) INFO create_tfRecords.py:(150) Start : 157053
2020-05-18 03:33:47,691 MainThread (ProcessId : 7056) INFO create_tfRecords.py:(210) year_week : 201931

[plamut]

@yantrikskaran Thanks for the report, I transferred it to the corresponding repository.

client.query() returns a QueryJob instance and the latter's result() method accepts a page_size argument.

Would increasing the page size to N * 1000 help? After all, that's effectively the same as fetching N pages of 1000 rows in a single batch.

[yantrikskaran]

@plamut Thanks a lot for the response. Let me try page size and update this thread.

One doubt - When I am going to increase the page size. I think it will add delay in loading the next page.

Example -
Suppose I have to process 500 pages, each has 1000 rows. Each row is of x MB.
Currently with default pagination - after initial page, next page is taking 10 sec.
I change the page size to 10*1000 records per page, after initial page, next page, will take more time as it has to load more rows/data at a time?

[plamut]

I change the page size to 10*1000 records per page, after initial page, next page, will take more time as it has to load more rows/data at a time?

@yantrikskaran If the delay is due to the network bottleneck, then yes, fetching a bigger page will take longer, although the total delay might be lower than individually fetching 10 pages of 1000 records.

Since fetching the data is an I/O operation, you can also parallelize it and start fetching the next page while processing the current page by moving either the processing or fetching the data to its own thread.

You might also have a look at the new (much) faster BigQuery Storage API, which will become a default in one of the next releases. For now, if it's OK to receive data in a pandas DataFrame and you have the required dependencies, you can try processing table data as a stream of DataFrames:

from google.cloud import bigquery_storage_v1
bqstorage_client = bigquery_storage_v1.BigQueryReadClient()

dataframes = client.query(query).to_dataframe_iterable(bqstorage_client=bqstorage_client)
for df in dataframes:
    # process the dataframe

[yantrikskaran]

@plamut - Yes I tried changing page size, yes it is adding delay for next page.

I had tried using to_dataframe_iterable() also and but I had an issue where I cannot convert the result to a DF as it has a type which is not supported. I don;t want to add more logic to my SQL to create a DF compatible result.

You have mentioned to "Since fetching the data is an I/O operation, you can also parallelize it and start fetching the next page while processing the current page by moving either the processing or fetching the data to its own thread."

Do you have a sample code which can help me to try this parallelization.

[yantrikskaran]

@plamut - I think there some major issue either at library or Big Query level.

My Query is suppose to return 4500 records.

query.result() implementation doesn't help. Still I can see it is loading around 730 records per page.

from google.cloud.bigquery.client import Client
client = bigquery.Client()
select_query = client.query(query)
result = select_query.result(page_size=10000, max_results=10000)

for row in result:
print(row)

I tried table.list_rows. But implementation doesn't help.

from google.cloud.bigquery.client import Client
client = bigquery.Client()
select_query = client.query(query)
result = select_query.result(page_size=10000, max_results=10000)

destination_table = select_query.destination

result = client.list_rows(destination_table, start_index=0, max_results=10000, page_size=10000)

for row in result:
print(row)

It also has same behaviour around 730 records per page.

[plamut]

@yantrikskaran 4500 records indeed doesn't sound that much, unless rows are excessively large. What's the typical row size in your data and what's the internet connection speed on the machine downloading the data?

Can you maybe also try reproducing the issue by fetching something from one of the public datasets? If yes, I can try fetching the same data and see how it behaves on my local machine.

[yantrikskaran]

@yantrikskaran 4500 records indeed doesn't sound that much, unless rows are excessively large. What's the typical row size in your data and what's the internet connection speed on the machine downloading the data?

Can you maybe also try reproducing the issue by fetching something from one of the public datasets? If yes, I can try fetching the same data and see how it behaves on my local machine.

@plamut Yes number is not high but I am sure the row size is very large. Each row may be around 2 MB or little more. It is a json row which I am constructing as part of my SQL.

Internet speed should not be an issue, I am facing the problem when running on Google Compute Engine.
My local connection speed is 100 mbps.