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

No. This INSERT INTO tablename VALUES (x,y,z) syntax is currently not supported in Hive.

Answer 3

To insert entire data of table2 in table1. Below is a query:

INSERT INTO TABLE table1 SELECT * FROM table2; 

Answer 4

I think in such scenarios you should be using HBASE which facilitates such kind of insertion but it does not provide any SQL kind of query language. You need you use Java API of HBASE like the put method to do such kind of insertion. Moreover HBASE is column oriented no-sql database.

Answer 5

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
;

Answer 6

Enter the following command to insert data into the testlog table with some condition:

INSERT INTO TABLE testlog SELECT * FROM table1 WHERE some condition;