This is an automated email from the ASF dual-hosted git repository.
qiaojialin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new 4867912 [IOTDB-924] Support insert multi rows (#2956)
4867912 is described below
commit 4867912247574c2161badac38cc21aae23acf60a
Author: Superainbower <[email protected]>
AuthorDate: Mon Apr 26 11:31:18 2021 +0800
[IOTDB-924] Support insert multi rows (#2956)
---
.../antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4 | 4 +
.../DML-Data-Manipulation-Language.md | 12 +-
.../DML-Data-Manipulation-Language.md | 12 +-
.../iotdb/db/qp/logical/crud/InsertOperator.java | 10 +-
.../apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java | 27 +++--
.../iotdb/db/qp/strategy/PhysicalGenerator.java | 25 ++++-
.../db/integration/IoTDBInsertMultiRowIT.java | 125 +++++++++++++++++++++
7 files changed, 192 insertions(+), 23 deletions(-)
diff --git a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4
b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4
index a7b2fc9..c753069 100644
--- a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4
+++ b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4
@@ -382,6 +382,10 @@ measurementName
;
insertValuesSpec
+ :(COMMA? insertMultiValue)*
+ ;
+
+insertMultiValue
: LR_BRACKET dateFormat (COMMA measurementValue)+ RR_BRACKET
| LR_BRACKET INT (COMMA measurementValue)+ RR_BRACKET
;
diff --git
a/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
b/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
index 60e44ac..327cc30 100644
--- a/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
+++ b/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
@@ -49,10 +49,16 @@ The INSERT statement can also support the insertion of
multi-column data at the
IoTDB > insert into root.ln.wf02.wt02(timestamp, status, hardware) VALUES (2,
false, 'v2')
```
+In addition, The INSERT statement support insert multi-rows at once. The
sample code of inserting two rows as follows:
+
+```
+IoTDB > insert into root.ln.wf02.wt02(timestamp, status, hardware) VALUES (3,
false, 'v3'),(4, true, 'v4')
+```
+
After inserting the data, we can simply query the inserted data using the
SELECT statement:
```
-IoTDB > select * from root.ln.wf02 where time < 3
+IoTDB > select * from root.ln.wf02 where time < 5
```
The result is shown below. The query result shows that the insertion
statements of single column and multi column data are performed correctly.
@@ -63,8 +69,10 @@ The result is shown below. The query result shows that the
insertion statements
+-----------------------------+--------------------------+------------------------+
|1970-01-01T08:00:00.001+08:00| v1|
true|
|1970-01-01T08:00:00.002+08:00| v2|
false|
+|1970-01-01T08:00:00.003+08:00| v3|
false|
+|1970-01-01T08:00:00.004+08:00| v4|
true|
+-----------------------------+--------------------------+------------------------+
-Total line number = 2
+Total line number = 4
It costs 0.170s
```
diff --git
a/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
b/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
index c25e204..5563a04 100644
--- a/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
+++ b/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
@@ -50,10 +50,16 @@ INSERT语句还可以支持在同一个时间点下多列数据的插入，同
IoTDB > insert into root.ln.wf02.wt02(timestamp, status, hardware) values (2,
false, 'v2')
```
+此外，INSERT语句支持一次性插入多行数据，同时向2个不同时间点插入上述时间序列的值，示例代码如下：
+
+```
+IoTDB > insert into root.ln.wf02.wt02(timestamp, status, hardware) VALUES (3,
false, 'v3'),(4, true, 'v4')
+```
+
插入数据后我们可以使用SELECT语句简单查询已插入的数据。
```
-IoTDB > select * from root.ln.wf02 where time < 3
+IoTDB > select * from root.ln.wf02 where time < 5
```
结果如图所示。由查询结果可以看出，单列、多列数据的插入操作正确执行。
@@ -64,8 +70,10 @@ IoTDB > select * from root.ln.wf02 where time < 3
+-----------------------------+--------------------------+------------------------+
|1970-01-01T08:00:00.001+08:00| v1|
true|
|1970-01-01T08:00:00.002+08:00| v2|
false|
+|1970-01-01T08:00:00.003+08:00| v3|
false|
+|1970-01-01T08:00:00.004+08:00| v4|
true|
+-----------------------------+--------------------------+------------------------+
-Total line number = 2
+Total line number = 4
It costs 0.170s
```
diff --git
a/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/InsertOperator.java
b/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/InsertOperator.java
index 731001e..bd410c6 100644
---
a/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/InsertOperator.java
+++
b/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/InsertOperator.java
@@ -21,7 +21,7 @@ package org.apache.iotdb.db.qp.logical.crud;
/** this class extends {@code RootOperator} and process insert statement. */
public class InsertOperator extends SFWOperator {
- private long time;
+ private long[] times;
private String[] measurementList;
private String[] valueList;
@@ -46,11 +46,11 @@ public class InsertOperator extends SFWOperator {
this.valueList = insertValue;
}
- public long getTime() {
- return time;
+ public long[] getTimes() {
+ return times;
}
- public void setTime(long time) {
- this.time = time;
+ public void setTimes(long[] times) {
+ this.times = times;
}
}
diff --git
a/server/src/main/java/org/apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java
b/server/src/main/java/org/apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java
index b88a09f..9003016 100644
--- a/server/src/main/java/org/apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java
+++ b/server/src/main/java/org/apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java
@@ -1756,20 +1756,25 @@ public class IoTDBSqlVisitor extends
SqlBaseBaseVisitor<Operator> {
}
private void parseInsertValuesSpec(InsertValuesSpecContext ctx,
InsertOperator insertOp) {
- long timestamp;
- if (ctx.dateFormat() != null) {
- timestamp = parseTimeFormat(ctx.dateFormat().getText());
- } else {
- timestamp = Long.parseLong(ctx.INT().getText());
- }
- insertOp.setTime(timestamp);
+ List<InsertMultiValueContext> insertMultiValues = ctx.insertMultiValue();
List<String> valueList = new ArrayList<>();
- List<MeasurementValueContext> values = ctx.measurementValue();
- for (MeasurementValueContext value : values) {
- for (ConstantContext counstant : value.constant()) {
- valueList.add(counstant.getText());
+ long[] timeArray = new long[insertMultiValues.size()];
+ for (int i = 0; i < insertMultiValues.size(); i++) {
+ long timestamp;
+ if (insertMultiValues.get(i).dateFormat() != null) {
+ timestamp =
parseTimeFormat(insertMultiValues.get(i).dateFormat().getText());
+ } else {
+ timestamp = Long.parseLong(insertMultiValues.get(i).INT().getText());
+ }
+ timeArray[i] = timestamp;
+ List<MeasurementValueContext> values =
insertMultiValues.get(i).measurementValue();
+ for (MeasurementValueContext value : values) {
+ for (ConstantContext counstant : value.constant()) {
+ valueList.add(counstant.getText());
+ }
}
}
+ insertOp.setTimes(timeArray);
insertOp.setValueList(valueList.toArray(new String[0]));
}
diff --git
a/server/src/main/java/org/apache/iotdb/db/qp/strategy/PhysicalGenerator.java
b/server/src/main/java/org/apache/iotdb/db/qp/strategy/PhysicalGenerator.java
index 7b8c0ac..fdb331b 100644
---
a/server/src/main/java/org/apache/iotdb/db/qp/strategy/PhysicalGenerator.java
+++
b/server/src/main/java/org/apache/iotdb/db/qp/strategy/PhysicalGenerator.java
@@ -77,6 +77,7 @@ import org.apache.iotdb.db.qp.physical.crud.FillQueryPlan;
import org.apache.iotdb.db.qp.physical.crud.GroupByTimeFillPlan;
import org.apache.iotdb.db.qp.physical.crud.GroupByTimePlan;
import org.apache.iotdb.db.qp.physical.crud.InsertRowPlan;
+import org.apache.iotdb.db.qp.physical.crud.InsertRowsPlan;
import org.apache.iotdb.db.qp.physical.crud.LastQueryPlan;
import org.apache.iotdb.db.qp.physical.crud.QueryIndexPlan;
import org.apache.iotdb.db.qp.physical.crud.QueryPlan;
@@ -129,6 +130,7 @@ import org.apache.iotdb.tsfile.read.expression.IExpression;
import org.apache.iotdb.tsfile.utils.Pair;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
@@ -240,14 +242,31 @@ public class PhysicalGenerator {
measurementsNum++;
}
}
- if (measurementsNum != insert.getValueList().length) {
+ if (measurementsNum == 0 || (insert.getValueList().length %
measurementsNum != 0)) {
throw new SQLParserException(
String.format(
"the measurementList's size %d is not consistent with the
valueList's size %d",
measurementsNum, insert.getValueList().length));
}
- return new InsertRowPlan(
- paths.get(0), insert.getTime(), insert.getMeasurementList(),
insert.getValueList());
+ if (measurementsNum == insert.getValueList().length) {
+ return new InsertRowPlan(
+ paths.get(0),
+ insert.getTimes()[0],
+ insert.getMeasurementList(),
+ insert.getValueList());
+ }
+ InsertRowsPlan insertRowsPlan = new InsertRowsPlan();
+ for (int i = 0; i < insert.getTimes().length; i++) {
+ insertRowsPlan.addOneInsertRowPlan(
+ new InsertRowPlan(
+ paths.get(0),
+ insert.getTimes()[i],
+ insert.getMeasurementList(),
+ Arrays.copyOfRange(
+ insert.getValueList(), i * measurementsNum, (i + 1) *
measurementsNum)),
+ i);
+ }
+ return insertRowsPlan;
case MERGE:
if (operator.getTokenIntType() == SQLConstant.TOK_FULL_MERGE) {
return new MergePlan(OperatorType.FULL_MERGE);
diff --git
a/server/src/test/java/org/apache/iotdb/db/integration/IoTDBInsertMultiRowIT.java
b/server/src/test/java/org/apache/iotdb/db/integration/IoTDBInsertMultiRowIT.java
new file mode 100644
index 0000000..c82fb79
--- /dev/null
+++
b/server/src/test/java/org/apache/iotdb/db/integration/IoTDBInsertMultiRowIT.java
@@ -0,0 +1,125 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.iotdb.db.integration;
+
+import org.apache.iotdb.db.utils.EnvironmentUtils;
+import org.apache.iotdb.jdbc.Config;
+
+import org.junit.AfterClass;
+import org.junit.Assert;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Objects;
+
+/**
+ * @Author: Architect @Date: 2021-03-30 18:36 @Description: This class is
initially intend to test
+ * the issue of IOTDB-924
+ */
+public class IoTDBInsertMultiRowIT {
+ private static List<String> sqls = new ArrayList<>();
+ private static Connection connection;
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvironmentUtils.closeStatMonitor();
+ initCreateSQLStatement();
+ EnvironmentUtils.envSetUp();
+ insertData();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ close();
+ EnvironmentUtils.cleanEnv();
+ }
+
+ private static void close() {
+ if (Objects.nonNull(connection)) {
+ try {
+ connection.close();
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ private static void initCreateSQLStatement() {
+ sqls.add("SET STORAGE GROUP TO root.t1");
+ sqls.add("CREATE TIMESERIES root.t1.wf01.wt01.status WITH
DATATYPE=BOOLEAN, ENCODING=PLAIN");
+ sqls.add("CREATE TIMESERIES root.t1.wf01.wt01.temperature WITH
DATATYPE=FLOAT, ENCODING=RLE");
+ }
+
+ private static void insertData() throws ClassNotFoundException, SQLException
{
+ Class.forName(Config.JDBC_DRIVER_NAME);
+ connection =
+ DriverManager.getConnection(Config.IOTDB_URL_PREFIX +
"127.0.0.1:6667/", "root", "root");
+ Statement statement = connection.createStatement();
+
+ for (String sql : sqls) {
+ statement.execute(sql);
+ }
+
+ statement.close();
+ }
+
+ @Test
+ public void testInsertMultiRow() throws SQLException {
+ Statement st0 = connection.createStatement();
+ st0.execute("insert into root.t1.wf01.wt01(timestamp, status) values (1,
true)");
+ st0.execute("insert into root.t1.wf01.wt01(timestamp, status) values (2,
true),(3, false)");
+ st0.execute(
+ "insert into root.t1.wf01.wt01(timestamp, status) values (4, true),(5,
true),(6, false)");
+
+ st0.execute(
+ "insert into root.t1.wf01.wt01(timestamp, temperature, status) values
(7, 15.3, true)");
+ st0.execute(
+ "insert into root.t1.wf01.wt01(timestamp, temperature, status) values
(8, 18.3, false),(9, 23.1, false)");
+ st0.execute(
+ "insert into root.t1.wf01.wt01(timestamp, temperature, status) values
(10, 22.3, true),(11, 18.8, false),(12, 24.4, true)");
+ st0.close();
+
+ Statement st1 = connection.createStatement();
+ ResultSet rs1 = st1.executeQuery("select count(status) from
root.t1.wf01.wt01");
+ rs1.next();
+ long countStatus = rs1.getLong(1);
+ Assert.assertEquals(countStatus, 12L);
+
+ ResultSet rs2 = st1.executeQuery("select count(temperature) from
root.t1.wf01.wt01");
+ rs2.next();
+ long countTemperature = rs2.getLong(1);
+ Assert.assertEquals(countTemperature, 6L);
+
+ st1.close();
+ }
+
+ @Test(expected = Exception.class)
+ public void testInsertWithTimesColumns() throws SQLException {
+ Statement st1 = connection.createStatement();
+ st1.execute("insert into root.t1.wf01.wt01(timestamp) values(1)");
+ }
+}
