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.

[tswast]

For system tests of this fix, I'd like to see supported types added to https://github.com/googleapis/google-cloud-python/blob/master/bigquery/samples/load_table_dataframe.py and the tests for that sample updated to read and verify the table data.

I'll work on updating the tests for currently-supported data types.

[tswast]

Created draft PR #10028 to hopefully reproduce this issue in a system test.

[tswast]

I see in the unit tests for this that uploading DATETIME columns was blocked by https://issues.apache.org/jira/browse/ARROW-5450, so once this issue is fixed, we'll require the latest version of pyarrow to upload DATETIME columns.

[tswast]

I investigated this further by capturing the Parquet file that is generated by _pandas_helpers.dataframe_to_parquet.

client.py

            if pyarrow and job_config.schema:
                if parquet_compression == "snappy":  # adjust the default value
                    parquet_compression = parquet_compression.upper()

                _pandas_helpers.dataframe_to_parquet(
                    dataframe,
                    job_config.schema,
                    tmppath,
                    parquet_compression=parquet_compression,
                )
                import shutil
                shutil.copyfile(tmppath, "/Users/swast/Desktop/dataframe.parquet")
            else:

dataframe.parquet.zip

When I upload dataframe.parquet via the web interface, I'm not able to reproduce the issue. There must be something different with how we upload the data file from Python.

[tswast]

I'm able to reproduce this with the command-line:

bq load --source_format=PARQUET --schema=wikidata_id:STRING,title:STRING,release_year:INTEGER,length_minutes:FLOAT,release_date:TIMESTAMP,dvd_release:DATETIME my_dataset.monty_python dataframe.parquet

[tswast]

Since I'm able to reproduce this error with the command-line, I believe it is a backend issue. I've filed internal bug 147108331 for the backend engineers to investigate.

[tswast]

I was able to create a Parquet file with nanosecond-precision DATETIME columns, but the backend still multiplies the values by 1000. It seems we can't write DATETIME columns from Parquet without a backend fix.

dataframe_nanos.parquet.zip

[tswast]

After all this, I think the best option is to write datetime[ns] dtype columns as TIMESTAMP, since I'm not able to write correct DATETIME columns with parquet files. I've updated my draft PR with this workaround.

[tswast]

Talked with the backend engineers. DATETIME is not supported for uploading from Parquet (treating my request as a feature request), so changing the dtype to TIMESTAMP is the correct solution.