Hive insert query like SQL

Question

I am new to hive, and want to know if there is anyway to insert data into Hive table like we do in SQL. I want to insert my data into hive like

INSERT INTO tablename VALUES (value1,value2..)

I have read that you can load the data from a file to hive table or you can import data from one table to hive table but is there any way to append the data as in SQL?

Solution 1

Some of the answers here are out of date as of Hive 0.14

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingvaluesintotablesfromSQL

It is now possible to insert using syntax such as:

CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2));

INSERT INTO TABLE students
  VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);

Solution 2

You can use the table generating function stack to insert literal values into a table.

First you need a dummy table which contains only one line. You can generate it with the help of limit.

CREATE TABLE one AS
SELECT 1 AS one
FROM any_table_in_your_database
LIMIT 1;

Now you can create a new table with literal values like this:

CREATE TABLE my_table AS
SELECT stack(3
  , "row1", 1
  , "row2", 2
  , "row3", 3
) AS (column1, column2)
FROM one
;

The first argument of stack is the number of rows you are generating.

You can also add values to an existing table:

INSERT INTO TABLE my_table
SELECT stack(2
  , "row4", 1
  , "row5", 2
) AS (column1, column2)
FROM one
;

Solution 3

Slightly better version of the unique2 suggestion is below:

insert overwrite table target_table
select * from 
(
select stack(
    3,                 # generating new table with 3 records
    'John', 80,        # record_1
    'Bill', 61         # record_2
    'Martha', 101      # record_3
    ) 
) s;

Which does not require the hack with using an already exiting table.

Solution 4

You can use below approach. With this, You don't need to create temp table OR txt/csv file for further select and load respectively.

INSERT INTO TABLE tablename SELECT value1,value2 FROM tempTable_with_atleast_one_records LIMIT 1.

Where tempTable_with_atleast_one_records is any table with atleast one record.

But problem with this approach is that If you have INSERT statement which inserts multiple rows like below one.

INSERT INTO yourTable values (1 , 'value1') , (2 , 'value2') , (3 , 'value3') ;

Then, You need to have separate INSERT hive statement for each rows. See below.

INSERT INTO TABLE yourTable SELECT 1 , 'value1' FROM tempTable_with_atleast_one_records LIMIT 1;
INSERT INTO TABLE yourTable SELECT 2 , 'value2' FROM tempTable_with_atleast_one_records LIMIT 1;
INSERT INTO TABLE yourTable SELECT 3 , 'value3' FROM tempTable_with_atleast_one_records LIMIT 1;

Solution 5

Ways to insert data into Hive table: for demonstration, I am using table name as table1 and table2

1. create table table2 as select * from table1 where 1=1; or create table table2 as select * from table1;

2. insert overwrite table table2 select * from table1; --it will insert data from one to another. Note: It will refresh the target.

3. insert into table table2 select * from table1; --it will insert data from one to another. Note: It will append into the target.

4. load data local inpath 'local_path' overwrite into table table1; --it will load data from local into the target table and also refresh the target table.

5. load data inpath 'hdfs_path' overwrite into table table1; --it will load data from hdfs location iand also refresh the target table. or

   create table table2( col1 string, col2 string, col3 string) row format delimited fields terminated by ',' location 'hdfs_location';

6. load data local inpath 'local_path' into table table1; --it will load data from local and also append into the target table.

7. load data inpath 'hdfs_path' into table table1; --it will load data from hdfs location and also append into the target table.

8. insert into table2 values('aa','bb','cc'); --Lets say table2 have 3 columns only.

9. Multiple insertion into hive table

View more solutions

Comments

[Y0gesh Gupta]

is there a way around so that I can append data in my table as we do in SQL.

[Lukas Vermeer]

INSERT INTO tablename SELECT ... is supported, so you could put the new data in a temporary table and then insert by selecting from there.

[Y0gesh Gupta]

I want to append data in hive through java client, will it work if I create a temporary data table in java and write an insert and select query in my java client only?

[Lukas Vermeer]

Not entirely correct. "INSERT INTO will append to the table or partition keeping the existing data in tact. (Note: INSERT INTO syntax is only available starting in version 0.8)" cwiki.apache.org/confluence/display/Hive/…

[Lukas Vermeer]

If you're using Java, why not append to the file in HDFS directly? Hive is not really suitable for this kind of thing.

[ryan]

Why do you need to add an select statement on the outside of select stack, is that necessary?

[pavel_orekhov]

FAILED: Parse Error: line 1:28 cannot recognize input near 'VARCHAR' '(' '64' in column type