Execute SQL statements to query and analyze data - Tablestore

The SQL query feature provides a centralized access interface for multiple data engines of Tablestore and is compatible with the query syntax of MySQL. The SQL query feature is suitable for scenarios in which a large volume of data is stored and you want to access the data online. You can use the SQL query feature to perform complex queries and analytics on data in Tablestore in an efficient manner.

Background information

Tablestore provides the conventional NoSQL storage type that allows you to store structured data and the cloud native SQL engine capabilities. This way, you can smoothly migrate business to Tablestore and execute SQL statements to access Tablestore. The SQL query feature is compatible with the MySQL query syntax and allows you to create tables by executing DDL statements. For existing data tables, you can execute the CREATE TABLE statement to create mapping tables for the data tables. Then, you can execute 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 a suitable method to accelerate SQL access based on the SQL statements.

The SQL query feature is suitable for scenarios in which a large volume of data is stored and you want to access the 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 key 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 specific condition and the sum of values in a column.

Terms

The usage of SQL involves many terms in conventional databases. The following table describes specific terms in conventional databases and the mappings between the terms and Tablestore terms.

Database term	Tablestore term	Description
database	instance	A database is a data repository that organizes, stores, and manages data based on data structures. A database can contain one or more tables.
table	table	A table consists of rows and columns.
index	secondary index and search index	An index is a storage structure that is created to accelerate data queries.

Features

SQL features
- Allows you to initiate a single request by executing a single SQL statement.
- Supports basic DDL statements, including the CREATE TABLE, ALTER TABLE, DROP MAPPING TABLE, and DESCRIBE statements. For more information, see Create a mapping table for a table, Create mapping tables for search indexes, Update attribute columns of mapping tables, Delete mapping tables, and Query information about tables.
- Supports Data Query Language (DQL) statements, including the SELECT statement. You can execute the SELECT statement to perform basic data queries, aggregation, filtering based on conditions, and queries by page. For more information, see Query data.
- Supports basic database administration statements, including the SHOW TABLES and SHOW INDEX statements. For more information, see List mapping table names and Query the index information about a table.
- Allows you to use search indexes to perform data queries, including full-text search, queries based on ARRAY columns, queries based on NESTED columns, and queries based on virtual columns. For more information, see Full-text search, ARRAY supported in search indexes, NESTED supported in search indexes, and Virtual columns of search indexes.
- Allows you to join two or more tables and returns data that meets the join condition and query conditions. For more information, see Join.
- Supports the following JSON functions: ->>, JSON_UNQUOTE, and JSON_EXTRACT. For more information, see JSON functions.
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 and predefined columns in the existing table. For information about data type mappings, see Data type mappings in SQL.

Usage notes

The SQL query feature is suitable for the Wide Column model and the TimeSeries model. For more information, see Wide Column model and TimeSeries model.
When you use the SQL query feature, you cannot use the transaction feature.
Tablestore uses keywords in SQL statements as reserved words. If you want to use keywords to name tables or columns, add the `` symbol to escape the keywords. Keywords are not case-sensitive. For more information, see Reserved words and keywords.
The Tablestore SQL engine follows common SQL conventions. In addition, the column names in the Tablestore SQL engine are not case-sensitive. 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 you use SQL, 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.
The SQL query feature is available in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Indonesia (Jakarta), Japan (Tokyo), Malaysia (Kuala Lumpur), UK (London), US (Silicon Valley), Germany (Frankfurt), SAU (Riyadh - Partner Region), and US (Virginia).

Limits

For more information, see SQL limits.

Procedure

The steps that you can perform to query and analyze data by using the SQL query feature vary based on the data model that is used to store the data. You can use the SQL query feature to query and analyze data in the Wide Column model or TimeSeries model. Perform the steps to query and analyze data based on the data model that you use.

Steps performed to query and analyze data in the Wide Column model

Step	Operation	Description
1	Create a mapping table for a table	Before you use the SQL query feature to query data in a table, you must manually create a mapping table for the table. For more information, see Create a mapping table for a table. If a search index is created for a data table, you can create a mapping table for the search index. This way, you can execute SQL statements to query data based on the search index. For more information, see Create mapping tables for search indexes.
2	Use SQL to query data	You can use the mapping table to query data in the table for which the mapping table or search index is created. You can use the SQL query feature to perform the following query operations: Query data that meets specific conditions. Use the Join function to join two or more tables and return data that meets the join condition and query conditions. For more information, see Join. Use JSON functions to query JSON objects. For more information, see JSON functions. Perform full-text search by using match query or match phrase query. For more information, see Full-text search. Use the `ARRAY_EXTRACT(col_name)` function to query data of the ARRAY type in search indexes. For more information, see ARRAY supported in search indexes. Use operators or the `NESTED_QUERY(subcol_column_condition)` function to query data of the NESTED type in search indexes. For more information, see NESTED supported in search indexes. Use virtual columns to query data that meets specific conditions. For more information, see Virtual columns of search indexes.
3	Use SQL to analyze data	You can use the mapping table to analyze data in the table for which the mapping table is created. You can use the GROUP BY statement to group the result set based on specific conditions. You can also use aggregate functions to collect statistics on and compute data. For more information, see GROUP BY clause (groupby_condition) and Aggregate functions.

Steps performed to query and analyze data in the TimeSeries model

Step	Operation	Description
1	Create a mapping table for a table	Before you use the SQL query feature to query data in a table, you must create a mapping table for the table. You can create the following types of mapping table for a time series table: mapping table in the single-value model, mapping table in the multi-value model, or mapping table for time series metadata. After you create a time series table, the system automatically creates a mapping table in the single-value model and a mapping table for time series metadata for the time series table. You can create a mapping table in the multi-value model to query data. For more information, see Mapping tables for a time series table in SQL.
2	Use SQL to query data	You can use the mapping table to query data in the table for which the mapping table is created. You can use a mapping table in the single-value model or a mapping table in the multi-value model to query time series data and use a mapping table for time series metadata to query time series metadata. For more information, see SQL examples.
3	Use SQL to analyze data	You can use the mapping table to analyze data in the table for which the mapping table is created. The metadata of a time series consists of the metric name, data source, tags, and attributes of the time series. You can use the tag_value_at function to extract the value of a tag (_tags) and the value of an attribute (_attributes). For more information, see SQL examples.

Methods

You can use the SQL query feature in the Tablestore console or Tablestore CLI, or by Tablestore SDKs, JDBC driver, and Tablestore driver for Go.

Billing

You are not charged for using the SQL query feature. However, when you use the SQL query feature, you are charged for the read and write throughput that is generated by operations such as table scanning and index-based queries. For more information, see Billable items of SQL query.

FAQ

References

If you want to accelerate data queries and computing by executing SQL statements, you can create a secondary index or a search index. For more information, see Index selection policy and Computing pushdown.
You can also use computing engines, such as MaxCompute, Spark, Hive, HadoopMR, Function Compute, Flink, and PrestoDB, to compute and analyze data in tables. For more information, see Overview.
To visualize data, you can use Grafana. For example, you can use Grafana to display Tablestore data in charts. For more information, see Connect Tablestore to Grafana.

Appendix: Differences between the SQL query and search index features

The SQL query and search index features provide specific data query and analysis capabilities. The following table describes the capabilities of the SQL query and search index features, and provides the mappings between the capabilities.

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 paging	FieldSort	ORDER BY
Sorting and paging	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