target_entity_attribute_list_with_transform_cast_and_positions
Supported databases: Databricks SQL, Azure Fabric, Azure SQL Database, Azure Synapse SQL,Snowflake
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 source entity position
See also:
Usage
<target_entity_attribute_list_with_transform_cast_and_positions>
Notes
Supported in all loads.
For Azure databases only the 1-1 mapping will be resolving the position to support column list syntax defined in https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql .
For Databricks each source column reference will be done through position in form _c<order> (indexing beginning from zero).
For Snowflake each source column reference will be done through position in form $<order> (indexing beginning from one).
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_cast_and_positions>
from <source_entity_schema>.<source_entity_name>;
Load executed in the target database Snowflake:
insert into publish.D_RATECODE
select MD5(UPPER(NVL($1, '-1') || '~' || NVL($2, '-1')|| '~' || NVL($3, '-1'))) AS key_id,
<timemillis> AS run_id,
$4
from src.SOURCE;
Load executed in the target database Databricks:
insert into publish.D_RATECODE
select MD5(UPPER(COALESCE(CAST(_c1 AS STRING), '-1') || '~' || COALESCE(CAST(_c2 AS STRING), '-1')|| '~' || COALESCE(CAST(_c3 AS STRING), '-1'))) AS key_id,
<timemillis> AS run_id,
CAST(_c3 AS STRING) 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],
information 4
from [src].[SOURCE];