The analytical store feature is primarily used for long-term storage and analysis of time series data. With analytical stores, you can store time series data at a low cost and quickly query and analyze time series data.
Features
Analytical stores are low-cost storage engines designed and optimized by Tablestore for time series scenarios. Analytical stores support efficient data compression and storage and provide powerful query and analysis capabilities, which makes analytical stores ideal for large-scale data analysis tasks.
Analytical stores automatically synchronize data from time series tables. When the data write rate is stable, the synchronization latency is typically within 10 minutes. If the business workload is too high, analytical stores prioritize storage stability, which may slightly increase synchronization latency. Analytical stores operate independently from the data storage in time series tables. You can specify the time to live (TTL) of data in analytical stores. Query operations on analytical stores do not affect the read and write performance of the time series tables.
The following table describes the features supported by analytical stores.
Feature | Description |
Create an analytical store | You can create an analytical store for a time series table when you create the table or create an analytical store for an existing time series table. When you create an analytical store, you can specify the TTL and synchronization mode (including full synchronization and incremental synchronization) for the analytical store. |
Query and analyze data by executing SQL statements | You can query and analyze data in an analytical store by executing SQL statements in which various conditions can be specified to perform aggregation operations. |
Query information of an analytical store | You can query the configuration information, synchronization status, and storage usage of an analytical store. |
Update the TTL of an analytical store | You can update the TTL of an analytical store to extend the retention period of data in the analytical store or delete historical data in the analytical store. |
Delete an analytical store | If you no longer want to use an analytical store, you can delete the analytical store to reduce storage costs. |
Core benefits
Low-cost data storage
Hierarchical storage of hot and cold data: Tablestore uses wide-column stores that feature hybrid row-column storage format to store hot time series data and uses column stores to store full historical time series data.
Storage of data compressed at a high compression ratio: Column stores compress data based on data redundancy by using compression algorithms such as RLE, DICTIONARY, DELTA, and BIT-PACKING to increase storage utilization and reduce storage costs.
Real-time analysis of large amounts of data
For hot time series data, Tablestore uses wide-column stores that feature hybrid row-column storage format to provide real-time auto-increment write, overwrite, and query capabilities for large amounts of data.
For full historical time series data, Tablestore uses column stores. When you query or analyze data, you only need to read the required columns. This increases the query efficiency and data processing speed.
Flexible hierarchical TTL settings
The TTL of an analytical store is independent of the TTL of the time series table for which the analytical store is created.
Usage notes
You can create only one analytical store for a time series table.
The total number of Lastpoint indexes and analytical stores that are created for a time series table cannot exceed 10.
Preparations
Create an instance for the TimeSeries model.
Regions that support the analytical store feature include China (Hangzhou), China (Shanghai), China (Beijing), and China (Zhangjiakou).
Procedure
Create an analytical store
Create an analytical store when creating a time series table
When you create a time series table, the Create Analytical Store switch is automatically turned on.
Create an analytical store for an existing time series table
Go to the Instance Management page.
Log on to the Tablestore console.
In the top navigation bar, select a resource group and a region.
On the Overview page, click the instance alias or click Manage Instance in the Actions column of the instance.
On the Instance Details tab of the Instance Management page, click the Time Series Tables tab.
On the Time Series Tables tab, click the time series table name.
In the Analytical Store section of the Basic Information tab, click Create Analytical Store.
In the Create Analytical Store dialog box, configure the parameters. The following table describes the parameters.
Parameter
Description
Name
The name of the analytical store. The naming conventions for analytical stores are the same as those for time series tables.
TTL
The validity period of data in the analytical store, in seconds. The value must be -1 (data never expires) or an int32 positive integer greater than or equal to 2592000 (30 days).
If the system detects that the difference between the current time and the time when data columns are passed exceeds the specified TTL value, the system automatically deletes the expired data.
NoteIn an analytical store, the system determines the data generation time based on the time when data columns are passed, not the time when the data is written to the table.
The TTL of an analytical store is independent of the TTL of the time series table for which the analytical store is created.
Synchronization Method
The method used by the analytical store to synchronize data from the time series table. Valid values:
Full Synchronization: synchronizes both historical and incremental data from the time series table.
Incremental Synchronization: synchronizes only the incremental data changes in the time series table after the analytical store is created.
ImportantThe synchronization method cannot be modified after it is specified. Proceed with caution.
Click OK.
Query and analyze data by executing SQL statements
Go to the Manage Time Series Table page.
Log on to the Tablestore console.
In the top navigation bar, select a resource group and a region.
On the Overview page, click the instance name or click Manage Instance in the Actions column of the instance.
On the Instance Management page, click the Instance Details tab, and then click the Time Series Tables tab.
On the Time Series Tables tab, click the name of the time series table.
Create a mapping table for the time series table and enable the analytical store feature.
On the Manage Time Series Table page, click the Query by Executing SQL Statement tab, and then click the
icon.In the Create Mapping Table dialog box, configure the parameters. The following table describes the parameters.
Parameter
Description
Table Type
The type of the table. Select Time Series Table.
Table Name
The name of the time series table.
Mapping Table Name
The identifier of the mapping table. You can specify a custom identifier for the mapping table.
NoteThe system automatically adds the
time series table name::prefix to the mapping table name if you select Time Series Table for Table Type.Enable Analytical Store
Specifies whether to enable the analytical store feature. Turn on the Enable Analytical Store switch to use the analytical store engine.
Click Generate SQL Statement.
Click Execute SQL Statement(F8).
The mapping table for the time series table will be displayed in the left area of the page.
Use the SQL query feature to query data.
On the Query by Executing SQL Statement tab, enter a SELECT statement to query the required data.
For information about SQL query examples and SQL functions, see SQL query examples and SQL functions.
Click Execute SQL Statement(F8).
The data that meets the conditions will be displayed in the Execution Results section. Query results can be displayed in list, line chart, and histogram formats.
Query information of an analytical store
On the Instance Management page, click the Instance Details tab, and then click the Time Series Tables tab.
On the Time Series Tables tab, click the name of the time series table.
In the Analytical Store section of the Basic Information tab, you can view the name, TTL, synchronization method, synchronization status, storage usage, and synchronization time of the analytical store.
Update the TTL of an analytical store
On the Instance Management page, click the Instance Details tab, and then click the Time Series Tables tab.
On the Time Series Tables tab, click the name of the time series table.
In the Analytical Store section of the Basic Information tab, click Edit in the Actions column of the analytical store.
In the Update Analytical Store dialog box, modify the TTL of the analytical store.
Click Yes.
Delete an analytical store
On the Instance Management page, click the Instance Details tab, and then click the Time Series Tables tab.
On the Time Series Tables tab, click the name of the time series table.
In the Analytical Store section of the Basic Information tab, click Delete in the Actions column of the analytical store.
In the dialog box that appears, configure the Delete SQL Mapping Table for Analytical Store switch, and click OK.
If you do not create a mapping table for the time series table for which the Enable Analytical Store switch is turned on, retain the default configuration for the Delete SQL Mapping Table for Analytical Store switch.
If you create a mapping table for the time series table for which the Enable Analytical Store switch is turned on, make sure to turn on the Delete SQL Mapping Table for Analytical Store switch. When you delete the analytical store, the system automatically deletes the mapping table created for the time series table.
Development integration
Feature | Call method |
Create an analytical store | |
Query and analyze data by executing SQL statements | |
Update the TTL of an analytical store | |
Query information of an analytical store | |
Delete an analytical store |
Billing
The billable items for analytical stores include storage usage, metered write throughput, and metered read throughput. For more information, see Billable items of the TimeSeries model.