CODEWITHSUNDEEP
hadoophivehiveql
krackoder
krackoder

Reputation: 2981

Hive table not retrieving rows from external file

I have a text file called as sample.txt. The file looks like: abc,23,M def,25,F efg,25,F

I am trying to create a table in hive using:

CREATE EXTERNAL TABLE ppldb(name string, age int,gender string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '/path/to/sample.txt';

But the data isn't getting into the table. When I run the query: select count(*) from ppldb I get 0 in output.

What could be the reason for data not getting loaded into the table?

Upvotes: 2

Views: 5000

Answers (2)

Mike
Mike

Reputation: 7203

the LOCATION clause indicates where the table will be stored, not where to retrieve data from. After moving the samples.txt file into hdfs with something like

hdfs dfs -copyFromLocal ~/samples.txt /user/tables/

you could load the data into a table in hive with 

create table temp(name string, age int, gender string)
row format delimited fields terminated by ','
stored as textfile;

load data inpath '/user/tables/samples.txt' into table temp;

That should work

Upvotes: 2

Mukesh S
Mukesh S

Reputation: 2886

The location in a external table in Hive should be an HDFS directory and not the full path of the file. If that directory does not exists then the location we give will be created automatically. In your case /path/to/sample.txt is being treated as a directory.

So just give the /path/to/ in the LOCATION and keep the sample.txt file inside the directory. It will work.

Hope it helps...!!!

Upvotes: 5

Related Questions