Data Model
The CData Excel Add-In for Elasticsearch models Elasticsearch entities in relational Tables, Views, and Stored Procedures.
Tables
The table definitions are dynamically retrieved. When you connect, the add-in connects to Elasticsearch and retrieves the schemas, list of tables, and the metadata for the tables by querying the Elasticsearch REST server.Searching with SQL describes in further detail how the tables are dynamically retrieved.
Views
Views are created from Elasticsearch aliases and the definitions are dynamically retrieved. When you connect, the add-in connects to Elasticsearch and retrieves the list of views and the metadata for the views by querying the Elasticsearch REST server.Views are treated in a similar manner to Tables and thus exhibit similar behavior. There are some differences in the background though which are a direct result of how aliases work within Elasticsearch. (Note: In the following description, 'alias', 'index', 'type', and 'field' are referring to the Elasticsearch objects and not directly to anything within the add-in).
Views (aliases) are tied to an index and thus span all the types within an index. Additionally aliases can span multiple indices. Therefore you may see an alias (view) listed multiple times under different schemas (index). When querying the view, regardless of the schema specified, data will be retrieved and returned for all indices and types associated with the corresponding alias. Thus the generated metadata will contain a column for each field within each type of each index associated with the alias.
Searching with SQL describes in further detail how the views are dynamically retrieved.
The ModifyIndexAliases stored procedure can be used to create index aliases within Elasticsearch.
In addition to the Elasticsearch aliases, an '_all' view is returned which enables querying the _all endpoint to retrieve data for all indices in a single query. Given how many indices and documents the _all view could cover, certain queries agains the '_all' view could be very expensive. Additionally, for scanning for table metadata, as governed by RowScanDepth, will be less accurate for '_all' views that cover very large or very heterogenous indices. See Automatic Schema Discovery for more information about this.