数据库管理-第188期 23ai:怎么用PGQL创建图（20240511）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Associate: Database（Oracle与MySQL）
PostgreSQL ACE Partner
10年数据库行业经验，现主要从事数据库服务工作
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP、认证技术专家、年度墨力之星，ITPUB认证专家、专家百人团成员，OCM讲师，PolarDB开源社区技术顾问，OceanBase观察团成员
圈内拥有“总监”、“保安”、“国产数据库最大敌人”等称号，非著名社恐（社交恐怖分子）
公众号：胖头鱼的鱼缸；CSDN：胖头鱼的鱼缸（尹海文）；墨天轮：胖头鱼的鱼缸；ITPUB：yhw1809。
除授权转载并标明出处外，均为“非法”抄袭

苦逼的周六补班，还是得吐槽一下这个调休调班策略。
PGQL，可不是Postgres Query Language，PGQL的全称为Property Graph Query Language，属性图查询语言，其介绍可以查阅网页：https://pgql-lang.org/。

1 PGQL创建属性图

CREATE PROPERTY GRAPH语句使用PGQL方式会创建一个包含图元数据的类似于视图的对象。这个图可以使用PGQL查询。
PGQL属性图直接在存在于关系型表中数据上创建。这些图存储在数据库表中，他们有自己的SCHEMA。
PGQL属性图的一个主要的好处就是所有数据库表的更新会立即在图中展示。

1.1 PGQL属性图的元数据表

当通过CREATE PROPERTY GRAPH创建PGQL属性图时，元数据表会在本SCHEMA下创建。对应的元数据表信息参考下图（这里就不翻译了）：

1.2 创建一个PGQL属性图

CREATE PROPERTY GRAPH语句的OPTIONS子句可以指定创建属性图的执行模式。

• 可以使用RDBMS Java API或通过SQLcl（就是之前说过的sql）来创建PGQL属性图
• 可以通过图可视化工具或SQLcl来查询PGQL属性图

CREATE PROPERTY GRAPH students_network
  VERTEX TABLES (
    persons KEY (person_id)
      LABEL person
        PROPERTIES (person_id, name, birthdate AS dob),
    university KEY (id)
  )
  EDGE TABLES (
    friends
      KEY (friendship_id)
      SOURCE KEY (person_a) REFERENCES persons(person_id)
      DESTINATION KEY (person_b) REFERENCES persons(person_id)
      PROPERTIES (friendship_id, meeting_date),
    student_of
      SOURCE KEY (s_person_id) REFERENCES persons(person_id)
      DESTINATION KEY (s_univ_id) REFERENCES university(id)
      PROPERTIES (subject)
  ) OPTIONS (PG_PGQL);

毕竟PGQL和SQL不同，这个创建语句无法在sqlplus下执行：

而23ai Free版本不包含SQLcl工具，所以这时候前面部署的PGX就发挥作用了：

这时候就可以查询这个图的元数据表了：

SELECT * FROM STUDENTS_NETWORK_ELEM_TABLE$;
SELECT * FROM STUDENTS_NETWORK_LABEL$;
SELECT * FROM STUDENTS_NETWORK_PROPERTY$;
SELECT * FROM STUDENTS_NETWORK_KEY$;
SELECT * FROM STUDENTS_NETWORK_SRC_DST_KEY$;

1.3 获取PGQL属性图的元数据

可以使用PGQL查询从一个名为PROPERTY_GRAPH_METADATA的内建图中查询PGQL属性图的元数据，图的结构如下：

查询这个图：

以下描述了上面元数据图的设计：

PROPERTY_GRAPH -[:HAS_VERTEX_TABLE]-> VERTEX_TABLE
               -[:HAS_EDGE_TABLE]-> EDGE_TABLE
 
VERTEX_TABLE -[:HAS_KEY_COLUMN]-> KEY_COLUMN
             -[:HAS_LABEL]-> LABEL
 
EDGE_TABLE -[:HAS_KEY_COLUMN]-> KEY_COLUMN
           -[:HAS_LABEL]-> LABEL
           -[:HAS_SOURCE_TABLE]-> VERTEX_TABLE
           -[:HAS_DESTINATION_TABLE]-> VERTEX_TABLE
 
LABEL -[:HAS_PROPERTY]-> PROPERTY

在PGQL查询中使用PROPERTY_GRAPH_METADATA时，请注意以下几点，这非常重要：

• 首次尝试在PGQL查询中访问PROPERTYGRAPH_METADATA图时，会自动创建并更新该图
• PROPERTYGRAPH_METADATA图类似于PGQL属性图，并且有自己的一组元数据表来描述其结构。PROPERTYGRAPH_METADATA的图形数据也存储在下表中列出的数据库对象中（请不要变更删除这些表和数据）：
  
• 当使用Java API运行PGQL查询时，必须禁用JDBC连接上的自动提交（conn.setAutoCommit(false)）。这样可以确保自动创建PROPERTYGRAPH_METADATA图。

2 PGQL属性图

由于没有SQLcl，这里操作只能用PGX来实现：

SELECT e
FROM MATCH ()-[e]->() ON STUDENTS_NETWORK
LIMIT 100

这里可以以表或图的方式展示数据，也可以拖动任意点。对底层表任意修改可以即时展示：

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('Scott', 1.75, to_date('20/05/1985', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Assistant"}');

INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 22, to_date('10/06/2001', 'DD/MM/YYYY'));

commit;

3 官方示例演示

3.1 插入数据

CREATE TABLE bank_accounts(id NUMBER, name VARCHAR2(10));
CREATE TABLE bank_txns(from_acct_id NUMBER, to_acct_id NUMBER, description VARCHAR2(10), amount NUMBER);

使用sqlldr通过csv文件导入数据，将对应文件拷贝到数据库服务器
csv文件位置为pgx服务器/opt/oracle/graph/data/bank_graph/

scp -r /opt/oracle/graph/data/bank_graph/ oracle@10.10.10.230:~

cd ~/bank_graph
vim bank_nodes.ctl
load data
infile '/home/oracle/bank_graph/bank_nodes.csv'
into table bank_accounts
fields terminated by "," optionally enclosed by '"'
( id, name )

sqlldr ec/ec@freepdb1 CONTROL=bank_nodes.ctl

vim bank_edges_amt.ctl
load data
infile '/home/oracle/bank_graph/bank_edges_amt.csv'
into table bank_txns
fields terminated by "," optionally enclosed by '"'
(from_acct_id,to_acct_id,description,amount)

sqlldr ec/ec@freepdb1 CONTROL=bank_edges_amt.ctl

ALTER TABLE bank_accounts ADD PRIMARY KEY (id);
ALTER TABLE bank_txns ADD txn_id NUMBER;
UPDATE bank_txns SET txn_id = ROWNUM;
COMMIT;
ALTER TABLE bank_txns ADD PRIMARY KEY (txn_id);
ALTER TABLE bank_txns MODIFY from_acct_id REFERENCES bank_accounts(id);
ALTER TABLE bank_txns MODIFY to_acct_id REFERENCES bank_accounts(id);

3.2 创建PGQL属性图

CREATE PROPERTY GRAPH bank_graph 
VERTEX TABLES (
 BANK_ACCOUNTS AS ACCOUNTS KEY (ID)
  LABEL ACCOUNTS
   PROPERTIES (ID, NAME)
)
EDGE TABLES (
 BANK_TXNS AS TRANSFERS
 KEY (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT)
  SOURCE KEY (FROM_ACCT_ID) REFERENCES ACCOUNTS (ID)
  DESTINATION KEY (TO_ACCT_ID) REFERENCES ACCOUNTS (ID)
   LABEL TRANSFERS
    PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION)
) OPTIONS (PG_PGQL);

3.3 通过PGQL属性图查询图数据

SELECT e
FROM MATCH ()-[e]->() ON BANK_GRAPH
LIMIT 100

3.4 通过PGQL属性图修改数据

先查询需要修改的内容：

SELECT e
FROM MATCH (v1 IS ACCOUNTS)-[e IS TRANSFERS]->(v2 IS ACCOUNTS)
ON BANK_GRAPH
WHERE v1.id=178 and v2.id=268

select * from bank_txns where from_acct_id=178 and to_acct_id=268;

然后使用PGQL修改数据:

UPDATE e
SET (e.AMOUNT=30000)
FROM MATCH (v1 IS ACCOUNTS)-[e IS TRANSFERS]->(v2 IS ACCOUNTS)
ON BANK_GRAPH
WHERE v1.id=178 and v2.id=268

这里看到底层数据已经变更了：

对应的图数据也变化了：

3.5 通过PGQL属性图删除数据

删除操作和上面修改操作类似：

SELECT e
FROM MATCH (v1)-[e]->(v2)
ON BANK_GRAPH
WHERE v1.id=1000

select * from bank_txns where from_acct_id=1000;

DELETE e FROM
MATCH (v1)-[e]->(v2)
ON BANK_GRAPH
WHERE v1.id=1000

查看底层数据和图输出，对应数据已被删除：

其余的PGX操作则可以参考https://docs.oracle.com/en/database/oracle/property-graph/24.2/spgdg/using-graph-visualization-application.html
这里还需要注意PGX会话token有超时时间如果使用过程中无响应或报错，则就需要重新登录或者调整PGX配置
在虚拟机中使用PGX，使用挂起功能恢复后可能导致PGX无法将DML正常推送到底层数据库，需要重启PGX应用

总结

本期展示了PGQL属性图，其实和SQL属性图在语法层面大差不差，可以通过PGX进行展示操作。
老规矩，知道写了些啥。