Insert overwrite table values in Hive with examples
Contents
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
|
1 2 3 |
INSERT OVERWRITE TABLE <table_name> VALUES (value1,value2,.....,valueN); |
Example for Insert overwrite table
Lets create the table cust_txns with auto.purge = true in the Table properties.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE cust_txns( customer_id varchar(40), txn_amout decimal(38,2), txn_type varchar(100)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS TEXTFILE TBLPROPERTIES ('auto.purge'='true'); |
Consider that the table cust_txns contains the few records as below.
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.
|
1 2 3 |
INSERT OVERWRITE TABLE cust_txns VALUES ('124',2800,'Saving'); |
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.
Related Articles : Insert Into Table in Hive
