Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive table is loading with NULL values

avatar
Contributor

CREATE TABLE IF NOT EXISTS employee (eid int, name String,
salary String)
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/user/hdadmin/sample.txt' OVERWRITE INTO TABLE employee;

Sample.txt

1201^AGopal^A45000
1202^AManisha^A45000
1203^AMasthanvali^A40000
1204^AKiran^A40000
1205^AKranthi^A30000

Both queries were executed successfully, but the table have only NULL values,

Output:

hive> select * from employee;
OK
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
Time taken: 0.148 seconds, Fetched: 5 row(s)

Please help me to fix this

1 ACCEPTED SOLUTION

avatar
Contributor

Thank you @Jonathan Sneep I resolved the issue. It is due to typing the query in text editor and copied it to hive cli, the single quote was malformed.

View solution in original post

2 REPLIES 2

avatar

Hi @Sivakumar Mahalingam

Try creating your table like this instead;

CREATE TABLE IF NOT EXISTS employee2 (eid int, name String,salary String)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' WITH SERDEPROPERTIES ("field.delim"="^A","line.delim" = '\n')
STORED AS TEXTFILE;

PS. If this helps to resolve your issue, please take a moment to click accept answer 🙂

avatar
Contributor

Thank you @Jonathan Sneep I resolved the issue. It is due to typing the query in text editor and copied it to hive cli, the single quote was malformed.