Creating hive table from hdfs data not successful, can anyone help to sort it out? Thank you


#1

I have a small file in file system to be put on to hdfs; and I want to create a hive table (external) that takes its content, here is what I did:

[paslechoix@gw03 ~]$ vim file_on_local.txt
2,Fitness
3,Footwear
4,Apparel
5,Golf
6,Outdoors
7,Fan Shop

hdfs dfs -put file_on_local.txt /apps/hive/warehouse/paslechoix.db/file_on_hive

From Hive:
create external table file_on_hive (id int, name string) location ‘/apps/hive/warehouse/paslechoix.db/file_on_hive’;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:hdfs://nn01.itversity.com:8020/apps/hive/warehouse/paslechoix.db/file_on_hive is not a directory or unable to create one)

Where on HDFS should I put the file file_on_local.txt to ?

Thank you.


#2

Please follow below steps.

  1. Create a new text file.
    vi hivetext.txt

  2. Create a new directory on HDFS.
    hdfs dfs -mkdir /user/cloudera/problem12/hive/

  3. Place the file on HDFS.
    hdfs dfs -put hivetext.txt /user/cloudera/problem12/hive/

  4. Check the file.
    [root@quickstart hive]# hdfs dfs -ls /user/cloudera/problem12/hive/
    Found 1 items
    -rw-r–r-- 1 root cloudera 60 2018-02-27 16:36 /user/cloudera/problem12/hive/hivetext.txt

  5. Execute below command on Hive terminal.

CREATE EXTERNAL TABLE sports(id INT, sport STRING)
COMMENT 'This is the staging page view table’
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION ‘/user/cloudera/problem12/hive/’

image


#3

Thanks, it works now, so the problem seems to be lacking the row format line.