@Rocky can I do federated queries from opensearch in Starrocks?

Yes, you can perform federated queries from OpenSearch in StarRocks by using the Elasticsearch Catalog (introduced in StarRocks v3.1). Since OpenSearch was originally forked from Elasticsearch 7.10.2 and maintains high API compatibility, StarRocks treats OpenSearch as an Elasticsearch-compatible data source. Key Features of the Integration * Zero Data Migration: You can query OpenSearch data directly via SQL without moving it into StarRocks. * Automatic Schema Mapping: StarRocks automatically maps OpenSearch indexes to tables within a

default_db

database in the catalog. * Predicate Pushdown: StarRocks pushes filters (like

=

,

IN

,

>

,

<

) down to OpenSearch to minimize data transfer and improve performance. * Complex Query Pushdown: You can use the

esquery()

function to pass raw OpenSearch/Elasticsearch Query DSL (like

match

or

geo_shape

) directly to the backend. How to Set It Up You can create an external catalog using the following SQL syntax:

sql
CREATE EXTERNAL CATALOG opensearch_catalog
PROPERTIES (
    "type" = "es",
    "hosts" = "http://<opensearch_host>:9200",
    "user" = "<username>",
    "password" = "<password>",
    "es.nodes.wan.only" = "true" -- Set to true if StarRocks cannot reach OpenSearch data nodes directly
);

Querying Data Once the catalog is created, you can query your OpenSearch indexes:

sql
-- View available tables (indexes)
SHOW TABLES FROM opensearch_catalog.default_db;

-- Standard SQL Query
SELECT * FROM opensearch_catalog.default_db.my_index 
WHERE status = 'active' AND price > 100;

-- Federated Join (joining OpenSearch with a StarRocks native table)
SELECT es.user_id, sr.user_name, es.action
FROM opensearch_catalog.default_db.logs AS es
JOIN default_catalog.business_db.users AS sr ON es.user_id = sr.id;

Important Considerations 1. Authentication: StarRocks supports OpenSearch/Elasticsearch clusters that have HTTP basic authentication enabled. 2. Versions: StarRocks supports Elasticsearch v5.x and later, which includes all current versions of OpenSearch. 3. Experimental Status: While the Elasticsearch catalog is the modern way (v3.1+), older versions of StarRocks used an

Elasticsearch External Table

engine, which is now deprecated. Always prefer the Catalog approach for federated analysis. 4. Performance: While StarRocks is excellent at joining and aggregating, for simple

COUNT(*)

queries on large indexes, OpenSearch might be faster natively because it uses internal metadata. For complex analytics across multiple sources, StarRocks provides significant performance benefits through federated queries. References • data_source/External_table.md

@Rocky but there is no reference in you docs or in your codes, how can i be sure?

The reason you won't find a dedicated "OpenSearch Catalog" keyword in the documentation is that StarRocks uses the Elasticsearch (ES) Catalog to support OpenSearch. Because OpenSearch was forked from Elasticsearch 7.10.2, it remains wire-compatible with the Elasticsearch REST APIs that StarRocks uses for federated queries. StarRocks treats OpenSearch as an Elasticsearch-compatible data source. How to verify this works To connect to OpenSearch, you use the standard Elasticsearch Catalog syntax but point it to your OpenSearch endpoint:

sql
CREATE EXTERNAL CATALOG opensearch_catalog
PROPERTIES (
    "type" = "es",                     -- Use 'es' for both Elasticsearch and OpenSearch
    "hosts" = "http://<opensearch-ip>:9200",
    "user" = "admin",
    "password" = "your_password",
    "es.nodes.wan.only" = "true"       -- Recommended for cloud/managed OpenSearch (like AWS)
);

Why it's not explicitly in the "Codes" In the StarRocks source code, the connector handles the communication via standard HTTP REST calls (e.g.,

_nodes/http

,

_search

, and

_mapping

). These endpoints are identical in OpenSearch. A Note on Version Sniffing: StarRocks performs "version sniffing" to determine which features are supported. * OpenSearch 1.x: Reports itself as compatible with Elasticsearch 7.x, so the StarRocks

es

connector recognizes it immediately. * OpenSearch 2.x: By default, OpenSearch 2.x might report a version that the connector doesn't recognize. If you encounter a connection error, you can enable Compatibility Mode on your OpenSearch cluster:

bash
    PUT /_cluster/settings
    {
      "persistent": {
        "compatibility.override_main_response_version": true
      }
    }

This makes OpenSearch report its version as

7.10.2

, allowing the StarRocks catalog to function perfectly. Summary of Support * StarRocks Version: Requires v3.1 or later for the Catalog feature. * OpenSearch Version: Supports all versions of OpenSearch (1.x and 2.x) via the

es

catalog type. * Functionality: Supports standard SQL queries, Joins with StarRocks internal tables, and Predicate Pushdown. You can be confident in this setup because many users in the community use StarRocks to query Amazon OpenSearch Service using this exact "Elasticsearch Catalog" method. References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/elasticsearch_catalog.md