Create data models - Lindorm - Alibaba Cloud Documentation Center

This topic describes how to use LindormTSDB to create data models.

Create a Lindorm time series database

After you create a Lindorm instance, a database named default is automatically created for the instance. By default, data written to the instance is stored in this database. The default database cannot be deleted.

LindormTSDB allows you to create multiple databases. Data stored in different databases is physically isolated. You can also configure different data time-to-lives (TTLs), partition intervals, and hot and cold data boundaries for different databases. For more information about how to create a database, see CREATE DATABASE.

Examples

Create a database named DB1 and specify that data in DB1 does not expire.
```
CREATE DATABASE DB1;
```
Create a database named DB2 and set the data TTL of DB2 to 60 days.
```
CREATE DATABASE DB2 WITH (ttl=60);
```
Create a database named DB3 and set the data TTL of DB3 to 60 days and the hot and cold data boundary to 30 days.
```
CREATE DATABASE DB3 WITH (cold_boundary=30, ttl=60);
```
Note
- The data TTL and hot and cold data boundary are measured in days.
- The value of the hot and cold data boundary must be smaller than that of the data TTL.

Partition a database based on time

You can partition a LindormTSDB database by time. Data within different time ranges are stored in different partitions. Each partition contains time series data and the indexes for the time series.

If you create periodic time series and a large number of time series are generated, we recommend that you partition your database by time to avoid oversized indexes for the same partition. The default partition interval is 30 days. We recommend that you set the partition interval to 7 or 30 days. For more information about how to partition a database by time, see CREATE DATABASE.

When you query data in a database, only partition that stores the data within the specified time range is queried.

Examples

Create a database named DB4 and set the partition interval to 30 days.
```
CREATE DATABASE DB4 WITH (partition_interval=30);
```
For an existing database, you can change its partition interval by modifying the partition_interval parameter.
```
ALTER DATABASE DB4 WITH (partition_interval=60);
```
Important
After you change the partition interval of a database, the new partition interval does not immediately take effect. After the partition interval is changed, LindormTSDB specifies the last day when data is written to the current partition as the end time of the partition and creates a new partition to store data that are written to the database after the day. The new partition interval takes effect only for the partition created after the interval is changed. The partition interval of existing partitions remains unchanged.
For example, the partition interval of the database DB4 is changed from 30 days to 60 days. When the interval is changed, data within 50 days has been separately stored in two partitions named Partition1 and Partition2 in DB4. Partition1 stores data written to DB4 from day 1 to day 30. Partition2 stores data written to DB4 from day 31 to day 50. After the partition interval is changed, data written to DB4 from day 51 is written to the new partition whose partition interval is 60 days. The partition interval of Partition1 and Partition2 remains unchanged.

Specify a hot and cold data boundary

LindormTSDB allows you to separately store hot data and cold data in different storage media. You can specify a hot and cold data boundary in days for a database by configuring the cold_boundary parameter. Data that has been stored for a period longer than the boundary is transferred to cold storage that is more cost-effective.

To separately store hot data and cold data, you must enable cold storage for the database. For more information, see Enable cold storage.

Important

Data that is transferred to cold storage cannot be transferred back to hot storage.
Data is asynchronously transferred to cold storage in the background. Therefore, it takes a period of time for the size of cold storage to change after the boundary is configured or modified.

Examples

Create a database named DB5 and set the hot and cold data boundary to 30 days. Data that has been stored in DB5 for more than 30 days is transferred to cold storage.
```
CREATE DATABASE DB5 WITH (cold_boundary=30);
```
For an existing database, you can change its hot and cold data boundary by modifying the cold_boundary parameter.
```
ALTER DATABASE DB5 WITH (cold_boundary=30);
```

Create a time series table

Before you create a time series table, you must create data models for the table and determine the tag, timestamp, and field columns in the table. In general, LindormTSDB uses tag columns to match time series in queries and creates an inverted index for each tag column.

In addition, you can use the primary key of the table as the partition key. LindormTSDB partitions data by using the partition key and routes data to different storage nodes based on the hashes of data. If the condition specified in a query matches a partition key, LindormTSDB queries data only on the corresponding node instead of scanning data on all nodes. We recommend that you use columns that contain unique values as the primary key, such as device IDs in Internet of Things (IoT) scenarios.

For more information, see Data model and Design a time series table.

Examples

Create a sample table named sensor.

CREATE TABLE sensor (
    device_id VARCHAR TAG,
    region VARCHAR TAG,
    time   TIMESTAMP,
    temperature DOUBLE,
    humidity DOUBLE);

Create a table that stores sensor information and specify the device_id column as the primary key.

CREATE TABLE sensor (
    device_id VARCHAR TAG,
    region VARCHAR TAG,
    time   TIMESTAMP,
    temperature DOUBLE,
    humidity DOUBLE,
    PRIMARY KEY(device_id));

Automatic table creation

LindormTSDB provides three types of schema constraint policies: strong constraint, weak constraint, and no constraint. You can specify the schema constraint policy when you write data by using Lindorm native SDK for Java, the InfluxDB line protocol, or the multi-value data model compatible with TSDB APIs. In scenarios where the weak constraint policy is specified, LindormTSDB automatically creates a table or column when the table or column to which you want to write data does not exist, or verifies the data type of the column to which you want to write data when the column exists. For more information, see Constraint policies for schemas.

Single-value and multi-value data models

Single values written to a table by using the OpenTSDB protocol or TSDB APIs are stored by using the single-value model. LindormTSDB does not create tables for single-value data. LindormTSDB optimizes the underlying storage of multi-value data for efficient data reading. In addition, LindormTSDB automatically creates a table for multi-value data even if the weak constraint policy is specified. Therefore, we recommend that you use the multi-value data model to store data.