DW Mapping Script Syntax

In this topic:

Syntax
Sample Queries
Examples

Syntax

The syntax for inserting a DW Mapping Script is:

The following table outlines each component of the DW Mapping script syntax.

Component	Description	Possible values	Example
action	Script action to perform.	CREATE TABLE INSERT INTO/OVERWRITE TABLE MERGE INTO SELECT	`CREATE TABLE $table`
columns	Name of table or columns to operate on. $columns{<comma separated table names> \| exclude= <comma separated column names> : type1=value1: type2=value2:DEFAULT=value}	Table type, for example customer table.	`$columns{mcps:customer\|DEFAULT=$columnName $columnType}`
primary key	The name of the table or foreign key to operate on.		`dw` `raw`
foreign key	The scope of the table or what component of the table to output.	Specifying `$columnName` prints the name of the column, otherwise name of the table or column such as `dw`	`default=$columnName`
partition	Type of partition to perform.		`$partitions{mcps:customer\|DEFAULT=$sourceColName`

Sample Queries

Select

SELECT
$columns{mcps:customer|exclude=RowModifiedTS,RowCreatedTS
:PRIMARYKEY=concat(SourceID,'_',$sourceAttribute) AS $columnName
:ARRAY=udfs.merge_list($columnName) AS $columnName
:MAP=udfs.max_map($columnName, RowModifiedTS) AS $columnName
:DEFAULT=udfs.max_value($columnName, RowModifiedTS) AS $columnName},
FROM_UNIXTIME(UNIX_TIMESTAMP()) AS RowModifiedTS,
FROM_UNIXTIME(UNIX_TIMESTAMP()) AS RowCreatedTS}
$partitions{mcps:customer|DEFAULT=$sourceColName AS $TargetColName}
FROM $table{mcps:customer|raw}
GROUP BY
$primarykey{mcps:customer};

Create a partitioned table

CREATE TABLE $table{mcps:customer|raw}

     (

       $columns{mcps:customer|DEFAULT=$columnName  $columnType}

     )

PARTITIONED BY($partitions{mcps:customer | DEFAULT= $TargetColName   $TargetColType})

CLUSTERED BY(column1,column2)

STORED AS ORC

TBLPROPERTIES ("transactional"="true");

Insert into a table

INSERT INTO/OVERWRITE TABLE $table{mcps:customer|raw}

[PARTITION ($partitions{mcps:customer|DEFAULT=  $TargetColName })]

SELECT

        $columns{mcps:customer|DEFAULT=$columnName }

        $partitions{mcps:customer|DEFAULT=$sourceColName AS $TargetColName}

FROM  $table{mcps:customer|raw};

Merge dw table (Subquery)

MERGE INTO  $table{mcps:customer|dw} AS T
USING

(
      SELECT
        $columns{mcps:customer|exclude= RowModifiedTS,RowCreatedTS
        :PRIMARYKEY=concat(SourceID,'_',$SourceAttributeId(Customer)) AS $columnName
        :ARRAY=udfs.merge_list($columnName) AS $columnName
        :MAP=udfs.max_map($columnName, RowModifiedTS) AS $columnName
        :DEFAULT=udfs.max_value($columnName, RowModifiedTS) AS $columnName},
        FROM_UNIXTIME(UNIX_TIMESTAMP()) AS RowModifiedTS,
        FROM_UNIXTIME(UNIX_TIMESTAMP()) AS RowCreatedTS}
        $partitions{mcps:customer|DEFAULT=$sourceColName AS $TargetColName}
      FROM $table{mcps:customer|raw}
      GROUP BY
                 $primarykey{mcps:customer}
) S
ON t.ID = Concat(S.SourceID , $SourceAttributeId(Customer))
WHEN MATCHED THEN UPDATE
SET
     $columns(mcps:customer|exclude=primaryKeys,RowModifiedTS,RowCreatedTS
     :MAP=$columnName = udfs.combine(S.$columnName, T.$columnName)
     :ARRAY=$columnName = udfs.combine(S.$columnName, T.$columnName)
     :DEFAULT=$columnName = coalesce(S.$columnName, T.$columnName)},
    RowModifiedTS = FROM_UNIXTIME(UNIX_TIMESTAMP()),
    RowCreatedTS = T.RowCreatedTS
WHEN NOT MATCHED THEN INSERT VALUES
(
     $columns(mcps:customer|DEFAULT=S.$columnName}
     $partitions{mcps:customer|DEFAULT= $TargetColName}
);

Merge DW table (raw)

MERGE INTO $table{mcps:customer|dw} AS T

USING

       $table{mcps:customer|raw} AS S

ON t.ID = Concat(S.SourceID , $SourceAttributeId(Customer))

WHEN MATCHED AND [conditions] THEN UPDATE SET

      $columns(mcps:customer|

     exclude=primaryKeys,RowModifiedTS,RowCreatedTS

      :MAP=$columnName = udfs.combine(S.$columnName, T.$columnName)

     :ARRAY=$columnName = udfs.combine(S.$columnName, T.$columnName)
     :DEFAULT=$columnName = coalesce(S.$columnName, T.$columnName)},
RowModifiedTS = FROM_UNIXTIME(UNIX_TIMESTAMP()),
RowCreatedTS = T.RowCreatedTS
WHEN MATCHED [conditions] THEN DELETE
WHEN NOT MATCHED [conditions] THEN INSERT VALUES

        (

           $columns(mcps:customer|DEFAULT=S.$columnName}

           $partitions{mcps:customer|DEFAULT=$TargetColName}
       );

Examples

CreateSourceOrderItemID

{
  "tenantId": 15,
  "name": "CreateSourceOrderItemID",
  "versionTS": 1576624608381,
  "active": true,
  "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
  "createdBy": "MCPS_TEST_ADMIN_USER",
  "createdTS": 1576621425563,
  "type": "HIVE",
  "script": "INSERT INTO TABLE ${mcps:OrderItem:tableName|dw} PARTITION (`OrderEntryDate`) SELECT ${MCPS:OrderItem:columns|default=subQuery.$columnName}, from_unixtime(cast(subQuery.`OrderEntryTS` as bigint),'yyyyMM') FROM (SELECT ${MCPS:OrderItem:columns|exclude=SourceOrderItemID:default=$columnName},CONCAT(`SourceOrderItemID`, '_', reflect('java.util.UUID','randomUUID')) as `SourceOrderItemID` FROM ${mcps:OrderItem:tableName|raw}) subQuery;",
  "disableDefault": false,
  "order": 1,
  "entityName": "OrderItem",
  "scriptId": "CreateSourceOrderItemID"
}

{
  "tenantId": 15,
  "name": "CreateOrderTotal",
  "versionTS": 1576704273655,
  "active": true,
  "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
  "createdBy": "MCPS_TEST_ADMIN_USER",
  "createdTS": 1576693961791,
  "type": "HIVE",
  "script": "INSERT INTO TABLE ${mcps:Order:tableName|dw} PARTITION (`OrderEntryDate`) SELECT ${MCPS:Order:columns|default=subQuery.$columnName}, from_unixtime(cast(subQuery.`OrderEntryTS` as bigint),'yyyyMM')FROM (SELECT ${MCPS:Order:columns|exclude=Total,SourceOrderID:default=MAX($columnName) AS $columnName}, SUM(`Total`) AS Total, SourceOrderID FROM ${mcps:Order:tableName|raw} GROUP BY SourceOrderID) subQuery;",
  "disableDefault": false,
  "order": 1,
  "entityName": "Order",
  "scriptId": "CreateSourceOrderItemID"
}

{
  "tenantId": 8,
  "name": "OrderItemQA",
  "versionTS": 1575925504431,
  "active": true,
  "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
  "createdBy": "MCPS_TEST_ADMIN_USER",
  "createdTS": 1575925504431,
  "type": "HIVE",
  "script": "INSERT INTO TABLE ${mcps:OrderItem:tableName|dw}\nPARTITION (`OrderEntryDate`)\nSELECT \n ${MCPS:OrderItem:columns|default=subQuery.$columnName},\n subQuery.`OrderEntryDate`\nFROM\n(\nSELECT \n ${MCPS:OrderItem:columns|exclude=SourceCustomerID,OrderEntryTS:default=OI.$columnName},\n O.`SourceCustomerID` as `SourceCustomerID`, \n O.`OrderEntryTS` as `OrderEntryTS`, \n from_unixtime(cast(O.`OrderEntryTS` as bigint),'YYYYMM') AS `OrderEntryDate`\nFROM \n ${mcps:OrderItem:tableName|raw} OI\nLEFT OUTER JOIN \n (\n SELECT\n SourceOrderID, \n SourceID,\n udfs.max_value(SourceCustomerID, RowModifiedTS) AS SourceCustomerID,\n udfs.max_value(OrderEntryTS, RowModifiedTS) AS OrderEntryTS\n FROM \n ${mcps:Order:tableName|raw} \n GROUP BY \n SourceOrderID, \n SourceID\n )O on OI.SourceOrderID = O.SourceOrderID\n and OI.SourceID = O.SourceID\n)subQuery;",
  "disableDefault": false,
  "order": 1,
  "entityName": "OrderItem",
  "scriptId": "script1"
}

Order Create SourceCustomerID

{
  "tenantId": 15,
  "name": "OrderCreateSourceCustomerID",
  "versionTS": 1576777924618,
  "active": true,
  "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
  "createdBy": "MCPS_TEST_ADMIN_USER",
  "createdTS": 1576777924618,
  "type": "HIVE",
  "script": "INSERT INTO TABLE ${mcps:Order:tableName|dw} PARTITION (`OrderEntryDate`) SELECT ${MCPS:Order:columns|default=subQuery.$columnName}, from_unixtime(cast(subQuery.`OrderEntryTS` as bigint),'yyyyMM') FROM (SELECT ${MCPS:Order:columns|exclude=SourceCustomerID:default=$columnName},CONCAT(`SourceCustomerID`, '_', CASE WHEN Currency = 'AUD' THEN '17' WHEN Currency = 'IDR' THEN '8' WHEN Currency = 'MYR' THEN '3' WHEN Currency = 'PHP' THEN '4' WHEN Currency = 'SGD' THEN '2' WHEN Currency = 'THB' THEN '10' WHEN Currency = 'NTD' THEN '9' END) as `SourceCustomerID` FROM ${mcps:Order:tableName|raw}) subQuery;",
  "disableDefault": false,
  "order": 2,
  "entityName": "Order",
  "scriptId": "OrderCreateSourceCustomerID"
}

OrderItem Create SourceCustomerID

{
"tenantId": 15,
"name": "OrderItemCreateSourceCustomerID",
"versionTS": 1576777898547,
"active": true,
"lastModifiedBy": "MCPS_TEST_ADMIN_USER",
"createdBy": "MCPS_TEST_ADMIN_USER",
"createdTS": 1576777898547,
"type": "HIVE",
"script": "INSERT INTO TABLE ${mcps:OrderItem:tableName|dw} PARTITION (`OrderEntryDate`) SELECT ${MCPS:OrderItem:columns|default=subQuery.$columnName}, from_unixtime(cast(subQuery.`OrderEntryTS` as bigint),'yyyyMM') FROM (SELECT ${MCPS:OrderItem:columns|exclude=SourceCustomerID:default=$columnName},CONCAT(`SourceCustomerID`, '_', CASE WHEN CurrencyCode = 'AUD' THEN '17' WHEN CurrencyCode = 'IDR' THEN '8' WHEN CurrencyCode = 'MYR' THEN '3' WHEN CurrencyCode = 'PHP' THEN '4' WHEN CurrencyCode = 'SGD' THEN '2' WHEN CurrencyCode = 'THB' THEN '10' WHEN CurrencyCode = 'NTD' THEN '9' END) as `SourceCustomerID` FROM ${mcps:OrderItem:tableName|raw}) subQuery;",
"disableDefault": false,
"order": 2,
"entityName": "OrderItem",
"scriptId": "OrderItemCreateSourceCustomerID"
}

{
  "tenantId": 15,
  "name": "EventCreateSourceCustomerID",
  "versionTS": 1576793842554,
  "active": true,
  "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
  "createdBy": "MCPS_TEST_ADMIN_USER",
  "createdTS": 1576784483878,
  "type": "HIVE",
  "script": "INSERT INTO TABLE ${mcps:Event:tableName|dw} PARTITION (`EventDate`) SELECT ${MCPS:Event:columns|default=subQuery.$columnName}, from_unixtime(cast(subQuery.`EventTS` as bigint),'yyyyMM') FROM (SELECT ${MCPS:Event:columns|exclude=SourceCustomerID:default=$columnName},CONCAT(`SourceCustomerID`, '-', CASE WHEN EmailDomain = 'www.shopoback.com.au' THEN '17' WHEN EmailDomain = 'www.shopback.co.id' THEN '8' WHEN EmailDomain = 'www.shopback.my' THEN '3' WHEN EmailDomain = 'www.shoopback.ph' THEN '4' WHEN EmailDomain = 'www.shopback.sg' THEN '2' WHEN EmailDomain = 'www.myshopback.co.th' THEN '10' WHEN EmailDomain = 'www.shopback.com.tw' THEN '9' END) as `SourceCustomerID` FROM ${mcps:Event:tableName|raw}) subQuery;",
  "disableDefault": false,
  "order": 1,
  "entityName": "Event",
  "scriptId": "EventCreateSourceCustomerID"
}

c_fan_pre-script

{
    "tenantId": 103,
    "name": "c_fan_prescript",
    "versionTS": 1583430529986,
    "active": true,
    "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
    "createdBy": "MCPS_TEST_ADMIN_USER",
    "createdTS": 1583429003728,
    "type": "HIVE",
    "script": "INSERT INTO TABLE ${mcps:c_fan:tableName|dw}  SELECT ${MCPS:c_fan:columns|default=$columnName} FROM (SELECT concat('ATT_SAART_',FAN_ID) AS ID,  FAN_ID as Sourcec_FANID,  'ATT_SAART' as SourceID, max(tenantid) as tenantid,  max(createdby) as createdby,  max(modifiedby) as modifiedby,  max(createdts) as createdts,  max(modifiedts) as modifiedts,  max(rowcreatedts) as rowcreatedts,  max(rowmodifiedts) as rowmodifiedts FROM ${mcps:c_clm_sublive:tableName|raw}  WHERE fan_id IS NOT NULL  GROUP BY FAN_ID) a;",
    "disableDefault": false,
    "order": 1,
    "entityName": "c_fan",
    "scriptId": "c_fan_prescript"
  }

Account_c_FAN_prescript

[
  {
    "tenantId": 103,
    "name": "Account_c_FAN_prescript",
    "versionTS": 1583433436273,
    "active": true,
    "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
    "createdBy": "MCPS_TEST_ADMIN_USER",
    "createdTS": 1583432171733,
    "type": "HIVE",
    "script": "CREATE TABLE tmp_bci_acct (sourceaccountid string, Sourcec_GisID string, tenantid int, createdby string, modifiedby string, createdts timestamp, modifiedts timestamp, rowcreatedts timestamp, rowmodifiedts timestamp);\n\nCREATE TABLE tmp_aloc_mikey (ALOC string, MIKEY string);\n\nINSERT INTO TABLE tmp_aloc_mikey SELECT ALOC, MIKEY \nFROM (select ALOC, MAX(MIKEY) AS MIKEY FROM ${mcps:c_Gis:tableName|raw}  \nWHERE ALOC is not null AND MIKEY is not null\nGROUP BY ALOC) e;\n\nINSERT INTO TABLE tmp_bci_acct \nSELECT sourceaccountid, Sourcec_GisID, tenantid, createdby, modifiedby, createdts, modifiedts, rowcreatedts, rowmodifiedts\nFROM ${mcps:Account:tableName|raw} \nWHERE sourceid = 'ATT_SAART_BCI' AND c_billercode = 'W' AND Sourcec_GisID is not null;\n\nINSERT INTO TABLE ${mcps:Account_c_FAN:tableName|dw} \nSELECT ${MCPS:Account_c_FAN:columns|default=$columnName} \nFROM (\nSELECT concat('ATT_SAART_',concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER),',',FAN_ID) AS ID, \nmax(tenantid) as tenantid, \n'ATT_SAART' as SourceID, \nconcat(concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER),',',FAN_ID) as sourceaccount_c_fanid, \nconcat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER) as sourceaccountid, \nconcat('ATT_SAART_',concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER)) as accountid,\nFAN_ID as sourcec_fanid,\nconcat('ATT_SAART_',FAN_ID) as c_fanid, \nmax(createdby) as createdby, \nmax(modifiedby) as modifiedby, \nmax(createdts) as createdts, \nmax(modifiedts) as modifiedts, \nmax(rowcreatedts) as rowcreatedts, \nmax(rowmodifiedts) as rowmodifiedts\nFROM tmp_bci_acct a\nJOIN (select \n  account_id, \n  MAX(CLOC) as cloc,\n  MAX(ACCOUNT_NUMBER) as ACCOUNT_NUMBER,\n  MAX(FAN_ID) as FAN_ID\n  FROM\n  ${mcps:c_clm_sublive:tableName|raw}\n  WHERE cru_flag = 1\n  GROUP BY account_id\n) b\nON a.sourceaccountid = b.account_id \nLEFT OUTER JOIN tmp_aloc_mikey c ON c.aloc = a.Sourcec_GisID\nWHERE FAN_ID is not null AND concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER) is not null \nGROUP BY FAN_ID, concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER)) a;\n",
    "disableDefault": false,
    "order": 1,
    "entityName": "Account_c_FAN",
    "scriptId": "Account_c_FAN_prescript"
  }
]

Address_prescript

{
    "tenantId": 103,
    "name": "Address_prescript",
    "versionTS": 1583457807141,
    "active": true,
    "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
    "createdBy": "MCPS_TEST_ADMIN_USER",
    "createdTS": 1583449786176,
    "type": "HIVE",
    "script": "CREATE TABLE tmp_l2_saart (sourceaccountid string, c_saartlevel string);  INSERT INTO TABLE tmp_l2_saart  SELECT sourceaccountid, udfs.max_value(c_saartlevel, rowmodifiedts) as c_saartlevel  FROM ${mcps:Account:tableName|raw}  WHERE sourceid = 'ATT_SAART_L2L3_SOURCE'  AND coalesce(trim(c_saartlevel), '') != ''  GROUP BY sourceaccountid;  INSERT INTO TABLE ${mcps:Address:tableName|dw} SELECT ${MCPS:Address:columns|default=$columnName} FROM ${mcps:Address:tableName|raw}  WHERE SourceID NOT LIKE '%SAART%';    INSERT INTO TABLE ${mcps:Address:tableName|dw} SELECT ${MCPS:Address:columns|default=$columnName} FROM ${mcps:Address:tableName|raw}  WHERE sourceid = 'ATT_SAART_L2L3_SOURCE' AND size(split(sourceaddressid, \",\")) = 3;   INSERT INTO TABLE ${mcps:Address:tableName|dw} SELECT ${MCPS:Address:columns|default=$columnName} FROM ( SELECT   'ATT_SAART' as SourceID, CONCAT(b.sourceaccountid, '_custloc') as SourceAddressID, b.sourceaccountid as sourceaccountid, ${MCPS:Address:columns|exclude=SourceID,SourceAddressID,SourceAccountID:default=a.$columnName} FROM (SELECT * FROM ${mcps:Address:tableName|raw} where sourceid = 'ATT_SAART_L2L3_SOURCE' AND size(split(sourceaddressid, \",\")) = 1) a  JOIN tmp_l2_saart b  ON a.sourceaddressid = b.c_saartlevel) a;  CREATE TABLE tmp_bci_acct (sourceaccountid string, Sourcec_GisID string, type string, tenantid int, createdby string, modifiedby string, createdts timestamp, modifiedts timestamp, rowcreatedts timestamp, rowmodifiedts timestamp);  CREATE TABLE tmp_aloc_mikey (ALOC string, MIKEY string);  INSERT INTO TABLE tmp_aloc_mikey SELECT ALOC, MIKEY  FROM (select ALOC, MAX(MIKEY) AS MIKEY FROM ${mcps:c_Gis:tableName|raw}   WHERE ALOC is not null AND MIKEY is not null GROUP BY ALOC) e;  INSERT INTO TABLE tmp_bci_acct  SELECT sourceaccountid, Sourcec_GisID, type, tenantid, createdby, modifiedby, createdts, modifiedts, rowcreatedts, rowmodifiedts FROM ${mcps:Account:tableName|raw}  WHERE sourceid = 'ATT_SAART_BCI' AND c_billercode = 'W' AND Sourcec_GisID is not null;  INSERT INTO TABLE ${mcps:Address:tableName|dw} SELECT ${MCPS:Address:columns|default=$columnName} FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER) order by rowmodifiedts desc) as system_row, concat('ATT_SAART_', concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER)) as id, a.tenantid as tenantid, 'ATT_SAART' as sourceid, concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER) as sourceaddressid, concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER) sourceaccountid, concat('ATT_SAART_', concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER)) as accountid, array() as commerceaddresstype, b.ADDR1 as addressline1, b.ADDR2 as addressline2, CITY as city, ST as state, ZIP as zipcode, 'US' as country, map() as av_ncoa_mailto, a.createdby, a.modifiedby, a.createdts, a.modifiedts, a.rowcreatedts, a.rowmodifiedts, ${MCPS:Address:columns|exclude=ID,tenantid,sourceid,sourceaddressid,sourceaccountid,accountid,commerceaddresstype,addressline1,addressline2,city,state,zipcode,country,av_ncoa_mailto,createdby,modifiedby,createdts,modifiedts,rowcreatedts,rowmodifiedts:default=null as $columnName} FROM tmp_bci_acct a JOIN (select  account_id,  MAX(ADDR1) as ADDR1,  MAX(ADDR2) as ADDR2, MAX(CITY) as CITY, MAX(ST) as ST, MAX(ZIP) as ZIP, MAX(CLOC) as cloc, MAX(ACCOUNT_NUMBER) as ACCOUNT_NUMBER FROM ${mcps:c_clm_sublive:tableName|raw} where cru_flag = 1 GROUP BY account_id ) b ON a.sourceaccountid = b.account_id  LEFT OUTER JOIN tmp_aloc_mikey c ON c.aloc = a.Sourcec_GisID WHERE a.type is not null AND concat(a.Sourcec_GisID,',', b.cloc,',', coalesce(c.mikey, ''), ',', b.ACCOUNT_NUMBER) is not null) e WHERE system_row = 1;   INSERT INTO TABLE ${mcps:Address:tableName|dw} SELECT ${MCPS:Address:columns|default=$columnName} FROM ( SELECT concat('ATT_SAART_','fan_',FAN_ID) as id, max(tenantid) as tenantid, 'ATT_SAART' as sourceid, concat('fan_',FAN_ID) as sourceaddressid, array() as commerceaddresstype, TCM_ADDRESS1 as addressline1, TCM_ADDRESS2 as addressline2, TCM_CITY as city, TCM_STATE as state, TCM_ZIPCODE as zipcode, 'US' as country, map() as av_ncoa_mailto, max(createdby) as createdby, max(modifiedby) as modifiedby, max(createdts) as createdts, max(modifiedts) as modifiedts, max(rowcreatedts) as rowcreatedts, max(rowmodifiedts) as rowmodifiedts, FAN_ID as sourcec_fanid, concat('ATT_SAART_',FAN_ID) c_fanid, ${MCPS:Address:columns|exclude=ID,tenantid,sourceid,sourceaddressid,commerceaddresstype,addressline1,addressline2,city,state,zipcode,country,av_ncoa_mailto,createdby,modifiedby,createdts,modifiedts,rowcreatedts,rowmodifiedts,sourcec_fanid,c_fanid:default=null as $columnName}  FROM ${mcps:c_clm_sublive:tableName|raw}  GROUP BY FAN_ID, TCM_ADDRESS1, TCM_ADDRESS2, TCM_CITY, TCM_STATE, TCM_ZIPCODE) a;",
    "disableDefault": false,
    "order": 1,
    "entityName": "Address",
    "scriptId": "Address_prescript"
  }

Customer_prescript

[
  {
    "tenantId": 103,
    "name": "Customer_prescript",
    "versionTS": 1583529315416,
    "active": true,
    "lastModifiedBy": "MCPS_TEST_ADMIN_USER",
    "createdBy": "MCPS_TEST_ADMIN_USER",
    "createdTS": 1583529315416,
    "type": "HIVE",
    "script": "INSERT INTO TABLE ${mcps:Customer:tableName|dw} SELECT ${MCPS:Customer:columns|default=$columnName} FROM ${mcps:Customer:tableName|raw};  INSERT INTO TABLE ${mcps:Customer:tableName|dw} SELECT ${MCPS:Customer:columns|default=$columnName} FROM ( SELECT concat('ATT_SAART_','fan_',FAN_ID) as id , max(tenantid) as tenantid, 'ATT_SAART' as sourceid, concat('fan_',FAN_ID) as sourcecustomerid , TCM_EMAIL as email , array() as alternateemails , map() as alternatephones , map() as properties , TCM_FIRST_NAME as firstname , TCM_LAST_NAME as lastname , MAX(createdby) as createdby, MAX(modifiedby) as modifiedby, MAX(createdts) as createdts, MAX(modifiedts) as modifiedts, MAX(rowcreatedts) as rowcreatedts, MAX(rowmodifiedts) as rowmodifiedts, TCM_TITLE as title , TCM_TITLE as jobcode , FAN_ID as sourcec_fanid, concat('ATT_SAART_',FAN_ID) c_fanid, ${MCPS:Customer:columns|exclude=id,tenantid,sourceid,sourcecustomerid,email,alternateemails,alternatephones,properties,firstname,lastname,createdby,modifiedby,createdts,modifiedts,rowcreatedts,rowmodifiedts,title,jobcode,sourcec_fanid,c_fanid:default=null as $columnName} FROM ${mcps:c_clm_sublive:tableName|raw} GROUP BY FAN_ID, TCM_TITLE, TCM_FIRST_NAME, TCM_LAST_NAME, TCM_EMAIL ) a;  ",
    "disableDefault": false,
    "order": 1,
    "entityName": "Customer",
    "scriptId": "Customer_prescript"
  }
]