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.

   

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 on the Data Sources menu from the left menu panel and selecting Create Data Source from the data sources page.

Connect to AWS Open Search

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.

   

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.

   

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.

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.

   

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

   

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.

   

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

   

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

   

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

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 for adding expressions when creating Data Source

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

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

Related links

Data Transformation

Editing a Data Connection

Dashboard Designer Walkthrough