What Is New in Denodo 7.0 Update 20200310¶
This page lists the main enhancements added in the update 7.0 20200310 of the Denodo Platform.
We also have published a video with this content.
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.
The ORDER BY clause of a query can now include fields that are not projected.
New SQL functions to deal with datetimes with time zone:
AT TIME ZONE <time zone>: it does two things:
If the input parameter is a timestamp (without time zone), it applies the offset of the time zone you specify and returns a timestamp with the time zone.
If the input parameter is a timestamp with time zone, it applies the offset of the time zone you specify and returns a timestamp without time zone.
SELECT timestamp_with_time_zone_field AT TIME ZONE 'America/Chicago'
convert_timezone(...): it transforms timestamp and timestamptz values from one time zone to another. Find the syntax of this function in the dialog Help > Functions list of the administration tool.
New stored procedure: MIGRATE_DATE_TYPES. It scans all the base views, interface views and complex types and tries to change the type of the columns of type “date” (deprecated) to one of the new datetime types included in Denodo 7.0 (localdate, time, timestamp or timestamptz). There will be views for which there is not enough information to do this change and the procedure will inform of that. Previously, this conversion could only be done automatically when exporting the metadata from Virtual DataPort 6.0, during the upgrade process to Denodo 7.0. With this procedure, you can do this change in the data types after importing the VQL file from version 6.0 to 7.0 and also, do it on only a few databases.
During a bulk data load to an HDFS-based database (Impala, Presto, Spark, Databricks…), Denodo creates files with the format Apache Parquet. Starting with this update, by default, Denodo creates these files with the compression algorithm “snappy”.
The UNION operations that involve three or more views are executed faster.
Added support to push the operation JOIN under a UNION when the query involves partitioned UNIONs by the same fields in several JOIN branches.
The cost-optimizer now propagates the primary key of the views through UNIONs. This will allow the cost-optimizer to choose more efficient plans.
The execution trace now explains why the optimizer did not apply the optimization “JOIN branch pruning”.
Virtual DataPort (Data Sources)
Added support for new databases:
Azure Server Analytics Services (multidimensional database).
Databricks Delta (use adapter Spark SQL 2.x Databricks)
Oracle 18c and Oracle 19c
New features for Remote Tables:
Support to execute the statement INSERT INTO … SELECT on Remote Tables.
Support to create, edit and delete indexes on Remote Tables. To do this, go to the tab Indexes of the Options of the Remote Table.
New command REFRESH: it clears the content of a Remote Table and loads it executes again the query that was used to create the Remote Table in the first place.
When creating a base view over the multidimensional data source Microsoft SQL Server Analysis Services, you can select more than one hierarchy per dimension.
DF, XML and JSON data sources with an HTTP route:
You can now configure the connection timeout.
There is a new pagination strategy: obtaining the URL to the following “page” from an HTTP header of the responses of the REST API you are connecting to.
You can configure the data source to ignore certain HTTP errors. E.g. if you are connecting to a REST API that returns information about customers and the URL of the data source has an interpolation variable “input_customer_id”, you may want to ignore the HTTP error 404 (Not Found). That is because this error does not mean that there was an error in the request, only that a customer with that id does not exist.
Virtual DataPort (Cache Engine)
Support for new cache databases:
Virtual DataPort (Web Services Published by Denodo)
New features that can be used to hide information about the software used to publish REST web services (i.e. the Denodo Platform). This is particularly useful if the REST web services published by Denodo will be available to the public:
Disable the OpenAPI/Swagger endpoint of the REST web services.
In the XML representation of the REST web services, change the namespace (by default, “http://www.denodo.com/restful”) and its prefix (by default, “denodo”).
New settings for when the client applications will connect to the REST web services through a reverse proxy.
Other options to remove all references to the word “Denodo” from the XML and JSON responses of the REST web services.
To configure this, go to the menu Administration > Server configuration and click the tab REST web services.
When a request to a REST web service fails because a query to an underlying data source failed, the response now includes the name of the data source that caused the issue.
Virtual DataPort (Administration Tool)
The dialogs Browse of the administration tool are faster. The administration tool sends less
LIST FILEstatements and the server executes them much faster. This is noticeable in directories with more than one hundred files.
Virtual DataPort (JDBC Driver)
The JDBC driver now can expose the databases of Virtual DataPort as schemas instead of catalogs. To enable this, add the property
publishCatalogsAsSchemas=trueto the configuration of the connection. This improves the interoperability with third-party tools that do not have a connector for Denodo.
See more about the new parameter
publishCatalogsAsSchemasin the section JDBC Driver Parameters of the Developer Guide.
Virtual DataPort (ODBC Driver)
With this update, when TLS is enabled on Virtual DataPort, the Denodo ODBC driver establishes the connections faster. To benefit from this, you do not need to update the ODBC driver in your application because the enhancement involved modifying the ODBC interface of Virtual DataPort, not the driver.
Virtual DataPort (Operations and Security)
The Denodo Monitor can now log requests executed over databases created after the Denodo Monitor was launched. The Denodo Monitor of previous updates logs the queries sent to the databases that existed at the time the Monitor was launched but not the queries to the new databases. To benefit from this update, you need to unpack the new version of the Denodo Monitor.
This update adds support to use the LDAP directory “Oracle Internet Directory”, to obtain the roles granted to users that log in to Virtual DataPort.
You can now restrict the IP address on which a server of the Denodo Platform listens to connections. This applies to all the server components of the Denodo Platform: Virtual DataPort, Scheduler, the Apache Tomcat included with Denodo, etc.
By default, these components listen to connections in all the network interfaces. To comply with the security policies of your organization, you may want to restrict these components to listen to connections only on one of the IP address. To do that, follow the instructions of the appendix Configuring the Network Interface Restriction in Virtual DataPort of the Installation Guide.
The stored procedure GET_VIEW_STATISTICS returns an additional column:
last_updated. This column contains the last time the statistics of a view were gathered. This can be useful to monitor programmatically how recent the statistics of a view are.
Several improvements in SELECT_NAVIGATIONAL result in the following improvements on the Data Catalog:
When querying a view, you can now apply scalar functions to relationship fields. That is, if you are querying the view “order_details” that has the role “customer”, you can add a field “UPPER ( customer / customer_name)”.
In queries that have relationship fields from two or more views, you can now obtain the total number of records.
Added support to index data using Elasticsearch version 7.x (earlier versions were already supported).
Added support to store the metadata of the Data Catalog in MySQL 5.6, MySQL 5.7 version was already supported (see list of databases you can use to store the metadata).
When querying a Denodo web service from the Data Catalog, you can also see the URL of this web service so you can use it from other applications.
You can disable the use of HTML in the descriptions of the elements and in the values of properties.
Revisions can now include Scheduler jobs of type VDP (in addition to Scheduler jobs of the type VDP Cache and VDP Indexer).
Solution Manager launches the Denodo Monitor to inspect the Denodo servers. When the Solution Manager stops, the Denodo Monitor stops as well. Now, you can configure the Solution Manager to keep the Denodo Monitor running when the Solution Manager stops. When the Solution Manager is up again, it will detect that the Denodo Monitor is running.
This feature is useful if you use Denodo Monitor to keep a history of the activity of the Denodo servers. For example, to log all the queries that the Virtual DataPort servers of the organization run.
This feature is disabled by default. The section Monitoring explains how to enable it.
REST API of the Solution Manager:
The API is now stateless. With previous updates, you need to obtain a cookie to be able to invoke any operation of the API. This is no longer necessary; you can send a request to any operation using HTTP Basic authentication or HTTP SPNEGO (Kerberos). This makes it much simpler to use this API from a script, from automation servers like Jenkins, etc.
The existing “login” endpoint still works so you do not need to modify the scripts that you already have in place.
You can obtain the list of servers of an environment.
You can restrict the IP address on which a server of the Denodo Platform listens to connections. This applies to Solution Manager, License Manager, etc. By default, these components listen to connections in all the local network interfaces. You may want to restrict these components to listen to connections only on one of the IP address to comply with the security policies of your organization. To do that, follow the instructions of the appendix Configuring the Network Interface Restriction in the Solution Manager of the Installation Guide.
Information about Deprecated Features
This subsection lists the features that have been marked as deprecated with the release of this update.
The comparison operators containsand, containsor and iscontained are now deprecated and they will be removed in the next major version of the Denodo Platform (Denodo 8). The section Contains, Containsand, Containsor and Iscontained Operators of the Migration Guide explains how to replace them with SQL-standard operators.
SELECTstatements is now deprecated and it will be removed in the next major version (Denodo 8).
The filter Decrypt (PBE with MD5 and DES) is now deprecated. This is the filter you can set in the data sources DF, JSON and XML.
API of custom wrappers: the method
CustomWrapperResult.addRow(Object, List<CustomWrapperFieldExpression>)is now deprecated. Use
The section Features Deprecated in Virtual DataPort 7.0 lists all the features that are deprecated in Denodo 7.0.