Test Table Creation Templates¶

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.

The TEST CREATE_TABLE_TEMPLATE command checks if a table creation template is valid in the data source indicated as parameter. This command does the following steps:

Reads the template and replaces its parameters by their values.
Executes the CREATE TABLE SQL command generated in the first step.
Deletes the temporary table created in the previous step.

Syntax for TEST CREATE_TABLE_TEMPLATE statement:

TEST CREATE_TABLE_TEMPLATE
    TEMPLATE_INFO
        [ TEMPLATE_NAME = <template_name:literal> ]
        TEMPLATE_STATEMENT = <template_definition:literal> [ DEFAULT( <parameter_value> [, <parameter_value> ]* ) ]
    DATA_SOURCE_INFO
        DATABASE_NAME = <data_source_database:literal>
        DATA_SOURCE_NAME = <data_source_name:literal>
        [ CATALOG_NAME = <data_source_catalog:literal> ]
        [ SCHEMA_NAME = <data_source_schema:literal> ]
    [ VIEW_INFO
        DATABASE_NAME = <view_database:literal>
        VIEW_NAME = <view_name:literal> ]
    [ DATA_LOAD_QUERY = <select_query:literal> ]

<parameter_value> ::= <parameter_name:literal> = <parameter_value:literal>

<literal> ::= '.*' (a single quote in a literal has to be escaped with another single quote. E.g. 'literal''with a quote')

Note: You can use parameters within the template definition. Syntax: @{parameter_name}

Privileges Required

To execute this command, the user needs the following privileges:

Connect over the database of the JDBC data source.
Execute and Write over the JDBC data source.
Metadata over the view (in case the command uses a view).

Example of usage

TEST CREATE_TABLE_TEMPLATE
    TEMPLATE_INFO
        TEMPLATE_NAME = 'my_template'
        TEMPLATE_STATEMENT = 'CREATE TABLE @{internal_parameter_table_name} (@{internal_parameter_columns} @{internal_parameter_restrictions}) STORAGE (INITIAL @{first_extent_size})' DEFAULT('first_extent_size' = '50K')
    DATA_SOURCE_INFO
        DATABASE_NAME = 'admin'
        DATA_SOURCE_NAME = 'my_data_source'
        SCHEMA_NAME = 'my_schema'

Command result:

Product Name : Oracle
Product Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

Table creation template: my_template
Create table command: CREATE TABLE "my_schema".TEMPLATE_TEST_76443224450387 (
    column_1 DECIMAL(20), column_2 VARCHAR2(4000), column_3 VARCHAR2(4000)
)
STORAGE (INITIAL 50K)

USER MANUALS

Test Table Creation Templates¶