BigQuery: to_dataframe does not respect date fields

[QuinRiva]

Environment details

1. google-cloud-bigquery: version 1.22
2. OS type and version: Centos 7
3. Python version: 3.6.9

Steps to reproduce

1. Download a table from BigQuery to a pandas Dataframe

Code example

df = bq_client.list_rows(table).to_dataframe()
df.types

contractId int64
contractTypeId int64
affiliateId int64
invoicePeriodId int64
startDate **object**
endDate **object**
contractName object
details object
updated datetime64[ns, UTC]
created datetime64[ns, UTC]
dtype: object

Timestamps seem to work fine, but date is just being treated as a string.

Table schema is defined as:

'contract' : [
 bigquery.SchemaField("contractId", "INTEGER", mode="REQUIRED"),
 bigquery.SchemaField("contractTypeId", "INTEGER", mode="REQUIRED"),
 bigquery.SchemaField("affiliateId", "INTEGER", mode="REQUIRED"),
 bigquery.SchemaField("invoicePeriodId", "INTEGER", mode="REQUIRED"),
 bigquery.SchemaField("startDate", "DATE", mode="REQUIRED"),
 bigquery.SchemaField("endDate", "DATE", mode="NULLABLE"),
 bigquery.SchemaField("contractName", "STRING", mode="NULLABLE"),
 bigquery.SchemaField("details", "STRING", mode="NULLABLE"),
 bigquery.SchemaField("created", "TIMESTAMP", mode="REQUIRED"),
 bigquery.SchemaField("updated", "TIMESTAMP", mode="REQUIRED"),
 ]

This should have anything to do with pagination, because the startDate field is required; also the table only has 20 records.

[QuinRiva]

I'm also seeing problem using load_table_from_dataframe with datetime columns. If I specificy the schema as part of the LoadJobConfig (specifying the column as DATE, it works fine.

However if I fail to specify the schema, it identifies the column as DATETIME and creates invalid dates:

Row	| startDate	
1	| 0021-04-25T22:13:48.360448
2	| 0021-04-25T22:13:48.360448
3	| 0021-08-13T11:42:29.163840

As opposed to :

Row	| startDate	
1	| 2017-07-01
2	| 2017-07-01
3	| 2018-01-01

Pandas dtype is datetime64[ns]

[HemangChothani]

@QuinRiva I have tried to reproduce the issue related to load_table_from_dataframe but unable to do that, here is my sample code and dataframe:

contractId | contractTypeId | startDate | endDate | created
 2 | 2 | 2019-12-06 | 2019-12-06 | 2019-12-06 17:09:11.317689+00:00
 1 | 1 | 2019-12-06 | 2019-12-06 | 2019-12-06 17:09:11.317689+00:00

df.types

contractId int64
contractTypeId int64
startDate object
endDate object
created datetime64[ns, UTC]

Sample code:

from google.cloud import bigquery
client = bigquery.Client()
destination_table = bigquery.table('your-project.your_dataset.your_table')
client.load_table_from_dataframe (df,destination_table)

It creates table with the valid dates and datetime.
If it doesn't help than could you please share reproducible code?

[PaddyAlton]

I've been having the same issue with load_table_from_dataframe since updating dependencies. I have uncovered the real problem. Some of my recent timestamps throw errors like this one:

Cannot return an invalid datetime value of 1575982560000000 microseconds relative to the Unix epoch

What's actually going on here is that I have a pandas DataFrame with values like this:

'2019-12-10 12:56:00', dtype='datetime64[ns]

which, in ns, is 1575982560000000000.

In other words, somewhere between being a dataframe and ending up in BigQuery, the underlying value stored for this date is being interpreted as microseconds since the Unix epoch rather than nanoseconds!

I recently had an issue with the same code throwing errors after a pyarrow update, which might be related - I ended up fixing that one by rounding my timestamps to ms-precision before uploading to BigQuery (see https://stackoverflow.com/questions/53333742/schema-conflict-when-storing-dataframes-with-datetime-objects-using-load-table-f for an example).

EDIT: it appears that something has changed in terms of the BigQuery type being inferred from pandas. Previously, the type used was TIMESTAMP. Now, it appears to be DATETIME. I am trying to fix my specific issue by using a partial schema - in case it helps anyone else:

 job_config = LoadJobConfig()

 dt_columns = df.columns[df.dtypes == "datetime64[ns]"]

 job_config.schema = [SchemaField(col, "TIMESTAMP") for col in dt_columns]

[HemangChothani]

@tswast , @plamut please comment, is it related to pyarrow?

[tswast]

Actually, let's specify a second issue for load_table_from_dataframe. googleapis/google-cloud-python#9996

[tswast]

@QuinRiva is the behavior of to_dataframe any different when using the BigQuery Storage API?

You might also want to try setting the dtypes argument to to_dataframe. We don't have any logic to convert from the API response types to pandas dtypes unless the dtypes argument is specified.

[emkornfield]

@tswast I expect the current behavior with the BigQuery Storage API that the dtype will still be "object" but at least with Arrow (and I assume Avro) the actual objects will python datetime.date object instead of strings.

@QuinRiva could you specify which dtype/object type you are expecting for the date columns?

[HemangChothani]

@emkornfield Another issue has filed by user #136 which is duplicate of this issue.

As per comment received from user on the issue, Manually setting the dtype argument works but user wants that automated.