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
     | [db_name.]table_name.tag_name
     | FROM [db_name.]table_name.tag_name
     | tag_name FROM [db_name.]table_name.tag_name

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 or virtual basic tables, FROM columns can come from regular tables, subtables, or existing virtual tables. Supertables and views are not supported as direct sources, and tables with composite primary keys are not supported.
13. In TAGS (...) for a virtual subtable, each tag can be a literal value or a tag-ref. Supported tag-ref forms are table.tag, FROM table.tag, and tag_name FROM table.tag. Use db_name.table.tag for cross-database references.
14. A tag-ref must point to a tag column, not a data column, and the source tag type must match the target virtual tag type.

Currently Supported Reference Patterns

• Virtual subtables support tag-ref, and queries resolve tag values dynamically from the current source-tag values.
• Virtual-table columns can directly reference columns from existing virtual tables, so direct VChild → VChild chains are supported.
• You can build layered virtual-child chains across virtual supertables, including same-database and cross-database cases.
• Tag-ref and col-ref can be mixed across multiple hops. The current reference-depth limit is 32; validation or query execution returns 0x8000620C when the chain exceeds that limit.

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:

t1			t2			t3
ts	value		ts	value		ts	value1	value2
0:00:01	1
			0:00:02	20
						0:00:03	300	3000
0:00:04	4		0:00:04	40
			0:00:05	50		0:00:05	500	5000

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:

v1
ts	c1	c2	c3	c4
0:00:01	1
0:00:02		20
0:00:03			300	3000
0:00:04	4	40
0:00:05		50	500	5000

Partial column query:

SELECT c1, c2 FROM v1;

Result:

v1
ts	c1	c2
0:00:01	1
0:00:02		20
0:00:04	4	40
0:00:05		50

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 | [db_name.]table_name.tag_name}
}

Modify Subtable Tag Value

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

SET TAG can assign either a literal value or a tag-ref to a tag.

Set a Tag to a Literal

ALTER VTABLE v0 SET TAG local_tag='local0_updated';

When a tag is set to a literal value, any existing tag-ref on that tag is cleared. The tag then becomes a static value that no longer tracks the source.

Set a Tag to a tag-ref (create or repoint a reference)

-- Same database: reference the city tag of table src0
ALTER VTABLE v0 SET TAG ref_city=src0.city;

-- Cross database: use the db_name.table.tag three-part form
ALTER VTABLE v0 SET TAG ref_city=db1.src1.city;

After a tag is set to a tag-ref, queries resolve it to the referenced tag's current value at query time. This operation can both add a reference to a tag that was previously a literal and repoint an existing tag-ref to a different source tag. The constraints match those for tag-refs at CREATE VTABLE time:

• The referenced object must be a tag column (of a child table or virtual child table), not a data column.
• The source tag and target tag must have the same data type.
• Reference cycles are not allowed (for example, pointing a tag of v_a at a virtual table that ultimately references v_a again); this is validated and rejected.
• The total reference chain depth must not exceed 32 hops; exceeding it returns error code 0x8000620C.

Repointing within a multi-hop reference chain

When a virtual subtable's tag is one link of a multi-hop chain (for example, v2_0.l2_ref_city -> v0.ref_city -> src0.city), you can adjust the reference at any level with SET TAG, and the change propagates at query time following dynamic-binding rules:

• Repointing an intermediate link (e.g. ALTER VTABLE v0 SET TAG ref_city=src1.city) changes the result of upper-layer virtual tables that reference it.
• Repointing the top link (e.g. ALTER VTABLE v2_0 SET TAG l2_ref_city=db.v1.ref_city) can redirect it to a different chain, or even point it directly at a physical tag to "flatten" the chain.
• Setting an intermediate tag to a literal clears that link's reference, severing the upper layer's propagation from the original physical source; the upper layer then resolves to that literal value.

Note: The batch form ALTER VTABLE USING stb_name SET TAG ... WHERE ... (modifying tags through the virtual super table) accepts literal values only and does not support setting a tag to a tag-ref. To set or repoint a tag-ref, use the single-subtable ALTER VTABLE vtb_name SET TAG ... syntax shown above.

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'];

Use SHOW VTABLES to list the virtual basic tables and virtual subtables covered here. SHOW TABLES does not return these virtual tables.

Show Creation Statement

SHOW CREATE VTABLE [db_name.]vtable_name;

Displays the creation statement for the specified virtual table. For virtual subtables created with tag-ref, the returned statement preserves the tag-ref definition.

Describe Structure

DESCRIBE [db_name.]vtb_name;

DESCRIBE shows the virtual table's columns and tags. For tag-ref or col-ref entries, the result also shows the reference source.

Show Current Tag Values of a Virtual Child Table

SHOW TAGS FROM child_table_name [FROM db_name];
SHOW TAGS FROM [db_name.]child_table_name;

For tag-ref virtual subtables, SHOW TAGS returns the currently resolved tag values.

Validate Virtual-Table References

SHOW VTABLE VALIDATE FOR [db_name.]vtb_name;

SHOW VTABLE VALIDATE checks column/tag references for a virtual basic table or virtual child table and returns the same validation metadata as information_schema.ins_virtual_tables_referencing, including err_code and err_msg.

Query Virtual Basic Tables and Virtual Subtables

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

SELECT ... FROM information_schema.ins_virtual_tables_referencing;

Use ins_virtual_tables_referencing to inspect source database, source table, source column, and validation status for virtual-table columns and tags.

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

Property	Virtual Table	View
Definition	Dynamic structure combining multiple tables by timestamp.	Saved SQL query definition.
Data Source	Multiple tables with timestamp alignment.	Single/multiple table query results.
Storage	No physical storage; dynamic generation.	No storage; query logic only.
Timestamp Handling	Aligns timestamps across tables.	Follows query logic.
Update Mechanism	Real-time reflection of source changes.	Depends on query execution.
Special Features	Supports NULL filling and interpolation (prev/next/linear).	No built-in interpolation.
Use Case	Time series alignment, cross-table analysis.	Simplify complex queries, access control.
Performance	Potentially higher complexity.	Similar to underlying queries.

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:

No.	Operation	Permission Requirements
1	CREATE VTABLE	The user has WRITE permission on the database to which the virtual table belongs, and the user has READ permission on the source tables or source virtual tables referenced by the virtual table.
2	DROP/ALTER VTABLE	The user has WRITE permission on the virtual table. If specifying a source for either a column reference or a tag reference, the user must also have READ permission on the referenced source table or source virtual table.
3	SHOW VTABLES	None
4	SHOW CREATE VTABLE	None
5	DESCRIBE VTABLE	None
6	Query System Tables	None
7	SELECT FROM VTABLE	The user has READ permission on the virtual table.
8	GRANT/REVOKE	Only the root user has permission.

Use Cases

SQL Query	SQL Write	STMT Query	STMT Write
Supported	Not Supported	Not Supported	Not Supported