Skip to main content

Logical View

About 4 min

Logical View

1. Functional Overview

IoTDB supports creating logical views, which save queries as reusable views. A logical view can then be queried in the same way as a regular table, reducing the need to repeatedly write complex SQL and providing applications with a unified query interface.

A logical view stores only the query statement; it does not copy or persist query results. Each time a logical view is queried, IoTDB executes the query statement saved in the view and returns the query results.

Note: This feature is supported starting from V2.0.11.1.

2. Feature Description

2.1 Creating a Logical View

2.1.1 Syntax Definition

CREATE [OR REPLACE] VIEW qualifiedName
  [COMMENT string]
  AS query

2.1.2 Syntax Description

1. qualifiedName

The logical view name, which follows the same naming rules as table names. If the current session is using a database, you can specify the view name directly; otherwise, you must use the fully qualified view name that includes the database name.

2. OR REPLACE

If OR REPLACE is specified and a logical view with the same name already exists, the new query statement replaces the original definition.

A logical view can be replaced even if it is already referenced by other logical views. Note that if the column names, number of columns, or column types returned by the replacement change, other logical views that reference this view may report errors when queried. In this case, the related views must also be adjusted accordingly.

3. COMMENT string

Adds a comment to the logical view. The comment can be viewed using metadata display statements.

4. query

The query statement saved by the logical view. The query statement can reference the following objects:

  • Regular tables.

  • Tree-to-table views.

  • Writable views.

  • Other logical views.

Note: Circular references are not allowed between logical views. For example, if view a references view b, while view b directly or indirectly references view a, the definition is rejected.

2.1.3 Examples

The following examples use table1 from the sample data. This table contains columns such as region, plant_id, device_id, temperature, humidity, and status.

  • Create a logical view based on a regular table:
CREATE VIEW device_temperature_view AS
  SELECT time, region, plant_id, device_id, temperature
  FROM table1;
  • Create a logical view based on another logical view:
CREATE VIEW high_temperature_view AS
  SELECT time, region, plant_id, device_id, temperature
  FROM device_temperature_view
  WHERE temperature >= 90.0;
  • Create a logical view based on a tree-to-table view:
CREATE VIEW treeView  (
    value FLOAT FIELD
)
AS root.test.**;

CREATE VIEW view_base_treeview AS
  SELECT value
  FROM treeView;
  • Create a logical view based on a writable view:
CREATE WRITABLE VIEW table1_writable_view AS
  SELECT temperature, humidity, status
  FROM table1;

CREATE VIEW view_base_writableview AS
  SELECT temperature, humidity
  FROM table1_writable_view;
  • Circular reference example:
CREATE VIEW a AS
  SELECT time, region, plant_id, device_id, temperature
  FROM table1;

CREATE VIEW b AS
  SELECT * FROM a;

CREATE OR REPLACE VIEW a AS
  SELECT * FROM b;

The preceding statements create a circular reference between view a and view b, so execution fails:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Failed analyzing stored view 'b': Statement would create a recursive view

2.2 Modifying a Logical View

2.2.1 Syntax Definition

You can rename a logical view, as well as add, overwrite, or clear its comment.

ALTER VIEW [IF EXISTS] viewName RENAME TO identifier

COMMENT ON VIEW qualifiedName IS (string | NULL)

2.2.2 Syntax Description

  1. Use ALTER VIEW ... RENAME TO to rename a logical view.

  2. Use COMMENT ON VIEW to modify a logical view comment. Specifying a string adds or overwrites the comment; specifying NULL clears the existing comment.

2.2.3 Examples

ALTER VIEW IF EXISTS device_temperature_view RENAME TO temperature_view ;

COMMENT ON VIEW high_temperature_view IS 'high temperature view';

COMMENT ON VIEW high_temperature_view IS NULL;

2.3 Dropping a Logical View

2.3.1 Syntax Definition

DROP VIEW [IF EXISTS] viewName

2.3.2 Example

DROP VIEW IF EXISTS view_base_writableview;

2.4 Viewing Logical Views

2.4.1 Syntax Definition

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

2.4.2 Syntax Description

  • SHOW VIEWS lists the views in the current database.

  • SHOW TABLES DETAILS uses the TableType field in the result set to show the type of each table or view. For a logical view, TableType is VIEW.

  • DESC <VIEW_NAME> DETAILS displays the column information of a logical view.

  • Both SHOW CREATE VIEW and SHOW CREATE TABLE can display the statement used to create a view.

2.4.3 Examples

  • View table and view details:
TimechoDB:database1> SHOW TABLES DETAILS FROM database1;
+-----------------------+-------+------+-------+--------------+-----------------+
|              TableName|TTL(ms)|Status|Comment|     TableType|OriginalTableName|
+-----------------------+-------+------+-------+--------------+-----------------+
|       temperature_view|    INF| USING| device|          VIEW|             null|
|                 table1|    INF| USING|   null|    BASE TABLE|             null|
|            table1_view|    INF| USING|   null| WRITABLE VIEW|           table1|
|               treeview|    INF| USING|   null|VIEW FROM TREE|             null|
+-----------------------+-------+------+-------+--------------+-----------------+
Total line number = 5
It costs 0.000s
  • View the columns of a logical view:
TimechoDB:database1> desc temperature_view
+-----------+---------+--------+
| ColumnName| DataType|Category|
+-----------+---------+--------+
|       time|TIMESTAMP|   FIELD|
|     region|   STRING|   FIELD|
|   plant_id|   STRING|   FIELD|
|  device_id|   STRING|   FIELD|
|temperature|    FLOAT|   FIELD|
+-----------+---------+--------+
Total line number = 5
It costs 0.020s
  • View detailed column information for a logical view:
TimechoDB:database1> desc temperature_view details
+-----------+---------+--------+------+-------+
| ColumnName| DataType|Category|Status|Comment|
+-----------+---------+--------+------+-------+
|       time|TIMESTAMP|   FIELD| USING|   null|
|     region|   STRING|   FIELD| USING|   null|
|   plant_id|   STRING|   FIELD| USING|   null|
|  device_id|   STRING|   FIELD| USING|   null|
|temperature|    FLOAT|   FIELD| USING|   null|
+-----------+---------+--------+------+-------+
Total line number = 5
It costs 0.032s
  • View the statement used to create a logical view:
TimechoDB:database1> SHOW CREATE VIEW device_temperature_view;
+----------------+-----------------------------------------------------------------------------------------------------------------+
|            View|                                                                                                      Create View|
+----------------+-----------------------------------------------------------------------------------------------------------------+
|temperature_view|CREATE VIEW "temperature_view" AS SELECT time , region , plant_id , device_id , temperature FROM database1.table1|
+----------------+-----------------------------------------------------------------------------------------------------------------+
Total line number = 1
It costs 0.026s

Notes:

  • SHOW CREATE VIEW supports views only; it does not support regular tables or system tables under INFORMATION_SCHEMA.

  • SHOW CREATE TABLE does not support system tables under INFORMATION_SCHEMA.

Invalid examples:

SHOW CREATE VIEW table1;
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: The table table1 is a base table, does not support show create view.

SHOW CREATE VIEW information_schema.tables;
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: The system view does not support show create.

SHOW CREATE TABLE information_schema.tables;
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: The system view does not support show create.

3. Limitations and Considerations

3.1 Changes to Referenced Tables or Views

A logical view stores only its query definition and does not lock the tables or views that it references. Therefore, a table or view referenced by a logical view can still be altered, dropped, or replaced using ALTER, DROP, or REPLACE.

After a referenced table or view changes, querying the logical view may result in the following errors:

  • If a referenced table or view is dropped, querying the logical view reports an error indicating that the corresponding table does not exist.

  • If a referenced table or view is dropped and then re-created, or if it is replaced using REPLACE, and the actual query result of the logical view no longer matches its original definition, querying the logical view reports an error indicating that the logical view is stale or in an invalid state.

Related error examples:

Table '%s' does not exist

View '%s' is stale or in invalid state: %s

3.2 Access Control

The permissions required for logical view operations are as follows:

  • To execute CREATE VIEW or CREATE OR REPLACE VIEW, you must have the CREATE privilege on the target view or its database, as well as the SELECT privilege on all referenced tables or views.

  • To execute ALTER VIEW, you must have the ALTER privilege on the target view or its database.

  • To execute DROP VIEW, you must have the DROP privilege on the target view or its database.

  • To query data from a logical view, you need only the SELECT privilege on the logical view; you do not need the SELECT privilege on the referenced tables or views.

Last Updated:

Copyright © 2026 Timecho Limited All rights reserved.
Apache IoTDB and the Apache IoTDB project logo are trademarks of The Apache Software Foundation.