target_entity_attribute_list_with_transform_cast_and_names
First SOURCE type entity mapping will be used for the resolution of this variable. The one to one source-target attribute mappings without transformation will be added with their datatype casts
See also:
Usage
<target_entity_attribute_list_with_transform_cast_and_names>
Notes
Supported in all loads.
Examples
Variable used in an OVERRIDE load step
Given mapping:
Target entity: publish.D_RATECODE
Source entity: src.SOURCE
source attributes | Formula | Target attribute |
---|---|---|
attribute_A,attribute_B,attribute_C | HASH | key_id |
RUN_ID | run_id | |
attribute_D | information |
OVERRIDE load step in D_RATECODE entity:
insert into <target_schema>.<target_entity_name>
select <target_entity_attribute_list_with_transform>
from <source_entity_schema>.<source_entity_name>;
Load executed in the target database Snowflake:
insert into publish.D_RATECODE
select MD5(UPPER(NVL(attribute_A, '-1') || '~' || NVL(attribute_B, '-1')|| '~' || NVL(attribute_C, '-1'))) AS key_id,
<timemillis> AS run_id,
CAST(attribute_D AS VARCHAR(40)) AS information
from src.SOURCE;
Load executed in the target database BigQuery:
insert into publish.D_RATECODE
select TO_HEX(MD5(UPPER(CONCAT(COALESCE(CAST(attribute_A AS STRING), '-1'),'~',COALESCE(CAST(attribute_B AS STRING), '-1'),'~',COALESCE(CAST(attribute_C AS STRING), '-1'))) AS key_id,
<timemillis> AS run_id,
CAST(attribute_D AS STRING(40)) AS information
from src.SOURCE;
Load executed in the target database Databricks:
insert into publish.D_RATECODE
select MD5(UPPER(COALESCE(CAST(attribute_A AS STRING), '-1') || '~' || COALESCE(CAST(attribute_B AS STRING), '-1')|| '~' || COALESCE(CAST(attribute_C AS STRING), '-1'))) AS key_id,
<timemillis> AS run_id,
CAST(attribute_D AS STRING(40)) AS information
from src.SOURCE;
Load executed in the target database Microsoft Fabric, Azure SQL Database, Azure Synapse SQL:
insert into [publish].[D_RATECODE]
select CONVERT(VARCHAR(32),HASHBYTES('MD5', UPPER(TRIM(COALESCE([attribute_A], '-1')) + '~' + TRIM(COALESCE([attribute_B], '-1')) + '~' + TRIM(COALESCE([attribute_C], '-1')))),2) AS [key_id],
<timemillis> AS [run_id],
CAST(attribute_D AS NVARCHAR(40)) AS [information]
from [src].[SOURCE];
Load executed in the target database Redshift:
insert into publish.D_RATECODE
select MD5(UPPER(NVL(attribute_A, '-1') || '~' || NVL(attribute_B, '-1')|| '~' || NVL(attribute_C, '-1'))) AS key_id,
<timemillis> AS run_id,
CAST(attribute_D AS VARCHAR(40)) AS information
from src.SOURCE;