Query a logical table-Data Management-阿里云帮助中心

This topic describes how to query a logical table.

Background

After you implement database and table sharding, you need a simple way to query data spread across multiple physical tables. A logical table provides a unified view, allowing you to query the sharded data as if it were a single table. This abstracts away the complexity of manually locating the correct database and table for each query.
Data Management (DMS) enables these transparent queries by aggregating data from logical databases and logical tables.

Procedure

Select the logical database to query and export data. Before you configure a routing algorithm, you can run only SELECT, UPDATE, and DELETE statements. The INSERT statement is not supported.

If you run an INSERT statement at this stage, the system returns the following error: [Error] dsql.DSQLException: No shard route found, execution failed. Make sure a routing algorithm is configured for the logical table.
Configure a routing algorithm. This example uses simple modulo. For more information about configuring complex algorithms, see Configure a routing algorithm.

In the New algorithm dialog box, set Algorithm type to Single-column modulo, Modulo type to Simple modulo, and Routing field to user_id (bigint). Set Modulo to 128 and then click Save.
Run an INSERT statement.

For example, run INSERT INTO scend_test(gmt_create,gmt_modified,user_id) VALUES (now(),now(),2); to insert a record into the scend_test table. A green check mark appears in the results area upon successful execution.
Perform a quick query.
- After you configure a routing algorithm, you can query a specific physical shard table using the routing field in your query condition. This eliminates the need to manually calculate and switch between physical databases and tables. In the DMS SQL Console, run the SQL statement SELECT * FROM scend_test WHERE user_id = 4, which uses the routing field user_id as a query condition. The execution log shows that DMS routes the logical table scend_test to the physical shard table poc_dev.scend_test_000.
- The query result includes an extra column that indicates the source database and table. You can click the value in this column to navigate to the corresponding physical shard table with the SQL statement and query conditions pre-filled. For example, when you run SELECT * FROM scend_test WHERE user_id = 1, the data location column in the result shows poc_dev.scend_test_000. This indicates that DMS routed the data to the corresponding physical shard table and returned one matching record.
- You can also run queries without including the routing field as a condition. This performs a full table scan across all physical shard tables, which is useful for operations such as summary statistics.
  
  Note
  Syntax such as GROUP BY is also supported. Most SQL syntax is consistent with the source database, but subqueries are not currently supported.
  
  For example, if you run SELECT COUNT(*) FROM scend_test; in the SQL Console, the result is 1, indicating that the table contains one record.