Schemaless Writing

In IoT applications, data is collected for many purposes such as intelligent control, business analysis, device monitoring and so on. Due to changes in business or functional requirements or changes in device hardware, the application logic and even the data collected may change. Schemaless writing automatically creates storage structures for your data as it is being written to TDengine, so that you do not need to create supertables in advance. When necessary, schemaless writing will automatically add the required columns to ensure that the data written by the user is stored correctly.

The schemaless writing method creates super tables and their corresponding subtables. These are completely indistinguishable from the super tables and subtables created directly via SQL. You can write data directly to them via SQL statements. Note that the names of tables created by schemaless writing are based on fixed mapping rules for tag values, so they are not explicitly ideographic and they lack readability.

Tips: The schemaless write will automatically create a table. You do not need to create a table manually, or an unknown error may occur.

Schemaless Writing Line Protocol

TDengine’s schemaless writing line protocol supports InfluxDB’s Line Protocol, OpenTSDB’s telnet line protocol, and OpenTSDB’s JSON format protocol. However, when using these three protocols, you need to specify in the API the standard of the parsing protocol to be used for the input content.

For the standard writing protocols of InfluxDB and OpenTSDB, please refer to the documentation of each protocol. The following is a description of TDengine’s extended protocol, based on InfluxDB’s line protocol first. They allow users to control the (super table) schema more granularly.

With the following formatting conventions, schemaless writing uses a single string to express a data row (multiple rows can be passed into the writing API at once to enable bulk writing).

measurement,tag_set field_set timestamp

where:

• measurement will be used as the data table name. It will be separated from tag_set by a comma.
• tag_set will be used as tags, with format like <tag_key>=<tag_value>,<tag_key>=<tag_value> Enter a space between tag_set and field_set.
• field_setwill be used as data columns, with format like <field_key>=<field_value>,<field_key>=<field_value> Enter a space between field_set and timestamp.
• timestamp is the primary key timestamp corresponding to this row of data

All data in tag_set is automatically converted to the NCHAR data type and does not require double quotes (“).

In the schemaless writing data line protocol, each data item in the field_set needs to be described with its data type. Let’s explain in detail:

• If there are English double quotes on both sides, it indicates the BINARY(32) type. For example, "abc".
• If there are double quotes on both sides and an L prefix, it means NCHAR(32) type. For example, L"error message".
• Spaces, equal signs (=), commas (,), and double quotes (“) need to be escaped with a backslash (\) in front. (All refer to the ASCII character)
• Numeric types will be distinguished from data types by the suffix.

• t, T, true, True, TRUE, f, F, false, and False will be handled directly as BOOL types.

For example, the following data rows write c1 column as 3 (BIGINT), c2 column as false (BOOL), c3 column as “passit” (BINARY), c4 column as 4 (DOUBLE), and the primary key timestamp as 1626006833639000000 to child table with the t1 label as “3” (NCHAR), the t2 label as “4” (NCHAR), and the t3 label as “t3” (NCHAR) and the super table named st.

st,t1=3,t2=4,t3=t3 c1=3i64,c3="passit",c2=false,c4=4f64 1626006833639000000

Note that if the wrong case is used when describing the data type suffix, or if the wrong data type is specified for the data, it may cause an error message and cause the data to fail to be written.

Main processing logic for schemaless writing

Schemaless writes process row data according to the following principles.

1. You can use the following rules to generate the subtable names: first, combine the measurement name and the key and value of the label into the next string:

"measurement,tag_key1=tag_value1,tag_key2=tag_value2"

Note that tagkey1, tag_key2 are not the original order of the tags entered by the user but the result of using the tag names in ascending order of the strings. Therefore, tag_key1 is not the first tag entered in the line protocol. The string’s MD5 hash value “md5_val” is calculated after the ranking is completed. The calculation result is then combined with the string to generate the table name: “t_md5_val”. “t“ is a fixed prefix that every table generated by this mapping relationship has. You can configure smlChildTableName in taos.cfg to specify table names, for example, smlChildTableName=tname. You can insert st,tname=cpul,t1=4 c1=3 1626006833639000000 and the cpu1 table will be automatically created. Note that if multiple rows have the same tname but different tag_set values, the tag_set of the first row is used to create the table and the others are ignored.

1. If the super table obtained by parsing the line protocol does not exist, this super table is created.
2. If the subtable obtained by the parse line protocol does not exist, Schemaless creates the sub-table according to the subtable name determined in steps 1 or 2.
3. If the specified tag or regular column in the data row does not exist, the corresponding tag or regular column is added to the super table (only incremental).
4. If there are some tag columns or regular columns in the super table that are not specified to take values in a data row, then the values of these columns are set to NULL.
5. For BINARY or NCHAR columns, if the length of the value provided in a data row exceeds the column type limit, the maximum length of characters allowed to be stored in the column is automatically increased (only incremented and not decremented) to ensure complete preservation of the data.
6. Errors encountered throughout the processing will interrupt the writing process and return an error code.
7. It is assumed that the order of field_set in a supertable is consistent, meaning that the first record contains all fields and subsequent records store fields in the same order. If the order is not consistent, set smlDataFormat in taos.cfg to false. Otherwise, data will be written out of order and a database error will occur.(smlDataFormat in taos.cfg default to false after version of 3.0.1.3)

tip

All processing logic of schemaless will still follow TDengine’s underlying restrictions on data structures, such as the total length of each row of data cannot exceed 16KB. See TDengine SQL Boundary Limits for specific constraints in this area.

Time resolution recognition

Three specified modes are supported in the schemaless writing process, as follows:

In InfluxDB line protocol mode, you must specify the precision of the input timestamp. Valid precisions are described in the following table.

In OpenTSDB file and JSON protocol modes, the precision of the timestamp is determined from its length in the standard OpenTSDB manner. User input is ignored.

Data Model Mapping

This section describes how data in line protocol is mapped to a schema. The data measurement in each line is mapped to a supertable name. The tag name in tag_set is the tag name in the schema, and the name in field_set is the column name in the schema. The following example shows how data is mapped:

st,t1=3,t2=4,t3=t3 c1=3i64,c3="passit",c2=false,c4=4f64 1626006833639000000

This row is mapped to a supertable: st contains three NCHAR tags: t1, t2, and t3. Five columns are created: ts (timestamp), c1 (bigint), c3 (binary), c2 (bool), and c4 (bigint). The following SQL statement is generated:

create stable st (_ts timestamp, c1 bigint, c2 bool, c3 binary(6), c4 bigint) tags(t1 nchar(1), t2 nchar(1), t3 nchar(2))

Processing Schema Changes

This section describes the impact on the schema caused by different data being written.

If you use line protocol to write to a specific tag field and then later change the field type, a schema error will ocur. This triggers an error on the write API. This is shown as follows:

st,t1=3,t2=4,t3=t3 c1=3i64,c3="passit",c2=false,c4=4    1626006833639000000
st,t1=3,t2=4,t3=t3 c1=3i64,c3="passit",c2=false,c4=4i   1626006833640000000

The first row defines c4 as a double. However, in the second row, the suffix indicates that the value of c4 is a bigint. This causes schemaless writing to throw an error.

An error also occurs if data input into a binary column exceeds the defined length of the column.

st,t1=3,t2=4,t3=t3 c1=3i64,c5="pass"     1626006833639000000
st,t1=3,t2=4,t3=t3 c1=3i64,c5="passit"   1626006833640000000

The first row defines c5 as a binary(4). but the second row writes 6 bytes to it. This means that the length of the binary column must be expanded to contain the data.

st,t1=3,t2=4,t3=t3 c1=3i64               1626006833639000000
st,t1=3,t2=4,t3=t3 c1=3i64,c6="passit"   1626006833640000000

The preceding data includes a new entry, c6, with type binary(6). When this occurs, a new column c6 with type binary(6) is added automatically.

Write Integrity

TDengine guarantees the idempotency of data writes. This means that you can repeatedly call the API to perform write operations with bad data. However, TDengine does not guarantee the atomicity of multi-row writes. In a multi-row write, some data may be written successfully and other data unsuccessfully.

: Error Codes

The TSDB_CODE_TSC_LINE_SYNTAX_ERROR indicates an error in the schemaless writing component. This error occurs when writing text. For other errors, schemaless writing uses the standard TDengine error codes found in taos_errstr.