Unable to create Managed Hive Table after Hortonworks (HDP) to Cloudera (CDP) migration

Question

We are testing our Hadoop applications as part of migrating from Hortonworks Data Platform (HDP v3.x) to Cloudera Data Platform (CDP) version 7.1. While testing, we found below issue while trying to create Managed Hive Table. Please advise on possible solutions. Thank you!

Error: Error while compiling statement: FAILED: Execution Error, return code 40000 from org.apache.hadoop.hive.ql.ddl.DDLTask. MetaException(message:A managed table's location should be located within managed warehouse root directory or within its database's managedLocationUri. Table MANAGED_TBL_A's location is not valid:hdfs://cluster/prj/Warehouse/Secure/APP/managed_tbl_a, managed warehouse:hdfs://cluster/warehouse/tablespace/managed/hive) (state=08S01,code=40000)

DDL Script

create table if not exists MANAGED_TBL_A
(
   name string,
   id varchar(20),
   created_on TIMESTAMP,
   created_by VARCHAR(10)
)
partitioned by (partition_date varchar(20))
clustered by (id) into 5 buckets
stored as orc
location '/prj/Warehouse/Secure/APP/managed_tbl_a'
tblproperties ('compactorthreshold.hive.compactor.delta.num.threshold'='1','compactorthreshold.hive.compactor.delta.pct.threshold'='0.5','transactional'='true','orc.create.index'='true','orc.compress.size'='8192');

Answer 1

hive.metastore.warehouse.dir - is a warehouse root directory.

When you create the database, specify MANAGEDLOCATION - a location root for managed tables and LOCATION - root for external tables.

MANAGEDLOCATION is within hive.metastore.warehouse.dir

Setting the metastore.warehouse.tenant.colocation property to true allows a common location for managed tables (MANAGEDLOCATION) outside the warehouse root directory, providing a tenant-based common root for setting quotas and other policies.

See more details in this manual: Hive managed location.