Specify custom time series identifiers and custom data fields as the primary key columns

You may want to specify custom time series identifiers in scenarios that require precise time positioning, range queries, trend analysis, and event association. The following items describe the scenarios:

• Troubleshooting and root cause analysis

  In IT O&M and industrial equipment monitoring, custom time series identifiers help precisely mark when failures occur. Combined with other time series data (such as device status and log information), custom time series identifiers help quickly locate problem causes for root cause analysis.

• User behavior analysis

  In Internet applications, custom time series identifiers enable the analysis of user active periods, session durations, and behavior paths by recording user login time, page view durations, and operation sequences. This data-driven approach facilitates user experience optimization and intelligent content recommendations.

• Financial transaction auditing

  In the financial sector, each transaction requires a precise timestamp. Custom time series identifiers are used not only to ensure the sequence and integrity of transactions but also for anti-fraud analysis, such as identifying abnormal transaction patterns and tracking fund flows.

By specifying custom time series identifiers, you can group, filter, and analyze time series data in a flexible manner, enabling fine-grained management and deeper insights across diverse business scenarios.

If you want to store multiple rows of time series data with the same time series identifiers and timestamp, you can specify custom data fields as the primary key columns of your time series table. The following items describe the scenarios:

• Event association analysis

  In some complex analysis scenarios, you may want to associate time series data based on data fields other than time series identifiers. You can specify custom data fields as the primary key columns to quickly locate data of a specific device at specific timestamps. This approach is suitable for troubleshooting and behavior analysis.

• Multi-dimensional indexing

  For advanced applications of time series databases, if you want to perform multi-dimensional queries and filtering on data, you can specify custom data fields as the primary key columns to improve query efficiency. This approach enables more precise location of relevant time series data especially when you process large amounts of data.

• Multi-tenant time series data management

  When you provide time series data storage services for multiple customers, you may want to specify the tenant ID as one of the primary key columns in the time series table to form a composite primary key with the timestamp. This approach ensures data isolation and efficient access for different tenants.

1. When you create a time series table, specify custom time series identifiers. For more information, see Operations on time series tables.

2. Write data to the time series table based on the custom time series identifiers.

   When you write data to the time series table, the fields of the custom time series identifiers on the client are passed to the Tablestore server as key-value pairs. The Tablestore server first extracts the metric name and data source from the key-value pairs, and then uses the remaining key-value pairs as tags of the time series. For more information, see Write time series data.

   Important

   If the fields of the custom time series identifiers do not include the _tags field when you create the time series table but there are still key-value pairs remaining after excluding the metric name, data source, and custom fields from the key-value pairs when you write time series data, the data fails to be written.

3. Read data from the time series table.

   1. Create an SQL mapping table for the time series table. For more information, see Create mapping tables in the multi-value model for time series tables.

      1. On the Manage Time Series Table page, click the Query by Executing SQL Statement tab.

      2. On the left side of the Query by Executing SQL Statement tab, click the + icon next to the name of the instance for the TimeSeries model.

      3. In the Create Mapping Table dialog box, configure the parameters. The following table describes the parameters.

         Parameter	Description
         Table Name	The name of the time series table.
         Mapping Table Name	The name of the mapping table. Note When you create an SQL mapping table for a time series table, the system automatically adds the prefix time series table name:: to the mapping table name.
         Enable Analytical Store	The analytical store feature is used in scenarios in which you want to store time series data for a long period of time and analyze time series data. You can use the analytical store feature to store time series data at lower costs and query and analyze time series data in an efficient manner. For more information, see Analytical store for time series. By default, Enable Analytical Store is turned off. Configure the parameter based on your business requirements. If you do not want to use an analytical store to store and analyze time series data, retain the default setting for the Enable Analytical Store parameter. If you want to store time series data at lower costs or query and analyze time series data in an efficient manner, make sure that an analytical store is created and Enable Analytical Store is turned on. Important The analytical store feature is supported in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), and China (Zhangjiakou). To use the analytical store feature, create an instance for the TimeSeries model in one of the preceding regions. For more information, see Create an instance for the TimeSeries model. If you create a mapping table for which Enable Analytical Store is turned on, when you delete the analytical store, you must delete the mapping table at the same time. Otherwise, you fail to delete the analytical store.

      4. Click Generate SQL Statement. The system automatically generates the SQL statement to create the mapping table.

         After you specify custom time series identifiers, when you create an SQL mapping table, specify the custom time series identifiers and custom data fields that are used as the primary key columns of the time series table as the primary key columns of the SQL mapping table.

      5. Click Execute SQL Statement(F8).

   2. Execute the SELECT statement to query data. For more information, see Query data.

      Enter the SQL statement in the SQL editor in the console and click Execute SQL Statement(F8).

      The following sample SQL statement provides an example on how to query information of the time series in which the cpu_usage value exceeds 90.0 in a time series table by using the mapping table named cpu::ts1:

      SELECT `host`, `processor`, `_tags` , `_time` FROM `cpu::ts1` WHERE cpu_usage > 90.0;

In this example, Tablestore SDK for Java is used.

1. When you create a time series table, specify custom time series identifiers.

   The following sample code provides an example on how to create a time series table without an analytical store and specify custom time series identifiers:

   private static void createTimeseriesTable(TimeseriesClient client) {
       TimeseriesTableMeta timeseriesTableMeta = new TimeseriesTableMeta("");
       timeseriesTableMeta.setTimeseriesTableOptions(new TimeseriesTableOptions(-1));
       // Specify custom time series identifiers. The host field stores the metric name, the processor field stores the data source, and the _tags field stores the tags.
       timeseriesTableMeta.addTimeseriesKey("host");
       timeseriesTableMeta.addTimeseriesKey("processor");
       timeseriesTableMeta.addTimeseriesKey("_tags");
       CreateTimeseriesTableRequest request = new CreateTimeseriesTableRequest(timeseriesTableMeta);
       request.setEnableAnalyticalStore(false);
       client.createTimeseriesTable(request);
   }

2. Write data to the time series table based on the custom time series identifiers.

   When you write data to the time series table, the fields of the custom time series identifiers on the client are passed to the Tablestore server as key-value pairs. The Tablestore server first extracts the metric name and data source from the key-value pairs, and then uses the remaining key-value pairs as tags of the time series.

   Important

   If the fields of the custom time series identifiers do not include the _tags field when you create the time series table but there are still key-value pairs remaining after excluding the metric name, data source, and custom fields from the key-value pairs when you write time series data, the data fails to be written.

   The following sample code provides an example on how to write three rows of time series data to a time series table based on the custom time series identifiers.

   private static void putTimeseriesData(TimeseriesClient client) {
       long timeInUs = System.currentTimeMillis() * 1000;
       List rows = new ArrayList();
       for (int i = 0; i < 3; i++) {
           Map tags = new HashMap<>();
           // Add fields of the custom time series identifiers.
           tags.put("host", "host_" + i);
           tags.put("processor", String.valueOf(i));
           // Key-value pairs that do not correspond to custom time series identifiers are used as tags of the time series.
           tags.put("region", "hangzhou");
            TimeseriesKey timeseriesKey = new TimeseriesKey("", "", tags);
           TimeseriesRow row = new TimeseriesRow(timeseriesKey, timeInUs);
           row.addField("cpu_usage", ColumnValue.fromDouble(10.0));
           row.addField("cpu_sys", ColumnValue.fromDouble(5.0));
           rows.add(row);
       }
       String tableName = "";
       PutTimeseriesDataRequest putTimeseriesDataRequest = new PutTimeseriesDataRequest(tableName);
       putTimeseriesDataRequest.setRows(rows);
       client.putTimeseriesData(putTimeseriesDataRequest);
   }

   The following table shows the time series data in the time series table after the data is successfully written.

   host	processor	_tags	_time	cpu_usage	cpu_sys
   host_0	0	["region=hangzhou"]	1712476524000000	10.0	5.0
   host_1	1	["region=hangzhou"]	1712476524000000	10.0	5.0
   host_2	2	["region=hangzhou"]	1712476524000000	10.0	5.0

3. Execute SQL statements to read data from the time series table.

   1. Create an SQL mapping table for the time series table. For more information, see Create mapping tables in the multi-value model for time series tables.

      After you specify custom time series identifiers, when you create an SQL mapping table, specify the fields of the custom time series identifiers and the timestamp field as the primary key columns of the SQL mapping table.

      Sample configurations:

      CREATE TABLE `cpu::ts1` (
          `host` VARCHAR(1024),
          `processor` VARCHAR(1024),
          `_tags` VARCHAR(1024),
          `_time` BIGINT(20),
          `cpu_usage` DOUBLE,
          `cpu_sys` DOUBLE,
          PRIMARY KEY(`host`,`processor`,`_tags`,`_time`)
      );

   2. Execute the SELECT statement to query data. For more information, see Query data.

      The following sample SQL statement provides an example on how to query information of the time series in which the cpu_usage value exceeds 90.0 in a time series table by using the mapping table named cpu::ts1:

      SELECT `host`, `processor`, `_tags` , `_time` FROM `cpu::ts1` WHERE cpu_usage > 90.0;

1. When you create a time series table, specify custom data fields as the primary key columns of the time series table. For more information, see Operations on time series tables.

2. Write time series data to the time series table.

   When you write data to a time series table, the data fields that are specified as the primary key columns cannot be left empty. You can store multiple rows of time series data with the same time series identifiers and timestamp by specifying custom data fields as the primary key columns. For more information, see Write time series data.

3. Read data from the time series table.

   1. Create an SQL mapping table for the time series table. For more information, see Create mapping tables in the multi-value model for time series tables.

      1. On the Manage Time Series Table page, click the Query by Executing SQL Statement tab.

      2. On the left side of the Query by Executing SQL Statement tab, click the + icon next to the name of the instance for the TimeSeries model.

      3. In the Create Mapping Table dialog box, configure the parameters. The following table describes the parameters.

         Parameter	Description
         Table Name	The name of the time series table.
         Mapping Table Name	The name of the mapping table. Note When you create an SQL mapping table for a time series table, the system automatically adds the prefix time series table name:: to the mapping table name.
         Enable Analytical Store	The analytical store feature is used in scenarios in which you want to store time series data for a long period of time and analyze time series data. You can use the analytical store feature to store time series data at lower costs and query and analyze time series data in an efficient manner. For more information, see Analytical store for time series. By default, Enable Analytical Store is turned off. Configure the parameter based on your business requirements. If you do not want to use an analytical store to store and analyze time series data, retain the default setting for the Enable Analytical Store parameter. If you want to store time series data at lower costs or query and analyze time series data in an efficient manner, make sure that an analytical store is created and Enable Analytical Store is turned on. Important The analytical store feature is supported in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), and China (Zhangjiakou). To use the analytical store feature, create an instance for the TimeSeries model in one of the preceding regions. For more information, see Create an instance for the TimeSeries model. If you create a mapping table for which Enable Analytical Store is turned on, when you delete the analytical store, you must delete the mapping table at the same time. Otherwise, you fail to delete the analytical store.

      4. Click Generate SQL Statement. The system automatically generates the SQL statement to create the mapping table.

         After you specify custom data fields as the primary key columns of a time series table, when you create an SQL mapping table, specify the time series identifiers and custom data fields that are used as the primary key columns of the time series table as the primary key columns of the SQL mapping table.

      5. Click Execute SQL Statement(F8).

   2. Execute the SELECT statement to query data. For more information, see Query data.

      Enter the SQL statement in the SQL editor in the console and click Execute SQL Statement(F8).

      The following sample SQL statement provides an example on how to query information of the time series in which the cpu_usage value exceeds 90.0 in a time series table by using the mapping table named cpu::ts1:

      SELECT `host`, `processor`, `_tags` , `_time` FROM `cpu::ts1` WHERE cpu_usage > 90.0;

In this example, Tablestore SDK for Java is used.

1. When you create a time series table, specify custom data fields as the primary key columns of the time series table.

   The following sample code provides an example on how to create a time series table without an analytical store and specify custom data fields as the primary key columns of the time series table:

   private static void createTimeseriesTable(TimeseriesClient client) {
       TimeseriesTableMeta timeseriesTableMeta = new TimeseriesTableMeta("");
       timeseriesTableMeta.setTimeseriesTableOptions(new TimeseriesTableOptions(-1));
       // Specify custom data fields as the primary key columns.
       timeseriesTableMeta.addFieldPrimaryKey("processor", PrimaryKeyType.INTEGER);
       CreateTimeseriesTableRequest request = new CreateTimeseriesTableRequest(timeseriesTableMeta);
       request.setEnableAnalyticalStore(false);
       client.createTimeseriesTable(request);
   }

2. Write time series data to the time series table.

   When you write data to a time series table, the data fields that are specified as the primary key columns cannot be left empty. You can store multiple rows of time series data with the same time series identifiers and timestamp by specifying custom data fields as the primary key columns.

   The following sample code provides an example on how to write three rows of time series data with the same time series identifiers and timestamp to a time series table:

   private static void putTimeseriesData(TimeseriesClient client) {
       long timeInUs = System.currentTimeMillis() * 1000;
       List rows = new ArrayList();
       for (int i = 0; i < 3; i++) {
           Map tags = new HashMap<>();
           tags.put("region", "hangzhou");
            TimeseriesKey timeseriesKey = new TimeseriesKey("cpu", "host_" + i, tags);
           TimeseriesRow row = new TimeseriesRow(timeseriesKey, timeInUs);
           // The data fields that are specified as the primary key columns cannot be left empty.
           row.addField("processor", ColumnValue.fromLong(i));
           row.addField("cpu_usage", ColumnValue.fromDouble(10.0));
           row.addField("cpu_sys", ColumnValue.fromDouble(5.0));
           rows.add(row);
       }
       String tableName = "<TIMESERIES_TABLE_NAME>";
       PutTimeseriesDataRequest putTimeseriesDataRequest = new PutTimeseriesDataRequest(tableName);
       putTimeseriesDataRequest.setRows(rows);
       client.putTimeseriesData(putTimeseriesDataRequest);
   }

   The following table shows the time series data in the time series table after the data is successfully written.

   _m_name	_data_source	_tags	_time	processor	cpu_usage	cpu_sys
   cpu	host_0	["region=hangzhou"]	1712476524000000	0	10.0	5.0
   cpu	host_1	["region=hangzhou"]	1712476524000000	1	10.0	5.0
   cpu	host_2	["region=hangzhou"]	1712476524000000	2	10.0	5.0

3. Execute SQL statements to read data from the time series table.

   1. Create an SQL mapping table for the time series table. For more information, see Create mapping tables in the multi-value model for time series tables.

      After you specify custom data fields as the primary key columns of a time series table, when you create an SQL mapping table, specify the time series identifiers and custom data fields that are used as the primary key columns of the time series table as the primary key columns of the SQL mapping table.

      Sample configurations:

      CREATE TABLE `cpu::ts1` (
          `_m_name` VARCHAR(1024),
          `_data_source`  VARCHAR(1024),
          `_tags` VARCHAR(1024),
          `_time` BIGINT(20),
          `processor` BIGINT,
          `cpu_usage` DOUBLE,
          `cpu_sys` DOUBLE,
          PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`,`processor`)
      );

   2. Execute the SELECT statement to query data. For more information, see Query data.

      The following sample SQL statement provides an example on how to query information of the time series in which the cpu_usage value exceeds 90.0 in a time series table by using the mapping table named cpu::ts1:

      SELECT `_m_name`, `_data_source`, `_tags` , `_time`, `processor` FROM `cpu::ts1` WHERE cpu_usage > 90.0;