Virtual Tables

Create Virtual Table

The CREATE VTABLE statement is used to create virtual basic tables and virtual subtables using virtual supertables as templates.

Create Virtual Supertables

Refer to the VIRTUAL parameter in Create Supertable.

Create Virtual Basic Table

CREATE VTABLE [IF NOT EXISTS] [db_name].vtb_name 
    ts_col_name timestamp, 
    (create_definition[ ,create_definition] ...) 
     
  create_definition:
    vtb_col_name column_definition
    
  column_definition:
    type_name [FROM [db_name.]table_name.col_name]

Create Virtual Subtable

CREATE VTABLE [IF NOT EXISTS] [db_name].vtb_name 
    (create_definition[ ,create_definition] ...) 
    USING [db_name.]stb_name 
    [(tag_name [, tag_name] ...)] 
    TAGS (tag_value [, tag_value] ...)
     
  create_definition:
    [stb_col_name FROM] [db_name.]table_name.col_name
  tag_value:
     const_value

Usage Notes:

1. Naming rules for virtual tables/columns follow Name Rules.
2. The maximum number of columns in a virtual table is 32767.
3. Maximum table name length: 192 characters.
4. The first column must be TIMESTAMP and is automatically set as primary key.
5. Row length cannot exceed 512KB (Note: VARCHAR/NCHAR/GEOMETRY columns consume 2 extra bytes each).
6. Specify maximum length for VARCHAR/NCHAR/GEOMETRY types (e.g., VARCHAR(20)).
7. Use FROM to specify column data sources. Cross-database sources are supported via db_name.
8. The timestamp column (ts) values of virtual table are merged results from all involved tables' timestamp primary keys during queries.
9. Virtual supertables only support creating virtual subtables, virtual subtables can only use virtual supertables as template.
10. Ensure virtual tables' column/tag data types match their source columns/tags.
11. Virtual table names must be unique within a database and cannot conflict with table names, and it is recommended that view names do not duplicate virtual table names (not enforced). When a view and a virtual table have the same name, operations such as writing, querying, granting, and revoking permissions prioritize the virtual table with the same name. .
12. When creating virtual subtables/basic tables, FROM columns must originate from basic tables/subtables (not supertables, views, or other virtual tables).

Query Virtual Tables

Virtual tables use the same query syntax as regular tables, but their dataset may vary between queries based on data alignment rules.

Data Alignment Rules

1. Align data from multiple source tables by timestamp.
2. Combine columns with same timestamp into one row; missing values fill with NULL.
3. Virtual table timestamps are the union of all involved columns' origin tables' timestamps. Therefore, the number of rows in the result set may vary when different queries select different columns.
4. Users can combine any columns from multiple tables; unselected columns are excluded.

Example:

Given tables t1, t2, t3 with data:

Create a virtual table v1:

CREATE VTABLE v1 (
    ts timestamp,
    c1 int FROM t1.value,
    c2 int FROM t2.value,
    c3 int FROM t3.value1,
    c4 int FROM t3.value2);

Querying all columns:

SELECT * FROM v1;

Result:

Partial column query:

SELECT c1, c2 FROM v1;

Result:

Since the original tables t1 and t2 (corresponding to columns c1 and c2) lack the timestamp 0:00:03, this timestamp will not appear in the final result.

Modify Virtual Basic Tables

ALTER VTABLE [db_name.]vtb_name alter_table_clause

alter_table_clause: {
  ADD COLUMN vtb_col_name vtb_column_type [FROM table_name.col_name]
  | DROP COLUMN vtb_col_name
  | ALTER COLUMN vtb_col_name SET {table_name.col_name | NULL }
  | MODIFY COLUMN col_name column_type
  | RENAME COLUMN old_col_name new_col_name
}

Add Column

ALTER VTABLE vtb_name ADD COLUMN vtb_col_name vtb_col_type [FROM [db_name].table_name.col_name]

Drop Column

ALTER VTABLE vtb_name DROP COLUMN vtb_col_name

Modify Column Width

ALTER VTABLE vtb_name MODIFY COLUMN vtb_col_name data_type(length);

Rename Column

ALTER VTABLE vtb_name RENAME COLUMN old_col_name new_col_name

Change Column Source

ALTER VTABLE vtb_name ALTER COLUMN vtb_col_name SET {[db_name.]table_name.col_name | NULL}

Modify Virtual Subtables

ALTER VTABLE [db_name.]vtb_name alter_table_clause

alter_table_clause: {
  ALTER COLUMN vtb_col_name SET table_name.col_name
  | SET TAG tag_name = new_tag_value
}

Modify Subtable Tag Value

ALTER VTABLE tb_name SET TAG tag_name1=new_tag_value1, tag_name2=new_tag_value2 ...;

Change Column Source

ALTER VTABLE vtb_name ALTER COLUMN vtb_col_name SET {[db_name.]table_name.col_name | NULL}

Drop Virtual Tables

DROP VTABLE [IF EXISTS] [dbname].vtb_name;

View Virtual Table Information

List Virtual Tables

SHOW [NORMAL | CHILD] [db_name.]VTABLES [LIKE 'pattern'];

Show Creation Statement

SHOW CREATE VTABLE [db_name.]vtable_name;

Describe Structure

DESCRIBE [db_name.]vtb_name;

Query All Virtual Tables' Information

SELECT ... FROM information_schema.ins_tables WHERE type = 'VIRTUAL_NORMAL_TABLE' OR type = 'VIRTUAL_CHILD_TABLE';

Write to Virtual Tables

Writing or deleting data in virtual tables is not supported. Virtual tables are logical views computed from source tables.

Virtual Tables vs. Views

Mutual conversion between virtual tables and views is not supported. For example, you cannot create a view based on a virtual table or create a virtual table from a view.

Permissions

Virtual table permissions are categorized into READ and WRITE. Query operations require READ permission, while operations to delete or modify the virtual table itself require WRITE permission.

Syntax

Grant

GRANT privileges ON [db_name.]vtable_name TO user_name
privileges: { ALL | READ | WRITE }

Revoke

REVOKE privileges ON [db_name.]vtable_name FROM user_name
    privileges: { ALL | READ | WRITE }

Permission Rules

1. The creator of a virtual table and the root user have all permissions by default.
2. Users can grant or revoke read/write permissions for specific virtual tables (including virtual supertables and virtual regular tables) via dbname.vtbname. Direct permission operations on virtual subtables are not supported.
3. Virtual subtables and virtual supertables do not support tag-based authorization (table-level authorization). Virtual subtables inherit permissions from their virtual supertables.
4. Granting and revoking permissions for other users must be performed through GRANT and REVOKE statements, and only the root user can execute these operations.
5. The detailed permission control rules are summarized below:

Use Cases