Why wont my Bigquery accept dates as dates and will only accept them as strings from Python?

Question

I have a python script using simple salesforce and I'm uploading into BQ

I broke down the query to just say 'select CloseDate , LastModifiedDate from xxxx'

then I uploaded it into BQ using 'from google.cloud import bigquery'

in my job_config, I can tell python the following and everything is fine in my schema bigquery.SchemaField("CloseDate","STRING", mode="NULLABLE") bigquery.SchemaField("LastModifiedDate","TIMESTAMP", mode="NULLABLE")

but when I create a new table with CloseDate as a date and have this in my job_config, it fails bigquery.SchemaField("CloseDate","DATE", mode="NULLABLE") bigquery.SchemaField("LastModifiedDate","TIMESTAMP", mode="NULLABLE") with this error: pyarrow.lib.ArrowTypeError: object of type <class 'str'> cannot be converted to int"

Example of each field CloseDate:2015-11-16 LastModifiedDate:2022-11-17T15:20:09.000+0000

Testing I've done: if I extact it into a csv and manually upload the CSV file, it accepts it as a date. If I use the autodetect schema 'from google.cloud import bigquery', it will take CloseDate as a string.

error I get when I try to force it as a date field: pyarrow.lib.ArrowTypeError: object of type <class 'str'> cannot be converted to int"

Answer 1

The issue you're encountering seems to be related to the data type mismatch when specifying the schema for your BigQuery table.

When you try to use bigquery.

SchemaField("CloseDate","DATE", mode="NULLABLE") 

and the data for CloseDate is in the format '2015-11-16', BigQuery expects it to be of DATE data type. However, if the schema is mismatched, it might lead to errors, as it's trying to convert the string date into an integer.

Make sure your schema matches the actual data type of the field. In your case, since the data format for CloseDate is in 'YYYY-MM-DD' format, it should indeed be specified as a DATE data type.