All Products
Search

This Product

    OpenSearch:Quick start: Set up online multi-table joins

    Document Center

    OpenSearch:Quick start: Set up online multi-table joins

    Last Updated:Jun 21, 2026

    This guide explains how to quickly set up an OpenSearch High-performance Search Edition instance that supports online multi-table joins.

    Prerequisites

    1. You have an Alibaba Cloud account and have completed identity verification.

    2. You have created an AccessKey. The Alibaba Cloud console prompts you to create an AccessKey during your initial login.

    Procedure

    1. Create and purchase an application instance.

    2. Configure the application: define the application and index schemas, and configure a data source.

    3. Test the search functionality.

    Create an application

    1. On the Instances page of the OpenSearch High-performance Search Edition console, click Create Instance.

    2. Specify the instance settings, including the edition, billing method, region and zone, application name, cluster preference, storage capacity, and computing resources. Then, click Buy Now.

      Available editions include Industry Algorithm Edition, LLM Intelligent Q&A Edition, High-performance Search Edition, Vector Search Edition, and Recall Engine Edition.

      For Edition, select High-performance Search Edition.

      The Application name must start with a letter, can contain only letters, digits, and underscores (_), and cannot exceed 30 characters in length. You cannot change the name after the application is created. For Resource Group, select or create one as required.

      • Billing method: You can select Pay-as-you-go or Pay-as-you-go.

      • Region and zone: Select a region and zone based on your business needs.

      • Application name: Enter a custom name.

      • Cluster preference: Fixed to Storage-optimized Dedicated Cluster.

      • Storage capacity and Computing resources: Keep the default values or specify values based on your business needs.

    3. Confirm your order, agree to the terms of service, and then click Open immediately.

    4. After activation, the instance appears on the Instance Management page with a status of Pending Configuration.

      For an instance with the Pay-as-you-go billing method, click Configure in the Actions column to configure the application.

    Configure the application

    1. On the instance list page, find the newly purchased instance and click Configure in the Actions column.

      The status of the new instance is Pending Configuration.

    2. Define the application schema in one of the following four ways:

      • From a template: You can save a user-defined application schema as a template and reuse it to quickly create new applications.

      • By uploading a document: Upload a JSON data file. The system automatically parses it to create an initial application schema. You must then review and adjust settings like field types.

      • From a data source: This method is ideal for synchronizing data from sources like RDS and MaxCompute. It automatically generates an initial application schema from the source table schema, reducing manual effort and potential errors. For details, see Data Source Configuration.

      • Manually: This method is suitable for quick testing. This guide uses this method to create two tables.

      The first table is named main and contains the fields m_id (INT, primary key), m_name (LITERAL), m_age (LITERAL), and m_title (TEXT). The second table is named table_1 and contains the fields t_id_1 (INT, primary key), t_name (LITERAL), t_age (LITERAL), and t_title (TEXT).

      For information about field types, see Application schema in OpenSearch High-performance Search Edition.

      Note:

      Note

      The system supports a maximum of eight tables.

    3. Configure the index schema for each table separately.

      The index schema configuration page contains the following sections: Index Fields (configure the index name, included fields, and analysis method), Attribute Fields, Routing Fields (Optional), and Advanced Settings (Optional). For applications with multiple tables, such as the main and table_1 tables, you must expand and configure each table separately.

      • For more information about how to configure an index schema, see Index schema.

      • For more information about how to select an analysis method, see Text analyzers.

      • To use a field in a SELECT, WHERE, or ORDER BY clause, you must configure it as an attribute field.

      Note

      • Fields of the FLOAT, FLOAT_ARRAY, DOUBLE, and DOUBLE_ARRAY types cannot be configured as index fields.

      • Fields of the TEXT and SHORT_TEXT types cannot be configured as attribute fields.

    4. In the settings for each table, such as the main table settings and table_1 settings sections, expand the Routing Fields (Optional) area and add a routing field in the Included Fields column.

      OpenSearch instances use a distributed backend. In a multi-table scenario, all data required for a join operation must be located on the same machine, as shown in the following figure.

      When building the index, the engine hashes the value of the configured routing field to ensure that records with the same hash result are stored in the same shard. At query time, the query node sends the request to each shard. Each shard performs the join operation internally without communicating with other shards. Finally, each shard returns its join results to the query node. The query node aggregates the results and returns the final result to the user.

      Note

      • By default, the system uses the primary key as the routing field.

      • You can select only one routing field.

      • Records across tables are co-located based on the routing field value.

      • Supported data types for the routing field are INT and LITERAL.

      • To perform a join on a non-primary key, you must configure the join key as the routing field.

    5. Configure a data source based on the types supported by the High-performance Search Edition.

      The page displays data source sections for both the main and table_1 tables. You must click Add Data Source in each section to configure them separately.

    6. After you complete the configuration, click Completed.

      The system then redirects you to the instance details page.

    7. On the instance details page, wait for the offline application status to become Normal before running queries.

      During this time, the offline application status is Application Initializing.

    Search test

    1. After the offline application status changes to Normal and the application is published online, you can test the application on the Search Test page.

      In the navigation pane on the left, choose Feature Extensions > Search Test. You can also click Search Test in the Actions column of the instance list.

    2. Currently, online multi-table joins support only SQL queries.

      In the SQL Query box, enter a multi-table join statement, such as SELECT ... FROM main INNER JOIN table_1 ON main.m_id = table_1.t_id_1 WHERE contain(main.m_title, ?) ORDER BY ... LIMIT 20 OFFSET 0. Pass query parameters by using dynamic_params=123,456. Click Execute to run the query and view the results below.

      For detailed information about the supported SQL syntax, see SQL support.

    Limitations

    • Only Exclusive Storage-optimized clusters support online multi-table joins.

    • In multi-table join scenarios, only SQL queries are supported.

    • In multi-table join scenarios, custom analyzers are not supported.

    • In multi-table join scenarios, sort configurations are not supported. Use an ORDER BY clause in your SQL query instead.

    • In multi-table join scenarios, you cannot display search results.

    • Exclusive Storage-optimized cluster instances cannot be changed to other instance types.