[BigQuery: create(jobInfo)] QueryJob execution status is "DONE" but the destination table isn't created

[sherrywan629]

Thanks for stopping by to let us know something could be better!

PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.

Please run down the following list and make sure you've tried the usual "quick fixes":

• Search the issues already opened: https://github.com/googleapis/java-bigquery/issues
• Check for answers on StackOverflow: http://stackoverflow.com/questions/tagged/google-cloud-platform

If you are still having issues, please include as much information as possible:

Environment details

1. Specify the API at the beginning of the title. For example, "BigQuery: ...").
   BigQuery: create(jobInfo), with
   JobInfo jobInfo = JobInfo.newBuilder(queryConfig).setJobId(jobId).build();, so query config and job Id are generated in our code.
2. OS type and version: MacOs Sequoia 15.2
3. Java version: java 21
4. version(s):

Steps to reproduce

To include more details:
In our query job, what we do is we construct a SQL command which will select a bunch of records from a table, then we'll construct a QueryJobConfiguration in our code like this:

      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(sqlString)
          .setDefaultDataset(datasetId)
          .setDestinationTable(tempUnpartitionedTable)
          .build(); 
      JobInfo jobInfo = JobInfo.newBuilder(queryConfig).setJobId(jobId).build();

In 99% the case, it works very well. But recently we've hit some corner cases, where the queryJob's status returned as "DONE", however the destination table isn't created.
We made sure that the sql returns valid records so there're valid records to insert into destination table
We also leveraged bq show and confirmed that the query job "recordsRead":"1000","recordsWritten":"1000" are valid and look correct, but the destination table isn't created.

Code example

Provided above but let me know if you need more details.

Stack trace

A sample job Id, which has a DONE status but not bringing the selected records into destination table: copyToTempUnpartitionedTable_reconciliation-development_development_master_cleansed_payment_events_1_2024-04-09_00_0_1

External references such as API reference guides

Any additional information below

Following these steps guarantees the quickest resolution possible.

Thanks!

[whuffman36]

Thanks for filing this issue @sherrywan629 . Just to clarify, are you explicitly creating the tempUnpartitionedTable table before sending the job to BigQuery, or are you expecting that the table be created implicitly during the job?

Also, when you mention that bq shows "recordsRead":"1000","recordsWritten":"1000", can you see where these records are being written to? If the destination table is not created, are the records written to an anonymous (unnamed) table?

[sherrywan629]

Hello @whuffman36 - Thank you so much for getting back to me! Truly appreciate!

are you explicitly creating the tempUnpartitionedTable table before sending the job to BigQuery, or are you expecting that the table be created implicitly during the job?

The latter - We have only TableId generated as tempUnpartitionedTable on our own, and that table itself would be created implicitly during the job.

can you see where these records are being written to?

I'm not sure if any anonymous table is being involved after calling the BigQuery API to create the job. As mentioned in the problem statement, we've been using this approach below

      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(sqlString)
          .setDefaultDataset(datasetId)
          .setDestinationTable(tempUnpartitionedTable)
          .build(); 
      JobInfo jobInfo = JobInfo.newBuilder(queryConfig).setJobId(jobId).build();

      ....
      return bigQuery.create(jobInfo);

Which basically leverages QueryJobConfiguration.Builder#setDestinationTable(TableId destinationTable) (google official doc) to put our query results into the destination table.
I would assume the final destination of this step will be tempUnpartitionedTable, which is where the records are being written to.
I've attached the full job info for your reference in case it's helpful.

full_job_info.json

Please let me know if additional info is needed on our end, thank you again for all your help! :)

[whuffman36]

Sorry for the delayed response @sherrywan629. Unfortunately I have not been able to repeat the issue. You mentioned that 99% of the time it works as expected - are there any noticeable changes in the log output in the cases that the destination table is not created? Are you able to provide us with any logs for this case?

Also, how are you checking for the job status to know whether or not it is marked as "DONE"? There are a few "hidden" behaviors that could explain the issue:

Job.isDone():
As per the comment, this function returns true if the job does not exist. This could be due to the job failing to be created, or due to a delay in creating the job in the backend, whether due to scheduling or network latency. It's possible that your code checks this function and returns true before the job is created in the BQ backend (if this method is called).

Job.waitFor():
A common misconception about this method is that it updates the internal state of the calling Job object. This is not the case. Many users simply call Job.waitFor() without using the return value, but the return value is the Job object that will be updated. It's possible that your code is checking the outdated Job object. It should be used like the following example:

Job completedJob = job.waitFor();
if (completedJob == null) {
  // job no longer exists
} else if (completedJob.getStatus().getError() != null) {
   // job failed, handle error
} else {
   // job completed successfully
}

The comment above the function has several good examples.

It might be worth it to make sure if you are checking the job status correctly.

[sherrywan629]

Thanks @whuffman36 ! Unfortunately I also do not have the logs available any more (since it's already passed the retention time)
here's the logic around how we're checking the job status leveraging Job.isDone() and Job.waitFor(), please let me know if it's helpful.

@VisibleForTesting Job idempotentRun(JobInfo jobInfo) {
      String jobId = jobInfo.getJobId().getJob();

      Pattern jobIdFormat = Pattern.compile("[\\w-]+_\\d+");
      Matcher matcher = jobIdFormat.matcher(jobId);
      Preconditions.checkState(
          matcher.matches(),
          "Job id must end in an underscore and an integer"
      );

      Job queryJob = this.bigQueryFacade.create(jobInfo);

      if (queryJob.getStatus().getState() == JobStatus.State.DONE) {
        if (queryJob.getStatus().getError() != null) {
          long fiveMinsAgo = this.clock.currentTimeMillis() - 5 * MILLISECONDS_PER_MINUTE;
          long endTime = queryJob.getStatistics().getEndTime();
          if (endTime < fiveMinsAgo) {
            // retry if job completed with errors more than 5 minutes ago
            String oldJobId = jobInfo.getJobId().getJob();
            int attemptCounter = Integer.parseInt(
                oldJobId.substring(oldJobId.lastIndexOf('_') + 1)
            );
            if (attemptCounter < MAX_ATTEMPTS) {
              attemptCounter++;
              JobId newJobId = JobId.of(
                  oldJobId.substring(0, oldJobId.lastIndexOf('_') + 1) + attemptCounter
              );
              return idempotentRun(jobInfo.toBuilder().setJobId(newJobId).build());
            }
          }
        }
      } else {
        try {
          queryJob = queryJob.waitFor();
        } catch (BigQueryException | InterruptedException e) {
          throw new RuntimeException(format("Job id %s failed", jobId), e);
        }
      }

      if (queryJob.getStatus().getError() != null) {
        throw new RuntimeException(
            format("Job id %s failed: %s", jobId, queryJob.getStatus().getError().toString())
        );
      } else {
        logger.info(format("Job id %s completed", jobId));
      }

      return queryJob;
    }

[jinseopkim0]

Thanks for providing details. If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.