Hive ACID Props

DDL Sample Table

CREATE TABLE table_name (
  id                int,
  name              string
)
CLUSTERED BY (id) INTO 2 BUCKETS STORED AS ORC
TBLPROPERTIES ("transactional"="true",
  "compactor.mapreduce.map.memory.mb"="2048",     -- specify compaction map job properties
  "compactorthreshold.hive.compactor.delta.num.threshold"="4",  -- trigger minor compaction if there are more than 4 delta directories
  "compactorthreshold.hive.compactor.delta.pct.threshold"="0.5" -- trigger major compaction if the ratio of size of delta files to
                                                                   -- size of base files is greater than 50%
);

DDL Table Level Props

Enable Compaction

"transactional"="true"

Disable all Compaction

'TBLPROPERTIES ("NO_AUTO_COMPACTION"="true")'

Specify compaction map job properties

"compactor.mapreduce.map.memory.mb"="2048"

Trigger minor compaction if there are more than 4 delta directories

"compactorthreshold.hive.compactor.delta.num.threshold"="4"

Trigger major compaction if the ratio of size of delta files to

"compactorthreshold.hive.compactor.delta.pct.threshold"="0.5"

DML ACID Functions

ALTER TABLE table_name COMPACT 'minor' 
   WITH OVERWRITE TBLPROPERTIES ("compactor.mapreduce.map.memory.mb"="3072"); 
ALTER TABLE table_name COMPACT 'major'
   WITH OVERWRITE TBLPROPERTIES ("tblprops.orc.compress.size"="8192");