Skip to main content

Query sharded databases and tables

Use NineData database and table group query to run one SQL statement across sharded databases and tables. NineData routes the query to the matching shards, executes the subqueries, and returns the merged result.

Challenges In Sharded Queries​

Sharding improves storage and access scalability, but it also makes ad hoc queries harder:

  • Data is split across multiple databases and tables.
  • Developers may need to connect to each shard and combine results manually.
  • Routing rules must be calculated correctly for each query.
  • Manual query and aggregation work increases operational risk.

How NineData Helps​

NineData table groups simplify query work in sharded environments:

  • Write a single SQL statement instead of querying each shard separately.
  • Use a routing algorithm to locate the matching physical tables.
  • Execute distributed subqueries and return a combined result.
  • Query sharded data from SQL Console in a familiar workflow.

Prerequisites​

Before you create a table group query, make sure that:

  • The target data sources have been added to NineData.
  • The sharded databases are included in a database group.
  • You know the table naming pattern and routing algorithm.
  • You have permission to create table groups and run SQL Console queries.

Example​

This example uses an e-commerce order dataset. There are eight sharded tables, orders_0 through orders_7, and each table stores order data with order_id values from 1 to 100. The sharding rule is based on order_id % 8.

1. Create A Table Group​

Create a table group

  1. Use the expression orders[1-2].orders_[0-7] to add the eight sharded tables to the table group.
  2. Enter #order_id#%8 as the routing algorithm.
  3. Select Create Table Group.

2. Query The Table Group​

Open the table group in SQL Console

  1. Create a query in SQL Console.
  2. Select the database group that contains the table group.
  3. Enter a SQL statement that includes the routing field used by the routing algorithm. In this example, the routing field is order_id.

Example:

SELECT *
FROM orders2.orders_0
WHERE order_id < 50
  AND order_date > '2003-01-01';

View the sharded query result

Result​

NineData distributes the query to the matching databases and tables, aggregates the results, and returns them in SQL Console. This gives users a single-query experience while keeping the underlying sharded architecture.

Next Steps​