Generating a Graph and Ontology with a Direct Load Step

With no mapping required, Anzo's Direct Load Step functionality automatically generates a graph and an ontology (model) for a data source. Using a relatively simple SPARQL query, the direct load option invokes the Graph Data Interface (GDI) RDF and Ontology Generators. The GDI Generators recognize the structure of a data source and automatically generate the necessary statements.

Invoking the Generators is preferable to producing a hand-written query, especially when the structure of the data is very complex, such as a JSON data source with many inner repeating structures or a database with many tables and keys. When the source contains complex structures, the GDI will generate only the required statements and avoid cross-products, optimizing query execution and memory usage. In addition, the GDI Generator parallelizes the load across the AnzoGraph cluster so that a data source (such as a database) can be ingested with a single query.

This topic provides details about invoking the GDI RDF and Ontology Generators. The Generators can be used with all of the supported data source types.

• How to Use the GDI Generator in a Graphmart
• GDI Generator Query Syntax
• GDI Generator Example Queries

How to Use the GDI Generator in a Graphmart

To invoke the GDI Generator in a data layer, you add a Direct Load Step to the layer. In the Direct Load Step, you compose a SPARQL query that incorporates the GDI Generator parameters as detailed below in GDI Generator Query Syntax.

Why Use a Direct Load Step?

It is important to use a Direct Load Step with the RDF and Ontology Generators because it is the only type of step with the ability to manage the generated ontologies (models). An ontology that is generated in a Direct Load Step is automatically registered in Anzo. The registered model is linked to and managed by the data layer that contains the step. If an Ontology Generator query is changed, additional Direct Load Steps are added to the same layer, or the underlying source schema changes, the managed model is automatically updated when the graphmart is reloaded or refreshed. See Managed Model Details below for important details about layer-managed models.

Managed Model Details

Though an ontology that is generated in a Direct Load Step is registered in Anzo and is available for viewing in the Model editor, the model is owned and managed by the data layer that contains the Direct Load Step. That means any manual changes made to the model outside of the step, such as from the Model editor, will be overwritten any time the graphmart or layer is refreshed or reloaded. Do not modify generated managed models except by editing (or adding) Direct Load Step queries.

There is only one managed model per layer. If you include multiple Direct Load Steps in the same layer, they will all update the same ontology. This functionality can be useful if you want to align the data and generated model across multiple steps. If you have multiple sources that are not intended to align or update the same model, create separate layers.

If you delete a layer that includes a managed model, the model is also deleted. Use caution when referencing a managed model outside of a graphmart. For example, if you create a dataset and reference a managed model when you select the ontology, the reference will break if the data layer that manages the model is deleted.

GDI Generator Query Syntax

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

# PREFIX Clause
PREFIX s:    <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX owl:  <http://www.w3.org/2002/07/owl#>
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#>

#Result Clause
INSERT {
   GRAPH ${targetGraph} {
     ?s ?p ?o .
  }
}
WHERE
{
	SERVICE <http://cambridgesemantics.com/services/DataToolkit>
    {
      ?data a s:source_type ;      
# Based on the source_type, additional connection and input parameters are
# available. The options below are valid for all sources. For source-related
# options, see Source-Specific Properties in the GDI property reference. 
        s:url "string" ;
        [ s:model "class_name_for_this_source" ; ]
        [ s:username "string" ; ]
        [ s:password "string" ; ]
        [ s:timeout int ; ]
        [ s:maxConnections int ; ]
        [ s:batching boolean | int ; ]
        [ s:paging [ pagination_options ; ]
        [ s:concurrency int | [ list_of_properties ] ; ]
        [ s:rate int | "string" ; ]
        [ s:locale "string" ; ]
        [ s:sampling int ; ]
        [ s:selector "string" | [ list ] ; ]
        [ s:key ("string") ; ]
        [ s:reference [ s:model "string" ; s:using ("string") ]
        [ s:formats [ datatype_formatting_options ] ; ]
        [ s:normalize boolean | [ source_normalization_rules ] ; ]
        [ s:count ?variable ; ]
        [ s:offset int ; ]
        [ s:limit int ; ] .

      # Multiple data sources can be merged if they project a similar set
      # of output variables. Make sure each source has a unique subject variable.
  
        [ ?unique_variable a s:source_type ;
          ...
        . ]

      ?rdf a s:RdfGenerator, s:OntologyGenerator ;
        s:as (?s ?p ?o);
        s:ontology ontology_uri ;
        s:base base_uri ;
        [ s:normalize boolean | [ global_normalization_rules ] ; ]
        .
      # Additional clauses such as BIND, FILTER
   }
}

PREFIX s:    <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX owl:  <http://www.w3.org/2002/07/owl#>
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#>

INSERT {
   GRAPH ${targetGraph} {
     ?s ?p ?o .
  }
}

?data a s:DbSource ;
  s:url "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.url}}" ;
  s:username "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.user}}" ;
  s:password "{{@db.eca4bfa83481f3638b93ab5fdf93ff9a.password}}" ;

s:concurrency [
  s:limit 24 ;
  s:nodes 4 ;
  s:executorsPerNode 8 ;
] ;

s:rate "90/minute" ;
s:rate "90 per minute" ;
s:rate "200000 every week" ;
s:rate "10000 every 6 hours" ;

GDI Generator Example Queries

This section includes sample queries that may be useful as a starting point for writing your own RDF and Ontology Generator queries.

• Basic Query that Generates RDF and Ontology for a JSON File
• Basic Query that Generates an Ontology for a Directory of CSV Files
• Query that Normalizes and Generates RDF and Ontology for a Database
• Query with Query Context that Normalizes and Generates RDF and Ontology for a Database
• Query for Multiple Sources that Generates RDF and Ontology with Resource Templates and Object Properties

Basic Query that Generates RDF and Ontology for a JSON File

PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>

INSERT {
   GRAPH ${targetGraph} {
     ?s ?p ?o .
  }
}
WHERE {
   SERVICE <http://cambridgesemantics.com/services/DataToolkit> {

      ?data a s:FileSource ;
         s:model "People" ;
         s:url "/opt/shared-files/json/people.json" .

      ?rdf a s:RdfGenerator , s:OntologyGenerator ;
         s:as (?s ?p ?o) ;
         s:ontology <http://cambridgesemantics.com/ontologies/People> ;
         s:base <http://cambridgesemantics.com/data/> .
  }
}

Basic Query that Generates an Ontology for a Directory of CSV Files

PREFIX s:  <http://cambridgesemantics.com/ontologies/DataToolkit#>

INSERT {
   GRAPH ${targetGraph} {
     ?s ?p ?o .
  }
}
WHERE {
   SERVICE <http://cambridgesemantics.com/services/DataToolkit> {

      ?data a s:FileSource ;
         s:model "Sales" ;
         s:url "/opt/shared-files/csv/sales" ;
         s:format [
            s:delimiter "," ;
            s:headers true ;
            s:comment "#" ;
            s:quote "\"" ;
            s:maxColumns 22 ;
         ] .

      ?rdf a s:OntologyGenerator ;
         s:as (?s ?p ?o) ;
         s:ontology <http://cambridgesemantics.com/ontologies/Sales> ;
         s:base <http://cambridgesemantics.com/data/> .
  }
}

Query that Normalizes and Generates RDF and Ontology for a Database

PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>

INSERT {
   GRAPH ${targetGraph} {
      ?s ?p ?o .
  }
}
WHERE {
   SERVICE <http://cambridgesemantics.com/services/DataToolkit> {

      ?data a s:DbSource ;
      s:url "jdbc:mysql://10.11.12.9/emrdbbig" ;
      s:username "root" ;
      s:password "sql1@#" ;
      s:normalize [
         s:model [
            s:removeStart "emr_" ;
            s:words "activity 'patient complaint' medication observation patient specialty study" ;
         ] ;
         s:field [ 
            s:removePartialPrefix true ;
            s:words "provider description start end drug complaint date medication normal code
                     observation product active dose generic route admin strength collection
                     activity home first last status first year birth death directed complex
                     period age flag gender language" ;
         ] ;
      ] .

      ?rdf a s:RdfGenerator , s:OntologyGenerator ;
      s:as (?s ?p ?o) ;
      s:ontology <http://cambridgesemantics.com/ontologies/EMR> ;
      s:base <http://cambridgesemantics.com/EMR> .
  }
}

Query with Query Context that Normalizes and Generates RDF and Ontology for a Database

The query below references a Query Context to supply the username and password for the database connection.

PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>

INSERT {
   GRAPH ${targetGraph} {
      ?s ?p ?o .
  }
}
WHERE {
   SERVICE <http://cambridgesemantics.com/services/DataToolkit> {

      ?data a s:DbSource ;
         s:url "jdbc:sqlserver://localhost;databaseName=AdventureWorks2012" ;
         s:username "{{@db.username}}" ;
         s:password "{{@db.password}}" ;
         s:schema "Production", "HumanResources", "Person", "Sales", "Purchasing" ;
         s:normalize [ 
            s:model [
               s:localNamePrefix "C_" ;
               s:localNameSeparator "_" ;
               s:match [ s:pattern "(.+)Enlarged" ; s:replace "$1" ] ;
            ] ;
            s:field [
               s:localNamePrefix "P_" ;
               s:localNameSeparator "_" ;
               s:ignore "rowguid ModifiedDate" ;
               s:match (
                  [ s:pattern "(.+)GUID$" ; s:replace "$1" ]
                  [ s:pattern "(.+)ID$" ; s:replace "$1" ]
               ) ;
            ] ;
         ] .

      ?rdf a s:RdfGenerator, s:OntologyGenerator ;
         s:as (?s ?p ?o) ;
         s:ontology <http://cambridgesemantics.com/ontologies/AdventureWorks> ;
         s:base <http://cambridgesemantics.com/AdventureWorks> .
  }
}

Query for Multiple Sources that Generates RDF and Ontology with Resource Templates and Object Properties

This query also includes global normalization rules for normalizing the data across all Data Sources.

PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>

INSERT {
   GRAPH ${targetGraph} {
      ?s ?p ?o .
  }
}
WHERE { 
   SERVICE <http://cambridgesemantics.com/services/DataToolkit> {

      ?event a s:FileSource ;
         s:model "event" ;
         s:url  "/opt/shared-files/csv/events.csv" ;
         s:key ("EVENT_ID") .

      ?listing a s:FileSource ;
         s:model "listing" ;
         s:url " /opt/shared-files/csv/listings.csv" ;
         s:key ("LIST_ID") ;
         s:reference [ s:model "event" ; s:using ("EVENT_ID") ; s:key ("EVENT_ID") ] .

      ?date a s:FileSource ;
         s:model "date" ;
         s:url  "/opt/shared-files/csv/event_dates.csv" ;
         s:key ("DATE_ID") ;
         s:reference [ s:model "event" ; s:using ("EVENT_ID") ; s:key ("EVENT_ID") ] .

      ?venue a s:FileSource ;
         s:model "venue" ;
         s:url " /opt/shared-files/csv/venues.csv" ;
         s:key ("VENUE_ID") ;
         s:reference [ s:model "event" ; s:using ("EVENT_ID") ; s:key ("EVENT_ID") ] .
     
      ?sale a s:FileSource ;
         s:model "sale" ;
         s:url " /opt/shared-files/csv/sales.csv" ;
         s:key ("SALE_ID") ;
         s:reference [ s:model "event" ; s:using ("EVENT_ID") ; s:key ("EVENT_ID") ] ;
         s:reference [ s:model "listing" ; s:using ("LIST_ID") ; s:key ("LIST_ID") ] .

      ?rdf a s:RdfGenerator, s:OntologyGenerator ;
         s:as (?s ?p ?o) ;
         s:ontology <http://cambridgesemantics.com/tickets> ;
         s:base <http://cambridgesemantics.com/data> ;
         s:normalize [ 
            s:all [
               s:casing s:UPPER ;
               s:localNameSeparator "_" ;
            ] ;
         ] .
  }
}

Related Topics

• Directly Loading a Data Source (Direct Load Step)