Creating a Hive external table with Azure Blob Storage credentials in Spark-sql

Anant Furia 1 Reputation point
2021-03-19T06:33:44.367+00:00

Hi, we are using our own Hadoop/Spark cluster where we are running some spark-sql queries.
My question is how can we set azure storage credential for a external table? instead of storing the credential in core-site.xml

For e.g.

CREATE EXTERNAL TABLE test_my_table ( t1 string, t2 string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE LOCATION
'wasbs://test-blob@storageAcccount.blob.core.windows.net/example/data'

This would give me an error
Error in query: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Got exception: org.apache.hadoop.fs.azure.AzureException org.apache.hadoop.fs.azure.AzureException: No credentials found for account
in the configuration, and its container test-blob is not accessible using anonymous credentials. Please check if the container exists first. If it is not publicly available, you have to provide account credentials.);

So how do I provide azure account key in create table statement? or in spark-sql ?

Azure Storage Accounts
Azure Storage Accounts
Globally unique resources that provide access to data management services and serve as the parent namespace for the services.
2,686 questions
Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,427 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. deherman-MSFT 33,306 Reputation points Microsoft Employee
    2021-03-19T18:43:03.327+00:00

    @Anant Furia
    Please check the threads here and here to see if it resolves your issues. There are also instructions available for doing this in HDInsight which also might be helpful to you.

    Hope this helps. Let us know if you have further questions or issues.

    -------------------------------

    Please don’t forget to "Accept the answer" and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

    0 comments
  2. Anant Furia 1 Reputation point
    2021-03-19T19:22:16.983+00:00

    @deherman-MSFT - I did look through it, they all talk about environment level keys. What I am interested in is
    for e.g.
    I have 3 tables, and all 3 of them actually belong to different 3 storage account. That means the key needs to be table specific.

    Table 1 --> wasbs://test-blob1@account1.blob.core.windows.net/example/data
    Table 2 --> wasbs://test-blob2@account2.blob.core.windows.net/example/data
    Table 3 --> wasbs://test-blob3@account3.blob.core.windows.net/example/data

    And keys for account1 or 2 or 3 are different.

    Sorry, I know this sounds confusing, but feel free to let me know if you need more details.

    0 comments