Table Properties (TBLPROPERTIES)

The TBLPROPERTIES clause specified on the CREATE DATALAKE or ALTER DATALAKE table statement allows you to add custom or predefined metadata properties to a Datalake table.

Many of these table properties are predefined and managed by Hive. Db2 has also created some custom properties to help define properties and manage the functionality of Datalake tables. These properties provide information about the table and can affect the behavior of the table.

Note: There is no validation of the table properties by Db2 nor by Hive, and hence care must be taken when specifying these options. For unspecified predefined table properties or those specified with incorrect values, the property value will be ignored (no error). Note that properties which are managed by Hive can change in future releases of Hive.

auto.purge: Default value is false. If set to true, a table’s directories and files will not be moved to the trash bin when the table is dropped or altered to drop a partition and the DELETE DATA clause is specified; resulting in the permanent deletion of the directories and files. For more information, see Datalake table Trash bin support. In the case of an insert overwrite, the overwritten data will not be moved to the trash bin resulting in the permanent deletion of the data. Alternatively you can use the PURGE clause on the drop or alter table statements to bypass the trash bin.

bigsql.external.catalog

No default value. This Db2 specific property indicates that a Hive Datalake table is registered in a remote metastore. This property is automatically added to tables imported into DB2 by the SYSHADOOP.EXT_METASTORE_SYNC procedure. To export a table from Db2, this property must be added to the TBLPROPERTIES clause on the CREATE DATALAKE TABLE statement. The value specified for this property on the CREATE DATALAKE TABLE statement must match the external-metastore-name specified on the SYSHADOOP.REGISTER_EXT_METASTORE procedure.

Tables tagged with this property are referred to as externally managed tables and they have additional restrictions. For more information, see Terminology and Restrictions and limitations sections.

datalake.table

No default value. This is a Db2 specific property is which set to true for tables created as Datalake tables.

external.table.purge

This Hive Datalake table property affects what happens to a table's directories and files when the table is dropped or altered to drop a partition. If set to false, a table's files and directories will not be removed when the table is dropped or altered to drop a partition. If set to true, the table’s directories and data files will be deleted when the table is dropped and the DELETE DATA clause is specified on the drop table statement. For dropped partitions, the partition directory and data files will be deleted if the DELETE DATA clause is specified when dropping a partition.

Note: If you’ve enabled trash bin supported, the data may be moved to the trash bin rather than being deleted.

For Iceberg tables, this property is mapped to the gc.enabled property.

format-version

Default value is 2. This Iceberg specific property specifies the table format version. The format version number is incremented when new features are added that will break forward-compatibility. Tables may continue to be written with an older version of the spec to ensure compatibility by not using features that are not yet implemented by processing engines. A table can be altered to change the version from 1 to 2 but cannot be altered to change the value from 2 to 1.

gc.enabled

This Iceberg specific property affects what happens to a table's directories and files when the table is dropped. If set to false, a table's files and directories will not be removed when the table is dropped or altered to drop a partition. If set to true, the table’s directories and data files will be deleted when the table is dropped and the DELETE DATA clause is specified on the drop table statement. For dropped partitions, the partition directory and data files will be deleted if the DELETE DATA clause is specified when dropping a partition.

Note: If you’ve enabled trash bin support, deleted table directories and files will be moved to the trash bin.

This property is equivalent to the external.table.purge property for non-Iceberg Datalake tables. When a Datalake Iceberg table is create with the external.table.purge property or is altered to set the property, the property is mapped to the gc.enabled property.

iceberg.catalog

This Iceberg specific property indicates the metastore where a Datalake Iceberg table is registered.

This property is automatically added to tables imported into DB2 from an external metastore by the SYSHADOOP.EXT_METASTORE_SYNC procedure.

If the property is omitted on the CREATE DATALAKE TABLE statement, the table is registered in the Db2 internal metastore.

If the property is specified on the CREATE DATALAKE TABLE statement, the table is exported from Db2 and registered in the external metastore specified by the property value. This value must match the external-metastore-name specified on the SYSHADOOP.REGISTER_EXT_METASTORE procedure.

For Iceberg tables registered in an external metastore, the table metadata is loaded from that metastore when the table is referenced in DB2. The metadata specifies the table’s schema, partitioning config, properties and snapshots of the table content.

Tables tagged with this property are referred to as externally managed tables and they have additional restrictions.

For more information see Terminology and Restrictions and limitations sections.

skip.trash

Default value is false.

If set to true, a table’s directories and files will not be moved to the trash bin when the table is dropped or altered to drop a partition and the DELETE DATA clause is specified; resulting in the permanent deletion of the directories and files. For more information, see Datalake table Trash bin support. Alternatively you can use the PURGE clause on the drop or alter table statements to bypass the trash bin.

write.format.default

Default value is parquet.

This Iceberg specific property specifies the file format for the table’s data files. Valid values are parquet, avro, or orc. Alternatively you can use the STORED AS clause to specify the data file format.