[destination-bigquery] DESTINATION_TYPECAST_ERROR

sakent commented 1 month ago

Connector Name

destination-bigquery

Connector Version

2.6.1

What step the error happened?

During the sync

Relevant information

I'm using MSSQL as the source and Biquery as the destination. Data of type Datetimeoffset is not synced to destination. I see following in "_airbyte_meta". <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns="http://www.w3.org/TR/REC-html40">

id | _airbyte_meta | CreateDate | UpdateDate -- | -- | -- | -- 1 | {"changes":[{"change":"NULLED","field":"CreateDate","reason":"DESTINATION_TYPECAST_ERROR"},{"change":"NULLED","field":"UpdateDate","reason":"DESTINATION_TYPECAST_ERROR"}],"sync_id":0} | null | null

Data in our source: <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns="http://www.w3.org/TR/REC-html40">

id | CreateDate | UpdateDate -- | -- | -- 1 | 2024-03-18 16:01:44.6210111 -04:00 | 2024-05-02 09:21:03.7968791 -04:00

Relevant log output

airbyte-worker                    | 2024-06-10 23:00:09 destination > ERROR sync-operations-4 i.a.i.b.d.t.TyperDeduperUtil(executeTypeAndDedupe):223 Encountered Exception on unsafe SQL for stream dev_airbyte AnswerSet with suffix , attempting with error handling com.google.cloud.bigquery.BigQueryException: Query error: Invalid timestamp: '2023-10-12 14:12:14.9436517 -04:00' at [2:1]
airbyte-worker                    |     at com.google.cloud.bigquery.Job.reload(Job.java:424) ~[google-cloud-bigquery-2.37.0.jar:2.37.0]
airbyte-worker                    |     at io.airbyte.integrations.destination.bigquery.typing_deduping.BigQueryDestinationHandler.execute(BigQueryDestinationHandler.java:148) ~[io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]
airbyte-worker                    |     at io.airbyte.integrations.base.destination.typing_deduping.TyperDeduperUtil.executeTypeAndDedupe(TyperDeduperUtil.kt:219) ~[airbyte-cdk-typing-deduping-0.36.0.jar:?]
airbyte-worker                    |     at io.airbyte.integrations.destination.bigquery.operation.BigQueryStorageOperation.typeAndDedupe(BigQueryStorageOperation.kt:104) ~[io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]

Contribute

[ ] Yes, I want to contribute

evantahler commented 1 month ago

Please provide the row from your source data that is causing this problem so we can debug it.

sakent commented 1 month ago

Please provide the row from your source data that is causing this problem so we can debug it.

Data in our source: <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns="http://www.w3.org/TR/REC-html40">

id | CreateDate | UpdateDate -- | -- | -- 1 | 2024-03-18 16:01:44.6210111 -04:00 | 2024-05-02 09:21:03.7968791 -04:00

evantahler commented 1 month ago

Ah! We may have just fixed this in the mssql source - https://github.com/airbytehq/airbyte/pull/39342 - can you try the latest version please?

sakent commented 1 month ago

Ah! We may have just fixed this in the mssql source - #39342 - can you try the latest version please?

Yes, I have tried with latest MSSQL source, However I believe error I'm encountering is coming from the destination which is biguery. Both the stories are around datetimeoffset, I believe that's causing confuison.

sakent commented 1 month ago

@evantahler does it help if I share whole log file from worker?

evantahler commented 1 month ago

Yes please!

sakent commented 1 month ago

@evantahler here are the logs, thank you! logs_worker_6_10_2024.txt

evantahler commented 3 weeks ago

Closing this issue which we believe was fixed by https://github.com/airbytehq/airbyte/pull/39419. Thanks for the report!

airbytehq / airbyte