All Products
Search
Document Center

Logical table

Last Updated: May 09, 2020

Definition

  • When the business reaches a certain scale, load balancing needs to be conducted through database sharding and table partitioning to smoothly support business expansion.
  • If the number of physical tables is greater than 1, they are evenly distributed across physical databases.
    • Generally, the number of tables is an exponential power of 2.
    • Generally, the suffix of a table name is in the format of _xxxx. In the format, xxxx is a four-digit number, which starts from 0000 and increments by 1.
    • The number of physical tables must be evenly dividable by the number of physical databases. For example, if there are 1024 physical tables and 32 physical databases, 32 physical tables are evenly distributed to each database. If there are 1024 physical tables and 33 physical databases, the tables cannot be evenly divided and therefore no logical table can be created.
  • Typical scenarios:
    • Single database with table partitioning
      • Table shards are divided in a single physical database.
    • Database sharding with table partitioning (most common)
      • Physical tables are evenly divided by the specified number of physical databases. The numbering of physical tables increments by 1 in each physical database.
      • The names of physical tables distributed to each database are the same. For example, each database has 12 physical tables that are numbered from 01 to 12.
    • Database sharding without table partitioning (same name of a table on different database shards)
      • For example, the same physical table has the same name on each database shard.

Usage

  • Note that logical tables must be operated in a logical database in the preceding three scenarios.
    • Apply for the permissions on data sources.
      • If you directly apply for the permissions on a logical table, you have the permissions on all the physical tables associated with the logical table.
    • Query data in the SQLConsole.
    • Export data.
    • Change data.
  • When performing any of the preceding operations, you can select a specific logical database and use a logical table as a single table.

Configuration

  • Automatic configuration
    • After you configure a logical database, the system automatically aggregates the physical tables in the logical database to a logical table based on the schemas of the physical tables. You cannot modify the automatic aggregation configuration.
      • Aggregation rules:
        • A physical table can be aggregated to only one logical table.
        • All physical tables mapping the same logical table must have the same schemas including the field names and field types. Otherwise, the physical tables cannot be aggregated. The benefit of this rule is that users can be alerted when data inconsistencies occur.
  • Manual configuration
    • If the existing logical table is configured incorrectly, you can manually configure a logical table as follows: Find the target logical database from the database list and choose More > Extract Logical Tables.

FAQ

  • Q: A logical table already exists in the logical database and no physical table needs to be created. However, the logical table is unavailable in the table list in the logical database. What can I do?
    • A: You can search for the target logical database from the database list and choose More > Extract Logical Tables.