数据库管理-第187期 23ai:怎么用SQL创建图（20240510）

数据库管理-第187期 23ai:怎么用SQL创建图（20240510）

作者：胖头鱼的鱼缸（尹海文）
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。
除授权转载并标明出处外，均为“非法”抄袭

1 安装PGX

安装Graph Server，PGX，本次部署版本为24.2.0，需要到这个网址去下载：

https://edelivery.oracle.com/

这里下载最新的Oracle Graph Server and Client 24.2.0（全部加起来有点大）:

重命名一下并解压看看：



还需要单独部署一台OracleLinux 8的主机，本次操作的两台机器如下：

主机名	IP地址	操作系统	角色
23ai-free	10.10.10.230	OracleLinux 9.3	DB 23ai Free
pgx	10.10.10.80	OracleLinux 8.9	PGX App

1.1 数据库配置对应用户

这里继续使用之前用于JSON关系二元性视图实验的用户ec，密码为ec，已授予connect和resource权限：

--这两个role权限是23ai新增，之前版本需要手工创建：https://docs.oracle.com/en/database/oracle/property-graph/24.2/spgdg/user-authentication-and-authorization.html
GRANT GRAPH_DEVELOPER TO ec;
GRANT GRAPH_ADMINISTRATOR to ec;

为了偷懒把两个权限都赋予了。

1.2 使用RPM包安装Graph Server

dnf -y install libgfortran
# install Oracle JDK 11
rpm -i jdk-11.0.23_linux-x64_bin.rpm

# install graph
rpm -i oracle-graph-24.2.0.x86_64.rpm

# add operating system users allowed to use the server installation to the operating system group oraclegraph
# usermod -a -G oraclegraph <graphuser>
useradd graph
usermod -a -G oraclegraph graph
passwd graph

# As <graphuser>, configure the server by modifying the files /etc/oracle/graph/pgx.conf
vim /etc/oracle/graph/pgx.conf
# ...
# "pgx_realm": {
#   "implementation": "oracle.pg.identity.DatabaseRealm",
#   "options": {
#     "jdbc_url": "jdbc:oracle:thin:@myhost:1521/myservice",
#     "token_expiration_seconds": 3600,
# ...
...
"pgx_realm": {
  "implementation": "oracle.pg.identity.DatabaseRealm",
  "options": {
    "jdbc_url": "jdbc:oracle:thin:@10.10.10.230:1521/freepdb1",
    "token_expiration_seconds": 3600,
...

# disable tls
vim /etc/oracle/graph/server.conf
{
  "port": 7007,
  "enable_tls": false,
  "enable_client_authentication": false,
  "working_dir": "/opt/oracle/graph/pgx/tmp_data"
}

systemctl start pgx

1.3 安装Oracle Graph Client

su - graph
unzip Oracle\ Graph\ Client\ 24.2.0.zip
unzip oracle-graph-client-24.2.0.zip

用过下面的命令可以连接到PGX：

cd oracle-graph-client-24.2.0/bin/
./opg4j --base_url https://10.10.10.80:7007 --username ec

也可以通过PGX安装包含的client进行连接：

/opt/oracle/graph/bin/opg4j --base_url http://10.10.10.80:7007 --username e

1.4 访问PGX页面

从Oracle Graph Server 23.3开始，使用RPM安装的PGX是包含了图形界面，可以不用部署Oracle Graph Webapps（相关部署可以参看对应官方文档：https://docs.oracle.com/en/database/oracle/property-graph/24.2/spgdg/deploying-oracle-graph-server-web-server.html），现在可以通过10.10.10.80:7007/ui来访问PGX：

输入数据库对应账户名/密码：ec/ec即可访问：

2 SQL Property Graph

以在任何基于SQL的接口（如SQLDeveloper、SQLPLUS或SQLcl）中使用SQL属性图，也可以从使用JDBC的Java程序中使用。

• 从已经schema中存在的数据库对象创建一个SQL属性图，比如：
  – 表（包含创建SQL属性图的限制中列出的一些例外）
  – 物化视图
  – 外部表
  – 任何上面数据库对象的同义词
• 为SQL属性图创建同义词
• Revalidate一个属性图
• 在一个SQL属性图上运行图模式匹配查询
• 删除一个SQL属性图

2.1 创建SQL属性图

使用CREATE PROPERTY GRAPH DDL语句，可以直接在Oracle数据库中创建属性图对象。
这里我们用一个官方文档的示例来演示创建SQL属性图，首先需要创建示例数据：

CREATE TABLE university (
    id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name VARCHAR2(10),
    CONSTRAINT u_pk PRIMARY KEY (id));

INSERT INTO university (name) VALUES ('ABC');
INSERT INTO university (name) VALUES ('XYZ');

CREATE TABLE persons (
     person_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT
     BY 1),
     name VARCHAR2(10),
     birthdate DATE,
     height FLOAT DEFAULT ON NULL 0,
     hr_data JSON,
     CONSTRAINT person_pk PRIMARY KEY (person_id)
   );

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('John', 1.80, to_date('13/06/1963', 'DD/MM/YYYY'), '{"department":"IT","role":"Software Developer"}');

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('Mary', 1.65, to_date('25/09/1982', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Manager"}');

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('Bob', 1.75, to_date('11/03/1966', 'DD/MM/YYYY'), '{"department":"IT","role":"Technical Consultant"}');

INSERT INTO persons (name, height, birthdate, hr_data)
       VALUES ('Alice', 1.70, to_date('01/02/1987', 'DD/MM/YYYY'), '{"department":"HR","role":"HR Assistant"}');

CREATE TABLE student_of (
      s_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
      s_univ_id NUMBER,
      s_person_id NUMBER,
      subject VARCHAR2(10),
      CONSTRAINT stud_pk PRIMARY KEY (s_id),
      CONSTRAINT stud_fk_person FOREIGN KEY (s_person_id) REFERENCES persons(person_id),
      CONSTRAINT stud_fk_univ FOREIGN KEY (s_univ_id) REFERENCES university(id)
    );

INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (1,1,'Arts');
INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (1,3,'Music');
INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (2,2,'Math');
INSERT INTO student_of(s_univ_id, s_person_id,subject) VALUES (2,4,'Science');

CREATE TABLE friends (
    friendship_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    person_a NUMBER,
    person_b NUMBER,
    meeting_date DATE,
    CONSTRAINT fk_person_a_id FOREIGN KEY (person_a) REFERENCES persons(person_id),
    CONSTRAINT fk_person_b_id FOREIGN KEY (person_b) REFERENCES persons(person_id),
    CONSTRAINT fs_pk PRIMARY KEY (friendship_id)
);

INSERT INTO friends (person_a, person_b, meeting_date) VALUES (1, 3, to_date('01/09/2000', 'DD/MM/YYYY'));
INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 4, to_date('19/09/2000', 'DD/MM/YYYY'));
INSERT INTO friends (person_a, person_b, meeting_date) VALUES (2, 1, to_date('19/09/2000', 'DD/MM/YYYY'));
INSERT INTO friends (person_a, person_b, meeting_date) VALUES (3, 2, to_date('10/07/2001', 'DD/MM/YYYY'));

commit;

这里创建了四张表：university表存储大学名称信息，person表存储人员的信息，student_of表存储大学、人员以及专业关联的信息，friends表则存储包含第一次见面信息的人与人之间的关联信息。这里可以注意到每张表都有主键，后两张作为关联信息表的非主键列与前两张基础信息表的主键是有外键约束关系的。我们依托这四张表创建下面的SQL属性图：

语句如下：

CREATE PROPERTY GRAPH students_graph
  VERTEX TABLES (
    persons KEY (person_id)
      LABEL person
        PROPERTIES (person_id, name, birthdate AS dob)
      LABEL person_ht
        PROPERTIES (height),
    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)
  );

在执行时，前面的示例创建了一个SQL属性图对象，该对象使用schema中的表来定义其图元素表。请注意，创建新的SQL属性图对象只会存储属性图元数据，并且不会将数据从底层数据库对象复制到图元素表中。这意味着在查询SQL属性图时，所有的图查询都是对数据库中的当前图数据执行的。如果您有足够的权限，还可以指定另一个schema来包含SQL属性图。
示例中的图定义创建了一个图，该图包括：

• 两个点图元素表：
  • person：表有一个唯一键person_id，与两个标签（LEBEL）关联：
    • person：这个标签将person_id、name和birthdate作为属性公开
    • person_ht：这个标签仅把height作为属性公开
  • university：这个表的标签是隐式推断的，默认情况下，基础数据库表的所有可见列都显示为属性。
• 两个边图元素表：
  • friends: 这个边表引用person作为源点表和目标点表的基础数据库表。边表的源键和目标键（person_a和person_b）分别对应于源点表和目标点表的唯一键。边表的标签是根据图元素表的名称（在本例中为friends）自动推断的，并将friendship_id和meeting_date作为属性公开。
  • student_of：这个边表分别引用person和university作为源点表和目标点表的基础数据库表。边表的源键字和目标键字（s_person_id和s_univ_id）分别对应于源点表和目的点表的唯一键。边缘表的标签是根据图形元素表的名称（在本例中为student_of）自动推断的，并将subject公开为属性。

2.2 关于点和边图元素表

从底层数据库对象定义的SQL属性图的点和边存储在图元素表中。图元素表可以试点表也可以是边表。
Vertex graph element table（点图元素表）：

• 一个点图元素表通过VERTEX TABLES子句定义
• 点表中的每一行对应图中的一个点
• 一个点元素表名称独立于底层数据库对象名称
• 默认情况下，点图元素表的名称和底层数据库对象名称一致
• 点元素表名称在图中必须是唯一的。为了可以是源自于同一个底层数据库对象可以在SQL属性图中定义为多个图形元素表，必须使用AS子句指定多个元素表的名称

Edge graph element table（边图元素表）：

• 一个边图元素表通过EDGE TABLES子句定义
• 它使用引用各个点表的source和destination关键字指定源点表和目标点表之间的直接关系
• 边表中的每一行对应图中的一个边
• 一个边元素表名称独立于底层数据库对象名称
• 默认情况下，边图元素表的名称和底层数据库对象名称一致
• 边素表名称在图中必须是唯一的。一个边表名字不能和点表和其他边表名称一致

2.3 关于点和边表建

在SQL属性图中的每个点表和边表都必须有一个键，用以标识SQL属性图中一个唯一的点或一个边。键是由底层表中的一列或多列定义的。基于从底层表的一个现有主键或唯一约束来隐式推断这个键、键是唯一的。
然而需要注意的是，使用唯一约束的键列需要用ENFORCED MODE来创建图，否则使用TRUSTED MODE来使用没有唯一约束的键列。
点和边表的键可以使用下面任意的内建的数据类型列来定义：

• VARCHAR2
• NVARCHAR2
• NUMBER
• BINARY_FLOAT
• BINARY_DOUBLE
• CHAR
• NCHAR
• DATE
• INTERVAL (both YEAR TO MONTH and DAY TO SECOND)
• TIMESTAMP

注意，TIMESTAMP WITH TIME ZONE是不被支持的。
Vertex Table Key（点表键）：

• 默认情况下，点表的键自动从底层数据库对象的一个单一的主键或者唯一键约束定义。如果二者同时存在，则使用主键约束优先于唯一键约束
• 如果点表的键是自动基于一个单一的唯一键推断的，那么这个列除了UNIQUE以外也必须是NOT NULL
• 如果底层数据库对象没有包含唯一约束来强制控制数据唯一性，那么必须明确在VERTEX TABLES子句中显式定义的KEY的子子句，来确定点表定义唯一键的列。注意，这个列的名称必须与底层数据库对象的名称相匹配
• 复合点表键也是支持的
  Edge Table Key（边表键）：
• 默认情况下，边表的键自动从底层数据库对象的一个单一的主键或者唯一键约束定义。如果二者同时存在，则使用主键约束优先于唯一键约束
• 如果边表的键是自动基于一个单一的唯一键推断的，那么这个列除了UNIQUE以外也必须是NOT NULL
• 如果底层数据库对象没有包含唯一约束来控强制制数据唯一性，那么必须明确在EDGE TABLES子句中显式定义的KEY的子子句，来确定点表定义唯一键的列。注意，这个列的名称必须与底层数据库对象的名称相匹配
• 默认情况下，源和目标表键自动从边表和底层源和目标表之间的外键约束分别获取
• 然而，在一下任意情况下必须在源和目标点表中明确指定KEY子子句：
  • 在边表和关联点表之间没有外键约束
  • 在边表和关联点表之间有多个外键约束
  • 边表以及它的源和目标点表的底层数据库对象为物化视图或外部表

2.4 关于标签（LABEL）和属性（PROPERTY）

标签可以与一个或多个图元素表相关联，并且它们丰富了图定义。标签可以定义为具有或不具有属性。可以选择为图中的点表和边表定义LABEL和PROPERTIES。如果未指定，则会自动为图形元素表分配一个带有图元素表名称的标签，并且所有可见列都显示为属性，使用列名作为属性名称。
LABEL（标签）：

• 默认情况下，点表和边表自动使用各自的元素表名称分配标签
• DEFAULT LABEL子子句也可以用于显式的应用上一条规则
• 可以使用LABEL子子句显示的给点和边图元素表指定一个新的标签名称
• 一个图元素表可以有多个标签
• 同一个标签可以被多个图元素表共享
  在慢满足一下条件的情况下，一个标签可以被不止一个图元素表使用（shared label）：
  • 共享此标签的所有图元素表都声明相同的一组属性名称。请注意，属性顺序在标签定义中并不重要。
  • 由同一属性名称公开的不同列或值表达式具有union compatible类型。

Properties（属性）：

• 默认情况下，在没有定义标签或在属性图定义时使用DEFAULT LABEL子子句，点表和边表的所有可见列会自动公开为属性
• 使用任意Oracle内建数据类型的列都可以作为SQL属性图中标签的属性被公开。包含虚拟列、JSON、CLOB和BLOB。
  但是下面的不被支持：
  • XMLType和SDO_GEOMETRY
  • 在XMLType列上存储为二进制XML的SQL/XML值表达式，和在SDO_GEOMETRY object数据类型列使用SDO_GEOMETRY内建函数返回的是属性支持类型的值则是允许的。不支持任何通用对象数据类型和用户定义的数据类型及其对应的SQL运算符值表达式。
  • ANYTYPE
• 在SQL属性图创建时，点和边属性的数据类型确定如下：
  • 与不同标签关联的不同属性与基础数据库列有相同的数据类型
  • 来自不同标签的具有相同名称的属性与基础数据库列具有相同的数据类型。然而你必须在创建SQL属性图的时候使用ALLOW MIXED PROPERTY TYPES选项
  • 来自同一标签的具有相同名称的属性将具有UNION ALL兼容类型的基础数据库列，也必须在创建SQL属性图的时候使用ALLOW MIXED PROPERTY TYPES选项
• 如果要显式定义标签的点或边属性，则支持以下属性声明：
  • PROPERTIES [ARE] ALL COLUMNS：将图元素表的所有可见列作为标签属性公开。但是，如果在创建SQL属性图之后，在源数据库对象中添加或删除了任何列，则这些列将不会反映在图上
  • PROPERTIES [ARE] ALL COLUMNS EXCEPT(<column_names_list>)：将图元素表的所有可见列作为标签属性公开，但显式列出的列除外
  • PROPERTIES(<list_of_column_names>)：仅将图元素表中指定的的列作为标签属性公开。属性名称默认为列名
  • PROPERTIES(<column_name AS property_name,…>)：和上一条一样，但是AS property_name附加到column_name，然后property_name用作属性名称
  • PROPERTIES(<column_expressions AS property_name,…>)：声明一个属性，该属性是列上的表达式。在这种情况下，AS子句是强制性的。值表达式可以是在标量数据类型列上定义的SQL运算符表达式，也可以是JSON表达式。
  • NO PROPERTIES：没有列在标签中公开
• 伪列（Pseudo-column）不能作为标签属性公开（这里我怀疑官方文档有误，打成了Peudo-column）

2.5 使用Graph Option来创建SQL属性图

您可以使用图选项（Graph Option）来控制SQL属性图形在创建时的行为。
可以使用options子句在CREATE PROPERTY GRAPH DDL语句的末尾指定图选项。可以根据需要使用MODE或MIXED PROPERTY TYPES选项，也可以同时使用这两种选项。

2.5.1 使用OPTION来制定图的MODE

可以下面的图选项在创建SQL属性图的时候指定图的MODE：

• ENFORCED MODE：这确保了在底层数据库表上存在对唯一键约束的依赖关系。如果满足以下任何条件，在创建SQL属性图时使用这个模式，CREATE PROPERTY GRAPH语句将引发错误：
  • 图元素表指定的点或边表键既不是主键也不是NOT NULL的唯一键
  • 没有明确的图元素表的点或边表键并且系统无法系统识别默认的点或边表键，比如在底层数据库表中没有单独的主键或单独的NOT NULL唯一约束的列
  • 对于指定的边的源键和对应的源点键，或者对于指定边的目标键和对应目标的点键，在底层表之间不存在对应的外键
  • 边表在没有源或目标明确的键，系统无法隐式推断键，因为边表和引用的源（或目标）点表之间没有单个外键约束
• TRUSTED MODE (默认)：使用TRUSTED MODE时，不依赖于底层数据库表上的唯一键约束。因此，前面的例子在TRUSTED MODE下运行时不会抛出任何错误。这意味着，如果选择使用此选项，则必须保证每个图形元素表上主键的唯一性，以及边表及其源表和目标表之间的有效外键引用。否则，图查询结果可能不正确，因为没有达到预期的保证。

2.5.2 使用OPTION来允许/禁止不同属性类型共享属性名称

可以使用下面的值来指定MIXED PROPERTY TYPES选项：

• ALLOW MIXED PROPERTY TYPES：这个值确保了：
  • 如果两个拥有相同名称的属性属于不同标签，那么它们可以有完全不同的类型
  • 如果在共享标签内共享属性名称，则它们应该是所有所有可兼容的类型
    比如FLOAT和VARCHAR就不兼容，而FLAT和BINARY_DOUBLE则是兼容的
• DISALLOW MIXED PROPERTY TYPES (默认)：这确保了具有相同名称的属性应严格为相同的数据类型。这适用于所有标签，无论它们与单个或多个图元素表相关联。

2.6 相关权限

这里直接贴出截图：

2.7 Revalidating and Drop

ALTER PROPERTY GRAPH graph_name COMPILE;
DROP PROPERTY GRAPH graph_name;

2.8 JSON支持

SQL属性图是支持JSON数据类型的。

3 SQL Graph查询

这里是根据上一节创建SQL属性图，下面是一个范例：

SELECT * FROM GRAPH_TABLE (students_graph
  MATCH
  (a IS person) -[e IS friends]-> (b IS person WHERE b.name = 'Mary')
  WHERE a.name='Bob'
  COLUMNS (a.name AS person_a, b.name AS person_b)
);

使用SELECT FROM GRAPH_TABLE语句和MATCH子句：

• FROM子句：包括GRAPH_TABLE运算符，该运算符将输入的图名称作为第一个参数
• MATCH子句：表示要在SQL属性图上搜索的图形元素模式（点或边模式）。它可以选择性地包括元素模式WHERE子句，如前面的示例（（b IS person WHERE b.name=‘Mary’））查询中所示。此界内WHERE子句可以访问任何匹配的变量
• WHERE子句：这是一个可选的界外WHERE子句。与元素模式WHERE子句类似，它可以访问所有图形模式变量，并在MATCH子句中表达适用于整个模式的谓词。
• COLUMNS子句：包含查询输出的列

关于更多的pattern（模式），请查看官方文档https://docs.oracle.com/en/database/oracle/property-graph/24.2/spgdg/sql-graph-queries.html

总结

本期部署了PGX，并演示了SQL属性图的创建、重定义、查询等，这里需要说明的是PGX上SQL属性图是无法可视化展示的，那么用哪种方式可以可视化，下期继续分享。
老规矩，知道写了些啥。