🥇 Gabatarwa zuwa SQL Server 2017 Databases Graph

Kafin a fara karatun "MS SQL Server Developer" Mun shirya muku wani fassarar mai amfani.

Ma'ajin bayanai na hoto wata fasaha ce mai mahimmanci ga ƙwararrun bayanai. Ina ƙoƙarin bin sabbin abubuwa da sabbin fasahohi a cikin wannan yanki kuma bayan yin aiki tare da alaƙa da bayanan NoSQL, na ga cewa aikin bayanan jadawali yana ƙara ƙaruwa. Lokacin aiki tare da hadaddun bayanan matsayi, ba kawai bayanan gargajiya ba, har ma NoSQL ba su da tasiri. Sau da yawa, tare da karuwa a yawan matakan haɗin gwiwa da girman bayanan bayanai, akwai raguwa a cikin aiki. Kuma yayin da alaƙar ke ƙara haɓaka, adadin JOINs shima yana ƙaruwa.

Tabbas, a cikin ƙirar haɗin gwiwa akwai mafita don aiki tare da matsayi (misali, ta yin amfani da CTE masu maimaitawa), amma waɗannan har yanzu hanyoyin warwarewa ne. A lokaci guda, ayyuka na SQL Server jadawalai bayanan bayanai yana sauƙaƙa sarrafa matakan matsayi da yawa. Duk samfuran bayanai da tambayoyin sun sauƙaƙa, sabili da haka ana haɓaka ingancin su. Mahimmanci yana rage adadin lambar.

Rubutun bayanan hoto harshe ne mai bayyanawa don wakiltar hadaddun tsarin. An riga an yi amfani da wannan fasaha sosai a cikin masana'antar IT a yankuna kamar cibiyoyin sadarwar jama'a, tsarin hana zamba, nazarin hanyar sadarwar IT, shawarwarin zamantakewa, samfuri da shawarwarin abun ciki.

Ayyukan bayanan jadawali a cikin SQL Server ya dace da yanayin yanayi inda bayanan ke da haɗin kai sosai kuma yana da ƙayyadaddun alaƙa.

Samfurin bayanan hoto

jadawali saitin inti ne (kumburi, kumburi) da gefuna (dangantaka, baki). Ƙirar suna wakiltar ƙungiyoyi, kuma gefuna suna wakiltar hanyoyin haɗin gwiwa waɗanda halayensu zasu iya ƙunsar bayanai.

Rubutun bayanan jadawali yana ƙirƙira mahaɗan azaman jadawali kamar yadda aka ayyana a ka'idar jadawali. Tsarin bayanai sune gaba da gefuna. Sifofi sune kaddarorin madaidaici da gefuna. Haɗi haɗin kai ne.

Ba kamar sauran nau'ikan bayanai ba, ma'ajin bayanai na jadawali suna ba da fifiko ga dangantaka tsakanin ƙungiyoyi. Don haka, babu buƙatar ƙididdige alaƙa ta amfani da maɓallan ƙasashen waje ko ta wata hanya dabam. Yana yiwuwa a ƙirƙira ƙirƙira ƙirar bayanai masu rikitarwa ta amfani da tsattsauran ra'ayi kawai da gefuna.

A cikin duniyar yau, ƙirar ƙira tana buƙatar ƙarin dabaru na zamani. Don ƙirar alaƙa, SQL Server 2017 yana ba da damar bayanan bayanan hoto. Ana wakilta madaidaita da gefuna na jadawali azaman sabbin nau'ikan tebur: NODE da EDGE. Tambayoyin zane suna amfani da sabon aikin T-SQL mai suna MATCH(). Tun da an gina wannan aikin a cikin SQL Server 2017, ana iya amfani da shi a cikin bayanan da kuke ciki ba tare da buƙatar kowane juzu'i na bayanai ba.

Amfanin Samfurin Zane

A zamanin yau, kasuwanci da masu amfani suna buƙatar aikace-aikacen da ke aiki tare da ƙarin bayanai, yayin da ake tsammanin babban aiki da aminci. Misalin jadawali na bayanai yana ba da ingantacciyar hanya don sarrafa hadaddun alaƙa. Wannan hanyar tana magance matsaloli da yawa kuma tana taimakawa wajen samun sakamako a cikin mahallin da aka bayar.

Yana kama da yawancin aikace-aikacen za su amfana daga bayanan bayanan hoto a nan gaba.

Samfuran Bayanai: Daga Alaka zuwa Tsarin Zane

Alal misali:

Bari mu kalli misali na tsarin ƙungiya tare da matsayi na ma'aikata: ma'aikaci yana ba da rahoto ga manaja, manajan yana ba da rahoto ga babban manajan, da dai sauransu. Dangane da takamaiman kamfani, ana iya samun kowane adadin matakan a cikin wannan matsayi. Amma yayin da adadin matakan ke ƙaruwa, ƙididdige dangantaka a cikin bayanan alaƙa yana ƙara wahala. Yana da matukar wahala a iya tunanin matsayin ma'aikata, matsayi a cikin tallace-tallace ko haɗin gwiwar kafofin watsa labarun. Bari mu ga yadda SQL Graph zai iya magance matsalar sarrafa matakan matsayi daban-daban.

Don wannan misali, bari mu yi samfurin bayanai mai sauƙi. Ƙirƙiri tebur na ma'aikata EMP da id EMPNO da shafi M.G.R.A da ke nuna ID na shugaban (manajan) na ma'aikaci. Ana adana duk bayanan game da matsayi a cikin wannan tebur kuma ana iya tambayar su ta amfani da ginshiƙai EMPNO и M.G.R..

Hoton da ke gaba yana nuna samfurin org iri ɗaya tare da matakan gida guda huɗu a cikin sanannen tsari. Ma'aikata sune ƙarshen jadawali daga tebur EMP. Mahalarta "ma'aikaci" tana da alaƙa da kanta ta hanyar dangantakar "ta ƙaddamar" (ReportsTo). A cikin sharuɗɗan jadawali, hanyar haɗi ita ce gefen (EDGE) wanda ke haɗa nodes (NODEs) na ma'aikata.

Bari mu ƙirƙiri tebur na yau da kullun EMP kuma ƙara ƙima a can bisa ga zanen da ke sama.

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)

Hoton da ke ƙasa yana nuna ma'aikatan:

ma'aikaci tare da EMPNO 7369 yana ƙarƙashin 7902;
ma'aikaci tare da EMPNO 7902 ƙarƙashin 7566
ma'aikaci tare da EMPNO 7566 ƙarƙashin 7839

Yanzu bari mu dubi wakilcin bayanai iri ɗaya a cikin nau'i na jadawali. Kullin ma'aikaci yana da halaye da yawa kuma an haɗa shi da kansa ta hanyar "ƙaddamarwa" dangantaka (EmplReportsTo). EmplReportsTo shine sunan dangantakar.

Teburin gefen (EDGE) na iya samun halaye.

Ƙirƙiri tebur mai kumburi EmpNode

Maganar ƙirƙira kumburi abu ne mai sauƙi: ga magana KIRKIRA BAYA kara zuwa karshen 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;

Yanzu bari mu canza bayanan daga tebur na yau da kullun zuwa jadawali ɗaya. Na gaba INSERT saka bayanai daga tebur mai alaƙa EMP.

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

A cikin tebur na nodes a cikin wani shafi na musamman $node_id_* an adana ID mai masaukin baki azaman JSON. Sauran ginshiƙan wannan tebur sun ƙunshi halayen kumburin.

Ƙirƙiri Gefuna (EDGE)

Ƙirƙirar tebur na gefen yana kama da ƙirƙirar tebur mai kumburi, sai dai kalmar maɓalli AS EDGE.

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Yanzu bari mu ayyana alaƙa tsakanin ma'aikata ta amfani da ginshiƙai EMPNO и M.G.R.. Taswirar org yana nuna a sarari yadda ake rubutu 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);

Teburin gefen yana da ginshiƙai uku ta tsohuwa. Na farko, $edge_id - mai gano gefe a cikin hanyar JSON. Sauran biyun ($from_id и $to_id) wakiltar dangantaka tsakanin nodes. Bugu da ƙari, gefuna na iya samun ƙarin kaddarorin. A wurinmu, wannan Deptno.

Ra'ayin Tsari

A cikin tsarin duba sys.tables Akwai sabbin ginshiƙai guda biyu:

shine_ baki
shine_node

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

SMS

Abubuwan da ke da alaƙa da jadawali suna cikin babban fayil ɗin Tables. Alamar tebur ɗin node tana da alamar digo, da gunkin tebur ɗin gefen tare da da'irori biyu masu alaƙa (wanda yayi kama da tabarau).

MAGANAR MATCH

Magana MATCH daga CQL (Cypher Query Language). Wannan ita ce ingantacciyar hanya don bincika kaddarorin jadawali. CQL yana farawa da magana MATCH.

ginin kalma

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

misalai

Bari mu kalli wasu ‘yan misalai.

Tambayar da ke ƙasa tana nuna ma'aikatan da Smith da manajansa suka ba da rahoton.

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'

Tambayar mai zuwa shine don nemo ma'aikata da manajoji na biyu na Smith. Idan ka cire tayin INA, sa'an nan duk ma'aikata za a nuna a sakamakon.

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'

Kuma a ƙarshe, buƙatar ma'aikata da manajoji na mataki na uku.

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'

Yanzu bari mu canza alkibla don samun shugabannin 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)

ƙarshe

SQL Server 2017 ta kafa kanta a matsayin cikakkiyar mafita ta kasuwanci don buƙatun IT iri-iri. Sigar farko ta SQL Graph tana da ban sha'awa sosai. Duk da wasu iyakoki, an riga an sami isassun ayyuka don bincika yuwuwar jadawali.

Ayyukan zane na SQL an haɗa shi sosai cikin Injin SQL. Koyaya, kamar yadda aka riga aka ambata, SQL Server 2017 yana da iyakoki masu zuwa:

Babu goyon baya ga polymorphism.

Hanyoyin haɗin kai unidirection kawai ake tallafawa.
Gefen ba za su iya sabunta ginshiƙan $from_id da $to_id ta hanyar UPDATE.
Ba a goyan bayan rufewar, amma ana iya samun su ta amfani da CTEs.
Taimako mai iyaka don abubuwan OLTP In-Memory.
Tebura na ɗan lokaci (Table-Tsarin Tsarin lokaci), tebur na wucin gadi na gida da na duniya ba a tallafawa.
Ba za a iya bayyana nau'ikan tebur da masu canjin tebur azaman NODE ko EDGE ba.
Ba a goyan bayan tambayoyin tushen bayanai.
Babu wata hanya kai tsaye ko wani nau'in mayen (mayen maye) don canza tebur na yau da kullun zuwa tebur mai zane.
Babu GUI don nuna hotuna, amma ana iya amfani da Power BI.

Kara karantawa:

Yadda SQL Server ke amfani da matattarar bitmap

source: www.habr.com

Gabatarwa zuwa SQL Server 2017 Databases Graph