CONFIG_LOAD_TRANSFORMATIONS
CONFIG_LOAD_TRANSFORMATIONS is a configuration package that is used to configure custom load transformations, such as defining custom transformation logic for source entity attributes.
See also:
Latest default configurations available in ADE default configuration (GitHub)
Usage
Open the configuration package from Designer package list.
Click Export package in the Actions tab.
Click Download.
Edit JSON file.
Import edited JSON back to Designer with Import Package.
After a successful import, changes to the configuration package have been applied to Designer only. It is recommended to deploy the same changes to Runtime environments as well.
Contents
ATTR transformation type configuration
Attribute transformation type configurations are managed inside the JSON array block named attrTransformationTypes.
Key | Value type | Example | Description |
|---|---|---|---|
transformationType | String | HASH | The name/type of the transformation. |
isShownInDesigner | Boolean | true | Toggles the visibility of the transformation type in Designer. |
ordinalPositionInDesigner | Integer | 1 | Toggles the order in which the transformation types are shown in Designer. |
You can add a new transformation type or update the contents of an existing transformation type.
Be careful when editing existing transformation types!
Note that existing transformation types are utilised in Agile Data Engine load and view generation logic. Therefore, it is recommended to create new transformation types for specific use cases instead of editing existing ones to avoid unwanted misbehavior.
Default transformation types
Transformation type | Description |
|---|---|
CUSTOM | User defined transformation for the attribute in DBMS specific SQL Dialect. Can contain environment variables. |
HASH | Data Vault HASH transformation. |
CONCAT | Concatenation of the mapped attributes. |
CURRENT_TS | Current Timestamp |
DV_STATUS | Status satellite activity indicator (1 or 0) |
DV_BUSINESS_KEY | Data Vault Business Key transformation |
ST_ASTEXT | GEOGRAPHY/GEOMETRY to WKT format |
ST_ASBINARY | GEOGRAPHY/GEOMETRY to WKB format |
ST_GEOGFROMTEXT | WKT formatted spatial information to GEOGRAPHY |
ST_GEOGFROMWKB | WKB formatted spatial information to GEOGRAPHY |
ST_GEOGFROMTEXT_FLAT | WKT format spatial information to GEOMETRY |
ST_GEOGFROMWKB_FLAT | WKB formatted spatial information to GEOMETRY |
RUN_ID | Unique RUN_ID for each transaction |
DV_RUNNING_ID | Data Vault DV_RUNNING_ID transformation. |
HASH_LIST | Data Vault HASH list transformation. |
Example: Transformation Type Configuration
"attrTransformationTypes": [
. . .
{
"transformationType": "HASH",
"isShownInDesigner": true,
"ordinalPositionInDesigner": 2
}
. . .
]
Hide unneeded transformations
If a transformation formula is not needed it can be hidden from the Designer by setting the isShownInDesigner as false.
Transformation types can also be removed completely by removing them from the configuration package, but this is not recommended.
DBMS transformation formula configuration
DBMS transformation formula configurations are managed inside the JSON array block named dbmsTransformationFormulas. The transformation formulas are DBMS product specific.
Key | Value type | Example | Description |
|---|---|---|---|
transformationType | String | CUSTOM | The transformation type. For more information, refer to the Transformation Type Configuration section above. |
dbmsProduct | String | REDSHIFT | The DBMS product. |
transformationFormula | String | Optional. The transformation formula itself needs to be in Freemarker syntax and it has currently access to sourceAttributes list information (so access to datatype and name information). The end result of the formula will be compressed into a single line format and due to this, please do not use single line sql comments in your solution. |
Variables available for the transformation formula:
Variable | Description |
|---|---|
sourceAttributes | Contains attributeName and datatype for the source attribute linked to this formula |
sourceEntity | Access to source entity owner and entityName details) |
loadOptDefined | Function to test whether certain load option is set for the load |
loadOpt | Provides the requested load option |
batchLoading | Indicates whether batch loading has been enabled in this load |
keyAttributeList | Contains the sourceAttributes of the HASH KEY field in the load (used in the multi-active satellite) |
prefix | Prefix required for the source attributes given by the calling load [ |
You can add a new transformation formula or update the contents of an existing transformation formula.
Example: DBMS Transformation Formula Configuration
"dbmsTransformationFormulas": [
. . .
{
"transformationType": "MODIFIED_HASH_FOR_RS",
"dbmsProduct": "REDSHIFT",
"transformationFormula": "MD5(UPPER(<#list sourceAttributes as attribute>\n<#if attribute.datatype?? && attribute.datatype?lower_case?contains(\"char\")>\nNVL(${attribute.attributeName}, '-1')\n<#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"bool\")>\nNVL(CAST(CAST(${attribute.attributeName} AS INT) AS VARCHAR), '-1')\n<#else>\nNVL(CAST(${attribute.attributeName} AS VARCHAR), '-1')\n</#if>\n<#if attribute_has_next> || '#' || </#if></#list>))"
}
. . .
]If an existing customized transformation formula needs to be deleted from the system, simply remove it from the set of values before importing the configuration package. Once this is done, Agile Data Engine will revert back to the default behavior of the transformation type.
If the transformation formula requires repetitive steps, the definition can provide macros in the beginning of the formula definition (example: Customized hash list function for Azure Sql Database).
Examples
Customized transformation formula for Data Vault Hub entities
In this example, we will define a custom transformation formula for Hub hash keys following the these guidelines:
MD5 hash in 16 byte BINARY
UPPER function for all source attributes
Leading and trailing whitespace trimmed using TRIM function
If composite key, add ~ between key attributes
If null value, use '00' value in BINARY
The transformation formula would be the following:
"attrTransformationTypes": [
{
"transformationType": "CUSTOM_HUB",
"shortDescription": "Transformation for Hub",
"description": "Transformation formula to be used for Hub-entities. Target field should be BINARY(16).",
"isShownInDesigner": true,
"ordinalPositionInDesigner": 14
}
],
"dbmsTransformationFormulas": [
{
"transformationType": "CUSTOM_HUB",
"dbmsProduct": "SNOWFLAKE",
"transformationFormula": "NVL(MD5_BINARY(UPPER(<#list sourceAttributes as attribute>TRIM(${attribute.attributeName}::STRING)<#if attribute_has_next> || '~' ||</#if></#list>)), '00'::BINARY)"
}
]Customized hash list function for Azure SQL Database
In this example, we will define a custom transformation formula for the multi-active satellite hash list key in Azure SQL Database following this guideline:
Using SHA2_256 instead of MD5
The transformation formula would be the following:
"attrTransformationTypes": [
{
"transformationType": "HASH_LIST_SHA2",
"shortDescription": "Hash list with SHA2",
"description": "Transformation formula with SHA2 hashing",
"isShownInDesigner": true,
"ordinalPositionInDesigner": 17
}
],
"dbmsTransformationFormulas": [
{
"transformationType": "HASH_LIST_SHA2",
"dbmsProduct": "MS_SQL",
"transformationFormula": "<#macro castClouse attribute prefix=\"\"> <#if attribute.datatype?? && ( attribute.datatype?lower_case?contains(\"nvarchar\") || attribute.datatype?lower_case?contains(\"nchar\") )> TRIM(COALESCE(CAST(${prefix}[${attribute.attributeName}] AS VARCHAR(MAX)), '-1')) <#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"char\")> TRIM(COALESCE(${prefix}[${attribute.attributeName}], '-1')) <#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"geo\")> COALESCE(CAST(${prefix}[${attribute.attributeName}].STAsText() AS VARCHAR(MAX)), '-1') <#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"bool\")> COALESCE(CAST(CAST(${prefix}[${attribute.attributeName}] AS INT) AS VARCHAR), '-1') <#else> COALESCE(CAST(${prefix}[${attribute.attributeName}] AS VARCHAR), '-1') </#if></#macro><#macro sourceAttributeList sourceAttributes prefix=\"\"> <#if sourceAttributes?size == 0 > null <#else> <#list sourceAttributes as attribute><@castClouse attribute prefix/><#if attribute_has_next> + '~' + </#if></#list> </#if></#macro> ( SELECT CONVERT(VARCHAR(32),HASHBYTES('SHA2_256', STRING_AGG(<@sourceAttributeList sourceAttributes/>) WITHIN GROUP ( ORDER BY <@sourceAttributeList sourceAttributes/> ) ),2) FROM [${sourceEntity.owner}].[${sourceEntity.entityName}] value_list WHERE<#if useBatchLoading> <#if sourceEntity.containsRunIdAttribute> value_list.[${sourceEntity.runIdAttributeName}] IN (<loadablerunids>) <#else> value_list.<sourcerunidattr> IN (<loadablerunids>) </#if></#if><#if loadOptDefined(\"OPT_WHERE\")><#if useBatchLoading>AND </#if>${loadOpt(\"OPT_WHERE\")}</#if><#list keyAttributeList as keyAttribute> <#if useBatchLoading || loadOptDefined(\"OPT_WHERE\")>AND </#if> <@castClouse keyAttribute \"value_list.\"/>=<@castClouse keyAttribute \"src_entity.\"/></#list>)"
}
]