Writable View

About 9 min

Writable View

1. Functional Overview

Starting from V2.0.10.1, SQL can be used to create writable views for specified tables. A writable view can be read from and written to like a normal base table, and it also supports changing table names and column names.

2. Feature Description

2.1 Creating a Writable View

2.1.1 Syntax Definition

CREATE WRITABLE VIEW <VIEW_NAME> AS
    SELECT (<SOURCE_COL>[ as <VIEW_COL>][COMMENT <comment>](,<SOURCE_COL>[ as <VIEW_COL>][COMMENT <comment>])* | '*')
        FROM <TABLE_NAME> [COMMENT <comment>] [WITH (schema_cascade=true|false[, ttl=])];

2.1.2 Parameter Description

Parameter	Description
`<VIEW_NAME>`	The name of the writable view to be created.
`<TABLE_NAME>`	The source table name. The writable view is created based on this table.
`<COLUMN_NAME>`	The column name in the source table. You can select all columns or only some columns.
`AS <NEW_NAME>`	Specifies a new name for a view column. If not specified, the view column name is the same as the source table column name.
`COMMENT <comment>`	Adds a comment to the view or view column.
`schema_cascade`	Specifies whether some view schema changes are synchronized to the source table. The default value is `true` (see the following description).
`ttl`	The TTL of the view. If not specified or specified as `default`, the TTL of the source table is inherited.

2.1.3 Usage Notes

Creating a writable view across databases is not supported.
Creating a writable view based on another view, including a tree-to-table view or a writable view, is not supported.
Mapping multiple view columns to the same source table column is not supported, for example temperature AS temp1, temperature AS temp2.
If the time column is not explicitly selected when creating a view, the system automatically adds the time column from the source table.
Selecting only some Tag columns is supported. Tag columns in the source table that do not appear in the view are filled with null when data is written through the view.
When data is written or deleted through a writable view, the operation takes effect on the source table. When operating on a writable view, if the source table or its columns have been deleted, an error is reported.
schema_cascade determines whether metadata modification operations initiated through the writable view are cascaded to the source table. The specific behavior is as follows:

Operation	schema_cascade = true (default)	schema_cascade = false
Rename Table / Column	Synchronization to the source table is not supported	Synchronization to the source table is not supported
Add column (ADD COLUMN)	Synchronized to the source table. If the source table already has this column, the category and type must be consistent; otherwise, an error is reported	Added only in the view. The target column must exist in the source table, and the category and type must be consistent; otherwise, an error is reported
Drop column (DROP COLUMN)	Synchronized to the source table. If the category of the source table column is inconsistent with that of the view, an error is reported. If the dropped column is a Tag column of the view, it is not synchronized to the source table	Only deletes the column from the view and does not affect the source table
Drop view (DROP)	If cascade is true, the source table is also deleted	Only deletes the view and does not affect the source table
Modify column data type	Synchronized to the source table	Modification is not allowed
Modify TTL	Synchronized to the source table	Does not affect the source table
Modify Comment	If a comment is explicitly set and cascade is true, it is synchronized to the source table	Does not affect the source table

Note:

Metadata modifications on the source table are **not **automatically synchronized to the writable view. When operating on the writable view, if the source table or its columns do not exist, an error is reported.
TTL modifications on the source table are **not **automatically synchronized to the writable view. When the TTL of the view and the source table are inconsistent:
- Physical deletion of data is based on the source table.
- Query visibility for new data is based on the lower TTL of the two tables.
If SET PROPERTIES modifies both schema_cascade and other properties such as TTL, this modification uses the old schema_cascade value. Subsequent operations use the modified schema_cascade value.

Data synchronization related:

When creating a Pipe, if the specified table is a view, it is automatically rewritten to the source table.
Metadata synchronization needs to synchronize the new view operation statements:
- If a table with the same name on the receiver is not a writable view, or the receiver-side sourceTable corresponding to creation is not a base table, an error is reported.
- Any cascade modification to the base table must be synchronized to the receiver, regardless of the receiver status.
- When pattern / privilege only supports writable views, synchronization to the receiver-side specified source table is determined by the receiver's own cascade setting. When only the source table is supported, only source table modifications are synchronized. If neither is supported, synchronization is not performed.
- For source columns, if the sender-side view and receiver-side view map to different table columns, both the receiver's own source columns and the sender's source columns are modified.
- For receiver privileges: when skipIfNoPrivileges is true, pruning and mapping are performed according to the preceding rules. Otherwise, view authorization errors return a failure, while source authorization errors still perform pruning regardless of skipIfNoPrivileges.

2.1.4 Simple Examples

Create a writable view with the same columns as the source table:

CREATE WRITABLE VIEW table1_view AS
    SELECT *
    FROM Table;

Create a writable view with new column names:

CREATE WRITABLE VIEW table1_device_view AS
    SELECT
        region,
        plant_id,
        device_id AS device,
        model_id AS model,
        temperature AS temp,
        humidity,
        status,
        arrival_time
    FROM table1;

Create a writable view with comments and TTL:

CREATE WRITABLE VIEW table1_public_view AS
    SELECT
        region COMMENT '区域',
        plant_id COMMENT '工厂编号',
        device_id COMMENT '设备编号',
        temperature COMMENT '温度',
        humidity COMMENT '湿度',
        status COMMENT '设备状态'
    FROM table1
    COMMENT '设备运行数据视图'
    WITH (schema_cascade = false, ttl = 604800000);

2.2 Modifying a Writable View

2.2.1 Syntax Definition

Writable views can be modified using ALTER VIEW:

alterViewStatement
    : ALTER VIEW (IF EXISTS)? from=qualifiedName RENAME TO to=identifier #renameTableView
    | ALTER VIEW (IF EXISTS)? viewName=qualifiedName ADD COLUMN (IF NOT EXISTS)? viewColumnDefinition #addViewColumn
    | ALTER VIEW (IF EXISTS)? viewName=qualifiedName RENAME COLUMN (IF EXISTS)? from=identifier TO to=identifier #renameViewColumn
    | ALTER VIEW (IF EXISTS)? viewName=qualifiedName DROP COLUMN (IF EXISTS)? column=identifier #dropViewColumn
    | ALTER VIEW (IF EXISTS)? viewName=qualifiedName SET PROPERTIES propertyAssignments #setTableViewProperties

The compatible ALTER TABLE syntax can also be used:

alterTableStatement
    : ALTER TABLE (IF EXISTS)? from=qualifiedName RENAME TO to=identifier                                #renameTable
    | ALTER TABLE (IF EXISTS)? tableName=qualifiedName ADD COLUMN (IF NOT EXISTS)? column=columnDefinition                #addColumn
    | ALTER TABLE (IF EXISTS)? tableName=qualifiedName RENAME COLUMN (IF EXISTS)? from=identifier TO to=identifier    #renameColumn
    | ALTER TABLE (IF EXISTS)? tableName=qualifiedName DROP COLUMN (IF EXISTS)? column=identifier                     #dropColumn
    // set TTL can use this
    | ALTER TABLE (IF EXISTS)? tableName=qualifiedName SET PROPERTIES propertyAssignments                #setTableProperties
    | ALTER TABLE (IF EXISTS)? tableName=qualifiedName ALTER COLUMN (IF EXISTS)? column=identifier SET DATA TYPE new_type=type #alterColumnDataType

2.2.2 Usage Notes

Renaming the view name or a view column name only affects the view itself, and does not modify the source table name or source table column names.
For the cascade impact of view metadata modifications, refer to the table in section 2.1.3.

2.2.3 Simple Examples

Rename a writable view:

ALTER VIEW table1_device_view
    RENAME TO table1_equipment_view;

Rename a view column:

ALTER VIEW table1_equipment_view
    RENAME COLUMN device TO equipment_id;

Add a view column:

ALTER VIEW table1_equipment_view
    ADD COLUMN maintenance STRING ATTRIBUTE COMMENT '维护状态';

Modify the view TTL:

ALTER VIEW table1_equipment_view
    SET PROPERTIES ttl = 2592000000;

Disable schema cascade:

ALTER VIEW table1_equipment_view
    SET PROPERTIES schema_cascade = false;

Re-enable schema cascade and then modify the column type:

ALTER VIEW table1_equipment_view
    SET PROPERTIES schema_cascade = true;

ALTER TABLE table1_equipment_view
    ALTER COLUMN humidity SET DATA TYPE DOUBLE;

Drop a view column:

ALTER VIEW table1_equipment_view
    DROP COLUMN maintenance;

2.3 Dropping a Writable View

2.3.1 Syntax Definition

dropViewStatement
    : DROP VIEW (IF EXISTS)? qualifiedName
    ;

The compatible DROP TABLE syntax can also be used:

dropTableStatement
    : DROP TABLE (IF EXISTS)? qualifiedName
    ;

2.3.2 Usage Notes

The default value of schema_cascade for a writable view is true. When a view is dropped, the drop operation also affects the source table.
If you want to delete only the view and keep the source table, set the view's schema_cascade to false before dropping it.

ALTER VIEW table1_equipment_view
    SET PROPERTIES schema_cascade = false;

2.3.3 Simple Examples

DROP VIEW IF EXISTS table1_equipment_view;

DROP TABLE IF EXISTS table1_equipment_view;

2.4 Viewing Writable Views

2.4.1 Syntax Definition

showViewStatement
    : SHOW WRITABLE? VIEWS ((FROM | IN) databaseName=identifier)? #showViews
    | SHOW TABLES DETAILS ((FROM | IN) databaseName=identifier)? #showTablesDetails
    | (DESC | DESCRIBE) viewName=qualifiedName DETAILS? #describeView
    | SHOW CREATE (TABLE | VIEW) viewName=qualifiedName #showCreateView
    ;

2.4.2 Usage Notes

SHOW VIEWS is used to view views in the current database. SHOW WRITABLE VIEWS displays only writable views.
SHOW TABLES DETAILS displays the type of a table or view through the TableType field in the result set. For a writable view, TableType is WRITABLE VIEW, and the OriginalTableName column displays the source table name.
When DESC <VIEW_NAME> DETAILS is executed on a writable view, the OriginalColumn column in the result set displays the source table column name corresponding to the view column.
Both SHOW CREATE VIEW and SHOW CREATE TABLE can be used to view the creation statement of a writable view.

2.4.3 Simple Examples

View table and writable view details:

IoTDB:database1> show tables details
+------------------+---------+------+----------------+-------------+-----------------+
|         TableName|  TTL(ms)|Status|         Comment|    TableType|OriginalTableName|
+------------------+---------+------+----------------+-------------+-----------------+
|            table1|      INF| USING|            null|   BASE TABLE|             null|
|table1_public_view|604800000| USING|  设备运行数据视图|WRITABLE VIEW|           table1|
|       table1_view|      INF| USING|            null|WRITABLE VIEW|           table1|
+------------------+---------+------+----------------+-------------+-----------------+
Total line number = 3
It costs 0.004s

View column information of a writable view:

IoTDB:database1> desc table1_view
+------------+---------+---------+
|  ColumnName| DataType| Category|
+------------+---------+---------+
|        time|TIMESTAMP|     TIME|
|      region|   STRING|      TAG|
|    plant_id|   STRING|      TAG|
|   device_id|   STRING|      TAG|
|    model_id|   STRING|ATTRIBUTE|
| maintenance|   STRING|ATTRIBUTE|
| temperature|    FLOAT|    FIELD|
|    humidity|    FLOAT|    FIELD|
|      status|  BOOLEAN|    FIELD|
|arrival_time|TIMESTAMP|    FIELD|
+------------+---------+---------+
Total line number = 10
It costs 0.013s

View detailed column information of a writable view:

IoTDB:database1> desc table1_view details
+------------+---------+---------+------+-------+------------------+
|  ColumnName| DataType| Category|Status|Comment|OriginalColumnName|
+------------+---------+---------+------+-------+------------------+
|        time|TIMESTAMP|     TIME| USING|   null|              time|
|      region|   STRING|      TAG| USING|   null|            region|
|    plant_id|   STRING|      TAG| USING|   null|          plant_id|
|   device_id|   STRING|      TAG| USING|   null|         device_id|
|    model_id|   STRING|ATTRIBUTE| USING|   null|          model_id|
| maintenance|   STRING|ATTRIBUTE| USING|   null|       maintenance|
| temperature|    FLOAT|    FIELD| USING|   null|       temperature|
|    humidity|    FLOAT|    FIELD| USING|   null|          humidity|
|      status|  BOOLEAN|    FIELD| USING|   null|            status|
|arrival_time|TIMESTAMP|    FIELD| USING|   null|      arrival_time|
+------------+---------+---------+------+-------+------------------+
Total line number = 10
It costs 0.006s

View the creation statement of a writable view:

IoTDB:database1> show create view table1_view 
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       View|                                                                                                                                                                                                                                                                                                                                                     Create View|
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|table1_view|CREATE WRITABLE VIEW "table1_view" AS SELECT "time" AS "time","region" AS "region","plant_id" AS "plant_id","device_id" AS "device_id","model_id" AS "model_id","maintenance" AS "maintenance","temperature" AS "temperature","humidity" AS "humidity","status" AS "status","arrival_time" AS "arrival_time" FROM "table1" WITH (ttl='INF', schema_cascade=true)|
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Total line number = 1
It costs 0.011s

View the writable view list:

IoTDB:database1> show writable views
+------------------+---------+
|         TableName|  TTL(ms)|
+------------------+---------+
|table1_public_view|604800000|
|       table1_view|      INF|
+------------------+---------+
Total line number = 2
It costs 0.004s

3. Permission Description

Operation	Super administrator	Regular user
Create a writable view	Supported	Requires the CREATE permission on the View (or its DB, or ANY), and the SELECT, INSERT, DELETE permissions on the original Table (or its DB, or ANY). If schema_cascade=true, ALTER and DROP permissions or SYSTEM permission are also required
Drop a writable view	Supported	Requires the DROP permission on the View (or its DB, or ANY) or SYSTEM permission
Modify a writable view	Supported	Requires the ALTER permission on the View (or its DB, or ANY) or SYSTEM permission
Write/delete/query through a writable view	Supported	Requires the INSERT/DELETE/SELECT permission on the View (or its DB, or ANY) or SYSTEM permission

Because the permissions on the source table are obtained when the view is created, subsequent operations treat a user who has been granted permissions on the view as indirectly granted the corresponding permissions on the source table. Therefore, source table authorization is no longer required.

4. Scenario Examples

Provide a new name for an existing table

If you want to use table1_view externally as a new entry point for table1:

CREATE WRITABLE VIEW table1_view AS
    SELECT *
    FROM table1;

You can then query the view directly:

SELECT *
FROM table1_view
WHERE device_id = '101';

Returned result

+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|                         time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status|                 arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-30T09:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    35.2|  true|                         null|
|2024-11-30T14:30:00.000+08:00|  上海|    3002|      101|       F|        360|       90.0|    34.8|  true|2024-11-30T14:30:17.000+08:00|
|2024-11-29T10:00:00.000+08:00|  上海|    3001|      101|       D|        360|       85.0|    null|  null|2024-11-29T10:00:13.000+08:00|
|2024-11-27T16:38:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    35.1|  true|2024-11-27T16:37:01.000+08:00|
|2024-11-27T16:39:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    35.3|  null|                         null|
|2024-11-27T16:40:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    null|  null|2024-11-27T16:37:03.000+08:00|
|2024-11-27T16:41:00.000+08:00|  北京|    1001|      101|       B|        180|       85.0|    null|  null|2024-11-27T16:37:04.000+08:00|
|2024-11-27T16:42:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    35.2| false|                         null|
|2024-11-27T16:43:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    null| false|                         null|
|2024-11-27T16:44:00.000+08:00|  北京|    1001|      101|       B|        180|       null|    null| false|2024-11-27T16:37:08.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 10
It costs 0.021s

Provide business names for fields

If the business side wants to use shorter field names such as device and temp:

CREATE WRITABLE VIEW table1_business_view AS
    SELECT
        region,
        plant_id,
        device_id AS device,
        model_id AS model,
        maintenance,
        temperature AS temp,
        humidity,
        status,
        arrival_time
    FROM table1;

The business side can query using the new field names:

SELECT device, model, temp, humidity, status
FROM table1_business_view
WHERE region = '北京';

Returned result

+------+-----+----+--------+------+
|device|model|temp|humidity|status|
+------+-----+----+--------+------+
|   100|    A|90.0|    35.1|  true|
|   100|    A|90.0|    35.1|  true|
|   101|    B|null|    35.1|  true|
|   101|    B|85.0|    35.3|  null|
|   101|    B|85.0|    null|  null|
|   101|    B|85.0|    null|  null|
|   101|    B|null|    35.2| false|
|   101|    B|null|    null| false|
|   101|    B|null|    null| false|
+------+-----+----+--------+------+
Total line number = 9
It costs 0.044s

Expose only some columns

If you only want users to access device status and environment data:

CREATE WRITABLE VIEW table1_status_view AS
    SELECT
        region,
        plant_id,
        device_id,
        temperature,
        humidity,
        status
    FROM table1
    WITH (schema_cascade = false);

Users only need to focus on the public columns when querying:

SELECT device_id, temperature, humidity, status 
FROM table1_status_view
WHERE plant_id = '3002';

Returned result

+---------+-----------+--------+------+
|device_id|temperature|humidity|status|
+---------+-----------+--------+------+
|      100|       null|    45.1|  true|
|      100|       90.0|    35.4|  true|
|      101|       90.0|    35.2|  true|
|      101|       90.0|    34.8|  true|
+---------+-----------+--------+------+
Total line number = 4
It costs 0.034s

Compatible with historical system field names

If a historical system uses field names such as factory, device, and temp, you can create a compatibility view:

CREATE WRITABLE VIEW table1_legacy_view AS
    SELECT
        plant_id AS factory,
        device_id AS device,
        temperature AS temp,
        humidity,
        status
    FROM table1;

The historical system can continue writing using the old field names:

INSERT INTO table1_legacy_view (
    time,
    factory,
    device,
    temp,
    humidity,
    status
) VALUES (
    2026-05-19T10:00:00+08:00,
    'plant-01',
    'device-001',
    36.5,
    42.0,
    true
);

Verify the write result:

IoTDB:database1> select * from table1_legacy_view where device = 'device-001';
+--------+----------+----+--------+------+-----------------------------+
| factory|    device|temp|humidity|status|                         time|
+--------+----------+----+--------+------+-----------------------------+
|plant-01|device-001|36.5|    42.0|  true|2026-05-19T10:00:00.000+08:00|
+--------+----------+----+--------+------+-----------------------------+
Total line number = 1
It costs 0.016s

IoTDB:database1> select * from table1 where device_id = 'device-001';
+-----------------------------+------+--------+----------+--------+-----------+-----------+--------+------+------------+
|                         time|region|plant_id| device_id|model_id|maintenance|temperature|humidity|status|arrival_time|
+-----------------------------+------+--------+----------+--------+-----------+-----------+--------+------+------------+
|2026-05-19T10:00:00.000+08:00|  null|plant-01|device-001|    null|       null|       36.5|    42.0|  true|        null|
+-----------------------------+------+--------+----------+--------+-----------+-----------+--------+------+------------+
Total line number = 1
It costs 0.016s