Azure数据流为身份关系创建/管理密钥 [英] Azure Data Flow creating / managing keys for identity relationships

问题描述

好奇地发现最好的方法是通过ADF生成关系身份.

Curious to find out what the best way is to generate relationship identities through ADF.

现在，我正在使用没有任何身份信息的JSON数据.然后，将这些数据转换为具有关系(1..n等)的多个数据库接收器表.由于某些目标接收器表受到FK限制，因此必须一次建立"这些关系.

Right now, I'm consuming JSON data that does not have any identity information. This data is then transformed into multiple database sink tables with relationships (1..n, etc.). Due to FK constraints on some of the destination sink tables, these relationships need to be "built up" one at a time.

这种方法似乎有点笨拙，所以我想看看是否还有其他我不知道的选项.

This approach seems a bit kludgy, so I'm looking to see if there are other options that I'm not aware of.

请注意，我需要为每个插入内容包含代理密钥生成.如果不执行此操作，则基于输出数据库架构，将收到无法插入PK空值"错误.

Note that I need to include the Surrogate key generation for each insert. If I do not do this, based on output database schema, I'll get a 'cannot insert PK null' error.

还要注意，我为每个接收器打开/关闭了 IDENTITY_INSERT .

Also note that I turn IDENTITY_INSERT ON/OFF for each sink.

推荐答案

我倾向于采用更多的ELT方法，并在Azure SQL DB中使用本机JSON能力，即 OPENJSON .您可以使用ADF(例如，存储的Proc活动)将JSON放入Azure SQL DB的表中，然后调用另一个存储的proc来处理JSON，如下所示:

I would tend to take more of an ELT approach and use the native JSON abilites in Azure SQL DB, ie OPENJSON. You could land the JSON in a table in Azure SQL DB using ADF (eg a Stored Proc activity) and then call another stored proc to process the JSON, something like this:

-- Setup
DROP TABLE IF EXISTS #tmp
DROP TABLE IF EXISTS import.City;
DROP TABLE IF EXISTS import.Region;
DROP TABLE IF EXISTS import.Country;
GO

DROP SCHEMA IF EXISTS import 
GO

CREATE SCHEMA import
    CREATE TABLE Country ( CountryKey INT IDENTITY PRIMARY KEY, CountryName VARCHAR(50) NOT NULL UNIQUE )
    CREATE TABLE Region ( RegionKey INT IDENTITY PRIMARY KEY, CountryKey INT NOT NULL FOREIGN KEY REFERENCES import.Country, RegionName VARCHAR(50) NOT NULL UNIQUE )
    CREATE TABLE City ( CityKey INT IDENTITY(100,1) PRIMARY KEY, RegionKey INT NOT NULL FOREIGN KEY REFERENCES import.Region, CityName VARCHAR(50) NOT NULL UNIQUE )
GO


DECLARE @json NVARCHAR(MAX) = '{
   "Cities": [
      {
         "Country": "England",
         "Region": "Greater London",
         "City": "London"
      },
      {
         "Country": "England",
         "Region": "West Midlands",
         "City": "Birmingham"
      },
      {
         "Country": "England",
         "Region": "Greater Manchester",
         "City": "Manchester"
      },
      {
         "Country": "Scotland",
         "Region": "Lothian",
         "City": "Edinburgh"
      }
   ]
}'


SELECT *
INTO #tmp
FROM OPENJSON( @json, '$.Cities' )
WITH
(
    Country     VARCHAR(50),
    Region      VARCHAR(50),
    City        VARCHAR(50)
)
GO


-- Add the Country first (has no foreign keys)
INSERT INTO import.Country ( CountryName )
SELECT DISTINCT Country
FROM #tmp s
WHERE NOT EXISTS ( SELECT * FROM import.Country t WHERE s.Country = t.CountryName )


-- Add the Region next including Country FK
INSERT INTO import.Region ( CountryKey, RegionName )
SELECT t.CountryKey, s.Region
FROM #tmp s
    INNER JOIN import.Country t ON s.Country = t.CountryName


-- Now add the City with FKs
INSERT INTO import.City ( RegionKey, CityName )
SELECT r.RegionKey, s.City
FROM #tmp s
    INNER JOIN import.Country c ON s.Country = c.CountryName
    INNER JOIN import.Region r ON s.Region = r.RegionName
        AND c.CountryKey = r.CountryKey


SELECT * FROM import.City;
SELECT * FROM import.Region;
SELECT * FROM import.Country;

这是一个简单的测试脚本，旨在演示该思想，并且应端对端运行，但不是生产代码.

This is a simple test script designed to show the idea and should run end-to-end but it is not production code.

这篇关于Azure数据流为身份关系创建/管理密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！