Creating or Editing the Table Creation Template On the Data Sources¶

This section explains how to manage the table creation templates. As mention in the previous section Denodo supports SQL command templates to customize the table creation command used for cache, data movement, remote tables, and summary tables. For the sake of simplicity the explanation will focus on the remote table scenario but the process is equivalent for the other use cases.

This feature is only available for data sources supported as cache.

To customize the table creation templates, edit the data source and click the tab Read & Write. Then, expand the section Manage custom DDL templates.

Initially, this section only has the built-in table creation template (called Default), and all five different table creation types are configured to use the built-in template. Click New to create a custom table creation template.

This popup contains the built-in table creation template that you can use as a reference. You can edit the template as you like. The template can contain additional statements apart from CREATE, for example, to grant privileges to specific users. The only restriction is you cannot remove the parameters whose name starts with @{internal_parameter_...}. There are three types of parameters, all following the syntax @{parameter_name}. See section Template Parameters for more information.

Before saving your custom table creation template, click Test to check it is valid. See Test Table Creation Templates section for more info. Then, click Ok to add the template to the list of table creation templates available in the data source.

Then, use the drop-down to select the desired template for each type of table creation. Click Save to finish the configuration of the table creation template.

Privileges Required

The user needs the following privileges to configure the table creation templates of a data source:

• Connect over the database of the JDBC cache data source.

• Execute and Write over the JDBC cache data source.

Configure Table Creation Templates in a New Data Source

Configuring table creation templates is not possible during data source creation. You have to save the data source first. Then, the section Manage custom DDL templates will be enabled.

Configure the Cache Table Creation Templates¶

Denodo supports customizing the SQL command used to create the tables in the cache. The purpose of the custom templates is to be able to create tables using a different syntax than the one we provide by default. This allows including optional hints that are specific of a database. You can configure a custom create table template for the global server cache or you can do it just for a specific Denodo database.

Configuring the Cache Table Creation Templates in the Server Cache

To customize the table creation command used in the server cache, open Design Studio and click the menu Administration > Server Configuration. And then, click the pill CACHE. If the cache was disabled, enable it first, click Save and then, do the changes.

If you set an existing data source as cache, to configure the server cache template open this data source and follow the steps explained in the Creating or Editing the Table Creation Template On the Data Sources section.

If the server cache is already configured, to edit the cache table creation template, click Read & Write tab and expand Manage custom DDL templates.

Follow these steps to configure a cache template:

1. Click New.

2. Follow the steps described in the previous section (Creating or Editing the Table Creation Template On the Data Sources) to add a cache table creation template.

3. In Cache tables, select the new cache template.

4. Click Save.

You can select a different template for two cache-related tables: Cache tables and Cache management tables. The first ones refer to those tables storing the data from the cached views while the second ones are tables containing metadata information about the cache system itself. It is not recommended to use a custom template for the cache management tables. In case this would be necessary visit section Customize the Cache Management Tables Template for further information.

Configuring the Cache Table Creation Templates for a Specific Denodo database

To customize the table creation command used for a specific Denodo database, log in to Design Studio and click the menu Administration > Database management. Select a database and click Cache and clear the check box Default configuration.

If you set an existing data source as cache, to configure the database cache template you have to open this data source and follow the steps explained in the Creating or Editing the Table Creation Template On the Data Sources section.

To edit the cache table creation template click on Read & Write tab > Manage CREATE TABLE templates and follow the same steps described in the previous section.

Customize the Cache Management Tables Template¶

It is also possible, but not recommended, to customize the command to create the Denodo cache management tables. You can do it editing the cache data source configuration. The steps are the same as in the previous sections except for the drop-down in which you select the template. In this case, use the drop-down Cache management tables.

Note that if the cache management tables already exist:

• Denodo will not replace the current cache management tables when you configure a new table creation template for cache management.

• The only way to make the new template effective is to create new cache management tables in another catalog or schema never used before as Denodo cache. Therefore, all currently cached data will be lost.

• Manually deleting the cache management tables is not recommended.

Creating or Editing the Table Creation Template On Views¶

It is possible to set a custom cache table creation template for a specific view only, for view with materialization full. To customize the table creation templates do this:

1. Open the view and click OPTIONS.

2. In the tab Materialization (pka Cache), expand the section Manage custom DDL template (only available for views configured with Full materialization).

To edit the custom table creation template or to add a new one, select Custom template and click Edit template. This opens a popup to define the template for this view. You can edit this statement as you like but you cannot remove the parameters whose name starts with @{internal_parameter_...}. This means that you must not use custom templates to change the name of the table in the cache database. There are three types of parameters, all following the syntax @{parameter_name}. See section Template Parameters for more information.

It is possible to edit the template referencing existing columns of the view as the following image shows.

Before saving your custom table creation template, we recommend you click Test to validate this statement is valid (see Test Table Creation Templates for more information). Then, click Ok and then, Save to change these settings.

Privileges Required

The user needs the following privileges to configure the table creation templates of a view:

• Connect over the database of the JDBC cache data source.

• Execute and Write over the JDBC cache data source.

• Write over the view.

See Customizing the Cache Templates on a View section for more info.

Template Parameters¶

The table creation templates are parameterized SQL CREATE TABLE commands. Denodo replaces these parameters with the corresponding values when it creates the table. There are three types of parameters that you can use in the table creation templates:

• Internal parameters: Parameters to set the table name, columns, restrictions, data format, data location, etc. in the create table command. Denodo will automatically set the value of these parameters. If the built-in table creation template contains any parameters whose name starts with @{internal_parameter_...}, you also must use them in your custom template. See Internal Parameters List for more info about the internal parameters.

• Column parameters: Parameters that references a column of the view. For the remote tables and summary templates these parameters reference columns of the <select query> result (also known as data load query). You can use the column parameters to set colum restrictions, for example: PRIMARY KEY(@{<column_name>}). The column parameters are only available for the views, remote tables and summaries. Therefore, note that it is not possible to utilize a column parameter in a template that is defined in a data source.

• Environment properties: User-defined parameters to use different property values for different environments. These properties will be consider as environment-specific for exports and promotions. If you export the data source or the view using the option export with properties, Denodo will export the value of these properties in the environment properties file. See sections Configuring Virtual DataPort Properties and Export to a File with Properties for more information.

Internal Parameters List¶

The following list contains the internal parameters used by Denodo in the table creation templates. Some of these parameters are common for all databases (eg. @{internal_parameter_columns}) while some are database specific:

• @{internal_parameter_catalog}: Catalog where Denodo will create the table.

• @{internal_parameter_schema}: Schema where Denodo will create the table.

• @{internal_parameter_table}: Name used to create the table.

• @{internal_parameter_table_name}: Concatenation of <catalog>.<schema>.<table>.

• @{internal_parameter_columns}: List of column names and their data type.

• @{internal_parameter_restrictions}: Table restrictions, for example, Primary Key, unique constraint, etc.

• @{internal_parameter_table_format}: Format used to store the data of the table: PARQUET, DELTA, delimited files. This parameter applies to some databases using bulk load.

• @{internal_parameter_data_location}: Location in the HDFS or DBFS used to store the table data.

• @{internal_parameter_s3_location}: Location in AWS S3 used to store the table data.

• @{internal_parameter_distribution_method}: Distribution used to store the rows: HASH or ROUND_ROBIN.

• @{internal_parameter_distribution_columns}: Distribution columns used to distribute data evenly across available data slices.

• @{internal_parameter_utf_charset}: Charset used to store the data.

• @{internal_parameter_utf8_collate_table}: Collation rules that define how to compare and sort character strings.

• @{internal_parameter_primary_index}: Columns used as the Primary Index of the table.

• @{internal_parameter_index_name}: Random index name based on the view name and prefixed by idx.