Relational Model
The CData Excel Add-In 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.
_id | name | address_street | address_city | address_state | insured_ages | year | make | model | body_style | _insured_id | _vehicles_c_id | |
1 | John Smith | Main Street | Chapel Hill | NC | [ 17, 43, 45 ] | 2015 | Dodge | RAM 1500 | TK | 1 | 1 | |
1 | John Smith | Main Street | Chapel Hill | NC | [ 17, 43, 45 ] | 2015 | Suzuki | V-Strom 650 XT | MC | 1 | 2 | |
1 | John Smith | Main Street | Chapel Hill | NC | [ 17, 43, 45 ] | 1992 | Harley Davidson | FXR | MC | 1 | 3 | |
2 | Joseph Newman | Oak Street | Raleigh | NC | [ 23, 25 ] | 2010 | Honda | Accord | SD | 2 | 4 | |
2 | Joseph Newman | Oak Street | Raleigh | NC | [ 23, 25 ] | 2008 | Honda | Civic | CP | 2 | 5 |
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.