Loading a SQL Property Graph Using the readGraphByName API

6.1 Loading a SQL Property Graph Using the readGraphByName API

You can load a SQL property graph into the graph server (PGX) by calling the readGraphByName API on a PgxSession object.

When loading a SQL property graph into the graph server (PGX), the full graph schema will be determined and mapped to a graph configuration. The graphs will be loaded as partitioned graphs where each vertex or edge table will be mapped to the respective vertex or edge provider of the same name. Labels and properties will also be loaded as defined.

However, note that only one label per vertex or edge table is supported in order to load a SQL graph into the graph server (PGX).

For example, consider the following SQL property graph:

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

You can load this SQL graph into memory as shown:

opg4j> var graph = session.readGraphByName ("STUDENT_NETWORK",GraphSource.PG_SQL)
graph ==> PgxGraph[name=STUDENTS_NETWORK,N=4,E=4,created=1681007796946]

PgxGraph graph = session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL);

>>> graph = session.read_graph_by_name("STUDENT_NETWORK", "pg_sql")
>>> graph
PgxGraph(name: STUDENTS_NETWORK, v: 4, e: 4, directed: True, memory(Mb): 0)

Loading a SQL Property Graph from a Different Schema
You can specify the schema name when using the readGraphByName API for loading a SQL property graph.
Loading a SQL Property Graph Using Graph Optimization Options
You can optimize the read or update performance, when loading a SQL property graph using the graph optimization options.
Loading a SQL Property Graph Using OnMissingVertex Options
If either the source or destination vertex or both are missing for an edge, then you can use the OnMissingVertexOption to specify the behavior for handling the edge with the missing vertex.
Loading a SQL Property Graph with Properties Mapped to CLOB Data Type Columns
You can load a SQL property graph from a database table having a CLOB data type column into the graph server (PGX).

Parent topic: Loading a SQL Property Graph into the Graph Server (PGX)

6.1.1 Loading a SQL Property Graph from a Different Schema

You can specify the schema name when using the readGraphByName API for loading a SQL property graph.

If you only provide the graph name when calling the readGraphByName API, it is assumed that the graph is owned by current user. But if you want to load a graph owned by another user, then you must provide the schema name as well. Also, ensure that you have SELECT permission on the SQL graph and all its underlying data tables.

The following example loads a SQL property graph from the GRAPHUSER schema:

opg4j> var graph = session.readGraphByName("GRAPHUSER", "STUDENT_NETWORK", GraphSource.PG_SQL)
graph ==> PgxGraph[name=STUDENT_NETWORK,N=4,E=4,created=1680769031393]

PgxGraph graph = session.readGraphByName("GRAPHUSER", "STUDENT_NETWORK", GraphSource.PG_SQL);

>>> graph = session.read_graph_by_name("STUDENT_NETWORK", "pg_sql", "GRAPHUSER")
>>> graph
PgxGraph(name: STUDENT_NETWORK_2, v: 4, e: 4, directed: True, memory(Mb): 0)

6.1.2 Loading a SQL Property Graph Using Graph Optimization Options

You can optimize the read or update performance, when loading a SQL property graph using the graph optimization options.

The following example shows loading a SQL property graph optimized for READ operation:

opg4j> var graph = session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL,
...>     ReadGraphOption.optimizeFor(GraphOptimizedFor.READ))
graph ==> PgxGraph[name=STUDENT_NETWORK,N=4,E=4,created=1681008951415]

PgxGraph graph = session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL,
  ReadGraphOption.optimizeFor(GraphOptimizedFor.READ);

>>> session.read_graph_by_name('STUDENT_NETWORK', 'pg_sql', options=['optimized_for_read'])
PgxGraph(name: STUDENT_NETWORK, v: 4, e: 4, directed: True, memory(Mb): 0)

The following example shows loading a SQL property graph optimized for UPDATE operation. Also, note that the READ and UPDATE options cannot be used at the same time.

opg4j> var graph = session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL,
...>     ReadGraphOption.optimizeFor(GraphOptimizedFor.UPDATES))
graph ==> PgxGraph[name=STUDENT_NETWORK_2,N=4,E=4,created=1681009073501]

PgxGraph graph = session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL,
  ReadGraphOption.optimizeFor(GraphOptimizedFor.UPDATES));

>>> session.read_graph_by_name('STUDENT_NETWORK', 'pg_sql', options=['optimized_for_updates'])
PgxGraph(name: STUDENT_NETWORK, v: 4, e: 4, directed: True, memory(Mb): 0)

The following example shows loading a SQL property graph with the SYNCHRONIZABLE optimization option. This option can be used in combination with the READ and UPDATE options.

opg4j> var graph = session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL,
...>                            ReadGraphOption.SYNCHRONIZABLE)
graph ==> PgxGraph[name=STUDENT_NETWORK,N=4,E=4,created=1696341305374]

PgxGraph graph = session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL,
  ReadGraphOption.SYNCHRONIZABLE);

>>> session.read_graph_by_name('STUDENT_NETWORK', 'pg_sql', options=['synchronizable'])
PgxGraph(name: STUDENT_NETWORK_2, v: 4, e: 4, directed: True, memory(Mb): 0)

6.1.3 Loading a SQL Property Graph Using `OnMissingVertex` Options

If either the source or destination vertex or both are missing for an edge, then you can use the OnMissingVertexOption to specify the behavior for handling the edge with the missing vertex.

The supported values are:

OnMissingVertex.ERROR (default): Specifies that an error must be thrown for edges with missing source or destination vertex.
OnMissingVertex.IGNORE_EDGE: Specifies that the edge for a missing source or destination vertex must be ignored.
OnMissingVertex.IGNORE_EDGE_LOG: Specifies that the edge for a missing source or destination vertex must be ignored and all ignored edges must be logged.
OnMissingVertex.IGNORE_EDGE_LOG_ONCE: Specifies that the edge for a missing source or destination vertex must be ignored and only the first ignored edge must be logged.

The following example loads a SQL property graph by ignoring the edges with missing vertices and logging only the first ignored edge.

opg4j> session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL,
...>      ReadGraphOption.onMissingVertex(OnMissingVertex.IGNORE_EDGE_LOG_ONCE))
$2 ==> PgxGraph[name=STUDENT_NETWORK_2,N=4,E=4,created=1697264084059]

PgxGraph graph = session.readGraphByName("STUDENT_NETWORK", GraphSource.PG_SQL,
  ReadGraphOption.onMissingVertex(OnMissingVertex.IGNORE_EDGE_LOG_ONCE));

>>> session.read_graph_by_name('STUDENT_NETWORK', 'pg_sql',
    options=['on_missing_vertex_ignore_edge_log_once'])
PgxGraph(name: STUDENT_NETWORK, v: 4, e: 4, directed: True, memory(Mb): 0)

Parent topic: Loading a SQL Property Graph Using the readGraphByName API

6.1.4 Loading a SQL Property Graph with Properties Mapped to CLOB Data Type Columns

You can load a SQL property graph from a database table having a CLOB data type column into the graph server (PGX).

The CLOB data type columns are processed as String properties in the graph server (PGX).

For example, consider the following sample data in the database:

CREATE TABLE depts (
  dept_id       NUMBER,
  dept_name     VARCHAR2 (10),
  emp_details   CLOB,
  CONSTRAINT employees_pk PRIMARY KEY (dept_id)
);

INSERT INTO depts
VALUES (1, 'HR', '
<employees>
  <employee empNo="1234" eName="SMITH" hireDate="17-DEC-1990"></employee>
  <employee empNo="5678" ename="ALLEN" hireDate="02-JAN-1981"></employee>
  </employees>
');
INSERT INTO depts
VALUES (2, 'IT', '
<employees>
  <employee empNo="5628" ename="JONES" hireDate="13-MAR-1986"></employee>>
  <employee empNo="5628" ename="TOM"   hireDate="13-MAR-1986"></employee>>
  </employees>
');

Create a SQL property graph created using the CREATE PROPERTY GRAPH DDL statement.


CREATE PROPERTY GRAPH dept
VERTEX TABLES ( 
  DEPTS KEY (dept_id)
  LABEL dept
    PROPERTIES (dept_id, dept_name, emp_details)
)

Load the graph into the graph server (PGX) as shown:

opg4j> var g = session.readGraphByName("DEPT", GraphSource.PG_SQL)
g ==> PgxGraph[name=DEPT,N=2,E=0,created=1727242485228]
opg4j> g.queryPgql("SELECT n.* FROM MATCH (n:dept)").print()
+--------------------------------------------------------------------------------------------------
| DEPT_ID | DEPT_NAME | EMP_DETAILS
| 2.0     | IT        | <employees>
                          <employee empNo="5628" ename="JONES" hireDate="13-MAR-1986"></employee>>
                          <employee empNo="5628" ename="TOM"   hireDate="13-MAR-1986"></employee>>
                        </employees>                                                               |
| 1.0     | HR        | <employees>
                          <employee empNo="1234" eName="SMITH" hireDate="17-DEC-1990"></employee>
                          <employee empNo="5678" ename="ALLEN" hireDate="02-JAN-1981"></employee>
                        </employees>
                                                                                                   |
+--------------------------------------------------------------------------------------------------

PgxGraph g = session.readGraphByName("DEPT", GraphSource.PG_SQL);
PgqlResultSet rs = g.queryPgql("SELECT n.* FROM MATCH (n:dept)");
rs.print();

>>> g = session.read_graph_by_name("DEPT", "pg_sql")
>>> g.query_pgql("SELECT n.* FROM MATCH (n:dept)").print()

+--------------------------------------------------------------------------------------------------
| DEPT_ID | DEPT_NAME | EMP_DETAILS
| 2.0     | IT        | <employees>
                          <employee empNo="5628" ename="JONES" hireDate="13-MAR-1986"></employee>>
                          <employee empNo="5628" ename="TOM"   hireDate="13-MAR-1986"></employee>>
                        </employees>                                                               |
| 1.0     | HR        | <employees>
                          <employee empNo="1234" eName="SMITH" hireDate="17-DEC-1990"></employee>
                          <employee empNo="5678" ename="ALLEN" hireDate="02-JAN-1981"></employee>
                        </employees>
                                                                                                   |
+--------------------------------------------------------------------------------------------------

Related Topics

Mapping Oracle Database Types to PGX Types