Vertica Templates

This article describes the principal changes of Vertica Templates.

The download section can be found at this page.

 

This article is dedicated to Stambia DI S17, S18 or S19.

If you are using Stambia DI 2020 (S20) please refer to this article.

 

Templates.Vertica.2020-05-26

Load File to Vertica (with copy)

New parameter xfieldSep

A new parameter 'xfieldSep' has been added.

It specifies the hexadecimal code of the Field Separator to be used in the temporary file imported into Vertica.

Examples: 08 = backspace, 09 = tab, 1F = ascii unit separator

 

Note that this option is taken into account only when 'Force Pre Transform File' is set to 'true'.

 

INTEGRATION Vertica

Fix creation of temporary integration table when "usingExportObject" is used

Some issues were encountered when using "usingExportObject" mode for "Create Integration Table Like Target Table" parameter with some Vertica database versions.

Queries have been fixed for this mode to work as expected with the different Vertica database versions.

 

Fix expressions used for generating a row id

Expressions used in the various queries to create a row id have been fixed to use the correct syntax.

They were using an order by expression which was not supported by Vertica and not useful.

 

Fix expression used for projection name

Expression used when creating projections is now using properly the temporary work name.

 

Templates.Vertica.2019-08-09

REJECT Vertica

Reject rules which are defined with "warning" severity level in Metadata should not be removed from the flow and should be loaded in target table.

Previous version of the Template was unexpectedly removing rejected rows with "warning" severity level from the flow, which was wrong, and which has been fixed from this version.

Rows which does not fulfill a rule which has been specified with "warning" severity level will not be kept in the flow and loaded into target table.

 

Templates.Vertica.2019-05-20

LOAD Rdbms to Vertica (with COPY)

New parameter allowing to specify escape character

A new parameter named 'Escape Extended Char' has been added to allow customizing the character used as escape character when loading data.

This new parameter defines what will be specified for the 'ESCAPE' option of the Vertica's COPY statement generated for loading data.

When nothing is specified in this parameter (default behavior), 'NO ESPACE' option is now generated in the copy statement.

 

Default statement used for dropping tables updated

Drop table expressions automatically generated when working with Vertica has been changed from "drop table" to "drop table if exists".

This has been performed to avoid unnecessary logged errors when trying to drop temporary work tables for instance.

 

Templates.Vertica.2019-02-08

INTEGRATION Vertica

Recycling of previous rejects fixed

When using the option to recycle the rejects of previous execution an extra step is executed to add those previous rejects in the integration flow.

This step had several issues which avoided it to work properly and which have been fixed in this version.

Moreover, possible duplicates while retrieving those rejects are now filtered using DISTINCT keyword.

 

Templates.Vertica.2019-01-18

INTEGRATION Vertica

Use Distinct issue fixed

'Use Distinct' parameter had no effect to queries inserting data into temporary integration table.

Queries have been fixed for the parameter to work as expected.

 

Optimization

A step performing an 'ANALYZE_STATISTICS' statement on the target table was previously executed at the beginning of execution in some situations.

After investigation this step was not useful and beneficial at this place, so it has been removed to avoid unnecessary operations on the Vertica database.

 

Datatype added

UUID datatype has been added to the list of available datatypes.

 

Templates.Vertica.2018-12-05

INTEGRATION Vertica

Support generating the create table statement of work table using export_objects Vertica statement

When using this mode the function is executed on the target table and the corresponding query returned is parsed and analyzed to generate a create table statement optimized for the work table.

This mode is now used by default as it offers better performances and performs less queries on the target Vertica server.

It can be changed through 'Create Integration Table Like Target Table' parameter.

 

Target table statistics computation is now performed only when there is change on data

When 'Analyze Target Table' parameter is enabled the Template recomputes the statistics of target table using the Vertica's "ANALYZE_STATISTICS".

As this statement can be time and resource consuming on the Vertica server the Template has been modified to execute it only when data changed during the integration (if there have been inserts, deletes, updates, or merges.)

 

TOOL Vertica Drop Partition

The tool used for dropping partitions has been updated to use the 'drop_partitions' function instead of drop_partition which is deprecated on last Vertica versions.

Parameters have been updated accordingly to match the parameters of this new function.

Note also that Integration Template which has parameters for dropping partitions has also been slightly modified to take into account those changes, as it uses the tool behind the scenes when using those parameters.

 

Templates.Vertica.2018-10-11

LOAD File to Vertica (with COPY)

This template now supports to optionally send the file to load through SSH.

Simply drag and drop an SSH folder Metadata Link in the target Vertica Metadata and rename it to "TARGET_SSH".

The Templates will then automatically send the file through SSH before loading it with the Vertica COPY statement.

Refer to the Template's 'Enable File Transport' parameter documentation for further information.

 

Templates.Vertica.2018-09-27

INTEGRATION Vertica, LOAD Hive to Vertica and REJECT Vertica

• Steps dropping tables are now all using the "drop if exists" syntax to avoid logging unnecessary failed requests in the database when the dropped tables do not exit.

 

Templates.Vertica.2018-09-26

LOAD Rdbms to Vertica (with COPY)

• New parameter "String Delimiter" to customize the character used to enclose string in the temporary file generated and loaded into Vertica
• New parameter "Row Separator" to customize the character used as row separator in the temporary file generated and loaded into Vertica
• New parameter "Parser" to specify the Vertica Flex PARSER to use when loading the temporary file into Vertica

 

Templates.Vertica.2018-09-17

INTEGRATION Vertica

• The step dropping the previously created temporary integration table is now using the 'if exists' clause to avoid logging unnecessary failed requests in the database when it does not exit.