Management Statements
February 18, 2025About 4 min
Management Statements
1. Status Inspection
1.1 View Current Tree/Table Model
Syntax:
showCurrentSqlDialectStatement
: SHOW CURRENT_SQL_DIALECT
;Example:
IoTDB> SHOW CURRENT_SQL_DIALECT
+-----------------+
|CurrentSqlDialect|
+-----------------+
| TABLE|
+-----------------+1.2 View Current User
Syntax:
showCurrentUserStatement
: SHOW CURRENT_USER
;Example:
IoTDB> SHOW CURRENT_USER
+-----------+
|CurrentUser|
+-----------+
| root|
+-----------+1.3 View Connected Database
Syntax:
showCurrentDatabaseStatement
: SHOW CURRENT_DATABASE
;Example:
IoTDB> SHOW CURRENT_DATABASE;
+---------------+
|CurrentDatabase|
+---------------+
| null|
+---------------+
IoTDB> USE test;
IoTDB> SHOW CURRENT_DATABASE;
+---------------+
|CurrentDatabase|
+---------------+
| test|
+---------------+1.4 View Cluster Version
Syntax:
showVersionStatement
: SHOW VERSION
;Example:
IoTDB> SHOW VERSION
+-------+---------+
|Version|BuildInfo|
+-------+---------+
|2.0.1.2| 1ca4008|
+-------+---------+1.5 View Key Cluster Parameters
Syntax:
showVariablesStatement
: SHOW VARIABLES
;Example:
IoTDB> SHOW VARIABLES
+----------------------------------+-----------------------------------------------------------------+
| Variable| Value|
+----------------------------------+-----------------------------------------------------------------+
| ClusterName| defaultCluster|
| DataReplicationFactor| 1|
| SchemaReplicationFactor| 1|
| DataRegionConsensusProtocolClass| org.apache.iotdb.consensus.iot.IoTConsensus|
|SchemaRegionConsensusProtocolClass| org.apache.iotdb.consensus.ratis.RatisConsensus|
| ConfigNodeConsensusProtocolClass| org.apache.iotdb.consensus.ratis.RatisConsensus|
| TimePartitionOrigin| 0|
| TimePartitionInterval| 604800000|
| ReadConsistencyLevel| strong|
| SchemaRegionPerDataNode| 1|
| DataRegionPerDataNode| 0|
| SeriesSlotNum| 1000|
| SeriesSlotExecutorClass|org.apache.iotdb.commons.partition.executor.hash.BKDRHashExecutor|
| DiskSpaceWarningThreshold| 0.05|
| TimestampPrecision| ms|
+----------------------------------+-----------------------------------------------------------------+1.6 View Cluster ID
Syntax:
showClusterIdStatement
: SHOW (CLUSTERID | CLUSTER_ID)
;Example:
IoTDB> SHOW CLUSTER_ID
+------------------------------------+
| ClusterId|
+------------------------------------+
|40163007-9ec1-4455-aa36-8055d740fcda|1.7 View Server Time
Shows time of the DataNode server directly connected to client
Syntax:
showCurrentTimestampStatement
: SHOW CURRENT_TIMESTAMP
;Example:
IoTDB> SHOW CURRENT_TIMESTAMP
+-----------------------------+
| CurrentTimestamp|
+-----------------------------+
|2025-02-17T11:11:52.987+08:00|
+-----------------------------+2. Status Configuration
2.1 Set Connection Tree/Table Model
Syntax:
SET SQL_DIALECT EQ (TABLE | TREE)Example:
IoTDB> SET SQL_DIALECT=TABLE
IoTDB> SHOW CURRENT_SQL_DIALECT
+-----------------+
|CurrentSqlDialect|
+-----------------+
| TABLE|
+-----------------+2.2 Update Configuration Items
Syntax:
setConfigurationStatement
: SET CONFIGURATION propertyAssignments (ON INTEGER_VALUE)?
;
propertyAssignments
: property (',' property)*
;
property
: identifier EQ propertyValue
;
propertyValue
: DEFAULT
| expression
;Example:
IoTDB> SET CONFIGURATION disk_space_warning_threshold='0.05',heartbeat_interval_in_ms='1000' ON 1;2.3 Load Manually Modified Configuration
Syntax:
loadConfigurationStatement
: LOAD CONFIGURATION localOrClusterMode?
;
localOrClusterMode
: (ON (LOCAL | CLUSTER))
;Example:
IoTDB> LOAD CONFIGURATION ON LOCAL;2.4 Set System Status
Syntax:
setSystemStatusStatement
: SET SYSTEM TO (READONLY | RUNNING) localOrClusterMode?
;
localOrClusterMode
: (ON (LOCAL | CLUSTER))
;Example:
IoTDB> SET SYSTEM TO READONLY ON CLUSTER;3. Data Management
3.1 Flush Memory Table to Disk
Syntax:
flushStatement
: FLUSH identifier? (',' identifier)* booleanValue? localOrClusterMode?
;
booleanValue
: TRUE | FALSE
;
localOrClusterMode
: (ON (LOCAL | CLUSTER))
;Example:
IoTDB> FLUSH test_db TRUE ON LOCAL;3.2 Clear DataNode Cache
Syntax:
clearCacheStatement
: CLEAR clearCacheOptions? CACHE localOrClusterMode?
;
clearCacheOptions
: ATTRIBUTE
| QUERY
| ALL
;
localOrClusterMode
: (ON (LOCAL | CLUSTER))
;Example:
IoTDB> CLEAR ALL CACHE ON LOCAL;4. Data Repair
4.1 Start Background TsFile Repair
Syntax:
startRepairDataStatement
: START REPAIR DATA localOrClusterMode?
;
localOrClusterMode
: (ON (LOCAL | CLUSTER))
;Example:
IoTDB> START REPAIR DATA ON CLUSTER;4.2 Pause TsFile Repair
Syntax:
stopRepairDataStatement
: STOP REPAIR DATA localOrClusterMode?
;
localOrClusterMode
: (ON (LOCAL | CLUSTER))
;Example:
IoTDB> STOP REPAIR DATA ON CLUSTER;5. Query Operations
5.1 View Active Queries
Syntax:
showQueriesStatement
: SHOW (QUERIES | QUERY PROCESSLIST)
(WHERE where=booleanExpression)?
(ORDER BY sortItem (',' sortItem)*)?
limitOffsetClause
;Example:
IoTDB> SHOW QUERIES WHERE elapsed_time > 30
+-----------------------+-----------------------------+-----------+------------+------------+----+
| query_id| start_time|datanode_id|elapsed_time| statement|user|
+-----------------------+-----------------------------+-----------+------------+------------+----+
|20250108_101015_00000_1|2025-01-08T18:10:15.935+08:00| 1| 32.283|show queries|root|
+-----------------------+-----------------------------+-----------+------------+------------+----+5.2 Terminate Queries
Syntax:
killQueryStatement
: KILL (QUERY queryId=string | ALL QUERIES)
;Example:
IoTDB> KILL QUERY 20250108_101015_00000_1; -- teminate specific query
IoTDB> KILL ALL QUERIES; -- teminate all query5.3 Query Performance Analysis
5.3.1 View Execution Plan
Syntax:
EXPLAIN <SELECT_STATEMENT>Detailed syntax reference: EXPLAIN STATEMENT
Example:
IoTDB> explain select * from t1
+-----------------------------------------------------------------------------------------------+
| distribution plan|
+-----------------------------------------------------------------------------------------------+
| ┌─────────────────────────────────────────────┐ |
| │OutputNode-4 │ |
| │OutputColumns-[time, device_id, type, speed] │ |
| │OutputSymbols: [time, device_id, type, speed]│ |
| └─────────────────────────────────────────────┘ |
| │ |
| │ |
| ┌─────────────────────────────────────────────┐ |
| │Collect-21 │ |
| │OutputSymbols: [time, device_id, type, speed]│ |
| └─────────────────────────────────────────────┘ |
| ┌───────────────────────┴───────────────────────┐ |
| │ │ |
|┌─────────────────────────────────────────────┐ ┌───────────┐ |
|│TableScan-19 │ │Exchange-28│ |
|│QualifiedTableName: test.t1 │ └───────────┘ |
|│OutputSymbols: [time, device_id, type, speed]│ │ |
|│DeviceNumber: 1 │ │ |
|│ScanOrder: ASC │ ┌─────────────────────────────────────────────┐|
|│PushDownOffset: 0 │ │TableScan-20 │|
|│PushDownLimit: 0 │ │QualifiedTableName: test.t1 │|
|│PushDownLimitToEachDevice: false │ │OutputSymbols: [time, device_id, type, speed]│|
|│RegionId: 2 │ │DeviceNumber: 1 │|
|└─────────────────────────────────────────────┘ │ScanOrder: ASC │|
| │PushDownOffset: 0 │|
| │PushDownLimit: 0 │|
| │PushDownLimitToEachDevice: false │|
| │RegionId: 1 │|
| └─────────────────────────────────────────────┘|
+-----------------------------------------------------------------------------------------------+5.3.2 Analyze Query Performance
Syntax:
EXPLAIN ANALYZE [VERBOSE] <SELECT_STATEMENT>Detailed syntax reference: EXPLAIN ANALYZE STATEMENT
Example:
IoTDB> explain analyze verbose select * from t1
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 38.860 ms |
|Fetch Partition Cost: 9.888 ms |
|Fetch Schema Cost: 54.046 ms |
|Logical Plan Cost: 10.102 ms |
|Logical Optimization Cost: 17.396 ms |
|Distribution Plan Cost: 2.508 ms |
|Dispatch Cost: 22.126 ms |
|Fragment Instances Count: 2 |
| |
|FRAGMENT-INSTANCE[Id: 20241127_090849_00009_1.2.0][IP: 0.0.0.0][DataRegion: 2][State: FINISHED]|
| Total Wall Time: 18 ms |
| Cost of initDataQuerySource: 6.153 ms |
| Seq File(unclosed): 1, Seq File(closed): 0 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 0 |
| ready queued time: 0.164 ms, blocked queued time: 0.342 ms |
| Query Statistics: |
| loadBloomFilterFromCacheCount: 0 |
| loadBloomFilterFromDiskCount: 0 |
| loadBloomFilterActualIOSize: 0 |
| loadBloomFilterTime: 0.000 |
| loadTimeSeriesMetadataAlignedMemSeqCount: 1 |
| loadTimeSeriesMetadataAlignedMemSeqTime: 0.246 |
| loadTimeSeriesMetadataFromCacheCount: 0 |
| loadTimeSeriesMetadataFromDiskCount: 0 |
| loadTimeSeriesMetadataActualIOSize: 0 |
| constructAlignedChunkReadersMemCount: 1 |
| constructAlignedChunkReadersMemTime: 0.294 |
| loadChunkFromCacheCount: 0 |
| loadChunkFromDiskCount: 0 |
| loadChunkActualIOSize: 0 |
| pageReadersDecodeAlignedMemCount: 1 |
| pageReadersDecodeAlignedMemTime: 0.047 |
| [PlanNodeId 43]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 5.523 ms |
| output: 2 rows |
| HasNext() Called Count: 6 |
| Next() Called Count: 5 |
| Estimated Memory Size: : 327680 |
| [PlanNodeId 31]: CollectNode(CollectOperator) |
| CPU Time: 5.512 ms |
| output: 2 rows |
| HasNext() Called Count: 6 |
| Next() Called Count: 5 |
| Estimated Memory Size: : 327680 |
| [PlanNodeId 29]: TableScanNode(TableScanOperator) |
| CPU Time: 5.439 ms |
| output: 1 rows |
| HasNext() Called Count: 3
| Next() Called Count: 2 |
| Estimated Memory Size: : 327680 |
| DeviceNumber: 1 |
| CurrentDeviceIndex: 0 |
| [PlanNodeId 40]: ExchangeNode(ExchangeOperator) |
| CPU Time: 0.053 ms |
| output: 1 rows |
| HasNext() Called Count: 2 |
| Next() Called Count: 1 |
| Estimated Memory Size: : 131072 |
| |
|FRAGMENT-INSTANCE[Id: 20241127_090849_00009_1.3.0][IP: 0.0.0.0][DataRegion: 1][State: FINISHED]|
| Total Wall Time: 13 ms |
| Cost of initDataQuerySource: 5.725 ms |
| Seq File(unclosed): 1, Seq File(closed): 0 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 0 |
| ready queued time: 0.118 ms, blocked queued time: 5.844 ms |
| Query Statistics: |
| loadBloomFilterFromCacheCount: 0 |
| loadBloomFilterFromDiskCount: 0 |
| loadBloomFilterActualIOSize: 0 |
| loadBloomFilterTime: 0.000 |
| loadTimeSeriesMetadataAlignedMemSeqCount: 1 |
| loadTimeSeriesMetadataAlignedMemSeqTime: 0.004 |
| loadTimeSeriesMetadataFromCacheCount: 0 |
| loadTimeSeriesMetadataFromDiskCount: 0 |
| loadTimeSeriesMetadataActualIOSize: 0 |
| constructAlignedChunkReadersMemCount: 1 |
| constructAlignedChunkReadersMemTime: 0.001 |
| loadChunkFromCacheCount: 0 |
| loadChunkFromDiskCount: 0 |
| loadChunkActualIOSize: 0 |
| pageReadersDecodeAlignedMemCount: 1 |
| pageReadersDecodeAlignedMemTime: 0.007 |
| [PlanNodeId 42]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 0.270 ms |
| output: 1 rows |
| HasNext() Called Count: 3 |
| Next() Called Count: 2 |
| Estimated Memory Size: : 327680 |
| [PlanNodeId 30]: TableScanNode(TableScanOperator) |
| CPU Time: 0.250 ms |
| output: 1 rows |
| HasNext() Called Count: 3 |
| Next() Called Count: 2 |
| Estimated Memory Size: : 327680 |
| DeviceNumber: 1 |
| CurrentDeviceIndex: 0 |
+-----------------------------------------------------------------------------------------------+