You can use the SQL query feature to perform complex queries and analytics on data in Tablestore in an efficient manner. Tablestore 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. 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 concepts in conventional databases. The following table describes some concepts in conventional databases and the mappings between the concepts and Tablestore concepts.

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
  • 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 schema of the mapping table is the same as the primary key schema of 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