How to replicate VQL elements from one instance to another

Goal

This document describes how to migrate the elements from an on-premise Virtual DataPort instance to a cloud Virtual DataPort instance (say Amazon Web Service EC2) using Export and Import options.

Content

Every element in Denodo Virtual DataPort can be recreated precisely by executing the equivalent Virtual Query Language statements. This allows the elements to be transferable and can be recreated at any time provided we have the VQL of that element. Virtual DataPort provides inbuilt functionalities to export, that is, to generate the VQL that would be required to re-create the element. It also provides a similar feature to import, that is, to create an element out of exported VQL statements.

Together, the Export and Import form a powerful, lite, and easy to use strategy to replicate, promote, and backup the elements in more than one instance.

Export the Metadata of the Source VDP Instance

The metadata of the Source Virtual DataPort server can be exported using the GUI export option.

1. Start the Virtual DataPort server of the on-premise instance and launch either the Virtual DataPort Administration Tool or the Denodo Web Design Studio.
2. Now, export the metadata of Virtual DataPort from the old installation by navigating to File > Export.
3. In the Export dialog window, perform the following steps:

1. Output File: The location where to store the exported files. In the Web Design Studio after the export is created, you will be prompted to choose the file location

2. Use a custom password for sensitive data encryption: Provide a password which will be required when importing the VQL into another Virtual DataPort Server (only possible in Denodo 8)

3. Export environment-specific properties separately: Using this option two files are created. A VQL file with parameters and one property file with variables holding the values for the parameters in the VQL file.
4. Choose the option Drop elements before creating them under the Replace / Drop elements section to drop and re-create the elements while importing the VQL statements.
5. Choose the following optional check-boxes to export along with the VQL statements:

1. Include Jars - to include custom jars imported, if any.
2. Include ITPilot scanners - to include the scanners used by WWW (Web) sources, if any.
3. Include ITPilot custom components - to include the ITPilot custom components used by the WWW data sources, if any.
4. Enter Single User Mode during Import - switches into single user mode while importing this file, if required.
5. Include view statistics - to include the statistics gathered for the views that are used by the cost-based optimization, if any.

Prerequisites before performing the Import operation

1. Copy the external classes and libraries that you added to the directory if any.

•  For Denodo 6.0 <ONPREMISE_DENODO_HOME>/extensions/thirdparty/lib to <CLOUD_DENODO_HOME>/extensions/thirdparty/lib.
• For Denodo 7.0 <ONPREMISE_DENODO_HOME>/lib-external/jdbc-drivers  to <CLOUD_DENODO_HOME>/lib-external/jdbc-drivers.
  Note: Denodo 7.0 creates a folder in <CLOUD_DENODO_HOME>/lib-external/jdbc-drivers for each supported JDBC adapter whose driver is not included in the new Denodo instance. You should copy the drivers to the appropriate subdirectory.
• For Denodo 8.0: To import a JDBC driver not included in Denodo use the Extensions Management option.

2. If the cache engine on the previous installation uses the “Generic” cache adapter, copy the files cacheConfig-generic.xml and cacheConfig-generic-unicode.xml of the directory <ONPREMISE_DENODO_HOME>/conf/vdp, to <CLOUD_DENODO_HOME>/conf/vdp.
3. In the previous installation, if you have enabled SSL, copy the keystore file to the new installation. To check if SSL was enabled, open the following file from the old installation directory and check if the property com.denodo.security.ssl.enabled is uncommented and is true. If it is, copy the file of the property com.denodo.security.ssl.keyStore to the new installation:

<ONPREMISE_DENODO_HOME>/conf/vdp/VDBConfiguration.properties

Import the Metadata into the Target VDP Instance

1. Start the Virtual DataPort server of the AWS cloud Denodo Platform instance and launch either the Virtual DataPort Administration tool or the Denodo Web Design Studio.
2. To import the VQL elements into the cloud Virtual DataPort instance using the Import dialog. You can do this by navigating to File > Import option of the Virtual DataPort Administration tool.
3. In the Import dialog window, Choose the below parameters

1. VQL file: Enter the path to the VQL file or browse through the file system. The file that contains the VQL statements to recreate the metadata.
2. Use properties file: If you have exported the metadata with the “Export environment-specific properties separately” option enabled, select the Use properties file checkbox and enter the path to the file with the .properties extension in the Properties File box if required.
3. Save output: to save the output of the import process into a file. If selected, enter the path to the output file in the Output file box, if required (Currently only available in the Virtual DataPort Administration Tool).
4. Report only commands that were not executed successfully: If selected, the Tool will only list the error occurred while executing the VQL statements of the “VQL file”. If cleared, this list will contain a list of all the executed statements, if required.
5. Use a custom password for sensitive data encryption: This option must be enabled in case the VQL which is going to be imported was exported with a custom password. If no or an incorrect password is provided then the import will fail (Only available in Denodo 8).
6. Import in current server: If selected, the VQL file will be executed on the current Server, on the database selected on the list Default database (Currently only available in the Virtual DataPort Administration Tool).
7. Import in multiple servers: If selected, the VQL file will be imported to all the selected servers on the list below, if required (Currently only available in the Virtual DataPort Administration Tool).

Import Screen Web Design Studio

Import Screen Administration Tool

Alternatively, you can perform the same export/import operation using the scripts provided by the Denodo Platform. You can find those scripts available under the <DENODO_HOME>/bin. The section Using the Import/Export Scripts for Backup And/or Replication of the Virtual DataPort Administration Guide provides more details about how to use the Export/Import scripts.

Note: Similarly, you can follow the same steps to migrate from cloud to cloud, cloud to on-premise, on-premise to on-premise within the same version of the Virtual DataPort instances.

References
Virtual DataPort Administration Guide - Exporting and Importing the Server Metadata