Connecting to an Elasticsearch database

Last modified by Aurelie Bertrand on 2026/07/02 11:51

Connecting to an Elasticsearch database

To connect to an Elasticsearch database :

  1. In the Create a new data model box, click the ES button in the Database section.
    ES_button.png
    ➡ The Elasticsearch dialog box is displayed
  2. First select the desired connection mode:

  • Manual connection mode
    The connection will be visible in the data source only.

    1. In the Database URL field , enter the address of your database.
      For example:http://host:port
    2. In the Index field, enter the name of the Elasticsearch index you wish to use.
    3. In the User field, enter the name of the user to be used when connecting to the database.
    4. In the Password field, enter the password to be used when connecting to the database.
    5. Click Connect to obtain the status of the connection (successful connection or failed connection). If the connection fails, change the connection parameters.
  • Connection mode named
    The connection will be visible in the Connection Manager and can be used by all data sources.
    1. Click Select: the Database Connection Manager is displayed.
    2. You can :
      • Select an existing connection from the list. Click OK.
      • Create a new connection as described on the Add a named connection.
  • Parameterised connection mode
    This mode allows you to use a database connection assigned to the user via a user parameter.
    See the page Parameterised connection to a database page for more details.
  1. Next, configure the query to be executed. Requests must be of the Request Body Search type (https://www.elastic.co/guide/en/elasticsearch/reference/current/search-search.html).
  2. Click on the Preview button to display a preview of the result. Go to the Preview page page for more details.
    Preview

Note: 50 rows are displayed by default.
You can change this number by typing the desired value in the Maximum number of rows for preview field .

  1. You can also add data transformations. See the Transform data page for more details.
  2. Once you are satisfied with the result, click Next to configure the data model.

You can save the query you have entered for future use. To do this

  1. Click on the Favourites button below your SQL query.
  2. Select Add to favourites then enter the name you want to give to this query.

Advanced options

Options

OptionDescription
Query Timeout

Maximum query duration in seconds.
0 corresponds to the default Elastic Search value.

Number of retries on error

Number of times the query is repeated on error.
If other than 0, the time between retries must be specified, in seconds.

Persist error on schedule

Used to generate a cube containing the error. The whole process will fail later, but this avoids having to redo the job (and being in error again) for all the tasks.

Incremental data mode

Incremental mode is used to feed the cube incrementally: each refresh of the cube brings back only the new data compared with the previous refresh.
See paragraph below.

JQ queryAdvanced feature that allows you to specify a JQ filter to be executed on the json returned by Elasticsearch when the query is executed.

Configuring incremental mode

The Elasticsearch data source supports an incremental mode to minimise queries to the database.

  1. In the Connection section, click on the Advanced... button.
  2. Check the Incremental data mode box.
  3. A warning message informs you that :
    • Incremental mode uses a reference column that uniquely identifies each record in the result (for example an auto-increment, an identifier, a date...)
    • The query must be sorted on this reference column (and contain this column)
    • The query must contain a range clause on this column comparing its value to a ${LASTVAL} variable. Example: "query": { "range" : { "order_date" : { "gt" : "${LASTVAL}" } } }
    • Incremental mode was designed to process large volumes of data incrementally, and is not optimal for small volumes.
    • If the query is modified in the future, the history previously accumulated in the cube will be invalidated. ${LASTVAL} will be evaluated to 0 and, depending on the range clause specified , all the data will be retrieved from the database.
  4. Enter the name of the column that will be used as an incremental reference in the Reference column field .
  5. Add a range clause to your SQL query (or an AND condition to an existing WHERE clause) that tests whether the reference column is greater than "${LASTVAL}". The exact syntax may depend on your database and the type of column. The comparison is made lexicographically, so it is preferable for the comparison to be made via a character string.