What Is New in Denodo 7.0 Update 20201116¶
This page lists the main enhancements added in the update 7.0 20201116 of the Denodo Platform. We also have published a video that explains these enhancements.
For a full list of enhancements and bug fixes, read the Release Notes in the Support Site or inside the zip file of the update.
There are several enhancements in the query optimizer that maximize the delegation of queries to the underlying data sources. These enhancements are particularly beneficial in queries that involve a star schema in which the data is obtained from different data sources.
Performance improvements in the query optimizer and the cost-based optimizer when choosing an execution plan of queries that involve complex views or views with a complex tree view.
You can now use the position of the column in the “ORDER BY” clause of an analytical function. For example:
SELECT department_id , employee_id , last_name || ', ' || first_name , salary , RANK() OVER ( PARTITION BY department_id ORDER BY 4 DESC -- This is equivalent to: -- "PARTITION BY department_id ORDER BY salary DESC" ) AS dept_ranking FROM employee;
The ORDER BY clause of a query can now include fields that are not projected.
Virtual DataPort (Data Sources)
Delimited files data sources: added support to read files in fixed-length format. That is, files in which all the columns have a predefined length.
In the JDBC data sources, you can now define an “Initial SQL query” that will be executed in this database right before every query executed by this data source. This query can include several interpolation variables. These variables can be properties of the connection (e.g. “@USER_AGENT”, “@CLIENT_IP”, etc.) or variables that are added by the “Resource manager”.
Added support to use an AWS IAM role to connect to Redshift and Athena. This enhancement also comes into play when loading data into these databases, using their bulk data load API.
Improvements in the introspection of multidimensional databases:
The introspection of SQL Server Analysis Services (SSAS) and SAP BI/BW is faster.
Oracle Essbase: up until now, to do introspection of Essbase, the user account required the privilege “manager (database)” in Essbase. Now, you can configure Virtual DataPort to not require this privilege. In this update, this feature is disabled by default. The section Requirements of the Essbase 11 Adapter explains how to enable it.
Virtual DataPort (Cache Engine)
The Cache Engine can now use S3 paths when loading data into Presto and other Hadoop-based sources, when the default location of the schema is HDFS.
This enhancement also benefits data movements, which share the same bulk data load mechanism than the Cache Engine.
To enable this feature with Presto, see the section Bulk Data Load - Presto.
In views with full cache mode and Time to Live set to never expire, the queries that the execution engine executes to retrieve data from the cache database are more efficient. These queries no longer filter by the expiration time of the data because the rows on these tables never expire.
Virtual DataPort (Web Services Published by Denodo)
Open API specifications of the REST web services: the Open API specifications now include the fields of the views as input parameters of the operations. Previously, the definition of the “GET” operations only included these input parameters: “$filter”, “$count”, “$format”, “$orderby”, etc. This will make it easier for developers to build applications that query these web services.
Virtual DataPort (Administration Tool)
Virtual DataPort (JDBC Driver)
The JDBC driver has two enhancements that simplify the connections to Virtual DataPort when SSL is enabled:
Now, if you add the driver property sslTrustServerCertificate=true, the driver does not validate if the SSL certificate presented by the server is signed by a well-known Certification Authority (CA). This makes it easier to use a self-signed certificate or a certificate signed by a CA that your organization uses internally. However, consider that not validating the certificates can be considered a security risk.
Now you can configure the driver to load a trust store file instead of trusting only in the certificated of the trust store of the Java Runtime Environment (JRE) of the client application. To do this, add the driver property
sslTrustStoreLocation=<path to the trust store>.
You can specify these properties as a driver property or as a parameter in the connection URL.
Virtual DataPort (Operations and Security)
Added support for Kerberos constrained delegation in cross-domain scenarios.
New default role: “create_temporary_table”. The users with this role can create temporary tables. This is useful to allow a service account to create temporary tables to calculate intermediate results, without granting the privilege of creating other elements.
You can now configure Virtual DataPort so its administration tool and the Denodo JDBC driver use a single port - by default, 9999 - to communicate with Virtual DataPort. By default, the communication requires two ports (9999 and 9997). If there is a firewall between the Denodo and the client applications, consider enabling this features because it simplifies the configuration of firewalls. This does not apply to the Denodo ODBC driver because it uses only one port (9996).
Tableau integration: now you can export the result of the queries to .hyper files.
Added support to store the metadata of the Data Catalog in MySQL 8 (see the list of databases you can use to store the metadata).
Several improvements in SELECT_NAVIGATIONAL result in more efficient queries. This is noticeable when adding fields of other views to the result of the query.
Added support to store the metadata of Scheduler in Azure SQL. The section Database Settings of the Scheduler Administration Guide lists the databases you can use to store the metadata of Scheduler.
New option to create revisions: Include users and privileges. With this option enabled, a revision will include the SQL statements to replicate the privileges of the elements included in the revision. Specifically, the statements to create the users and roles of the owners of each element, and the statements to grant the privileges over these elements to these users/roles.
Added support to store the metadata of the Solution Manager in Oracle 18 and 19c; previous version of Oracle were already supported. The section Setting-Up an External Database lists the databases you can use to store the metadata.
Information about Deprecated Features
No features have been marked as deprecated with the release of this update.
The following sections list all the features that are currently deprecated in Denodo 7.0: