Search code examples
hivehiveqlcreate-tablehiveddl

Hive table data load gives NULL values

Select * from movierating gives NULL values as a Result.

I have tried below create table queries:

CREATE TABLE movierating(id INT, movieid INT, rating INT, time string);
CREATE TABLE movierating(id INT, movieid INT, rating INT, time string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' stored as textfile;

Tried below load queries:

load data local inpath '/tmp/Movie-rating.txt' into table movierating;
load data local inpath '/tmp/Movie-rating.txt' OVERWRITE into table movierating;

data into 'Movie-rating.txt' file:(delimeter is tab)

1     123      3   881250949   
2     125      4   881250123
Solution

  • For tab delimited data use '\t' as field delimiter:

    CREATE TABLE movierating(id int,movieid int,rating int,time string) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY  '\t' LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;