Loading python datetime into Google BigQuery

3
votes

I am loading a datetime field from Pandas into a Google BigQuery DATETIME and get the following error:

google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: Invalid datetime value 1594835746000000 for field 'my field name' of type 'INT64' (logical type 'TIMESTAMP_MICROS'): generic::out_of_range: Cannot return an invalid datetime value of 1594835746000000 microseconds relative to the Unix epoch. The range of valid datetime values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]

Inside Pandas the object is definitely a datetime.datetime object and has a valid date and when I take 1594835746000000 through https://www.epochconverter.com/ it returns a valid date.

I am loading the data into BigQuery by way of a call:

job_config = bigquery.LoadJobConfig(schema = schema_fieldlist)
job = bigquery_client.load_table_from_dataframe(df, f'{dataset}.{tablename}', job_config)
job.result()

where schema_fieldlist is an array and, for the field in question, is defined as:

bigquery.SchemaField('my field name', 'DATETIME')

I am not doing anything clever - can anyone advise if they have got this to work and how? I have seen other questions to do with returning timestamps and the change in valid range between standard and legacy SQL dialects

2
python-3.xgoogle-bigquery
I believe this might be a current ongoing issue. Check this similar issue tracker. I would suggest trying TIMESTAMP type as a workaround and see if it works for your case. - aemon4
Thank you. I agree that looks the same and switching yo time stamp works - MCFH
pandas-gbq uses CSV serialization rather than Parquet. You may consider using it as well. - Tim Swast

2 Answers

1
votes

I too had this problem with DATETIME as well as TIME (which was reported https://issuetracker.google.com/issues/169230812).

Here are the relevant package versions I am using:

   pyarrow==1.0.1
   pandas==1.1.1
   google-cloud-bigquery==1.28.0
   numpy==1.19.1

TIMESTAMP is acceptable as replacement for DATETIME although with implied timezone. It's not a good replacement for TIME however.

0
votes

I had a similar issue trying to insert a pandas datetime.time column into BigQuery using bigquery.client.Client.load_table_from_dataframe.

Error:

BadRequest: 400 Error while reading data, error message: Invalid time value 64176000000 for column 'time': generic::out_of_range: Cannot return an invalid time value of 64176000000 microseconds relative to the Unix epoch. The range of valid time values is [00:00:00, 23:59:59.999999]

I found the solution here: https://github.com/googleapis/python-bigquery/issues/382

Add source_format=bigquery.SourceFormat.CSV to the job_config:

bq = bigquery.Client()
job_config = bigquery.LoadJobConfig(source_format=bigquery.SourceFormat.CSV)
job = bq.load_table_from_dataframe(df, "wb_dev_us.time_test", job_config=job_config)
job.result()