Databricks OPTIMIZE对分区Delta表无作用问题求助

我有一个按Partition_Date分区的Delta表，发现2023-01-19分区存在大量小数据文件。执行以下OPTIMIZE命令后，日志显示未进行任何分箱打包操作，小文件数量无变化：

Running OPTIMIZE:
OPTIMIZE delta.`dbfs:/mnt/backup/bronze/DeviceData`
WHERE Partition_Date = '2023-01-19'
+----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|path                              |metrics                                                                                                                                                     |
+----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|dbfs:/mnt/backup/bronze/DeviceData|{0, 0, {NULL, NULL, 0.0, 0, 0}, {NULL, NULL, 0.0, 0, 0}, 1, NULL, 0, 63, 63, true, 0, 0, 1705194826563, 1705194828732, 8, 0, NULL, {0, 0}, 7, 7, 0, 0, NULL}|
+----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

随后执行VACUUM命令也未删除任何文件：

Running VACUUM:
VACUUM delta.`dbfs:/mnt/backup/bronze/DeviceData` RETAIN 0 HOURS
Deleted 0 files and directories in a total of 653 directories.
+----------------------------------+
|path                              |
+----------------------------------+
|dbfs:/mnt/backup/bronze/DeviceData|
+----------------------------------+

使用环境：Databricks Runtime 14.2（包含Apache Spark 3.5.0、Scala 2.12）。

更新信息 #

• 更新1：该分区自创建后无新数据写入，其他分区执行OPTIMIZE可正常优化。
• 更新2：关闭spark.databricks.delta.optimize.preserveInsertionOrder参数后重新执行OPTIMIZE，成功完成优化，日志如下：

{
    "numFilesAdded": 70,
    "numFilesRemoved": 4381,
    "filesAdded": {
        "min": 16899041,
        "max": 613054015,
        "avg": 303944954.8857143,
        "totalFiles": 70,
        "totalSize": 21276146842
    },
    "filesRemoved": {
        "min": 159655,
        "max": 9243024,
        "avg": 4173938.1842045197,
        "totalFiles": 4381,
        "totalSize": 18286023185
    },
    "partitionsOptimized": 1,
    "zOrderStats": {
        "strategyName": "minCubeSize(107374182400)",
        "inputCubeFiles": {
            "num": 0,
            "size": 0
        },
        "inputOtherFiles": {
            "num": 4381,
            "size": 18286023185
        },
        "inputNumCubes": 0,
        "mergedFiles": {
            "num": 4381,
            "size": 18286023185
        },
        "numOutputCubes": 1,
        "mergedNumCubes": null
    },
    "numBatches": 1,
    "totalConsideredFiles": 4381,
    "totalFilesSkipped": 0,
    "preserveInsertionOrder": false,
    "numFilesSkippedToReduceWriteAmplification": 0,
    "numBytesSkippedToReduceWriteAmplification": 0,
    "startTimeMs": 1705705885113,
    "endTimeMs": 1705710361132,
    "totalClusterParallelism": 4,
    "totalScheduledTasks": 1,
    "autoCompactParallelismStats": null,
    "deletionVectorStats": {
        "numDeletionVectorsRemoved": 0,
        "numDeletionVectorRowsRemoved": 0
    },
    "numTableColumns": 7,
    "numTableColumnsWithStats": 7,
    "totalTaskExecutionTimeMs": 8150342
}

原因分析 #

最初OPTIMIZE无效果的核心原因是**spark.databricks.delta.optimize.preserveInsertionOrder参数默认开启**，该参数要求优化操作严格保留数据的插入顺序。

当该参数为true时，Delta Lake会优先保证插入顺序不被打乱，若目标分区的小文件恰好处于"按插入顺序连续排列"的状态，且合并这些文件无法在不破坏插入顺序的前提下生成符合大小阈值的文件，OPTIMIZE就会跳过合并操作——即便文件数量多、单文件体积小。

关闭该参数后，Delta Lake不再强制保留插入顺序，可以自由合并小文件，因此优化操作成功执行。而VACUUM未删除文件是因为之前的OPTIMIZE没有生成待清理的旧文件，自然无内容可删除。

内容的提问来源于stack exchange，提问作者Steffen Mangold