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?

This is what I ended up doing in Hive 0.12 which doesn't support INSERT-VALUES: `insert into table foo select 12, 'xyz' from (select count(1) from foo) a;` — alecswan, Apr 13 '17 at 17:23

David Gruzman · Answer 1 · 2013-03-04T10:03:48.280

29

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;

edited Mar 04 '13 at 10:03

answered Mar 04 '13 at 09:51

David Gruzman

7,900
1
28
30

1

In this case fields should be terminated by ','. – www Mar 04 '13 at 09:56

MoustafaAAtta · Answer 2 · 2014-06-30T11:02:52.080

8

There's no direct way to insert 1 record at a time from the terminal, however, here's an easy straight forward workaround which I usually use when I want to test something:

Assuming that t is a table with at least 1 record. It doesn't matter what is the type or number of columns.

INSERT INTO TABLE foo
SELECT '12', 'xyz'
FROM t
LIMIT 1;

edited Jun 30 '14 at 11:02

answered Jun 24 '14 at 08:54

MoustafaAAtta

1,011
12
17

NOTE: there's not even any `insert into table foo (column1, column2) select 'col1-value', 'col2-value' from t limit 1;` You have to specify each column in the select clause. – Milimetric Jul 04 '14 at 13:13
As totto90 describes this is good for testing, but it's also worth pointing out that doing this creates a new tiny file each time, which would likely lead to severe performance degradation in a real world use case. – Jeremy Beard Mar 21 '15 at 22:10

score 4 · Answer 3 · answered Oct 11 '14 at 00:52

4

Hive apparently supports INSERT...VALUES starting in Hive 0.14.

Please see the section 'Inserting into tables from SQL' at: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

answered Oct 11 '14 at 00:52

user3650393

41
2

score 3 · Answer 4 · edited Oct 20 '14 at 08:42

What ever data you have inserted into one text file or log file that can put on one path in hdfs and then write a query as follows in hive

  hive>load data inpath<<specify inputpath>> into table <<tablename>>;

EXAMPLE:

hive>create table foo (id int, name string)
row format delimited
fields terminated by '\t' or '|'or ','
stored as text file;
table created..
    DATA INSERTION::
    hive>load data inpath '/home/hive/foodata.log' into table foo;

score 3 · Answer 5 · answered Mar 02 '16 at 18:16

3

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

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

answered Mar 02 '16 at 18:16

bignano

573
5
21

Could you elaborate? – Kmeixner Mar 02 '16 at 18:36

score 2 · Answer 6 · answered Nov 13 '15 at 21:01

2

this is supported from version hive 0.14

INSERT INTO TABLE pd_temp(dept,make,cost,id,asmb_city,asmb_ct,retail) VALUES('production','thailand',10,99202,'northcarolina','usa',20)

answered Nov 13 '15 at 21:01

sateesh

21
1

1

This just seems like a duplicate of user3650393's answer from over a year ago. – blm Nov 13 '15 at 21:22

score 1 · Answer 7 · edited May 23 '17 at 12:34

1

It's a limitation of hive.

1.You cannot update data after it is inserted

2.There is no "insert into table values ... " statement

3.You can only load data using bulk load

4.There is not "delete from " command

5.You can only do bulk delete

But you still want to insert record from hive console than you can do select from statck. refer this

edited May 23 '17 at 12:34

Community

1
1

answered Jul 09 '15 at 06:49

Ashish Panery

1,196
3
13
24

2

has not been a limitation since spring 2013 your referenced article is quite outdated – Newtopian Apr 12 '16 at 19:43

score 1 · Answer 8 · answered Jul 13 '15 at 18:38

You may try this, I have developed a tool to generate hive scripts from a csv file. Following are few examples on how files are generated. Tool -- https://sourceforge.net/projects/csvtohive/?source=directory

Select a CSV file using Browse and set hadoop root directory ex: /user/bigdataproject/

Tool Generates Hadoop script with all csv files and following is a sample of generated Hadoop script to insert csv into Hadoop

#!/bin/bash -v

hadoop fs -put ./AllstarFull.csv /user/bigdataproject/AllstarFull.csv
hive -f ./AllstarFull.hive


hadoop fs -put ./Appearances.csv /user/bigdataproject/Appearances.csv
hive -f ./Appearances.hive


hadoop fs -put ./AwardsManagers.csv /user/bigdataproject/AwardsManagers.csv
hive -f ./AwardsManagers.hive

Sample of generated Hive scripts

CREATE DATABASE IF NOT EXISTS lahman;

USE lahman;

CREATE TABLE AllstarFull (playerID string,yearID string,gameNum string,gameID string,teamID string,lgID string,GP string,startingPos string) row format delimited fields terminated by ',' stored as textfile;

LOAD DATA INPATH '/user/bigdataproject/AllstarFull.csv' OVERWRITE INTO TABLE AllstarFull;

SELECT * FROM AllstarFull;

Thanks Vijay

score 1 · Answer 9 · answered May 15 '18 at 12:21

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.

score 0 · Answer 10 · answered Jun 15 '12 at 19:24

0

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.

answered Jun 15 '12 at 19:24

Olaf

6,249
1
19
37

1

Hmm, what about INSERT INTO TABLE foo SELECT 12, "xyz" FROM foo L IMIT 1? – www Mar 04 '13 at 09:22

score 0 · Answer 11 · edited Jun 09 '16 at 11:37

0

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.

edited Jun 09 '16 at 11:37

Remi Guan

21,506
17
64
87

answered Jun 09 '16 at 11:26

Indrajeet Gour

4,020
5
43
70

score 0 · Answer 12 · answered Jul 06 '22 at 07:24

0

LOAD DATA [LOCAL] INPATH '' [OVERWRITE] INTO TABLE <table_name>; use this command it will load the data at once just specify the file path if file is in local fs then use LOCAL if file is in hdfs then no need to use local

answered Jul 06 '22 at 07:24

Rishab

67
6

Inserting Data into Hive Table

12 Answers12

Linked