TDengine TSDB ETL and Data Transformation in Zero-Code Ingestion

Why built-in ETL matters

In industrial data platform projects, data ingestion is often the most time-consuming and error-prone phase. Industrial data sources present multiple challenges: multi-source heterogeneity (PLC, sensor, and SCADA system data formats all differ), inconsistent units (temperature in Celsius versus Fahrenheit, pressure in any number of units), conflicting naming conventions (field names vary wildly across device manufacturers), and time zone differences (globally deployed equipment lacks a uniform time reference).

The traditional approach handles these discrepancies layer by layer in application code or middleware. This adds system complexity and introduces data quality problems. TDengine moves ETL (Extract, Transform, Load) into the data ingestion pipeline itself, so data is standardized before it ever reaches the database.

Parsing: extracting structured information

Parsing is the first step in the ETL process. It converts raw data into structured fields. TDengine supports three parsing methods.

JSON parsing

TDengine handles both JSONObject and JSONArray formats. Simple JSON data is parsed automatically. For nested JSON, JSON Path expressions locate and extract the target numeric fields while ignoring irrelevant metadata.

Regex parsing

Named capture groups ((?P<name>pattern)) extract fields from unstructured strings. This approach works well for parsing log output from legacy equipment and data packets from custom protocols.

UDT (user-defined transformation)

When JSON and regex cannot handle the complexity, UDT steps in. UDT uses the Rhai scripting language, taking the raw data as JSON text input. It supports arbitrarily complex parsing logic, including multi-line association parsing and conditional branching.

Extraction and split rules

TDengine provides three extraction rules:

• Split rule: Splits a single field into multiple fields by a delimiter and count. A common scenario: a device ID that encodes a “plant, workshop, equipment” hierarchy can be split into three independent fields in one step.
• Regex extraction rule: Similar to regex in the parsing stage, but operates on field values that have already been parsed.
• Convert rule: Uses a JSON lookup table to transform values. For example, status codes become readable text: {"0": "Stopped", "1": "Running", "2": "Fault"}. This achieves semantic enrichment at the database level.

Filtering: precise data control

Data filtering screens data through conditional expressions before writing:

• Comparison operators: >, >=, <=, <, ==, !=
• Time functions: between_time_range(ts, t1, t2) filters by time range. Useful for backfill scenarios.
• String functions: is_empty, contains, starts_with, ends_with, len
• Logical operators: &&, ||, !
• Type conversion: parse_int and parse_float convert strings to numeric types.

Mapping rules: constructing target fields

Seven mapping types are available:

1. Mapping (direct mapping): A source field maps directly to a target field with the same name and meaning.
2. Value (constant): Assigns a fixed value. For example, adding a source identifier to every data stream helps with provenance tracking.
3. Generator (timestamp generation): The now generator automatically appends a timestamp, solving the problem of devices that do not supply their own.
4. Join: Concatenates multiple fields into a single string.
5. Format: String processing functions: pad, trim, sub_string, replace.
6. Sum: Adds multiple numeric fields together.
7. Expr (numeric expression): Full arithmetic and math functions (+, -, *, /, sin, cos, sqrt). Example: Fahrenheit to Celsius conversion via expr: (col_fahrenheit - 32) * 5 / 9.

Practical recommendations

Four principles to follow:

1. Prefer automatic parsing for standard JSON formats.
2. Set reasonable filter conditions to reduce storage pressure.
3. Complete all unit conversions during the ETL stage so data stays uniformly standardized.
4. Use mapping rules to enrich data dimensions at the ingestion point.

Summary

TDengine TSDB’s built-in ETL covers the full data processing chain: JSON parsing, regex extraction, conditional filtering, and flexible mapping. It addresses the core data standardization requirements of industrial data management platforms. Moving these transformations into the ingestion pipeline cuts development and maintenance costs while preserving data quality and consistency.