Connections

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 Databases for
 * Metadata Import into App. These are then stored as Connections in the metadata
 *
 * Referenced names/databases from:
 *
 * - AdventureWorks: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
 * - Wide World Importers: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
 * - Northwind: https://github.com/microsoft/sql-server-samples/blob/master/samples/databases/northwind-pubs/readme.md
 */

USE master
GO

-- Derive Connection Extensions as needed (key/value/notes)
;WITH DbExtensions
 AS (SELECT [database_id],
            'connectionType' AS [key],
            'MSSQLDB' AS [value],
            'database/connection type' AS [notes]
     FROM sys.databases
     UNION ALL
     SELECT [database_id],
            'defaultDatastore' AS [key],
            [name] AS [value],
            'the default database name/datastore to use for the connection' AS [notes]
     FROM sys.databases
     UNION ALL
     SELECT [database_id],
            'defaultLocation' AS [key],
            'dbo' AS [value],
            'the default schema name/location to use for the connection' AS [notes]
     FROM sys.databases)
-- Main Query to generate JSON
-- name is the minimum information needed for the Connection
-- add folder/filename and extensions etc as needed
SELECT [name],
       '/sources' AS [folder],
       [name] + '.json' AS [fileName],
       -- Add the Connection extensions from the CTE
       (
           SELECT [key],
                  [value]
           FROM DbExtensions de
           WHERE de.[database_id] = db.[database_id]
           FOR JSON PATH
       ) AS [extensions]
FROM sys.databases db
-- Filter to specific databases as needed
WHERE [name] IN ( 'AdventureWorksLT2012', 'WideWorldImporters', 'Northwind' )
ORDER BY [name]
FOR JSON PATH