Community Articles
Find and share helpful community-sourced technical articles
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (2)

Steps to Create Table in Hive on S3A with Ranger

  • Create a bucket with a unique name, I've used "myhivebucket" and do not change any details in the permissions
  • Complete the "Create bucket" wizard by clicking on "create bucket" button
  • Make the following entries in custom hdfs-site.xml
    • fs.s3a.access.key = <access key>
    • fs.s3a.secret.key = <access secret>
    • fs.s3a.impl = org.apache.hadoop.fs.s3a.S3AFileSystem
  • To retrieve the value for access key and secret, follow these steps:
    • Login to
    • Click on "Sign in to the console" tab
    • Login with appropriate credentials
    • Once logged in, you should see your login name on the top right corner of the AWS page
    • Click on the drop-down arrow beside your login name and select "My Security Credentials"
    • This should take you to a page titled "Your Security Credentials"
    • From this page, collapse the option that says "Access Keys(Access Key ID and Secret Access Key)"
    • You have to click on "create a new access key" because of Amazon limitation described here
    • This lets you download the key/secret in this format (this is not case sensitive)
      • AWSSecretKey=XXXXXxxxxxXXXXXxxxxxXXXXX/xxxxx
    • Value for "fs.s3a.access.key" will be the value for "AWSAccessKeyId"
    • Value for "fs.s3a.secret.key" will be the value for "AWSSecretKey"
  • Login to ranger admin interface and create a policy for hive/desired user to allow the desired permissions


  • Now login to hive with the kerberos credentials, as required via beeline and create table ensuring that the location is on s3a
[hive@xlnode-standalone ~]$ beeline -u "jdbc:hive2://;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2" 
   WARNING: Use "yarn jar" to launch YARN applications.
   Connecting to jdbc:hive2://;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2
   Connected to: Apache Hive (version 1.2.1000.
   Driver: Hive JDBC (version 1.2.1000.
   Transaction isolation: TRANSACTION_REPEATABLE_READ
   Beeline version 1.2.1000. by Apache Hive
   0: jdbc:hive2://> create table mys3test (col1 int, col2 string) row format delimited fields terminated by ',' stored as textfile location 's3a://myhivebucket/test';
   No rows affected (12.04 seconds)
   0: jdbc:hive2://>
  • Now try and insert some rows
0: jdbc:hive2://> insert into mys3test values (1,'test'),(2,'test');
   Error: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [hive] does not have [UPDATE] privilege on [default/mys3test] (state=42000,code=40000)
   0: jdbc:hive2://>
  • The above error is intentional, since we do not have "UPDATE" privilege assigned via ranger, we cannot insert the values yet, allow the permission and INSERT again


0: jdbc:hive2://> insert into mys3test values (1,'test'),(2,'test');
   INFO  : Tez session hasn't been created yet. Opening session
   INFO  : Dag name: insert into mys3test ...1,'test'),(2,'test')(Stage-1)
   INFO  : 
   INFO  : Status: Running (Executing on YARN cluster with App id application_1492107639289_0002)
   INFO  : Map 1: -/- 
   INFO  : Map 1: 0/1 
   INFO  : Map 1: 0(+1)/1 
   INFO  : Map 1: 0(+1)/1 
   INFO  : Map 1: 0(+1)/1 
   INFO  : Map 1: 0(+1)/1 
   INFO  : Map 1: 0/1 
   INFO  : Map 1: 1/1 
   INFO  : Loading data to table default.mys3test from s3a://myhivebucket/test/.hive-staging_hive_2017-04-13_19-27-13_226_6105571528298793138-1/-ext-10000
   INFO  : Table default.mys3test stats: [numFiles=1, numRows=2, totalSize=14, rawDataSize=12]
   No rows affected (53.854 seconds)
   0: jdbc:hive2://> select * from mys3test;
   | mys3test.col1  | mys3test.col2  |
   | 1              | test           |
   | 2              | test           |
   2 rows selected (3.554 seconds)
   0: jdbc:hive2://> 

Don't have an account?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
2 of 2
Last update:
‎08-17-2019 01:19 PM
Updated by:
Top Kudoed Authors