Build Asset Model from TDengine TSDB

For users already using TDengine TSDB, IDMP supports building asset models from TDengine TSDB. It provides three methods, which are introduced below.

Easy Import

When TDengine TSDB was originally designed, it considered the difference between static tags and dynamic time-series data, and static tags support hierarchical structures with levels separated by ".". Moreover, TDengine designed super tables to create a template for the same type of devices - a super table can have multiple instance sub-tables. This design perfectly corresponds to IDMP's hierarchical modeling and element templates, so if you already have well-designed asset models in TDengine TSDB, data import will be extremely simple.

A super table in TSDB corresponds to an element template in IDMP, a sub-table of a super table in TSDB corresponds to an element instance of an element template in IDMP, the sub-table name becomes the element name, the sub-table's tag values are mapped to the element's static attributes (data reference as TDengine Tag), the sub-table's time-series data is mapped to the element's dynamic data (data reference as TDengine Metric), and the sub-table's hierarchical structure tag (such as location tag) directly serves as the element's hierarchical path. With this correspondence, importing metadata from TSDB to IDMP is very easy.

The import steps are as follows:

1. In the admin console, create a connection to TDengine TSDB.
2. On the connection list page, or in the left tree structure list, click the three-dot menu and select Easy Import.
3. The dropdown at the top of the page will display the databases in this connection. After selecting a database, the dropdown on the right will display the super tables owned by that database.
4. Each super table needs to be configured. If you don't plan to import a certain super table, you can choose to ignore it in the upper right corner. If you want to import, continue:
   • The system lists the tag list of this super table. Select one tag as the hierarchical path. If multiple tags can serve as hierarchical paths, you can also select multiple, so one element will appear in multiple hierarchical structures. For tags not selected as paths, you can choose to set them as static attributes of the element. You can also make no settings and discard certain tag values.
   • The system lists the time-series data of this super table. You can choose to import or not import. If imported, they will become dynamic attributes of the element.
   • The element attribute names default to the column names in TSDB, but you can rename them.
5. After configuring one super table, click next. After configuring all super tables, click the Finish icon in the action area, or the Finish button below, and the easy import task is officially created. You can also click Finish at any time to accept the system's default configuration and import immediately.
6. The system will display a popup showing the import progress. After completion, it will show statistics such as how many element templates and how many elements were created.

Auto Sync: After the import task is completed, IDMP will monitor changes to TDengine TSDB metadata. For configured super tables, if sub-tables are added or deleted from the super table, IDMP will automatically sync.

Rebuild: If new super tables are added in TSDB or you want to add previously ignored super tables, you can click Rebuild to reconfigure. The old settings will be loaded, but you can modify them.

Data Contextualization: After data import, if possible, you need to enrich the data and do more data contextualization work, such as adding physical units, categories, descriptions, limit values to each element, adding attributes, categories, descriptions to non-leaf nodes, giving business semantics to the data, making your data platform AI-Ready so that AI can better recommend panels, perform real-time analysis, and play its role.

Map STable to Element

If data has already been written to TSDB but without good modeling, such as no hierarchical structure tags, or using a single-column model even though a device has multiple collected values, then you cannot use the easy import method to build asset models. IDMP provides a flexible but complex configuration method that allows you to aggregate multiple super tables into one element template and build hierarchical structures through string builders, naming patterns, etc. It can perfectly solve the single-column model modeling problem, allowing OT domain data (such as PLC collected data) to be better structured in the IT domain with business semantics, making it easier to gain business insights.

In this scenario, you need to first create the element template and define the element's attributes. A super table only has some attributes of an element template, and a sub-table in TSDB only has some attributes of an element. Therefore, IDMP will automatically create virtual super tables and virtual tables internally, assembling multiple sub-tables into one virtual table and multiple super tables into one super virtual table, but this process is transparent to users. The entire creation process is: select database and super table, then select element template, configure element name and element path, then map the tags and time-series data from the super table to the element template's attributes.

The specific steps are as follows:

1. In the admin console, create a connection to TDengine TSDB.
2. On the connection list page, or in the left tree structure list, click the three-dot menu and select Map STable to Element.
3. The dropdown at the top of the page will display the databases in this connection. After selecting a database, the dropdown on the right will display the super tables owned by that database.
4. Select an element template
5. Configure the element name. Click + to see available replacement strings, such as sub-table tag values. You can assemble strings, insert fixed strings, prefixes, suffixes, etc. Click Preview to view the assembled element name and confirm if it's correct.
6. Configure the element path. Click + to see available replacement strings, such as sub-table tag values. You can assemble strings, insert fixed strings, prefixes, suffixes, etc. Click Preview to view the assembled path name and confirm if it's correct.
7. Map tag values. You can map super table tags to element attributes, or choose none to not map.
8. Map metric values. You can map super table time-series data to element attributes, or choose none to not map.
9. Set sub-table filter conditions, because not all sub-tables under a super table will be mapped to one element template. For example, for single-point models, some users create super tables using data types, and sub-tables under the same super table may come from different types of devices. In this case, filtering needs to be done through sub-table tag values.
10. Click Finish, and the system will create the asset model task.

The above steps only complete one super table, or even just the mapping configuration of some metrics of one super table. For complete asset model creation, multiple asset models need to be configured. In extreme cases, for single-column models, if each device type has 100 attributes and there are 10 different types of devices, then 100 * 10 asset models need to be created. This workload is very large, and you should consider using the Import from CSV feature.

Auto Sync: After the above asset models are created, if new sub-tables are added in TSDB, they will be automatically synced to IDMP without human intervention.

New Super Tables: After asset models are created, if new super tables are added in TSDB, new asset models need to be added, otherwise the metadata in the new super tables cannot be synced to IDMP, and the new data assets cannot be presented in IDMP.

Import from CSV

After using Map STable to Element import, you will quickly find that if there are too many super tables under the single-column model, task creation becomes very cumbersome. Although element templates can now be created from CSV files, attribute templates and super table mappings still require multiple task creations. IDMP provides CSV file-based data import, which can be considered as a batch operation of Easy Import and Map STable to Element. With CSV data import, you can create element templates in advance, or not create element templates.

The specific steps are as follows:

1. In the admin console, create a connection to TDengine TSDB.
2. On the connection list page, or in the left tree structure list, click the three-dot menu and select Data Import.
3. Click the export button on the page. A popup for exporting to CSV configuration will appear. You can choose to export all databases and super tables. If you don't choose to export all databases, you need to select the databases and super tables you want to export.
4. You can also select Export Sub-table Names. If this option is selected, all sub-tables under each selected super table will be exported. This method is used when sub-tables don't have element paths or when you need to specify specific element names or element paths.
5. After obtaining the exported CSV file, you should make configuration modifications based on that file, such as setting element name expressions and element path expressions. If the attribute template corresponding to a super table column doesn't exist, you can set various properties of the attribute template in the corresponding row, such as default unit of measurement.
6. After setting up the CSV file, upload it by clicking the import button. The import task starts immediately after uploading.

CSV Configuration File Instructions:

1. Comment lines start with #, and comment lines will not be processed. CSV row data only supports English comma separation.
2. The first line to be processed is the Header. Lines after the Header will be processed as data.
3. Although it's CSV, data will be processed in blocks. The first line of each data block needs to contain the database name and super table name settings. Similarly, if database name and super table name are set, it will be treated as a new data block configuration.
4. If no element template is set, elements (element template name will use the super table name) and attribute templates will be automatically created based on the attribute template configuration in the CSV rows.
5. If a sub-table name is set, only that sub-table will be processed. If sub-table filter rules are set, only sub-tables matching the filter rules will be processed. If neither sub-table name nor sub-table filter rules are set, all sub-tables will be processed.
6. Note: Element name expressions cannot contain English commas. The element name expression in the first row of each data block cannot be empty. It can be filled with a string combining tag names, for example: prefix_${tbname}_suffix, prefix_${device_name_tag}.
7. Element path expressions cannot be empty. They can be filled with any string, or combined with tags like location.${location_tag}. If the tag value contains English periods, it will be automatically layered. For example, Beijing.Chaoyang, then Beijing will be created as the parent element of Chaoyang.
8. Super table column names cannot be empty. Element template categories support setting comma-separated strings. If a category doesn't exist, it will be automatically created. The same applies to attribute template categories.
9. Attribute template names can be empty. If the attribute template is empty, the super table column will be automatically mapped to an attribute template with the same name. If the attribute template doesn't exist, it will be automatically created. If an attribute template name is set but the attribute template doesn't exist, it will also be automatically created.
10. Reference types currently only support TDengineMetric and TDengineTag. TDengineMetric represents metrics, and TDengineTag represents tags. Cannot be empty.
11. Attribute template description and subsequent column values can all be empty. If you want to fill in units of measurement, you need to ensure the unit of measurement already exists in IDMP.
12. Note that comment lines cannot be deleted. Deleting comment lines will cause CSV parsing to fail.

note

The CSV configuration file uses UTF-8 encoding format and does not currently support UTF-8 with BOM encoding. When using Chinese Windows systems and opening with Excel software, garbled characters may be displayed. It is recommended to use WPS or Notepad++ or other tools for editing. If you can only use Excel software for editing, please note that the CSV file encoding saved in this way may be UTF-8 with BOM, which needs to be converted to UTF-8 encoding before it can be uploaded normally.

CSV Configuration File Examples:

1. Element path already defined in super table. Here we use the commonly used smart meter in TDengine TSDB as an example. In the smart meter super table (meters) structure, a location tag is defined, which stores paths separated by .. It can be considered that the element path already exists in the super table. Each sub-table of meters corresponds to an element. Since there is no tag for element name, the sub-table name is used as the element name. The CSV configuration example for this case is as follows:

   Database Name	Super Table Name	Element Template Name	Element Template Category	Sub-table Name	Sub-table Filter Rule	Element Name Expression	Element Description Expression	Element Path Expression	Super Table Column Name	Attribute Template Name	Reference Type	Attribute Template Category	Attribute Template Description	Attribute Template Hidden	Attribute Template Excluded	Attribute Template Default Unit	Attribute Template Display Unit	Attribute Template Default Value	Attribute Template Display Digits
   test	meters	Smart Meter				${tbname}		Location.${location}	current	Current	TDengineMetric
   									voltage	Voltage	TDengineMetric
   									phase	Phase	TDengineMetric
   									groupid	Group ID	TDengineTag

2. Element path needs to be combined from multiple tag values. This case is different from Example 1 where the location tag value is already a . separated string. Element paths need to be combined from multiple tags, forming combinations like ${tag1}.${tag2}.${tag3}. Using super table vm_diskio as an example, this table has location, rack_num, slot_num. When defining the element path, all three need to be combined. At the same time, vm_host is used as the element name, and the element template is defined as VM Disk IO. This template does not currently exist in the base library and will be automatically created based on the CSV configuration. The CSV configuration example is as follows:

   Database Name	Super Table Name	Element Template Name	Element Template Category	Sub-table Name	Sub-table Filter Rule	Element Name Expression	Element Description Expression	Element Path Expression	Super Table Column Name	Attribute Template Name	Reference Type	Attribute Template Category	Attribute Template Description	Attribute Template Hidden	Attribute Template Excluded	Attribute Template Default Unit	Attribute Template Display Unit	Attribute Template Default Value	Attribute Template Display Digits
   idmp_telegraf	vm_diskio	VM Disk IO				${vm_host}		${location}.${rack_num}.${slot_num}.vm	slot_num	Physical Machine ID	TDengineTag
   									write_bytes		TDengineMetric
   									reads		TDengineMetric
   									location		TDengineTag
   									vm_host		TDengineTag
   									rack_num		TDengineTag

3. Sub-tables have no path-related information at all. There are two ways to handle this case. One is that the table structure supports modification - add a new tag column to store the element path corresponding to the sub-table. After this modification, the element path can be specified through the above two methods. The other way is to explicitly configure the element path in CSV. Next, we demonstrate the configuration method for this case using a single-column storage structure. The super table name is opc_stw, and the sub-tables under this super table correspond to four devices: Inlet/Outlet Water Monitoring, Biochemical Pool North Group, Biochemical Pool South Group, and MBR Membrane Pool. Among them, Biochemical Pool North Group and Biochemical Pool South Group share the Biochemical Pool element template.

   Database Name	Super Table Name	Element Template Name	Element Template Category	Sub-table Name	Sub-table Filter Rule	Element Name Expression	Element Description Expression	Element Path Expression	Super Table Column Name	Attribute Template Name	Reference Type	Attribute Template Category	Attribute Template Description	Attribute Template Hidden	Attribute Template Excluded	Attribute Template Default Unit	Attribute Template Display Unit	Attribute Template Default Value	Attribute Template Display Digits
   db_stw	opc_stw	Inlet/Outlet Monitoring		t_Wastewater_Demo_Wastewater_Plant_Public_Inlet_Outlet_Monitoring_Outlet_COD		Inlet/Outlet Monitoring		Wastewater_Demo.Wastewater_Plant.Public	val	Outlet COD	TDengineMetric					mg/L	mg/L		2
   				t_Wastewater_Demo_Wastewater_Plant_Public_Inlet_Outlet_Monitoring_Outlet_Total_Nitrogen		Inlet/Outlet Monitoring		Wastewater_Demo.Wastewater_Plant.Public	val	Outlet Total Nitrogen	TDengineMetric					mg/L	mg/L		2
   				t_Wastewater_Demo_Wastewater_Plant_Public_Inlet_Outlet_Monitoring_Outlet_Ammonia		Inlet/Outlet Monitoring		Wastewater_Demo.Wastewater_Plant.Public	val	Outlet Ammonia	TDengineMetric					mg/L	mg/L		2
   db_stw	opc_stw	Biochemical Pool		t_Wastewater_Demo_Wastewater_Plant_Biochemical_Zone_A2O_Pool_North_Group_Aerobic_MLSS		Biochemical Pool North		Wastewater_Demo.Wastewater_Plant.Biochemical_Zone.A2O_Pool	val	Aerobic Pool MLSS	TDengineMetric					mg/L	mg/L		2
   				t_Wastewater_Demo_Wastewater_Plant_Biochemical_Zone_A2O_Pool_North_Group_Aerobic_DO		Biochemical Pool North		Wastewater_Demo.Wastewater_Plant.Biochemical_Zone.A2O_Pool	val	Aerobic Pool Outlet DO	TDengineMetric					mg/L	mg/L		2
   				t_Wastewater_Demo_Wastewater_Plant_Biochemical_Zone_A2O_Pool_North_Group_Aerobic_Flow		Biochemical Pool North		Wastewater_Demo.Wastewater_Plant.Biochemical_Zone.A2O_Pool	val	Aerobic Pool Channel Outlet Flow	TDengineMetric					m³/h	m³/h		2
   db_stw	opc_stw	Biochemical Pool		t_Wastewater_Demo_Wastewater_Plant_Biochemical_Zone_A2O_Pool_South_Group_Aerobic_MLSS		Biochemical Pool South		Wastewater_Demo.Wastewater_Plant.Biochemical_Zone.A2O_Pool	val	Aerobic Pool MLSS	TDengineMetric					mg/L	mg/L		2
   				t_Wastewater_Demo_Wastewater_Plant_Biochemical_Zone_A2O_Pool_South_Group_Aerobic_DO		Biochemical Pool South		Wastewater_Demo.Wastewater_Plant.Biochemical_Zone.A2O_Pool	val	Aerobic Pool Outlet DO	TDengineMetric					mg/L	mg/L		2
   				t_Wastewater_Demo_Wastewater_Plant_Biochemical_Zone_A2O_Pool_South_Group_Aerobic_Flow		Biochemical Pool South		Wastewater_Demo.Wastewater_Plant.Biochemical_Zone.A2O_Pool	val	Aerobic Pool Channel Outlet Flow	TDengineMetric					m³/h	m³/h		2
   db_stw	opc_stw	MBR Membrane Pool		t_Wastewater_Demo_Wastewater_Plant_Biochemical_Zone_MBR_Pool_Equipment_MBR_Outlet_NH4		MBR Membrane Pool		Wastewater_Demo.Wastewater_Plant.Biochemical_Zone.MBR_Equipment	val	Membrane Pool Outlet_NH4	TDengineMetric					mg/L	mg/L		2
   				t_Wastewater_Demo_Wastewater_Plant_Biochemical_Zone_MBR_Pool_Equipment_MBR_Outlet_NO3		MBR Membrane Pool		Wastewater_Demo.Wastewater_Plant.Biochemical_Zone.MBR_Equipment	val	Membrane Pool Outlet_NO3	TDengineMetric					mg/L	mg/L		2

Auto Sync: After the above CSV data import task is created, if new sub-tables are added in TSDB, they will be automatically synced to IDMP without human intervention.

Monitor Tasks: After the CSV data import task is created, you can see the task and previously created import tasks and their status on the page (if failed, the error reason will be displayed). For created imports that don't have sub-table names set, auto-sync tasks will be automatically created. The sync task can be stopped and started by clicking the menu button on the right side of the task.

note

After the CSV data import task is created, if new super tables are added in TSDB, new asset models need to be added, otherwise the metadata in the new super tables cannot be imported to IDMP, and the new data assets cannot be presented in IDMP.