In this part, we will introduce you IoTDB's Query Language. IoTDB offers you a SQL-like query language for interacting with IoTDB, the query language can be devided into 4 major parts:
All of these statements are write in IoTDB's own syntax, for details about the syntax composition, please check the Reference
section.
SET STORAGE GROUP TO <PrefixPath> Eg: IoTDB > SET STORAGE GROUP TO root.ln.wf01.wt01 Note: PrefixPath can not include `*`
CREATE TIMESERIES <Timeseries> WITH <AttributeClauses> AttributeClauses : DATATYPE=<DataTypeValue> COMMA ENCODING=<EncodingValue> [COMMA <ExtraAttributeClause>]* DataTypeValue: BOOLEAN | DOUBLE | FLOAT | INT32 | INT64 | TEXT EncodingValue: GORILLA | PLAIN | RLE | TS_2DIFF | REGULAR ExtraAttributeClause: { COMPRESSOR = <CompressorValue> MAX_POINT_NUMBER = Integer } CompressorValue: UNCOMPRESSED | SNAPPY Eg: IoTDB > CREATE TIMESERIES root.ln.wf01.wt01.status WITH DATATYPE=BOOLEAN, ENCODING=PLAIN Eg: IoTDB > CREATE TIMESERIES root.ln.wf01.wt01.temperature WITH DATATYPE=FLOAT, ENCODING=RLE Eg: IoTDB > CREATE TIMESERIES root.ln.wf01.wt01.temperature WITH DATATYPE=FLOAT, ENCODING=RLE, COMPRESSOR=SNAPPY, MAX_POINT_NUMBER=3 Note: Datatype and encoding type must be corresponding. Please check Chapter 3 Encoding Section for details.
DELETE TIMESERIES <PrefixPath> [COMMA <PrefixPath>]* Eg: IoTDB > DELETE TIMESERIES root.ln.wf01.wt01.status Eg: IoTDB > DELETE TIMESERIES root.ln.wf01.wt01.status, root.ln.wf01.wt01.temperature Eg: IoTDB > DELETE TIMESERIES root.ln.wf01.wt01.*
SHOW TIMESERIES Eg: IoTDB > SHOW TIMESERIES Note: This statement can only be used in IoTDB Client. If you need to show all timeseries in JDBC, please use `DataBaseMetadata` interface.
SHOW TIMESERIES <Path> Eg: IoTDB > SHOW TIMESERIES root Eg: IoTDB > SHOW TIMESERIES root.ln Eg: IoTDB > SHOW TIMESERIES root.ln.*.*.status Eg: IoTDB > SHOW TIMESERIES root.ln.wf01.wt01.status Note: The path can be prefix path, star path or timeseries path Note: This statement can be used in IoTDB Client and JDBC.
SHOW STORAGE GROUP Eg: IoTDB > SHOW STORAGE GROUP Note: This statement can be used in IoTDB Client and JDBC.
INSERT INTO <PrefixPath> LPAREN TIMESTAMP COMMA <Sensor> [COMMA <Sensor>]* RPAREN VALUES LPAREN <TimeValue>, <PointValue> [COMMA <PointValue>]* RPAREN Sensor : Identifier Eg: IoTDB > INSERT INTO root.ln.wf01.wt01(timestamp,status) values(1509465600000,true) Eg: IoTDB > INSERT INTO root.ln.wf01.wt01(timestamp,status) VALUES(NOW(), false) Eg: IoTDB > INSERT INTO root.ln.wf01.wt01(timestamp,temperature) VALUES(2017-11-01T00:17:00.000+08:00,24.22028) Eg: IoTDB > INSERT INTO root.ln.wf01.wt01(timestamp, status, temperature) VALUES (1509466680000, false, 20.060787); Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries> Note: The order of Sensor and PointValue need one-to-one correspondence
UPDATE <UpdateClause> SET <SetClause> WHERE <WhereClause> UpdateClause: <prefixPath> SetClause: <SetExpression> SetExpression: <Path> EQUAL <PointValue> WhereClause : <Condition> [(AND | OR) <Condition>]* Condition : <Expression> [(AND | OR) <Expression>]* Expression : [NOT | !]? TIME PrecedenceEqualOperator <TimeValue> Eg: IoTDB > UPDATE root.ln.wf01.wt01 SET temperature = 23 WHERE time < NOW() and time > 2017-11-1T00:15:00+08:00 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
DELETE FROM <PrefixPath> [COMMA <PrefixPath>]* WHERE TIME LESSTHAN <TimeValue> Eg: DELETE FROM root.ln.wf01.wt01.temperature WHERE time < 2017-11-1T00:05:00+08:00 Eg: DELETE FROM root.ln.wf01.wt01.status, root.ln.wf01.wt01.temperature WHERE time < NOW() Eg: DELETE FROM root.ln.wf01.wt01.* WHERE time < 1509466140000
SELECT <SelectClause> FROM <FromClause> [WHERE <WhereClause>]? SelectClause : <SelectPath> (COMMA <SelectPath>)* SelectPath : <FUNCTION> LPAREN <Path> RPAREN | <Path> FUNCTION : ‘COUNT’ , ‘MIN_TIME’, ‘MAX_TIME’, ‘MIN_VALUE’, ‘MAX_VALUE’ FromClause : <PrefixPath> (COMMA <PrefixPath>)? WhereClause : <Condition> [(AND | OR) <Condition>]* Condition : <Expression> [(AND | OR) <Expression>]* Expression : [NOT | !]? <TimeExpr> | [NOT | !]? <SensorExpr> TimeExpr : TIME PrecedenceEqualOperator <TimeValue> SensorExpr : (<Timeseries> | <Path>) PrecedenceEqualOperator <PointValue> Eg: IoTDB > SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature < 24 and time > 2017-11-1 0:13:00 Eg. IoTDB > SELECT * FROM root Eg. IoTDB > SELECT COUNT(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 25 Eg. IoTDB > SELECT MIN_TIME(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 25 Eg. IoTDB > SELECT MAX_TIME(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature > 24 Eg. IoTDB > SELECT MIN_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature > 23 Eg. IoTDB > SELECT MAX_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 25 Note: the statement needs to satisfy this constraint: <Path>(SelectClause) + <PrefixPath>(FromClause) = <Timeseries> 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> Note: In Version 0.8.1, if <WhereClause> includes `OR`, time filter can not be used.
SELECT <SelectClause> FROM <FromClause> WHERE <WhereClause> GROUP BY <GroupByClause> SelectClause : <Function> [COMMA < Function >]* Function : <AggregationFunction> LPAREN <Path> RPAREN FromClause : <PrefixPath> WhereClause : <Condition> [(AND | OR) <Condition>]* Condition : <Expression> [(AND | OR) <Expression>]* Expression : [NOT | !]? <TimeExpr> | [NOT | !]? <SensorExpr> TimeExpr : TIME PrecedenceEqualOperator <TimeValue> SensorExpr : (<Timeseries> | <Path>) PrecedenceEqualOperator <PointValue> GroupByClause : LPAREN <TimeUnit> (COMMA TimeValue)? COMMA <TimeInterval> (COMMA <TimeInterval>)* RPAREN TimeUnit : Integer <DurationUnit> DurationUnit : "ms" | "s" | "m" | "h" | "d" | "w" TimeInterval: LBRACKET <TimeValue> COMMA <TimeValue> RBRACKET Eg: SELECT COUNT(status), COUNT(temperature) FROM root.ln.wf01.wt01 where temperature < 24 GROUP BY(5m, [1509465720000, 1509466380000]) Eg. SELECT COUNT (status), MAX_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE time < 1509466500000 GROUP BY(5m, 1509465660000, [1509465720000, 1509466380000]) 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]) Note: the statement needs to satisfy this constraint: <Path>(SelectClause) + <PrefixPath>(FromClause) = <Timeseries> 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> Note: <TimeValue>(TimeInterval) needs to be greater than 0 Note: First <TimeValue>(TimeInterval) in needs to be smaller than second <TimeValue>(TimeInterval)
SELECT <SelectClause> FROM <FromClause> WHERE <WhereClause> FILL <FillClause> SelectClause : <Path> [COMMA <Path>]* FromClause : < PrefixPath > [COMMA < PrefixPath >]* WhereClause : <WhereExpression> WhereExpression : TIME EQUAL <TimeValue> FillClause : LPAREN <TypeClause> [COMMA <TypeClause>]* RPAREN TypeClause : <Int32Clause> | <Int64Clause> | <FloatClause> | <DoubleClause> | <BoolClause> | <TextClause> Int32Clause: INT32 LBRACKET (<LinearClause> | <PreviousClause>) RBRACKET Int64Clause: INT64 LBRACKET (<LinearClause> | <PreviousClause>) RBRACKET FloatClause: FLOAT LBRACKET (<LinearClause> | <PreviousClause>) RBRACKET DoubleClause: DOUBLE LBRACKET (<LinearClause> | <PreviousClause>) RBRACKET BoolClause: BOOLEAN LBRACKET (<LinearClause> | <PreviousClause>) RBRACKET TextClause: TEXT LBRACKET (<LinearClause> | <PreviousClause>) RBRACKET PreviousClause : PREVIOUS [COMMA <ValidPreviousTime>]? LinearClause : LINEAR [COMMA <ValidPreviousTime> COMMA <ValidBehindTime>]? ValidPreviousTime, ValidBehindTime: <TimeUnit> TimeUnit : Integer <DurationUnit> DurationUnit : "ms" | "s" | "m" | "h" | "d" | "w" Eg: SELECT temperature FROM root.ln.wf01.wt01 WHERE time = 2017-11-01T16:37:50.000 FILL(float[previous, 1m]) 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]) 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]) 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]) Note: the statement needs to satisfy this constraint: <PrefixPath>(FromClause) + <Path>(SelectClause) = <Timeseries> Note: Integer in <TimeUnit> needs to be greater than 0
SELECT <SelectClause> FROM <FromClause> [WHERE <WhereClause>] [LIMIT <LIMITClause>] [SLIMIT <SLIMITClause>] SelectClause : [<Path> | Function]+ Function : <AggregationFunction> LPAREN <Path> RPAREN FromClause : <Path> WhereClause : <Condition> [(AND | OR) <Condition>]* Condition : <Expression> [(AND | OR) <Expression>]* Expression: [NOT|!]?<TimeExpr> | [NOT|!]?<SensorExpr> TimeExpr : TIME PrecedenceEqualOperator <TimeValue> SensorExpr : (<Timeseries>|<Path>) PrecedenceEqualOperator <PointValue> LIMITClause : <N> [OFFSETClause]? N : NonNegativeInteger OFFSETClause : OFFSET <OFFSETValue> OFFSETValue : NonNegativeInteger SLIMITClause : <SN> [SOFFSETClause]? SN : NonNegativeInteger SOFFSETClause : SOFFSET <SOFFSETValue> SOFFSETValue : NonNegativeInteger NonNegativeInteger:= ('+')? Digit+ 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 Eg. IoTDB > SELECT COUNT (status), MAX_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE time < 1509466500000 GROUP BY(5m, 1509465660000, [1509465720000, 1509466380000]) LIMIT 3 Note: The order of <LIMITClause> and <SLIMITClause> does not affect the grammatical correctness. Note: <SLIMITClause> can only effect in Prefixpath and StarPath. Note: <FillClause> can not use <LIMITClause> but not <SLIMITClause>.
CREATE USER <userName> <password>; userName:=identifier password:=identifier Eg: IoTDB > CREATE USER thulab pwd;
DROP USER <userName>; userName:=identifier Eg: IoTDB > DROP USER xiaoming;
CREATE ROLE <roleName>; roleName:=identifie Eg: IoTDB > CREATE ROLE admin;
DROP ROLE <roleName>; roleName:=identifier Eg: IoTDB > DROP ROLE admin;
GRANT USER <userName> PRIVILEGES <privileges> ON <nodeName>; userName:=identifier nodeName:=identifier (DOT identifier)* privileges:= string (COMMA string)* Eg: IoTDB > GRANT USER tempuser PRIVILEGES 'DELETE_TIMESERIES' on root.ln;
GRANT ROLE <roleName> PRIVILEGES <privileges> ON <nodeName>; privileges:= string (COMMA string)* roleName:=identifier nodeName:=identifier (DOT identifier)* Eg: IoTDB > GRANT ROLE temprole PRIVILEGES 'DELETE_TIMESERIES' ON root.ln;
GRANT <roleName> TO <userName>; roleName:=identifier userName:=identifier Eg: IoTDB > GRANT temprole TO tempuser;
REVOKE USER <userName> PRIVILEGES <privileges> ON <nodeName>; privileges:= string (COMMA string)* userName:=identifier nodeName:=identifier (DOT identifier)* Eg: IoTDB > REVOKE USER tempuser PRIVILEGES 'DELETE_TIMESERIES' on root.ln;
REVOKE ROLE <roleName> PRIVILEGES <privileges> ON <nodeName>; privileges:= string (COMMA string)* roleName:= identifier nodeName:=identifier (DOT identifier)* Eg: IoTDB > REVOKE ROLE temprole PRIVILEGES 'DELETE_TIMESERIES' ON root.ln;
REVOKE <roleName> FROM <userName>; roleName:=identifier userName:=identifier Eg: IoTDB > REVOKE temproleFROM tempuser;
LIST USER Eg: IoTDB > LIST USER
LIST ROLE Eg: IoTDB > LIST ROLE
LIST PRIVILEGES USER <username> ON <path>; username:=identifier path=‘root’ (DOT identifier)* Eg: IoTDB > LIST PRIVIEGES USER sgcc_wirte_user ON root.sgcc;
LIST PRIVILEGES ROLE <roleName> ON <path>; roleName:=identifier path=‘root’ (DOT identifier)* Eg: IoTDB > LIST PRIVIEGES ROLE wirte_role ON root.sgcc;
LIST USER PRIVILEGES <username> ; username:=identifier Eg: IoTDB > LIST USER PRIVIEGES tempuser;
LIST ROLE PRIVILEGES <roleName> roleName:=identifier Eg: IoTDB > LIST ROLE PRIVIEGES actor;
LIST ALL ROLE OF USER <username> ; username:=identifier Eg: IoTDB > LIST ALL ROLE OF USER tempuser;
LIST ALL USER OF ROLE <roleName>; roleName:=identifier Eg: IoTDB > LIST ALL USER OF ROLE roleuser;
UPDATE USER <username> SET PASSWORD <password>; roleName:=identifier password:=identifier Eg: IoTDB > UPDATE USER tempuser SET PASSWORD newpwd;
The COUNT function returns the value number of timeseries(one or more) non-null values selected by the SELECT statement. The result is a signed 64-bit integer. If there are no matching rows, COUNT () returns 0.
SELECT COUNT(Path) (COMMA COUNT(Path))* FROM <FromClause> [WHERE <WhereClause>]? Eg. SELECT COUNT(status), COUNT(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
The FIRST function returns the first point value of the choosen timeseries(one or more).
SELECT FIRST (Path) (COMMA FIRST (Path))* FROM <FromClause> [WHERE <WhereClause>]? Eg. SELECT FIRST (status), FIRST (temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
The MAX_TIME function returns the maximum timestamp of the choosen timeseries(one or more). The result is a signed 64-bit integer, greater than 0.
SELECT MAX_TIME (Path) (COMMA MAX_TIME (Path))* FROM <FromClause> [WHERE <WhereClause>]? Eg. SELECT MAX_TIME(status), MAX_TIME(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
The MAX_VALUE function returns the maximum value(lexicographically ordered) of the choosen timeseries (one or more).
SELECT MAX_VALUE (Path) (COMMA MAX_VALUE (Path))* FROM <FromClause> [WHERE <WhereClause>]? Eg. SELECT MAX_VALUE(status), MAX_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
The MEAN function returns the arithmetic mean value of the choosen timeseries over a specified period of time. The timeseries must be int32, int64, float, double type, and the other types are not to be calculated. The result is a double type number.
SELECT MEAN (Path) (COMMA MEAN (Path))* FROM <FromClause> [WHERE <WhereClause>]? Eg. SELECT MEAN (temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
The MIN_TIME function returns the minimum timestamp of the choosen timeseries(one or more). The result is a signed 64-bit integer, greater than 0.
SELECT MIN_TIME (Path) (COMMA MIN_TIME (Path))*FROM <FromClause> [WHERE <WhereClause>]? Eg. SELECT MIN_TIME(status), MIN_TIME(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
The MIN_VALUE function returns the minimum value(lexicographically ordered) of the choosen timeseries (one or more).
SELECT MIN_VALUE (Path) (COMMA MIN_VALUE (Path))* FROM <FromClause> [WHERE <WhereClause>]? Eg. SELECT MIN_VALUE(status),MIN_VALUE(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>
The NOW function returns the current timestamp. This function can be used in the data operation statement to represent time. The result is a signed 64-bit integer, greater than 0.
NOW() Eg. INSERT INTO root.ln.wf01.wt01(timestamp,status) VALUES(NOW(), false) Eg. UPDATE root.ln.wf01.wt01 SET temperature = 23 WHERE time < NOW() Eg. DELETE FROM root.ln.wf01.wt01.status, root.ln.wf01.wt01.temperature WHERE time < NOW() Eg. SELECT * FROM root WHERE time < NOW() Eg. SELECT COUNT(temperature) FROM root.ln.wf01.wt01 WHERE time < NOW()
The SUM function returns the sum of the choosen timeseries (one or more) over a specified period of time. The timeseries must be int32, int64, float, double type, and the other types are not to be calculated. The result is a double type number.
SELECT SUM(Path) (COMMA SUM(Path))* FROM <FromClause> [WHERE <WhereClause>]? Eg. SELECT SUM(temperature) FROM root.ln.wf01.wt01 WHERE root.ln.wf01.wt01.temperature < 24 Note: the statement needs to satisfy this constraint: <PrefixPath> + <Path> = <Timeseries>