Родительская иерархия верхнего уровня Synapse

У вас есть несколько вариантов. Я опишу здесь три:

1. если у вас есть близлежащая база данных SQL Azure и тома не слишком велики, представьте таблицу в этой базе данных SQL Azure с помощью CREATE EXTERNAL TABLE или просто используйте фабрику данных Azure (ADF) для копирования данных, выполните рекурсивный CTE, а затем используйте ADF для перенесите его обратно. В качестве альтернативы используйте какую-либо предварительную обработку, прежде чем эти данные попадут в ваш пул SQL.
2. Рекурсивный CTE - это просто тип цикла в конце дня, поэтому WHILE поддерживается в Synapse. Очевидно, что этот тип цикла не хорошо переносится в Synapse, поскольку он болтлив, но может быть вариантом для небольших объемов с низкой глубиной иерархии. Вы должны изучить компромисс между неэффективным использованием архитектуры MPP таким образом и написанием альтернативного кода.

Я закодировал пример варианта 2, выполнение которого заняло более 20 секунд всего для нескольких строк. Обычно я считаю это неприемлемым, но, как уже упоминалось, вы должны взвесить альтернативы:

IF OBJECT_ID('dbo.someHierarchy') IS NOT NULL
    DROP TABLE dbo.someHierarchy;

CREATE TABLE dbo.someHierarchy (
    code        INT NOT NULL,
    [name]      VARCHAR(50) NOT NULL,
    under       INT NOT NULL
)
WITH
    (
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
    );


INSERT INTO dbo.someHierarchy ( code, [name], under )
SELECT 1, 'National Sales Manager', 1
UNION ALL
SELECT 2, 'Regional Sales Manager', 1
UNION ALL
SELECT 3, 'Area Sales Manager', 2
UNION ALL
SELECT 4, 'Sales Manager', 3

INSERT INTO dbo.someHierarchy ( code, [name], under )
SELECT 5, 'Lead Bob', 5
UNION ALL
SELECT 6, 'Main Bob', 5
UNION ALL
SELECT 7, 'Junior Bob 1', 6
UNION ALL
SELECT 8, 'Junior Bob 2', 6

INSERT INTO dbo.someHierarchy ( code, [name], under )
SELECT 9, 'Jim - CEO', 9
UNION ALL
SELECT 10, 'Tim - CFO', 9
UNION ALL
SELECT 11, 'Rob - CIO', 9
UNION ALL
SELECT 12, 'Bob - VP', 10
UNION ALL
SELECT 13, 'Shon - Director', 12
UNION ALL
SELECT 14, 'Shane - VP', 11
UNION ALL
SELECT 15, 'Sheryl - VP', 11
UNION ALL
SELECT 16, 'Dan - Director', 15
UNION ALL
SELECT 17, 'Kim - Director', 15
UNION ALL
SELECT 18, 'Carlo - PM', 16
UNION ALL
SELECT 19, 'Monty - Sr Dev', 18
UNION ALL
SELECT 20, 'Chris - Sr Dev', 18




IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;

CREATE TABLE #tmp (
    xlevel          INT NOT NULL,
    code            INT NOT NULL,
    [name]          VARCHAR(50) NOT NULL,
    under           INT NOT NULL,
    ultimateParent  INT NOT NULL
    );
    

-- Insert first level; similar to anchor section of CTE
INSERT INTO #tmp ( xlevel, code, [name], under, ultimateParent )
SELECT 1 AS xlevel, code, [name], under, under AS ultimateParent
FROM dbo.someHierarchy
WHERE under = code;


-- Loop section
DECLARE @i INT = 1

WHILE EXISTS (
    SELECT * FROM dbo.someHierarchy h
    WHERE NOT EXISTS ( SELECT * FROM #tmp t WHERE h.code = t.code )
    )
BEGIN

    -- Insert subsequent levels; similar to recursive section of CTE
    INSERT INTO #tmp ( xlevel, code, [name], under, ultimateParent )
    SELECT t.xlevel + 1, h.code, h.[name], h.under, t.ultimateParent
    FROM #tmp t
        INNER JOIN dbo.someHierarchy h ON t.code = h.under
    WHERE h.under != h.code
      AND t.xlevel = @i;

    -- Increment counter
    SET @i += 1

    -- Loop guard
    IF @i > 99
    BEGIN
        RAISERROR( 'Too many loops!', 16, 1 )
        BREAK
    END
END

SELECT 'loop' s, *
FROM #tmp
ORDER BY code, xlevel;

Полученные результаты:

Условие состоит в том, что цикл WHILE EXISTS - это особенно дорогой способ сделать это, поэтому, возможно, есть более простой способ с вашими данными.

Третья альтернатива - использовать блокнот и библиотеку Azure Synapse, например GraphFrames, чтобы пройтись по иерархия. Существуют более простые способы сделать это, но я обнаружил, что метод Connected Components позволяет определить конечного менеджера. Одним из преимуществ использования GraphFrames является то, что он позволяет выполнять более сложные запросы к графу, например, с использованием мотивов, если это необходимо. В этом ноутбуке используется версия Spark (Scala):

Загрузите нужную версию библиотеки graphFrames в Spark:

%%configure -f
{
    "conf": {
        "spark.jars": "abfss://{yourContainer}@{yourDataLake}.dfs.core.windows.net/synapse/workspaces/{yourWorkspace}/sparkpools/{yourSparkpool}/libraries/graphframes-0.8.1-spark2.4-s_2.11.jar",
    }
}

Сконфигурируйте элементы с фигурными скобками для вашей среды.

Импортируйте соответствующие библиотеки:

import org.apache.spark.sql._
import org.apache.spark.sql.functions._
import org.graphframes._

Получите данные из выделенного пула SQL и назначьте их фрейму данных:

// Get a table from Synapse dedicated SQL pool, select / rename certain columns from it to vertices and edge dataframes
val df = spark.read.synapsesql("yourDB.dbo.someHierarchy")
val v = df.selectExpr("code AS id", "name AS empName", "under")

v.show

// Reformat the code/under relationship from the original table 
// NB Exclude because in graph terms these don't have an edge
val e = df.selectExpr("code AS src", "under AS dst", "'under' AS relationship").where("code != under")

e.show

Создайте фрейм графа из фреймов данных вершин и ребер:

// Create the graph frame
val g = GraphFrame(v, e)
print(g)

Установите контрольную точку для connectedComponents:

// The connected components adds a component id to each 'group'
// Set a checkpoint to start
sc.setCheckpointDir("/tmp/graphframes-azure-synapse-notebook")

Запустите алгоритм связанных компонентов с данными:

// Run connected components algorithm against the data
val cc = g.connectedComponents.run() // doesn't work on Spark 1.4
display(cc)

Присоединитесь к исходному фрейму данных вершин и результату алгоритма связанных компонентов и запишите его обратно в выделенный пул SQL Azure Synapse:

val writeDf = spark.sqlContext.sql ("select v.id, v.empName, v.under, cc.component AS ultimateManager from v inner join cc on v.id = cc.id")

//display(writeDf)
writeDf.write.synapsesql("someDb.dbo.someHierarchy2", Constants.INTERNAL)

Полученные результаты:

У меня есть чувство, что есть более простой способ добиться этого с помощью записных книжек, но я с нетерпением жду возможности увидеть некоторые альтернативы. Проголосуйте за элемент обратной связи для рекурсивных CTE в Synapse здесь:

https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/14876727-support-for-recursive-cte

Вы рассматривали или пробовали поместить данные в файл json и использовать поток данных Synapse для выравнивания иерархии для вас?