Creating Snowflake Tables and Databases with Collibra Metadata
I just learned our SQL DBAs have been manually exporting metadata from Collibra and importing it to Snowflake. They are migrating all data on virtual machines to Snowflake Cloud. I know there are API on both sides. Has anyone written a script that would export Collibra metadata (Collibra holds everything, old databases are barren) and create a Snowflake DDL file to recreate databases in Snowflake?
Heather Fara
1 comment
George Cheung
Hi Heather:
In Collibra, there is an API used to extract data called the Output Module:
https://developer.collibra.com/api/references/data-governance/output-module
https://developer.collibra.com/api/guides/output-module/the-output-module-query-language
The export options frequently used/recommended: json or excel
POST /outputModule/export/json - Exports results in JSON format, returns the results immediately.
POST /outputModule/export/excel-file - Exports results in Excel format, returns information about created file.
In either call, you would need to pass in a parameter called the TableViewConfig.
Example Table View Config used to extract all columns in the OOTB Community:
{ "TableViewConfig": { "displayLength": -1, "Resources" : { "Asset": { "Signifier": {"name": "FullName"}, "DisplayName": {"name": "ColumnName"}, "Id": {"name": "Id"}, "Domain": { "Community": { "Id": {"name": "Community_Id"} } }, "AssetType": { "Id": {"name": "AssetType_Id"} }, "Relation": [ { "typeId": "00000000-0000-0000-0000-000000007042", "type" : "SOURCE", "Target": { "DisplayName": {"name": "TableName"}, "Relation" : [ { "typeId": "00000000-0000-0000-0000-000000007043", "type" : "TARGET", "Source": { "DisplayName": {"name": "SchemaName"} } } ] } } ], "StringAttribute": [ { "labelId" : "00000000-0000-0000-0001-000500000074", "LongExpression": {"name": "DescriptionSourceSystem"} }, { "labelId" : "00000000-0000-0000-0000-000000003114", "LongExpression": {"name": "Description"} }, { "labelId" : "00000000-0000-0000-0000-000000000219", "LongExpression": {"name": "TechnicalDataType"} } ], "BooleanAttribute": [ { "labelId" : "00000000-0000-0000-0001-000500000015", "BooleanValue": {"name": "IsPrimaryKey"} }, { "labelId" : "00000000-0000-0000-0001-000500000011", "BooleanValue": {"name": "isNullable"} } ], "NumericAttribute": [ { "labelId" : "00000000-0000-0000-0001-000500000020", "NumericValue": {"name": "ColumnPosition"} }, { "labelId" : "00000000-0000-0000-0001-000500000009", "NumericValue": {"name": "Size"} }, { "labelId" : "00000000-0000-0000-0001-000500000010", "NumericValue": {"name": "NumberOfFractionalDigits"} }, { "labelId" : "00000000-0000-0000-0001-000500000012", "NumericValue": {"name": "OctetLength"} } ], "Filter": { "AND": [ { "Field": { "name" : "Community_Id", "operator" : "IN", "caseInsensitive": true, "descendants" : true, "values" : ["00000000-0000-0000-0001-000100000001"] } }, { "Field": { "name" : "AssetType_Id", "operator": "EQUALS", "value" : "00000000-0000-0000-0000-000000031008" } } ] }, "Order": [ { "Field": {"name": "ColumnName", "order": "ASC"} } ] } }, "Columns" : [ { "Column": {"fieldName": "ColumnName", "label": "Column Name"} }, { "Column": {"fieldName": "TableName", "label": "Table Name"} }, { "Column": {"fieldName": "SchemaName", "label": "Schema Name"} }, { "Column": { "fieldName": "DescriptionSourceSystem", "label" : "Description from source system" } }, { "Column": {"fieldName": "Description", "label": "Description"} }, { "Column": { "fieldName": "TechnicalDataType", "label" : "Technical Data Type" } }, { "Column": {"fieldName": "ColumnPosition", "label": "Column Position"} }, { "Column": {"fieldName": "IsPrimaryKey", "label": "Is Primary Key"} }, { "Column": {"fieldName": "isNullable", "label": "Is Nullable"} }, { "Column": {"fieldName": "Size", "label": "Size"} }, { "Column": { "fieldName": "NumberOfFractionalDigits", "label" : "Number Of Fractional Digits" } }, { "Column": {"fieldName": "OctetLength", "label": "Char octet Length"} } ] } }In the example above - it is extracting the column name, table name, schema name, and the column's description and other fields populated during metadata synchronization. This tableViewConfig can be further modified to include other information that can be extracted from collibra.
After the column information is extracted from the collibra side using a REST API in a script - it can then be used in the creation elsewhere by parsing the results in json or excel.
Hope this helps.