BigQuery: DATETIME columns invalid when uploaded with `load_table_from_dataframe`

[tswast]

https://github.com/googleapis/google-cloud-python/issues/9920#issuecomment-566642042

[tswast]

See:

google-cloud-python/bigquery/google/cloud/bigquery/_pandas_helpers.py

Line 55 in 2dabc2d

"datetime64[ns]": "DATETIME",

and

google-cloud-python/bigquery/google/cloud/bigquery/_pandas_helpers.py

Line 79 in 2dabc2d

return pyarrow.timestamp("us", tz=None)

The fix is likely to change the pyarrow type to ns to match pandas behavior. We'll definitely want a system test to verify the fix.

[tswast]

One thing to consider: the full range of DATETIME is not supported when nanosecond precision is used.

We need to check the input precision and make sure it matches the Arrow precision we use.

[emkornfield]

@tswast I think the short term fix would be to change datetime64 to TIMESTAMP instead of DATETIME

[HemangChothani]

@tseaver pyarrow.timestamp("ns", tz=None) doesn't help to resolve the issue, I've also tried with manually generated files but unable to find the resolution, I'm not sure but there may be something related to the backend as well?

[emkornfield]

@HemangChothani Can you try changing

google-cloud-python/bigquery/google/cloud/bigquery/_pandas_helpers.py

Line 55 in 2dabc2d

"datetime64[ns]": "DATETIME",

to be TIMESTAMP.

[HemangChothani]

@emkornfield I have tried with the change and it's working fine.

…

On Fri, 20 Dec, 2019, 11:12 PM emkornfield, ***@***.***> wrote: @HemangChothani <https://github.com/HemangChothani> Can you try changing https://github.com/googleapis/google-cloud-python/blob/2dabc2dcc606e3867e25fd86ede21614cdcaaa04/bigquery/google/cloud/bigquery/_pandas_helpers.py#L55 to be TIMESTAMP. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#9996?email_source=notifications&email_token=AMAR4JVARUKEPRM3BEOMRKLQZT7W7A5CNFSM4J4PDI62YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEHNT2MQ#issuecomment-568016178>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMAR4JTTUGVZK6SBYMYVXVDQZT7W7ANCNFSM4J4PDI6Q> .

[emkornfield]

I'll let others chime in, but in the short term I think this might be a reasonable fix. The main downside is that it doesn't allow round-tripping "datetime64[ns]", but at least data is preserved.

[tswast]

If you manually specify TIMESTAMP in the job_config, does that work?

I’d prefer to keep time zone less columns mapping to DATETIME and fix the conversion.

[HemangChothani]

@tswast Yes it works if i manually specify TIMESTAMP in the job_config.

[tswast]

OK, thanks. Since we have a workaround, I'll drop the priority to P2.