Connecting Bold BI to AWS Open Search

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

Supported Server Versions:

• Amazon Elastic Search 7.10 and above

Prerequisites

To connect Amazon Elasticsearch service through Open Search, make sure you have enabled 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 Elastic Search data source connector. To configure the AWS Open Search data source, follow these below steps:

1. Open dashboard designer configuration panel and Click 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 the Data Sources menu from the left menu panel and 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 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 with Elasticsearch database by selecting connection type as Elasticsearch SQL. Please click here for Elasticsearch data source 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.

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 AWS Open Search server will be shown 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 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, 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: Amazon Open Search 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.

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 Amazon Elasticsearch service, make sure you have enabled the following settings:

PUT _opendistro/_sql/settings

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

Type while creating the data source needs to be elasticsearch or awsopensearch.

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

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

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