With AgenticBI you can natively connect to data in your ElasticSearch cluster, create visualizations, perform joins across multiple indexes, use AgenticBI's search-based analytics feature (also known as natural language querying), and more.
Connect, extract and transform data from your ElasticSearch through our UI to connect directly.
Visualize and Automate your Reporting instantly.
Log in to AgenticBI and select Queries from the left sidebar.
Click on New Datasource + button and select ElasticSearch from the list of datasources.
After navigating to the New Datasource page, either use the pre-configured settings into AgenticBI's own demo Elasticsearch database or follow the prompts and configure the following details to set up connectivity to your own Elasticsearch database:
a. Datasource Name: Enter a name for your datasource
b. Elasticsearch URL: Elasticsearch URL to connect to.
c. User ID: Enter the User ID to connect
d. Password: Enter the password to connect to the database
e. Custom Headers: Headers for additional connection properties.
f. Version: Select Elasticsearch server version
Establish Network connectivity and click on the Test Connection button.
Note: The connection validity of the network can be tested only if it has been established via Direct Connectivity or an SSH tunnel. For more information on connectivity and datasource, please refer to the documentation on- Connectivity & Datasources.
Click on Save and start Querying.
Set up Query using a visual builder or query editor
Step 1: After connecting to the ElasticSearch datasource, AgenticBI will pull out a list of indexes along with field samples. Using these indexes, you can automatically generate queries through our visual builder in a no-code environment by either dragging and dropping fields or making your selections through the drop-down.
Tip: You can also write queries directly in the Query Editor, a versatile text editor that offers more advanced editing functionalities like ElasticSearch JSON Query, support for multiple language modes, Cloud9QL, and more.
Step 2: Define data execution strategy by using any of the following two options:
Direct Execution: Directly execute the Query on the original Datasource, without any storage in between. In this case, when a widget is displayed, it will fetch the data in real time from the underlying Datasource.
Non-Direct Execution: For non-direct queries, results will be stored in AgenticBI's Elastic Store. Benefits include- long-running queries, reduced load on your database, and more.
Non-direct execution can be put into action if you choose to run the Query once or at scheduled intervals. For more information, feel free to check out this documentation- Defining Data Execution Strategy
Step 3: Click on the Preview button to analyze the results of your Query and fine-tune the desired output, if required.
The result of your Query is called Dataset. After reviewing the results, name your dataset and then hit the Create & Run button.
A versatile text editor designed for editing code that comes with a number of language modes including Elasticsearch JSON Query and add-ons like Cloud9QL.
Step 1: Write your query using Elasticsearch JSON Query in the Query Editor. Optionally, apply Cloud9QL on top for additional transformations.
Step 2: Define data execution strategy by using any of the following two options:
Direct Execution: Directly execute the Query on the original Datasource, without any storage in between. In this case, when a widget is displayed, it will fetch the data in real time from the underlying Datasource.
Non-Direct Execution: For non-direct queries, results will be stored in AgenticBI's Elastic Store. Benefits include- long-running queries, reduced load on your database, and more.
Non-direct execution can be put into action if you choose to run the Query once or at scheduled intervals. For more information, feel free to check out this documentation- Defining Data Execution Strategy
Step 3: Click on the Preview button to analyze the results of your Query and fine-tune the desired output, if required.
Users can pull ElasticSearch highlights from one or more fields in an ElasticSearch query. Requesting highlights (see: ElasticSearch Highlighting Syntax) in the editor view of an ElasticSearch query will return a _highlight column in your query output containing highlighted fields and fragments.
Sample query that searches for Costco records and highlights the customer field:
{ "query" : {
"match_phrase" : {
"customer" : "Costco"
}
},
"highlight": {
"fields": {
"customer": {}
}
},
"size" : 10000
}
This will return a separate field named _highlight containing the highlighted field and fragments. See the documentation on ElasticSearch Highlight for more information.
AgenticBI supports querying Elasticsearch templates by allowing the end-user queries to pass as a parameter into the search template. This prevents your query-building logic from being exposed to the end user.
Elasticsearch templates make it easy to convert the user input into Elasticsearch queries. Simply modify the template for id and parameter(s) based on your requirements and run the query against it.
AgenticBI automatically detects Elasticsearch templates in the code editor and allows you to query it as shown below:
A Direct Passthrough Query allows you to query an Elasticsearch index right from your dashboard through the use of dashboard filters. Currently, Elasticsearch is the only datasource supported.
The primary use case for this would be when you have a large amount of data (billions of records) that you'd like to aggregate in real-time. For example, take an index containing 800 million records. Instead of pulling all 800 million records in before manually filtering and grouping at the widget level, you can perform aggregation operations and query against your Elasticsearch index directly from your dashboard.
This is achieved by utilizing AgenticBI's existing Direct Query tokens. When a user makes a dashboard filter selection using these specific "template" query tokens (defined below), AgenticBI will translate these selections into Elasticsearch and Cloud9QL queries, execute the query, and display the resulting dataset in the passthrough query widgets.
Here is a summary of the flow:
1. Create a direct passthrough query against an Elasticsearch datasource (see template below)
2. Save the query
3. Add the newly created widget to a dashboard
4. Configure dashboard filters using the template tokens (ƒ) to set metrics and dimensions
Query your Elasticsearch datasource by following the steps outlined in this document.
To create a Direct Passthrough Query, we will have to manually add the template in the query Editor mode. Currently, there is no way to auto-generate this template, but we may add this functionality later on.
In the Elasticsearch JSON Query section, define your table (index), metric field, dimension field, and query tokens. Note that these tokens follow the format defined in the Runtime Query Parameters:
Example:
/*
Query Template
Table: $c9_table$(activities)$
Metrics: $c9_metrics$[Metrics]${field|Metric}$
Dimensions: $c9_dimensions$[Dimensions]${field|Dimension}$
*/
$c9_query$({})$
In the Cloud9QL Transformations section, you can define how the auto-generated C9QL query should be placed by setting:
$c9_c9ql$()$
Step 1: Click on the Filters icon on the dashboard toolbar.
Step 2: Click on the + Add button and select Filter from the dropdown list.
Step 3: Configure the filter by defining the values for the following parameters:
Fields to filter (Metrics or Dimensions)
Fields to filter: If you select Metrics, then the Value field will be converted to the list of fields contained in the table.
Note: (ƒ) for field types, (T) for text, and (#) for numeric fields.
Condition type: Set to equals. This must be set to equals in order to pass the query parameters properly.
Value: List of fields from the index specified
Note: This provides the ability to select one field from multiple fields. The field selected will be highlighted, and the Label and Operation will be based on the selected field.
Label: This is the field name which can be set to an alias.
Note: This applies to the field selected (highlighted) above in the Value box.
Operation: This is dependent on the field's data type. For example, if the field data type is string then the operation dropdown listing includes None or Count. If the field data type is numeric, then the operation dropdown listing includes Sum, Min, Max, Avg, Median, and Count.
Note: This applies to the field selected (highlighted) above in the field box
Dimension is used to group the data based on the fields you have selected/aggregated in the Metrics. For example, if you select a field 'customer' here, then the data table will aggregate your metrics by your 'customer' field.
Condition type: Set to equals. This must be set to equals in order to pass the query parameters properly.
Value: List of fields from the index specified. These will automatically be added as columns in your result.
Step 3: Hit the Save button and the filter will be added to the right-hand filter panel.
You can edit your Metrics (aggregations) and Dimensions (groupings) via the dashboard filter bar. For your Metrics, click on existing fields to alter their respective Label and/or Operation. Add new fields by selecting additional fields in the Value dropdown. For Dimensions, add or remove groupings by selecting additional fields in the Value dropdown.
Selecting a field that exists on this index will be translated and passed through the query directly
Example: