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

User · Answer

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
;

User · Answer

Yes we can use Insert query in hive  hive gt  create table test  id int  name string    INSERT  INSERT   VALUES is available starting in version 0 14  hive gt  insert into table test values  1  mytest     This is going to work for insert  We have to use values keyword  Note  User cannot insert data into a complex datatype column  array  map  struct  union  using the INSERT INTO   VALUES clause

User · Answer

There are few properties to set to make a Hive table support ACID properties and to insert the values into tables as like in  SQL   Conditions to create a ACID table in Hive   The table should be stored as ORC file  Only ORC format can support ACID prpoperties for now  The table must be bucketed  Properties to set to create ACID table  set hive support concurrency  true  set hive enforce bucketing  true  set hive exec dynamic partition mode  nonstrict set hive compactor initiator on   true  set hive compactor worker threads  1  set hive txn manager   org apache hadoop hive ql lockmgr DbTxnManager   set the property hive in test to true in hive site xml After setting all these properties   the table should be created with  tblproperty  transactional    true   The table should be bucketed and saved as orc CREATE TABLE table name  col1 int col2 string  col3 int  CLUSTERED BY col1 INTO 4   BUCKETS STORED AS orc tblproperties  transactional    true     Now its possible to inserte values into the table like SQL query  INSERT INTO TABLE table name VALUES  1  a  100   2  b  200   3  c  300

User · Answer

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

User · Answer

You can t do insert into to insert single record  It s not supported by Hive  You may place all new records that you want to insert in a file and load that file into a temp table in Hive  Then using insert overwrite  select command insert those rows into a new partition of your main Hive table  The constraint here is your main table will have to be pre partitioned  If you don t use partition then your whole table will be replaced with these new records

User · Answer

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

User · Answer

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

User · Answer

You still can insert into complex type in Hive - it works  id is int  colleagues array   insert into emp  id colleagues  select 11  array  Alex   Jian   from  select  1

User · Answer

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

User · Answer

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

User · Answer

Enter the following command to insert data into the testlog table with some condition   INSERT INTO TABLE testlog SELECT   FROM table1 WHERE some condition

User · Answer

Yes you can insert but not as similar to SQL   In SQL we can insert the row level data  but here you can insert by fields  columns    During this you have to make sure target table and the query should have same datatype and same number of columns   eg   CREATE TABLE test stu name STRING stu id INT stu marks INT  ROW FORMAT DELIMITED FIELDS TERMINATED BY     STORED AS TEXTFILE   INSERT OVERWRITE TABLE test SELECT lang name  lang id  lang legacy id FROM export table

User · Answer

To insert entire data of table2 in table1  Below is a query   INSERT INTO TABLE table1 SELECT   FROM table2

User · Answer

You could definitely append data into an existing table   But it is actually not an append at the HDFS level   It s just that whenever you do a LOAD or INSERT operation on an existing Hive table without OVERWRITE clause the new data will be put without replacing the old data  A new file will be created for this newly inserted data inside the directory corresponding to that table  For example    I have a file named demo txt which has 2 lines    ABC XYZ   Create a table and load this file into it  hive gt  create table demo foo string   hive gt  load data inpath   demo txt  into table demo    Now if I do a SELECT on this table it ll give me    hive gt  select   from demo                          OK     ABC     XYZ   Suppose  I have one more file named demo2 txt which has    PQR   And I do a LOAD again on this table without using overwrite   hive gt  load data inpath   demo2 txt  into table demo    Now  if I do a SELECT now  it ll give me   hive gt  select   from demo                         OK ABC XYZ PQR   HTH

User · Answer

you can add values to specific columns as well  just specify the column names in which you like to add corresponding values   Insert into Table  Col1  Col2  Col4 col5 Col7  Values   Va11   Va2   Val4   Val5   Val7      Make sure the columns you skip dont have not null value type

User · Answer

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

[sql] Hive insert query like SQL

Examples related to sql

Examples related to hadoop

Examples related to hive

Examples related to hiveql