Unified Query for Sharded Databases and Tables

NineData's table grouping query feature supports unified queries across multiple sharded databases and tables through a SQL Console, significantly simplifying the complexity of data queries in sharded environments and improving query efficiency.

Background Information

As enterprises grow, sharding databases and tables becomes a common database architecture choice. In this scenario, efficient solutions are needed for querying and maintaining data to avoid the tedious manual process of querying, updating, and aggregating multiple sharded databases and tables one by one.

The introduction of the database grouping change feature has partly addressed the issue of making changes in sharded environments and has been well-validated and utilized by many enterprises. However, for query requirements, enterprises desire the convenience of operating on sharded databases and tables as if they were a single table. This approach caters to more data processing scenarios, and it is a critical functionality that Hikvision Ezviz heavily relies on.

Prerequisites

• You have created or joined an organization, and this organization has subscribed to either or . Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
• Your current account has switched to the target organization. For more information, please refer to Switching to an Organization.

Creating a Database Group

This step involves grouping multiple databases for unified query operations. A single SQL query will be executed across all databases within the same group.

1. Log in to the NineData Console.

2. In the left navigation bar, click >.

3. Click the tab, then click on the page > Target Data Source.

4. Configure the form according to the table below, then click .

   Parameter	Description
   	Enter the name of the database group, which should start with an English character and can only contain English characters, numbers, and underscores. For easier use and management, use a meaningful name.
   (optional)	Enter a description for the database group.
   	Select the environment name for your business to filter data sources accordingly.
   	Click to add a data source. : Select the data sources to be added to the group. Multiple selection, full selection, and reverse selection are supported. You can also search by entering the data source name. : Enter a database expression. The system will automatically traverse and extract all databases in the target data source that match the expression you provide. For the expression syntax, see Appendix of this document. After clicking , if you have configured , the system will automatically fetch the databases that meet the criteria. If you need to select additional databases, you can manually choose them from the right-hand database area.

Database Grouping Specifications and Process

After creating a database group, the SQL development specifications and approval processes configured in the environment are used by default. You can change them according to your business needs. For more information, see Modify SQL Development Specifications and Approval Processes for Database Groups.

Creating a Table Group

Compared to database groups, table groups are used for more granular table configurations. A single SQL query will be executed across all tables within the same table group. Creating a table group requires that a database group has already been created.

1. Log in to the NineData Console.

2. In the left navigation bar, click >.

3. Click the tab, then click the target database group name or click in the right column of the database group > .

4. On the page, click .

5. Configure the form according to the table below, then click .

   Parameter	Description
   	Enter the name of the table group, which should start with an English character and can only contain English characters, numbers, and underscores. For easier use and management, use a meaningful name.
   	Supports and methods.
   	Required if is set to . Enter an expression and click . The system will automatically extract all tables from the target database that match the expression. For expression syntax, see the Appendix.
   (optional)	Optional if is set to . Configure the parameter according to the routing algorithm configured in your application to quickly resolve the tables to be accessed, improving query efficiency.
   	Required if is set to . Click to select the data sources to be added to the group. Supports multiple selection, full selection, and reverse selection, and you can search for data sources by name. After clicking , select specific databases and tables, and schemas if required by the data source.

   tip

   To quickly route to the table that meets the query condition using and obtain the query result at the fastest speed, the following three conditions must be met; otherwise, the query will traverse all sharded databases and tables.

   • The table group is created using an expression, not manual selection.
   • is correctly configured in the table group creation process, and this algorithm must match the routing algorithm configured in your application.
   • The routing field in the WHERE condition of the query SQL must be an equality condition.

Modifying SQL Development Specifications and Approval Processes for Database Groups

When executing SQL queries on a target table group, non-standard SQL statements (e.g., slow SQL) can severely impact database performance. To address this, you can configure SQL development specifications for the target database group. These specifications serve as the basis for the system to automatically review user SQL. Non-compliant SQL will be automatically intercepted.

1. Log in to the NineData Console.

2. In the left navigation bar, click >.

3. On the page, click the tab, then click in the right column of the target database group.

4. In the window, uncheck the box to the left of , then select the desired specifications and processes, and click .

   Rechecking the box for will restore the default settings.

Executing Sharded Database and Table Queries

To execute queries on grouped databases and tables, use the SQL Console to perform the SQL query on the table group directly.

Steps

1. Log in to the NineData Console.

2. In the left navigation bar, click >, and select any data source to enter.

   If you have previously logged into a data source and have not logged out, you will automatically enter that data source page.

3. In the top left of the page, click the right of the current data source name . In the popup window, click the left dropdown under , select , then click the right dropdown and select the target database group to query.

4. You can now use the SQL Console to query the target database group. For more details on using the SQL Console, refer to SQL Console.

Appendix: Table Group Expression Syntax

NineData supports automatically adding tables with continuous names to a table group using expressions. The expression syntax is as follows:

• <dbname>, <tablename>: Represent the database and table names.
  • <prefix>: Prefix of the database or table name.
  • <suffix>: Suffix of the database or table name.
• <expression>: Specific expression with the following rules:
  • <range>: Range represented by a hyphen, e.g., 1-9.
  • <steps>: Step size used with <range>, in the format <range>:<steps>, indicating every nth element within the range. For example, 0-8:2 includes 0, 2, 4, 6, 8.
  • <enumeration>: Enumeration of numbers separated by commas, e.g., 1,3,5.
• <delimiter>: Dot separating database and table names, indicating hierarchy, e.g., dbname.tablename.

Syntax	Description	Example
<prefix>[<range>]	Prefix with a numeric range suffix, adding tables in natural order.	user_[0-9] adds tables user_0, user_1, ..., user_9.
<prefix>[<range>:<steps>]	Prefix with a numeric range and step size, adding tables at each step.	user_[0-9:3] adds tables user_0, user_3, user_6, user_9.
<prefix>[<expression>]<suffix>	Prefix and suffix with an expression, for tables with embedded numbers.	user_[0-1]_sample adds tables user_0_sample and user_1_sample.
<prefix>[<expression>][<expression>]	Prefix with multiple numeric expressions, for tables with multiple numbers.	user_[0-2]_[0-2] adds tables user_0_0, user_0_1, ..., user_2_2.
<dbname_prefix>[<expression>].<tablename>	Prefix with a numeric expression for databases containing the same table.	db_[0-2].user adds user tables from databases db_0, db_1, db_2.
<dbname_prefix>[<expression>].<tablename_prefix>[<expression>]	Prefix with numeric expressions for both databases and tables.	db_[0-2].user_[0-5] adds tables user_0 to user_5 from db_0 to db_2.
<prefix>[<enumeration>]	Prefix with enumerated table names.	user_[1,3,5] adds tables user_1, user_3, user_5.
<dbname_prefix>[<expression>].<tablename_prefix>[[<expression>]]	Prefix with numeric expressions for databases and tables with multiple patterns.	db_[0-2].user_[[0-2]] adds tables user_0, user_1, user_2 from db_0 to db_2.

Appendix 2: Routing Algorithm Description

The main function of routing algorithms is to automatically complete data routing. In routing algorithm configuration, target sharding of databases and tables is defined through the following expressions:

'<dbname_expression>''.<tablename_expression>'

• <dbname_expression>: Database name expression in the format: '<dbname_prefix>'+(<expression>)+'<dbname_suffix>'.

  • '<dbname_prefix>': The prefix of the database name, such as 'logical_db_0'.
  • (<expression>): The dynamic numeric part of the database name composition, e.g., #user_id#%4. Suppose the value of the user_id column is 1. Dividing 1 by 4 and taking the remainder gives 1, resulting in a database name of logical_db_01 when combined with the prefix. NineData specifies using # to enclose field names in routing algorithms for easier parsing.
  • '<dbname_suffix>': The suffix of the database name, which can be configured as needed or left empty, e.g., '_bak'. The final database name would be logical_db_01_bak.

  Example: If the result of #user_id#%4 is 0, the corresponding database routing <dbname_expression> would be written as follows:

  Target Database Name	<dbname_expression> Example
  logical_db_01	'logical_db_0'+(#user_id#%4+1)
  logical_db_01_bak	'logical_db_0'+(#user_id#%4+1)+'_bak'
  logical_db_00	'logical_db_0'+(#user_id#%4)
  logical_db_1	'logicaldb'+(#user_id#%4+1)

• .<tablename_expression>: Table name expression in the format: '.<tablename_prefix>'+(<expression>)+'<tablename_suffix>'.

  • '.<tablename_prefix>': The prefix of the table name, such as '.test_time_0'. The dot (.) indicates the table belongs to the preceding database.
  • (<expression>): The dynamic numeric part of the table name composition, e.g., #user_id#%4. Suppose the value of the user_id column is 1. Dividing 1 by 4 and taking the remainder gives 1, resulting in a table name of test_time_01 when combined with the prefix. NineData specifies using # to enclose field names in routing algorithms for easier parsing.
  • '<tablename_suffix>': The suffix of the table name, which can be configured as needed or left empty, e.g., '_bak'. The final table name would be .test_time_01_bak.

  Example: If the result of #user_id#%4 is 0, the corresponding table routing .<tablename_expression> would be written as follows:

  Target Table Name	<tablename_expression> Example
  test_time_01	'.test_time_0'+(#user_id#%4+1)
  test_time_01_bak	'.test_time_0'+(#user_id#%4+1)+'_bak'
  test_time_00	'.test_time_0'+(#user_id#%4)
  test_time_1	'.testtime'+(#user_id#%4+1)

Using the examples above, if the value of the user_id column is 0, the following routing algorithm will route to the test_time_01 table in the logical_db_01 database:

'logical_db_0'+(#user_id#%4+1)'.test_time_0'+(#user_id#%4+1)