Querying an Elasticsearch Source

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

• Query Syntax
• Query DSL and Filter Mapping
• Query Examples

Query Syntax

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

# PREFIX Clause
PREFIX s:     <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX es:    <http://elastic.co/search/>
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:ElasticSource ;
        s:url "string" ;
        [ s:username "string" ; ]
        [ s:password "string" ; ]
        [ s:property [ s:name "string" ; s:value "string" ; ]
        [ es:aggregations [ rdf_list ] ; ]
        [ es:config "string" ; ]
        [ es:document "string" ; ]
        [ es:field "string" | ?variable ; ]
        [ es:highlight [ rdf_list ] ; ]
        [ es:html boolean ; ]
        [ es:index "string" ; ]
        [ es:minScore float ; ]
        [ es:query "string" | [ rdf_list ] ; ]
        [ es:routing "string" ; ]
        [ es:searchAfter [ rdf_list ] ; ]
        [ es:size int ; ]
        [ es:source boolean | [ rdf_list ] ; ]
        [ s:timeout int ; ]
        [ s:batching boolean | int ; ]
        [ s:paging [ pagination_options ; ]
        [ s:concurrency int | [ list_of_properties ] ; ]
        [ s:rate int | "string" ; ]
        [ s:partitionBy "string" | ?variable ; ]
        [ 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: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
   }
}

PREFIX s:    <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX es:   <http://elastic.co/search/>
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/>

?data a s:ElasticSource ;
  s:url "{{@es.hostname}}:{{@es.port}}" ; 
  s:username "{{@es.username}}" ;
  s:password "{{@es.password}}" ;

s:credentials [
  s:username “username” ;
  s:password “password” ;
] ;

s:property [ s:name "custom_property_name" ; s:value "custom_value" ]

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" ;

Query DSL and Filter Mapping

The vocabulary used in GDI queries against an ElasticSource closely mimics the Elasticsearch Query DSL. The table below shows a side-by-side view of a DSL query that is mapped to SPARQL using the es:query property:

{
 "query": {
  "bool" : {
   "must" : {
    "term" : { "user.id" : "kimchy" }
   },
   "filter": { 
    "term" : { "tags" : "production" }
   },
   "must_not" : {
    "range" : {
     "age" : { "gte" : 10, "lte" : 20 }
    }
   },
   "should" : [
    { "term" : { "tags" : "env1" } },
    { "term" : { "tags" : "deployed" } }
   ],
   "minimum_should_match" : 1,
   "boost" : 1.0
  }
 }
}

es:query [
  a es:BoolQuery ;
  es:must [
   a es:TermQuery ;
   es:field "user.id" ;
   es:value "kimchy" ;
  ] ;
  es:filter [
   a es:TermQuery ;
   es:field "tags" ;
   es:value "production" ;
  ] ;
  es:mustNot [ 
   a es:RangeQuery ;
   es:field "age" ;
   es:gte 10 ;
   es:lte 20 ;
  ] ;
  es:should (
   [ a es:TermQuery ; es:field "tags" ; es:value "env1" ]
   [ a es:TermQuery ; es:field "tags" ; es:value "deployed" ]
  ) ;
  es:minimumShouldMatch 1 ;
  es:boost 1.0 ;
] ;

The following example SERVICE clause with comments provides details about how the GDI es:query property can be mapped to DSL:

SERVICE <http://cambridgesemantics.com/services/DataToolkit> {
  ?data a es:ElasticSource ;
    s:url "http://localhost:9200/" ;
# When the value of es:query is a simple literal,
# it is mapped to an Elastic query string query.
    es:query "literal"

# When the value of es:query is an RDF list,
# you can specify other query types,
# such as a match query:
    es:query [ 
      a es:MatchQuery ;
      es:field "title" | ?title ; # field can be a literal or bound variable
      es:query "moby dick" ;
    ] ;
# or a boolean query:
    es:query [ 
      a es:BoolQuery ;
      es:should ([
        a es:RangeQuery ;
        es:field ?amount ;
        es:gt 500 ;
        es:lt 1000 ;
      ] [
        a es:TermQuery ;
        es:field ?status ;
        es:value 'late' ;
      ]) ;
    ] ;
}

Filter Mapping

Filtering can be performed inside the es:query list or you can add a FILTER clause to the query. For example, the table below shows the SPARQL snippet above expressed as a FILTER clause.

es:query [
  a es:BoolQuery ;
  es:must [
   a es:TermQuery ;
   es:field "user.id" ;
   es:value "kimchy" ;
  ] ;
  es:filter [
   a es:TermQuery ;
   es:field "tags" ;
   es:value "production" ;
  ] ;
  es:mustNot [ 
   a es:RangeQuery ;
   es:field "age" ;
   es:gte 10 ;
   es:lte 20 ;
  ] ;
  es:should (
   [ a es:TermQuery ; es:field "tags" ; es:value "env1" ]
   [ a es:TermQuery ; es:field "tags" ; es:value "deployed" ]
  ) ;
  es:minimumShouldMatch 1 ;
  es:boost 1.0 ;
] ;

FILTER(?user_id = "kimchy" && 
       ?tags = "production" &&
       !(?age >= 10 && ?age <= 20) &&
       (?tags == "env1" || ?tags == "deployed"))

The table below shows each of the supported ElasticSource FILTER translations. Only expressions matching the list below will be translated by the GDI. If the expression is of the form value <= ?field, the inequality is flipped to ?field > value before translating.

Query Examples

• General Query
• Aggregations
• Highlighting

General Query

The following example queries any Elasticsearch indexes that are loaded in the graphmart for which you run the query. No configuration is needed because Anzo manages the indexes that it loads and uses predictable naming conventions and aliases.

PREFIX docm: <http://cambridgesemantics.com/ontologies/2011/07/DocumentMetadata#>
PREFIX es: <http://elastic.co/search/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX services: <http://cambridgesemantics.com/services/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?title
WHERE {
  SERVICE <http://cambridgesemantics.com/services/DataToolkit> {
    ?data a es:ElasticSource;
      s:selector "unstructuredfile" ;
      es:query "string" ; # input the text search string
      es:fields "fullText" ;
      ?title (xsd:string) .
  }
}

Aggregations

The following example query performs terms aggregations.

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

SELECT DISTINCT *
WHERE {
  SERVICE <http://cambridgesemantics.com/services/DataToolkit> {
    ?data a es:ElasticSource;
    s:url "https://{{@es.hostname}}:{{@es.port}}/" ;
    s:username "{{@es.username}}" ;
    s:password "{{@es.password}}" ;
    es:index "templated_consumption_es" ;
    es:query "*ELM*" ;
    ?instance () ;
    es:aggregations [
      ?artifactTypes [
        a es:TermsAggregation ;
        es:field ?artifactType ;
        es:meta [
          ?label "artifactType" ;
        ] ;
        ?value () ;
        ?count () ;
      ] ;
      ?fileTypes [
        a es:TermsAggregation ;
        es:field ?fileType ;
        es:meta [
          ?label "fileType" ;
        ] ;
        ?value () ;
        ?count () ;
      ] ;
      ?managedBys [
        a es:TermsAggregation ;
        es:field ?managedBy ;
        es:meta [ 
          ?label "managedBy" ;
        ] ;
        ?value () ;
        ?count () ;
      ] ;
    ] .
  }
}

Highlighting

The following example configures highlighting for fragments from the actor field.

PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX es: <http://elastic.co/search/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
​SELECT * 
WHERE {
  SERVICE TOPDOWN <http://cambridgesemantics.com/services/DataToolkit>
  {
    ?data a es:ElasticSource ;
      ​es:url "http://localhost:9200/" ;
      es:index "films" ;
      es:html false ;
      es:query "Clint" ;
      es:field ?actor, ?director ;
      es:highlight [
        es:field ?actor ;
        es:type "plain" ;
        es:fragmentSize 200 ;
        es:numberOfFragments 10 ;
        es:preTags "<mark hit='true'>" ;
        es:postTags "</mark>" ;
      ] ;
      s:selector "film" ;
      ?actor (xsd:string) ;
      ?awards (xsd:string) ;
      ?director (xsd:string) ;
      ?image (xsd:string) ;
      ?length (xsd:long) ;
      ?popularity (xsd:long) ;
      ?subject (xsd:string) ;
      ?title (xsd:string) ;
      ?year (xsd:long) ;
​      ?score () ;
      ?id () ;
      ?highlights [
        ?field () ;
        ?fragment () ;
      ] .
  FILTER(?year = 1990 || ?length > 103)
  FILTER(REGEX(?title, "Manhattan", "q") || REGEX(?subject, "Comedy", "q") || REGEX(?subject, "Drama", "q"))
  }
}