X

Best Practices from Oracle Development's A‑Team

RDF View from Relational - Spatial and Graph part 1

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

Check with the following command as sysdba
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

Check with the following command as sysdba
SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8
If not set, see Oracle Support Document 225912.1 (Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET )) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=225912.1

Your database MUST have NLS_NCHAR_CHARACTERSET set to AL16UTF16

Check with the following command as sysdba
SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_NCHAR_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL16UTF16
If not set, see Oracle Support Document 225912.1 (Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET )) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=225912.1

System parameter max_string_size must be set to extended

Check with the following command as sysdba
SQL> SHOW PARAMETER MAX_STRING_SIZE

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
max_string_size 		     string	 EXTENDED
If not set, see https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/MAX_STRING_SIZE.html#GUID-D424D23B-0933-425F-BC69-9C0E6724693C

System parameter _ksmg_granule_size larger than 16mb

I am using 32mb. Check with the following command as sysdba
SQL> SHOW PARAMETER _ksmg_granule_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
_ksmg_granule_size		     integer	 33554432
If it needs changing, as sysdba:
alter system set "_ksmg_granule_size"=33554432 scope=spfile;

Creating the RDF view

Make sure your database is started, the listener is up, and the PDB is open for read and write. If you want to have the PDB autmatically be opened when you start your database, open in and then save the state. You can check if your PDB is open for read and write with the show pdbs command mentioned earlier. To keep it open even after a restart:
ALTER PLUGGABLE DATABASE nwpdb SAVE STATE;
1. Create tablespace, nwuser, and semantic network Connect to nwpdb as the system user
[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’);

2. Connect to sqlplus as the nwuser you just created and test
[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);
You may need to clean up the list of tables if you generate your own as it could contain tables you do not want as part of your view, or you may have incompatible data types. If you try to create the RDF view with from a table with an incompatible data type, you will see something similar to the following:
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(,)
]
5. Create RDF view SQL command to generate RDF view called nw_rdfview:
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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha