Cache Table Creation Templates¶

Denodo uses built-in SQL statements to create the cache tables. Although this should be sufficient for most scenarios, 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 cache tables creation. It is also possible to use templates to customize the table creation of the data movement tables, 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. There are three types of parameters, all following the syntax @{parameter_name}:

• 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>}).

• 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. See sections Configuring Virtual DataPort Properties and Export to a File with Properties for more information.

Template definition syntax:

<template_name:literal> = '<template_definition:literal>' [ DEFAULT( <parameter definition> [, <parameter definition> ]* ) ]

<parameter definition> ::= '<parameter_name:literal>' = '<parameter_value:literal>'

The goal of the DEFAULT token is to set the value of the user-defined parameters in the template. 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. More info Export to a File with Properties.

Example:

my_template = 'CREATE TABLE @{internal_parameter_table_name} (@{internal_parameter_columns} @{internal_parameter_restrictions}) IN ACCELERATOR @{accelerator}'
              DEFAULT('accelerator' = 'accel1')

Use the command ALTER DATASOURCE JDBC to customize the table creation templates used for cache, data movements, remote tables and summaries.

ALTER DATASOURCE JDBC <data_source_name>
CREATE_TABLE_TEMPLATES (
    <template_list>
)
DEFAULT CREATE_TABLE_TEMPLATES (
    DATA_MOVEMENT = <data_movement_template_name>,
    CACHE = <cache_template_name>,
    CACHE_MANAGEMENT = <cache_management_template_name>,
    REMOTE_TABLE = <remote_table_template_name>,
    SUMMARY = <summary_template_name>
)

The DATA_SOURCE_DEFAULT token is a special template name used to identify the Denodo built-in CREATE TABLE command.

Example:

ALTER DATASOURCE JDBC my_data_source
CREATE_TABLE_TEMPLATES (
    my_template1 = '<template 1 definition>',
    my_template2 = '<template 2 definition>'
)
DEFAULT CREATE_TABLE_TEMPLATES (
    DATA_MOVEMENT = DATA_SOURCE_DEFAULT,
    CACHE = my_template1,
    CACHE_MANAGEMENT = DATA_SOURCE_DEFAULT,
    REMOTE_TABLE = my_template2,
    SUMMARY = my_template2
)

ALTER DATASOURCE JDBC my_cache_data_source
CREATE_TABLE_TEMPLATES (
    my_cache_template = '<my template definition>'
)
DEFAULT CREATE_TABLE_TEMPLATES (CACHE = my_cache_template);

ALTER [TABLE | VIEW] <view_name>
CREATE_TABLE_TEMPLATES (
    cache = '<my template definition>'
)

ALTER DATASOURCE JDBC my_cache_data_source
CREATE_TABLE_TEMPLATES (
    my_cache_management_template = '<my template definition>'
)
DEFAULT CREATE_TABLE_TEMPLATES (CACHE_MANAGEMENT = my_cache_management_template);