All Products
Search
Document Center

JOIN operations on multiple tables

Last Updated: Sep 09, 2021

The OpenSearch console provides four methods for you to define an application schema. An advanced application supports multiple tables, which facilitates your calls in complex business scenarios.

Rules of associating primary and secondary tables

Take note of the following rules when you manually create multiple tables to configure the schema of an application:

  • The ratio of primary table records to secondary table records must be N:1 or 1:1. The ratio of 1:N is not supported. This means that a primary table record cannot be associated with multiple secondary table records. In addition, only one table can be set as the primary table.

  • A secondary table can be associated with a primary table by associating the primary key of the secondary table with a foreign key of the primary table. Foreign keys of the primary table can be associated only with the primary key of the secondary table.

  • Up to two levels of association are supported, except for exclusive applications, which support three levels of association. Up to 10 secondary tables can be created for an application.

Primary and secondary tables

Association logic

Supported

Table A (primary table), Table B

Table A (primary table) -> Table B

Yes

Table A (primary table), Table B, Table C

Table A (primary table) -> Table B -> Table C

Yes

Table A (primary table), Table B, Table C, Table D

Table A (primary table) -> Table B, Table A (primary table) -> Table C, Table A (primary table) -> Table D. Only one level of association is configured. The number of secondary tables does not exceed 10.

Yes

Table A (primary table), Table B, Table C, Table D

Table A (primary table) -> Table B -> Table C -> Table D. More than two levels of association are configured.

No, except for exclusive applications, which support three levels of association.

Table A (primary table), Table B

Table A (primary table) -> Table B, Table B -> Table A. Tables are associated in the form of the cyclical association.

No

Table A (primary table), Table B, Table C, ..., Table K

Table A (primary table) -> Table B, Table A (primary table) -> Table C, ..., Table A (primary table) -> Table K. Only one level of association is configured. However, more than 10 secondary tables are created.

No, except for exclusive applications, which support more than 10 secondary tables.

Data models

1

Examples of association between primary and secondary tables

1:1N:1fail of join

Notes

  • Fields can be mapped between tables only if the fields are the same type. For example, if the primary key of a secondary table is the INT type, the primary key can map only fields of the INT type in a primary table.

  • Offline tables are joined based on the following logic: During the indexing process, a LEFT JOIN operation is performed on a primary table and secondary tables to generate a wide table. The index is built based on the wide table for search services. Assume that the ratio of primary table records to secondary table records is N:1 and the value of N is large. When data records in the secondary tables are updated, the number of data records that need to be updated in the primary table is N times that in the secondary tables. This may cause an update delay for both the primary and secondary tables. In this case, we recommend that the value of N no more than 10.

  • After the JOIN operation, the number of rows in the generated wide table is the same as that in the primary table. Fields that are empty in the secondary tables are automatically filled with a default value in the wide table. The default value for fields of the INT type is 0. The default value for fields of the STRING type is null.