BigQuery performance and running jobs in a batch request

Question

We are working in a Java web application with Google BigQuery and we are facing a strange behavior.

We are using query jobs in order to retrieve datas and then visualize them in some charts.

We create and add severals Jobs:

Job query1Job = startAsyncQuery(query1, "q1"+uuid);
jobMapping.put(query1Job.getJobReference().getJobId(), "q1");
runningJobs.add(query1Job);
...
Job query2Job = startAsyncQuery(query1, "q2"+uuid);
jobMapping.put(query2Job.getJobReference().getJobId(), "q2");
runningJobs.add(query2Job);
...
Job query3Job = startAsyncQuery(query1, "q3"+uuid);
jobMapping.put(query1Job.getJobReference().getJobId(), "q3");
runningJobs.add(query3Job);
...
Job query4Job = startAsyncQuery(query1, "q4"+uuid);
jobMapping.put(query4Job.getJobReference().getJobId(), "q4");
runningJobs.add(query4Job);
...
Job query5Job = startAsyncQuery(query1, "q5"+uuid);
jobMapping.put(query1Job.getJobReference().getJobId(), "q5");
runningJobs.add(query5Job);


 public Job startAsyncQuery(String query, String jobId) throws IOException {
        JobConfigurationQuery queryConfig = new JobConfigurationQuery().setQuery(query).setUseQueryCache(true);
        JobConfiguration config = new JobConfiguration().setQuery(queryConfig);
        Job job = new Job().setId(jobId).setConfiguration(config);
        Job queuedJob = this.bigquery.jobs().insert(this.projectId, job).execute();
        return queuedJob;
    }

We poll our list of running Jobs in order to retrieve datas:

  boolean isError = false;
        while (!runningJobs.isEmpty() && !isError) {
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
            }
            List<Job> tempJobs = new ArrayList<Job>();
            for (Job job : runningJobs) {
                JobReference jref = job.getJobReference();
                String jid = jobMapping.get(jref.getJobId());
                int jobState = pollJob(bigQueryManager, jref, jid);
                if (jobState == -1) {
                    System.out.println("Aborting because of error for job " + jid);
                    isError = true;
                } else if (jobState == 1) {
                    List<TableRow> rows = bigQueryManager.getQueryResults(jref);
                    if (jid.startsWith("q1")) {
                        parseQ1QueryResult(filter, metrics, metricsRTLDiv, clientList, objectList, rows);
                    } else if (jid.startsWith("q2")) {
                        parseQ2QueryResult(filter, metrics, rows);
                    } else if (jid.startsWith("q3")) {
                        parseQ3QueryResult(filter, metrics, metricsRTLDiv, rows);
                    } else if (jid.startsWith("q4")) {
                        parseQ4QueryResult(metricsRTLDiv, rows);
                    } else if (jid.startsWith("q5")) {
                        parseQ5QueryResult(metrics, rows);
                    } else {
                        System.out.println("Job finished for unknown id: " + jid);
                    }
                } else {
                    tempJobs.add(job);
                }               
            }
            runningJobs = tempJobs;
        }

The strange bahavior is that bigquery.jobs().insert.execute() takes severals seconds at each time:

Fri Mar 20 08:57:56 CET 2015 - q1
Fri Mar 20 08:57:59 CET 2015 - q2
Fri Mar 20 08:58:04 CET 2015 - q3
Fri Mar 20 08:58:09 CET 2015 - q4
Fri Mar 20 08:58:14 CET 2015 - q5 

Is it possible to put all theses queries in a batch request which will be only one HTTP request (instead of one http request per query)?

Do someone know a fast way to retrieve data from BigQuery tables when we need to have severals queries execution? Is there a way to improvise Job execution speed?

Thanks.

Answer 1

Google API Client Library for Java has Request Batching support.

While this example is for Calendar service, it can be adapted to BigQuery.

JsonBatchCallback<Calendar> callback = new JsonBatchCallback<Calendar>() {

  public void onSuccess(Calendar calendar, HttpHeaders responseHeaders) {
    printCalendar(calendar);
    addedCalendarsUsingBatch.add(calendar);
  }

  public void onFailure(GoogleJsonError e, HttpHeaders responseHeaders) {
    System.out.println("Error Message: " + e.getMessage());
  }
};

...

Calendar client = Calendar.builder(transport, jsonFactory, credential)
  .setApplicationName("BatchExample/1.0").build();
BatchRequest batch = client.batch();

Calendar entry1 = new Calendar().setSummary("Calendar for Testing 1");
client.calendars().insert(entry1).queue(batch, callback);

Calendar entry2 = new Calendar().setSummary("Calendar for Testing 2");
client.calendars().insert(entry2).queue(batch, callback);

batch.execute();