Connecting Bold BI to AWS Open Search

The Bold BI Dashboard designer supports connecting to AWS Open Search using the SQL Live Query.

Supported Server Versions:

  • Amazon Elastic Search 7.10 and above

Prerequisites

To connect to Amazon Elasticsearch service through Open Search, make sure you have enabled the following settings:

PUT _opendistro/_sql/settings

{ “persistent”: { “opendistro.sql.cursor.enabled:true } }

How to create AWS Open Search data source

You can connect your AWS Open Search data source using the Elastic Search data source connector. To configure the AWS Open Search data source, follow these steps:

  1. Open the dashboard designer configuration panel and click on the Data Sources icon to add a new data connection.

    Data source icon

  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.

    Choose data source

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

Select data source

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

Create an AWS Open Search data source in Live mode

After clicking the data source, the NEW DATA SOURCE configuration panel opens. Follow the given steps to create an Open Search data source:

  1. Enter a name and description (optional) for the data source.

  2. To connect to an Amazon Web Service hosted Open Search instance, please use the connection type as Open Distro.

  3. Enter a valid Open Search server or hostname in the Server Name text box.

  4. Enter the port in which the server is running in the port text box.

  5. Choose the Live mode option for this connection.

  6. Enter a valid username in the Username text box.

  7. Enter a valid password in the Password text box.

  8. Click Connect to connect the AWS Open Search server with the configured details.

    Open search connection

Create an AWS Open Search data source in Extract mode

After clicking the data source, the NEW DATA SOURCE configuration panel opens. Follow the given steps to create an Open Search data source:

  1. Enter a name and description (optional) for the data source.

  2. To connect to an Amazon Web Service hosted Open Search instance, use the connection type as Open Distro.

  3. Enter a valid Open Search server or hostname in the Server Name text box.

  4. Enter the port in which the server is running in the port text box.

  5. Choose the Extract mode option for this connection.

  6. Enter a valid username in the Username text box.

  7. Enter a valid password in the Password text box.

  8. Click Connect to connect the AWS Open Search server with the configured details.

    Open search connection

NOTE: You can connect to the Elasticsearch database by selecting the connection type as Elasticsearch SQL. Please click on the Elastic Search for Elasticsearch data source connection.

NOTE: You can also connect through the Custom query support in the extract mode of connection by providing a valid SQL query in both the Elasticsearch 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 AWS Open Search server will be displayed in a treeview section.

    Tree view

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

    Drag and drop table

  3. After dragging and dropping the required table, you can do data modeling such as Formatting Columns, Configure Data Filters, and Configure Expression Columns can be done.

Data Preview

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

    Update values

  2. The data from the selected table will then be displayed.

    Table values retrieve

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

    Save data source

NOTE: Amazon Open Search has the following limitations:

• Joins are not supported and will throw an error message.
• Code view switching is restricted as grouping for sub queries is unsupported.
• Virtual scrolling is restricted for now as there is no SQL function for the operation.
• The Top N option in initial filters is unsupported as the join command is not supported.
• Expressions that 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.

Connect using custom attribute and dashboard parameter

We have added support for custom attributes and dashboard parameters to 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 AWS Open Search Data Source via REST API

Prerequisites

Supported Server Versions:

  • Amazon Elastic Search 7.10 and above
  • All versions of Open Search

To connect to Amazon Elasticsearch service, make sure you have enabled the following settings:

PUT _opendistro/_sql/settings

{ “persistent”: { “opendistro.sql.cursor.enabled:true } }

The type required for creating the data source is elasticsearch or awsopensearch.

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 Open Distro
Servername

required 		`string`

Server name or Host name of the connection
Port

required 		`string`

Port number used to connect to opensearch
Username

required 		`string`

A valid username for the connection.
Password

required 		`string`

A valid Password for the connection.
Tablename

required 		`string`

Enter a valid Tablename
AdvancedSettings

optional 		`string`

Additional optional connection parameters can be provided. 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: When editing the Data Source via API, all parameters are optional. Only provide the parameter that needs to be changed.

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",
"Username": "string",
"Password": "string",
"Tablename": "string",
"AdvancedSettings": "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",
"Username": "string",
"Password": "string",
"Tablename": "string",
"AdvancedSettings": "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