Create stored procedures in Snowflake (optional)
Learn how to create optional stored procedures for manipulating Reltio data to be used in the Reltio object type views in Snowflake.
Before you create stored procedures, you must have created a database schema. For more information, see topic Create a Snowflake schema.
User-defined functions in Snowflake are called stored procedures in Reltio. Optionally create stored procedures to transform Reltio's JSON data into flattened/transformed values for use in either:
Legacy (optional) entities processing tasks in Snowflake
OV Only Views for Entities and Relations in Snowflake
Note: Only create stored procedures if you decided to use legacy staging tables, streams, and processing tasks, or if you intend to create OV-only views for entities and relations.
To create stored procedures in your Snowflake environment:
- In the Snowflake application worksheet area, create a new SQL worksheet.
- In the SQL worksheet, run these commands to create stored procedures for the types of data to transform:
OV Single Value Transformation (Used only in the legacy entity type processing task)
CREATE FUNCTION SINGLE_VALUE_TRANSFORMATION(json object) returns object language javascript strict as ' clearAttributes(JSON.attributes); clearCrosswalks(JSON.crosswalks); return JSON; function clearAttributes(attributes) { outerLoop: for (let key in attributes) { let attribute = attributes[key]; let values = []; let references = []; if (attribute.length > 0) { for (let i in attribute) { if (attribute[i].ov === true) { if (attribute[i].refEntity) { references.push({ "refEntity": { "objectURI": attribute[i].refEntity.objectURI } }); } if (attribute[i].refRelation) { references.push({ "refRelation": { "objectURI": attribute[i].refRelation.objectURI } }); } let value = attribute[i].value; if (attribute[i].lookupCode) { value = attribute[i].lookupCode } if (typeof value === "object") { values.push(clearAttributes(value)); } else { let newAttribute = {}; newAttribute[key] = value; Object.assign(attributes, newAttribute); continue outerLoop; } } } } attribute.length = 0; for (let j in values) { let atts = values[j]; for (let k in references) { Object.assign(atts, references[k]); } attribute.push(atts); } } return attributes; } function clearCrosswalks(crosswalks) { if (crosswalks.length > 0) { let newCrosswalks = []; for (let i in crosswalks) { let oldCrosswalk = crosswalks[i]; let newCrosswalk = {}; newCrosswalk["type"] = oldCrosswalk["type"]; newCrosswalk["value"] = oldCrosswalk["value"]; newCrosswalks.push(newCrosswalk); } crosswalks.length = 0; for (let j in newCrosswalks) { let crosswalk = newCrosswalks[j]; crosswalks.push(crosswalk); } } return crosswalks; } ';Attributes OV Multi Value Transformation (Used only in the OV only entity and relation type views)
CREATE FUNCTION ATTRIBUTES_MULTI_VALUE_TRANSFORMATION(json object) returns object language javascript strict as ' clearAttributes(JSON) return JSON; function clearAttributes(attributes) { for (let key in attributes) { let attribute = attributes[key]; let values = []; if (attribute.length > 0) { for (let i in attribute) { if (attribute[i].ov === true || attribute[i].isOv === true || (attribute[i].ov === undefined && attribute[i].isOv === undefined)) { let value = attribute[i].value; if (attribute[i].lookupCode) { value = attribute[i].lookupCode } if (typeof value === "object") { values.push(clearAttributes(value)); } else { values.push(value); } if (attribute[i].refEntity) { value.refEntity = { "objectURI": attribute[i].refEntity.objectURI }; } if (attribute[i].refRelation) { value.refRelation = { "objectURI": attribute[i].refRelation.objectURI }; } } } } attribute.length = 0; for (let j in values) { let atts = values[j]; attribute.push(atts); } } return attributes; } ';Crosswalks Transformation (Used only in the OV only entity and relation type views)
CREATE OR REPLACE FUNCTION CROSSWALKS_TRANSFORMATION(json array) returns array language javascript strict as ' clearCrosswalks(JSON); return JSON; function clearCrosswalks(crosswalks) { if (crosswalks.length > 0) { let newCrosswalks = []; for (let i in crosswalks) { let oldCrosswalk = crosswalks[i]; let newCrosswalk = {}; newCrosswalk["type"] = oldCrosswalk["type"]; newCrosswalk["value"] = oldCrosswalk["value"]; newCrosswalks.push(newCrosswalk); } crosswalks.length = 0; for (let j in newCrosswalks) { let crosswalk = newCrosswalks[j]; crosswalks.push(crosswalk); } } return crosswalks; } '; - In the SQL worksheet, view the Successfully created notification message.
For general information, see Create function in the Snowflake SQL command reference.