Hive metastore external tables and service principals

[mysura]

we are using mount points via service principals approach to connect the storage account. and using the same mount points to create external tables in hive meta store. but now we are trying using only service principals setup ,so  we need to change the external locations of tables from (dbfs/mnt/)..... path to adfss:// protocol (location remains same but the approach is by adfss URL) .i can alter the existing tables location , working fine but the physical tables in catalog explorer are not opening .
Tried to drop the tables and recreated with new adfss location, but still tables is unaccusable in catalog explore .
Service principal setup is running fine   i could load the data from storage account, as well as from the tables in hive meta store.
*cluster is not unity enabled and it is not need 
code:

1. spark. SQL("""

    ALTER TABLE schema. Table

    SET LOCATION 'abfss://container@storageaccount.dfs.core.windows.net/foldername/filename.delta'

""")
2 . creating  external table:

CREATE EXTERNAL TABLE hive_metastore.schema.table

USING DELTA

LOCATION "abfss://container@storageaccount.dfs.core.windows.net/foldername/filename.delta"

Error:
Failure to initialize configuration for storage account stalyceprdevbdls001.dfs.core.windows.net: Invalid configuration value detected for fs.azure.account.keyInvalid configuration value detected for fs.azure.account.key

. 

[Alberto_Umana]

Hi @mysura,

The error message indicates an invalid configuration value for fs.azure.account.key

Since your cluster is not Unity Catalog enabled, ensure that the cluster configuration includes the necessary settings for accessing ADLS. This includes setting the appropriate Spark configurations for OAuth.

Verify that the service principal has the necessary permissions to access the storage account. The service principal should have at least the Storage Blob Data Contributor role assigned at the storage account level

[mysura]

Hi @Alberto_Umana 
The above mentioned requirements are set correctly. still same issue
1.my doubt is do we need to enable unity in workspace and notebook with unity enabled cluster only to change the external location path from dbfs url to adfss uri.
2.does hive meta store allows this change to use service principals and adfss instead of dbfs/mnt/ ..
3. if it does allow can you please mention the steps and actions for the above requirement

[Alberto_Umana]

Hi @mysura,

Is your workspace Unity Catalog enabled? do you have a metastore assigned? I think legacy hive_metastore will not support service principal to do this change.

I think the best approach is to create an external location based on the adfss and then create the table with location as external location table.

https://learn.microsoft.com/en-us/azure/databricks/connect/unity-catalog/cloud-storage/external-loca...

https://learn.microsoft.com/en-us/azure/databricks/dbfs/unity-catalog