Reading Graphs from Database into the Graph Server (PGX)

Once logged into the graph server (PGX), you can read graphs from the database into the graph server.

Your database user must exist and have read access on the graph data in the database.

The following options are supported for loading a graph:

SQL Property Graphs

Using the readGraphByName API - see Loading a SQL Property Graph Using the readGraphByName API for more details.
Using the PgSqlSubgraphReader API to create and load a subgraph - see Loading a Subgraph Using PGQL Queries for more details.
Using the PGQL CREATE PROPERTY GRAPH statement - see Creating a SQL Property Graph Using PGQL for more details.

PGQL Property Graphs

Using the readGraphByName API - see Loading a PGQL Property Graph Using the readGraphByName API for more details.
Using the PGQL CREATE PROPERTY GRAPH statement - see Creating a Property Graph Using PGQL for more details.
Using the PgViewSubgraphReader#fromPgPgql API to create and load a subgraph - see Loading a Subgraph from a PGQL Property Graph for more details.

Also, refer to the following sections:

Enabling Lazy Loading of Graphs
Starting from Graph Server and Client Release 24.3, you can enable lazy loading for database graphs.
Reading Entity Providers at the Same SCN
If you have a graph which consists of multiple vertex or edge tables or both, then you can read all the vertices and edges at the same System Change Number (SCN).
Progress Reporting and Estimation for Graph Loading
Loading a large graph into the graph server(PGX) can be a long running operation. However, if you load the graph using an asynchronous action, then you can monitor the progress of the graph loading operation.
Graph Configuration Options
Learn about the graph configuration options.
Data Loading Security Best Practices
Loading a graph from the database requires authentication and it is therefore important to adhere to certain security guidelines when configuring access to this kind of data source.
Data Format Support Matrix
Learn about the different data formats supported in the graph server (PGX).
Immutability of Loaded Graphs
Once the graph is loaded into the graph server (PGX), the graph and its properties are automatically marked as immutable.

Enabling Lazy Loading of Graphs

Starting from Graph Server and Client Release 24.3, you can enable lazy loading for database graphs.

When lazy loading is enabled, the graphs are automatically loaded into the graph server (PGX) the first time you access them in a PGQL query. This implies that you can run a PGQL query from a PgxSession instance even if the graph was not loaded into the graph server (PGX) earlier. However, ensure that you specify the graph name using the ON clause as shown:

opg4j> session.queryPgql("SELECT x.* FROM MATCH (x) ON <graph_name>")

session.queryPgql("SELECT x.* FROM MATCH (x) ON <graph_name>");

session.query_pgql("SELECT x.* FROM MATCH (x) ON <graph_name>")

Also, note the following details when lazy loading a graph into the graph server (PGX) memory:

To use the lazy loading feature, you must add (if not previously added) and enable the allow_lazy_loading_for_database_graphs configuration field in the pgx.conf file.
```
{
     "allow_lazy_loading_for_database_graphs": true
}
```
The graph will be loaded by name if it exists in the database. Loading a graph by name relies on the database credentials provided in the pgx_realm options in the pgx.conf file.
Loading will only happen if the graph is not already loaded into the graph server (PGX) memory.
Lazy loading is supported for both PGQL and SQL property graphs. The graph server (PGX) first attempts to load the graph as a SQL property graph. In case it does not exist, then the graph is loaded as a PGQL property graph.
You must have READ permission on the graph and underlying data source tables.
Lazily loaded graph is added as a session dependency and periodic permission checks are performed on the graph.
The following APIs support lazy loading of graphs upon first use:
- PgxSession.queryPgql
- PgxSession.executePgql
- PgxSession.preparePgql
- PgxSession.explainPgql

The following example shows the various queries that will trigger lazing loading of a graph. The example assumes that allow_lazy_loading_for_database_graphs is enabled and MY_GRAPH exists in the database and is not already loaded into the graph server (PGX) memory.

opg4j> session.queryPgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH")
opg4j> session.preparePgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH WHERE x.age = ?")
opg4j> session.executePgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH")
opg4j> session.explainPgql("SELECT x.name, x.* FROM MATCH (x) ON MY_GRAPH")

session.queryPgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH");
session.preparePgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH WHERE x.age = ?");
session.executePgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH");
session.explainPgql("SELECT x.name, x.* FROM MATCH (x) ON MY_GRAPH");

session.query_pgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH")
session.prepare_pgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH WHERE x.age = ?")
session.execute_pgql("SELECT x.name FROM MATCH (x) ON MY_GRAPH")
session.explain_pgql("SELECT x.name, x.* FROM MATCH (x) ON MY_GRAPH")

Reading Entity Providers at the Same SCN

If you have a graph which consists of multiple vertex or edge tables or both, then you can read all the vertices and edges at the same System Change Number (SCN).

This helps to overcome issues such as reading edge providers at a later SCN than the SCN at which the vertices were read, as some edges may reference missing vertices.

Note that reading a graph from the database is still possible even if Flashback is not enabled in your database. In case of multiple databases, SCN can be used to maintain consistency for entity providers belonging to the same database only.

You can use the as_of flag in the graph configuration to specify at what SCN an entity provider must be read. The valid values for the as_of flag are as follows:

Table: Valid values for “as_of” Key in Graph Configuration

If "as_of" is omitted for a vertex or an edge provider in the graph configuration file, then this follows the same behavior as "as_of": null.

Example: Graph Configuration Using "as_of" for Vertex and Edge Providers in the Same Database

The following example configuration has three vertex providers and one edge provider pointing to the same database.

{
  "name": "employee_graph",
  "vertex_providers": [
    {
      "name": "Department",
      "as_of": "<current-scn>",
      "format": "rdbms",
      "database_table_name": "DEPARTMENTS",
      "key_column": "DEPARTMENT_ID",
      "props": [
        {
          "name": "DEPARTMENT_NAME",
          "type": "string"
        }
      ]
    },
    {
      "name": "Location",
      "as_of": "28924323",
      "format": "rdbms",
      "database_table_name": "LOCATIONS",
      "key_column": "LOCATION_ID",
      "props": [
        {
          "name": "CITY",
          "type": "string"
        }
      ]
    },
    {
      "name": "Region",
      "as_of": "<no-scn>",
      "format": "rdbms",
      "database_table_name": "REGIONS",
      "key_column": "REGION_ID",
      "props": [
        {
          "name": "REGION_NAME",
          "type": "string"
        }
      ]
    }
  ],
  "edge_providers": [
    {
      "name": "LocatedAt",
      "format": "rdbms",
      "database_table_name": "DEPARTMENTS",
      "key_column": "DEPARTMENT_ID",
      "source_column": "DEPARTMENT_ID",
      "destination_column": "LOCATION_ID",
      "source_vertex_provider": "Department",
      "destination_vertex_provider": "Location"
    }
  ]
}

When reading the employee_graph using the preceding configuration file, the graph is read at the same SCN for the Department and LocatedAt entity providers. This is explained in the following table:

Table: Example Scenario Using “as_of”

Entity Provider	`"as_of"`	SCN Value
`Department`	`"<current-scn>"`	SCN determined automatically
`Location`	`"28924323"`	`"28924323"` used as SCN
`Region`	`"<no-scn>"`	No SCN used
`LocatedAt`	`"as_of"` flag is omitted	SCN determined automatically

The current SCN value of the database can be determined using one of the following options:

Querying V$DATABASE view:
```
SELECT CURRENT_SCN FROM V$DATABASE;
```

Using DBMS_FLASHBACK package:

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

If you do not have the required privileges to perform either of the preceding operations, then you can use:

SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;

However, note that this option is less precise than the earlier two options.

You can then read the graph into the graph server using the JSON configuration file as shown:

opg4j> var g = session.readGraphWithProperties("employee_graph.json")

PgxGraph g = session.readGraphWithProperties("employee_graph.json");

g = session.read_graph_with_properties("employee_graph.json")

Progress Reporting and Estimation for Graph Loading

Loading a large graph into the graph server(PGX) can be a long running operation. However, if you load the graph using an asynchronous action, then you can monitor the progress of the graph loading operation.

The following table shows the asynchronous graph loading APIs supported for the following formats:

Table: Asynchronous Graph Loading APIs

Data Format	API
PGQL Property Graph	session.readGraphByNameAsync()
CSV	session.readGraphFileAsync()

These supported APIs return a PgxFuture object.

You can then use the PgxFuture.getProgress() method to collect the following statistics:

Report on the progress of the graph loading operation
Estimate of the remaining vertices and edges that need to be loaded into memory

For example, the following code shows the steps to load a PGQL property graph graph asynchronously and subsequently obtain the FutureProgress object to report and estimate the loading progress. However, note that the graph loading estimate (for example, the number of loaded entities and providers or the number of total entities and providers) can be obtained only until the graph loading operation is in progress. Also, the system internally computes the graph loading progress for every 10000 entries of entities that are loaded into the graph server (PGX).

JShell
Java

opg4j>  var graphLoadingFuture = session.readGraphByNameAsync("BANK_GRAPH", GraphSource.PG_PGQL)
readGraphFuture ==> oracle.pgx.api.PgxFuture@6106dfb6[Not completed]

opg4j> while (!graphLoadingFuture.isDone()) {
...>   var progress = graphLoadingFuture.getProgress();
...>   var graphLoadingProgress = progress.asGraphLoadingProgress();
...>   if (graphLoadingProgress.isPresent()) {
...>     var numLoadedVertices = graphLoadingProgress.get().getNumLoadedVertices();
...>   }
...>   Thread.sleep(1000);
...> }

opg4j> var graph = graphLoadingFuture.get();
graph ==> PgxGraph[name=BANK_GRAPH_3,N=999,E=4993,created=1664289985985]

PgxFuture<PgxGraph> graphLoadingFuture = session.readGraphByNameAsync("BANK_GRAPH", GraphSource.PG_PGQL);
while (!graphLoadingFuture.isDone()) {
  FutureProgress progress = graphLoadingFuture.getProgress();
  Optional < GraphLoadingProgress > graphLoadingProgress = progress.asGraphLoadingProgress();
  if (graphLoadingProgress.isPresent()) {
    long numLoadedVertices = graphLoadingProgress.get().getNumLoadedVertices();
  }
  Thread.sleep(1000);
}
PgxGraph graph = graphLoadingFuture.get();

It is recommended that you do not use the FutureProgress object in a chain of asynchronous operations.

Graph Configuration Options

Learn about the graph configuration options.

The following table lists the JSON fields that are common to all graph configurations:

Table: Graph Config JSON Fields

Note: Database connection fields specified in the graph configuration will be used as default in case underlying data provider configuration does not specify them.

Provider Configuration JSON file Options

You can specify the meta-information about each provider’s data using provider configurations. Provider configurations include the following information about the provider data:

Location of the data: a file, multiple files or database providers
Information about the properties: name and type of the property

Table: Provider Configuration JSON file Options

Provider Labels

The label field in the provider configuration can be used to set a label for the entities loaded from the provider. If no label is specified, all entities from the provider are labeled with the name of the provider. It is only possible to set the same label for two different providers if they have exactly the same properties (same names and same types).

Property Configuration

The props entry in the Provider configuration is an object with the following JSON fields:

Table: Property Configuration

| Field | Type | Description | Default | | — | — | — | — | | name | string | Name of the property. | Required | | type | enum[boolean, integer, vertex, edge, float, long, double, string, date, local_date, time, timestamp, time_with_timezone, timestamp_with_timezone, point2d] | Type of the property.
Note: date is deprecated, use one of local_date / time / timestamp / time_with_timezone / timestamp_with_timezone instead).
vertex/edge are place-holders for the type specified in vertex_id_type/edge_id_type fields. | Required | | aggregate | enum[identity, group_key, min, max, avg, sum, concat, count] | [currently unsupported] which aggregation function to use, aggregation always happens by vertex key. | ull| | <samp class="sysout">column</samp> | <samp class="sysout">value</samp> | Name or index (starting from 0) of the column holding the property data. If it is not specified, the loader will try to use the property name as column name (for CSV format only). | ull | | default | value | Default value to be assigned to this property if datasource does not provide it. In case of date type: string is expected to be formatted with yyyy-MM-dd HH:mm:ss. If no default is present ( ull), non-existent properties will contain default Java types (primitives) or empty string (<samp class="sysout">string</samp>) or 01.01.1970 00:00(<samp class="sysout">date</samp>). | ull | | dimension | integer | Dimension of property. | 0 | | drop_after_loading | boolean | [currently unsupported] indicating helper properties only used for aggregation, which are dropped after loading | false | | field | value | Name of the JSON field holding the property data. Nesting is denoted by dot - separation. Field names containing dots are possible, in this case the dots need to be escaped using backslashes to resolve ambiguities. Only the exactly specified object are loaded, if they are non existent, the default value is used. | ull| | <samp class="sysout">format</samp> | <samp class="sysout">array of string</samp> | Array of formats of property. | <samp class="sysout">[]</samp> | | <samp class="sysout">group_key</samp> | <samp class="sysout">string</samp> | [currently unsupported] can only be used if the property / key is part of the grouping expression. | ull | | max_distinct_strings_per_pool | integer | [only relevant ifstring_pooling_strategyisindexed] Amount of distinct strings per property after which to stop pooling. If the limit is reached an exception is thrown. If set to null, the default value from the global PGX configuration will be used. | ull| | <samp class="sysout">stores</samp> | <samp class="sysout">array of object</samp> | A list of storage identifiers that indicate where this property resides. | <samp class="sysout">[]</samp> | | <samp class="sysout">string_pooling_strategy</samp> | <samp class="sysout">enum[indexed, on_heap, none]</samp> | Indicates which string pooling strategy to use. If set to ull, the default value from the global PGX configuration will be used. | ull` |

Loading Configuration

The loading entry is a JSON object with the following fields:

Table: Loading Configuration

Error Handling Configuration

The error_handling entry is a JSON object with the following fields:

Table: Error Handling Configuration

Field	Type	Description	Default
`on_missed_prop_key`	`enum[silent, log_warn, log_warn_once, error]`	Error handling for a missing property key.	`log_warn_once`
`on_missing_vertex`	`enum[ignore_edge, ignore_edge_log, ignore_edge_log_once, create_vertex, create_vertex_log, create_vertex_log_once, error]`	Error handling for a missing source or destination vertex of an edge in a vertex data source.	`error`
`on_parsing_issue`	`enum[silent, log_warn, log_warn_once, error]`	Error handling for incorrect data parsing. If set to silent, `log_warn` or `log_warn_once`, will attempt to continue loading. Some parsing issues may not be recoverable and provoke the end of loading.	`error`
`on_prop_conversion`	`enum[silent, log_warn, log_warn_once, error]`	Error handling when encountering a different property type other than the one specified, but coercion is possible.	`log_warn_once`
`on_type_mismatch`	`enum[silent, log_warn, log_warn_once, error]`	Error handling when encountering a different property type other than the one specified, but coercion is not possible.	`error`
`on_vector_length_mismatch`	`enum[silent, log_warn, log_warn_once, error]`	Error handling for a vector property that does not have the correct dimension.	`error`

Note: The only supported setting for the on_missing_vertex error handling configuration is ignore_edge.

Data Loading Security Best Practices

Loading a graph from the database requires authentication and it is therefore important to adhere to certain security guidelines when configuring access to this kind of data source.

The following guidelines are recommended:

The user or role used to access the data should be a read-only account that only has access to the required graph data.
The graph data should be marked as read-only, for example, with non-updateable views in the case of the database.

Data Format Support Matrix

Learn about the different data formats supported in the graph server (PGX).

The following table illustrates the supported data formats:

Table: Data Format Support Matrix

Format	Vertex IDs	Edge IDs	Vertex Labels	Edge Labels	Vector properties
`PGB`	`int, long, string`	`long`	multiple	single	supported (vectors can be of type `integer`, `long`, `float` or `double`)
`CSV`	`int, long, string`	`long`	multiple	single	supported (vectors can be of type `integer`, `long`, `float` or `double`)

Immutability of Loaded Graphs

Once the graph is loaded into the graph server (PGX), the graph and its properties are automatically marked as immutable.

The immutability of loaded graphs is due to the following design choices:

Typical graph analyses happen on a snapshot of a graph instance, and therefore they do not require mutations of the graph instance.
Immutability allows PGX to use an internal graph representation optimized for fast analysis.
In remote mode, the graph instance might be shared among multiple clients.

However, the graph server (PGX) also provides methods to customize and mutate graph instances for the purpose of analysis. See Graph Mutation and Subgraphs for more information.