MS SQL Server: реалізація підтримки графової моделі даних
Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті
[ Про автора: ~30 років досвіду в розробці баз даних: розробка з нуля, супровід існуючих баз даних, міграція, вирішення проблем швидкодії, консалтинг, викладач курсів основ баз даних. Працював з MS SQL Server, Oracle, Sybase ASE, PostgreSQL, MySQL. Co-Leader of PASS Local Chapter (Data platform user group) у Львові, доповідач на конференціях SQLSaturday (Україна, з 2013 року), співорганізатор конференцій SQLSaturday у Львові]
З однієї сторони, підтримка MS SQL Server-ом графових баз даних це є декілька добавлених команд та розширень існуючих, з іншого — об’єднання під одним дахом реляційної і графової моделі даних. Обидві моделі мають міцне теоретичне підгрунтя, не один десяток років життя і стабільні класи задач, для вирішення яких вони використовуються.
Бази даних: трохи історії, класифікації, екосистема
В загальному, область зберігання і обробки даних більш стабільна ніж область доступу до даних.
Реляційні бази даних мають довгу історію. Починалося все з робіт Едгара Кодда (1969), потім були розробки компаній IBM (System R), Oracle. В 1974 Donald D. Chamberlin and Raymond F. Boyce розробили мову SQL. В 1979 була створена перша комерційна реляційна база даних з підтримкою SQL — Oracle. В 1986 вийша в світ перша версія стандарту ANSI SQL (остання версія стандарту ISO SQL датується 2019 роком). Далі почався активний розвиток SQL / RDBMS який в тій чи іншій мірі продовжеється зараз. В
Перша спробу посунути SQL «з трону» обробки даних була зі сторони об’єктних технологій. На піку розвитку об’єктих технологій в програмуванні вирішили примінити той самий підхід до обробки даних. Спроба вийша невдалою. Результатом стала інтеграція обєктного підходу і реляційних баз даних та розвиток ОРМ-ів (об’єктно — реляційних мапперів). Чисто об’єктні бази даних стали нішовим рішенням.
Друга хвиля боротьби за ринок обробки даних почалася з розвитком ітнетренту та епохою «NOSQL». Носіквельні бази існували і раніше (наприклад, багатовимірні аналітичні системи, MDX), але сам термін ще не був власним іменем (цікавий випадок класифікації, коли назва характеризує не то, чим є сімейство продуктів, а то чим воно не є). Власним іменем він став для документарних баз даних і баз даних типу ключ-значення. NOSQL не потіснив SQL з існуючих позиція, а зайняв нові «території» даних, котрі створила епоха інтернету; взяв на себе вирішення нових задач — масштабування і швидкодії. Нові проблеми вимагали нових підходів і інструментів. Поряд з «NOSQL» базами даних зявилися і інші не SQL-ні бази, але вони називались тим, чим вони є. Одними з них були графові бази даних. Навколо них не було такого шуму, як навколо «NOSQL», можливо через те, що вони не збиралися витісняти SQL бази даних з насиджених місць. Вони взяли на себе вирішення задач що стосувалися складності структури (моделі) даних, стали альтернативою, коли звязки між об’єктами є такими самими важливими як і самі об’єкти. Сьогодні бізнес для досягнення успіху більше орієнтується на зв’язки між даними, а не тільки на самі дані. А це територія графових баз.
Підходи SQL і класичного «NOSQL» до зберігання і обробки даних відрізняються абсолютно у всіх аспектах. Але є одна спільна деталь — як реляційні бази даних, так і документарні і ключ — значення працюють з чимось. Це щось може бути записом в таблиці, документом, значенням. По факту вони працють з сутностями. Сутності можуть мати різне представлення, але це не міняє їхньої природи. Графові бази даних працюють не стільки з сутостями, як з зв’язками між ними. На відміну від класичних «NOSQL» в них, як і в реляційних базах даних, теоретичним фундаментом є математична теорія — теорія графів.
Графові бази даних: структура, задачі
Структура графових баз даних проста. Є два типи об’єктів — вершини (або ноди), які представляють різні об’єкти, та ребра які репрезентують зв’язки між цими вершинами. Графова база даних надає можливості аналізувати зв’язки. Зв’язки між сутностями містять значний контекст, який втрачається при нормалізації даних в реляційних базах. З іншої сторони графові бази даних мають більш інтуїтивну модель даних. В реляційні моделі ми оперуєм множинами записів і менший акцент робиться на зв’язки між записами в межах множини. В класичному «NOSQL» модель спрощена по максимуму (ключ або документ) для досягнення кращого масштабування і швидкодії. Фактично графові бази даних добре працюють з складними моделями, дозволяють отримувати знання з зв’язків між даними. Сама теорія графів має практичні застосування в медицині, фізиці, соціології і, звичайно, в комп’ютерних науках. Типи задач, де використовуються графові бази даних: виявлення шахрайства, GDPR та інші типи регулювання, ІТ — управління мережевими даними і звичайно соціальні мережі та системи рекомендацій.
В архітектурі графових баз даних виділяються два основних підходи. Один з них — це RDF (ResourceDescription Framework) розроблений в кінці
CQL використовує схожий на SQL синтакс. Послідовність опцій в запиті є більш природньою, ніж в SQL — команда для виводу результатів знаходиться в кінці виразу (декларації). Приклад нотації для навігації: «(A) — [:LIKES]-> (B)» (зв’язок LIKES між А і В).
Structured Query Language | Cypher Query Language |
SELECT p.productname , p.unitprice FROM products pWHERE p.productname LIKE ’C%’ AND p.unitprice > 100; | MATCH (p:product) WHERE p.productname STARTS WITH «C» AND p.unitprice > 100RETURN p.productname , p.unitprice; |
SELECT DISTINCT c.companymame FROM customers cINNER JOIN orders o ON c.customerid = o.customeridINNER JOIN order_details od ON o.orderid = od.orderidINNER JOIN products p ON od.productid = p.productidWHERE p.productname = ’Chocolade’; | MATCH((p:product {productname:"Chocolade"})<-[:product]-(:order)<-[:purchased]-(c:Customer))RETURN DISTINCT c.companyname; |
На цьому загальна теорія завершена, далі розглянемо роботу з графовими даними в MS SQL Server.
MS SQL Server: реалізація підтримки графової моделі даних
Підтримка графових баз даних була добавлена у версії MS SQL Server 2017 року, нові команди добавлені в 2019. Для роботи з графовими даними в MS SQL Server реалізовано підхід labeled property graph. Що було додано: нові типи таблиць (NODE, EDGE (+ обмеження)), ключові слова MATCH (для підтримки пошуку за шаблонами та навігації по графу), FOR PATH, функція SHORTEST PATH, розширення агрегатних функцій WITHING GROUP (GRAPH PATH) Було внесено зміни в системні каталоги, додані нові системні функції.
Для прикладу розглянемо ієрархічну стурктуру. Вона проста, але дозволяє спробувати в роботі новий функціонал. Документація є на офіційному сайті компанії Майкрософт.
Структура даних (ієрархія)
Створюємо додаткову стейджингову таблицю з якої будуть завантажуватися дані в таблиці нових типів і завантажуємо в неї тестові дані. Таблиця містить зовнішній ключ сама на себе — найпростіша реалізація ієрархічної структури в реляційних базах даних. Досить проста для модифікації, але складніша для вибірки даних.
CREATE TABLE dbo.emp ( empno INTEGER NOT NULL PRIMARY KEY , ename VARCHAR(20) NULL , job VARCHAR(10) NULL , mgr INTEGER NULL REFERENCES dbo.emp (empno) , joindate DATETIME NULL , salary DECIMAL(7, 2) NULL , commission DECIMAL(7, 2) NULL , dno INTEGER NULL ) GO INSERT INTO dbo.emp (empno ,ename ,job ,mgr, joindate, salary, commission, dno) 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) GO
Є два варіанти запитів для роботи з такою структурою. Перший, коли нам відома глибина ієрархії. І цьому випадку використовується багаторазове з’єднання таблиці з собою. Запит нижче повертає підлеглих і їх менеджерів першого і другого рівнів. При зміні кількості рівнів ієрархії змінюєм кількість з’єднань таблиці.
SELECT e.empno , e.ename , m1.empno as mgrno_1 , m1.ename as mgrname_1 , m2.empno as mgrno_2 , m2.ename as mgrname_2 FROM emp e LEFT JOIN emp m1 ON e.mgr = m1.empno LEFT JOIN emp m2 ON m1.mgr = m2.empno;
Другий варіант — рекурсивні загальні табличні вирази. Наступний запит виводить список працівників і їхній рівень в ієрархії. Тут є свої обмеження по глибині ієрархії і швидкодії запитів.
;WITH dr (mgr, empno, ename, elevel) AS ( SELECT mgr, empno, ename, 0 FROM emp WHERE mgr IS NULL UNION ALL SELECT e.mgr, e.empno, e.ename, d.elevel + 1 FROM emp e INNER JOIN dr d ON e.mgr = d.empno ) SELECT mgr, empno, ename, elevel FROM dr ORDER BY elevel;
Створюємо нову структуру — таблички нових типів NODE та EDGE. Для створення табличок вершин до стандартної команди CREATE TABLE додаєм ключове слово ‘AS NODE’. При створенні таблиці автоматично створюються додаткові системні колонки graph_id — унікальний ідентифікатор графа (значення недоступні для перегляду) та $node_id — унікальний ідентифікатор вершини графа (модифікувати неможливо) та унікальний індекс по системній колонці graph_id. Команда для створення таблиці:
CREATE TABLE dbo.empnode ( empno INTEGER NOT NULL PRIMARY KEY , ename VARCHAR(10) NULL , salary DECIMAL(7, 2) NULL , commission DECIMAL(7, 2) NULL , dno INTEGER NULL ) AS NODE GO
Як виглядає структура таблиці в SSMS:
Для створення табличок ребер до стандартної команди для створення таблиці додаємо ключове слово ‘AS EDGE’. В цьому випадку можуть бути відхилення від стандартної команди CREATE TABLE — табличка ребер може не містити явно заданих колонок (це означає, що зв’язки прості і не мають ніяких властивостей). Також добавлений новий тип обмежень — CONNECTION, який контролює, які вершини з’єднуються між собою. При створенні таблиці автоматично створюються додаткові системні колонки — ідентифікатор графа і ребра та ідентифікатори вершин. Команди для створення / модифікації таблиці:
CREATE TABLE dbo.empreportsto AS EDGE GO ALTER TABLE dbo.empreportsto ADD CONSTRAINT ec_reportsto CONNECTION (dbo.empnode TO dbo.empnode) GO
Вигляд таблиці в SSMS:
Тепер можна подивитися до системних даних. Для таблиць добавлино дві нових властивості — is_edge, is_node. Ну і відповідно добавлені нові типи системних колонок. Запити до системних таблиць.
SELECT t.is_edge,t.is_node,* FROM sys.tables t WHERE name LIKE 'emp%' SELECT graph_type, graph_type_desc, * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('empreportsto')
Є структура — можна завантажити дані. Завантажуєм вершини. Тут стандартна команда, системні дані генеруються автоматично.
INSERT INTO dbo.empnode ( empno , ename , salary , commission , dno ) SELECT empno , ename , salary , commission , dno FROM dbo.emp GO
Завантаження ребер. Тут запит трохи складніший. Потрібно мати ідентифікатори вершин. Службові колонки не модифікуються, якщо нам потрібно модифікувати ребро (поапдейтити ідентифікатор вершини) — старий запис видаляється, створюється новий.
;WITH rs AS ( SELECT n.$node_id AS nodeid , n.empno , e.mgr FROM dbo.empnode n INNER JOIN dbo.emp e ON e.empno = n.empno ) INSERT INTO dbo.empreportsto ( $from_id , $to_id ) SELECT e.nodeid , m.nodeid FROM rs e INNER JOIN rs m ON e.empno = m.mgr
Дивимося на дані:
Таблиця вершин: ідентифікатор вершини (ідентифікатор графа — недоступний), решта колонок — властивості вершини.
$node_id_F65AF48E52D14E8CA055D38F7234FF34 | empno | ename | salary | commission | dno |
{"type":"node","schema":"dbo","table":"empnode","id":0} | 7369 | SMITH | 8000 | NULL | 2 |
{"type":"node","schema":"dbo","table":"empnode","id":1} | 7499 | ALLEN | 1600 | 3000 | 3 |
{"type":"node","schema":"dbo","table":"empnode","id":2} | 7521 | WARD | 1250 | 5000 | 3 |
{"type":"node","schema":"dbo","table":"empnode","id":3} | 7566 | JONES | 2975 | 50000 | 2 |
{"type":"node","schema":"dbo","table":"empnode","id":4} | 7654 | MARTIN | 1250 | 14000 | 3 |
{"type":"node","schema":"dbo","table":"empnode","id":5} | 7698 | BLAKE | 2850 | 12000 | 3 |
{"type":"node","schema":"dbo","table":"empnode","id":6} | 7782 | CLARK | 2450 | 13000 | 1 |
{"type":"node","schema":"dbo","table":"empnode","id":7} | 7788 | SCOTT | 3000 | 1200 | 2 |
{"type":"node","schema":"dbo","table":"empnode","id":8} | 7839 | KING | 5000 | 1456 | 1 |
{"type":"node","schema":"dbo","table":"empnode","id":9} | 7844 | TURNER | 1500 | 0 | 3 |
{"type":"node","schema":"dbo","table":"empnode","id":10} | 7876 | ADAMS | 1100 | 0 | 2 |
{"type":"node","schema":"dbo","table":"empnode","id":11} | 7900 | JAMES | 950 | 0 | 3 |
{"type":"node","schema":"dbo","table":"empnode","id":12} | 7902 | FORD | 3000 | 0 | 2 |
{"type":"node","schema":"dbo","table":"empnode","id":13} | 7934 | MILLER | 1300 | 0 | 1 |
Таблиця ребер: тут доступні для перегляду тільки ідентифікатори ребра та вершин, колонок властивостей немає (не створювали).
$edge_id_7471B5A4D0DA4194B083CCA33FA04E78 | $from_id_CFC1120B79EF4A878A96A8F9E7E4045C | $to_id_045ED1D64ABA4548A6BA4ACE93F79643 |
{"type":"edge","schema":"dbo","table":"empreportsto","id":0} | {"type":"node","schema":"dbo","table":"empnode","id":12} | {"type":"node","schema":"dbo","table":"empnode","id":0} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":1} | {"type":"node","schema":"dbo","table":"empnode","id":5} | {"type":"node","schema":"dbo","table":"empnode","id":1} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":2} | {"type":"node","schema":"dbo","table":"empnode","id":5} | {"type":"node","schema":"dbo","table":"empnode","id":2} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":3} | {"type":"node","schema":"dbo","table":"empnode","id":8} | {"type":"node","schema":"dbo","table":"empnode","id":3} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":4} | {"type":"node","schema":"dbo","table":"empnode","id":5} | {"type":"node","schema":"dbo","table":"empnode","id":4} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":5} | {"type":"node","schema":"dbo","table":"empnode","id":8} | {"type":"node","schema":"dbo","table":"empnode","id":5} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":6} | {"type":"node","schema":"dbo","table":"empnode","id":8} | {"type":"node","schema":"dbo","table":"empnode","id":6} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":7} | {"type":"node","schema":"dbo","table":"empnode","id":3} | {"type":"node","schema":"dbo","table":"empnode","id":7} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":8} | {"type":"node","schema":"dbo","table":"empnode","id":5} | {"type":"node","schema":"dbo","table":"empnode","id":9} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":9} | {"type":"node","schema":"dbo","table":"empnode","id":7} | {"type":"node","schema":"dbo","table":"empnode","id":10} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":10} | {"type":"node","schema":"dbo","table":"empnode","id":5} | {"type":"node","schema":"dbo","table":"empnode","id":11} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":11} | {"type":"node","schema":"dbo","table":"empnode","id":3} | {"type":"node","schema":"dbo","table":"empnode","id":12} |
{"type":"edge","schema":"dbo","table":"empreportsto","id":12} | {"type":"node","schema":"dbo","table":"empnode","id":6} | {"type":"node","schema":"dbo","table":"empnode","id«:13} |
Є дані в табличках, можна створювати запити. Розглянемо три запити, які демонструють основні можливості роботи з графовою базою даних. Це приклади базових конструкцій, на основі яких можна будувати складніші запити.
Перший запит повертає всіх підлеглих першого рівня, що звітують працівнику KING. Що бачимо нового в запиті: ключове слово MATCH (воно може бути тільки у WHERE), аргументами є шаблон: вершини, ребра і напрямок, в якому проходимо граф. У FROM перелічені таблиці (дві вершини і одне ребро між ними), порядок таблиць неважливий. Стиль з’єднання таблиць (FROM) схожий на синтаксис архіїчного декартового добутку. Синтаксис опції MATCH схожий на синтаксис Cypher (є відмінність у типі і наявності дужок).
SELECT e.empno AS mgr , e.ename AS mgrname , CONCAT('1-st: ', e1.empno, ':', e1.ename) AS subordanates FROM dbo.empnode e , dbo.empnode e1 , dbo.empreportsto m WHERE MATCH(e - (m) -> e1) AND e.ename = 'KING';
Результат:
mgr | mgrname | subordanates |
7839 | KING | |
7839 | KING | |
7839 | KING |
Другий запит повертає всіх менеджерів для працівника SMITH. Тут має бути відома глибина ієрархії. У FROM перераховуються таблиці ребер і вершин (в залежності від глибини ієрархії), в аргументі MATCHзмінений напрямок навігації і ланцюжок переходів довший. Цей приклад показує, що навігація може відбуватися в як від вершини до кінцевого вузла дерева, так і навпаки.
SELECT e.empno , e.ename , CONCAT( e1.empno,':', e1.ename, '<-' , e2.empno,':', e2.ename, '<-' , e3.empno,':', e3.ename ) AS managers FROM dbo.empnode e , dbo.empnode e1 , dbo.empreportsto m , dbo.empnode e2 , dbo.empreportsto m1 , dbo.empnode e3 , dbo.empreportsto m2 WHERE MATCH(e <- (m) - e1 <- (m1) - e2 <- (m2) - e3) AND e.ename = 'SMITH'
Результат:
empno | ename | managers |
7369 | SMITH | 7902:FORD<-7566:JONES<-7839:KING |
Ці два запити фактично продублювали існуючі можливості в іншому синтаксисі.
Третій запит — пошук найкоротшого шляху між вершинами (найкоротший шлях між працівником KINGі рештою працівників). Це вже складніша задача і її вирішення в рамках стандартного SQL не є тривіальним. Ієрархія не найкращий приклад для демонстрації цього функціоналу (є тільки один шлях і той найкоротший). Але для демонстрації синтаксису команди підійде. До ключового слова MATCH добавлена функція SHORTEST_PATH() (вона може використовуватися тільки як аргумент MATCH) У функції аргументом задано патерн з вершин і ребер, який має проходитися, поки не буде досягнутий результат. «+» говорить про те, що патерн повторюється 1 або більше разів для досягнення результату. Доступний також варіант «{1,n}» — повторюється 1 або n разів для досягнення результату (для прикладу, друзі, яких можна досягнути через 4 рукостискання в соціальній мережі). Для агрегатних функцій добавлена опція WITHING GROUP (GRAPHPATH). Опція FOR PATH добавлена до таблиць (вершина і ребро). У FROM задається патерн пошуку(вершина, ребро з ключовим словом FOR PATH і вершина). Агрегатна функція LAST_VALUE() може використовуватися тільки з функцією SHORTEST_PATH().
SELECT person2.ename AS mgrname , person2.empno AS mgr , STRING_AGG(CONCAT(person1.empno,':', person1.ename), '->') WITHIN GROUP (GRAPH PATH) AS subordanates , LAST_VALUE(person1.ename) WITHIN GROUP (GRAPH PATH) AS lastnode , COUNT(person1.ename) WITHIN GROUP (GRAPH PATH) AS levels FROM empnode FOR PATH person1 , empreportsto FOR PATH re , empnode person2 WHERE MATCH(SHORTEST_PATH(person2(-(re)->person1)+)) AND person2.ename = 'KING'
Результат:
mgrname | mgr | subordanates | lastnode | levels |
KING | 7839 | 7566:JONES | JONES | 1 |
KING | 7839 | 7698:BLAKE | BLAKE | 1 |
KING | 7839 | 7782:CLARK | CLARK | 1 |
KING | 7839 | 7698:BLAKE->7499:ALLEN | ALLEN | 2 |
KING | 7839 | 7698:BLAKE->7521:WARD | WARD | 2 |
KING | 7839 | 7698:BLAKE->7654:MARTIN | MARTIN | 2 |
KING | 7839 | 7566:JONES->7788:SCOTT | SCOTT | 2 |
KING | 7839 | 7698:BLAKE->7844:TURNER | TURNER | 2 |
KING | 7839 | 7698:BLAKE->7900:JAMES | JAMES | 2 |
KING | 7839 | 7566:JONES->7902:FORD | FORD | 2 |
KING | 7839 | 7782:CLARK->7934:MILLER | MILLER | 2 |
KING | 7839 | 7566:JONES->7902:FORD->7369:SMITH | SMITH | 3 |
KING | 7839 | 7566:JONES->7788:SCOTT->7876:ADAMS | ADAMS | 3 |
Під капотом використовується той самий рушій баз даних без додаткових компонент в плані запиту. Компанія Microsoft відома тим, що покращення на рівні рушія бази даних вносяться в наступних версіях після виходу нового функціоналу. Так як було з віконними функціями і пакетною обробкою записів для даних, що зберігаються по рядках (batch processing for row store). Існують певні обмеження для нових типів таблиць — вони не можуть бути тимчасовими, темпоральними таблицями, табличними змінними чи типами. Неможливо в одному запиті звертатися до графових об’єктів в різних базах даних (виконувати cross-database query).
Завершення
Вендори, які мають в своїх активах реляційні бази даних продовжують розвивати свої флагманські продукти. Їхній підхід — інтеграція під одним дахом різних структур даних і підходів до обробки цих даних. Такий аналог кухонного комбайну. Прикладами можуть бути Oracle з об’єктним розширенням, PostgreSQL та JSON і тепер MS SQL Server та Graph databases. Плюсом для роботи є те, що використовується той самий SQL. З таким підходом і SQL збагачується новими можливостями, які з часом з категорії розширень можуть перейти в стандарт.
12 коментарів
Додати коментар Підписатись на коментаріВідписатись від коментарів