Principle:Apache Shardingsphere Column Based Routing
| Knowledge Sources | |
|---|---|
| Domains | Database_Routing, Shadow_Testing |
| Last Updated | 2026-02-10 00:00 GMT |
Overview
Using column values present in SQL statements to determine whether a DML operation should be routed to a shadow database, enabling data-driven shadow routing decisions based on the actual content of INSERT, UPDATE, DELETE, and SELECT operations.
Description
Column Based Routing is a shadow routing strategy in which the decision to redirect a DML statement to a shadow database is derived from the actual values of designated shadow columns within the SQL statement itself. Unlike hint-based routing, which relies on external metadata, column-based routing inspects the SQL's content: the values being inserted, the conditions in WHERE clauses, or the values being set in UPDATE statements. A shadow column is a specially designated column (e.g., is_shadow, shadow_flag) whose value indicates whether the row is shadow data.
This principle addresses the scenario where shadow traffic is distinguished at the data level rather than at the session or request level. For example:
- An INSERT statement with shadow_flag = true in its values should be routed to the shadow database
- A DELETE statement with WHERE shadow_flag = 1 should target the shadow database
- A SELECT statement filtering on a shadow column should read from the shadow database
The column-based routing evaluation follows a multi-level matching process:
- Shadow table identification: Only tables that are configured as shadow tables in the shadow rule are candidates for column-based evaluation.
- Shadow column discovery: For each shadow table, the rule is consulted to find which columns are designated as shadow columns for the current operation type (INSERT, UPDATE, DELETE, SELECT).
- Column condition extraction: The actual column values are extracted from the SQL statement. This step is statement-type-specific: INSERT statements extract from the values clause, while UPDATE/DELETE/SELECT extract from WHERE conditions. This extraction is delegated to subclasses.
- Algorithm evaluation: Each extracted column condition is evaluated against the registered column shadow algorithms for that table and column combination. If any algorithm returns true for all values of a column condition, the table's data source mappings are returned.
The column-based retriever is only invoked as a fallback when hint-based routing does not produce a result, ensuring that hints take priority over column inspection.
Usage
Use Column Based Routing when:
- You need shadow routing decisions to be driven by specific column values within the SQL statement
- Your shadow traffic is distinguished by a designated column (e.g., a boolean flag or a special identifier) rather than by external session hints
- You want different shadow routing behavior for different tables based on their column content
- You are implementing a DML-specific shadow retriever that must extract column conditions from INSERT, UPDATE, DELETE, or SELECT statements
Theoretical Basis
The column-based routing algorithm proceeds through a cascading match across tables, columns, and algorithms:
FUNCTION retrieveByColumn(shadowRule, shadowTables, operationType):
FOR EACH tableName IN shadowTables:
shadowColumnNames = shadowRule.getShadowColumnNames(operationType, tableName)
IF shadowColumnNames is not empty:
IF isMatchAnyColumnShadowAlgorithm(shadowRule, tableName, shadowColumnNames):
RETURN shadowRule.getShadowDataSourceMappings(tableName)
RETURN empty map
FUNCTION isMatchAnyColumnShadowAlgorithm(rule, table, columnNames):
FOR EACH columnName IN columnNames:
algorithms = rule.getColumnShadowAlgorithms(operationType, table, columnName)
IF algorithms is empty:
CONTINUE
columnConditions = extractShadowColumnConditions(columnName) // abstract, statement-specific
FOR EACH condition IN columnConditions:
IF isMatchColumnShadowAlgorithm(table, algorithms, condition):
RETURN true
RETURN false
FUNCTION isMatchColumnShadowAlgorithm(table, algorithms, condition):
FOR EACH algorithm IN algorithms:
IF ColumnShadowAlgorithmDeterminer.isShadow(algorithm, new ShadowCondition(table, operationType, condition)):
RETURN true
RETURN false
Key properties of this algorithm:
- First-match table semantics: The first shadow table with matching column algorithms determines the returned data source mappings. Remaining tables are not evaluated.
- Abstract column extraction: The getShadowColumnConditions method is abstract, allowing each DML statement type (Insert, Update, Delete, Select) to implement its own logic for extracting column values from its specific SQL structure.
- All-values evaluation: For a given column condition, the ColumnShadowAlgorithmDeterminer evaluates each value in the condition. All values must pass the algorithm check for the condition to match (conjunctive logic within a single condition).
- Any-algorithm semantics: If multiple algorithms are registered for the same column, any single algorithm matching is sufficient (disjunctive logic across algorithms).
- Table name consistency check: During column algorithm evaluation, the shadow condition's table name must match the column condition's table name. This prevents cross-table mismatches.