Having installed the Graph Server in the last post, lets set up and create your very first graph.

This follows Oracles documentation. It however had different ways to do it and I struggled to figure out which would be the easiest. Having abandoned a couple for different reasons this is how I did it and I think it is pretty straight forward and uses the tool interactively as much as possible.

We first need a user we’ll use to log in to the graph server.

create user graphuser identified by graphuser
  default tablespace users
  temporary tablespace temp;
  
alter user graphuser quota unlimited on users;

grant graph_developer to graphuser;

grant create session, create table, create view to graphuser;

Having set up a database user with very basic privileges, we need to set up the groups used by the tool.

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  role_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(role_exists, -01921);
  TYPE graph_roles_table IS TABLE OF VARCHAR2(50);
  graph_roles graph_roles_table;
BEGIN
  graph_roles := graph_roles_table(
    'GRAPH_DEVELOPER',
    'GRAPH_ADMINISTRATOR',
    'GRAPH_USER',
    'PGX_SESSION_CREATE',
    'PGX_SERVER_GET_INFO',
    'PGX_SERVER_MANAGE',
    'PGX_SESSION_READ_MODEL',
    'PGX_SESSION_MODIFY_MODEL',
    'PGX_SESSION_NEW_GRAPH',
    'PGX_SESSION_GET_PUBLISHED_GRAPH',
    'PGX_SESSION_COMPILE_ALGORITHM',
    'PGX_SESSION_ADD_PUBLISHED_GRAPH');
  FOR elem IN 1 .. graph_roles.count LOOP
  BEGIN
    dbms_output.put_line('create_graph_roles: ' || elem || ': CREATE ROLE ' || graph_roles(elem));
    EXECUTE IMMEDIATE 'CREATE ROLE ' || graph_roles(elem);
  EXCEPTION
    WHEN role_exists THEN
      dbms_output.put_line('create_graph_roles: role already exists. continue');
    WHEN OTHERS THEN
      RAISE;
    END;
  END LOOP;
EXCEPTION
  when others then
    dbms_output.put_line('create_graph_roles: hit error ');
    raise;
END;
/

With the roles in place we grant them to the graph_administrator, graph_developer and graph_user groups.

grant pgx_session_create to graph_administrator;
grant pgx_server_get_info to graph_administrator;
grant pgx_server_manage to graph_administrator;
grant pgx_session_create to graph_developer;
grant pgx_session_new_graph to graph_developer;
grant pgx_session_get_published_graph to graph_developer;
grant pgx_session_modify_model to graph_developer;
grant pgx_session_read_model to graph_developer;
grant pgx_session_create to graph_user;
grant pgx_session_get_published_graph to graph_user;
grant graph_developer to graphuser;

That completes the setup of the user. Now we’ll look at adding some example data. Let’s first create two tables.

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);

The graph server comes with some example data. As it does not contain SQL Loader the easiest is to move it to the database server and load it there.

If you’re using docker then copying between the graph server container and the database container would look something like this.

docker cp graph-server:/opt/oracle/graph/data/bank_graph .
docker cp bank_graph oracledb_19_19:/tmp
rm -rf bank_graph

In the database server or container you want to change the owner of the files you copied over to have oracle own them.

cd /tmp
chown -R oracle:oinstall bank_graph

Create a control file for the first file. Name it /tmp/bank_graph/bank_nodes.ctl.

load data
infile 'bank_nodes.csv'
into table bank_accounts
fields terminated by "," optionally enclosed by '"'
( id, name )

Now you can load the data into the table.

sqlldr graphuser/graphuser@localhost/pdb19 control=bank_nodes.ctl

When it completes the first table has been loaded. Let’s repeat the process för the second table. Put this in /tmp/bank_graph/bank_edges_amt.ctl.

load data
infile 'bank_edges_amt.csv'
into table bank_txns
fields terminated by "," optionally enclosed by '"'
(from_acct_id,to_acct_id,description,amount)

Now before you import the data. Take a look at the contents of the file. In my case it contained DOS-style line endings which did not work well at all. use hexdump on a couple of rows and look at how they end. If they end with “0a0d” and you are in a *nix environment you want to convert them to *nix style line endings. There are many ways to skin a cat, in this case I opted for using vi to do it.

vi bank_edges_amt.csv -c "set ff=unix" -c ":wq"

With that issue out of the way we can load this data too.

sqlldr graphuser/graphuser@localhost/pdb19 control=bank_edges_amt.ctl

The data is now loaded. We finish up with making a few changes to the tables.

ALTER TABLE bank_accounts ADD PRIMARY KEY (id);
ALTER TABLE bank_txns ADD txn_id NUMBER;
UPDATE bank_txns SET txn_id = ROWNUM;
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);

Phew. Now the data is in place and we can turn to what we really wanted to look at, getting the graph configured.

Browse to the graph server you have set up. If you followed along in the last post it will be at https://localhost:7007/ui/.

Let us first create the property graph we will then run a query against to display the graph. Clinck on “Database (PGQL Property Graphs)” if that tab is not already selected. In the “PGQL Graph Query” field, enter the following and run it.

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)

With that executed you have a property graph to run queries against. This is not digging deep in PGQL, we’ll just use the simplest form that shows transactions. Replace the content in the PGQL Graph Query with this.

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

When you execute that query you will get a graph displayed that looks something like this.

Thus you now have all the components to play with the graph technology in your Oracle database. This is not reaching the goal, it is rather where you can start learning more both about what the visualization can do and how you can work with the graph interface as well as learning how to use PGQL to get insights from your data that is best visualized with a property graph.

Hopefully that makes you as happy as the made up professor at the very start of this post. 🙂

Ah, yes… Oracle DB 23ai has lots more with property graphs you can create as a regular database object and Oracle APEX can display property graphs from those database objects that looks awesome. So start that upgrade project…