All Products
Search
Document Center

Tablestore:Overview

Last Updated:Jun 20, 2024

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

  • 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 China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Indonesia (Jakarta), India (Mumbai), Germany (Frankfurt), SAU (Riyadh - Partner Region), and US (Virginia) regions.

Limits

For more information, see SQL limits.

Procedure

image

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

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