Connecting Bold BI to Elastic Search data source

The Bold BI Cloud Dashboard designer supports connecting to the Elastic Search database using the SQL Live Query.

Supported Server Versions:

  • Elasticsearch 6.8 and above
  • Amazon Elastic Search 7.10 and above

Choose an Elastic Search data source

To choose an Elastic Search data source, follow these steps:

  1. Click on the Data Sources icon in the configuration panel to add a new data connection.

Data source icon

  1. Click the CREATE NEW button to launch a new connection from the connection panel.

Create data source

  1. Select the Elastic Search connection in the connection panel.

Choose data source

NOTE: You can also create a data source from the Bold BI home page by clicking the Data Sources menu from the left menu panel and Create Data Source from the data sources page.

Select data source

An Elastic Search data source can be accessed in Bold BI using the live connection mode.

Create an Elastic Search data source

After selecting a data source, the NEW DATA SOURCE configuration panel will open. Follow the steps below to create an Elastic Search data source:

  1. Enter a name and description (optional) for the data source.
  2. Select a connection type for Elastic Search from the Connection Type drop-down box

There are two connection types available in the Elastic Search connector:

  • Elasticsearch SQL
  • Open Distro

Elastic search Connection

There are two connection types available in a data source

  • Live mode
  • Extract mode

Connect using the Elasticsearch SQL connection type in Live mode

  1. Enter a valid Elastic Search server or hostname in the Server Name text box.
  2. Enter the port in which the server is running in the port text box.
  3. The Authentication type provided is determined by the configuration of the Elastic Search server.
  • None
  • Basic HTTP Authentication

Choose Authentication

  1. If the Elastic Search server uses Basic HTTP Authentication in the Authentication type, then enter a valid Elastic Search username in the Username text box and enter a valid Elastic Search password in the Password text box.

  2. Choose the Live mode option for this connection.

  3. Select the database you want to query from the listed databases associated with the given Elastic Search server in the database drop-down box.

  4. Click Connect to connect the Elastic Search server with the configured details.

Data source connection

NOTE: To connect data source with SSH, enable the SSH check box in the NEW DATA SOURCE configuration panel and enter the required credentials.

Enable SSH

Connect using the Elasticsearch SQL connection type in Extract mode

  1. Enter a valid Elastic Search server or hostname in the Server Name text box.
  2. Enter the port in which the server is running in the port text box.
  3. The Authentication type provided is determined by the configuration of the Elastic Search server.
  • None
  • Basic HTTP Authentication

Choose Authentication

  1. If the Elastic Search server uses Basic HTTP Authentication in the Authentication type, then enter a valid Elastic Search username in the Username text box and enter a valid Elastic Search password in the Password text box.
  2. Choose the Extract mode option for this connection.
  3. Select a database that you want to query in the listed database associated with the given Elastic Search server in the database drop-down box.
  4. Click Connect to connect the Elastic Search server with the configured details.

Data source connection

Extract mode connection

  1. Switching to Extract mode, Extract Engine will become visible.

    Extract Engine

  2. In Bold BI Extract, The data will be extracted in Bold BI itself.

  3. When switching to Data Hub, Data extraction is handled through the Data Hub. An equivalent pipeline is created, utilizing the Data Hub’s Extract, Transform, Load (ETL) capabilities to filter, optimize, or transform the data, and to generate new tables.

  4. To view the equivalent pipeline created for data extraction, navigate to the Query Designer page and click on View Pipeline.

    Refresh Setting

  5. When the data refresh is configured for data sources that uses the Data Hub Extract Engine, the refresh will be managed within the Data Hub.

Connect using Open Distro connection type

Prerequisites

To connect to the Amazon Elasticsearch service through Open Distro, ensure that you have enabled the necessary settings.

PUT _opendistro/_sql/settings
“persistent”:{
“opendistro.sql.cursor.enabled”: true
}
}

Please follow these steps to connect using the Open Distro connection type.

  1. Enter a valid Elastic Search server or hostname in the Server Name text box.
  2. Enter the port, in which the server is running in the port text box.
  3. Enter a valid Elastic Search username in the Username text box.
  4. Enter a valid Elastic Search password in the Password text box.
  5. Select either Live or Extract mode of connection.
  6. Click Connect to connect the Elastic Search server with the configured details.

Open distro connection

NOTE: You can also connect through the Custom query support on the extract mode of connection by providing the valid SQL query in both the Elastic search SQL and Open distro connection types.

Custom query connection

Data Transformation

  1. After connecting to the data source, the data design view page will open. On this page, a list of schemas and tables from the Elastic Search server will be displayed in a treeview section.

Tree view

  1. Now, you can drag and drop the required table from the tree view list.

Drag and drop table

  1. After dragging and dropping the required table, you can perform data modeling tasks such as Joining Tables, Formatting Columns, Configure Data Filters, and Configure Expression Columns can be done.

Data Preview

  1. To view the data from the selected table, select the Update button.

Update values

  1. Now, the data from the selected table will be shown.

Table values retrieve

  1. Click Save to save the data source with a relevant name.

Save data source

NOTE: In the future, you can edit the connection information using the Edit Connection option and
Elastic Search datasource has the following Limitations:

• Joins are not supported and will result in an error message.
• Code view switching is restricted as applying grouping for sub queries is unsupported.
• Virtual scrolling is restricted for now as we did not have SQL function for the operation.
• The Top N option in initial filters is unsupported, as the join command is not supported.
• Expressions that use the today() and now() functions are unsupported in widgets.
• The data sampling feature is restricted, as applying a limit in a subquery is not supported.

Using IncludeFilterKey in URL Filters

In Elasticsearch, .keyword fields are used for exact-match filtering and offer better performance compared to analyzed text fields. Bold BI supports applying these sub-fields dynamically using the IncludeFilterKey property in embed URL filters.

How It Works

When you add IncludeFilterKey=<subField> in the URL, the specified sub-field is automatically applied to every column included in the filter.

Syntax

IncludeFilterKey=<subField>&&columnName=value

For Elasticsearch .keyword fields:

IncludeFilterKey=keyword

Example – Embed URL Filter

IncludeFilterKey=keyword&&(column4=item5|| column1=A00003)||column4=item99 This will be interpreted as:

  • column4.keyword = item5
  • column1.keyword = A00003
  • column4.keyword = item99

Keyword

Supported Values

You can use built-in Elasticsearch sub-fields such as keyword, raw, exact or any other sub-field defined in your index mapping.

NOTE: When a field uses the text data type, Elasticsearch breaks the value into tokens for full-text search. Because of this, exact comparisons using = in IF conditions will fail. If you need to compare the exact value, change the field type to keyword (or use the .keyword subfield if it already exists).

Connect using custom attribute and dashboard parameter

We have added support for custom attributes and dashboard parameters in the data source connection. You can now connect to the data source using custom attributes or dashboard parameters.

Custom Attribute

Custom

Dashboard Parameter

Dashboard Parameter

Note: Refer to the Dashboard Parameter Documentation and Custom Attributes Documentation for more details.

Connecting Bold BI to Elastic Search Data Source via REST API

Connect using Open Distro Connection Type

The detailed steps for connecting using an open distro connection type are explained in the following document. AWS Open Search

Connect using ElasticSearch SQL Connection Type

Prerequisites

Supported Server Versions:

  • Elasticsearch 6.8 and above

The type of data source that needs to be used is Elasticsearch.

Rest API - v4.0

Modes

Only the live mode data source can be created and edited through the REST API.

Parameters for creating Data Source

Parameters Details
ConnectionType

required 		`string`

Connection Type must be Elasticsearch SQL
Servername

required 		`string`

Server name or Host name of the connection
Port

required 		`string`

Port number used to connect to elasticsearch
AuthenticationType

optional 		`string`

AuthenticationType will be None or basichttpauthentication. By default, it is None.
Username

optional 		`string`

A valid username for the connection. Required for the basichttpauthentication
Password

optional 		`string`

A valid Password for the connection. Required for the basichttpauthentication
Database

required 		`string`

Enter a valid Database name or cluster name
Tablename

required 		`string`

Enter a valid Tablename
AdvancedSettings

optional 		`string`

Additional optional connection parameters can be provided. By default, it is empty.
IsSshConnection

optional 		`boolean`

Enable or disable SSH. By default, it is false.
SshServerName

optional 		`string`

Enter a valid Ssh Server name. By default, it is empty.
SshPort

optional 		`integer`

Enter a valid Ssh Port number.
SshUserName

optional 		`string`

Enter a valid Ssh Username. By default, it is empty.
SshPassword

optional 		`string`

Enter a valid Ssh Password. By default, it is empty.
Expressions

optional

`Array of Objects`

Parameters for edit the connection while upload the dashboard via API

Parameters Details
ConnectionType

required 		`string`

Connection Type must be Elasticsearch SQL
ServerName

required 		`string`

Server name or Host name of the connection
Port

required 		`string`

Port number used to connect to elasticsearch
AuthenticationType

optional 		`string`

AuthenticationType will be None or basichttpauthentication. By default, it is None.
UserName

optional 		`string`

A valid username for the connection. Required for the basichttpauthentication
Password

optional 		`string`

A valid Password for the connection. Required for the basichttpauthentication
Database

required 		`string`

Enter a valid Database name or cluster name
Schema

required 		string

Enter a valid Schemaname
AdvancedSettings

optional 		`string`

Additional optional connection parameters can be provided. By default, it is empty.
IsSshConnection

optional 		`boolean`

Enable or disable SSH. By default, it is false.
SshServerName

optional 		`string`

Enter a valid Ssh Server name. By default, it is empty.
SshPort

optional 		`integer`

Enter a valid Ssh Port number.
SshUserName

optional 		`string`

Enter a valid Ssh Username. By default, it is empty.
SshPassword

optional 		`string`

Enter a valid Ssh Password. By default, it is empty.

Parameters for adding expressions when creating Data Source

Parameters Details
Name

required

`string`

Name of the Expression
Expression



required

`string`

Expression


Parameters for editing Data Source

NOTE: To edit a Data Source via API, all parameters are optional. Only the parameter that needs to be changed should be provided.

Parameters for modifying expressions when editing Data Source

Parameters Details
Name

required

`string`

Name of the Expression
Expression



required

`string`

Expression


Action



optional

`string`

add/delete/edit

By default, it is add.
NewName

optional

`string`

For renaming the expression. This is applicable only if the Action is edit

Connection Sample

For creating connection:

"Connection":
{
"ConnectionType": "Open Distro",
"Servername": "string",
"Port": "string",
"AuthenticationType": "string",
"Username": "string",
"Password": "string",
"Database": "string",
"Tablename": "string",
"AdvancedSettings": "string",
"IsSshConnection": "false",
"SshServerName": "string",
"SshPort": 0,
"SshUsername": "string",
"SshPassword": "string",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
}]
}

For editing connection:

"Connection":
{
"ConnectionType": "Open Distro",
"Servername": "string",
"Port": "string",
"AuthenticationType": "string",
"Username": "string",
"Password": "string",
"Database": "string",
"Tablename": "string",
"AdvancedSettings": "string",
"IsSshConnection": "false",
"SshServerName": "string",
"SshPort": 0,
"SshUsername": "string",
"SshPassword": "string",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)",
"NewName" : "Sum",
"Action": "edit"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
"Action": "delete"
}]
}

Sample connection for editing the connection while uploading the dashboard via API.

"Connection":  {   
      "ServerName": "string",
      "Database": "string",
      "Port": "string",
      "UserName": "string",
      "Password": "string",
      "AuthenticationType": "string",
      "AdvancedSettings": "string",
      "ConnectionType": "string",
      "Schema": "string"
  }

Data Transformation

Editing a Data Connection

Dashboard Designer Walkthrough