Manage Format tables
Learn how to create, read, write, and manage a Format table in Data Lake Formation (DLF).
Format table overview
A Format table stores data in standard file formats such as Parquet, ORC, CSV, and JSON. Its directory layout is compatible with Hive tables. Spark and Flink can access the table through DLF Catalog, and other programs can read or write files in the table directory.
You can create a Format table in the following ways:
-
DLF console — guided UI for one-time setup
-
SQL — Flink SQL or Spark SQL for programmatic workflows (for example,
USING Parquetin Spark SQL ortype='format-table'in Flink SQL).
Key characteristics of Format tables
-
Managed data: DLF manages both the metadata and the data files. It generates a UUID-based storage path, so you do not need to specify one.
-
Open files: The data consists of standard-format files without snapshots or manifest files. Tools that support the file format can read the files.
-
Managed partitions: When enabled, DLF Catalog registers and manages partitions and supports partition DDL, partition expiration, and storage tiering.
-
Deletion protection: When you delete a table, DLF retains its data for one day by default. You can restore the table from the Recycle Bin during that period.
Choose a Format table or a Paimon table
|
Requirement |
Recommended table type |
|---|---|
|
Streaming writes with exactly-once semantics and partition completion notifications |
Paimon table |
|
Primary-key updates, time travel, or incremental reads |
Paimon table |
|
Batch processing output that downstream systems read as files |
Format table |
|
File exchange with other systems (a program writes and an engine reads, or vice versa) |
Format table |
|
Migration from Hive while retaining the original file format and directory layout |
Format table |
Usage limitations
-
Format tables do not support primary keys, row-level updates or deletes, snapshots, time travel, or incremental reads.
-
An overwrite is not atomic. Existing files are deleted before new files are written, so concurrent queries may return no data or partial data. For data that must be replaced as a unit, write to a new date partition and have downstream queries read the latest completed partition. Do not overwrite the same partition concurrently.
-
Format tables do not compact small files in the background. The writing job determines the number of files. Before writing with Spark, consider using
repartitionby partition. -
Managed partitions require compatible versions of every engine that reads or writes the table. Partitions written by an older engine may need to be registered with
MSCK REPAIR TABLE.
Create a Format table
DLF console
-
Log on to the DLF console
-
In the left-side navigation pane, click Catalogs, then click your catalog name.
-
In the Database section, click your database name.
-
Click Create Table and configure the following fields:
Field
Description
Table Format
Select Format Table.
Table Name
Enter a name. Must be unique within the database.
Table Description
Enter a description.
Columns
Define the table columns.
User-defined Table Properties
Set
file.formattoparquet,orc,csv, orjson. The default isparquet. For a partitioned table, setmetastore.partitioned-tabletotrueto enable managed partitions. -
Click OK.
SQL
The following examples create a partitioned Format table with managed partitions. Use STRING partition columns whose values match the directory names. For hourly partitions, use separate date and hour columns.
Flink SQL
CREATE TABLE order_detail (
order_id BIGINT,
amount DECIMAL(18, 2),
dt STRING
) PARTITIONED BY (dt) WITH (
'type' = 'format-table',
'file.format' = 'parquet',
'metastore.partitioned-table' = 'true'
);
Spark SQL
CREATE TABLE order_detail (
order_id BIGINT,
amount DECIMAL(18, 2),
dt STRING
) USING parquet
PARTITIONED BY (dt)
TBLPROPERTIES ('metastore.partitioned-table' = 'true');
The SQL examples above explicitly set metastore.partitioned-table=true to enable managed partitions. DLF does not enable them by default. For other setup methods, see Enable managed partitions.
View a Format table
-
In the Database section, click your database name.
-
On the Tables tab, click your table name.
-
On the Table Details tab, view basic information and column definitions.
-
On the Permissions tab, grant table access to DLF users or roles. For details, see Manage data permissions.
A deleted table can be restored within the metadata protection period. For more information, see Recycle Bin.
Read and write data
Compute engines
Use Spark or Flink for batch processing and data import. For example, use Spark SQL to write and read a partition:
INSERT INTO order_detail PARTITION (dt = '20261105')
SELECT order_id, amount FROM src;
INSERT OVERWRITE order_detail PARTITION (dt = '20261105')
SELECT order_id, amount FROM src;
SELECT * FROM order_detail WHERE dt = '20261105';
In Spark, INSERT OVERWRITE without a PARTITION clause overwrites the entire table. Partitions not written by the statement lose their data but remain registered. To overwrite only partitions written by the statement, first set spark.sql.sources.partitionOverwriteMode=dynamic.
Python SDK
Use pypaimon to create, read, and write Format tables without Spark or Flink. After writing to a table with managed partitions enabled, call catalog.create_partitions() to register partitions. For details, see Access DLF Format Tables with pypaimon. DataFusion through pypaimon-rust provides SQL access from Python and automatically registers managed partitions.
Console
Open the Format table, go to the file list, and upload files into the target partition directory, such as dt=20261105/. For a table with managed partitions enabled, create the partition first if the directory does not exist. The file format and columns must match the table definition. Do not start a file name with . or _.
After uploading to a table with managed partitions enabled, run ALTER TABLE ... ADD IF NOT EXISTS PARTITION or MSCK REPAIR TABLE so queries can see the new partition.
Partition management
A partitioned Format table supports two partition management modes. By default, DLF discovers partitions from the table directory. With managed partitions enabled, DLF Catalog registers and manages partitions, and queries return only registered partitions.
Enable managed partitions
Enable managed partitions for new tables
For a new table, set metastore.partitioned-table=true in the DLF console's user-defined table properties, in Spark SQL TBLPROPERTIES, or in Flink SQL WITH. For complete SQL examples, see Create a Format table.
Catalog-level settings
Setting the table property applies to that table. To enable managed partitions by default for partitioned Format tables created later in a Catalog, set format-table-default.metastore.partitioned-table=true on that Catalog. If neither setting is present, DLF discovers partitions from the table directory.
Enable managed partitions for existing tables
For an existing table, run:
ALTER TABLE t SET TBLPROPERTIES ('metastore.partitioned-table' = 'true');
After enabling managed partitions for an existing table, run MSCK REPAIR TABLE t to register existing partition directories. Otherwise, queries return no data from those partitions.
Managed partitions and directory discovery
|
Aspect |
Managed partitions (recommended) |
Directory discovery (default) |
|---|---|---|
|
Partitions returned by queries |
Only registered partitions. With managed partitions enabled, supported Spark and Flink writers using DLF Catalog and DataFusion register partitions automatically. Writes through pypaimon or console uploads require manual registration. |
All partition directories under the table path. |
|
Partition expiration and storage tiering |
Supported. |
Not supported. |
|
|
Supported. |
Not supported. |
|
Query planning with many partitions |
Lists partitions from DLF Catalog. |
Lists OSS directories level by level. Planning takes longer as the number of partitions grows. |
Register and manage partitions
The following Spark SQL statements apply only to tables with managed partitions enabled. Supported Spark and Flink writers that use DLF Catalog register partitions automatically, as does DataFusion. For a partition that has not been registered, run ADD PARTITION when you know its value, or run MSCK REPAIR TABLE to scan the table directory. After writing with pypaimon, call catalog.create_partitions() to register the partition.
ALTER TABLE t ADD IF NOT EXISTS PARTITION (dt = '20261105');
ALTER TABLE t DROP PARTITION (dt = '20261105');
MSCK REPAIR TABLE t;
MSCK REPAIR TABLE t DROP PARTITIONS;
MSCK REPAIR TABLE t SYNC PARTITIONS;
SHOW PARTITIONS t;
ANALYZE TABLE t PARTITION (dt = '20261105') COMPUTE STATISTICS NOSCAN;
ADD PARTITION registers a partition and creates an empty directory if needed. DROP PARTITION unregisters it and deletes its directory data. MSCK REPAIR TABLE registers unregistered directories; its DROP PARTITIONS form unregisters missing directories, and SYNC PARTITIONS performs both operations. ANALYZE TABLE updates file count, size, and last-write time. Without NOSCAN, it reads Parquet or ORC file footers to count rows.
DROP PARTITION deletes data. Register partitions before downstream Spark or Flink queries. MSCK REPAIR TABLE lists all partition directories, so register known partitions with ADD PARTITION when a table has many partitions.
A PARTITION clause must begin with the first partition column. For a table partitioned by (dt, hour), you can specify (dt = '20261105') but not only (hour = '08').
Partition expiration and storage tiering
DLF storage optimization manages partition expiration and storage tiering for Format tables with managed partitions. You do not need to configure OSS lifecycle rules. A table without managed partitions or a table that uses value-only partition paths does not support these lifecycle features. A nonpartitioned table cannot use partition expiration, but its storage class can be changed at the table level.
Expire partitions
Set partition.expiration-time to the retention period. If unset, partitions are retained indefinitely. partition.expiration-strategy defaults to values-time, which calculates age from the partition value; use update-time to calculate it from the last update. Use partition.timestamp-formatter for the date format and partition.timestamp-pattern to combine partition columns. The first partition column is used by default.
ALTER TABLE order_detail SET TBLPROPERTIES (
'partition.expiration-time' = '30 d',
'partition.timestamp-formatter' = 'yyyyMMdd'
);
ALTER TABLE order_detail_hourly SET TBLPROPERTIES (
'partition.expiration-time' = '7 d',
'partition.timestamp-pattern' = '$dt $hour:00:00',
'partition.timestamp-formatter' = 'yyyyMMdd HH:mm:ss'
);
When a partition expires, DLF deletes its directory data and unregisters the partition.
Change storage class
Enable intelligent storage tiering in the console or change an individual partition to infrequent-access, archive, or cold archive storage. For the console procedure, see Paimon table automatic storage optimization. For a nonpartitioned table, change the storage class of the entire table. A compute engine can read Archive data directly if archive direct reading is enabled on the bucket, or after the data is restored. Cold Archive data must be restored before a compute engine can read it.
Modify a Format table
Change the table schema
ALTER TABLE changes metadata without rewriting existing files. Use it to change the schema instead of deleting and recreating the table: a newly created table uses a new, empty directory and does not recover the deleted table's data.
|
Operation |
Spark SQL statement |
Effect on existing data |
|---|---|---|
|
Add column |
|
Existing files do not contain the column, so it reads as |
|
Drop column |
|
The column is no longer returned. |
|
Rename column |
|
Parquet and ORC read fields by name. Existing files still contain |
|
Change column type |
|
Existing files are not converted. Check that existing data in one partition remains readable. |
|
Rename table |
|
Supported only within the same database. The data and table path do not change. |
You cannot rename partition columns or change their types. Do not change file.format in place. Existing files would be parsed as the new format and could fail to read.
Change partition columns or file format
To change the partition columns or file format of a Format table, create a table with the new definition, migrate the data, and then switch table names. Keep the old table until you validate the new one.
-
Create
t_newwith the new definition and the other properties oft. -
Copy data with
INSERT OVERWRITE t_new SELECT ... FROM t. For large tables, copy partitions in batches. -
Compare row counts and key metrics between the two tables.
-
Pause jobs that write to
t. RunALTER TABLE t RENAME TO t_bak, followed byALTER TABLE t_new RENAME TO t. Then resume the jobs. -
Have programs that access the table path directly retrieve the path again.
-
After validating the new table, delete
t_bak.
Advanced configuration
|
Table property |
Description |
Default value |
|---|---|---|
|
|
Set to |
|
|
|
Compression algorithm used when writing files. |
Parquet: |
|
|
Field delimiter in CSV files. |
|
Delete a table
By default, DLF retains a deleted table's data for one day. You can restore it from the Recycle Bin during the metadata protection period. After one day, the data is permanently deleted. Creating another table with the same name does not restore the data; the new table gets a new path.
-
In the database list, click your database name.
-
In the table list, click Delete in the Actions column of the target table.
-
In the dialog box, click OK.
You can also run DROP TABLE t to delete the table.