Excel Add-In for Elasticsearch

Build 24.0.9060

Custom Schema Definitions

View schemas persist the relational structure the add-in infers for Elasticsearch types and queries. To provide an example of how custom schemas work, we will use the below mapping (where 'insured' is the name of the table).

{
  "insured": {
    "properties": {
      "name": { "type":"string" },
      "address": {
        "street": { "type":"string" },
        "city": { "type":"string" },
        "state": { "type":"string" }
      },
      "insured_ages": { "type": "integer" },
      "vehicles": {
        "type": "nested",
        "properties": {
          "year": { "type":"integer" },
          "make": { "type":"string" },
          "model": { "type":"string" },
          "body_style" { "type": "string" }
        }
      }
    }
  }
}

Also, consider the following example data for the above mapping:

{
  "_source": {
    "name": "John Smith",
    "address": {
      "street": "Main Street",
      "city": "Chapel Hill",
      "state": "NC"
    },
    "insured_ages": [ 17, 43, 45 ], 
    "vehicles": [
      {
        "year": 2015,
        "make": "Dodge",
        "model": "RAM 1500",
        "body_style": "TK"
      },
      {
        "year": 2015,
        "make": "Suzuki",
        "model": "V-Strom 650 XT",
        "body_style": "MC"
      },
      {
        "year": 2012,
        "make": "Honda",
        "model": "Accord",
        "body_style": "4D"
      }
    ]
  }
}

Defining a Custom Schema

Schemas persisted when GenerateSchemaFiles is set are placed into the folder specified by the Location property. For example, set GenerateSchemaFiles to "OnUse" and execute a SELECT query:

SELECT * FROM insured

You can then change column behavior in the resulting schema. The following schema uses the other:xPath property to define where the data for a particular column should be retrieved from. Using this model you can flatten arbitrary levels of hierarchy.

The es_index and es_type attributes specify the Elasticsearch index and type to retrieve. The es_index and es_type attributes give you the flexibility to use multiple schemas for the same type. If es_type is not specified, the filename determines the collection that is parsed.

Below is an example is an example of the column behavior markup. You can find a complete schema in Custom Schema Example.

  <rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">  
  
    <rsb:info title="StaticInsured" description="Custom Schema for the Elasticsearch insured data set.">  
      <!-- Column definitions -->
      <attr name="_id"                         xs:type="string"  other:xPath="_id"                                                         other:sourceField="_id"                 other:analyzed="true"  />
      <attr name="_score"                      xs:type="double"  other:xPath="_score"                                                      other:sourceField="_score"              other:analyzed="true"  />
      <attr name="name"                        xs:type="string"  other:xPath="_source/name"                                                other:sourceField="name"                other:analyzed="true"  />
      <attr name="address.street"              xs:type="string"  other:xPath="_source/address/street"                                      other:sourceField="address.street"      other:analyzed="true"  />
      <attr name="address.city"                xs:type="string"  other:xPath="_source/address/city"                                        other:sourceField="address.city"        other:analyzed="true"  />
      <attr name="address.state"               xs:type="string"  other:xPath="_source/address/state"                                       other:sourceField="address.state"       other:analyzed="true"  />
      <attr name="insured_ages"                xs:type="string"  other:xPath="_source/insured_ages"          other:valueFormat="aggregate" other:sourceField="insured_ages"        other:analyzed="false" />
      <attr name="insured_ages.0"              xs:type="integer" other:xPath="_source/insured_ages[0]"                                     other:sourceField="insured_ages"        other:analyzed="false" />
      <attr name="vehicles"                    xs:type="string"  other:xPath="_source/vehicles"              other:valueFormat="aggregate" other:sourceField="vehicles"            other:analyzed="true"  />
      <attr name="vehicles.0.year"             xs:type="integer" other:xPath="_source/vehicles[0]/year"                                    other:sourceField="vehicles.year"       other:analyzed="true"  />
      <attr name="vehicles.0.make"             xs:type="string"  other:xPath="_source/vehicles[0]/make"                                    other:sourceField="vehicles.make"       other:analyzed="true"  />
      <attr name="vehicles.0.model"            xs:type="string"  other:xPath="_source/vehicles[0]/model"                                   other:sourceField="vehicles.model"      other:analyzed="true"  />
      <attr name="vehicles.0.body_style"       xs:type="string"  other:xPath="_source/vehicles[0]/body_style"                              other:sourceField="vehicles.body_style" other:analyzed="true"  />
  
      <input name="rows@next" desc="Internal attribute used for paging through data."  />
    </rsb:info>  
  
  
    <rsb:set attr="es_index" value="auto"/>
    <rsb:set attr="es_type"  value="insured"/>
  
  </rsb:script>
  

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060