[destination-bigquery] BigQuery sync fails when destination project has a reserved name

[Elisabettatrire]

Connector Name

destination-bigquery

Connector Version

2.9.0

What step the error happened?

During the sync

Relevant information

Expected Behavior

When syncing to BigQuery destination all tables irrespective of whether their path has reserved keywords in Standard SQL should be capable of being synced.

Current Behavior

If you have a table which path has a reserved keyword in its name (e.g. "group" in project name in my case), the drop statement used to recreate Airbyte's internal tables on BigQuery uses the unqualified table name and BigQuery complains that a reserved keyword is in the table path, which leads to table drop fail. Logs below:

2024-12-17 10:04:43 platform INFO main i.a.c.i.d.s.S3StorageOperations(cleanUpBucketObject):465 Storage bucket airbyte/airbyte_internal_bronze_raw__stream__airbyte_bigquery_connection_test_d3d77a1760b7485fb85c28eabddfa2e5/ has been cleaned-up (1 objects were deleted)...
2024-12-17 10:04:43 platform INFO main i.a.i.b.d.o.AbstractStreamOperation(finalizeTable):352 Overwriting raw table for bronze._airbyte_bigquery_connection_test_d3d77a1760b7485fb85c28eabddfa2e5 because this is a truncate sync, we received a stream success message, and are using a temporary raw table.
2024-12-17 10:04:43 platform INFO main i.a.i.b.d.o.AbstractStreamOperation(finalizeTable):363 Typing and deduping disabled, skipping final table finalization. Raw records can be found at airbyte_internal.bronze_raw__stream__airbyte_bigquery_connection_test_d3d77a1760b7485fb85c28eabddfa2e5
2024-12-17 10:04:43 platform ERROR main i.a.i.d.b.BigQueryDestination(checkBigQueryCopyPermission):279 Error while cleaning up the temporary table... not throwing a new exception com.google.cloud.bigquery.BigQueryException: Syntax error: Unexpected keyword GROUP at [1:28]
	at io.airbyte.integrations.destination.bigquery.typing_deduping.BigQueryDestinationHandler.execute(BigQueryDestinationHandler.kt:150) ~[io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]
	at io.airbyte.integrations.destination.bigquery.BigQueryDestination.checkBigQueryCopyPermission(BigQueryDestination.kt:273) [io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]
	at io.airbyte.integrations.destination.bigquery.BigQueryDestination.check(BigQueryDestination.kt:115) [io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:166) [airbyte-cdk-core-0.46.0.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:119) [airbyte-cdk-core-0.46.0.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.run$default(IntegrationRunner.kt:113) [airbyte-cdk-core-0.46.0.jar:?]
	at io.airbyte.integrations.destination.bigquery.BigQueryDestinationKt.main(BigQueryDestination.kt:566) [io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]
Stack Trace: com.google.cloud.bigquery.BigQueryException: Syntax error: Unexpected keyword GROUP at [1:28]
	at io.airbyte.integrations.destination.bigquery.typing_deduping.BigQueryDestinationHandler.execute(BigQueryDestinationHandler.kt:150)
	at io.airbyte.integrations.destination.bigquery.BigQueryDestination.checkBigQueryCopyPermission(BigQueryDestination.kt:273)
	at io.airbyte.integrations.destination.bigquery.BigQueryDestination.check(BigQueryDestination.kt:115)
	at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:166)
	at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:119)
	at io.airbyte.cdk.integrations.base.IntegrationRunner.run$default(IntegrationRunner.kt:113)
	at io.airbyte.integrations.destination.bigquery.BigQueryDestinationKt.main(BigQueryDestination.kt:566)
2024-12-17 10:04:43 platform INFO main i.a.c.i.b.IntegrationRunner(runInternal):224 Completed integration: io.airbyte.integrations.destination.bigquery.BigQueryDestination
2024-12-17 10:04:43 platform INFO main i.a.i.d.b.BigQueryDestinationKt(main):567 Completed Destination : class io.airbyte.integrations.destination.bigquery.BigQueryDestination
2024-12-17 10:04:43 platform INFO Checking for optional control message...
2024-12-17 10:04:43 platform INFO Writing output of 22f6c74f-5699-40ff-833c-4a879ea40133_8_0_check to the doc store
2024-12-17 10:04:43 platform INFO Marking workload 22f6c74f-5699-40ff-833c-4a879ea40133_8_0_check as successful
2024-12-17 10:04:43 platform INFO Deliberately exiting process with code 0.[microsoft_sql_server__mssql____bigquery_soges_logs_8_txt.txt](https://github.com/user-attachments/files/18163634/microsoft_sql_server__mssql____bigquery_soges_logs_8_txt.txt)

Steps to Reproduce

Create a project that has a SQL reserved keyword in name (e.g. group)
Create a table in the project
Attempt to set up a sync to BigQuery
Run a sync and it will fail

Severity of the bug for you

High

Additional context

I found this part in the connector's code which I think is related to my problem, there it doesn't put backticks when dropping the table.

Relevant log output

{"timestamp":1734426283602,"message":"INFO main i.a.i.b.d.o.AbstractStreamOperation(finalizeTable):352 Overwriting raw table for bronze._airbyte_bigquery_connection_test_d3d77a1760b7485fb85c28eabddfa2e5 because this is a truncate sync, we received a stream success message, and are using a temporary raw table.","level":"info","logSource":"platform","caller":{"className":"io.airbyte.workers.internal.VersionedAirbyteStreamFactory","methodName":"internalLog","lineNumber":314,"threadName":"main"}}
{"timestamp":1734426283602,"message":"INFO main i.a.i.b.d.o.AbstractStreamOperation(finalizeTable):363 Typing and deduping disabled, skipping final table finalization. Raw records can be found at airbyte_internal.bronze_raw__stream__airbyte_bigquery_connection_test_d3d77a1760b7485fb85c28eabddfa2e5","level":"info","logSource":"platform","caller":{"className":"io.airbyte.workers.internal.VersionedAirbyteStreamFactory","methodName":"internalLog","lineNumber":314,"threadName":"main"}}
{"timestamp":1734426283603,"message":"ERROR main i.a.i.d.b.BigQueryDestination(checkBigQueryCopyPermission):279 Error while cleaning up the temporary table... not throwing a new exception com.google.cloud.bigquery.BigQueryException: Syntax error: Unexpected keyword GROUP at [1:28]\n\tat io.airbyte.integrations.destination.bigquery.typing_deduping.BigQueryDestinationHandler.execute(BigQueryDestinationHandler.kt:150) ~[io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]\n\tat io.airbyte.integrations.destination.bigquery.BigQueryDestination.checkBigQueryCopyPermission(BigQueryDestination.kt:273) [io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]\n\tat io.airbyte.integrations.destination.bigquery.BigQueryDestination.check(BigQueryDestination.kt:115) [io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]\n\tat io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:166) [airbyte-cdk-core-0.46.0.jar:?]\n\tat io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:119) [airbyte-cdk-core-0.46.0.jar:?]\n\tat io.airbyte.cdk.integrations.base.IntegrationRunner.run$default(IntegrationRunner.kt:113) [airbyte-cdk-core-0.46.0.jar:?]\n\tat io.airbyte.integrations.destination.bigquery.BigQueryDestinationKt.main(BigQueryDestination.kt:566) [io.airbyte.airbyte-integrations.connectors-destination-bigquery.jar:?]\n\nStack Trace: com.google.cloud.bigquery.BigQueryException: Syntax error: Unexpected keyword GROUP at [1:28]\n\tat io.airbyte.integrations.destination.bigquery.typing_deduping.BigQueryDestinationHandler.execute(BigQueryDestinationHandler.kt:150)\n\tat io.airbyte.integrations.destination.bigquery.BigQueryDestination.checkBigQueryCopyPermission(BigQueryDestination.kt:273)\n\tat io.airbyte.integrations.destination.bigquery.BigQueryDestination.check(BigQueryDestination.kt:115)\n\tat io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:166)\n\tat io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:119)\n\tat io.airbyte.cdk.integrations.base.IntegrationRunner.run$default(IntegrationRunner.kt:113)\n\tat io.airbyte.integrations.destination.bigquery.BigQueryDestinationKt.main(BigQueryDestination.kt:566)\n","level":"error","logSource":"platform","caller":{"className":"io.airbyte.workers.internal.VersionedAirbyteStreamFactory","methodName":"internalLog","lineNumber":310,"threadName":"main"}}
{"timestamp":1734426283604,"message":"INFO main i.a.c.i.b.IntegrationRunner(runInternal):224 Completed integration: io.airbyte.integrations.destination.bigquery.BigQueryDestination","level":"info","logSource":"platform","caller":{"className":"io.airbyte.workers.internal.VersionedAirbyteStreamFactory","methodName":"internalLog","lineNumber":314,"threadName":"main"}}
{"timestamp":1734426283605,"message":"INFO main i.a.i.d.b.BigQueryDestinationKt(main):567 Completed Destination : class io.airbyte.integrations.destination.bigquery.BigQueryDestination","level":"info","logSource":"platform","caller":{"className":"io.airbyte.workers.internal.VersionedAirbyteStreamFactory","methodName":"internalLog","lineNumber":314,"threadName":"main"}}
{"timestamp":1734426283606,"message":"Checking for optional control message...","level":"info","logSource":"platform","caller":{"className":"io.airbyte.connectorSidecar.ConnectorMessageProcessor","methodName":"updateConfigFromControlMessage","lineNumber":232,"threadName":"main"}}
{"timestamp":1734426283613,"message":"Writing output of 22f6c74f-5699-40ff-833c-4a879ea40133_8_0_check to the doc store","level":"info","logSource":"platform","caller":{"className":"io.airbyte.connectorSidecar.ConnectorWatcher","methodName":"saveConnectorOutput","lineNumber":164,"threadName":"main"}}
{"timestamp":1734426283716,"message":"Marking workload 22f6c74f-5699-40ff-833c-4a879ea40133_8_0_check as successful","level":"info","logSource":"platform","caller":{"className":"io.airbyte.connectorSidecar.ConnectorWatcher","methodName":"markWorkloadSuccess","lineNumber":169,"threadName":"main"}}
{"timestamp":1734426283727,"message":"Deliberately exiting process with code 0.","level":"info","logSource":"platform","caller":{"className":"io.airbyte.connectorSidecar.ConnectorWatcher","methodName":"exitProperly","lineNumber":217,"threadName":"main"}}
{"timestamp":1734426283727,"message":"","level":"info","logSource":"platform","caller":{"className":"io.airbyte.commons.io.LineGobbler","methodName":"voidCall","lineNumber":177,"threadName":"pool-6-thread-1"}}
{"timestamp":1734426283728,"message":"----- END CHECK -----"

Contribute

• Yes, I want to contribute

[marcosmarxm]

Thank you for bringing this issue to our attention. We acknowledge the problem of BigQuery syncs failing when the destination project has a reserved name. I have shared this with the destination team so they can review it and discuss potential future solutions.