You can use the SQL query feature to perform complex queries and analytics on data in Tablestore in an efficient manner. The SQL query feature provides a unified access interface for multiple data engines.
Background information
Tablestore is a storage service that is developed by Alibaba Cloud. Tablestore can store large volumes of structured data by using multiple models, and supports fast data query and analytics. Empowered by the distributed storage and index-based search engine, Tablestore can store petabytes (PBs) of data while delivering tens of millions transactions per second (TPS) at ultra-low latency (milliseconds). You can use Tablestore to store and query large volumes of data in a convenient manner.
Tablestore provides cloud-native SQL engine capabilities in addition to the conventional storage of NoSQL structured data. SQL queries are compatible with MySQL query syntax and provide basic SQL Data Definition Language (DDL) statements to create tables. This way, you can migrate your business to Tablestore and access Tablestore by executing SQL statements. For existing data tables, you can execute the CREATE TABLE statement to create mapping tables for the existing data tables. Then, you can use SQL statements to access the data in the existing data tables.
The SQL query feature allows you to use search indexes to quickly query data that meets query conditions. When you use the SQL query feature, the system automatically selects an appropriate method to accelerate SQL access based on the SQL statements.
The SQL query feature is suitable for scenarios in which you want to access a large amount of data online. When you use the SQL query feature, the access latency ranges from milliseconds to minutes. The SQL query feature supports point queries based on the primary keys of data tables (GetRow operation), term queries based on search indexes (TermQuery), and queries based on the aggregation capability of search indexes, such as queries on the number of rows that meet a specified condition and the sum of values in a column.
Terms
The use of SQL involves many terms in conventional databases. The following table describes some terms in conventional databases and the mappings between the terms and Tablestore terms.
| Term | Description |
|---|---|
| Database | A database is a data repository that organizes, stores, and manages data based on data structures. A database can contain one or more tables. Databases are mapped to Tablestore instances. |
| Table | A table consists of rows and columns. Tables in databases are mapped to Tablestore tables. |
| Index | Indexes in databases are mapped to secondary indexes or search indexes in Tablestore. |
Features
- SQL features
- Allows you to initiate a single request by executing a single SQL statement.
- Supports basic DDL statements, including CREATE TABLE, ALTER TABLE, DROP MAPPING TABLE, and DESCRIBE. For more information, see Create mapping tables for tables, Create mapping tables for search indexes, Update attribute columns of mapping tables, Delete mapping tables, and Query the information about a table.
- Supports Data Query Language (DQL) statements, including SELECT. For more information, see Query data.
- Supports basic database administration statements, including SHOW TABLES and SHOW INDEX. For more information, see List table names and Query the index information about a table.
- Character set and collation
- Character set: UTF-8
- Collation: binary collation
- Operators
Supports SQL operators such as arithmetic operators, relational operators, and logical operators. For more information, see SQL operators.
- Mapping tables for existing tables
You can execute the CREATE TABLE statement to create mapping tables for existing tables. When you create a mapping table, make sure that the primary key in the mapping table is the same as the primary key in the existing table, and the attribute columns in the mapping table have the same types as the attribute columns and predefined columns in the existing table. For more information about data type mappings, see Data type mappings.
Usage notes
When you use the SQL query feature, the transaction feature is not supported.
Limits
For more information, see SQL limits.
Case conventions in SQL
The Tablestore SQL engine follows common SQL conventions and is not case-sensitive
to column names. For example, the SELECT Aa FROM exampletable; statement is equivalent to the SELECT aa FROM exampletable; statement.
The column names in Tablestore are case-sensitive. When SQL is used, the column names in Tablestore are converted into lowercase letters for matching. For example, if you want to perform operations on the Aa column in a Tablestore table, you can use AA, aa, aA, or Aa in SQL. Therefore, the column names in Tablestore cannot be AA, aa, aA, and Aa at the same time.
Reserved words and keywords
Tablestore uses keywords in SQL statements as reserved words. If you need to use keywords
to name tables or columns, add the ‘’ symbol to escape the keywords. Keywords are not case-sensitive.
For more information about the reserved words and keywords, see Reserved words and keywords.
Comparison between SQL and search index
| Search index | SQL function/statement | |
|---|---|---|
| Term query | Equal (=) | |
| Range query | Greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and BETWEEN ... AND ... | |
| Boolean query | MustQueries | AND |
| MustNotQueries | != | |
| ShouldQueries | OR | |
| Sorting and pagination | FieldSort | ORDER BY |
| SetLimit | LIMIT | |
| Aggregation | Minimum value | MIN() |
| Maximum value | MAX() | |
| Sum | SUM() | |
| Average value | AVG() | |
| Count | COUNT() | |
| Distinct count | COUNT(DISTINCT) | |
| Query the rows that are obtained from the results of an aggregation operation in each group | ANY_VALUE() | |
| Group by field value | GROUP BY | |
Billing
For more information, see Billable items of SQL query.