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.

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

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

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.

Advanced

In the Advanced category, It will Redirect to the Bold ETL. Refer to Bold ETL.

Basic

In the Basic Category, It will act as the connector in Bold BI. And need to follow the below steps.

Connect to Elastic Search

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

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

4. 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.

5. Choose the Live mode option for this connection.

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

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

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

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

4. 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.
5. Choose the Extract mode option for this connection.
6. 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.
7. Click Connect to connect the Elastic Search server with the configured details.

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.

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.

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.

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

3. 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.

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

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

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.

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

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 for edit the connection while upload the dashboard via API

Parameters for adding expressions when creating Data Source

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

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"
  } 

Related links

Data Transformation

Editing a Data Connection

Dashboard Designer Walkthrough