Какой инструмент генерирует диаграммы из иерархических данных SQL Server?
Существует ли инструмент, который работает с SQL Server для создания древовидных диаграмм из иерархической модели данных?
Я работаю с большой географической иерархией и хотел бы ее визуализировать.
Вот пример.
У меня есть таблица NodeHierarchy, которая хранит иерархические отношения между узлами. Каждая строка в таблице представляет узел. Каждый узел, кроме одного, имеет родительский узел. Узел, у которого нет родителя, является корневым в иерархии.
Вот как я создаю свою таблицу:
CREATE DATABASE HierarchyTest;
GO
USE HierarchyTest;
GO
CREATE TABLE NodeHierarchy (
PK_NodeID INT NOT NULL
CONSTRAINT PK_NodeHierarchy PRIMARY KEY,
FK_ParentNodeID INT NULL
CONSTRAINT FK_NodeHierarchy_NodeHierarchy FOREIGN KEY
REFERENCES NodeHierarchy(PK_NodeID),
Name NVARCHAR(255) NOT NULL
);
У меня есть пример иерархии шотландских городов и мест. Шотландия является корнем иерархии. Потомки Шотландии - это города и места. В этой иерархии родитель "содержит" ребенка, поэтому мы говорим, например, "Курганы находятся в Глазго, а Глазго в Шотландии".
Этот оператор заполняет таблицу NodeHierachy примерами данных:
INSERT INTO NodeHierarchy(PK_NodeID, FK_ParentNodeID, Name)
VALUES
(1, NULL, N'Scotland'),
(2, 1, N'Glasgow'),
(3, 1, N'Edinburgh'),
(4, 1, N'St Andrews'),
(5, 2, N'The Barrowlands'),
(6, 2, N'The Cathouse'),
(7, 2, N'Carling Academy'),
(8, 2, N'SECC'),
(9, 2, N'King Tut''s Wah-Wah Hut'),
(10, 3, N'Henry''s Cellar Bar'),
(11, 3, N'The Bongo Club'),
(12, 3, N'Sneaky Pete''s'),
(13, 3, N'The Picture House'),
(14, 3, N'Potterrow'),
(15, 4, N'Aikman''s'),
(16, 4, N'The Union'),
(17, 4, N'Castle Sands');
Выход из SELECT * FROM NodeHierarchy;:
PK_NodeID FK_ParentNodeID Name
----------- --------------- ---------------------------------
1 NULL Scotland
2 1 Glasgow
3 1 Edinburgh
4 1 St Andrews
5 2 The Barrowlands
6 2 The Cathouse
7 2 Carling Academy
8 2 SECC
9 2 King Tut's Wah-Wah Hut
10 3 Henry's Cellar Bar
11 3 The Bongo Club
12 3 Sneaky Pete's
13 3 The Picture House
14 3 Potterrow
15 4 Aikman's
16 4 The Union
17 4 Castle Sands
(17 row(s) affected)
В Freemind я нарисовал эту эквивалентную диаграмму:
Какой инструмент может сделать это для меня с минимальными ручными усилиями?
РЕДАКТИРОВАТЬ: Первоначально я сказал, что я хотел визуализировать "все или часть" иерархии. Размещенное здесь решение безоговорочно визуализирует всю иерархию. Это хорошо для небольшой примерной иерархии, но для более крупной может быть более полезно визуализировать только ее часть.
Поскольку я не уточнил, что я имел в виду под "частью", я удалил это из вопроса. Я спросил о частичной визуализации в другом вопросе.
2 ответа
Я исследовал ответы в ответе Cade Roux и разработал решение, используя GraphViz.
Чтобы понять GraphViz, сначала я прочитал эту вводную статью и документацию по вызову командной строки. После успешного создания графиков из списка примеров кода в статье я чувствовал уверенность в работе со своими собственными данными.
Как предположил Кейд, лучший способ выучить язык DOT в GraphViz - это написать его самому. Я изучил примеры статьи (листинги 1, 2 и 6), а затем придумал это venues.gv описать мои собственные данные:
digraph Venues
{
N1[label = "Scotland"];
N2[label = "Glasgow"];
N3[label = "Edinburgh"];
N4[label = "St Andrews"];
N5[label = "The Barrowlands"];
N6[label = "The Cathouse"];
N7[label = "Carling Academy"];
N8[label = "SECC"];
N9[label = "King Tut's Wah-Wah Hut"];
N10[label = "Henry's Cellar Bar"];
N11[label = "The Bongo Club"];
N12[label = "Sneaky Pete's"];
N13[label = "The Picture House"];
N14[label = "Potterrow"];
N15[label = "Aikman's"];
N16[label = "The Union"];
N17[label = "Castle Sands"];
N1 -> N2;
N1 -> N3;
N1 -> N4;
N2 -> N5;
N2 -> N6;
N2 -> N7;
N2 -> N8;
N2 -> N9;
N3 -> N10;
N3 -> N11;
N3 -> N12;
N3 -> N13;
N3 -> N14;
N4 -> N15;
N4 -> N16;
N4 -> N17;
}
Я кормил это circoпросто одна из многих команд рисования графика, которые являются частью GraphViz, и получили приятный вывод:
Выход из circo -Tpng venues.gv -o venues.png:
Файл GraphViz состоит из двух блоков. Один блок объявляет метку для каждого узла, а другой блок объявляет ребра графа.
Чтобы предоставить данные для каждого из этих блоков, я создал вид NodeHierarchy,
Это представление предоставляет данные для объявления меток для узлов:
CREATE VIEW NodeLabels (
Node,
Label
)
AS
SELECT
PK_NodeID AS Node,
Name AS Label
FROM
NodeHierarchy;
Это представление предоставляет данные для объявления ребер между узлами:
CREATE VIEW Edges (
Parent,
Child
)
AS
SELECT
FK_ParentNodeID AS Parent,
PK_NodeID AS Child
FROM NodeHierarchy
WHERE FK_ParentNodeID IS NOT NULL;
Этот скрипт Powershell называется generate-graph.ps1 выбирает данные из представлений, преобразует их во входные данные GraphViz и передает их в circo создать визуализацию полной иерархии, как показано выше:
"digraph Venues {" + (
Invoke-Sqlcmd -Query "SELECT * FROM HierarchyTest.dbo.NodeLabels" |
ForEach-Object {"N" + $_.Node + "[label = """ + $_.Label + """];"}
) + (
Invoke-Sqlcmd -Query "SELECT * FROM HierarchyTest.dbo.Edges" |
ForEach-Object {"N" + $_.Parent + " -> N" + $_.Child + ";"}
) +
"}" | circo -Tpng -o venues.png
Скрипт должен быть запущен в sqlps вместо powershell таким образом Invoke-Sqlcmd Командлет доступен. Рабочий каталог по умолчанию sqlps является SQLSERVERпоэтому я должен указать диск при запуске скрипта через sqlps,
Это команда, которую я использую для создания графика, подобного приведенному выше:
sqlps C:.\generate-graph.ps1
Это выводит файл с именем venues.png в рабочем каталоге C.
Это решение Powershell выглядит немного не элегантно, но оно делает то, что мне нужно. Более опытный программист Powershell может придумать что-нибудь более чистое.
Экспортируйте и запустите его через GraphViz - вам даже не нужно создавать иерархию (просто экспортируйте узлы и ребра) - просто назначьте имена узлов, которые являются уникальными, на основе столбца NodeID и используйте те же имена узлов на ребрах.
Если вы хотите что-то интерактивное, у Microsoft есть библиотека Automatic Graph Layout, которую можно использовать из.NET.
Вот введение в GraphViz.
То, что вы собираетесь сделать, это вывести файл DOT, экспортировав SQL с помощью скрипта, подобного следующему: http://data.stackexchange.com/stackru/q/109885/ который будет проходить через GraphViz и генерировать вашу картинку.
Синтаксис DOT относительно прост - вы можете сначала написать его вручную, а затем сгенерировать его из SQL и просто вставить его в файл или что-то еще (например,.NET или PowerShell), который читает наборы SQL и генерирует файл.
Вы можете автоматизировать это с помощью SSIS. Я сделал пакет, который записал DOT-файл, запустил graphviz и ежедневно сохранял снимок нашей системы в виде графики.