Query a logical table - Data Management - Alibaba Cloud Documentation Center

This topic describes how to query a logical table.

Background information

In case of database and table sharding, you may want to perform queries on a table to obtain query results from one or more tables without the need to perform queries on all tables one by one. The most satisfying solution is to query data from these tables by using a single table.
Data Management (DMS) implements the preceding solution by aggregating the data from logical databases and logical tables.

Procedure

Select a logical database and query or export data as needed. Try writing data to the logical table. If no routing algorithm is configured, only SELECT, UPDATE, and DELETE statements are supported, and the INSERT statement is not supported.
Configure a routing algorithm. The following example shows how to configure a routing algorithm that performs simple modulo operations. For more information about how to configure complex routing algorithms, see Configure a routing algorithm.
After a routing algorithm is configured, run an INSERT statement to insert values into the logical table.
After you insert values into the logical table, query the data by using SQL statements with query conditions.
- After you configure a routing algorithm, you can query data of routing-based fields from specified physical tables. This eliminates the need to perform calculations manually and switch between physical databases and tables.
- The query result displays the database and table information in the last column. You can click the value in the column to go to the physical database or table and query data with the previous SQL statements and query conditions.
- You can also query data from the whole logical table, which includes all physical tables, without using routing-based fields. For example, you can run COUNT() statements to obtain the number of records in a table.
  Note You can also execute statements such as GROUP BY. Other SQL syntaxes are similar to the syntaxes of the source database, except that subqueries are not supported.