Search results

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
  • All versions of Open Search

Prerequisites

To connect Amazon Elasticsearch service, 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 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.

    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 the Data Sources menu from the left menu panel and 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 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 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.

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 AWS Open Search server will be shown in a treeview section.

    Tree view

  2. Now, you can drag and drop the required table from the tree view 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, select the Update button.

    Update values

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

    Table values retrieve

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

    Save data source

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 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: 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",
"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