Hive table properties configurations

Let’s have a look about few hive table configuration properties

Mutable and Immutable

Hive provides an option to create mutable table and immutable table.

Mutable Table

All the tables by default are mutable. Mutable table allows appending the data when data already present in table.

Immutable Table

A Table can be created as immutable table, by setting its table property to True. By default this property is false.

create table if not exists Test_immutable (id int, name string) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’ stored as textfile tblproperties (“immutable”=”true”);

Immutable property allows us to load the data for the first time. After the first data insert, the successive insert into commands will fail resulting in only one set of data into table.

If we try to insert data into immutable table, the it will throws error saying, inserting into non-empty immutable table is not allowed.

Purge

Purge is a table property. If Purge is set to true, then in case of dropping/Truncating an internal table, the data won’t go to trash, it will be permanently gone. And in case of Insert overwrite table, the previous data won’t go to trash, it will be permanently deleted.

This property works only for internal table.

create table if not exists Test_immutable (id int, name string) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’ stored as textfile tblproperties (“auto.purge”=”true”);

Ignore the header and footer

skip.header.line.count :

This configuration will skip ’n’ number of lines from beginning

skip.footer.line.count

This configuration will skip ’n’ number of lines from bottom

create table if not exists Test_immutable (id int, name string) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’ stored as textfile tblproperties (“skip.header.line.count”=”1", “skip.footer.line.count”=”1")