Graph Example¶
이 예제는 retail dataset에서 AkasicDB 확장을 로드하고, graph를 정의한 뒤 graph query를 실행하는 workflow를 보여줍니다.
확장 로드¶
- AkasicDB의 모든 타입과 함수를 데이터베이스에 등록합니다.
- 이 과정은 관리자 권한을 가진 계정으로만 수행할 수 있습니다.
테이블 생성¶
CREATE TABLE store (
s_id integer PRIMARY KEY,
name varchar(50),
manager varchar(40)
);
CREATE TABLE item (
i_no integer PRIMARY KEY,
name varchar(50),
price decimal(7,2)
);
CREATE TABLE customer (
c_id integer PRIMARY KEY,
first_name varchar(20),
last_name varchar(30)
);
CREATE TABLE orders (
c_id integer,
i_no integer,
s_id integer
);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (c_id) REFERENCES customer(c_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_item
FOREIGN KEY (i_no) REFERENCES item(i_no);
ALTER TABLE orders ADD CONSTRAINT fk_orders_store
FOREIGN KEY (s_id) REFERENCES store(s_id);
- 상점, 상품, 소비자, 주문 테이블로 유통 데이터를 구성합니다.
튜토리얼 데이터 생성¶
테이블 데이터 생성 SQL
INSERT INTO store (s_id, name, manager)
VALUES
(1, 'ought', 'William Ward'),
(2, 'able', 'Scott Smith'),
(3, 'able', 'Scott Smith'),
(4, 'ese', 'Edwin Adams'),
(5, 'anti', 'Edwin Adams');
INSERT INTO item (i_no, name, price)
VALUES
(1, 'ought', 27.02),
(2, 'able', 1.12),
(3, 'pri', 7.11),
(4, 'ese', 1.35),
(5, 'anti', 4.00),
(6, 'cally', 0.85),
(7, 'ation', 9.94),
(8, 'eing', 2.76),
(9, 'n st', 4.46),
(10, 'barought', 8.94),
(11, 'oughtought', 54.87),
(12, 'ableought', 6.54),
(13, 'priought', 8.76),
(14, 'eseought', 1.85),
(15, 'antiought', 2.57),
(16, 'callyought', 0.31),
(17, 'ationought', 6.49),
(18, 'eingought', 0.87),
(19, 'n stought', 10.61),
(20, 'barable', 29.35);
INSERT INTO customer (c_id, first_name, last_name)
VALUES
(1, 'Javier', 'Lewis'),
(2, 'Amy', 'Moses'),
(3, 'Latisha', 'Hamilton'),
(4, 'Michael', 'White'),
(5, 'Robert', 'Moran'),
(6, 'Brunilda', 'Sharp'),
(7, 'Fonda', 'Wiles'),
(8, 'Ollie', 'Shipman'),
(9, 'Karl', 'Gilbert'),
(10, 'Albert', 'Brunson'),
(11, 'Betty', 'Williams'),
(12, 'Margaret', 'Farias'),
(13, 'Rosalinda', 'Grimes'),
(14, 'Jack', 'Wilcox'),
(15, 'Margie', 'Browning'),
(16, 'Lee', 'Stovall'),
(17, 'Brad', 'Lynch'),
(18, 'Andre', 'Moore'),
(19, 'Stanton', 'Dallas'),
(20, 'Naomi', 'Barnett'),
(21, 'Victor', 'Martinez'),
(22, 'Paul', 'Morris'),
(23, 'Nancy', 'Mccormick'),
(24, 'Monique', 'Baker'),
(25, 'Shawn', 'Prather'),
(26, 'Edith', 'Hernandez'),
(27, 'Margaret', 'Collins'),
(28, 'Pamela', 'Luna'),
(29, 'William', 'Craig'),
(30, 'Kenneth', 'Wood');
INSERT INTO orders (c_id, i_no, s_id)
VALUES
(9,7,4),
(11,14,1),
(16,12,3),
(17,17,3),
(6,14,4),
(18,14,4),
(27,14,4),
(12,17,4),
(28,8,1),
(22,5,2),
(23,14,4),
(24,14,1),
(24,3,5),
(4,8,3),
(10,8,4),
(4,18,3),
(28,15,5),
(12,2,2),
(15,18,1),
(12,20,1),
(16,10,3),
(17,1,4),
(24,19,3),
(6,10,5),
(30,6,1),
(19,12,4),
(21,5,4),
(7,17,3),
(26,9,2),
(25,16,3),
(13,9,5),
(17,15,1),
(29,7,2),
(25,4,2),
(5,6,3),
(18,9,2),
(10,1,4),
(22,16,4),
(12,10,5),
(20,15,3),
(19,7,2),
(10,11,1),
(3,6,2),
(17,12,5),
(18,5,5),
(19,18,5),
(23,8,1),
(6,13,1),
(9,18,1),
(3,5,1),
(17,6,4),
(9,11,4),
(14,3,4),
(23,12,1),
(25,6,3),
(7,10,4),
(14,4,5),
(11,4,1),
(9,10,5),
(17,16,3),
(11,7,4),
(26,4,2),
(7,18,2),
(19,2,5),
(22,18,2),
(26,7,3),
(27,3,5),
(8,17,4),
(26,2,4),
(6,6,5),
(27,6,3),
(22,20,3),
(27,6,2),
(30,2,5),
(13,18,3),
(6,18,2),
(28,7,1),
(27,12,5),
(5,8,1),
(27,11,3),
(9,10,5),
(27,2,2),
(8,18,5),
(22,5,2),
(27,16,5),
(29,9,5),
(30,2,4),
(30,8,5),
(29,3,4),
(30,20,4),
(22,5,2),
(4,1,1),
(29,8,3),
(19,6,2),
(15,17,5),
(15,15,3),
(6,20,1),
(14,15,4),
(18,13,3),
(18,15,4);
- 준비된 위의 SQL 스크립트를 이용해 유통 데이터의 각 테이블을 채웁니다.
그래프 정의 및 생성¶
SELECT akasicdb.define_graph('retail_graph');
SELECT akasicdb.define_vertex(
'retail_graph',
'v_item',
ARRAY['i_no integer', 'name varchar(50)', 'price decimal(7,2)'],
'item'
);
SELECT akasicdb.define_vertex(
'retail_graph',
'v_customer',
ARRAY['c_id integer', 'first_name varchar(20)', 'last_name varchar(30)'],
'customer'
);
SELECT akasicdb.define_vertex(
'retail_graph',
'v_store',
ARRAY['s_id integer', 'name varchar(50)'],
'store',
'SELECT s_id, name FROM store'
);
SELECT akasicdb.define_edge(
'retail_graph',
'buy',
'v_customer', 'v_item',
null,
'SELECT null FROM customer c, orders o, item i '
'WHERE c.c_id = o.c_id AND i.i_no = o.i_no',
'customer c', 'item i'
);
SELECT akasicdb.define_edge(
'retail_graph',
'sell',
'v_store', 'v_item',
null,
'SELECT null FROM store s, orders o, item i '
'WHERE s.s_id = o.s_id AND i.i_no = o.i_no',
'store s', 'item i'
);
SELECT akasicdb.create_graph('retail_graph');
- 유통 데이터로부터 그래프
retail_graph를 정의, 생성합니다. - 상품, 소비자, 상점 테이블로부터 각각 정점
v_item,v_customer,v_store를 정의합니다. - 상품과 이를 판매하는 상점 간의 관계, 상품과 이를 구매한 소비자 간의 관계를 각각 간선
sell,buy로 정의합니다.
그래프 질의¶
-- 소비자 'Margaret Farias'가 구매한 상품의 이름과 가격을 검색
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c:v_customer)-[:buy]->(i:v_item)
WHERE c.first_name = 'Margaret'
AND c.last_name = 'Farias'
RETURN
i.name as product_name,
i.price as price
$$) as (
product_name varchar,
price decimal
);
-- 소비자 'Margaret Farias'와 같은 상품을 구매한 다른 소비자의 이름을 검색
SELECT *
FROM akasicdb.cypher('retail_graph', $$
MATCH (c1:v_customer)-[:buy]->(i:v_item)<-[:buy]-(c2:v_customer)
WHERE c1.first_name = 'Margaret'
AND c1.last_name = 'Farias'
AND c1.vertex_id <> c2.vertex_id
RETURN
c2.first_name as first_name,
c2.last_name as last_name
$$) as (
first_name varchar,
last_name varchar
);
이 예제에서는 AkasicDB에서 관계형 데이터로부터 그래프를 생성하고 그래프 질의를 실행하는 방법을 살펴보았습니다.