Hive managed table issue to create a hive table from a hdfs location in CDP

Question

I have a CDP 7.3.1 where using sqoop , I have loaded data from Postgres database table into HDFS location /ts/gp/node. Now I am trying to create a hive table on this. I get the below error. Please help

CREATE TABLE dsk.node
(
    district  string,
    zone  string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '/ts/gp/node'
ERROR: ImpalaRuntimeException: Error making 'createTable' RPC to Hive Metastore:
CAUSED BY: MetaException: A managed table's location should be located within managed warehouse root directory or within its database's managedLocationUri. Table node's location is not valid:hdfs://gFasService/ts/gp/node, managed warehouse:hdfs://gFasService/warehouse/tablespace/managed/hive

Answer 1

If you want managed table, then create it without location specified

CREATE TABLE dsk.node
(
    district  string,
    zone  string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'; 

Use DESCRIBE FORMATTED dsk.node; and check the location, see the manual.

Then load data into location from DESCRIBE command above, if you have the data already loaded into some other location, just copy it using hdfs dfs -cp command.