Server Set-Up

From the Server set-up dialog you can configure all the settings of the Data Catalog server. It is divided into the following tabs:

Configure the Virtual DataPort Servers

In the Servers section of the VDP servers tab you can configure the Virtual DataPort servers that are listed in the login dialog.

Dialog to configure the Virtual DataPort servers listed in the login page

Dialog to configure the Virtual DataPort servers listed in the login page

To add a new Virtual DataPort server click on the Add server button and enter the following values:

  • Name. The name that will be shown in the login page for this server.

  • URL. The connection URL of the server, which follows the pattern //<host>:<port>/<database>. You should take into account the following considerations:

    • The database is optional.

    • If you include the database, only those users granted with the CONNECT privilege on that database will be able to connect to the Data Catalog.

    • If you do not include it, the users will be able to connect as long as they have the CONNECT privilege on any database.

    • You must include it in case the server is configured with LDAP authentication for a specific database.

  • Description. A description for the server.

Dialog to add a new Virtual DataPort server

Dialog to add a new Virtual DataPort server

Note

Saved queries are stored per user and Virtual DataPort server. The servers are identified by an internal identifier, so if a server is edited, it will maintain the same queries associated to it. However, if the server is removed, the queries are removed too.

Configure the Connection Settings to the Virtual DataPort Servers

In the Connection settings section of the VDP servers tab you can configure the global parameters of the connections created to execute queries against the Virtual DataPort Servers.

Dialog to configure the connection settings to the Virtual DataPort servers

Dialog to configure the connection settings to the Virtual DataPort servers

These parameters are:

  • Query timeout. Maximum time in milliseconds that the query will wait for the statement to be completed. If not indicated (or you enter 0 as a value), then it will wait until the execution is complete. By default it is 900,000 milliseconds.

  • Chunk timeout. Maximum time in milliseconds that the query will wait until it arrives a new chunk of results. Where this time is exceeded, Virtual DataPort returns the results extracted up to that moment. If not specified (or you enter 0 as a value), Virtual DataPort returns all the results together at the end of the statement run. By default it is 90,000 milliseconds.

  • Chunk size. Number of results that make up a chunk of results. When Virtual DataPort Tool obtains this number of results, it will return them to the Data Catalog, even though the Chunk timeout has not been reached. By default it is 1,000 rows.

Enable Single Sign-On with Kerberos

In the Authentication tab you can enable single sign-on with Kerberos in the Data Catalog.

Dialog to configure Kerberos authentication in the Data Catalog

Dialog to configure Kerberos authentication in the Data Catalog

Follow these steps:

  1. Enable the Use Kerberos option.

  2. Enter the Service Principal Name of the Data Catalog in the Server Principal field. You should enter the same value used to generated the keytab file.

  3. Drag-and-drop the keytab file to the Keytab file field. As an alternative, you can click the field and select it from the files browser.

  4. Consider to add a krb5 file to the Configuration file field if one of the following conditions is met and there is no krb5 file in the default location of your system:

    • The host where the Data Catalog runs does not belong to a Kerberos realm (e.g., a Windows Active Directory domain).

    • The host where the Data Catalog runs is Linux/Unix.

    • The service account in Active Directory configured for the Data Catalog has the option constrained delegation enabled.

    • You are in a cross-domain scenario. That is, the organization has several domains.

    Otherwise, you can leave the field empty.

    See also

    For more details on the krb5 file, go to Providing a Krb5 File for Kerberos Authentication.

  5. In case you run into any issues, select the Activate Kerberos debug mode option. Otherwise, disable it.

    See also

    For more details on debugging Kerberos issues, go to How to Debug Kerberos in Web Applications.

  6. Click the Save button to confirm the Kerberos configuration. This configuration will take effect immediately. There is no need to restart the Data Catalog.

Important

If no user is able to log in the Data Catalog due to an inappropriate configuration of the Kerberos authentication, remember that you can still connect to the Data Catalog using the local authentication or the VDP-based authentication. Then you can modify the Kerberos settings or enable its debug mode.

Use an External Database for the Data Catalog

By default, the Data Catalog stores its settings and metadata in an embedded database (Apache Derby) shipped with the Denodo Platform. However, you can configure it to store its settings and metadata on an external database. This is necessary if you want to set up a cluster of Data Catalog servers.

The Data Catalog supports the following databases:

  • MySQL 5.6 and 5.7

    Note

    Make sure that MySQL (or the database in MySQL you are going to use) is configured with the options Default Charset = utf8 and Collation = utf8_unicode_ci.

  • Oracle 12c, 18c and 19c

  • PostgreSQL 9.5

  • SQL Server 2014

  • Amazon Aurora for MySQL 5.7 and PostgreSQL 9.5

  • Azure SQL Server

Follow these steps to store the metadata of the Data Catalog on an external database:

  1. In the target database, create a catalog or a schema for the metadata of the Data Catalog.

    Although you can use an existing schema, we suggest creating one to keep the tables separate from the tables of other applications. We suggest reserving 5 gigabytes of space for this schema. In most cases, less space will be required. However, we recommend a high value to avoid issues due to the lack of space in the database.

  2. Copy the JDBC driver of the database to the directory <DENODO_HOME>/lib/data-catalog-extensions.

    Take into account the following considerations:

    • To use Oracle 12, only copy the files ojdbc6.jar and orai18n.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/oracle-12c.

    • To use Oracle 18, only copy the files ojdbc8.jar and orai18n.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/oracle-18c.

    • To use Oracle 19, only copy the files ojdbc8.jar and orai18n.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/oracle-19c.

    • To use Amazon Aurora for MySQL 5.7, only copy the file mariadb-java-client-2.7.1.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/mariadb-2.7.

    • To use Amazon Aurora for PostgreSQL 9.5, only copy the file postgresql-42.2.5 from <DENODO_HOME>/lib/extensions/jdbc-drivers/postgresql-12.

    • To use Azure SQL Server, only copy the file: mssql-jdbc-7.2.2.jre8.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/mssql-jdbc-7.x.

    • To use Azure SQL Server via Active Directory, copy the files mssql-jdbc-7.2.2.jre8.jar, adal4j-1.6.3.jar, gson-2.8.0.jar and oauth2-oidc-sdk-5.64.4.jar from <DENODO_HOME>/lib/extensions/jdbc-drivers/mssql-jdbc-7.x; and accessors-smart.jar, json-smart.jar, javax.mail.jar and nimbus-jose-jwt.jar from <DENODO_HOME>/lib/contrib.

    • You may find the JDBC of other databases in <DENODO_HOME>/lib/extensions/jdbc-drivers.

  3. Log in the Data Catalog with an administrator account and export the metadata.

    This is necessary because the metadata of the Data Catalog is not transferred automatically from the current database to the new one. You can skip this step if this is a fresh installation and you have not done any other change to this Data Catalog.

  4. Stop all the components of the Denodo Platform. Then, execute <DENODO_HOME>/bin/webcontainer_shutdown to make sure the web container is stopped.

  5. Start Data Catalog and the other components of the Denodo Platform.

  6. Log in the Data Catalog with an administrator account and go to Administration > Set-Up and management > Server. In the Database tab you will find the dialog to configure the external database.

    Dialog to configure the database where the Data Catalog stores its metadata

    Dialog to configure the database where the Data Catalog stores its metadata

    Provide the following information:

    • Database. Select the database you want to use.

    • Driver class. The name of the Java class of the JDBC driver to be used. The default value is usually correct.

    • URL. The connection URL to the database.

    • Username and Password. The credentials of the account used to connect to the database. They are optional.

    Note

    When the selected database is Derby Embedded, the fields Driver class, URL, Username and Password are not editable.

    To speed up the queries, the Data Catalog creates a pool with connections to the database. You can configure this pool with the following optional settings:

    • Maximum pool size. The maximum number of actual connections to the database, including both idle and in-use connections.

    • Minimum idle connections. The minimum number of idle connections that the Data Catalog tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than Maximum pool size, the Data Catalog will make a best effort to add additional connections.

    • Connection timeout. The maximum number of milliseconds that the Data Catalog will wait for a connection from the pool. If this time is exceeded without a connection becoming available, an error will be thrown.

    • Ping query. The query that will be executed just before using a connection from the pool to validate that it is still alive. This is required only for legacy drivers that do not support the JDBC 4.0 Connection.isValid() API. If your driver supports JDBC 4.0 we strongly recommend not setting this property.

      Note

      The jTDS driver for Microsoft SQL Server is considered legacy since it only supports JDBC 3.0. To use it with the Data Catalog you need to provide a value for the Ping query field.

  7. Click the Save button.

    The Data Catalog will check that it can reach the database and that the necessary tables exist:

    • If the JDBC driver for the selected database cannot be loaded, you will see the following warning.

      Warning when the JDBC driver cannot be loaded

      Warning when the JDBC driver cannot be loaded

      The configuration can still be saved, but the JDBC driver has to be available before restarting the Data Catalog.

    • If for some reason the database cannot be reached, you will see the following warning.

      Warning when cannot connect to the database

      Warning when cannot connect to the database

      Again, the configuration can be saved, but you have to fix the error before restarting the Data Catalog. Otherwise, it is not recommended to save it.

    • If the database does not have the necessary tables, you will see the following dialog.

      Dialog to create the tables in the database

      Dialog to create the tables in the database

      Confirm if you want the Data Catalog to create the tables for you. It will use the user account you configured for the database, so make sure it has DDL privileges on the database. Alternatively, you can specify another user account with the right privileges.

      Click the Cancel button if you want to run the script manually. You will find it on the folder <DENODO_HOME>/scripts/data-catalog/sql/db_scripts.

      In case you are setting-up a cluster of Data Catalog servers, all servers will share the same database. Therefore, you only need to create the tables in the database once.

  8. Restart Data Catalog to apply the changes.

    If the Data Catalog does not start due to an error in the database configuration you can restore the default database configuration manually. Edit the file <DENODO_HOME>/conf/data-catalog/datasource.properties and modify its content to match the following. Remember to replace <DENODO_HOME> with the path to the Denodo installation. The property datasource.url.default will contain the path to the database so you can copy its content into the spring.datasource.url property.

    datasource.type=derby
    datasource.url.default=jdbc:derby:<DENODO_HOME>/metadata/data-catalog-database;create=true
    spring.datasource.driver-class-name=org.apache.derby.jdbc.EmbeddedDriver
    spring.datasource.url=jdbc:derby:<DENODO_HOME>/metadata/data-catalog-database;create=true
    spring.datasource.username=
    spring.datasource.password=
    datasource.password.encrypted=false
    

    Restart Data Catalog again to apply the changes.

  9. Import the metadata you exported on step #3.

Configure the Permissions

In the Permissions tab you can configure the privileges granted for a role. The privileges are divided into three categories: Management, Administration and Collaboration. Select a category and check all the privileges you want to assign for a role. All the users with that role will be granted with those privileges in the Data Catalog.

Dialog to configure the user privileges

Dialog to configure the user privileges

If a role has been removed from Virtual DataPort it is displayed in red in the permissions table. Click on the remove icon next to its name to remove it from the Data Catalog too.

Note

To assign privileges in the Permissions dialog, a user needs:

  1. The PERMISSIONS privilege in Data Catalog, which gives access to the Permissions dialog.

  2. The ADMIN privilege for a database in Virtual DataPort, which is the minimum requirement to access all the roles available in the Virtual DataPort server.