CONNECT Privilege¶

In Denodo 7.0 you must grant the CONNECT privilege to a user over a database in order to allow this user to access any of the elements of that database. The other privileges over the database or its elements are disregarded if the user/role does not have the privilege CONNECT.

In previous versions, the CONNECT privilege was only checked when establishing the connection.

Because of this change, grant the users/roles the CONNECT privilege over the databases in which they already have other privileges.

Privileges of Users with the Role “serveradmin”¶

Denodo 6.0 has an option to grant LDAP/Kerberos users that have the role “serveradmin” the permission to grant/revoke privileges to users and roles, including themselves. This was located in tab Privileges of the Server configuration.

In this version this option is not available. Therefore, you need to add these users to the “assignprivileges” group of Active Directory.

Search Fields that Use the Type Date¶

During the process of exporting the VQL from a Denodo 6.0 installation, we recommend setting the following properties:

SET 'com.denodo.exportMigrationCompatibility80' = 'true';
SET 'com.denodo.vdb.catalog.exportMigrationCompatibility.migrateDateTypes' = 'true';

As explained in that section, with these properties, the Denodo server 6.0 analyzes the date fields to see if they can be exported to one of the new datetime types.

You can also use the procedure MIGRATE_DATE_TYPES in Denodo 7.0 at any time to migrate the types automatically at any time.

This section explains how to search what views still have fields of type date and how to change them.

Step #1

Execute these VQL statements on the new Denodo 7.0 installation:

-- Materialized table that returns information about base views, interface views
-- and materialized tables, but not derived views.
CREATE MATERIALIZED TABLE view_summary AS
SELECT database_name
    ,name
    ,view_type
FROM GET_VIEWS()
WHERE view_type IN (0, 2, 3)
CONTEXT('queryTimeout' = '120000');

-- Materialized table that returns information about the columns of
-- type "date".
CREATE MATERIALIZED TABLE view_date_column_summary AS
SELECT database_name
    ,view_name
    ,column_name
    ,column_vdp_type
    ,column_sql_type
FROM GET_VIEW_COLUMNS()
WHERE column_vdp_type = 'date' CONTEXT('queryTimeout' = '120000');

Step #2

Execute the following query. It returns the fields of type “date” of the base views, interface views and materialized views. We do not include derived views yet because in many of them, the type is inherited from an underlying view.

SELECT v.database_name
    ,v.name
    ,c.column_name
    ,c.column_vdp_type
    ,c.column_sql_type
FROM view_summary v
INNER JOIN view_date_column_summary c ON v.database_name = c.database_name
    AND v.name = c.view_name CONTEXT('queryTimeout' = '120000');

Step #3

For each field of the result, we recommend changing its type to one of the new data types:

• For base views, select the type that matches the type of the column in the table of the underlying database.

• For interface views, modify the definition of the field.

• For materialized views, recreate them with one of the new types instead of “date”.

Step #4

Execute the following query. It returns the fields of type “date”, including derived views.

SELECT database_name
    ,view_name
    ,column_name
    ,column_vdp_type
    ,column_sql_type
FROM GET_VIEW_COLUMNS()
WHERE column_vdp_type = 'date' CONTEXT('queryTimeout' = '120000');

Step #5

The type of a field of a derived view is still date if:

1. The field is defined as such by one of its underlying views.

2. Or if the field is the result of an expression that returns a date. For example, if a field is the result of the expression TO_DATE, the type of the field is date. In this case, you should use one of the new functions instead (TO_LOCALDATE, TO_TIME, TO_TIMESTAMP or TO_TIMESTAMPTZ depending on the type of the input field).

Subtracting “Date” Expressions Now Returns a Long¶

The result of subtracting two expressions of type date (<expression 1> - <expression 2>) is the number of days between <expression 1> and <expression 2>. The result is a positive number if <expression 1> is a more recent datetime than <expression 2>. Otherwise, the result is a negative number.

In Denodo 6.0, this number is of type int. In Denodo 7.0, this value is of type long. On very rare cases, this change may cause a query to fail. If it does, modify the view or the query.

See more about this in the section Arithmetic Operators for Datetime and Interval Values of the VQL Guide.

Backward Compatibility Properties¶

This section explains how to restore the behavior of previous versions of Denodo, regarding the values of type “date”. Although they are available, we recommend not using them and instead, switch to the new datetime types.

• The execution engine may implicitly convert a text value into a datetime value. In previous versions, the result of this conversion is a value of type “date”. In Denodo 7.0, literals may be converted to one of the new datetime types, not to “date”.

  To keep the behavior of previous versions, execute the following from the VQL Shell:

  SET 'com.denodo.vdb.parser.datetime.literals.transformToOldToDateFunction' = 'true';
  

• In Denodo 7.0, custom functions that in its Java source code have an input parameter or return an object of the Java class java.util.Calendar, now return a “timestamptz” value. In previous versions, they return a “date”.

  To keep the behavior of previous versions, execute the following from the VQL Shell:

  SET 'com.denodo.vdb.compatibility.datetime.custom.mapCalendarToDateType' = 'true'
  

• Temporary tables use the new datetime types. In previous versions they use “date”. To keep the behavior of previous versions, execute the following from the VQL Shell:

  SET 'com.denodo.vdb.parser.datetime.createsqltable.mapDateTimeToOldDateType' = 'true'
  

Procedure VIEW_DEPENDENCIES¶

Now when you call this stored procedure and the target has a dependency of the type storedprocedure, it returns the value Predefined Storedprocedure when this procedure is a predefined one.

Procedure GET_CATALOG_METADATA_WS¶

Now you can call this stored procedure to get information related to Web Services.

Procedures GET_ELEMENTS and GET_VIEWS¶

These procedures now differentiate materialized tables from base views. In previous versions, these procedures considered materialized views as base views.

In the result of these procedures, the value of the field subtype is now materialized and the value of view_type is now 3.

Oracle¶

Starting with Denodo 7.0 GA, right after a JDBC data source opens a connection to Oracle, it executes the following command on that connection:

ALTER SESSION SET NLS_DATE_FORMAT= 'YYYY-MM-DD';

By doing this, the pattern of the datetime values does not depend on the configuration of Oracle. Note that if in a previous version of Denodo you created a base view from a SQL query, the conditions of this query over datetime fields may not have the right format and the query will fail. In that case, modify the query so the datetime value follows the pattern “YYYY-MM-DD” (<year>-<month>-<day>). E.g. hire_date >= DATE '2018-02-03'.

Apache Hive¶

The Apache Hive driver is no longer distributed.

The adapters for Apache Hive 0.7, 0.10, 0.11 and 0.12 have been removed. If you were using any of these, use the adapter for Apache Hive 0.13 instead.

Impala JDBC Drivers¶

Due to license restrictions, the JDBC driver of Impala is no longer distributed.