BigQuery - set "" as Null

7
votes

I get csv files where string null value expressed as "", when I load the file to BigQuery the value of the field is empty string and not Null.

Is there a way to set BigQuery to see "" as Null value?

3
google-bigquery

3 Answers

9
votes

Adding this for those who might still need this. There is a NULLIF function that can do this easily.

NULLIF(column_name,'')

What this does is if column_name is an empty string i.e. '', it puts NULL, if not, it puts the column_name value.

3
votes

It seems to me that it might be easiest to simply remove all instances of "" from the original file. (Through find and replace.)

Otherwise, once you have loaded the table into BigQuery, you could select all columns, apply case when column_name = '' then null else column_name end to the relevant column, and save the results as a new table.

0
votes

Are you using the command line tool that comes with the Google Cloud SDK (https://cloud.google.com/sdk/downloads)? If so, check out bq help load to see the parameters on the load command. One of the flags that you can pass is --null_marker.

I haven't verified whether or not this flag supports the empty string, but if Google Cloud supported it, it would be there.