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?

Answer 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); 

Answer 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 ;