Inserting Data into Hive Table

Question

I am new to hive. I have successfully setup a single node hadoop cluster for development purpose and on top of it, I have installed hive and pig.

I created a dummy table in hive:

create table foo (id int, name string);

Now, I want to insert data into this table. Can I add data just like sql one record at a time? kindly help me with an analogous command to:

insert into foo (id, name) VALUES (12,"xyz);

Also, I have a csv file which contains data in the format:

1,name1
2,name2
..
..

..


1000,name1000

How can I load this data into the dummy table?

Answer 1

You can use following lines of code to insert values into an already existing table. Here the table is db_name.table_name having two columns, and I am inserting 'All','done' as a row in the table.

insert into table db_name.table_name
select 'ALL','Done';

Hope this was helpful.

Answer 2

Hadoop file system does not support appending data to the existing files. Although, you can load your CSV file into HDFS and tell Hive to treat it as an external table.

Answer 3

Use this -

create table dummy_table_name as select * from source_table_name;

This will create the new table with existing data available on source_table_name.

Answer 4

I think the best way is:
a) Copy data into HDFS (if it is not already there)
b) Create external table over your CSV like this

CREATE EXTERNAL TABLE TableName (id int, name string)
ROW FORMAT DELIMITED   
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 'place in HDFS';

c) You can start using TableName already by issuing queries to it.
d) if you want to insert data into other Hive table:

insert overwrite table finalTable select * from table name;

Answer 5

to insert ad-hoc value like (12,"xyz), do this:

insert into table foo select * from (select 12,"xyz")a;