Data selection from adls2 in Serverless Warehouse

[ogs]

Hi everyone,

I'm trying to query data from our adls2 delta lake using a serverless sql warehouse. We've already set up private connectivity via NCC, but hitting a snag when running queries like:

SELECT * FROM delta.`abfss://container@xxx.dfs.core.windows.net/delta_tbl`

"Invalid configuration value detected for fs.azure.account.key"

My questions:

1. Where exactly should I configure this storage key in a Serverless setup? (It's not a traditional cluster where I'd use spark.conf.set)

2. Is creating an external table in UC a better approach? Something like:
   

   CREATE EXTERNAL TABLE xxx
   USING DELTA
   LOCATION 'abfss://container@xxx.dfs.core.windows.net/xxx'
   

   and what config should be made for this to work Thanks.

[BigRoux]

Here is something to try:

To resolve your query and simplify access to your ADLS Gen2 Delta Lake via a serverless SQL warehouse, here are the steps and considerations:

1. Configuring fs.azure.account.key in a Serverless Setup The error indicates that your storage account key isn't properly registered in the configuration. Unlike traditional clusters (where you'd use spark.conf.set), with a serverless SQL warehouse, you must specify this configuration in the SQL Warehouse's "Advanced Options" under the Spark Config section.

• Navigate to the Databricks console.
• Go to SQL > SQL Warehouses, then select the serverless SQL warehouse you're using.
• In the Advanced Options, add the following under the Spark Config textbox: fs.azure.account.key.<storage_account>.dfs.core.windows.net "<storage_account_key>" Replace <storage_account> with your Azure Storage account name and <storage_account_key> with your actual storage key.

2. External Table Using Unity Catalog (UC) Creating an external table through Unity Catalog can streamline security and simplify access management across different users and services. The key configurations for this approach would depend on using the necessary access credentials and ensuring private connectivity is correctly established:

• External Table Syntax: sql CREATE EXTERNAL TABLE catalog_name.schema_name.table_name USING DELTA LOCATION 'abfss://container@xxx.dfs.core.windows.net/delta_tbl'; Replace catalog_name, schema_name, and other parameters as per your setup.
• Steps to Configure Access:

  • If using a service principal or OAuth, ensure the following Spark configurations are added either through the SQL Warehouse interface, an admin console, or a direct configuration file: plaintext fs.azure.account.auth.type <auth_type> fs.azure.account.oauth2.client.id <client_id> fs.azure.account.oauth2.client.secret <client_secret> fs.azure.account.oauth2.client.endpoint <oauth_endpoint> Adjust these values based on your exact setup and Azure credentials.
  • If relying on a shared key, you would still add the fs.azure.account.key.<storage_account>.dfs.core.windows.net property directly, as explained in Section 1 above.

3. Network Connectivity Considerations with NCC Since your setup already utilizes private connectivity via NCC (Network Connectivity Configuration), ensure the following: - The NCC is correctly configured and attached to your workspace. - Private endpoints for both your SQL warehouse and ADLS Gen2 are properly set up. - You have allowed the required subnets or IP ranges in the storage firewall settings to enable communication between the Databricks serverless compute plane and your storage account.

Recommendation Using an external table along with Unity Catalog is indeed a better approach in terms of centralizing metadata and managing access consistently. Ensure your Spark configurations, storage connectivity, and table definitions align with the recommendations above.

If required, further troubleshooting can be performed for connectivity/failures using utilities like nslookup to verify private endpoint reachability, or by reviewing network access rules within NCC and Azure.

Hope this helps, Big Roux.

[ogs]

Hi, thanks for the detailed explanation.
Unfortunately, configuring fs.azure.account.key in the Serverless advanced options didn’t help (I’m sure I wrote it correctly) - still receiving the same error.
I saw in some sources around the net that I should create an external location - will try that approach too.