USER MANUALS

OPTIMIZE_LAKEHOUSE_ACCELERATOR_CACHE_TABLES

Description

The cache in Virtual DataPort includes two different kinds of tables:

  • Control tables: These are tables that VDP uses to internally manage the cache data.

  • Data tables: These are the tables that contain the data corresponding to a cached view in Virtual DataPort.

Starting with Denodo 9.5, if the Lakehouse Accelerator is configured as the cache engine these tables will use Iceberg format. Over time, frequent data modifications on Iceberg tables can cause an accumulation of old metadata files, fragmented data, and wasted storage. Therefore, to prevent performance degradation and manage the lifecycle of transactional data, Iceberg tables require regular maintenance. The stored procedure OPTIMIZE_LAKEHOUSE_ACCELERATOR_CACHE_TABLES executes the following maintenance operations to Iceberg cache control tables to ensure optimal query performance and minimize storage costs (see OPTIMIZE_LAKEHOUSE_ACCELERATOR_ICEBERG_VIEWS for data tables):

  • Remove Orphan Files: Deletes files that are no longer referenced by any metadata file of a table.

  • Remove Snapshots: Cleans up expired snapshot metadata associated with the table.

  • Compute Stats: Computes the statistics of the table.

  • Compact Data Files: Rewrites the data files of a specified table so that they are merged into fewer but larger files.

  • Optimize Manifests: Rewrites manifest files of an Iceberg table to optimize table metadata.

Consider creating jobs using the Denodo Scheduler to run this procedure periodically (e.g. once per week).

Note

In Denodo Lakehouse Accelerator 2.1.0 using the Presto on Velox engine, the Compact Data Files operation is not supported. This feature requires Presto version 0.298 or higher.

Syntax

OPTIMIZE_LAKEHOUSE_ACCELERATOR_CACHE_TABLES (
    lakehouse_accelerator_data_source_database  : text,
    lakehouse_accelerator_data_source_name  : text,
    compute_stats : boolean,
    compact_data_files : boolean,
    optimize_manifests : boolean,
    remove_orphan_files : boolean,
    remove_orphan_files_older_than : timestamp,
    remove_snapshots : boolean,
    remove_snapshots_older_than : timestamp,
    remove_snapshots_retain_last : integer
)

  • lakehouse_accelerator_data_source_database (optional): name of the database where the Lakehouse Accelerator data source is located. Default value: admin_denodo_mpp.

  • lakehouse_accelerator_data_source_name (optional): name of the Lakehouse Accelerator data source containing cache control tables. Default value: embedded_mpp.

  • compute_stats (optional): if true, executes the table statistics computation. Default value: false. This is equivalent to executing the COMPUTE_SOURCE_TABLE_STATS stored procedure.

  • compact_data_files (optional): if true, executes data file compaction to optimize file sizes. Default value: true.

  • optimize_manifests (optional): if true, optimizes the Iceberg manifest files. Default value: true.

  • remove_orphan_files (optional): if true, removes unreferenced (orphan) data files. Default value: true. This is equivalent to executing the REMOVE_ICEBERG_VIEW_ORPHAN_FILES stored procedure.

  • remove_orphan_files_older_than (optional): timestamp before which orphan files will be deleted. Default value: 3 days ago.

  • remove_snapshots (optional): if true, purges expired snapshots. Default value: true. This is equivalent to executing the REMOVE_ICEBERG_VIEW_SNAPSHOTS stored procedure.

  • remove_snapshots_older_than (optional): timestamp before which snapshots will be deleted. Default value: 5 days ago.

  • remove_snapshots_retain_last (optional): minimum number of ancestor snapshots to retain. Default value: 1.

Note

Default Execution and Safety Overrides

Behavior when optional parameters are omitted:

If this stored procedure is executed using its default parameters (or by omitting the optional inputs), both remove_snapshots and remove_orphan_files default to true. The procedure will automatically perform a full, safe cleanup on the default Lakehouse Accelerator cache control tables (using admin_denodo_mpp and embedded_mpp) based on the following built-in thresholds:

  • Snapshots: Purges expired snapshots older than 5 days, while strictly protecting and retaining the last active snapshot (remove_snapshots_retain_last = 1). The underlying Iceberg cache control tables will never be left without a valid state.

  • Orphan Files: Deletes unreferenced data files older than 3 days.

Detailed Parameter Behavior & Rules (When customized):

  • Snapshot Expiration Priority: When customizing both remove_snapshots_older_than and remove_snapshots_retain_last, the retention count acts as a strict safety override. A snapshot is only deleted if it is older than the specified timestamp AND does not belong to the last N ancestors. The retention condition always wins to prevent accidental data loss.

  • Orphan Files Safety Margin: The 3-day buffer for orphan files protects active concurrent writes. It ensures the procedure does not accidentally delete temporary or in-flight files currently being written by active transactions. Be cautious if you reduce this value.

This stored procedure returns one row for each optimized cache control table optimized, with the following fields:

  • catalog: name of the Iceberg catalog.

  • schema: name of the schema where the Iceberg table is located.

  • table: name of the Iceberg table.

  • status: an integer representing the execution outcome:

    • 0 (success): All requested maintenance operations executed successfully for the table.

    • 1 (error): One or more maintenance operations failed. Check the details field for the error message.

  • details: descriptive message providing context or error details regarding the execution.

Privileges required

Only users that have Global Admin privileges can execute this procedure. This means, the following users can execute this procedure:

  • Global administrators

Examples

Example 1: Optimize cache control tables using default parameters

Execute the procedure on the cache control tables of the default data source (admin_denodo_mpp.embedded_mpp) using default parameters. This will perform all operations except for computing the statistics of the table. This includes compacting data and metadata, removing orphan files older than 3 days ago and snapshots older than 5 days ago.

CALL OPTIMIZE_LAKEHOUSE_ACCELERATOR_CACHE_TABLES();

Example 2: Optimize cache control tables using timestamp thresholds

Execute all maintenance operations on the cache control tables of test_database.test_data_source. This removes orphan files older than 20 days and snapshots older than 10 days (assuming a current timestamp of 30-01-2025 12:13:14).

CALL OPTIMIZE_LAKEHOUSE_ACCELERATOR_CACHE_TABLES(
    'test_database',
    'test_data_source',
    true,
    true,
    true,
    true,
    '10-01-2025 12:13:14',
    true,
    '20-01-2025 12:13:14',
    null
);

Example 3: Optimize cache control tables while retaining a minimum number of snapshots

Execute all maintenance operations on the cache control tables of test_database.test_data_source, removing orphan files older than 20 days and configuring snapshot purging to preserve a minimum of 10 snapshots.

CALL OPTIMIZE_LAKEHOUSE_ACCELERATOR_CACHE_TABLES(
    'test_database',
    'test_data_source',
    true,
    true,
    true,
    true,
    '10-01-2025 12:13:14',
    true,
    null,
    10
);

Example 4: Performance optimization only (No file/snapshot deletion)

Execute statistics computation, data file compaction, and manifest rewriting for the cache control tables of test_database.test_data_source without deleting historical snapshots or orphan files.

CALL OPTIMIZE_LAKEHOUSE_ACCELERATOR_CACHE_TABLES(
    'test_database',
    'test_data_source',
    true,
    true,
    true,
    false,
    null,
    false,
    null,
    null
);
Add feedback