Unified query for sharded databases and tables

Use NineData unified query to read sharded databases and tables from SQL Console through database groups, table groups, and routing algorithms.

Overview

When an application uses database or table sharding, one business query may read from multiple physical databases or tables. Querying each shard manually takes time and can lead to incomplete results or repeated work.

NineData uses database groups and table groups to give SQL Console a single query target. If you configure a routing algorithm, NineData can route eligible queries to the matching shard instead of scanning every database and table.

Before you begin

• You have created or joined an organization, and this organization has subscribed to either DevOps Pro or DevOps Enterprise. 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.

Create a database group

Create a database group when the query needs to cover multiple databases. A query can then run across all databases in the group.

1. Sign in to the NineData Console.

2. Go to Datasource > Datasource.

3. Click the Database Grouping tab, then click Create Database Grouping > Target Data Source.

4. Fill out the form, then click Create Database Grouping.

   Parameter	Description
   Database Grouping Name	Enter the database group name. Use letters, digits, and underscores only, and start with a letter. Use a meaningful name so the group is easy to identify later.
   Description (optional)	Enter a description for the database group.
   Environment	Select the environment that contains the target data sources. The data source list is filtered by this environment.
   Database	Click Add Datasource to add data sources. Datasource: Select the data sources to add to the group. Select multiple data sources, select all, reverse the selection, or search by data source name. Database Expression: Enter a database expression. NineData scans the target data source and extracts databases that match the expression. For syntax details, see Table group expression syntax. After you click OK, NineData fetches the databases that match Database Expression. Select additional databases from the pane on the right if required.

Database group standards and approvals

After a database group is created, it uses the SQL development standards and approval process configured for the corresponding environment by default. Adjust them for the group if your business process requires it. See Modify SQL development standards and approval processes for database groups.

Create a table group

Use table groups for finer-grained queries across sharded tables. A query can run against all tables in the group. Create the required database group before you create a table group.

1. Sign in to the NineData Console.

2. Go to Datasource > Datasource.

3. Click the Database Grouping tab, then click the target database group name, or click Details in the Actions column for the database group.

4. On the Database Grouping Detail page, click Create Table Grouping.

5. Fill out the form, then click Create Table Grouping.

   Parameter	Description
   Table Grouping Name	Enter the table group name. Use letters, digits, and underscores only, and start with a letter. Use a meaningful name so the group is easy to identify later.
   Methods	Select Add Manually or Add by Expression.
   Add by Expression	Use this option when Methods is Add by Expression. Enter an expression and click Auto Fetch. NineData extracts tables from the target database that match the expression. For syntax details, see Table group expression syntax.
   Routing Algorithm (optional)	Configure this option when Methods is Add by Expression. Set it to match the routing algorithm used by your application so NineData can resolve the target tables more efficiently.
   Database	Use this option when Methods is Add Manually. Click Add Datasource to select the data sources to add to the group. Select multiple data sources, select all, reverse the selection, or search by data source name. After you click OK, select the target databases and tables and, if required by the data source type, the target schemas.

   tip

   To route a query to the matching table by using Routing Algorithm, all of these conditions must be met. Otherwise, NineData scans all sharded databases and tables.

   • The table group is created using an expression, not manual selection.
   • Routing Algorithm is correctly configured during table group creation, and it matches the routing algorithm configured in your application.
   • The routing field in the WHERE condition of the query SQL is an equality condition.

Modify SQL development standards and approval processes for database groups

Non-standard SQL, such as slow SQL, can affect database performance when a query runs across sharded tables. Configure SQL development standards for the target database group so NineData can review user SQL and block non-compliant statements.

1. Sign in to the NineData Console.

2. Go to Datasource > Policy & Process.

3. On the Policy & Process page, click the Database Grouping Configuration tab, then click Edit in the target database group row.

4. In the Edit Group Config window, clear the checkbox next to Inheriting Envir. Config, select the required standards and process, and then click OK.

   Re-selecting Inheriting Envir. Config restores the default settings.

Query sharded databases and tables

Use SQL Console to query a database group or table group directly.

1. Sign in to the NineData Console.

2. Go to DevOps > SQL Console and open a data source.

   If you already opened a data source and left it open, NineData takes you back to that page.

3. In the upper-left corner, click next to the current data source name. In the dialog, open the left drop-down list under Datasource, select Database Grouping, and then select the target database group from the right drop-down list.

4. Use SQL Console to query the target database group. See SQL Console for SQL Console operations.

Result

SQL Console runs the query against the selected database group or table group. If a routing algorithm is configured and the query condition matches the routing field, NineData routes the query to the matching shards instead of scanning every table.

Appendix: Table group expression syntax

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

• <dbname>, <tablename>: Database and table names.
• <prefix>: Prefix of a database or table name.
• <suffix>: Suffix of a database or table name.
• <expression>: An expression that can contain:
  • <range>: A range represented by a hyphen, such as 1-9.
  • <steps>: A step size used with <range> in the <range>:<steps> format. For example, 0-8:2 includes 0, 2, 4, 6, and 8.
  • <enumeration>: Numbers separated by commas, such as 1,3,5.
• <delimiter>: A dot that separates a database name and a table name, such as 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: Routing algorithm description

Use routing algorithms to route data to target sharded databases and tables. In routing algorithm configuration, define the target database and table with 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. This value is optional. For example, use '_bak' to produce the final database name logical_db_01_bak.

  Example: If the result of #user_id#%4 is 0, use these database routing <dbname_expression> examples:

  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. This value is optional. For example, use '_bak' to produce the final table name .test_time_01_bak.

  Example: If the result of #user_id#%4 is 0, use these table routing .<tablename_expression> examples:

  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 routes data 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)