Hive external table creation access issues

hive

#1

I am trying to create HIVE external table using the below command

create external table orders_sqoop
STORED AS AVRO
LOCATION ‘/user/hive/warehouse/retail_stage.db/orders’
TBLPROPERTIES (‘avro.schema.url’=’/user/sushital1997/hive/schema/orders/orders.avsc’);

However I am getting the below access error.So I can’t create external table in itversity LAB ?

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:java.security.AccessControlException: Permission denied: user=sushital1997, access=WRITE, inode="/user/hive/warehouse/retail_stage.db/orders":jesuiswang:hadoopthoughtworks:drwxr-xr-x


#2

@sushital1997 You can’t use other user databases.

  1. Create your own database in a hive.
  2. Import data from mysql to hive using the sqoop command as avro file format:
sqoop import \
--connect jdbc:mysql://ms.itversity.com:3306/retail_db \
--username retail_user \
--password itversity \
--table orders \
--as-avrodatafile \
--warehouse-dir /apps/hive/warehouse/database_name.db

3.Create hive external table as below command:

CREATE EXTERNAL TABLE order_sqoop
STORED AS AVRO
LOCATION ‘/apps/hive/warehouse/database_name.db/orders’
TBLPROPERTIES (‘avro.schema.url’=‘hdfs://nn01.itversity.com/user/sushital1997/hive/schema/orders/orders.avsc’);