[destination-bigquery] can't parse correctly datetimeoffset

[goldengrisha]

Connector Name

destination-bigquery

Connector Version

2.3.17

What step the error happened?

During the sync

Relevant information

Please help, gb connector can't parse correctly timestamps from MSSQL.

Relevant log output

2023-12-05 15:22:15 ERROR i.a.i.b.d.t.TypeAndDedupeTransaction(executeTypeAndDedupe):42 - Encountered Exception on unsafe SQL for stream ___ Users with suffix _airbyte_tmp, attempting with error handling
2023-12-05 15:22:15 destination > com.google.cloud.bigquery.BigQueryException: Query error: Invalid timestamp: '2022-07-07 09:00:04.9610582 +00:00' at [2:1]
2023-12-05 15:22:15 destination >         at com.google.cloud.bigquery.Job.reload(Job.java:424) ~[google-cloud-bigquery-2.31.1.jar:2.31.1]
2023-12-05 15:22:15 destination >         at io.airbyte.integrations.destination.bigquery.typing_deduping.BigQueryDestinationHandler.execute(BigQueryDestinationHandler.java:110) ~[io.airbyte.airbyte-integrations.connectors-destination-bigquery-0.50.35.jar:?]
2023-12-05 15:22:15 destination >         at io.airbyte.integrations.base.destination.typing_deduping.TypeAndDedupeTransaction.executeTypeAndDedupe(TypeAndDedupeTransaction.java:39) ~[airbyte-cdk-typing-deduping-0.4.11.jar:?]
2023-12-05 15:22:15 destination >         at io.airbyte.integrations.base.destination.typing_deduping.DefaultTyperDeduper.lambda$typeAndDedupeTask$2(DefaultTyperDeduper.java:220) ~[airbyte-cdk-typing-deduping-0.4.11.jar:?]
2023-12-05 15:22:15 destination >         at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768) [?:?]
2023-12-05 15:22:15 destination >         at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
2023-12-05 15:22:15 destination >         at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
2023-12-05 15:22:15 destination >         at java.base/java.lang.Thread.run(Thread.java:833) [?:?]

Contribute

• [ ] Yes, I want to contribute

[marcosmarxm]

Can you update the title to be more informative about what connector the problem is happening? (take a look in other issues open).

[evantahler]

This should be fixed now with the latest versions of the connetors

[sakent]

Hi @evantahler I'm using latest version of MSSQL(4.0.23) and Bigquery (2.4.20) connectors and still seeing this error

[sakent]

Hi @evantahler and @marcosmarxm, I'm trying to debug this issue following the doc, however when I run

• DEBUG_CONTAINER_IMAGE="destination-bigquery:5006" VERSION="dev" docker compose -f docker-compose.yaml -f docker-compose.debug.yaml up - I'm seeing following error and observed that dev tag is not available in docker hub - Error response from daemon: manifest for airbyte/proxy:dev not found: manifest unknown: manifest unknown -

• when I run the same command without VERSION="dev", I see following errors in worker logs, as well as I have observed that destination-bigquery container is not started after trigger a sync.

> `2024-05-17 17:38:12 [46mplatform[0m > WARNING: Published ports are discarded when using host network mode
> 2024-05-17 17:38:13 [46mplatform[0m > Picked up JAVA_TOOL_OPTIONS: -agentlib:jdwp=transport=dt_socket,server=y,suspend=y,address=*:5006
> 2024-05-17 17:38:13 [46mplatform[0m > ERROR: transport error 202: bind failed: Address already in use
> 2024-05-17 17:38:13 [46mplatform[0m > ERROR: JDWP Transport dt_socket failed to initialize, TRANSPORT_INIT(510)
> 2024-05-17 17:38:13 [46mplatform[0m > JDWP exit error AGENT_ERROR_TRANSPORT_INIT(197): No transports initialized [src/jdk.jdwp.agent/share/native/libjdwp/debugInit.c:700]
> 2024-05-17 17:38:13 [46mplatform[0m > Check connection job subprocess finished with exit code 2`

• I have never worked with docker before, so I'm not sure if this is specific to my local environment or not, any help is much appreciated.

[evantahler]

If you want to run a dev image of a container, choose that in the settings page UI.

Setting VERSION="dev" on the command line is how you run a dev version of the platform images, probably not what you are trying to achieve.

[evantahler]

Closing as duplicate of https://github.com/airbytehq/airbyte/issues/36225

[sakent]

@evantahler Tried the latest connectors for MSSQL and Biquery, I don't see error from this story #36225, However data of type datetimeoffset is not synced. Perhaps we need to re open this story , In addition to the logs I shared previously, I also see following in _airbyte_meta, which says there is a problem in destination connector. Please let me know if I can provide any additional details

{"changes":[{"change":"NULLED","field":"CreateDate","reason":"DESTINATION_TYPECAST_ERROR"},{"change":"NULLED","field":"UpdateDate","reason":"DESTINATION_TYPECAST_ERROR"}],"sync_id":0}

[evantahler]

Can you please file a new issue, and include the full contents of the row that produced that error?

[sakent]

@evantahler created #39386