Tree-to-Table Mapping

1. Functional Overview

IoTDB introduces a tree-to-table function, which enables the creation of table views from existing tree-model data. This allows querying via table views, achieving collaborative processing of both tree and table models for the same dataset:

During the data writing phase, the tree-model syntax is used, supporting flexible data ingestion and expansion.
During the data analysis phase, the table-model syntax is adopted, allowing complex data analysis through standard SQL queries.

This feature supports from version V2.0.5.
Table views are read-only, so data cannot be written through them.

2. Feature Description

2.1 Creating a Table View

2.1.1 Syntax Definition

-- create (or replace) view on tree
CREATE
    [OR REPLACE]
    VIEW view_name ([viewColumnDefinition (',' viewColumnDefinition)*])
    [comment]
    [RESTRICT]
    [WITH properties]
    AS prefixPath

viewColumnDefinition
  : column_name [dataType] TAG [comment]                                    # tagColumn
  | column_name [dataType] TIME [comment]                                   # timeColumn
  | column_name [dataType] FIELD [FROM original_measurement] [comment]    # fieldColumn
  ;
  
comment
  : COMMENT string
  ;

Note: Columns only support tags, fields, or time; attributes are not supported.

2.1.2 Syntax Explanation

prefixPath

Corresponds to the path in the tree model. The last level of the path must be **, and no other levels can contain *or **. This path determines the subtree corresponding to the VIEW.

view_name

The name of the view, which follows the same rules as a table name (for specific constraints, refer to Create Table), e.g., db.view.

viewColumnDefinition

TAG: Each TAG column corresponds, in order, to the path nodes at the levels following the prefixPath.
FIELD: A FIELD column corresponds to a measurement (leaf node) in the tree model.
- If a FIELD column is specified, the column name uses the declared column_name.
  - If original_measurementis declared, it maps directly to that measurement in the tree model. Otherwise, the lowercase column_nameis used as the measurement name for mapping.
  - Mapping multiple FIELD columns to the same measurement name in the tree model is not supported.
  - If the dataTypefor a FIELD column is not specified, the system defaults to the data type of the mapped measurement in the tree model.
  - If a device in the tree model does not contain certain declared FIELD columns, or if their data types are inconsistent with the declared FIELD columns, the value for that FIELD column will always be NULLwhen querying that device.
- If no FIELD columns are specified, the system automatically scans for all measurements under the prefixPathsubtree (including all ordinary sequence measurements and measurements defined in any templates whose mounted paths overlap with the prefixPath) during creation. The column names will use the measurement names from the tree model.
  - The tree model cannot have measurements with the same name (case-insensitive) but different data types.

WITH properties

Currently, only TTL is supported. It indicates that data older than TTL (in milliseconds) will not be displayed in query results, i.e., effectively WHERE time > now() - TTL. If a TTL is also set in the tree model, the query uses the smaller value of the two.

Note: The table view's TTL does not affect the actual TTL of the devices in the tree model. When data reaches the TTL set in the tree model, it will be physically deleted by the system.

OR REPLACE

A table and a view cannot have the same name. If a table with the same name already exists during creation, an error will be reported. If a view with the same name already exists, it will be replaced.

RESTRICT

This constrains the number of levels of the tree model devices that are matched (starting from the level below the prefixPath). If the RESTRICTkeyword is present, only devices whose level count exactly equals the number of TAG columns are matched. Otherwise, devices whose level count is less than or equal to the number of TAG columns are matched. The default behavior is non-RESTRICT, meaning devices with a level count less than or equal to the number of TAG columns are matched.

2.1.3 Usage Example

Tree Model and Table View Schema

Creating the Table View

Creation Statement:

CREATE OR REPLACE VIEW viewdb."wind_turbine"
  (wind_turbine_group String TAG, 
   wind_turbine_number String TAG, 
   voltage DOUBLE FIELD, 
   current DOUBLE FIELD
  ) 
with (ttl=604800000)
AS root.db.**

Detailed Explanation

This statement creates a view named viewdb.wind_turbine(an error will occur if viewdbdoes not exist). If the view already exists, it will be replaced.

It creates a table view for the time series mounted under the tree model path root.db.**.
It has two TAG columns, wind_turbine_group and wind_turbine_number, so the table view will only include devices from the 3rd level of the original tree model.
It has two FIELDcolumns, voltage and current. Here, these FIELD columns correspond to measurement names in the tree model that are also voltage and current, and only select time series of type DOUBLE.

Renaming measurement requirement:

If the measurement name in the tree model is current_new, but you want the corresponding FIELD column name in the table view to be current, the SQL should be changed as follows:

CREATE OR REPLACE VIEW viewdb."wind_turbine"
  (wind_turbine_group String TAG, 
   wind_turbine_number String TAG, 
   voltage DOUBLE FIELD, 
   current DOUBLE FIELD FROM current_new
   ) 
with (ttl=604800000)
AS root.db.**

2.2 Modifying a Table View

2.2.1 Syntax Definition

The ALTER VIEW function supports modifying the view name, adding columns, renaming columns, deleting columns, setting the view's TTL property, and adding comments via COMMENT.

-- Rename view
ALTER VIEW [IF EXISTS] viewName RENAME TO to=identifier

-- Add a column to the view
ALTER VIEW [IF EXISTS] viewName ADD COLUMN [IF NOT EXISTS] viewColumnDefinition
viewColumnDefinition
  : column_name [dataType] TAG                                     # tagColumn
  | column_name [dataType] FIELD [FROM original_measurement]     # fieldColumn

-- Rename a column in the view
ALTER VIEW [IF EXISTS] viewName RENAME COLUMN [IF EXISTS] oldName TO newName

-- Delete a column from the view
ALTER VIEW [IF EXISTS] viewName DROP COLUMN [IF EXISTS] columnName

-- Modify the view's TTL
ALTER VIEW [IF EXISTS] viewName SET PROPERTIES propertyAssignments

-- Add comments
COMMENT ON VIEW qualifiedName IS (string | NULL) #commentView
COMMENT ON COLUMN qualifiedName '.' column=identifier IS (string | NULL) #commentColumn

2.2.2 Syntax Explanation

The SET PROPERTIESoperation currently only supports configuring the TTL property for the table view.
The DROP COLUMNfunction only supports deleting FIELD columns; TAG columns cannot be deleted.
Modifying the comment will overwrite the original comment. If set to null, the previous comment will be erased.

2.2.3 Usage Examples

-- Rename view
ALTER VIEW IF EXISTS tableview1 RENAME TO tableview

-- Add a column to the view
ALTER VIEW IF EXISTS tableview ADD COLUMN IF NOT EXISTS temperature float field

-- Rename a column in the view
ALTER VIEW IF EXISTS tableview RENAME COLUMN IF EXISTS temperature TO temp

-- Delete a column from the view
ALTER VIEW IF EXISTS tableview DROP COLUMN IF EXISTS temp

-- Modify the view's TTL
ALTER VIEW IF EXISTS tableview SET PROPERTIES TTL=3600

-- Add comments
COMMENT ON VIEW tableview IS 'Tree to Table'
COMMENT ON COLUMN tableview.status is Null

2.3 Deleting a Table View

2.3.1 Syntax Definition

DROP VIEW [IF EXISTS] viewName

2.3.2 Usage Example

DROP VIEW IF EXISTS tableview

2.4 Viewing Table Views

2.4.1 `Show Tables`

Syntax Definition

SHOW TABLES (DETAILS)? ((FROM | IN) database_name)?

Syntax Explanation

The SHOW TABLES (DETAILS)statement displays the type information of tables or views through the TABLE_TYPEfield in the result set:

Type	`TABLE_TYPE`Field Value
Ordinary Table（Table)	`BASE TABLE`
Tree-to-Table View （Tree View)	`VIEW FROM TREE`
System Table（Iinformation_schema.Tables)	`SYSTEM VIEW`

Usage Examples

IoTDB> show tables details from database1
+-----------+-----------+------+---------------+--------------+
|  TableName|    TTL(ms)|Status|        Comment|     TableType|
+-----------+-----------+------+---------------+--------------+
|  tableview|        INF| USING| Tree to Table |VIEW FROM TREE|
|     table1|31536000000| USING|           null|    BASE TABLE|
|     table2|31536000000| USING|           null|    BASE TABLE|
+-----------+-----------+------+---------------+--------------+

IoTDB> show tables details from information_schema 
+--------------+-------+------+-------+-----------+
|     TableName|TTL(ms)|Status|Comment|  TableType|
+--------------+-------+------+-------+-----------+
|       columns|    INF| USING|   null|SYSTEM VIEW|
|  config_nodes|    INF| USING|   null|SYSTEM VIEW|
|configurations|    INF| USING|   null|SYSTEM VIEW|
|    data_nodes|    INF| USING|   null|SYSTEM VIEW|
|     databases|    INF| USING|   null|SYSTEM VIEW|
|     functions|    INF| USING|   null|SYSTEM VIEW|
|      keywords|    INF| USING|   null|SYSTEM VIEW|
|        models|    INF| USING|   null|SYSTEM VIEW|
|         nodes|    INF| USING|   null|SYSTEM VIEW|
|  pipe_plugins|    INF| USING|   null|SYSTEM VIEW|
|         pipes|    INF| USING|   null|SYSTEM VIEW|
|       queries|    INF| USING|   null|SYSTEM VIEW|
|       regions|    INF| USING|   null|SYSTEM VIEW|
| subscriptions|    INF| USING|   null|SYSTEM VIEW|
|        tables|    INF| USING|   null|SYSTEM VIEW|
|        topics|    INF| USING|   null|SYSTEM VIEW|
|         views|    INF| USING|   null|SYSTEM VIEW|
+--------------+-------+------+-------+-----------+

2.4.2 `Show Create Table/View`

Syntax Definition

SHOW CREATE TABLE|VIEW viewname;

Syntax Explanation

The SHOW CREATE TABLEstatement can be used to display the complete creation information for ordinary tables or views.
The SHOW CREATE VIEWstatement can only be used to display the complete creation information for views.
Neither statement can be used to display system tables.

Usage Examples

IoTDB> show create table tableview
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     View|                                                                                                                                                  Create View|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|tableview|CREATE VIEW "tableview" ("device" STRING TAG,"model" STRING TAG,"status" BOOLEAN FIELD,"hardware" STRING FIELD) COMMENT '树转表' WITH (ttl=INF) AS root.ln.**|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

IoTDB> show create view tableview
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     View|                                                                                                                                                  Create View|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|tableview|CREATE VIEW "tableview" ("device" STRING TAG,"model" STRING TAG,"status" BOOLEAN FIELD,"hardware" STRING FIELD) COMMENT '表视图' WITH (ttl=INF) AS root.ln.**|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

2.5 Query Differences Between Non-aligned and Aligned Devices

Queries on tree-to-table views may yield different results compared to equivalent tree model ALIGN BY DEVICEqueries when dealing with null values in aligned and non-aligned devices.

Aligned Devices
- Tree Model Query Behavior:Rows where all selected time series have null values are not retained.
- Table View Query Behavior:Consistent with the table model, rows where all selected fields are null are retained.
Non-aligned Devices
- Tree Model Query Behavior:Rows where all selected time series have null values are not retained.
- Table View Query Behavior:Consistent with the tree model, rows where all selected fields are null are not retained.

Explanation Example

Aligned

-- Write data in tree model (aligned)
CREATE ALIGNED TIMESERIES root.db.battery.b1(voltage INT32, current FLOAT)
INSERT INTO root.db.battery.b1(time, voltage, current) aligned values (1, 1, 1)
INSERT INTO root.db.battery.b1(time, voltage, current) aligned values (2, null, 1)

-- Create VIEW statement
CREATE VIEW view1 (battery_id TAG, voltage INT32 FIELD, current FLOAT FIELD) as root.db.battery.**

-- Query
IoTDB> select voltage from view1
+-------+
|voltage|
+-------+
|      1|
|   null|
+-------+
Total line number = 2

Non-aligned

-- Write data in tree model (non-aligned)
CREATE TIMESERIES root.db.battery.b1.voltage INT32
CREATE TIMESERIES root.db.battery.b1.current FLOAT
INSERT INTO root.db.battery.b1(time, voltage, current) values (1, 1, 1)
INSERT INTO root.db.battery.b1(time, voltage, current) values (2, null, 1)

-- Create VIEW statement
CREATE VIEW view1 (battery_id TAG, voltage INT32 FIELD, current FLOAT FIELD) as root.db.battery.**

-- Query
IoTDB> select voltage from view1
+-------+
|voltage|
+-------+
|      1|
+-------+
Total line number = 1

-- Can only ensure all rows are retrieved if the query specifies all FIELD columns, or only non-FIELD columns
IoTDB> select voltage,current from view1
+-------+-------+
|voltage|current|
+-------+-------+
|      1|    1.0|
|   null|    1.0|
+-------+-------+
Total line number = 2

IoTDB> select battery_id from view1
+-----------+
|battery_id|
+-----------+
|         b1|
|         b1|
+-----------+
Total line number = 2

-- If the query involves only some FIELD columns, the final number of rows depends on the number of rows after aligning the specified FIELD columns by timestamp.
IoTDB> select time,voltage from view1
+-----------------------------+-------+
|                         time|voltage|
+-----------------------------+-------+
|1970-01-01T08:00:00.001+08:00|      1|
+-----------------------------+-------+
Total line number = 1