How to create Hive table with Azure WASB storage?

[#1]

• HDP 2.6.4.0
• HDFS 2.7.3
• Hive 1.2.1
• Ranger 0.7.0

I'm testing WASB as a storage option. I've configured the core-site.xml property. Connectivity works well in these areas:

• Writing files to WASB
• Using files uploaded to WASB in Zeppelin using livy2
• Using files uploaded to WASB using spark-shell
• 'hdfs dfs' file system commands to add/modify/delete/list container items

I'm using the tweets data from the Twitter tutorial to test using WASB as the source for creating Hive tables. I've tried External and Managed tables. I still get the same error. Following is the most recent version of the table creation code I've used and the error I receive. The error is the same regardless of the table type.

What am I missing? Where should I look to solve this issue?

CREATE External TABLE IF NOT EXISTS tweets_text_azure( 
tweet_id bigint, 
created_unixtime bigint, 
created_time string, 
lang string, 
displayname string, 
time_zone string, 
msg string) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
LOCATION 'wasb://Container@StorageAccount.blob.core.windows.net'; 

FAILED: HiveAccessControlException Permission denied: user [AmbariUsername] does not have [ALL] privilege on [wasb://Container@StorageAccount.blob.core.windows.net]

[#2]

After disabling Ranger, this is the error:

java.sql.SQLException:
Error while processing statement: FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask.
MetaException(message:java.security.AccessControlException: Permission denied:
user=hive, path="wasb://Container@StorageAccount.blob.core.windows.net/tweets":<AmbariUsername>:supergroup:drw-r--r--)

[#3 · Accepted solution]

I was able to resolve this by modifying the Azure Blob Properties. To navigate to the Blob Properties:

1. Containers > [container] > [blob]
2. In the Metadata section of Blob Properties, modify the key hdi_permission "owner" value to the user executing the Hive process.

For this proof of concept, user "Hive" is executing the Hive CREATE TABLE so I changed the original value from...

{"owner":"root","group":"supergroup","permissions":"rw-r--r--"} 

to...

{"owner":"hive","group":"supergroup","permissions":"rw-r--r--"}

The hdi_permission value must be changed for each blob. Since WASB is flat storage, which only emulates a folder hierarchy, that means that each file will need to have the value changed.