All Products
Search
Document Center

Tablestore:Features

Last Updated:Jan 16, 2024

The core features of search indexes include queries based on primary key columns and non-primary key columns, Boolean query, Geo query, full-text search, fuzzy query, prefix query, exists query, nested query, collapse (distinct), sorting, match all query, and aggregation. You can use these features based on your business requirements. Some features of search indexes have equivalent SQL statements.

Core features

Search indexes support multi-dimensional data queries and common data statistical analysis. The following table describes the core features of search indexes.

Feature

Description

References

Queries based on primary key columns and non-primary key columns

Queries that are based on only primary key columns or prefixes of primary key columns cannot meet requirements in some scenarios.

If you want to query data based on a non-primary key column, you can include the column in a search index and perform queries based on the non-primary key column.

Term query

Boolean query

Boolean queries are suitable for order scenarios. In order scenarios, a table may contain dozens of fields. It may be difficult to determine how to combine the fields required for queries when you create a table. Even if you can determine how to combine the required fields, hundreds of combinations may be available.

If you use a relational database service, you may have to create hundreds of indexes. In addition, if no index is created for a specific combination in advance, you cannot query the required data.

You can use Tablestore to create a search index that includes all the fields that may be required and freely combine these fields in queries. Search indexes also support logical operators such as AND, OR, and NOT.

Boolean query

Geo query

As mobile devices gain popularity, geographical location data becomes increasingly important. This data is used in most apps such as social media apps, food delivery apps, sports apps, and Internet of Vehicles (IoV) apps. These apps must support query features for the geographical location data that these apps provide.

Search indexes support queries based on the following geographical location data:

  • Near: queries points within a specific radius based on a central point, such as the People Nearby feature in WeChat.

  • Within: queries points within a specific rectangular or polygonal area.

Tablestore allows you to use these features to query geographical location data without the need to use other database services or search engines.

Full-text search

Search indexes can tokenize data to support full-text search. However, unlike search engines, Tablestore returns only BM25-based results. Tablestore does not return custom relevant results in response to a query. If you need to query relevant results, we recommend that you use search engines.

Search indexes support the following tokenization methods: single-word tokenization, delimiter tokenization, minimum semantic unit tokenization, maximum semantic unit tokenization, and fuzzy tokenization. You can select tokenization methods based on your requirements.

Tokenization

Fuzzy query

Search indexes support queries based on wildcards. This feature is similar to the LIKE operator in relational databases. You can specify characters and wildcards such as question marks (?) or asterisks (*) to implement the feature similar to the LIKE operator.

Prefix query

Search indexes support prefix queries. For example, in a query based on prefix apple, results such as apple6s and applexr are returned. This feature is applicable to any natural languages.

Prefix query

Exists query

An exists query is also called a NULL query or a NULL-value query. This type of query is used in sparse data to determine whether a column of a row exists.

Exists query

Nested query

In addition to a flat structure, online data such as tagged pictures has complex and multilayered structures. For example, a database stores a large number of pictures, and each picture has multiple elements such as houses, cars, and people. Each element in a picture has a unique weight score. The score is evaluated based on the size and position of an element in a picture. Therefore, each picture has multiple tags. Each tag has a name and a weight score. You can use nested queries to query data based on the data tags. Image tags are stored in the JSON format. Example:

{
 "tags": [
   {
      "name": "car",
      "score": 0.78
   },
   {
      "name": "tree",
      "score": 0.24
   }
 ]
}

Nested queries can be used to query data with multiple layers of logical relationships and greatly facilitate the modeling of complex data.

Collapse (distinct)

Search indexes support the collapse (distinct) feature. This feature allows you to specify the highest frequency of occurrence of an attribute value to achieve high cardinality. For example, in e-commerce scenarios, if you search for laptop, the first page may display the laptops of the same brand. The results are not user-friendly. The collapse (distinct) feature can help query distinct values.

Collapse (distinct)

Sorting

Tablestore allows you to sort data in alphabetical order based on the primary key. If you want to sort data based on other fields, you can use the sorting feature of search indexes.

Search indexes support sorting in ascending order or descending order based on one or more conditions. The sorting is globally performed on all data in a search index. By default, the returned results of a search index are sorted based on the primary key in the data table.

Match all query

You can use search indexes to return the total number of rows that match the query conditions. This feature applies to data verification and data-driven operations.

  • If you configure an empty query condition when you perform a match all query by using a search index, all data in the search index matches the query condition. In this case, the total number of rows that is returned is the total number of rows in the search index.

  • If you do not write data during the query and all data in the data table is indexed, the total number of rows that is returned is the total number of rows in the data table.

Aggregation

Search indexes allow you to perform aggregation operations to obtain the maximum value, minimum value, average value, sum, count and distinct count of rows, percentiles, and histogram statistics. You can also use search indexes to group results. This allows you to perform lightweight statistical analysis.

Aggregation

Mappings between SQL statements and the features of search indexes

Some features of search indexes have equivalent SQL statements. The following table describes the mappings between SQL statements and the features of search indexes.

SQL

Search index feature

References

Show

API operation: DescribeSearchIndex

DescribeSearchIndex

Select

Parameter: ColumnsToGet

Term query

From

Parameter: index name

Important

Single-column indexes are supported. Multi-column indexes are not supported.

Term query

Where

Query: a variety of query methods such as term query

Term query

Order by

Parameter: sort

Sorting and paging

Limit

Parameter: limit

Sorting and paging

Delete

API operation: DeleteRow after a query

  1. Obtain the primary key of rows

  2. Delete data

Like

Query: WildcardQuery

Wildcard query

And

Parameter: operator = and

Boolean query

Or

Parameter: operator = or

Not

Query: BoolQuery(mustNotQueries)

Between

Query: RangeQuery

Range query

Null

Query: ExistsQuery

Exists query

In

Query: TermsQuery

Terms query

Min

Aggregation: min

Aggregation

Max

Aggregation: max

Avg

Aggregation: avg

Count

Aggregation: count

Count(distinct)

Aggregation: distinctCount

Sum

Aggregation: sum

Group By

GroupBy