Apache ShardingSphere 5.0.0-Beta is the first in-depth analysis of the ShardingSphere kernel principles, and detailed the release of the kernel level optimizations and improvements, especially in the SQL capabilities. A strong and stable kernel is the basis for the sustainable development of ShardingSphere. At the same time, ShardingSphere is also trying its best to explore the ecological characteristics of distributed database. DistSQL, released in 5.0.0-beta, is designed to work with the ShardingSphere distributed database architecture, providing flexibility, convenience, and elegance while providing a more standardized approach to distributed database management.

This article takes you through distSQL and shows you how to use it to manage ShardingSphere distributed database services with a single click, combined with a real life case study.

Meng haoran

Sphereex Senior Java Engineer

Apache ShardingSphere Committer

I used to work for JD Technology, responsible for the research and development of database products. I love open source and pay attention to database ecology. Currently, I focus on the development of Shardingsphere database middleware and the construction of open source community.

I met DistSQL

SQL (Structured Query Language) is a kind of data Query and programming Language. It is also used as the standard Language of relational database management systems, for accessing data, querying, updating and managing relational database systems.

Similar to standard SQL, distSQL, or Distributed SQL, is a built-in SQL language unique to ShardingSphere that provides incremental functionality beyond standard SQL. With ShardingSphere’s powerful SQL parsing engine, distSQL provides a syntax structure and syntax validation framework similar to standard SQL, making distSQL more flexible while keeping it normalized.

The Database Plus concept proposed by Shardingsphere aims to build an open source distributed Database system that is both Database and fit for practical business needs. DistSQL is built on top of traditional Database. Provide SQL capabilities that are both standard compliant and featured by ShardingSphere, which can better enable traditional databases.

DistSQL is designed for the purpose

In the past few years of rapid development, ShardingSphere has become a school of its own in the field of database middleware with the gradual stabilization of the kernel and the continuous polishing of the core functions. As a leader in open source in China, ShardingSphere does not stop in its exploration of distributed database ecology. Breaking the boundary between middleware and database and allowing developers to use Apache ShardingSphere as if they were using database is distSQL’s design goal. It is also an integral part of ShardingSphere’s ability to transform from a developer-oriented framework and middleware to an operations-oriented infrastructure product.

DistSQL syntax architecture

DistSQL was designed to be standards-oriented, taking into account the habits of database developers and operators, so the syntax of distSQL is fully borrowed from the standard SQL language, while keeping the ShardingSphere’s own characteristics as much as possible, while keeping readability and ease of use. It also provides the broadest customization boundaries for consumers to cope with different business scenarios. For developers familiar with SQL and ShardingSphere, this is a quick start.

While standard SQL provides different types of syntax, such as DQL, DDL, DML, and DCL, for defining SQL statements with different functions, distSQL also defines its own syntax.

In ShardingSphere, distSQL syntax is currently divided into three types: RDL, RQL and RAL:

  • RDL (Resource & Rule Definition Language) : Resource Rule Definition Language for the creation, modification, and deletion of resources and rules;
  • RQL (Resource & Rule Query Language) : Resource Rule Query Language, used for Query and presentation of resources and rules;
  • RAL (Resource & Rule Administrate Language) : Resource Rule management Language for hints, transaction type switching, sharding execution plan queries, and other incremental functions.

DistSQL’s syntax, which is a bridge to ShardingSphere’s path to distributed databases, is still being refined, and will grow stronger as more ideas are implemented. Developers interested in joining ShardingSphere are also welcome to contribute ideas and code to distSQL.

More detailed rules of grammar may refer to the official document: https://shardingsphere.apache…

DistSQL of actual combat

After understanding the design concept and syntax system of distSQL, this paper will take data sharding as an example to demonstrate how to build a data sharding service based on ShardingSphere.

Environment to prepare

  • MySQL > start MySQL
  • Create a MySQL database for sharding
  • Start the ZooKeeper service

Open distributed governance configuration and start the ShardingSphere – Proxy (https://shardingsphere.apache…

Practical demonstration

  • Connect to the started ShardingSphere-Proxy using the MySQL command line
  • Create and query the distributed database SHARDING_DB

  • Use the newly created database

  • Perform RDL to configure two data source resources DS_1 and DS_2 for sharding

  • Execute the RQL query for the newly added data source resource

  • Execute the RDL to create the sharding rule for the T_ORDER table

  • Execute the RQL query sharding rules

In addition to querying all the sharding rules under the current database, RQL can also query the sharding rules of a single table with the following statement

  • Create and query the T_ORDER shard table

  • After ShardingSphere-Proxy successfully created the sharding table T_ORDER, ShardingSphere automatically created the sharding table according to the sharding rules of T_ORDER by connecting to the underlying databases DS_1 and DS_2 through the client

  • After the sharding table is created, continue to execute SQL statement in ShardingSphere-Proxy side to insert data

  • Execute the plan through RAL queries

At this point, the sharding of ShardingSphere data using distSQL is complete. Compared to the previous version of the shardingSphere proxy, which was driven by configuration files, distSQL is more developer-friendly and has more flexible management of resources and rules. The SQL-driven approach is a seamless integration between distSQL and standard SQL.

The examples above demonstrate only a small portion of the DistSQL syntax in use. In addition to creating and querying resources and rules through CREATE and SHOW statements, DistSQL also provides Altre Drop and much more. At the same time, it supports configuration control of core functions of data sharding, read-write separation, data encryption and database discovery.

As one of the new features in the 5.0.0-beta release, distSQL will continue to build a more complete syntax and provide more power from this release.

DistSQL offers ShardingSphere unlimited possibilities in the field of distributed databases, and in the future plans, distSQL will serve as a link to connect more functions and provide one-click operations. For example, key analysis of the overall state of the database, and elastic migration docking, to provide a key data expansion, shrinkage capacity; And control docking, to achieve a key switch between master and slave, change the database state and so on.

Finally, please pay more attention to the community and learn the latest progress of ShardingSphere project.

Welcome to follow us