This documentation page assumes that you already have a SeekTable account. You can create free account by signing up.

Pivot table by ElasticSearch

Native ElasticSearch connector can be used for real-time reports (pivot tables, charts, usual data tables) and ad-hoc queries without need to use Elastic Query DSL directly. You may found that in many cases SeekTable can be a good alternative to Kibana - especially if you need table reports - which also allows you to publish live web reports by ElasticSearch in a simple way.

There are no any limitations on the ElasticSearch index size and performance is limited only by the power of your ElasticSearch cluster.

Looking for embedded solution? Try PivotData microservice which can be seamlessly integrated into any web application and generate pivot tables by ElasticSearch with simple web API.

How to configure ElasticSearch data source

  1. Click on "Connect to Database" item at "Cubes" view, or just open this link (ensure that you're logged in).
  2. Select "ElasticSearch" in Data Source Type selector: ElasticSearch connection settings
  3. Fill all required fields:
    Cube Name
    short title that describes this data source
    Connection URL
    Base URL of your ElasticSearch API endpoint, for example:
    https://ReadOnly:nxxr8qt7n26c7jwijhwg1ipnarf402yn@thorin-us-east-1.searchly.com
    Index
    the name of the index to query. In ElasticSearch you can query multiple indexes, so you can specify them as index1,index2 or use wildcards like logs-2018-*.
    Doc Type
    you might need to specify mapping type if you use legacy version of ElasitcSearch (5.x or earlier). For ElasticSearch 6.x no need to specify doc type option.
    Filter Expression
    here you may define filtering conditions for Elastic query with SQL-like syntax, for example:
    timestamp >= "2018-10-20":datetime
    • condition syntax: document_field condition "value"[:datatype]
      where condition can be: <, <=, >, >=, =, !=, like; datatype can be: int32, int64, decimal, double, datetime.
    • boolean and/or operators: C1 and C2, C1 or C2
  4. Infer dimensions and measures by columns option: keep it checked to determine dimensions and measures by first N documents - in this case you don't need to define Dimensions and Measures by yourself. You can edit configuration later and remove excessive elements, or customize automatically determined ones.
  5. Click on "Save" button.

If everything is fine you should see a new cube dashboard with the list of available dimensions/measures. In case of connection error you'll see an orange box with the error message; don't forget to ensure that ElasticSearch API can be accessed by SeekTable server and it is not blocked by firewall.
If you specified "Infer dimensions and measures" option and get a cube with no dimensions most likely you've specified non-existing mapping type in "Doc Type".

Dimensions setup

ElasticSearch dimensions setup
Type
Field: dimension value is a document field or script field (script code should be provided as first "Parameter").
Expression: dimension is defined as calculated field with custom formula that uses another dimensions as arguments (formula and arguments should be specified in "Parameters").
Name
Unique dimension identifier. For Type=Field this is document or sub-document field specifier.
Label
User-friendly dimension title (optional).
Format
Custom format string (.NET String.Format) for dimension values (optional). Examples:
  • for number values: ${0:0.##} → $10.25
  • for date values: {0:yyyy-MM-dd} → 2017-05-25
Parameters
For Type=Field: you can specify custom script field with "painless" expression syntax. For example:
(doc["registered"].empty ? null : doc["registered"].date.year)
(extracts year value from "registered" date field). Also you can specify "number" for 2-nd parameter if script result is a number (this affects sorting in flat table reports).
For Type=Expression: you can specify custom formula (1-st parameter) and dimension names for the arguments (2-nd, 3-rd etc parameter).

Measures setup

ElasticSearch measures setup
Type
Count: the number of aggregated documents.
Sum: the total sum of a numeric field.
Average: the average value of a numeric field.
Min: the minimal value of a column.
Max: the maximum value of a column.
FirstValue: custom acummulator aggregation pipeline expression.
Expression: measure defined as calculated field.
Name
Explicit unique measure identifier. You can leave it blank (for any measure types except "Expression") to generate the name automatically.
Label
User-friendly measure caption (optional).
Format
Custom format string (.NET String.Format) for measure values (optional). Example:
  • ${0:0.##} → $10.25
Parameters
For Type=Count: no parameters needed.
For Type=Sum/Average/Min/Max: document field or field path to aggregate.
For Type=Expression: first parameter is formula expression, and next parameters are names of measures used as arguments in the expression.

Report parameters setup

Report parameter is used when you need to specify some filtering condition by user-defined variable and use it in the ElasticSearch query. Typical usage of report parameters:

Name
Unique (for cube) parameter identifier.
Label
User-friendly parameter caption for UI (optional).
Data Type
String: text-based value.
Int32: 32-bit integer (max value is 2,147,483,647).
Int64: 64-bit integer (max value is 9,223,372,036,854,775,807).
Decimal: Fixed-point number with max 28 significant digits. Decimal point is '.' character.
DateTime: date or datetime value (in case of date this will be datetime value with 0:00:00 time). Date value should be specified as string in YYYY-MM-DD format.
Boolean: accepts only 'True' or 'False' value.
Multiple Values
If checked parameter can accept several values (as array, in UI user can enter them as comma-separated string). Multivalue parameter can be used only with IN condition.
Default Value
Defines default value of this parameter. Empty means 'not defined'.

When parameter is defined it can be used in Filter Expression as following:

"1"="1" @paramName[ and name.keyword="{0}":var ]

Parameter syntax notes:

@
identifies that this is a placeholder for the parameter
paramName
parameter Name
[ ]
expression between square brackets is added to Filter Expression when parameter is defined.
"{0}":var
placeholder for the parameter value.

In this sample parameter name is paramName and ElasticSearch document field to filter is name.keyword.

Troubleshooting

ERROR: Fielddata is disabled on text fields by default. Set fielddata=true on [some_text_field] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.

This error appears when you try to aggregate by text field and your ElasticSearch index doesn't have original values for this field. In most cases you can use .keyword suffix and enable unindexed values for aggregation as described in official ElasticSearch documentation.

ERROR: failed to create query (caused_by: { "type": "number_format_exception", "reason": "For input string: \"some_keyword\"" }): ElasticSearch query

You may get an error like this ('caused_by' may be a bit different) with flat table report type and when you specify a keyword to filter without field name hint: in this case SeekTable produces OR condition for all fields selected as columns, and one of them is not comparable with the specified keyword value.
Workaround: specify a hint for the keyword in the filter, for example: name:John.