Hey everyone. We're trying to use the Databricks Lakehouse destination connector to get our data into Databricks. It's not working and for us it's currently not entirely clear why. We're getting the following error:

Failed to finalize copy to temp table due to: java.sql.SQLException: [Databricks][DatabricksJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: Failure to initialize configurationInvalid configuration value detected for fs.azure.account.key

We're using the Azure Blob Storage data source instead of the default S3. We can see that Airbyte is writing files into the Azure Blob Storage account correctly, but we think Databricks might have trouble accessing that same location. We have tried multiple things: • adding the Azure Blob Storage account as an "external location" in the Databricks Unity Catalog with the associated storage credential. • writing directly to the managed Azure Blob Storage account • the Databricks Access Connector has been added as a storage contributor to the storage account • all network rules are open • we've tried both the regular Azure Blob Storage and ADLS Gen2 • We've correctly set up the connection from Airbyte to the Azure Blob Storage account using the SAS token auth. • The "test connection" button in Airbyte works correctly. Any pointers? --- Actually solved this just now. Apparently you should direct Airbyte towards an actual Databricks compute cluster. Directing it towards a SQL Warehouse does NOT work. Now getting another error, which does seem to be a problem in the Databricks Lakehouse integration itself:

CREATE TABLE public._airbyte_tmp_tvd_pokemon (_airbyte_ab_id string, _airbyte_emitted_at string, `abilities` , `base_experience` , `forms` , `game_indices` , `height` , `held_items` , `id` , `is_default ` , `location_area_encounters` , `moves` , `name` , `order` , `species` , `sprites` , `stats` , `types` , `weight` ) USING csv LOCATION 'abfss:REDACTED_LOCAL_PART@REDACTED.dfs.core.windows.net/d2b9f209-e36e-451c-9a66-6d29a712c699/public/_airbyte_tmp_tvd_pokemon/' options ("header" = "true", "multiLine" = "true")

Looks like the types of the columns are missing. I guess something's going wrong here? https://github1s.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/[…]ination/databricks/DatabricksAzureBlobStorageStreamCopier.java

Hello Steven, it's been a while without an update from us. Are you still having problems or did you find a solution?

Unfortunately there are quite some issues with the Databricks connector, making it unusable in production. I've commented on the issues here: https://github.com/airbytehq/airbyte/issues/21193

Thanks for documenting this Steven, it should helpful for the team when they’re able to work on the Databricks destination.

@Steven I'm getting the same error as your initial error concerning the fs.azure.acount.key configuration. I'm using a compute cluster and not a SQL warehouse and it doesn't work. Also added the external location. Can you give me any pointers on how to get past this error?

Hey @Bob De Schutter, some things to check: • Are the network rules open from Databricks to your storage account? • Is the Databricks compute cluster you're running "Unity Catalog Enabled" (you can see the blue "Unity Catalog" tag in the cluster summary when it's enabled). There are quite some flags that disable the Unity Catalog (like authentication passthrough) so make sure that's all OK. • Make sure you use the SAS key of the storage ACCOUNT and not the storage CONTAINER. I was unable to make the latter work for some reason.

Just in case this issue hasn't been solved yet, consider adding this to your spark config under "Advanced Options"

fs.azure.account.auth.type OAuth
fs.azure.account.oauth.provider.type org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider
fs.azure.account.oauth2.client.endpoint <https://login.microsoftonline.com/><directory-id>/oauth2/token
fs.azure.account.oauth2.client.id {{secrets/<my-secret-scope>/<application-id>}}
fs.azure.account.oauth2.client.secret {{secrets/<my-secret-scope>/<service-credential-key-name>}}
fs.azure.createRemoteFileSystemDuringInitialization true