question Re: Hive table load in Archives of Support Questions (Read Only)

Hive table load

SatishS — Wed, 16 Dec 2015 03:25:38 GMT

Hello Friends,

I am new to Hadoop and Hive. Created a simple table with one column as ID and loaded data into this table from a file that's located in local filesyste with 6 (one with NULL) records with command as "load data local inpath '/home/edureka/Desktop/data' into table emp;"

Did select and it's show 5 records. Later manually changed the source file and removed all those 5 records and added 5 new records and loaded these new records without using the OVERWRITE with command "load data local inpath '/home/edureka/Desktop/data' into table emp;" Data load was successful.

This time if I do select * then I am getting 18 records. 1st set is repeating twice. I don't know why it's showing like this. Am I missing any command ? pls help me to understand.

Pls refer the screenshot.

Thankscapture.png

Re: Hive table load

rich1 — Wed, 16 Dec 2015 10:00:38 GMT

I think you answered your own question: you did not use OVERWRITE on the second "load" command, so you added the records twice. If you wanted to start over w/ all new data in the table, run the load command with OVERWRITE.

Re: Hive table load

pminovic — Wed, 16 Dec 2015 16:45:26 GMT

Have you expected to end up with only 12 records instead of 18?

If '/home/edureka/Desktop/data' is a directory and your file called d1 I suspect that after changing it file d1~ was created. So the second time both files were loaded into the table causing 18 records.

Re: Hive table load

SatishS — Thu, 17 Dec 2015 00:41:18 GMT

Thanks. What should I use if I want to override all data (I mean fresh load)?

Re: Hive table load

SatishS — Thu, 17 Dec 2015 00:43:26 GMT

No, I have single file in data directory. First time I had created with 5 records and loaded this file into table and came back to same file, deleted all 5 records and entered 5 new records and reloaded again. I was missing the OVERWRITE keyword in the query. now it's fine. Thanks.

Re: Hive table load

rich1 — Thu, 17 Dec 2015 01:08:50 GMT

If you do an "INSERT OVERWRITE" then all the files in the table's LOCATION will be deleted and replaced with the new data.