Cache Module¶
Virtual DataPort incorporates a Cache Engine that can store a local copy of the data retrieved from the data sources, in a JDBC database. This may reduce the impact of repeated queries hitting the data source and speed up data retrieval, especially with certain type of sources.
This is the list of databases that you can use for this:
Database |
Driver is included in the Denodo Platform |
---|---|
Amazon Athena |
Yes |
Amazon Redshift |
Yes |
Azure SQL Database |
Yes |
Azure Synapse SQL (previously known as Azure SQL Data Warehouse) |
Yes (Microsoft SQL Server driver) |
Databricks |
Yes |
Hive 2.0.0 (HiveServer2) |
No |
HP Vertica 7 |
No |
IBM DB2 8, 9, 9 for z/OS, 10, 10 for z/OS, 11 and 11 for z/OS |
No |
Impala 2.3 |
No |
Microsoft SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016 |
Two drivers are available and both are included jTDS: included. Microsoft driver (MS driver) |
MySQL 4 and 5 |
No |
Netezza 6.0 and 7.0 |
No |
Oracle 8i, 9i, 10g, 11g, 12c, 12c In-Memory, 18c and 19c |
Yes |
Oracle TimesTen 11g |
No |
PostgreSQL 9 and 10 |
Yes |
Presto 0.1x |
Yes |
SAP HANA 1.0 and 2.0 |
No |
Snowflake |
Yes |
Spark SQL 2.x |
Yes |
Teradata 12, 13, 14, 15 and 16 |
No |
Vertica 7 and 9 |
No |
Yellowbrick |
Yes |
The drivers that are not included in the Denodo Platform have to be downloaded from the vendor’s website.
In addition to the adapters listed above, Virtual DataPort provides a generic adapter that can be used with any other JDBC database to store cached data. The section Generic Support for Other Databases explains how to configure it. However, we strongly recommend that you use one of the databases of the list above because the performance when querying the cache database will be better. The reason is that Virtual DataPort will push down more operations to the supported databases than the generic one.
When you enable the Cache Engine for the first time, the default database to store the cache data is an Apache Derby database that is embedded with Denodo.
Important
We advise against using this embedded Apache Derby database. This database is provided to show case the Cache Engine and doing small projects. Instead, use an external database, especially on production environments.
To enable the cache engine, follow these steps:
Create a catalog or schema in the database specifically for the Cache Engine of Denodo.
This is not mandatory but highly recommended. That is because the Cache Engine creates many tables (one for each view of Denodo where the cache is enabled). Therefore, it is useful for these tables to be isolated from the rest of the elements of that database.
This new catalog or schema has to be created with these options:
Support for multi-byte characters (e.g. UTF-8). This will allow to store data that contains multi-byte characters (e.g. Japanese characters). Also, to be able to enable cache for a view whose name or fields has multi-byte characters.
Binary collation. In a database management system, the collation specifies how the database compares and sorts character strings. One of the main effects of the collation is the order of the results in queries that have an ORDER BY with columns that are character strings. For example, the collation determines whether or not uppercase letters and lowercase letters are treated as the same, sensitivity to accents (e.g. is “A” the same as “Á”), etc.
With binary collations, the data is ordered according to the numeric value of each byte of the character strings.
Important
Validate with the administrator of this database that the collation of this catalog/schema is binary.
The Execution Engine of Virtual DataPort expects the data to be sorted following the rules of a binary collation. If the collation is not binary, queries that execute JOIN operations of data obtained from the cache database and from another database may return incorrect results.
Enable the Cache Engine at a server level (see section Configuring the Cache). Alternatively, you can enable the cache module only for some databases (see section Configuring and Deleting Databases).
When the Server initializes the cache, it creates a set of tables in the database that will have information about the data stored in the cache. For each query whose data is cached, the Server stores a Query Pattern that later is used to know if a query can be “solved” with the data stored in the cache. A Query Pattern is formed by:
An id that uniquely identifies every Query Pattern.
The name of the view that these data belongs to.
The conditions of the WHERE clause of the query used to retrieve the data from the data source.
The expiration time of the data set that the Query Pattern represents. Once this time is reached, the data set associated with this Query Pattern is marked as invalid and no longer used. At regular intervals (Maintenance Period), the Server deletes the data sets that are marked as invalid. Note that this applies to cache partial only. For cache full, the expiration date represents the time of the latest cache refresh.
Status of the Query Pattern. This can be valid, processing (while the data is being stored in the cache) or invalid (when the expiration time of the Query Pattern has been reached).
Enable the cache in the views that requires it (see section Configuring the Cache of a View).
When enabling the cache on a view, the cache module creates a table in the cache’s database that will store the cached data of this view. The data types of the fields of this table are equivalent to the data types of the fields of the view in Virtual DataPort.
When a user executes a query that involves a view with cache enabled, the Server checks if it can retrieve the data from the cache. If not, it retrieves the data from the data source, stores it in the cache and creates a Query Pattern that represents these data.
The cache can also be by-passed for specific queries if you need to access the actual data.
When querying a cache with required fields (e.g. a Web service base view with mandatory input parameters) and the view is cached, you do not have to provide a value for the input parameters. However, you have to do it if the data has to be retrieved from the data source.
Warning
Users should be careful when enabling the cache for views that involve data sources with pass-through credentials enabled. The section Considerations When Configuring Data Sources with Pass-Through Credentials explains this in more detail.
Warning
The tables of the database that store cached data should be monitored for excessive fragmentation and compacted periodically if needed. In particular, with tables whose rows are invalidated very often.
You can see information about the Query Pattern of the database by
executing the Denodo stored procedure CACHE_CONTENT
. The section
Displaying the Contents of the Cache of the VQL Guide
explains how to invoke this procedure.