question Hive table is loading with NULL values in Archives of Support Questions (Read Only)

Hive table is loading with NULL values

sivasai2006 — Wed, 17 Oct 2018 11:32:24 GMT

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

Re: Hive table is loading with NULL values

JonathanSneep — Wed, 17 Oct 2018 14:36:25 GMT

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 🙂

Re: Hive table is loading with NULL values

sivasai2006 — Thu, 18 Oct 2018 08:52:56 GMT

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.