Google BigQuery pagination does not return value I specified

user_5880486 0
Viewed 45

I have a bigQuery query I am executing via Java client. I am asking for 50k batches, but after first iteration, big query returns random number of batches. Does anyone knows why is that?

 var queryConfig = QueryJobConfiguration.newBuilder(query)
                .setWriteDisposition(JobInfo.WriteDisposition.WRITE_TRUNCATE)
                .setDestinationTable(tableId)
                .setAllowLargeResults(true)
                .build();

        JobId jobId = JobId.of(UUID.randomUUID().toString());
        Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());
        TableResult results = queryJob.getQueryResults(BigQuery.QueryResultsOption.pageSize(50000));

        //First Page
        results
                .getValues()
                .forEach(row -> {
                    //System.out.println(row);
                    //some job
                });


        long i = 0;
        long count = 0;

        while (results.hasNextPage()) {
            logger.info("Paginated result size: {} in page {}", results.getTotalRows(), results.getNextPageToken());
            results = results.getNextPage();
            count += results.getValues().spliterator().getExactSizeIfKnown();
            results
                    .getValues()
                    .forEach(row -> {
                        //System.out.println(row);
                        //some job
              });
            logger.info("iteration {}. Progress: {} / {}", i, count, results.getTotalRows());
            i++;
        }
    }

Logs:

iteration 0. Progress: 38720 / 158226305
iteration 1. Progress: 69091 / 158226305
iteration 2. Progress: 88977 / 158226305
iteration 3. Progress: 114797 / 158226305
...
google-bigquery java
1 Answers

Since you are using jobs.getQueryResult, from the doc :

jobs.getQueryResult can return 20 MB of data unless explicitly requested more through support.

Looking at your total number of rows (158m), I am assuming each page has more than 20MB data therefore it shows only rows up to that size. You may try to reduce the page size(maybe around 30K) and check.

An Example :

String query ="select commit, author, repo_name from `bigquery-public-data.github_repos.commits` "
                     +"where subject like '%bigquery%' or subject like '%github%' ";

QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query)
        .setWriteDisposition(JobInfo.WriteDisposition.WRITE_TRUNCATE)
        .setDestinationTable(tableId)
        .build();

JobId jobId = JobId.of(UUID.randomUUID().toString());
Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

TableResult results = queryJob.getQueryResults(BigQuery.QueryResultsOption.pageSize(PAGE_SIZE));
//returns 4.5m records
//rest of the code is same

Page size = 50K

iteration 0. Progress: 35693 / 4535455===========> 35693 //no of rows for this iteration
iteration 1. Progress: 70713 / 4535455===========> 35020
iteration 2. Progress: 102060 / 4535455===========> 31347
iteration 3. Progress: 128034 / 4535455===========> 25974
...

Page Size = 20K

iteration 0. Progress: 20000 / 4535455===========> 20000
iteration 1. Progress: 40000 / 4535455===========> 20000
iteration 2. Progress: 60000 / 4535455===========> 20000
iteration 3. Progress: 80000 / 4535455===========> 20000
iteration 4. Progress: 100000 / 4535455===========> 20000
...

Also as you are using allowLargeResults with a specific destination I think you should go through this public documentation regarding the same if not done already.

N.B If your goal is to process as much data as possible in a single iteration you may follow this similar StackoverFlow post.

user_191891830
Related