Querying a Database Source

This topic provides details about the structure to use when writing GDI queries to read or ingest data from database data sources. It also includes example queries that may be useful as a starting point for writing your own GDI queries.

• Supported Databases
• Query Syntax
• Query Examples

Supported Databases

The GDI supports querying any database through a JDBC connection. AnzoGraph installations include JDBC drivers for the following databases:

• Databricks
• H2
• IBM DB2
• Microsoft SQL Server
• MariaDB
• Oracle
• PostgreSQL
• SAP Sybase (jTDS)
• Snowflake

To extend the service to access other databases, additional JDBC drivers can be added to AnzoGraph. For information about acquiring additional JDBC drivers, contact your Cambridge Semantics Customer Success manager. For instructions on deploying drivers, see Deploy Drivers for Custom Database Sources.

Query Syntax

The following query syntax shows the structure of a GDI query for database sources. The clauses, patterns, and placeholders that are links are described below.

# PREFIX Clause
PREFIX s:     <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX rdf:   <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs:  <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd:   <http://www.w3.org/2001/XMLSchema#>
PREFIX owl:   <http://www.w3.org/2002/07/owl#>
PREFIX anzo:  <http://openanzo.org/ontologies/2008/07/Anzo#>
PREFIX zowl:  <http://openanzo.org/ontologies/2009/05/AnzoOwl#>
PREFIX dc:    <http://purl.org/dc/elements/1.1/>

# Result Clause
{ 
   [ GRAPH ${targetGraph} { ] 
   triple_patterns
 [ } ]
}
[ ${usingSources} ]

WHERE
{
   # SERVICE Clause: Include the following service call when reading or inserting data.
    SERVICE [ TOPDOWN ] <http://cambridgesemantics.com/services/DataToolkit>

   # View SERVICE Clause: Or use the service call below when constructing a view.
    SERVICE <http://cambridgesemantics.com/services/DataToolkitView>(${targetGraph})

    { 
      ?data a s:DbSource ;
        s:url "string" ;
        s:username "string" ;
        s:password "string" ;
        [ s:token "string" ; ]
        [ s:driver "string" ; ]
        [ s:property [ s:name "string" ; s:value "string" ] ; ]
        [ s:timeout int ; ]
        [ s:maxConnections int ; ]
        [ s:batching boolean | int ; ]
        [ s:concurrency int | [ list_of_properties ] ; ]
        [ s:rate int | "string" ; ]
        [ s:partitionBy "string" | ?variable | s:auto ; ]
        [ s:paging [ pagination_options ; ] 
        [ s:locale "string" ; ]
        [ s:sampling int ; ]
        [ s:selector "string" | [ list ] ; ]
        [ s:model "string" ; ]
        [ s:key ("string") ; ]
        [ s:reference [ s:model "string" ; s:using ("string") ]
        [ s:formats [ datatype_formatting_options ] ; ]
        [ s:normalize boolean | [ normalization_rules ] ; ]
        [ s:query "string" ; ]
        [ s:database "string" ; ]
        [ s:schema "string" ; ]
        [ s:table "string" ; ]
        [ s:count ?variable ; ]
        [ s:offset int ; ]
        [ s:orderBy "string" | ?variable ; ]
        [ s:limit int ; ]
        # Mapping variables
        ?mapping_variable ( [ "binding" ] [ datatype ] [ "datetime_format" ] ) ;
        ... ;
        .
     # Additional clauses such as BIND, VALUES, FILTER
   }
}

Query Examples

The example below selects data from the AdventureWorks2012 database. The s:selector property is used to specify the table (salesOrderHeader in the Sales schema) to target.

PREFIX s:   <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT (COUNT(*) as ?count)
WHERE
{
   SERVICE <http://cambridgesemantics.com/services/DataToolkit>
{
      ?SalesOrderHeader a s:DbSource ;
         s:url "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.url}}" ;
         s:username "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.user}}" ;
         s:password "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.password}}" ;
         s:selector "Sales.SalesOrderHeader" ;
         ?SalesOrderID (xsd:int) ;
         ?RevisionNumber (xsd:int) ;
         ?OrderDate (xsd:dateTime) ;
         ?DueDate (xsd:dateTime) ;
         ?TerritoryID (xsd:int) ;
         ?TotalDue (xsd:decimal) .
      FILTER(?TerritoryID IN (1, 2, 3))
      FILTER(?TotalDue < 11.0 || ?TotalDue > 250)
  }
}

The example below ingests data from a database. To define the data to target, the query includes the s:query property to run an SQL query. The s:table and partitionBy properties are also included to aid the GDI in partitioning the query.

PREFIX s:   <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ont: <http://cambridgesemantics.com/Layer/2f1e926b130a402db6fc10fa54199d49/Model#>

INSERT { 
   GRAPH ${targetGraph} {
      ?resource a ont:EmrPatient ;
      ont:EmrPatient.patientid ?PATIENTID ;
      ont:EmrPatient.gender ?GENDER ;
      ont:EmrPatient.language ?LANGUAGE ;
      ont:EmrPatient.patientfirstdocactivitydate ?PATIENTFIRSTDOCACTIVITYDATE .
  }
}
WHERE {
   SERVICE <http://cambridgesemantics.com/services/DataToolkit> {
      ?data a s:DbSource ;
      s:url "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.url}}" ;
      s:username "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.user}}" ;
      s:password "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.password}}" ;
      s:query "select * from emrdbsmall.emr_patient where emr_patient.PATIENTID < 500" ;
      s:partitionBy "PATIENTID" ;
      s:table "emrdbsmall.emr_patient" ;
      ?PATIENTID (xsd:int) ;
      ?GENDER (xsd:string) ;
      ?LANGUAGE (xsd:string) ;
      ?PATIENTFIRSTDOCACTIVITYDATE (xsd:dateTime "M/d/yyyy HH:mm:ss") .

   BIND(IRI("http://cambridgesemantics.com/Layer/2f1e926b130a402db6fc10fa54199d49/{{?PATIENTID}}") AS ?resource)
  }
}