元数据操作

1. 数据库管理

1.1 创建数据库

语法：

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

更多详细语法说明请参考：创建数据库

示例：

CREATE DATABASE database1;
CREATE DATABASE IF NOT EXISTS database1;

-- 创建一个名为 database1 的数据库，并将数据库的TTL时间设置为1年;
CREATE DATABASE IF NOT EXISTS database1 with(TTL=31536000000);

1.2 使用数据库

语法：

USE <DATABASE_NAME>

示例:

USE database1;

1.3 查看当前数据库

语法：

SHOW CURRENT_DATABASE;

示例：

SHOW CURRENT_DATABASE;

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

USE database1;
SHOW CURRENT_DATABASE;

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

1.4 查看所有数据库

语法：

SHOW DATABASES (DETAILS)?

更多返回结果详细说明请参考：查看所有数据库

示例:

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 修改数据库

语法：

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

示例:

ALTER DATABASE database1 SET PROPERTIES TTL=31536000000;

1.6 删除数据库

语法：

DROP DATABASE (IF EXISTS)? <DATABASE_NAME>;

示例:

DROP DATABASE IF EXISTS database1;

2. 表管理

2.1 创建表

语法：

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
    ;

更多详细语法说明请参考：创建表

示例:

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 (
  station STRING TAG,
  temperature int32 FIELD COMMENT 'temperature'
 ) with (TTL=DEFAULT);
 
-- 自定义时间列:命名为time_test, 位于表的第二列 (V2.0.8.2 起支持）
CREATE TABLE table1 (
 region STRING TAG, 
 time_user_defined TIMESTAMP TIME, 
 temperature FLOAT FIELD
);

注意：若您使用的终端不支持多行粘贴（例如 Windows CMD），请将 SQL 语句调整为单行格式后再执行。

2.2 查看表

语法：

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

示例:

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 查看表的列

语法：

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

示例:

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 查看表的创建信息

语法：

SHOW CREATE TABLE <TABLE_NAME>

示例:

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 修改表

语法：

#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;

示例:

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 (IF EXISTS)? <TABLE_NAME>

示例:

DROP TABLE table1;
DROP TABLE database1.table1;