Create a DW Mapping Script
Creates a new DW Mapping script.
| Request Method | Service URL |
|---|---|
|
|
/api-metadata/v1/{access-key}/metadata/dwmappingscripts
|
|
Request Headers |
|
Request parameters
Required
|
Name |
Type |
Description |
Possible values |
|---|---|---|---|
| type | string | The DW Mapping script type. | Must be set to HIVE. |
| script | string |
Script content in SQL. |
|
| order | string | Priority in which script is executed. |
Optional
|
Name |
Type |
Description |
Possible values |
|---|---|---|---|
| tenantId | integer | Your tenant ID. | |
| name | string | The name of the DW Mapping script to create. | |
| disableDefault | boolean | Whether pre-script overrides standard DW scripts. The default is false, which uses the standard DW Mapping script upsert logic. To instead use standard logic to delete, set to true. |
|
| entityName | string | The name of the entity in the account. | |
| scriptId | string | The identifier of the script. |
Response parameters
|
Name |
Type |
Description |
Possible values |
|---|---|---|---|
| tenantId | integer | Your tenant ID. | |
| name | string | Name of the DW Mapping script. | |
| versionTS | date/time | Timestamp when the DW Mapping script was last updated. Expressed in epoch time, down to milliseconds. | |
| active | boolean | Whether the DW Mapping script is active or not. |
|
| lastModifiedBy | string | Name of the user who last updated the DW Mapping script. Expressed in epoch time, down to milliseconds. | |
| createdBy | string | Name of the user who created the DW Mapping script. | |
| createdTS | date/time | Timestamp when the DW Mapping script was created. Expressed in epoch time, down to milliseconds. | |
| type | string | DW Mapping script type. | HIVE |
| script | string | Script content in SQL. | |
| disableDefault | boolean | Whether pre-script overrides standard DW scripts. The default is false, which uses the standard DW Mapping script upsert logic. To instead use standard logic to delete, set to true. |
|
| order | integer | Priority in which script is executed. | |
| entityName | string | Name of the entity in Oracle Unity. | |
| scriptId | string | The identifier of the script. |
Example
Create a DW Mapping script.
http://{AccountURL}/api-metadata/v1/e2a7a0df2410470d85ef69c8dad2bdb8/metadata/dwmappingscripts
Request body:
{
"tenantId": 1,
"name": "OrderItem",
"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,
"entityName": "OrderItem",
"scriptId": "script1"
}
Response:
A successful response displays information about the DW Mapping script.
{
"tenantId": 1,
"name": "OrderItem",
"versionTS": 1585237653775,
"active": true,
"lastModifiedBy": "ADMIN_USER",
"createdBy": "ADMIN_USER",
"createdTS": 1585237653775,
"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"
}