USER MANUALS

Configure the Denodo Server to Use the Denodo Lakehouse Accelerator as Cache

Virtual DataPort includes a Cache Engine that allows different materialization modes storing the data in an external relational database. The Denodo Lakehouse Accelerator is a very good option for the Denodo Cache Engine; on one hand, it is a powerful distributed query engine, and on the other, it relies on affordable, distributed, and scalable storage such as HDFS, AWS S3, or Azure ADLS.

Document Best Practices to Maximize Performance III: Caching provides generic best practices and considerations for the correct use of the cache system in Denodo. In addition Fine-grained privileges and caching best practices provides best practices in environments using caching and fine-grained privileges like row restrictions or masking. These documents provide a good starting point to correctly configure the caching system. However, caching in object storage—and using the Denodo Lakehouse Accelerator in particular—requires additional recommendations to consider.

Starting with Denodo 9.5, if the Lakehouse Accelerator is configured as the cache engine, these tables will always be stored in Iceberg format. Apache Iceberg is a high-performance table format designed for large analytic datasets. This provides multiple advantages over previous cache configurations based on traditional Hive tables in Parquet format:

  • Better performance: Especially when accessing and managing the internal cache control tables that store information about the cache.

  • Better maintenance: Providing specific procedures to periodically optimize these tables by leveraging Iceberg’s capabilities.

Note

These cache optimizations using Iceberg format apply only to Lakehouse Accelerator data sources registered in Denodo 9.5 or later updates. See the section Backward Compatibility for more details.

Configuring the Denodo Lakehouse Accelerator to use Iceberg Tables

In order to cache the data using tables in Iceberg format you have to follow these steps:

  1. Install and configure the Denodo Lakehouse Accelerator (see Denodo Lakehouse Accelerator Guide).

  2. Open Design Studio.

  3. Open the embedded_mpp data source, which is located in the admin_denodo_mpp database.

  4. Configure the bulk data load in the embedded_mpp data source.

  5. Click the Read Write tab.

  6. Select Specify custom catalog and schema.

  7. Click Reload Catalogs and Schemas.

  8. Select an Iceberg catalog. By default, the cluster includes one called iceberg.

  9. Click Save.

    Configure Iceberg tables for cache

Cache Maintenance for the Denodo Lakehouse Accelerator

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. Consider creating jobs using the Denodo Scheduler to run the following procedures periodically:

These procedures execute the following maintenance operations to ensure optimal query performance and minimize storage costs:

  • 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.

For detailed information on these maintenance tasks, refer to the documentation for each procedure.

Note

For Denodo Lakehouse Accelerator On Velox, operation Compact Data Files is not supported for version 2.1.0. Additionally to the above procedures, execute also COMPACT_CACHE.

Note

For Denodo 9.4 and earlier, execute procedure COMPACT_CACHE instead.

Concurrent preload of cached views with the Denodo Lakehouse Accelerator

To maximize performance and throughput across massive datasets, Iceberg relies on an optimistic concurrency control (OCC) model (see the Apache Iceberg Reliability Documentation <https://iceberg.apache.org/docs/1.6.0/reliability/>). As Apache Iceberg has been specially designed for analytical data lakes, its concurrency model assumes that conflicts are rare.

Therefore, it allows multiple processes to read and write simultaneously without heavy locking overhead. If two batch jobs try to alter the same data simultaneously, Iceberg safely aborts one rather than allowing it to silently corrupt or overwrite the other’s work. In highly concurrent environments, this design naturally requires a strategy for handling overlapping writes.

Specifically, when two or more concurrent UPDATE queries are executed on the same Iceberg table, the first one succeeds while the others fail.

When preloading cached views in Virtual DataPort, an UPDATE is performed on one of the internal control tables after the data is loaded. If two or more cache preload operations finish at almost the same time, this control table UPDATE can occur concurrently, causing some of the preload operations to fail.

When using Denodo Scheduler to preload cached views you can mitigate these issues by either:

  • Executing only one job at the same time and setting the concurrency of your job to 1. This ensures that no concurrent cache preloads will be executed, avoiding the issue.

  • Alternatively, if limiting to only one job with concurrency 1 is not possible, for Simple Cache Management jobs, retries can be enabled only for queries that returned an error. See Retry Section.

Add feedback