Schema语句

  • 设置存储组
  1. SET STORAGE GROUP TO <PrefixPath>
  2. Eg: IoTDB > SET STORAGE GROUP TO root.ln.wf01.wt01
  3. Note: PrefixPath can not include `*`
  • 创建时间序列语句
  1. CREATE TIMESERIES <Timeseries> WITH <AttributeClauses>
  2. AttributeClauses : DATATYPE=<DataTypeValue> COMMA ENCODING=<EncodingValue> [COMMA <ExtraAttributeClause>]*
  3. DataTypeValue: BOOLEAN | DOUBLE | FLOAT | INT32 | INT64 | TEXT
  4. EncodingValue: GORILLA | PLAIN | RLE | TS_2DIFF | REGULAR
  5. ExtraAttributeClause: {
  6.     COMPRESSOR = <CompressorValue>
  7.     MAX_POINT_NUMBER = Integer
  8. }
  9. CompressorValue: UNCOMPRESSED | SNAPPY
  10. Eg: IoTDB > CREATE TIMESERIES root.ln.wf01.wt01.status WITH DATATYPE=BOOLEAN, ENCODING=PLAIN
  11. Eg: IoTDB > CREATE TIMESERIES root.ln.wf01.wt01.temperature WITH DATATYPE=FLOAT, ENCODING=RLE
  12. Eg: IoTDB > CREATE TIMESERIES root.ln.wf01.wt01.temperature WITH DATATYPE=FLOAT, ENCODING=RLE, COMPRESSOR=SNAPPY, MAX_POINT_NUMBER=3
  13. Note: Datatype and encoding type must be corresponding. Please check Chapter 3 Encoding Section for details.
  • 删除时间序列语句
  1. DELETE TIMESERIES <PrefixPath> [COMMA <PrefixPath>]*
  2. Eg: IoTDB > DELETE TIMESERIES root.ln.wf01.wt01.status
  3. Eg: IoTDB > DELETE TIMESERIES root.ln.wf01.wt01.status, root.ln.wf01.wt01.temperature
  4. Eg: IoTDB > DELETE TIMESERIES root.ln.wf01.wt01.*
  • 显示所有时间序列语句
  1. SHOW TIMESERIES
  2. Eg: IoTDB > SHOW TIMESERIES
  3. Note: This statement can only be used in IoTDB Client. If you need to show all timeseries in JDBC, please use `DataBaseMetadata` interface.
  • 显示特定时间序列语句
  1. SHOW TIMESERIES <Path>
  2. Eg: IoTDB > SHOW TIMESERIES root
  3. Eg: IoTDB > SHOW TIMESERIES root.ln
  4. Eg: IoTDB > SHOW TIMESERIES root.ln.*.*.status
  5. Eg: IoTDB > SHOW TIMESERIES root.ln.wf01.wt01.status
  6. Note: The path can be prefix path, star path or timeseries path
  7. Note: This statement can be used in IoTDB Client and JDBC.
  • 显示存储组语句
  1. SHOW STORAGE GROUP
  2. Eg: IoTDB > SHOW STORAGE GROUP
  3. Note: This statement can be used in IoTDB Client and JDBC.

数据管理语句

  • 插入记录语句
  1. INSERT INTO <PrefixPath> LPAREN TIMESTAMP COMMA <Sensor> [COMMA <Sensor>]* RPAREN VALUES LPAREN <TimeValue>, <PointValue> [COMMA <PointValue>]* RPAREN
  2. Sensor : Identifier
  3. Eg: IoTDB > INSERT INTO root.ln.wf01.wt01(timestamp,status) values(1509465600000,true)
  4. Eg: IoTDB > INSERT INTO root.ln.wf01.wt01(timestamp,status) VALUES(NOW(), false)
  5. Eg: IoTDB > INSERT INTO root.ln.wf01.wt01(timestamp,temperature) VALUES(2017-11-01T00:17:00.000+08:00,24.22028)
  6. Eg: IoTDB > INSERT INTO root.ln.wf01.wt01(timestamp, status, temperature) VALUES (1509466680000, false, 20.060787);
  7. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  8. Note: The order of Sensor and PointValue need one-to-one correspondence
  • 更新记录语句
  1. UPDATE <UpdateClause> SET <SetClause> WHERE <WhereClause>
  2. UpdateClause: <prefixPath>
  3. SetClause: <SetExpression> 
  4. SetExpression: <Path> EQUAL <PointValue>
  5. WhereClause : <Condition> [(AND | OR) <Condition>]*
  6. Condition  : <Expression> [(AND | OR) <Expression>]*
  7. Expression : [NOT | !]? TIME PrecedenceEqualOperator <TimeValue>
  8. Eg: IoTDB > UPDATE root.ln.wf01.wt01 SET temperature = 23 WHERE time < NOW() and time > 2017-11-1T00:15:00+08:00
  9. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  • 删除记录语句
  1. DELETE FROM <PrefixPath> [COMMA <PrefixPath>]* WHERE TIME LESSTHAN <TimeValue>
  2. Eg: DELETE FROM root.ln.wf01.wt01.temperature WHERE time < 2017-11-1T00:05:00+08:00
  3. Eg: DELETE FROM root.ln.wf01.wt01.status, root.ln.wf01.wt01.temperature WHERE time < NOW()
  4. Eg: DELETE FROM root.ln.wf01.wt01.* WHERE time < 1509466140000
  • 选择记录语句
  1. SELECT <SelectClause> FROM <FromClause> [WHERE <WhereClause>]?
  2. SelectClause : <SelectPath> (COMMA <SelectPath>)*
  3. SelectPath : <FUNCTION> LPAREN <Path> RPAREN | <Path>
  4. FUNCTION : ‘COUNT’ , ‘MIN_TIME’, ‘MAX_TIME’, ‘MIN_VALUE’, ‘MAX_VALUE’
  5. FromClause : <PrefixPath> (COMMA <PrefixPath>)?
  6. WhereClause : <Condition> [(AND | OR) <Condition>]*
  7. Condition  : <Expression> [(AND | OR) <Expression>]*
  8. Expression : [NOT | !]? <TimeExpr> | [NOT | !]? <SensorExpr>
  9. TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
  10. SensorExpr : (<Timeseries> | <Path>) PrecedenceEqualOperator <PointValue>
  11. Eg: IoTDB > SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature < 24 and time > 2017-11-1 0:13:00
  12. Eg. IoTDB > SELECT * FROM root
  13. Eg. IoTDB > SELECT COUNT(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 25
  14. Eg. IoTDB > SELECT MIN_TIME(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 25
  15. Eg. IoTDB > SELECT MAX_TIME(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature > 24
  16. Eg. IoTDB > SELECT MIN_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature > 23
  17. Eg. IoTDB > SELECT MAX_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 25
  18. Note: the statement needs to satisfy this constraint: <Path>(SelectClause) + <PrefixPath>(FromClause) = <Timeseries>
  19. Note: If the <SensorExpr>(WhereClause) is started with <Path> and not with ROOT, the statement needs to satisfy this constraint: <PrefixPath>(FromClause) + <Path>(SensorExpr) = <Timeseries>
  20. Note: In Version 0.8.2, if <WhereClause> includes `OR`, time filter can not be used.
  • Group By语句
  1. SELECT <SelectClause> FROM <FromClause> WHERE  <WhereClause> GROUP BY <GroupByClause>
  2. SelectClause : <Function> [COMMA < Function >]*
  3. Function : <AggregationFunction> LPAREN <Path> RPAREN
  4. FromClause : <PrefixPath>
  5. WhereClause : <Condition> [(AND | OR) <Condition>]*
  6. Condition  : <Expression> [(AND | OR) <Expression>]*
  7. Expression : [NOT | !]? <TimeExpr> | [NOT | !]? <SensorExpr>
  8. TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
  9. SensorExpr : (<Timeseries> | <Path>) PrecedenceEqualOperator <PointValue>
  10. GroupByClause : LPAREN <TimeUnit> (COMMA TimeValue)? COMMA <TimeInterval> (COMMA <TimeInterval>)* RPAREN
  11. TimeUnit : Integer <DurationUnit>
  12. DurationUnit : "ms" | "s" | "m" | "h" | "d" | "w"
  13. TimeInterval: LBRACKET <TimeValue> COMMA <TimeValue> RBRACKET
  14. Eg: SELECT COUNT(status), COUNT(temperature) FROM root.ln.wf01.wt01 where temperature < 24 GROUP BY(5m, [1509465720000, 1509466380000])
  15. Eg. SELECT COUNT (status), MAX_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE time < 1509466500000 GROUP BY(5m, 1509465660000, [1509465720000, 1509466380000])
  16. Eg. SELECT MIN_TIME(status), MIN_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE temperature < 25 and time < 1509466800000 GROUP BY (3m, 1509465600000, [1509466140000, 1509466380000], [1509466440000, 1509466620000])
  17. Note: the statement needs to satisfy this constraint: <Path>(SelectClause) + <PrefixPath>(FromClause) = <Timeseries>
  18. Note: If the <SensorExpr>(WhereClause) is started with <Path> and not with ROOT, the statement needs to satisfy this constraint: <PrefixPath>(FromClause) + <Path>(SensorExpr) = <Timeseries>
  19. Note: <TimeValue>(TimeInterval) needs to be greater than 0
  20. Note: First <TimeValue>(TimeInterval) in needs to be smaller than second <TimeValue>(TimeInterval)
  • Fill语句
  1. SELECT <SelectClause> FROM <FromClause> WHERE <WhereClause> FILL <FillClause>
  2. SelectClause : <Path> [COMMA <Path>]*
  3. FromClause : < PrefixPath > [COMMA < PrefixPath >]*
  4. WhereClause : <WhereExpression>
  5. WhereExpression : TIME EQUAL <TimeValue>
  6. FillClause : LPAREN <TypeClause> [COMMA <TypeClause>]* RPAREN
  7. TypeClause : <Int32Clause> | <Int64Clause> | <FloatClause> | <DoubleClause> | <BoolClause> | <TextClause>
  8. Int32Clause: INT32 LBRACKET (<LinearClause> | <PreviousClause>)  RBRACKET
  9. Int64Clause: INT64 LBRACKET (<LinearClause> | <PreviousClause>)  RBRACKET
  10. FloatClause: FLOAT LBRACKET (<LinearClause> | <PreviousClause>)  RBRACKET
  11. DoubleClause: DOUBLE LBRACKET (<LinearClause> | <PreviousClause>)  RBRACKET
  12. BoolClause: BOOLEAN LBRACKET (<LinearClause> | <PreviousClause>)  RBRACKET
  13. TextClause: TEXT LBRACKET (<LinearClause> | <PreviousClause>)  RBRACKET
  14. PreviousClause : PREVIOUS [COMMA <ValidPreviousTime>]?
  15. LinearClause : LINEAR [COMMA <ValidPreviousTime> COMMA <ValidBehindTime>]?
  16. ValidPreviousTime, ValidBehindTime: <TimeUnit>
  17. TimeUnit : Integer <DurationUnit>
  18. DurationUnit : "ms" | "s" | "m" | "h" | "d" | "w"
  19. Eg: SELECT temperature FROM root.ln.wf01.wt01 WHERE time = 2017-11-01T16:37:50.000 FILL(float[previous, 1m])
  20. Eg: SELECT temperature,status FROM root.ln.wf01.wt01 WHERE time = 2017-11-01T16:37:50.000 FILL (float[linear, 1m, 1m], boolean[previous, 1m])
  21. Eg: SELECT temperature,status,hardware FROM root.ln.wf01.wt01 WHERE time = 2017-11-01T16:37:50.000 FILL (float[linear, 1m, 1m], boolean[previous, 1m], text[previous])
  22. Eg: SELECT temperature,status,hardware FROM root.ln.wf01.wt01 WHERE time = 2017-11-01T16:37:50.000 FILL (float[linear], boolean[previous, 1m], text[previous])
  23. Note: the statement needs to satisfy this constraint: <PrefixPath>(FromClause) + <Path>(SelectClause) = <Timeseries>
  24. Note: Integer in <TimeUnit> needs to be greater than 0
  • Limit语句
  1. SELECT <SelectClause> FROM <FromClause> [WHERE <WhereClause>] [LIMIT <LIMITClause>] [SLIMIT <SLIMITClause>]
  2. SelectClause : [<Path> | Function]+
  3. Function : <AggregationFunction> LPAREN <Path> RPAREN
  4. FromClause : <Path>
  5. WhereClause : <Condition> [(AND | OR) <Condition>]*
  6. Condition : <Expression> [(AND | OR) <Expression>]*
  7. Expression: [NOT|!]?<TimeExpr> | [NOT|!]?<SensorExpr>
  8. TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
  9. SensorExpr : (<Timeseries>|<Path>) PrecedenceEqualOperator <PointValue>
  10. LIMITClause : <N> [OFFSETClause]?
  11. N : NonNegativeInteger
  12. OFFSETClause : OFFSET <OFFSETValue>
  13. OFFSETValue : NonNegativeInteger
  14. SLIMITClause : <SN> [SOFFSETClause]?
  15. SN : NonNegativeInteger
  16. SOFFSETClause : SOFFSET <SOFFSETValue>
  17. SOFFSETValue : NonNegativeInteger
  18. NonNegativeInteger:= ('+')? Digit+
  19. Eg: IoTDB > SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature < 24 and time > 2017-11-1 0:13:00 LIMIT 3 OFFSET 2
  20. Eg. IoTDB > SELECT COUNT (status), MAX_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE time < 1509466500000 GROUP BY(5m, 1509465660000, [1509465720000, 1509466380000]) LIMIT 3
  21. Note: The order of <LIMITClause> and <SLIMITClause> does not affect the grammatical correctness.
  22. Note: <SLIMITClause> can only effect in Prefixpath and StarPath.
  23. Note: <FillClause> can not use <LIMITClause> but not <SLIMITClause>.

数据库管理语句

  • 创建用户
  1. CREATE USER <userName> <password>;  
  2. userName:=identifier  
  3. password:=identifier
  4. Eg: IoTDB > CREATE USER thulab pwd;
  • 删除用户
  1. DROP USER <userName>;  
  2. userName:=identifier
  3. Eg: IoTDB > DROP USER xiaoming;
  • 创建角色
  1. CREATE ROLE <roleName>;  
  2. roleName:=identifie
  3. Eg: IoTDB > CREATE ROLE admin;
  • 删除角色
  1. DROP ROLE <roleName>;  
  2. roleName:=identifier
  3. Eg: IoTDB > DROP ROLE admin;
  • 赋予用户权限
  1. GRANT USER <userName> PRIVILEGES <privileges> ON <nodeName>;  
  2. userName:=identifier  
  3. nodeName:=identifier (DOT identifier)*  
  4. privileges:= string (COMMA string)*
  5. Eg: IoTDB > GRANT USER tempuser PRIVILEGES 'DELETE_TIMESERIES' on root.ln;
  • 赋予角色权限
  1. GRANT ROLE <roleName> PRIVILEGES <privileges> ON <nodeName>;  
  2. privileges:= string (COMMA string)*  
  3. roleName:=identifier  
  4. nodeName:=identifier (DOT identifier)*
  5. Eg: IoTDB > GRANT ROLE temprole PRIVILEGES 'DELETE_TIMESERIES' ON root.ln;
  • 赋予用户角色
  1. GRANT <roleName> TO <userName>;  
  2. roleName:=identifier  
  3. userName:=identifier
  4. Eg: IoTDB > GRANT temprole TO tempuser;
  • 撤销用户权限
  1. REVOKE USER <userName> PRIVILEGES <privileges> ON <nodeName>;   
  2. privileges:= string (COMMA string)*  
  3. userName:=identifier  
  4. nodeName:=identifier (DOT identifier)*
  5. Eg: IoTDB > REVOKE USER tempuser PRIVILEGES 'DELETE_TIMESERIES' on root.ln;
  • 撤销角色权限
  1. REVOKE ROLE <roleName> PRIVILEGES <privileges> ON <nodeName>;  
  2. privileges:= string (COMMA string)*  
  3. roleName:= identifier  
  4. nodeName:=identifier (DOT identifier)*
  5. Eg: IoTDB > REVOKE ROLE temprole PRIVILEGES 'DELETE_TIMESERIES' ON root.ln;
  • 撤销用户角色
  1. REVOKE <roleName> FROM <userName>;
  2. roleName:=identifier
  3. userName:=identifier
  4. Eg: IoTDB > REVOKE temproleFROM tempuser;
  • 列出用户
  1. LIST USER
  2. Eg: IoTDB > LIST USER
  • 列出角色
  1. LIST ROLE
  2. Eg: IoTDB > LIST ROLE
  • 列出权限
  1. LIST PRIVILEGES USER  <username> ON <path>;    
  2. username:=identifier    
  3. path=‘root’ (DOT identifier)*
  4. Eg: IoTDB > LIST PRIVIEGES USER sgcc_wirte_user ON root.sgcc;
  • 列出角色权限
  1. LIST PRIVILEGES ROLE <roleName> ON <path>;    
  2. roleName:=identifier  
  3. path=‘root’ (DOT identifier)*
  4. Eg: IoTDB > LIST PRIVIEGES ROLE wirte_role ON root.sgcc;
  • 列出用户权限
  1. LIST USER PRIVILEGES <username> ;   
  2. username:=identifier  
  3. Eg: IoTDB > LIST USER PRIVIEGES tempuser;
  • 列出角色权限
  1. LIST ROLE PRIVILEGES <roleName>
  2. roleName:=identifier
  3. Eg: IoTDB > LIST ROLE PRIVIEGES actor;
  • 列出用户角色
  1. LIST ALL ROLE OF USER <username> ;  
  2. username:=identifier
  3. Eg: IoTDB > LIST ALL ROLE OF USER tempuser;
  • 列出角色用户
  1. LIST ALL USER OF ROLE <roleName>;
  2. roleName:=identifier
  3. Eg: IoTDB > LIST ALL USER OF ROLE roleuser;
  • 更新密码
  1. UPDATE USER <username> SET PASSWORD <password>;
  2. roleName:=identifier
  3. password:=identifier
  4. Eg: IoTDB > UPDATE USER tempuser SET PASSWORD newpwd;

功能

  • COUNT
  1. SELECT COUNT(Path) (COMMA COUNT(Path))* FROM <FromClause> [WHERE <WhereClause>]?
  2. Eg. SELECT COUNT(status), COUNT(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24
  3. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  • FIRST
  1. SELECT FIRST (Path) (COMMA FIRST (Path))* FROM <FromClause> [WHERE <WhereClause>]?
  2. Eg. SELECT FIRST (status), FIRST (temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24
  3. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  • MAX_TIME
  1. SELECT MAX_TIME (Path) (COMMA MAX_TIME (Path))* FROM <FromClause> [WHERE <WhereClause>]?
  2. Eg. SELECT MAX_TIME(status), MAX_TIME(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24
  3. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  • MAX_VALUE
  1. SELECT MAX_VALUE (Path) (COMMA MAX_VALUE (Path))* FROM <FromClause> [WHERE <WhereClause>]?
  2. Eg. SELECT MAX_VALUE(status), MAX_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24
  3. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  • MEAN
  1. SELECT MEAN (Path) (COMMA MEAN (Path))* FROM <FromClause> [WHERE <WhereClause>]?
  2. Eg. SELECT MEAN (temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24
  3. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  • MIN_TIME
  1. SELECT MIN_TIME (Path) (COMMA MIN_TIME (Path))*FROM <FromClause> [WHERE <WhereClause>]?
  2. Eg. SELECT MIN_TIME(status), MIN_TIME(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24
  3. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  • MIN_VALUE
  1. SELECT MIN_VALUE (Path) (COMMA MIN_VALUE (Path))* FROM <FromClause> [WHERE <WhereClause>]?
  2. Eg. SELECT MIN_VALUE(status),MIN_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24
  3. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
  • NOW
  1. NOW()
  2. Eg. INSERT INTO root.ln.wf01.wt01(timestamp,status) VALUES(NOW(), false) 
  3. Eg. UPDATE root.ln.wf01.wt01 SET temperature = 23 WHERE time < NOW()
  4. Eg. DELETE FROM root.ln.wf01.wt01.status, root.ln.wf01.wt01.temperature WHERE time < NOW()
  5. Eg. SELECT * FROM root WHERE time < NOW()
  6. Eg. SELECT COUNT(temperature) FROM root.ln.wf01.wt01 WHERE time < NOW()
  • SUM
  1. SELECT SUM(Path) (COMMA SUM(Path))* FROM <FromClause> [WHERE <WhereClause>]?
  2. Eg. SELECT SUM(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24
  3. Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>