hive Insert Statement Insert into table


Example

INSERT INTO will append to the table or partition, keeping the existing data intact.

    INSERT INTO table yourTargetTable SELECT * FROM yourSourceTable;

If a table is partitioned then we can insert into that particular partition in static fashion as shown below.

    INSERT INTO TABLE yourTargetTable PARTITION (state=CA, city=LIVERMORE) 
    select * FROM yourSourceTable;

If a table is partitioned then we can insert into that particular partition in dynamic fashion as shown below. To perfom dynamic partition inserts we must set below below properties.

    Dynamic Partition inserts are disabled by default. These are the relevant configuration properties for dynamic partition inserts:
    SET hive.exec.dynamic.partition=true;
    SET hive.exec.dynamic.partition.mode=non-strict

    INSERT INTO TABLE yourTargetTable PARTITION (state=CA, city=LIVERMORE) (date,time)
    select * FROM yourSourceTable;

Multiple Inserts into from a table.

Hive extension (multiple inserts):

    FROM table_name

    INSERT OVERWRITE TABLE table_one SELECT table_name.column_one,table_name.column_two

    INSERT OVERWRITE TABLE table_two SELECT table_name.column_two WHERE table_name.column_one == 'something'