Connect to Blob storage from Azure Databricks SQL

Kaizad Wadia 20 Reputation points
2024-06-11T13:36:31.3633333+00:00

So I would like to read a table from a CSV file on Azure Blob Storage in my own account, and load it into a table in Unity Catalog on databricks (hopefully using SQL).

I have tried this SQL command:

CREATE TABLE IF NOT EXISTS <table_name>;

COPY INTO <table_name>
FROM 'abfss://<container>@<storage_account>.dfs.core.windows.net/<filename>.csv'
FILEFORMAT = CSV
COPY_OPTIONS ('mergeSchema' = 'true');

But it gives me the following error:

Failure to initialize configuration for storage account <storage_account>.dfs.core.windows.net: Invalid configuration value detected for fs.azure.account.key

I ideally don't want to use an access token, but a role if possible (or short lived credentials) for authentication, but have no idea why this doesn't work.

Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,823 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,175 questions
Accepted answer
  1. PRADEEPCHEEKATLA-MSFT 89,296 Reputation points Microsoft Employee
    2024-06-12T04:47:55+00:00

    @Kaizad Wadia - Thanks for the question and using MS Q&A platform.

    This is excepted when you haven't used any credentials to access the Azure Data Lake Storage Gen2 account where you have the sample data.

    User's image

    According to the document: Connect to Azure Data Lake Storage Gen2 and Blob Storage - The following credentials can be used to access Azure Data Lake Storage Gen2 or Blob Storage:

    • OAuth 2.0 with a Microsoft Entra ID service principal
    • Shared access signatures (SAS)
    • Account keys

    For the demo purpose, I had used Account keys option to just to show case the authentication can be used to access Azure Data Lake Storage Gen2.

    spark.conf.set(
    "fs.azure.account.key.<storage-account>.dfs.core.windows.net","<storage-account-access-key>")

    User's image

    Checking to see if the sales table has been created.

    User's image

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.