🥇Введення до графових баз даних SQL Server 2017

Напередодні старту курсу "MS SQL Server Developer" підготували вам ще один корисний переклад.

Графові бази даних - це важлива технологія для фахівців з баз даних. Я намагаюся стежити за інноваціями та новими технологіями в цій галузі і після роботи з реляційними та NoSQL базами даних я бачу, що роль графових баз даних стає все більше. У роботі зі складними ієрархічними даними малоефективні як традиційні бази даних, а й NoSQL. Часто зі збільшенням кількості рівнів зв'язків і розміру бази спостерігається зниження продуктивності. А з ускладненням взаємозв'язків зростає і кількість JOIN.

Звичайно, в реляційній моделі є рішення для роботи з ієрархіями (наприклад, за допомогою рекурсивних CTE), але все одно залишається обхідними шляхами. При цьому функціонал графових баз даних SQL Server дозволяє з легкістю обробляти кілька рівнів ієрархії. Спрощуються як модель даних, і запити, отже, збільшується їх ефективність. Значно скорочується обсяг коду.

Графові бази даних — це виразна мова уявлення складних систем. Ця технологія вже досить широко використовується в ІТ-індустрії в таких галузях, як соціальні мережі, антифрод-системи, аналіз ІТ-мереж, соціальні рекомендації, рекомендації щодо продуктів та контенту.

Функціонал графових баз даних у SQL Server підходить для сценаріїв, у яких дані сильно пов'язані між собою та мають чітко визначені зв'язки.

Графова модель даних

Граф - це безліч вершин (вузлів, node) і ребер (взаємозв'язків, edge). Вершини являють собою сутності, а ребра — зв'язки, в атрибутах яких може міститися інформація.

Графа база даних моделює сутності як графа у вигляді, як і визначено теорії графів. Структури даних – це вершини та ребра. Атрибути - це властивості вершин та ребер. Зв'язок – це з'єднання вершин.

На відміну від інших моделей даних, у графових базах даних у пріоритеті взаємозв'язку між сутностями. Тому не потрібно обчислювати зв'язки за допомогою зовнішніх ключів чи іншими способами. Можна створювати складні моделі даних, використовуючи лише абстракції вершин та ребер.

У світі моделювання взаємозв'язків вимагає дедалі складніших методик. Для моделювання зв'язків SQL Server 2017 пропонує можливості графових баз даних. Вершини та ребра графа представляються у вигляді нових типів таблиць: NODE та EDGE. Для запитів до графа використовується нова функція T-SQL під назвою MATCH(). Так як цей функціонал вбудований в SQL Server 2017, його можна використовувати у ваших існуючих базах даних без необхідності будь-якої їх конвертації.

Користь графової моделі

В даний час бізнес та користувачі вимагають додатків, які працюють все з великим і більшим обсягом даних, очікуючи при цьому високої продуктивності та надійності. Надання даних у вигляді графа пропонує зручні засоби для обробки складних зв'язків. Цей підхід дозволяє вирішити багато проблем та допомагає отримати результати в рамках заданого контексту.

Зважаючи на все, у майбутньому багато додатків зможуть виграти від використання графових баз даних.

Моделювання даних: від реляційної моделі до графової

Приклад

Давайте розглянемо приклад організаційної структури з ієрархією співробітників: співробітник підпорядковується менеджеру, менеджер - старшому менеджеру і таке інше. Залежно від конкретної компанії у цій ієрархії може бути будь-яка кількість рівнів. Але зі збільшенням кількості рівнів обчислення зв'язків у реляційній базі даних стає дедалі складнішим. У ній досить складно уявити ієрархію співробітників, ієрархію у маркетингу чи зв'язку у соціальних мережах. Давайте подивимося, як можна за допомогою SQL Graph вирішити проблему з обробкою різних рівнів ієрархії.

Для цього зробимо просту модель даних. Створимо таблицю співробітників ЕМІ з ідентифікатором EMPNO та колонкою М.Г.Р.що вказує на ідентифікатор керівника (менеджера) співробітника. Вся інформація про ієрархію зберігається в цій таблиці і може бути запрошена за допомогою колонок EMPNO и М.Г.Р..

На наступній діаграмі зображено так само модель оргструктури з чотирма рівнями вкладеності в більш звичному вигляді. Співробітники – це вершини графа з таблиці ЕМІ. Сутність «співробітник» пов'язана сама з собою зв'язком «підпорядковується» (ReportsTo). У термінах графа зв'язок — це ребро (EDGE), яке пов'язує вузли (NODE) співробітників.

Давайте створимо звичайну таблицю ЕМІ і додамо туди значення відповідно до наведеної вище діаграми.

CREATE TABLE EMP
(EMPNO INT NOT NULL,
ENAME VARCHAR(20),
JOB VARCHAR(10),
MGR INT,
JOINDATE DATETIME,
SALARY DECIMAL(7, 2),
COMMISIION DECIMAL(7, 2),
DNO INT)
 
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '02-MAR-1970', 8000, NULL, 2),
(7499, 'ALLEN', 'SALESMAN', 7698, '20-MAR-1971', 1600, 3000, 3),
(7521, 'WARD', 'SALESMAN', 7698, '07-FEB-1983', 1250, 5000, 3),
(7566, 'JONES', 'MANAGER', 7839, '02-JUN-1961', 2975, 50000, 2),
(7654, 'MARTIN', 'SALESMAN', 7698, '28-FEB-1971', 1250, 14000, 3),
(7698, 'BLAKE', 'MANAGER', 7839, '01-JAN-1988', 2850, 12000, 3),
(7782, 'CLARK', 'MANAGER', 7839, '09-APR-1971', 2450, 13000, 1),
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, 1200, 2),
(7839, 'KING', 'PRESIDENT', NULL, '17-JUL-1971', 5000, 1456, 1),
(7844, 'TURNER', 'SALESMAN', 7698, '08-AUG-1971', 1500, 0, 3),
(7876, 'ADAMS', 'CLERK', 7788, '12-MAR-1973', 1100, 0, 2),
(7900, 'JAMES', 'CLERK', 7698, '03-NOV-1971', 950, 0, 3),
(7902, 'FORD', 'ANALYST', 7566, '04-MAR-1961', 3000, 0, 2),
(7934, 'MILLER', 'CLERK', 7782, '21-JAN-1972', 1300, 0, 1)

На наведеному нижче малюнку показані співробітники:

співробітник з EMPNO 7369 підпорядковується 7902;
співробітник з EMPNO 7902 підпорядковується 7566
співробітник з EMPNO 7566 підпорядковується 7839

Тепер давайте подивимося на подання тих самих даних у вигляді графа. Вершина EMPLOYEE має кілька атрибутів і пов'язана сама із собою зв'язком «підпорядковується» (EmplReportsTo). EmplReportsTo - це назва зв'язку.

У таблиці ребер (EDGE) також можуть бути атрибути.

Створимо таблицю вузлів EmpNode

Синтаксис створення вузла досить простий: до виразу CREATE TABLE в кінець додається "AS NODE".

CREATE TABLE dbo.EmpNode(
ID Int Identity(1,1),
EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
MGR NUMERIC(4),
DNO INT
) AS NODE;

Тепер перетворимо дані зі звичайної таблиці на графову. Наступний INSERT вставляє дані з реляційної таблиці ЕМІ.

INSERT INTO EmpNode(EMPNO,ENAME,MGR,DNO) select empno,ename,MGR,dno from emp

У таблиці вузлів у спеціальній колонці $node_id_* зберігається ідентифікатор вузла як JSON. У решті стовпчиків цієї таблиці знаходяться атрибути вузла.

Створюємо ребра (EDGE)

Створення таблиці ребер дуже схоже створення таблиці вузлів, крім те, що використовується ключове слово "AS EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Тепер визначимо зв'язки між співробітниками, використовуючи стовпці EMPNO и М.Г.Р.. По діаграмі оргструктури добре видно, як написати INSERT.

INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 1),
   	(SELECT $node_id FROM EmpNode WHERE id = 13),20);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 2),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 3),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10)
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 4),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 5),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 6),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 7),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 8),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 9),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 10),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 11),
   	(SELECT $node_id FROM EmpNode WHERE id = 8),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 12),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 13),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 14),
   	(SELECT $node_id FROM EmpNode WHERE id = 7),30);

Таблиця ребер за замовчуванням складається із трьох стовпців. Перший, $edge_id - Ідентифікатор ребра у вигляді JSON. Два інших ($from_id и $to_id) представляють зв'язок між вузлами. Крім того, ребра можуть мати додаткові властивості. У нашому випадку це Deptno.

Системні уявлення

У системному поданні sys.tables з'явилося дві нові колонки:

is_edge
is_node

SELECT t.is_edge,t.is_node,*
FROM sys.tables t
WHERE name like 'emp%'

ssms

Об'єкти, пов'язані з графами, розміщуються у папці Graph Tables. Іконка таблиці вузлів позначена точкою, а таблиці ребер двома зв'язаними колами (що трохи схоже на окуляри).

Вираз MATCH

вираз MATCH взято з CQL (Cypher Query Language). Це ефективний спосіб запиту до властивостей графа. CQL починається з виразу MATCH.

Синтаксис

MATCH (<graph_search_pattern>)
 
<graph_search_pattern>::=
    {<node_alias> {
                 	{ <-( <edge_alias> )- }
               	| { -( <edge_alias> )-> }
             	<node_alias>
             	}
 	}
 	[ { AND } { ( <graph_search_pattern> ) } ]
 	[ ,...n ]
 
<node_alias> ::=
    node_table_name | node_alias
 
<edge_alias> ::=
    edge_table_name | edge_alias

Приклади

Погляньмо на кілька прикладів.

Наведений нижче запит відображає співробітників, яким підпорядковується Smith та його менеджер.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR
FROM
    empnode e, empnode e1, empReportsTo m
WHERE
    MATCH(e-(m)->e1)
and e.ENAME='SMITH'

Наступний запит призначений для пошуку співробітників та менеджерів другого рівня для Smith. Якщо прибрати пропозицію ДЕ, то в результаті будуть відображатися всі працівники.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2
WHERE
    MATCH(e-(m)->e1-(m1)->e2)
and e.ENAME='SMITH'

І, нарешті, запит для співробітників та менеджерів третього рівня.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR,E3.EMPNO,e3.ENAME,E3.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2, empReportsTo M2, empnode e3
WHERE
    MATCH(e-(m)->e1-(m1)->e2-(m2)->e3)
and e.ENAME='SMITH'

Тепер давайте змінимо напрямок, щоб отримати начальників Smith'а.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR,E3.EMPNO,e3.ENAME,E3.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2, empReportsTo M2, empnode e3
WHERE
    MATCH(e<-(m)-e1<-(m1)-e2<-(m2)-e3)

Висновок

SQL Server 2017 зарекомендував себе як повноцінне корпоративне рішення для різноманітних ІТ-задач бізнесу. Перша версія SQL Graph дуже перспективна. Навіть незважаючи на деякі обмеження, вже зараз є достатньо функціоналу для вивчення можливостей графів.

Функціонал SQL Graph повністю інтегрований у SQL Engine. Однак, як уже було сказано, SQL Server 2017 має такі обмеження:

Немає підтримки поліморфізму.

Підтримуються лише односпрямовані зв'язки.
У ребер не можна оновлювати стовпці $from_id та $to_id через UPDATE.
Не підтримуються транзитивні замикання (transitive closure), але їх можна отримати за допомогою CTE.
Обмежено підтримку об'єктів In-Memory OLTP.
Не підтримуються темпоральні таблиці (System-Versioned Temporal Table), тимчасові локальні та глобальні таблиці.
Табличні типи та табличні змінні не можуть бути оголошені як NODE чи EDGE.
Не підтримуються запити між базами даних (cross-database queries).
Немає прямого способу або якогось майстра (wizard) для перетворення звичайних таблиць на графові.
Для відображення графів немає GUI, але можна використовувати Power BI.

Читати ще:

Як SQL Server використовує bitmap-фільтри

Джерело: habr.com

Введення в графові бази даних SQL Server 2017