USER MANUALS

Table Creation Templates Management

When you configure the cache for a specific view Denodo creates a table in the cache data source and loads it using the data from that view. In order to create this table, Denodo uses a built-in SQL CREATE TABLE statement that should be sufficient for most scenarios. However, there might be special cases where this default statement is not enough, for example, if you require an optional hint that is specific of that database. For these uncommon situations Denodo allows to customize the commands used to create the cache tables. There are two different ways to do it:

  • On the cache data source: Denodo will use the custom table creation command to create all the cache tables.

  • On the view: Denodo will use the custom table creation command only to create the cache table of this view.

Denodo supports SQL command templates to customize the table creation command used for cache, data movement, remote tables, and summary tables. A template is a CREATE TABLE SQL statement with some parameters that Denodo will replace with their values before executing the sentence.

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.

To customize the table creation templates you have to edit the target data source. First, open the data source and click the tab Read & Write tab. If the data source is configured to use the built-in table creation template, you will see the + Manage CREATE TABLE templates button. Only data sources supported as cache will display this button.

Manage CREATE TABLE templates button

Click that button to open the form for managing table creation templates.

Manage CREATE TABLE templates form

By default, the form only contains the built-in table creation template (called Default), and all five different table creation types are configured to use the built-in template.

Click the New button to create a custom table creation template. The following window will open.

Create new remote table template dialog

It contains the built-in table creation template that you can use as a reference. You can edit the template as you like. 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 a custom table creation template, click Test to check if 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 of the data source.

Manage CREATE TABLE templates form with custom remote table template

Then, use the drop-down to select the desired template for each type of table creation. Click Save button 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. To do it, first you have to create the data source, click Save button and then, + Manage CREATE TABLE templates will be enabled.

Manage table creation button disabled

Warning

If you edit a data source and change the Database adapter, all previously configured table creation templates will be removed and the data source will use the built-in template.

Configure the Cache Table Creation Templates

Denodo supports customizing the SQL command used to create the tables in the cache. You can configure a custom create table template for the global server cache or you can do it just for a specific Denodo database.

Note

Denodo will use the new cache table creation command only for creating the new cache tables. Denodo does not recreate the existing cache tables. See the section Cache Table Recreation to find more information about how to recreate the existing cache tables.

Configuring the Cache Table Creation Templates in the Server Cache

To customize the table creation command used in the server cache, click the menu Administration > Server Configuration > Cache. If the cache was disabled, you have to enabled it first.

If you set an existing data source as cache, then to configure the server 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.

Note

If you enabled the server cache for the first the time or if you changes the cache database adapter (Connection tab), you have to click on OK to save the new cache data source configuration before editing the table creation command.

Server cache manage table creation button disabled

Warning message displayed before saving the new cache data source configuration

If the server cache is already configured, to edit the cache table creation template you have to click on Read & Write tab > Manage CREATE TABLE templates.

Create new cache table template dialog

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 may have noticed it is possible to 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.

Manage CREATE TABLE templates form with custom cache template

Configuring the Cache Table Creation Templates for a Specific Denodo database

To customize the table creation command used for a specific Denodo database you have to open the Design Studio or the Admin Tool and click on Administration > Database management > Select a database > Cache button. Uncheck the checkbox Default configuration in case it is enabled.

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.

Note

If you enabled the database cache for first the time or if you changes the cache database adapter (Connection tab), you have to click on OK to save the new cache data source configuration before editing the table creation command.

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.

Manage CREATE TABLE templates form with custom cache management table template

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. See Customizing the Cache Templates on a View section for more info.

Template Parameters

The table creation templates are parametrized 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, then 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.

Note

Column parameters are only available in templates defined in views, remote tables, and summaries. Hence, it is not possible to utilize a column parameter in a template defined in a data source. See sections Customizing the Cache Templates on a View, CREATE_REMOTE_TABLE stored procedure and CREATE REMOTE TABLE Command 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.

Add feedback