Select query in Hive returns null rows

While I was working with some joins in Hive I was facing some issues with retrieving rows from table in hive.

Reproduce the issue

---

1. Create an employee table in hive.

create table employee (employee_name STRING, employee_age INT, department_id BIGINT)
COMMENT ‘This table will contain employee information’ ;

2. Create a text file in local file system employee.txt

userA, 25,001

userB,26,002

3. Load the file from local file system to hive warehouse

LOAD Data local inpath ‘/home/user/Desktop/employee.csv’ overwrite into table employee;

4. Query employee table

select * from employee;

Results shown:

OK
userA,25,001 NULL NULL
userB,26,021 NULL NULL

Time taken: 0.118 seconds

Correction

---

1. Create an employee table in hive.

create table employee (employee_name STRING, employee_age INT, department_id BIGINT)
COMMENT ‘This table will contain employee information’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

Make sure you mention how the fields in the text file are terminated and how the lines in the text file are terminated during creation of table.

2. Create a text file in local file system employee.txt

userA, 25,001

userB,26,002

3. Load the file from local file system to hive warehouse

LOAD Data local inpath ‘/home/user/Desktop/employee.csv’ overwrite into table employee;

4. Query employee table

select * from employee;

Results shown:

OK
userA 25 1
userB 26 2

Time taken: 0.127 seconds

 

You can also view other big data tutorials here.

Name(required)

Email(required)

Website

Comment(required)

Submit

Δ