A Comparison of JDBC Drivers for BigQuery

* Note the change in scale for the Heap graph.

The CData Driver averages near 700 MB of heap usage for the first few minutes and then uses around 250 MB for the remainder of the time. Based on the CPU usage graph, we can see that the CData Driver is consistently using around 6% or 7% of the CPU available, consistently processing the data as fast as it is returned.

The native driver averages near 700 MB of heap usage and, unlike the CData driver, maintains a high level of heap usage for most of the execution time, dropping to using around 350 MB for the last fourth of the execution. The Google Driver uses around 5% of the CPU available for the length of the execution time.

The competitor driver averages around 70 MB of heap usage but is relatively inconsistent in its heap usage (compared to the CData and Google Drivers). The competitor driver uses less than 5% of the CPU available for the length of the execution time.

Million Row Challenge Revisited: 3.5x Faster

In October of 2017, we put the CData JDBC Driver to the task of inserting one million rows (a task the native driver was not even able to perform). Our updates to the Driver since then have also improved the write speed, now taking (on average) 5 minutes 47 seconds to insert the data (with 19 columns in each row). This makes the new CData JDBC Driver over 3.5x faster at inserts than previously.

In comparison, neither the Google Driver nor the Competitor driver are able to execute high-performance INSERT statements. The drivers do not implement large-scale ingestion mechanisms. The drivers are able to execute a small number of INSERT statements, but any attempts to load large data sets cause you to hit the rate limits for the BigQuery Data Manipulation Language.¹

We used the same simple Java program as before to add the rows to a copy of the trips table from the nyc_tlc.yellow public dataset³. For our testing, we inserted the data in 100 batches of 10,000 rows.

Sample Code

//one batch
Connection connection = DriverManager.getConnection("jdbc:googlebigquery:InitiateOAuth=GETANDREFRESH;QueryPassthrough=false;ProjectId=" + projectId + ";DatasetId=" + datasetId + ";Timeout=240;UseLegacySQL=false;");
String cmd = "INSERT INTO TestDataset.nyc_yellow_trips_copy (vendor_id, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude, rate_code, passenger_count, trip_distance, payment_type, fare_amount, extra, mta_tax, imp_surcharge, tip_amount, tolls_amount, total_amount, store_and_fwd_flag) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = connection.prepareStatement(cmd);
 
for (int row = 0; row < rows.length; row++){
  String[] line = rows[row].split(",");
  for (int i = 0 ; i < line.length; i++) {
    //add parameters based on datatype
    if ( (i < 3) || (i ==7) || (i==10) || (i==18) ) {
      pstmt.setString(i + 1, line[i]);
    } else if (i == 8) {
      pstmt.setInt(i+1, Integer.parseInt(line[i]));                
    } else {
      pstmt.setDouble(i+1, Double.parseDouble(line[i]));
    }
  }
  pstmt.addBatch();
}

int[] affected = pstmt.executeBatch();

Results

Conclusion

The CData Driver's performance has improved since our last comparison and continues to offer better querying of large datasets over the Google-supported driver and the competitor driver, processing the largest dataset 54% faster by making better use of the available client resources read and process data as quickly as possible.

When it comes to inserting data, the CData Driver stands alone in its ability to rapidly insert large sets of data. In our previous article, we were able to insert 1 million rows in around 20 minutes. Now the driver performs the same task in less than 6 minutes.

Our developers have labored to optimize the performance in processing the results returned by Google to the point that the drivers seem to only be hindered by web traffic and server processing times. The same effort has been put into making the most of the BigQuery Write functionality, making the CData Drivers the best drivers for both reading from and writing to Google BigQuery.

References

1. Google Drivers
2. CData JDBC Drivers
3. City of New York Taxi Trip Public Dataset