Insert data into hive table

Question

Using a Cygwin distribution  I ve installed Hadoop 0 20 3 and Hive 0 11 0   First of all  I don t understand how to use the Hive CLI   hive gt  show tables    Then enter and nothing happens  I can execute queries using hive -e -f   Then  I ve created a table   CREATE TABLE tweet table  tweet STRING   COMMENT  Table of string    But how can I insert data into this table  I see some INSERT INTO examples but when I try   INSERT INTO TABLE tweet table  tweet  VALUES   data     I ve got an error   FAILED  ParseException line 1 30 cannot recognize input near      tweet      in select clause   How can I append data in my table

User · Answer

If table is without partition then code will be,

Insert into table table_name select col_a,col_b,col_c from another_table(source table)

--here any condition can be applied such as limit, group by, order by etc...

If table is with partitions then code will be,

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

insert into table table_name partition(partition_col1, paritition_col2) select col_a,col_b,col_c,partition_col1,partition_col2 from another_table(source table)

--here any condition can be applied such as limit, group by, order by etc...

User · Answer

Although there is an accepted answer I would want to add that as of Hive 0 14  record level operations are allowed  The correct syntax and query would be   INSERT INTO TABLE tweet table VALUES   data

User · Answer

Try to use this with single quotes in data   insert into table test hive values   1   puneet

User · Answer

If you already have a table pre loaded tbl with some data  You can use a trick to load the data into your table with following query  INSERT INTO TABLE tweet table    SELECT   my data  AS my column      FROM   pre loaded tbl     LIMIT   5    Also please note that  my data  is independent of any data in the pre loaded tbl  You can select any data and write any column name  here my data and my column   Hive does not require it to have same column name  However structure of select statement should be same as that of your tweet table  You can use limit to determine how many times you can insert into the tweet table   However if you haven t  created any table  you will have to load the data using file copy or load data commands in above answers

User · Answer

You can insert new data into table by two ways    Load the data of a file into table using load command   LOAD DATA  LOCAL  INPATH  filepath   OVERWRITE  INTO TABLE tablename   You can insert new data into table by using select query   INSERT INTO table tablename1 select columnlist FROM secondtable

[database] Insert data into hive table

Examples related to database

Examples related to hadoop

Examples related to hive