Update to how the BigQuery API handles null values in insert and query jobs

[Michael Manoochehri]

Hi BigQuery Developers:

Do you work with data containing null values?

We've added a new preserveNulls parameter that can be specified for query jobs via the jobs.insert or jobs.query methods. Setting this parameter to "true" will cause BigQuery to interpret null values in your tables as true null values rather than a default value such as zero or an empty string. On March 11, 2013, this behavior will become the default behavior, so we recommend setting this parameter on your queries now.

In order to use this parameter, set preserveNulls to "true" in POST requests to the jobs.insert or jobs.query methods, as in the following example:

POST https://www.googleapis.com/bigquery/v2/projects/bigquery-samples/queries
Content-Type:  application/json

{
"query": "SELECT TOP(word, 5), COUNT(*) as word_count FROM publicdata:samples.shakespeare;",
"preserveNulls": true
}


The following snippet of Java code illustrates how to add this parameter to calls to the jobs.insert method. Make sure to use the latest builds of the Google APIs Client for Java.

 public static JobReference startQuery(Bigquery bigquery,
                                       String projectId,
                                       String querySql)
                                       throws IOException {
   System.out.format("\nInserting Query Job: %s\n", querySql);

   Job job = new Job();
   JobConfiguration config = new JobConfiguration();
   JobConfigurationQuery queryConfig = new JobConfigurationQuery();
   config.setQuery(queryConfig);

   job.setConfiguration(config);
   queryConfig.setQuery(querySql);
   queryConfig.setPreserveNulls(true);

   Insert insert = bigquery.jobs().insert(projectId, job);
   insert.setProjectId(projectId);

   JobReference jobRef = insert.execute().getJobReference();

   System.out.format("\nJob ID of Query Job is: %s\n",
                     jobRef.getJobId());

   return jobRef;
 }

We welcome your feedback!

• Stack Overflow is our official support channel for developers using the Google BigQuery API - use the tag google-bigquery.
  
• To file bug reports or feature requests, visit the Google BigQuery API Issue Tracker.
  
• Add the Google Cloud Platform Developers Google+ page to your circles.
  

Thanks!

Google BigQuery Team

[Seth Hollyman]

As a followup to our original announcement, I wanted to let you know that as of Thursday 28 August 2014 we will no longer be evaluating the configuration values provided for configuration.query.preserveNulls as part of a BigQuery query job configuration.  After that date, all queries will be treated as if preserveNulls is set as true.

If you still need to mimic the original behavior of null values being replaced by default values, you may wish to leverage the IFNULL() function as documented in the BigQuery reference here.  For more context on the issue, please see the original posting in this thread.

-Seth Hollyman, on behalf of the BigQuery team