图数据建模从关系型数据到图数

举报
xcc-2022 发表于 2022/08/07 21:04:38 2022/08/07
【摘要】 1、目标  介绍将从PostgreSQL(RDBMS)导出的数据导入Neo4j(GraphDB),即将关系数据库模式建模,使之形成图。  预备知识:熟悉图模型并安装neo4j服务2、导RDBMS数据到Neo4j2.1、RDBMS数据集  用到的数据集是NorthWind dataset(点击下载),该数据库的E-R图如下:2.2、构建图模型  当将E-R模型转换成图模型时,需要遵守如下规则:...

1、目标

  介绍将从PostgreSQL(RDBMS)导出的数据导入Neo4j(GraphDB),即将关系数据库模式建模,使之形成图。
  预备知识:熟悉图模型并安装neo4j服务

2、导RDBMS数据到Neo4j

2.1、RDBMS数据集

  用到的数据集是NorthWind dataset(点击下载),该数据库的E-R图如下:

2.2、构建图模型

  当将E-R模型转换成图模型时,需要遵守如下规则:
  (1)一行仅表示一个节点(node)
  (2)一个表名对应一个Label名
  NorthWind dataset表示成图模型的一个局部示意图如下:
  
  #图模型和E-R模型的区别
  (1)前者的节点和边没有空值,而后者的字段存在空值;(2)前者描述“关系”(通过边)更加详尽,而且边可以添加元数据;(3)前者对于描述网络关系更加标准化。

2.3、将数据导出成CSV

  通过copy和export将PostgreSQL中的部分表导出:

COPY (SELECT * FROM customers) TO '/tmp/customers.csv' WITH CSV header;

COPY (SELECT * FROM suppliers) TO '/tmp/suppliers.csv' WITH CSV header;

COPY (SELECT * FROM products)  TO '/tmp/products.csv' WITH CSV header;

COPY (SELECT * FROM employees) TO '/tmp/employees.csv' WITH CSV header;

COPY (SELECT * FROM categories) TO '/tmp/categories.csv' WITH CSV header;

COPY (SELECT * FROM orders
      LEFT OUTER JOIN order_details ON order_details.OrderID = orders.OrderID) TO '/tmp/orders.csv' WITH CSV header;

2.4、基于Cypher导入数据

  通过Cypher的LOAD CSV实现数据导入
  (1)创建节点
  import_csv.cypher如下:

// Create customers
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:customers.csv" AS row
CREATE (:Customer {companyName: row.CompanyName, customerID: row.CustomerID, fax: row.Fax, phone: row.Phone});

// Create products
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:products.csv" AS row
CREATE (:Product {productName: row.ProductName, productID: row.ProductID, unitPrice: toFloat(row.UnitPrice)});

// Create suppliers
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:suppliers.csv" AS row
CREATE (:Supplier {companyName: row.CompanyName, supplierID: row.SupplierID});

// Create employees
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:employees.csv" AS row
CREATE (:Employee {employeeID:row.EmployeeID,  firstName: row.FirstName, lastName: row.LastName, title: row.Title});

// Create categories
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:categories.csv" AS row
CREATE (:Category {categoryID: row.CategoryID, categoryName: row.CategoryName, description: row.Description});

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:orders.csv" AS row
MERGE (order:Order {orderID: row.OrderID}) ON CREATE SET order.shipName =  row.ShipName;

  (2)创建索引
  对刚创建的节点建立索引,以便在下一步创建边关系的时候能快速检索到各点。

CREATE INDEX ON :Product(productID);

CREATE INDEX ON :Product(productName);

CREATE INDEX ON :Category(categoryID);

CREATE INDEX ON :Employee(employeeID);

CREATE INDEX ON :Supplier(supplierID);

CREATE INDEX ON :Customer(customerID);

CREATE INDEX ON :Customer(customerName);

  (3)创建边关系
  首先创建products和employees的边关系。

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:orders.csv" AS row
MATCH (order:Order {orderID: row.OrderID})
MATCH (product:Product {productID: row.ProductID})
MERGE (order)-[pu:PRODUCT]->(product)
ON CREATE SET pu.unitPrice = toFloat(row.UnitPrice), pu.quantity = toFloat(row.Quantity);

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:orders.csv" AS row
MATCH (order:Order {orderID: row.OrderID})
MATCH (employee:Employee {employeeID: row.EmployeeID})
MERGE (employee)-[:SOLD]->(order);

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:orders.csv" AS row
MATCH (order:Order {orderID: row.OrderID})
MATCH (customer:Customer {customerID: row.CustomerID})
MERGE (customer)-[:PURCHASED]->(order);

  其次,创建products, suppliers, and categories的边关系.

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:products.csv" AS row
MATCH (product:Product {productID: row.ProductID})
MATCH (supplier:Supplier {supplierID: row.SupplierID})
MERGE (supplier)-[:SUPPLIES]->(product);

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:products.csv" AS row
MATCH (product:Product {productID: row.ProductID})
MATCH (category:Category {categoryID: row.CategoryID})
MERGE (product)-[:PART_OF]->(category);

  然后,创建employees之间的“REPORTS_TO”关系。

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:employees.csv" AS row
MATCH (employee:Employee {employeeID: row.EmployeeID})
MATCH (manager:Employee {employeeID: row.ReportsTo})
MERGE (employee)-[:REPORTS_TO]->(manager);

  最后,为优化查询速度,在orders上创建唯一性约束:

CREATE CONSTRAINT ON (o:Order) ASSERT o.orderID IS UNIQUE;

  此外,也可以通过运行整个脚本一次性完成所上述工作:

bin/neo4j-shell -path northwind.db -file import_csv.cypher.

  (4)最终成果
  
  附:(1)Northwind SQL, CSV and Cypher data files (zip)
  (2)Tool:SQL to Neo4j Import

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200