Data Objects

Metadata Import

Importing metadata into the Agnostic Data Labs app format can be done through the app and it’s metadata importer feature.

The importer format is a lightweight version of the full metadata schema, and allows for matching the imported metadata to the existing metadata by names and conventions instead of identifiers.

If the metadata you gather has the required information, consider adding the generated metadata as json files straight to the repository instead of running it through the importer.

The main simplification done in the removal of id attributes and the use of name matching instead.

The main added feature is the ability to define the target file name as part of the imported metadata. The formal metadata schema doesn’t include the folder/file names in the metadata itself, so defining it on import makes it easier to organize the metadata both in the model as well as on disk. The format is also a list of Connections or Data Objects, allowing an arbitrary set of either metadata type to be imported.

The following SQL query illustrates how to query a SQL server for databases and generate an import JSON set for all databases in scope.

The imported metadata nodes will create or update a connection for each database.

/*
 * Copyright (c) Agnostic Data Labs, https://agnosticdatalabs.com
 * All rights reserved.
 * Licensed under the MIT License.
 * See License in the project root for license information.
 *
 * Sample SQL Server metadata script to generate JSON representation of Tables for
 * Metadata Import into App. These are then stored as Data Objects and Data Items
 * Structure should be same for all current versions of SQL Server that support JSON
 * This specific sample uses a few tables from AdventureWorksLT2012 as example
 * Change the USE statement, or remove it to use the current database or for Azure SQL db
 *
 * This script includes primary key metadata in the output
 *
 * NB the type discriminator 'dataObject'/'dataObjectQuery' must be the first column
 */

USE AdventureWorksLT2012
--USE WideWorldImporters
--USE Northwind
GO

-- CTE to generate Object level Extensions
;WITH ObjectExtensions
 AS (SELECT ta.[object_id],
            'datastore' AS [key],
            DB_NAME() AS [value],
            'database name' AS [notes]
     FROM sys.tables ta
     UNION ALL
     SELECT ta.[object_id],
            'location' AS [key],
            SCHEMA_NAME(ta.schema_id) AS [value],
            'schema name' AS [notes]
     FROM sys.tables ta
     UNION ALL
     SELECT ta.[object_id],
            'schemaQualifiedName' AS [key],
            '[' + SCHEMA_NAME(ta.[schema_id]) + '].[' + ta.[name] + ']' AS [value],
            'SQL Server format schema qualified name' AS [notes]
     FROM sys.tables ta)

-- Main Query to generate JSON
SELECT 'dataObject' AS [dataObjectType],
       ta.name AS [name],

       -- optional file location information
       '/' + DB_NAME() + '/' + SCHEMA_NAME(ta.[schema_id]) AS [folder],
       [name] + '.json' AS [fileName],

       -- optional connection reference
       DB_NAME() AS [dataConnection.name],

       -- Data Items array
       (
           SELECT 'dataItem' AS [dataItemType],
                  c.[name] AS [name],
                  c.[column_id] AS [ordinalPosition],
                  CAST(COALESCE(
                       (
                           SELECT CAST(i.[is_primary_key] AS BIT)
                           FROM sys.index_columns ic
                               INNER JOIN sys.indexes i
                                   ON ic.[object_id] = i.[object_id]
                                      AND ic.[index_id] = i.[index_id]
                           WHERE ic.[object_id] = c.[object_id]
                                 AND ic.[column_id] = c.[column_id]
                                 AND i.[is_primary_key] = 1
                       ),
                       NULL
                               ) AS BIT) AS [isPrimaryKey],
                  ty.[name] AS [dataType],

                  -- derive relevant data type attributes, but only those required. update this to cover any specific needs or custom data types
                  CASE
                      WHEN ty.[name] IN ( 'varchar', 'varbinary', 'char', 'binary' ) THEN
                          c.[max_length]
                      WHEN ty.[name] IN ( 'nvarchar', 'nchar' ) THEN
                          c.[max_length] / 2
                      ELSE
                          NULL
                  END AS [characterLength],
                  CASE
                      WHEN ty.[name] IN ( 'decimal', 'numeric', 'float', 'real' ) THEN
                          c.[precision]
                      ELSE
                          NULL
                  END AS [numericPrecision],
                  CASE
                      WHEN ty.[name] IN ( 'decimal', 'numeric', 'time', 'datetime2', 'datetimeoffset' ) THEN
                          c.[scale]
                      ELSE
                          NULL
                  END AS [numericScale],
                  CASE
                      WHEN c.[is_nullable] = 1 THEN
                          c.[is_nullable]
                      ELSE
                          NULL
                  END AS [isNullable],

                  -- Data Item level Extensions
                  (
                      SELECT 'isIdentityColumn' AS [key],
                             'true' AS [value]
                      FROM sys.columns cExt
                      WHERE cExt.[object_id] = c.[object_id]
                            AND cExt.[column_id] = c.[column_id]
                            AND c.[is_identity] = 1
                      FOR JSON PATH
                  ) AS [extensions]
           FROM sys.columns c
               INNER JOIN sys.types ty
                   -- translate any user data types to base datatypes for the metadata use
                   ON c.[system_type_id] = ty.[system_type_id]
                      AND ty.[system_type_id] = ty.[user_type_id]
           WHERE ta.[object_id] = c.[object_id]
           ORDER BY c.[column_id],
                    c.[name]
           FOR JSON PATH
       ) AS [dataItems],

       -- add Object level extensions from CTE
       (
           SELECT [key],
                  [value]
           FROM ObjectExtensions oe
           WHERE oe.[object_id] = ta.[object_id]
           FOR JSON PATH
       ) AS [extensions]

-- include only tables
FROM sys.tables ta
WHERE

    -- filter on schema
    SCHEMA_NAME(ta.[schema_id]) = 'SalesLT'

-- and/or table name
--     AND ta.[name] IN ( 'Product', 'ProductCategory', 'ProductModel', 'ProductModelProductDescription',
--                        'ProductDescription'
--                      )

-- optionally order output
ORDER BY SCHEMA_NAME(ta.[schema_id]),
         ta.[name]
FOR JSON PATH