Data Rules Extensions: A Step-by-Step Guide
- Pamela Totas (Deactivated)
Using core ontology for ETL processes is the best way to work with Data Rules, but sometimes it is necessary to extend the core resource to support additional fields and custom data. In this case, the best practice is to create an extension of the Data Rules core resource which will contain all the necessary information about the new fields.
Supported Types of Extension Columns
Details below is required minimum to describe additional fields as an extension element:
path - describes a path of the element;
datatype - describes a type of the data ('string', ‘number', 'date’, etc.);
formatdialect - an attribute of the datatype (as example
"length": 16for 'string').
All extension elements are stored in the “extension” complex element.
If the structure of the extension table is defined in advance, then extension element can have extra DB description:
owner - database SCHEMA
table_name - database TABLE
column_name - database COLUMN
On this page
- 1 Supported Types of Extension Columns
- 2 Fields for an Extension Table
- 3 Fields for Summary Extension Tables
- 4 Create an Extension
- 5 Processing Rule Generation for an Extension
- 6 Alter DDL for an Extension
- 7 Load and Extract Data to/from Extension Table
- 8 How to Remove an Extension
- 9 Extensions in Oracle
- 10 Create an extension in Eagle via EDS UI
- 11 Create an Extension for the Resources with History Mode
Fields for an Extension Table
When you create Data Rules extension which already has existing DB structure in market database, you have to mimic the same structure in Snowflake. Each element must be described to have the same table in Snowflake, as in below example:
"vocabulary":{
"newField": {
"path": "extension/newElement",
"datatype": "integer",
"owner": "CUSTOMDBO",
"table_name": "POSITION_DETAIL_EXT",
"column_name": "NEW_ELEMENT_INT",
"formatdialect": {
"precision": "38",
"scale": "0"
}
},
...
}When DB structure of extension is not predefined, you can create extension without DB parameters definition.
DB parameters for this case will be generated automatically:
owner -
{in_market_client_id}DBOtable_name -
{RESOURCE_NAME}_EXTcolumn_name -
{ELEMENT}
Information about the owner is taken from consul config dict by “in_market_client_id”.
https://consul.dev.az.eagleinvsys.com/ui/az-dev/kv/services/svc-eds/
Example:
"vocabulary":{
"newElementChar": {
"path": "extension/newElementChar",
"datatype": "string",
"formatdialect": {
"length": 16
}
},
...
}Fields for Summary Extension Tables
Fields for summary extension tables can be created if core resource has summary extension table
(e.g. warehouseposition).
All these fields are provided in summary_vocabulary block.
Example when you have predefined DB structure:
"vocabulary":{
"newField": {
"path": "extension/positionDetailId",
"datatype": "integer",
"owner": "CLIENTID",
"table_name": "POSITION_DETAIL_EXT",
"column_name": "POSITION_DETAIL_ID",
"formatdialect": {
"precision": "38",
"scale": "0"
}
},
...
},
"summary_vocabulary":{
"newSummaryField": {
"path": "extension/newSummaryField",
"datatype": "integer",
"owner": "JANUS",
"table_name": "POSITION_EXT",
"column_name": "POSITION_ID",
"formatdialect": {
"precision": "38",
"scale": "0"
},
...
}Example when you don`t have predefined DB structure:
"vocabulary":{
"newField": {
"path": "extension/positionDetailId",
"datatype": "integer",
"formatdialect": {
"precision": "38",
"scale": "0"
}
},
...
},
"summary_vocabulary":{
"newSummaryField": {
"path": "extension/newSummaryField",
"datatype": "integer",
"formatdialect": {
"precision": "38",
"scale": "0"
},
...
}DB parameters for this case will be generated automatically:
owner -
{in_market_client_id}DBOtable_name -
{RESOURCE_NAME}_EXTtable_name for summary vocabulary -
{RESOURCE_NAME}_EXT_SUMMARYcolumn_name -
{ELEMENT}
Create an Extension
To generate an extension via Rest API endpoint in Vault:
Please use the Ontology Publish Service API.
Where:
X-Eagle-Context - is Tenant ID;
Request body: see an example of the request below:
‘generateldm’ parameter should be passed as “N”
To generate an extension in Eagle via RTR:
To generate an extension via RTR the following RTR should be used via stream eagle_ml-2-0_default_cm_exec_eds , the example is below.
Add extension fields to “load”: “dataframe”: “vocabulary” list in resource JSON definition. (see in expand)
The attached RTR is an example of extension definition ‘resourcejsondef’ of Data Rules resource warehouseposition.
Generated extension can be located in the below folder
dynamic/metadata/custom/ontology/{in_market_cliend_id}
Processing Rule Generation for an Extension
A Processing rule is automatically generated when you create an extension.
Alter DDL for an extension will be automatically executed when you generate extension via API.
Cache will be stored in Redis and can be found via the following key:
eagle.ebs.resource:{in_market_cliend_id}_{resource_name}_{core_ontology version}
Example: eagle.ebs.resource_ik:CLI_WAREHOUSEPOSITION_1.0.1
The processing rule for the extension can be also generated via RTR for the corresponding core resource.
An example of the RTR to regenerate processing rule for an extension of core Data Rules warehouseposition resource:
Alter DDL for an Extension
This is an optional step, the new tables and fields will be created in the DB during the execution of processing rule generation:
The Extension describes tables/fields that do not exist in the database
Additional Summary fields are required/added
Alter DDL RTR for extension resource is the same as Alter DDL RTR for a core resource.
Load and Extract Data to/from Extension Table
Congratulations! Now extension is successfully generated and ready to use.
Load
To load data, please use the same RTR as for the Data Rules core resource.
New fields or Extensions elements are listed under the “extension” node:
Sample xml:
...
<extension>
<newElementNum>1</newElementNum>
<newElementString>str</newElementString>
</extension>
...Sample csv:
...,extension/newElementNum,extension/newElementString,...
...,1,str,...An example of warehouseposition extension data is attached:
Extract
To extract data use the same RTR as for the core resource
Attention: if new DB fields are described in extension, then Alter DDL is mandatory step for Load and Extract. Otherwise, there will be failures.
How to Remove an Extension
Delete extension ontology files from azure:
https://portal.azure.com/#@eagleinvsys.net/resource/subscriptions/030c4927-46ed-4031-934e-d8cb445e75d0/resourceGroups/dev-eastus2-storage-eds/providers/Microsoft.Storage/storageAccounts/deveastus2eds/containersList
metadata/custom/ontology/{in_market_client_id}/{in_market-client_id}_{resource}*.json
Example for EDS tenant warehouseposition extension:
metadata/custom/ontology/da1t1cedstest/da1t1cedstest_warehouseposition.json
metadata/custom/ontology/da1t1cedstest/da1t1cedstest_warehouseposition-models.json
Delete processing rule for extension from cache
https://apps.dev.az.eagleinvsys.com:8443/api/vault/metadata/api/doc#/processing-rule/deleteResource
resource-name = {in_market-client_id}_{resource}
resourceversion = current CORE ontology version (taken from Consul) + ontology versions from blob
Example for EDS tenant rule for warehouseposition extension:
name = DA1T1CEDSTEST_WAREHOUSEPOSITION
version = 2.0.39.1.0.1
Extensions in Oracle
End users can create an extension in Eagle via EDS UI for any core resource in case of Extract.
In case of data load to Oracle we use core Stored Procedure, so it limited at this time to below list of resources:
genericentity
genericsmf
warehouseposition
warehousetrade
warehousecashactivity
Create an extension in Eagle via EDS UI
Prerequisite: custom database table exists in the Eagle Oracle database
Please open EDS Tool to create new EDS extension. It can be found from Eagle pearl menu:
Click on the ‘New' button from the ribbon and choose 'EDS Extension’.
New EDS extension will be created off core EDS resource. Please select required resource name in the Search window that will be used for a new extension. Please continue with create button, the extension name will be added automatically.
As part of ‘Table Definition’ please ‘Add Table' (1) or click on '…’ button (2) to select a table.
In the opened window select required database schema and table (3). (If Datatable is not listed please create a table in DB)
All DB fields will be automatically identified by the tool and displayed for the end user. Click ’Select' to add fields to EDS extension (4):
The definition of the extension model is available via ‘Preview’:
Please review EDS Extension and publish your extension:
(While publishing if warnings are displayed as the (extensionFileName.json exists) try to delete the file or ignore it and publish it)
An example of warehousetrade extension:
<extension>
<nmTradeDate1>2022-09-30</nmTradeDate1>
<nmTradeDate2>2007-04-14</nmTradeDate2>
<nmTradeNumber1>16</nmTradeNumber1>
<nmTradeNumber2>12.162</nmTradeNumber2>
<nmTradeNumber3>2022609.8</nmTradeNumber3>
<nmTradeVarchar1>asasasasasasas1</nmTradeVarchar1>
<nmTradeVarchar2>2h</nmTradeVarchar2>
</extension>Create an Extension for the Resources with History Mode
DB table has to be created with the same name as for composite extension + _HIST suffix, for example, if composite extension table is called ESTAR.ENTITY_EXT, the history table should be ESTAR.ENTITY_EXT_HIST
The history table should have a proper primary key:
genericsmf - the PK for composite extension tables is SECURITY_ALIAS and SECURITY_ALIAS, EFFECTIVE_DATE, SRC_INTFC_INST for history extension table
genericentity - the PK for composite extension table is ENTITY_ID and ENTITY_ID, EFFECTIVE_DATE for history extension table (history mode is currently not available )
Do not add a history table to the extension model in EDS Tool, only composite table should be there.
To load history data to extension use historyOnlyFlag=Y parameter in the data file.