Turn Your Data Into Decisive Knowledge

Virtual + Physical Knowledge Graphs from CSV Documents

Script Breakdown: Creating a Knowledge Graph from 80 CSV Documents.

Convert customer and transaction data into Virtual and Physical RDF triples using R2RML (Wizard and Linked Data View Generator).

Load Data into Virtual and Physical SQL Tables

Install Custom Stored Procedure for quickly mapping multiple CSV documents to a virtual SQL table:

                    
DROP PROCEDURE file_dirlist_full;
CREATE PROCEDURE file_dirlist_full (IN a ANY)
    {
        DECLARE _inx integer;        
        DECLARE F VARCHAR;
        DECLARE L INT;

        F := file_dirlist(a,1);
        L := length(F);
        _inx := 0;

        
        While(_inx < L){
            DECLARE v any;
        F[_inx] := concat(a,'/',F[_inx]);
        _inx := _inx + 1;

        }

        return F;
    
    };

DROP PROCEDURE attach_from_csv_dir;
CREATE PROCEDURE attach_from_csv_dir (IN tb VARCHAR, IN dir ANY, in delimiter VARCHAR, in newline VARCHAR, in esc VARCHAR, in skip_rows int, in pkey_columns any := null)
    {
    declare f VARCHAR;

    f:= attach_from_csv(tb,file_dirlist_full(dir),delimiter,newline,esc,skip_rows,pkey_columns);

    return F;
    };
                        
                

Create Virtual and Physical SQL Tables of CSV Data

                        
SPARQL CLEAR GRAPH  ;
DROP TABLE demo.fint.account IF EXISTS;
DROP TABLE demo.fint.customer IF EXISTS;
DROP TABLE demo.fint.transaction IF EXISTS;
DROP TABLE demo.fint.fraud_label IF EXISTS;

DROP TABLE demo.fint.account_physical IF EXISTS;
DROP TABLE demo.fint.customer_physical IF EXISTS;
DROP TABLE demo.fint.transaction_physical IF EXISTS;
DROP TABLE demo.fint.fraud_label_physical IF EXISTS;

ATTACH_FROM_CSV_DIR('demo.fint.account','fint/synthetic_data/accounts',',','\n',null,1,VECTOR(1));
ATTACH_FROM_CSV_DIR('demo.fint.customer','fint/synthetic_data/customers',',','\n',null,1,VECTOR(1));
ATTACH_FROM_CSV_DIR('demo.fint.transaction','fint/synthetic_data/transactions',',','\n',null,1,VECTOR(1));
ATTACH_FROM_CSV_DIR('demo.fint.fraud_label','fint/synthetic_data/fraud_labels',',','\n',null,1,VECTOR(1));

GRANT SELECT ON demo.fint.account to SPARQL_SELECT;
GRANT SELECT ON demo.fint.customer to SPARQL_SELECT;
GRANT SELECT ON demo.fint.transaction to SPARQL_SELECT;
GRANT SELECT ON demo.fint.fraud_label to SPARQL_SELECT;


-- Optional Create SQL Tables Containing Physical Data

CREATE TABLE demo.fint.account_physical
AS SELECT * FROM demo.fint.account
WITH DATA;

CREATE TABLE demo.fint.customer_physical
AS SELECT * FROM demo.fint.customer
WITH DATA;

CREATE TABLE demo.fint.transaction_physical
AS SELECT * FROM demo.fint.transaction
WITH DATA;

CREATE TABLE demo.fint.fraud_label_physical
AS SELECT * FROM demo.fint.fraud_label
WITH DATA;

GRANT SELECT ON demo.fint.account_physical to SPARQL_SELECT;
GRANT SELECT ON demo.fint.customer_physical to SPARQL_SELECT;
GRANT SELECT ON demo.fint.transaction_physical to SPARQL_SELECT;
GRANT SELECT ON demo.fint.fraud_label_physical to SPARQL_SELECT;                        
                    
                

Confirm Virtual and Physical Table Creation by Chatting with our Data

We can use OPAL to query the generated table using natural language. A configuration file provides the DDL (Data Definition Language), which can be used to generate queries, provide hard-coded queries to execute, and deliver answers.

• Conversation Link

Generate Virtual and Physical Linked Data Views

Creating a Knowledge Graph from Federated SQL RDBMS Tables (local, SQLServer, MySQL)

Tables can be attached via Conductor interface, or programmatically using the `ATTACH_TABLE()` command.

This example runs a SQL query against a Local Virtuoso SQL table and remotely attached SQL Server and MySQL tables

Federated SQL Query Example

Virtual and Physical RDF Views can be generated using the “Linked Data Views” feature, or using an R2RML script.

Storing the query as a SQL View

DROP VIEW demo.sql_federation.data;
CREATE VIEW demo.sql_federation.data AS (
  SELECT 
    A.CustomerID, A.CompanyName, B.OrderID, 
    C.EmployeeID, C.LastName, C.FirstName 
  FROM "sqlserver"."northwind"."customers" A 
  NATURAL JOIN "mysql5"."northwind"."orders" B 
  NATURAL JOIN "Demo"."demo"."employees" C
);
GRANT SELECT ON demo.sql_federation.data TO SPARQL_SELECT;

Creating a Virtual Linked Data View

• Example Query Definition
• Example Query Result

Federated SPARQL Queries (SPARQL-FED)

List the Awards, past partners, and past/current spouses of Robert Downey Jr.

• Query Definition: Raw
• Query Result: Raw
• Query Definition: Minimized with group_concat()
• Query Result: Minimized with group_concat()