Views

Starting from TDengine 3.2.1.0, TDengine Enterprise Edition ¹ provides the functionality of views, which simplifies operations and enhances sharing capabilities among users.

A view (View) is essentially a query statement stored in the database. Views (non-materialized views) do not contain data themselves; the specified query statement is dynamically executed only when data is read from the view. When creating a view, we specify a name for it, and then it can be queried and operated on like a regular table. The use of views must follow these rules:

• Views can be nested in definitions and usage, and are bound to the database specified at creation time or the current database.
• Within the same database, view names must not be duplicated, and it is recommended that view names do not duplicate table names (not enforced). When a view and a table have the same name, operations such as writing, querying, granting, and revoking permissions prioritize the table with the same name.

Syntax

Creating (Updating) a View

CREATE [ OR REPLACE ] VIEW [db_name.]view_name AS query

Explanation:

• When creating a view, you can specify the database name (db_name) to which the view is bound. If not specified, it defaults to the database bound to the current connection;
• It is recommended to specify the database name in the query statement (query), supporting cross-database views. If not specified, it defaults to the database bound to the view (which may not be the database specified by the current connection);

Viewing Views

1. View all views under a certain database

SHOW [db_name.]VIEWS;

1. View the creation statement of a view

SHOW CREATE VIEW [db_name.]view_name;

1. View column information of a view

DESCRIBE [db_name.]view_name;

1. View all views information

SELECT ... FROM information_schema.ins_views;

Deleting a View

DROP VIEW [IF EXISTS] [db_name.]view_name;

Permissions

Explanation

View permissions are divided into READ, WRITE, and ALTER. Query operations require READ permission, write operations require WRITE permission, and modification or deletion of the view itself requires ALTER permission.

Rules

• The creator of the view and root users have all permissions by default.
• Granting and revoking permissions to other users can be done through the GRANT and REVOKE statements, which can only be performed by root users.
• View permissions must be granted and revoked separately; granting and revoking through db.* does not include view permissions.
• Views can be nested in definitions and usage, and similarly, the verification of view permissions is done recursively.
• To facilitate the sharing and use of views, the concept of an effective user of the view (i.e., the creator of the view) is introduced. Authorized users can use the effective user's database, tables, and nested views' read and write permissions. Note: The effective user is also updated when the view is REPLACEd.

The specific rules for permission control are shown in the table below:

Syntax

Authorization

GRANT privileges ON [db_name.]view_name TO user_name
privileges: {
    ALL,
  | priv_type [, priv_type] ...
}
priv_type: {
    READ
  | WRITE
  | ALTER
}

Revoke Permissions

REVOKE privileges ON [db_name.]view_name FROM user_name
privileges: {
    ALL,
  | priv_type [, priv_type] ...
}
priv_type: {
    READ
  | WRITE
  | ALTER
}

Use Cases

Examples

• Create View

  sqlCopy

  CREATE VIEW view1 AS SELECT _wstart, count(*) FROM table1 INTERVAL(1d);
  CREATE VIEW view2 AS SELECT ts, col2 FROM table1;
  CREATE VIEW view3 AS SELECT * from view1;
  

• Query Data

  sqlCopy

  SELECT * from view1;
  

• Delete View

  sqlCopy

  DROP VIEW view1;