元数据操作

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

示例：

IoTDB> SHOW CURRENT_DATABASE;
+---------------+
|CurrentDatabase|
+---------------+
|           null|
+---------------+

IoTDB> USE database1;

IoTDB> SHOW CURRENT_DATABASE;
+---------------+
|CurrentDatabase|
+---------------+
|      database1|
+---------------+

1.4 查看所有数据库

语法：

SHOW DATABASES (DETAILS)?

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

示例:

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

IoTDB> 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);

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

2.2 查看表

语法：

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

示例:

IoTDB> show tables from database1
+---------+---------------+
|TableName|        TTL(ms)|
+---------+---------------+
|   table1|    31536000000|
+---------+---------------+

IoTDB> show tables details from database1
+---------------+-----------+------+-------+
|      TableName|    TTL(ms)|Status|Comment|
+---------------+-----------+------+-------+
|         table1|31536000000| USING| table1|
+---------------+-----------+------+-------+

2.3 查看表的列

语法：

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

示例:

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

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

语法：

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

示例:

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

2.5 删除表

语法：

DROP TABLE (IF EXISTS)? <TABLE_NAME>

示例:

DROP TABLE table1
DROP TABLE database1.table1