Empty strings inconsistently converted to NULL's when using df.to_gbq()

[jerome-asselin-ivadolabs]

Here is a reproducible example of inconsistent conversion of empty strings to NULL when exporting to Bigquery.

import pandas as pd
from google.cloud import bigquery

df1 = pd.DataFrame.from_dict([{'a': ""}])
df2 = pd.DataFrame.from_dict([{'a': "", 'b': ""}])

df1.to_gbq('aa_temp.df1') # Keeps empty string, as expected.
df2.to_gbq('aa_temp.df2') # Converts empty strings to null values.

client = bigquery.Client()
print(client.list_rows('aa_temp.df1').to_dataframe())
print(client.list_rows('aa_temp.df2').to_dataframe())

Actual Output:

  a
0  
      a     b
0  None  None

Expected Output:

  a
0  
      a     b
0    

Using python 3.7, pandas 1.2.4, pandas-gbq 0.15.0.

[tseaver]

Running through this in the debugger, I found that BigQuery correctly infers schema for both dataframes (all fields are STRING w/ NULLABLE). The difference between the two dataframes seems to be in how dataframe.to_csv is generating the file used to upload the data:

>>> import pandas as pd
>>> df1 = pd.DataFrame.from_dict([{'a': ""}])
>>> df1.to_csv(None, index=False, header=False)
'""\n'
>>> df2 = pd.DataFrame.from_dict([{'a': "", 'b': ""}])
>>> df2.to_csv(None, index=False, header=False)
',\n'

When loading the file dumped from df1 preserves the empty string, because that file has the "". When loading the file dumped from df2, which does not have the "", the columns are loaded (properly) as null values.

[tswast]

I wonder if there's a flag we can pass to to_csv to force it to create a null value indicator? I think we'd also have to set https://googleapis.dev/python/bigquery/latest/generated/google.cloud.bigquery.job.LoadJobConfig.html#google.cloud.bigquery.job.LoadJobConfig.null_marker in that case.

[tswast]

Per https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoad.FIELDS.null_marker

The default value is the empty string

So that explains the current behavior.

[tswast]

The other option we can do is to start sending Parquet data instead of CSV, as that should correctly disambiguate between null and empty string in all cases.

[tswast]

Confirmed with a test case in #413 that using Parquet as the serialization format addresses this issue.