Manually create a table within the current or specified database.The format is “database name. table name”.
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 ;
Note:
TIME) is not specified, IoTDB automatically adds one. Other columns can be added using the enable_auto_create_schema configuration or session interface commands.FIELD if not specified. TAG and ATTRIBUTE columns must be of type STRING.TTL defaults to the database TTL. You can omit this property or set it to default if the default value is used.<TABLE_NAME>:~!"%, etc."")."a""b" becomes a"b.columnDefinition: Column names share the same characteristics as table names and can include special characters such as ..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 table2 (); CREATE TABLE tableC ( "Site" STRING TAG, "Temperature" int32 FIELD COMMENT 'temperature' ) with (TTL=DEFAULT);
Tables can be created automatically when inserting data via session.
Examples:
try (ITableSession session = new TableSessionBuilder() .nodeUrls(Collections.singletonList("127.0.0.1:6667")) .username("root") .password("root") .build()) { session.executeNonQueryStatement("CREATE DATABASE db1"); session.executeNonQueryStatement("use db1"); // Insert data without manually creating the table List<String> columnNameList = Arrays.asList("region_id", "plant_id", "device_id", "model", "temperature", "humidity"); List<TSDataType> dataTypeList = Arrays.asList( TSDataType.STRING, TSDataType.STRING, TSDataType.STRING, TSDataType.STRING, TSDataType.FLOAT, TSDataType.DOUBLE); List<ColumnCategory> columnTypeList = new ArrayList<>( Arrays.asList( ColumnCategory.TAG, ColumnCategory.TAG, ColumnCategory.TAG, ColumnCategory.ATTRIBUTE, ColumnCategory.FIELD, ColumnCategory.FIELD)); Tablet tablet = new Tablet("table1", columnNameList, dataTypeList, columnTypeList, 100); for (long timestamp = 0; timestamp < 100; timestamp++) { int rowIndex = tablet.getRowSize(); tablet.addTimestamp(rowIndex, timestamp); tablet.addValue("region_id", rowIndex, "1"); tablet.addValue("plant_id", rowIndex, "5"); tablet.addValue("device_id", rowIndex, "3"); tablet.addValue("model", rowIndex, "A"); tablet.addValue("temperature", rowIndex, 37.6F); tablet.addValue("humidity", rowIndex, 111.1); if (tablet.getRowSize() == tablet.getMaxRowNumber()) { session.insert(tablet); tablet.reset(); } } if (tablet.getRowSize() != 0) { session.insert(tablet); tablet.reset(); } }
After the code execution is complete, you can use the following statement to verify that the table has been successfully created, including details about the time column, tag columns, attribute columns, and field columns.
IoTDB> desc table1 +-----------+---------+-----------+-------+ | ColumnName| DataType| Category|Comment| +-----------+---------+-----------+-------+ | time|TIMESTAMP| TIME| null| | region_id| STRING| TAG| null| | plant_id| STRING| TAG| null| | device_id| STRING| TAG| null| | model| STRING| ATTRIBUTE| null| |temperature| FLOAT| FIELD| null| | humidity| DOUBLE| FIELD| null| +-----------+---------+-----------+-------+
Used to view all tables and their properties in the current or a specified database.
Syntax:
SHOW TABLES (DETAILS)? ((FROM | IN) database_name)?
Note:
FROM or IN clause is specified, the command lists all tables in the specified database.FROM nor IN is specified, the command lists all tables in the currently selected database. If no database is selected (USE statement not executed), an error is returned.DETAILS option is used, the command shows the current state of each table:USING: The table is available and operational.PRE_CREATE: The table is in the process of being created or the creation has failed; the table is not available.PRE_DELETE: The table is in the process of being deleted or the deletion has failed; the table will remain permanently unavailable.Examples:
IoTDB> show tables from test_db +---------+-------+-------+ |TableName|TTL(ms)|Comment| +---------+-------+-------+ | test| INF| TEST| +---------+-------+-------+ IoTDB> show tables details from test_db +---------+-------+----------+-------+ |TableName|TTL(ms)| Status|Comment| +---------+-------+----------+-------+ | test| INF| USING| TEST| | turbine| INF|PRE_CREATE| null| | car| 1000|PRE_DELETE| null| +---------+-------+----------+-------+
Used to view column names, data types, categories, and states of a table.
Syntax:
(DESC | DESCRIBE) <TABLE_NAME> (DETAILS)?
Note: If the DETAILS option is specified, detailed state information of the columns is displayed:
USING: The column is in normal use.PRE_DELETE: The column is being deleted or the deletion has failed; it is permanently unavailable.Examples:
IoTDB> desc tableB +----------+---------+-----------+-------+ |ColumnName| DataType| Category|Comment| +----------+---------+-----------+-------+ | time|TIMESTAMP| TIME| null| | a| STRING| TAG| a| | b| STRING| ATTRIBUTE| b| | c| INT32| FIELD| c| +----------+---------+-----------+-------+ IoTDB> desc tableB details +----------+---------+-----------+----------+-------+ |ColumnName| DataType| Category| Status|Comment| +----------+---------+-----------+----------+-------+ | time|TIMESTAMP| TIME| USING| null| | a| STRING| TAG| USING| a| | b| STRING| ATTRIBUTE| USING| b| | c| INT32| FIELD| USING| c| | d| INT32| FIELD|PRE_DELETE| d| +----------+---------+-----------+----------+-------+
Used to update a table, including adding or deleting columns and configuring table properties.
Syntax:
ALTER TABLE (IF EXISTS)? tableName=qualifiedName ADD COLUMN (IF NOT EXISTS)? column=columnDefinition #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'
Note:：
SET PROPERTIES operation currently only supports configuring the TTL property of a tableExample:
ALTER TABLE tableB ADD COLUMN IF NOT EXISTS a TAG ALTER TABLE tableB set properties TTL=3600 COMMENT ON TABLE table1 IS 'table1' COMMENT ON COLUMN table1.a IS null
Used to delete a table.
Syntax:
DROP TABLE (IF EXISTS)? <TABLE_NAME>
Examples:
DROP TABLE tableA DROP TABLE test.tableB