ODBC Driver for Elasticsearch

Build 24.0.9060

Relational Model

The CData ODBC Driver for Elasticsearch can be configured to create a relational model of the data, treating nested documents as individual tables containing a primary key and a foreign key that links to the parent document. This is particularly useful if you need to work with your Elasticsearch data in existing BI, reporting, and ETL tools that expect a relational data model.

Joining Nested Arrays as Tables

With DataModel set to "Relational", any JOINs are controlled by the query. Any time you perform a JOIN query, the Elasticsearch index will be queried once for each table (nested document) included in the query.

Example

Below is a sample query against the sample document in Raw Data, using a relational model.

Query

The following query explicitly JOINs the insured and vehiclestables.

SELECT 
  [insured].[_id], 
  [insured].[name], 
  [insured].[address.street] AS address_street, 
  [insured].[address.city.first] AS address_city, 
  [insured].[address.state.last] AS address_state, 
  [insured].[insured_ages], 
  [vehicles].[year], 
  [vehicles].[make], 
  [vehicles].[model], 
  [vehicles].[body_style],
  [vehicles].[_insured_id],
  [vehicles].[_c_id]
FROM 
  [insured]
JOIN 
  [vehicles] 
ON 
  [insured].[_id] = [vehicles].[_insured_id]

Results

In the example query, each vehicle document is JOINed to its parent insured object to produce a table with 5 rows.

_idnameaddress_streetaddress_cityaddress_stateinsured_agesyearmakemodelbody_style_insured_id_vehicles_c_id
1John SmithMain StreetChapel HillNC[ 17, 43, 45 ]2015DodgeRAM 1500TK11
1John SmithMain StreetChapel HillNC[ 17, 43, 45 ]2015SuzukiV-Strom 650 XTMC12
1John SmithMain StreetChapel HillNC[ 17, 43, 45 ]1992Harley DavidsonFXRMC13
2Joseph NewmanOak StreetRaleighNC[ 23, 25 ]2010HondaAccordSD24
2Joseph NewmanOak StreetRaleighNC[ 23, 25 ]2008HondaCivicCP25

See Also

  • Automatic Schema Discovery: Configure the columns reported in the table schemas.
  • FreeForm;: Use dot notation to select nested data.
  • VerticalFlattening;: Access nested object arrays as separate tables.
  • JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

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