Elasticsearch catalog

StarRocks supports Elasticsearch catalogs from v3.1 onwards.

StarRocks and Elasticsearch are both popular analytical systems with distinct strengths. StarRocks excels in large-scale distributed computing and supports querying data from Elasticsearch through external tables. Elasticsearch is known for its full-text search capabilities. The combination of StarRocks and Elasticsearch provides a more comprehensive OLAP solution. With Elasticsearch catalogs, you can directly analyze all indexed data in your Elasticsearch cluster by using SQL statements on StarRocks without the need for data migration.

Unlike catalogs for other data sources, an Elasticsearch catalog has only one database named default in it upon creation. Each Elasticsearch index is automatically mapped to a data table and mounted to the default database.

Create an Elasticsearch catalog

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
[COMMENT <comment>]
PROPERTIES ("key"="value", ...)

Parameters

catalog_name

The name of the Elasticsearch catalog. The naming conventions are as follows:

• The name can contain letters, digits (0-9), and underscores (_). It must start with a letter.
• The name is case-sensitive and cannot exceed 1023 characters in length.

comment

The description of the Elasticsearch catalog. This parameter is optional.

PROPERTIES

The properties of the Elasticsearch catalog. The following table describes the properties supported for Elasticsearch catalogs.

Examples

The following example creates an Elasticsearch catalog named es_test:

CREATE EXTERNAL CATALOG es_test
COMMENT 'test123'
PROPERTIES
(
    "type" = "es",
    "es.type" = "_doc",
    "hosts" = "https://xxx:9200",
    "es.net.ssl" = "true",
    "user" = "admin",
    "password" = "xxx",
    "es.nodes.wan.only" = "true"
);

Predicate pushdown

StarRocks supports pushing the predicates specified in queries against Elasticsearch tables down to Elasticsearch for execution. This minimizes the distance between the query engine and the storage source and improves query performance. The following table lists the operators that can be pushed down to Elasticsearch.

Query examples

The esquery() function can be used to push Elasticsearch queries, such as match and geoshape queries, that cannot be expressed in SQL down to Elasticsearch for filtering and processing. In the esquery() function, the first parameter that specifies a column name is used to associate with the index, and the second parameter is an Elasticsearch query's Elasticsearch Query DSL-based JSON representation enclosed in curly braces ({}). The JSON representation can and must have only one root key, such as match, geo_shape, or bool.

• Match query

  SELECT * FROM es_table WHERE esquery(k4, '{
     "match": {
        "k4": "StarRocks on elasticsearch"
     }
  }');

• Geoshape query

  SELECT * FROM es_table WHERE esquery(k4, '{
  "geo_shape": {
     "location": {
        "shape": {
           "type": "envelope",
           "coordinates": [
              [
                 13,
                 53
              ],
              [
                 14,
                 52
              ]
           ]
        },
        "relation": "within"
     }
  }
  }');

• Boolean query

  SELECT * FROM es_table WHERE esquery(k4, ' {
     "bool": {
        "must": [
           {
              "terms": {
                 "k1": [
                    11,
                    12
                 ]
              }
           },
           {
              "terms": {
                 "k2": [
                    100
                 ]
              }
           }
        ]
     }
  }');

Usage notes

• From v5.x onwards, Elasticsearch adopts a different data scanning method. StarRocks only supports querying data from Elasticsearch v5.x and later.
• StarRocks only supports querying data from Elasticsearch clusters that have HTTP basic authentication enabled.
• Some queries, such as count()-involved queries, run much slower on StarRocks than on Elasticsearch, because Elasticsearch can directly read the metadata related to the specified number of documents that meet the query conditions without the need to filter the requested data.