Documentation
Demos
Support
Blog
Download Free Trial
Search results

Connecting Bold BI to Elastic Search data source

The Bold BI Cloud Dashboard designer supports connecting 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 configure the Elastic Search data source, follow these steps:

  1. Click 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 clicking a data source, the NEW DATA SOURCE configuration panel opens. Follow the given steps to create an Elastic Search data source:

  1. Enter a name and description (optional) for the data source.
  2. Select a connection type you want to use with 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 the 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 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

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 the 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

Connect using Open Distro connection type

Prerequisites

To connect Amazon Elasticsearch service through Open Distro, make sure you have enabled 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 the data source, the data design view page will be opened. On this page, the list of schemas and tables from the Elastic Search server will be shown 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 do data modeling 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 have the following Limitations:

• Joins are not supported and thrown 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.
• Top N option in initial filters are unsupported as join command is not supported.
• Expressions which use today() and now functions are unsupported in widgets.
• The data sampling feature is restricted, as applying a limit in a subquery is not supported.

Connecting Bold BI to Elastic Search Data Source via REST API

Connect using Open Distro Connection Type

The detailed steps to connect using an open distro connection type is explained in the following document. AWS Open Search

Connect using ElasticSearch SQL Connection Type

Prerequisites

Supported Server Versions:

  • Elasticsearch 6.8 and above

Type while creating the data source needs to be elasticsearch.

Rest API - v4.0

Modes

Through the REST API, only the live mode data source can be created and edited.

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 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: For editing Data Source via API. All the parameters are optional. The parameter which needs to be changed can 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"
}]
}

Data Transformation

Editing a Data Connection

Dashboard Designer Walkthrough

Previous
Next