Insert overwrite table values in Hive with examples

Insert overwrite table in Hive

The insert overwrite table query will overwrite the any existing table or partition in Hive. It will delete all the existing records and insert the new records into the table.If the table property set as ‘auto.purge’=’true’, the previous data of the table is not moved to trash when insert overwrite query is run against the table.

If we not set the ‘auto.purge’=’true’ in the table properties and run the insert overwrite query frequently, it occupy the memory for the previous data in the trash and create the insufficient memory issue after some time.

Syntax for Insert overwrite table

Example for Insert overwrite table

Lets create the table cust_txns with auto.purge = true in the Table properties.

Consider that the table cust_txns contains the few records as below.

select * from hive table

If we run the below insert overwrite query against this table, the existing records will be deleted and the new records will inserted into the table. Lets run the insert overwrite against the table cust_txns.

Now the table cust_txns contains only the new records as below. Since we have set the table properties as auto.purge = true , the previous records is not moved to trash directory.

select * from cust_txns after insert overwrite

Related Articles : Insert Into Table in Hive