Articles in this series:

https://www.ateam-oracle.com/rdf-view-from-relational-spatial-and-graph-part-1
https://www.ateam-oracle.com/rdf-model-from-rdf-view-spatial-and-graph-part-2
https://www.ateam-oracle.com/property-graph-from-relational-spatial-and-graph-part-3
https://www.ateam-oracle.com/visualize-property-graph-with-cytoscape-spatial-and-graph-part-4
https://www.ateam-oracle.com/oracle-20c-pgx-and-graphviz-spatial-and-graph-part-5

Introduction

This will be a series of articles showing how to convert the Northwinds sample relational database to RDF and property graph, and how various tools can be used to visualize that data.

This first article will demonstrate converting relational tables to an RDF view.

Prerequisites

This document describes steps with Oracle Database Enterprise Edition 18.3.0.0.0 on Oracle Linux 7.7

If you are using any other version of the DB, the steps may be different.

Make sure you are working in your PDB, not the container (cdb$root)

Any time you connect using sqlplus or sqldeveloper, if you are not sure, execute “show con_name”.
If it shows CDB$ROOT, you need to change to your PDB

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> 
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 NWPDB			  READ WRITE NO
SQL> 
SQL> alter session set container=NWPDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
NWPDB
SQL> 

Partitioning installed and enabled

select * from v$option where parameter = 'Partitioning';

PARAMETER     VALUE     CON_ID
---------    -------   --------
Partitioning  TRUE      0

Spatial and graph installed and enabled.

Check with the following command as sysdba

SQL>  select * from mdsys.rdf_parameter where attribute='SEM_VERSION';

NAMESPACE           ATTRIBUTE           VALUE           DESCRIPTION
----------         ----------           -------        -------------
MDSYS               SEM_VERSION         18.1.0.0.0      VALID

Your database MUST have NLS_CHARACTERSET set to AL32UTF8

SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8

Your database MUST have NLS_NCHAR_CHARACTERSET set to AL16UTF16

SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_NCHAR_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL16UTF16

System parameter max_string_size must be set to extended

SQL> SHOW PARAMETER MAX_STRING_SIZE

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
max_string_size 		     string	 EXTENDED

System parameter _ksmg_granule_size larger than 16mb

SQL> SHOW PARAMETER _ksmg_granule_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
_ksmg_granule_size		     integer	 33554432

alter system set "_ksmg_granule_size"=33554432 scope=spfile;

Creating the RDF view

ALTER PLUGGABLE DATABASE nwpdb SAVE STATE;

[oracle@graph ~]$ sqlplus system@nwpdb

Enter password: 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> show con_name

CON_NAME
------------------------------
NWPDB
SQL> create bigfile tablespace nwtbs datafile '?/dbs/nwtbs.dat' size 512M reuse autoextend on next 512M maxsize UNLIMITED extent management local segment space management auto;

SQL> grant connect, resource, unlimited tablespace, select any dictionary to nwuser identified by nwuser;

## Create a sematic network
SQL> EXECUTE SEM_APIS.CREATE_SEM_NETWORK('nwtbs’);

[oracle@graph ~]$ sqlplus nwuser/nwuser@nwpdb
SQL> create table test (name varchar2(32676));
## If this passes, your extended string size is good to go. If it fails - STOP. Find the problem.
SQL> drop table test;
SQL> quit

3. Load Northwinds data You can find the scripts to create Northwinds tables and data quickly with a Google search. Note that if any of your columns have a datatype of Long, Long Raw, or Blob, you will have problems creating the RDF view. You can change remove or change the datatype of these columns.

4. Get a list of tables Using Oracle internal features, you can create an RDF view from relational data. You pass a list of tables to sem_apis.create_rdfview_model to do this. Below is a command you can copy/paste. You can alter the list of tables to play with what is exposed to the view. These commands should be executed as the nwuser in the nwpdb. If you want to generate your own list of tables, here is a command for sqlplus to generate a list of tables in a schema. You will need to wrap everything in single quotes once you have this output. A command with all the tables for Northwinds is provided in the next step.

set linesize 32767 long 2000000000 longchunksize 32767 PAGESIZE 0 FEEDBACK OFF ECHO OFF TERMOUT OFF
SELECT RTRIM(XMLAGG(XMLELEMENT(E,table_name,',').EXTRACT('//text()')).GetClobVal(),',') AS LIST FROM (select table_name from user_tables order by table_name);

ORA-13199: rel2rdf: step_num=2 tab=DCS_MEDIA_BIN SQLERRM=ORA-13199: Unsupported column datatype: 
[
 additional details: "RDFUSER"."DCS_MEDIA_BIN"=> column being processed: name="DATA" typeInfo=BLOB(,)
]

BEGIN
  sem_apis.create_rdfview_model(
    model_name => 'nw_rdfview',
    tables => SYS.ODCIVarchar2List('CATEGORIES','CUSTOMERCUSTOMERDEMO','CUSTOMERDEMOGRAPHICS','CUSTOMERS','EMPLOYEES','EMPLOYEETERRITORIES','ORDERDETAILS','ORDERS','PRODUCTS','REGION','SHIPPERS','SUPPLIERS','TERRITORIES'),
    prefix => 'http://nwrdf/',
    options => 'KEY_BASED_REF_PROPERTY=T CONFORMANCE=T'
  );
END;
/

Once created, you can execute sparql queries against this view. Using the view is not the fastest method of querying if you have a large amount of data. In the next article in this series, we will dump the RDF view to a regular RDF model, which is more flexible and performant.

6. Execute SPARQL You can now execute SPARQL queries against the RDF view. Here is an example screenshot of SQLDeveloper.