Metadata Operations

1. Database Management

1.1 Create Database

Syntax:

CREATE DATABASE (IF NOT EXISTS)? <DATABASE_NAME> (WITH properties)?

Detailed syntax reference

Examples:

CREATE DATABASE database1;
CREATE DATABASE IF NOT EXISTS database1;

-- Create database with 1-year TTL;
CREATE DATABASE IF NOT EXISTS database1 with(TTL=31536000000);

1.2 Use Database

Syntax:

USE <DATABASE_NAME>

Examples:

USE database1;

1.3 View Current Database

Syntax:

SHOW CURRENT_DATABASE;

Examples:

SHOW CURRENT_DATABASE;

+---------------+
|CurrentDatabase|
+---------------+
|           null|
+---------------+

USE database1;
SHOW CURRENT_DATABASE;

+---------------+
|CurrentDatabase|
+---------------+
|      database1|
+---------------+

1.4 List All Databases

Syntax:

SHOW DATABASES (DETAILS)?

Detailed syntax reference

Examples:

show databases;

+------------------+-------+-----------------------+---------------------+---------------------+
|          Database|TTL(ms)|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|
+------------------+-------+-----------------------+---------------------+---------------------+
|         database1|    INF|                      1|                    1|            604800000|
|information_schema|    INF|                   null|                 null|                 null|
+------------------+-------+-----------------------+---------------------+---------------------+

show databases details;

+------------------+-------+-----------------------+---------------------+---------------------+--------------------+------------------+
|          Database|TTL(ms)|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|SchemaRegionGroupNum|DataRegionGroupNum|
+------------------+-------+-----------------------+---------------------+---------------------+--------------------+------------------+
|         database1|    INF|                      1|                    1|            604800000|                   1|                 2|
|information_schema|    INF|                   null|                 null|                 null|                null|              null|
+------------------+-------+-----------------------+---------------------+---------------------+--------------------+------------------+

1.5 Modify Database

Syntax:

ALTER DATABASE (IF EXISTS)? database=identifier SET PROPERTIES propertyAssignments

Examples:

ALTER DATABASE database1 SET PROPERTIES TTL=31536000000;

1.6 Drop Database

Syntax:

DROP DATABASE (IF EXISTS)? <DATABASE_NAME>

Examples:

DROP DATABASE IF EXISTS database1;

2. Table Management

2.1 Create Table

Syntax:

createTableStatement
    : CREATE TABLE (IF NOT EXISTS)? qualifiedName
        '(' (columnDefinition (',' columnDefinition)*)? ')'
        charsetDesc?
        comment?
        (WITH properties)?
     ;

charsetDesc
    : DEFAULT? (CHAR SET | CHARSET | CHARACTER SET) EQ? identifierOrString
    ;

columnDefinition
    : identifier columnCategory=(TAG | ATTRIBUTE | TIME) charsetName? comment?
    | identifier type (columnCategory=(TAG | ATTRIBUTE | TIME | FIELD))? charsetName? comment?
    ;

charsetName
    : CHAR SET identifier
    | CHARSET identifier
    | CHARACTER SET identifier
    ;

comment
    : COMMENT string
    ;

Detailed syntax reference

Examples:

CREATE TABLE table1 (
  time TIMESTAMP TIME,
  region STRING TAG,
  plant_id STRING TAG,
  device_id STRING TAG,
  model_id STRING ATTRIBUTE,
  maintenance STRING ATTRIBUTE COMMENT 'maintenance',
  temperature FLOAT FIELD COMMENT 'temperature',
  humidity FLOAT FIELD COMMENT 'humidity',
  status BOOLEAN FIELD COMMENT 'status',
  arrival_time TIMESTAMP FIELD COMMENT 'arrival_time'
) COMMENT 'table1' WITH (TTL=31536000000);

CREATE TABLE if not exists tableB ();

CREATE TABLE tableC (
  "Site" STRING TAG,
  "Temperature" int32 FIELD COMMENT 'temperature'
 ) with (TTL=DEFAULT);
 
 -- Custom time column: named time_test, located in the second column of the table. (Support from V2.0.8.2)
 CREATE TABLE table1 (
     region STRING TAG, 
     time_user_defined TIMESTAMP TIME, 
     temperature FLOAT FIELD
 );

Note: If your terminal does not support multi-line paste (e.g., Windows CMD), please reformat the SQL statement into a single line before execution.

2.2 List Tables

Syntax:

SHOW TABLES (DETAILS)? ((FROM | IN) database_name)?

Examples:

show tables from database1;

+---------+---------------+
|TableName|        TTL(ms)|
+---------+---------------+
|   table1|    31536000000|
+---------+---------------+

show tables details from database1;

+---------------+-----------+------+-------+
|      TableName|    TTL(ms)|Status|Comment|
+---------------+-----------+------+-------+
|         table1|31536000000| USING| table1|
+---------------+-----------+------+-------+

2.3 Describe Table Columns

Syntax:

(DESC | DESCRIBE) <TABLE_NAME> (DETAILS)?

Examples:

desc table1;

+------------+---------+---------+
|  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|
+------------+---------+---------+

desc table1 details;

+------------+---------+---------+------+------------+
|  ColumnName| DataType| Category|Status|     Comment|
+------------+---------+---------+------+------------+
|        time|TIMESTAMP|     TIME| USING|        null|
|      region|   STRING|      TAG| USING|        null|
|    plant_id|   STRING|      TAG| USING|        null|
|   device_id|   STRING|      TAG| USING|        null|
|    model_id|   STRING|ATTRIBUTE| USING|        null|
| maintenance|   STRING|ATTRIBUTE| USING| maintenance|
| temperature|    FLOAT|    FIELD| USING| temperature|
|    humidity|    FLOAT|    FIELD| USING|    humidity|
|      status|  BOOLEAN|    FIELD| USING|      status|
|arrival_time|TIMESTAMP|    FIELD| USING|arrival_time|
+------------+---------+---------+------+------------+

2.4 View Table Creation Statement

Syntax:

SHOW CREATE TABLE <TABLE_NAME>

Examples:

show create table table1;

+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table|                                                                                                                                                                                                                                                                     Create Table|
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|table1|CREATE TABLE "table1" ("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) WITH (ttl=31536000000)|
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Total line number = 1

2.5 Modify Table

Syntax:

#addColumn;
ALTER TABLE (IF EXISTS)? tableName=qualifiedName ADD COLUMN (IF NOT EXISTS)? column=columnDefinition COMMENT 'column_comment';               
#dropColumn;
| ALTER TABLE (IF EXISTS)? tableName=qualifiedName DROP COLUMN (IF EXISTS)? column=identifier;                    
#setTableProperties;
// set TTL can use this;
| ALTER TABLE (IF EXISTS)? tableName=qualifiedName SET PROPERTIES propertyAssignments;                
| COMMENT ON TABLE tableName=qualifiedName IS 'table_comment';
| COMMENT ON COLUMN tableName.column IS 'column_comment';
#changeColumndatatype;
| ALTER TABLE (IF EXISTS)? tableName=qualifiedName ALTER COLUMN (IF EXISTS)? column=identifier SET DATA TYPE new_type=type;

Examples:

ALTER TABLE table1 ADD COLUMN IF NOT EXISTS a TAG COMMENT 'a';
ALTER TABLE table1 ADD COLUMN IF NOT EXISTS b FLOAT FIELD COMMENT 'b';
ALTER TABLE table1 set properties TTL=3600;
COMMENT ON TABLE table1 IS 'table1';
COMMENT ON COLUMN table1.a IS null;
ALTER TABLE table1 ALTER COLUMN IF EXISTS b SET DATA TYPE DOUBLE;

2.6 Drop Table

Syntax:

DROP TABLE (IF EXISTS)? <TABLE_NAME>

Examples:

DROP TABLE table1;
DROP TABLE database1.table1;