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. If the data source is configured to use the built-in table creation template, then you will see the + Manage CREATE TABLE templates
button. Only data sources supported as cache will display this button.
Click that button to open the form for managing table creation templates.
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.
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 your custom table creation template you should click on the Test
button to check if it is valid. See Test Table Creation Templates section for more info. Then, click on OK
and the template will be added 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 on 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
andWrite
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 on Save
button, and then the + Manage CREATE TABLE templates
button will be enabled.
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. 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.
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.
Note
If the syntax requires referencing specific columns, then you must use the templates at view level.
Configuring the Cache Table Creation Templates in the Server Cache
To customize the table creation command used in the server cache you have to open the Design Studio or the Admin Tool and click on Administration > Server Configuration > Cache. In case 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.
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.
Follow these steps to configure a cache template:
Click New.
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.
In Cache tables, select the new cache template.
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.
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.
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.
To customize the table creation templates you must edit the target view. First, open the view and click on Options
tab. If the view is configured to user FULL cache this tab includes the option Manage custom DDL templates
. It will be collapsed by default if the view does not have a custom template already configured. In order to configure a different one, click on the +
button.
By default, the form contains the default data source table creation template (Default). If the view has set a custom template the form will show this one.
Note
This default template is only visible if the user has Metadata privilege on the data source.
To edit the custom table creation template or to add a new one, check Custom template
option and the Edit template
button will be enabled.
After clicking this button, the edition dialog will open.
The dialog will show the data source default template as a starting point for the new one. You can edit the template as you like. The only restriction is you cannot remove the parameters whose name starts with @{internal_parameter_...}
. This means that it is NOT possible to 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.
Note
For the particular case of using custom templates to specify partitioning, take into account this will not work for those databases that requires hive-style partitioning, as that would not only require including the clause, but also to modify our Parquet upload process to create the different partitions.
Before saving your custom table creation template you should click on the Test
button to check if it is valid. See Test Table Creation Templates section for more info. Then, click on OK
and the template will be copied to the selected template.
Click on 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 view:
Connect
over the database of the JDBC cache data source.Execute
andWrite
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
, 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 stored 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.