1

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
stacktesting
  • 103
  • 8
  • See this answer: https://stackoverflow.com/a/67073849/2700344 – leftjoin Feb 18 '22 at 19:30
  • I saw that. But Im not sure how to use it in my context. Could you elaborate from my context?. I have already loaded data to the HDFS path here in my case under '/ts/gp/node' – stacktesting Feb 18 '22 at 19:35
  • Also, I get the below error when I try using the alter database command. ``` alter database dsk set managedlocation 'hdfs://gFasService/warehouse/tablespace/managed/hive'; Query: alter database dsk set managedlocation 'hdfs://gFasService/warehouse/tablespace/managed/hive' ERROR: ParseException: Syntax error in line 1: alter database dsk set managedlocation 'hdfs://gFas... ^ Encountered: MANAGEDLOCATION Expected: IDENTIFIER CAUSED BY: Exception: Syntax error ``` – stacktesting Feb 18 '22 at 19:39
  • The simple way: create table without location specified, describe formatted to get location, load data or copy into that location, or create external table – leftjoin Feb 18 '22 at 19:56
  • Could you share the steps ?. I know to create a table. But I am not sure how to describe formatted to get location and load data into that location or create external table – stacktesting Feb 19 '22 at 02:51

1 Answers1

0

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.

leftjoin
  • 36,950
  • 8
  • 57
  • 116