You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by qi...@apache.org on 2020/11/24 00:58:26 UTC
[iotdb] branch master updated: [IOTDB-937] change jdbc into session
and don't need to create metadata (#1914)
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 157be8f [IOTDB-937] change jdbc into session and don't need to create metadata (#1914)
157be8f is described below
commit 157be8f23073b9ef72e7c54eac9d41b2830bea2a
Author: Boris <ge...@apache.org>
AuthorDate: Tue Nov 24 08:58:14 2020 +0800
[IOTDB-937] change jdbc into session and don't need to create metadata (#1914)
---
README.md | 63 +--
README_ZH.md | 67 +--
cli/pom.xml | 7 +-
.../java/org/apache/iotdb/cli/AbstractCli.java | 11 +-
.../org/apache/iotdb/tool/AbstractCsvTool.java | 44 +-
.../main/java/org/apache/iotdb/tool/ExportCsv.java | 210 ++++-----
.../main/java/org/apache/iotdb/tool/ImportCsv.java | 488 +++++++--------------
.../java/org/apache/iotdb/cli/AbstractScript.java | 4 +
.../org/apache/iotdb/tool/CsvLineSplitTest.java | 32 ++
.../org/apache/iotdb/tool/ExportCsvTestIT.java | 12 +-
.../org/apache/iotdb/tool/ImportCsvTestIT.java | 12 +-
.../tests/tools/importCsv/AbstractScript.java | 4 +-
.../tests/tools/importCsv/ExportCsvTestIT.java | 193 ++++++++
.../tests/tools/importCsv/ImportCsvTestIT.java | 154 ++++++-
docs/UserGuide/System Tools/CSV Tool.md | 85 ++++
docs/zh/UserGuide/System Tools/CSV Tool.md | 88 ++++
16 files changed, 860 insertions(+), 614 deletions(-)
diff --git a/README.md b/README.md
index 17f0a01..add7307 100644
--- a/README.md
+++ b/README.md
@@ -72,14 +72,7 @@ For the latest information about IoTDB, please visit [IoTDB official website](ht
- [Stop IoTDB](#stop-iotdb)
- [Only build server](#only-build-server)
- [Only build cli](#only-build-cli)
- - [Usage of import-csv.sh](#usage-of-import-csvsh)
- - [Create metadata](#create-metadata)
- - [An example of import csv file](#an-example-of-import-csv-file)
- - [Run import shell](#run-import-shell)
- - [Error data file](#error-data-file)
- - [Usage of export-csv.sh](#usage-of-export-csvsh)
- - [Run export shell](#run-export-shell)
- - [Input query](#input-query)
+ - [Usage of CSV Import and Export Tool](#usage-of-csv-import-and-export-tool)
<!-- /TOC -->
@@ -333,59 +326,9 @@ Under the root path of iotdb:
After being built, the IoTDB cli is located at the folder "cli/target/iotdb-cli-{project.version}".
-## Usage of import-csv.sh
-
-### Create metadata
-```
-SET STORAGE GROUP TO root.fit.d1;
-SET STORAGE GROUP TO root.fit.d2;
-SET STORAGE GROUP TO root.fit.p;
-CREATE TIMESERIES root.fit.d1.s1 WITH DATATYPE=INT32,ENCODING=RLE;
-CREATE TIMESERIES root.fit.d1.s2 WITH DATATYPE=TEXT,ENCODING=PLAIN;
-CREATE TIMESERIES root.fit.d2.s1 WITH DATATYPE=INT32,ENCODING=RLE;
-CREATE TIMESERIES root.fit.d2.s3 WITH DATATYPE=INT32,ENCODING=RLE;
-CREATE TIMESERIES root.fit.p.s1 WITH DATATYPE=INT32,ENCODING=RLE;
-```
-
-### An example of import csv file
-
-```
-Time,root.fit.d1.s1,root.fit.d1.s2,root.fit.d2.s1,root.fit.d2.s3,root.fit.p.s1
-1,100,'hello',200,300,400
-2,500,'world',600,700,800
-3,900,'IoTDB',1000,1100,1200
-```
-
-### Run import shell
-```
-# Unix/OS X
-> tools/import-csv.sh -h <ip> -p <port> -u <username> -pw <password> -f <xxx.csv>
-
-# Windows
-> tools\import-csv.bat -h <ip> -p <port> -u <username> -pw <password> -f <xxx.csv>
-```
-
-### Error data file
-
-`csvInsertError.error`
-
-## Usage of export-csv.sh
-
-### Run export shell
-```
-# Unix/OS X
-> tools/export-csv.sh -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format>]
-
-# Windows
-> tools\export-csv.bat -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format>]
-```
-
-### Input query
-
-```
-select * from root.fit.d1
-```
+# Usage of CSV Import and Export Tool
+see [Usage of CSV Import and Export Tool](https://iotdb.apache.org/UserGuide/System%20Tools/CSV%20Tool.html)
# Frequent Questions for Compiling
see [Frequent Questions when Compiling the Source Code](https://iotdb.apache.org/Development/ContributeGuide.html#_Frequent-Questions-when-Compiling-the-Source-Code)
diff --git a/README_ZH.md b/README_ZH.md
index 97bf7ff..6f0928e 100644
--- a/README_ZH.md
+++ b/README_ZH.md
@@ -70,14 +70,7 @@ IoTDB的主要特点如下:
- [停止 IoTDB](#停止-iotdb)
- [只编译 server](#只编译-server)
- [只编译 cli](#只编译-cli)
- - [使用 import-csv.sh](#使用-import-csvsh)
- - [创建元数据](#创建元数据)
- - [从 csv 文件导入数据的示例](#从-csv-文件导入数据的示例)
- - [运行 import shell](#运行-import-shell)
- - [错误的数据文件](#错误的数据文件)
- - [使用 export-csv.sh](#使用-export-csvsh)
- - [运行 export shell](#运行-export-shell)
- - [执行查询](#执行查询)
+ - [导入导出CSV工具](#导入导出CSV工具)
<!-- /TOC -->
@@ -333,61 +326,9 @@ server 可以使用 "ctrl-C" 或者执行下面的脚本:
编译完成后, IoTDB cli 将生成在 "cli/target/iotdb-cli-{project.version}".
-## 使用 import-csv.sh
-
-### 创建元数据
-
-```
-SET STORAGE GROUP TO root.fit.d1;
-SET STORAGE GROUP TO root.fit.d2;
-SET STORAGE GROUP TO root.fit.p;
-CREATE TIMESERIES root.fit.d1.s1 WITH DATATYPE=INT32,ENCODING=RLE;
-CREATE TIMESERIES root.fit.d1.s2 WITH DATATYPE=TEXT,ENCODING=PLAIN;
-CREATE TIMESERIES root.fit.d2.s1 WITH DATATYPE=INT32,ENCODING=RLE;
-CREATE TIMESERIES root.fit.d2.s3 WITH DATATYPE=INT32,ENCODING=RLE;
-CREATE TIMESERIES root.fit.p.s1 WITH DATATYPE=INT32,ENCODING=RLE;
-```
-
-### 从 csv 文件导入数据的示例
-
-```
-Time,root.fit.d1.s1,root.fit.d1.s2,root.fit.d2.s1,root.fit.d2.s3,root.fit.p.s1
-1,100,'hello',200,300,400
-2,500,'world',600,700,800
-3,900,'IoTDB',1000,1100,1200
-```
-
-### 运行 import shell
-```
-# Unix/OS X
-> tools/import-csv.sh -h <ip> -p <port> -u <username> -pw <password> -f <xxx.csv>
-
-# Windows
-> tools\import-csv.bat -h <ip> -p <port> -u <username> -pw <password> -f <xxx.csv>
-```
-
-### 错误的数据文件
-
-`csvInsertError.error`
-
-## 使用 export-csv.sh
-
-### 运行 export shell
-
-```
-# Unix/OS X
-> tools/export-csv.sh -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format>]
-
-# Windows
-> tools\export-csv.bat -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format>]
-```
-
-### 执行查询
-
-```
-select * from root.fit.d1
-```
+# 导入导出CSV工具
+查看 [导入导出CSV工具](https://iotdb.apache.org/zh/UserGuide/System%20Tools/CSV%20Tool.html)
# 常见编译错误
查看 [常见编译错误](https://iotdb.apache.org/zh/Development/ContributeGuide.html#%E5%B8%B8%E8%A7%81%E7%BC%96%E8%AF%91%E9%94%99%E8%AF%AF)
@@ -401,4 +342,4 @@ select * from root.fit.d1
* 添加好友 tietouqiao，我们会邀请您进群
-获取更多内容，请查看 [加入社区](https://github.com/apache/iotdb/issues/1995)
\ No newline at end of file
+获取更多内容，请查看 [加入社区](https://github.com/apache/iotdb/issues/1995)
diff --git a/cli/pom.xml b/cli/pom.xml
index 4779c65..78eef51 100644
--- a/cli/pom.xml
+++ b/cli/pom.xml
@@ -37,7 +37,7 @@
<dependencies>
<dependency>
<groupId>org.apache.iotdb</groupId>
- <artifactId>iotdb-jdbc</artifactId>
+ <artifactId>iotdb-session</artifactId>
<version>${project.version}</version>
</dependency>
<dependency>
@@ -48,6 +48,11 @@
<scope>test</scope>
</dependency>
<dependency>
+ <groupId>org.apache.iotdb</groupId>
+ <artifactId>iotdb-jdbc</artifactId>
+ <version>${project.version}</version>
+ </dependency>
+ <dependency>
<groupId>commons-cli</groupId>
<artifactId>commons-cli</artifactId>
</dependency>
diff --git a/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java b/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
index b2970d7..2648199 100644
--- a/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
+++ b/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
@@ -536,14 +536,9 @@ public abstract class AbstractCli {
+ "Noted that your file path cannot contain any space character)");
return;
}
- try {
- println(cmd.split(" ")[1]);
- ImportCsv.importCsvFromFile(host, port, username, password, cmd.split(" ")[1],
- connection.getTimeZone());
- } catch (SQLException e) {
- println(String.format("Failed to import from %s because %s",
- cmd.split(" ")[1], e.getMessage()));
- }
+ println(cmd.split(" ")[1]);
+ ImportCsv.importCsvFromFile(host, port, username, password, cmd.split(" ")[1],
+ connection.getTimeZone());
}
private static void executeQuery(IoTDBConnection connection, String cmd) {
diff --git a/cli/src/main/java/org/apache/iotdb/tool/AbstractCsvTool.java b/cli/src/main/java/org/apache/iotdb/tool/AbstractCsvTool.java
index d7e3074..7b50924 100644
--- a/cli/src/main/java/org/apache/iotdb/tool/AbstractCsvTool.java
+++ b/cli/src/main/java/org/apache/iotdb/tool/AbstractCsvTool.java
@@ -22,10 +22,12 @@ import java.io.IOException;
import java.time.ZoneId;
import jline.console.ConsoleReader;
import org.apache.commons.cli.CommandLine;
+import org.apache.commons.cli.Option;
+import org.apache.commons.cli.Options;
import org.apache.iotdb.exception.ArgsErrorException;
-import org.apache.iotdb.jdbc.IoTDBConnection;
-import org.apache.iotdb.jdbc.IoTDBSQLException;
-import org.apache.thrift.TException;
+import org.apache.iotdb.rpc.IoTDBConnectionException;
+import org.apache.iotdb.rpc.StatementExecutionException;
+import org.apache.iotdb.session.Session;
public abstract class AbstractCsvTool {
@@ -69,7 +71,7 @@ public abstract class AbstractCsvTool {
protected static String timeZoneID;
protected static String timeFormat;
- protected static IoTDBConnection connection;
+ protected static Session session;
AbstractCsvTool() {}
@@ -85,11 +87,11 @@ public abstract class AbstractCsvTool {
return str;
}
- protected static void setTimeZone() throws IoTDBSQLException, TException {
+ protected static void setTimeZone() throws IoTDBConnectionException, StatementExecutionException {
if (timeZoneID != null) {
- connection.setTimeZone(timeZoneID);
+ session.setTimeZone(timeZoneID);
}
- zoneId = ZoneId.of(connection.getTimeZone());
+ zoneId = ZoneId.of(session.getTimeZone());
}
protected static void parseBasicParams(CommandLine commandLine, ConsoleReader reader)
@@ -110,8 +112,32 @@ public abstract class AbstractCsvTool {
return true;
}
}
- System.out.println(String.format("Input time format %s is not supported, "
- + "please input like yyyy-MM-dd\\ HH:mm:ss.SSS or yyyy-MM-dd'T'HH:mm:ss.SSS", timeFormat));
+ System.out.printf("Input time format %s is not supported, "
+ + "please input like yyyy-MM-dd\\ HH:mm:ss.SSS or yyyy-MM-dd'T'HH:mm:ss.SSS%n", timeFormat);
return false;
}
+
+ protected static Options createNewOptions() {
+ Options options = new Options();
+
+ Option opHost = Option.builder(HOST_ARGS).longOpt(HOST_NAME).required().argName(HOST_NAME)
+ .hasArg()
+ .desc("Host Name (required)").build();
+ options.addOption(opHost);
+
+ Option opPort = Option.builder(PORT_ARGS).longOpt(PORT_NAME).required().argName(PORT_NAME)
+ .hasArg()
+ .desc("Port (required)").build();
+ options.addOption(opPort);
+
+ Option opUsername = Option.builder(USERNAME_ARGS).longOpt(USERNAME_NAME).required()
+ .argName(USERNAME_NAME)
+ .hasArg().desc("Username (required)").build();
+ options.addOption(opUsername);
+
+ Option opPassword = Option.builder(PASSWORD_ARGS).longOpt(PASSWORD_NAME).optionalArg(true)
+ .argName(PASSWORD_NAME).hasArg().desc("Password (optional)").build();
+ options.addOption(opPassword);
+ return options;
+ }
}
diff --git a/cli/src/main/java/org/apache/iotdb/tool/ExportCsv.java b/cli/src/main/java/org/apache/iotdb/tool/ExportCsv.java
index 7a33eb4..df08bdc 100644
--- a/cli/src/main/java/org/apache/iotdb/tool/ExportCsv.java
+++ b/cli/src/main/java/org/apache/iotdb/tool/ExportCsv.java
@@ -25,16 +25,9 @@ import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
-import java.sql.DriverManager;
-import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
-import java.sql.SQLException;
-import java.sql.Statement;
-import java.sql.Types;
import java.time.Instant;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
-import java.util.ArrayList;
import java.util.List;
import jline.console.ConsoleReader;
import org.apache.commons.cli.CommandLine;
@@ -46,9 +39,13 @@ import org.apache.commons.cli.Options;
import org.apache.commons.cli.ParseException;
import org.apache.iotdb.cli.AbstractCli;
import org.apache.iotdb.exception.ArgsErrorException;
-import org.apache.iotdb.jdbc.Config;
-import org.apache.iotdb.jdbc.IoTDBConnection;
-import org.apache.thrift.TException;
+import org.apache.iotdb.rpc.IoTDBConnectionException;
+import org.apache.iotdb.rpc.StatementExecutionException;
+import org.apache.iotdb.session.Session;
+import org.apache.iotdb.session.SessionDataSet;
+import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+import org.apache.iotdb.tsfile.read.common.Field;
+import org.apache.iotdb.tsfile.read.common.RowRecord;
/**
* Export CSV file.
@@ -75,20 +72,16 @@ public class ExportCsv extends AbstractCsvTool {
private static final int EXPORT_PER_LINE_COUNT = 10000;
- private static String TIMESTAMP_PRECISION = "ms";
-
- private static List<Integer> typeList = new ArrayList<>();
-
/**
* main function of export csv tool.
*/
- public static void main(String[] args) throws IOException, SQLException {
+ public static void main(String[] args) throws IOException {
Options options = createOptions();
HelpFormatter hf = new HelpFormatter();
- hf.setOptionComparator(null); // avoid reordering
- hf.setWidth(MAX_HELP_CONSOLE_WIDTH);
CommandLine commandLine;
CommandLineParser parser = new DefaultParser();
+ hf.setOptionComparator(null); // avoid reordering
+ hf.setWidth(MAX_HELP_CONSOLE_WIDTH);
if (args == null || args.length == 0) {
System.out.println("Too few params input, please check the following hint.");
@@ -116,13 +109,11 @@ public class ExportCsv extends AbstractCsvTool {
if (!checkTimeFormat()) {
return;
}
- Class.forName(Config.JDBC_DRIVER_NAME);
String sqlFile = commandLine.getOptionValue(SQL_FILE_ARGS);
String sql;
-
- connection = (IoTDBConnection) DriverManager
- .getConnection(Config.IOTDB_URL_PREFIX + host + ":" + port + "/", username, password);
+ session = new Session(host, Integer.parseInt(port), username, password);
+ session.open(false);
setTimeZone();
if (sqlFile == null) {
@@ -134,21 +125,21 @@ public class ExportCsv extends AbstractCsvTool {
} else {
dumpFromSqlFile(sqlFile);
}
- } catch (ClassNotFoundException e) {
- System.out.println("Failed to export data because cannot find IoTDB JDBC Driver, "
- + "please check whether you have imported driver or not: " + e.getMessage());
- } catch (TException e) {
- System.out.println("Encounter an error when connecting to server, because " + e.getMessage());
- } catch (SQLException e) {
- System.out.println("Encounter an error when exporting data, error is: " + e.getMessage());
} catch (IOException e) {
System.out.println("Failed to operate on file, because " + e.getMessage());
} catch (ArgsErrorException e) {
System.out.println("Invalid args: " + e.getMessage());
+ } catch (IoTDBConnectionException | StatementExecutionException e) {
+ System.out.println("Connect failed because " + e.getMessage());
} finally {
reader.close();
- if (connection != null) {
- connection.close();
+ if (session != null) {
+ try {
+ session.close();
+ } catch (IoTDBConnectionException e) {
+ System.out
+ .println("Encounter an error when closing session, error is: " + e.getMessage());
+ }
}
}
}
@@ -176,26 +167,7 @@ public class ExportCsv extends AbstractCsvTool {
* @return object Options
*/
private static Options createOptions() {
- Options options = new Options();
-
- Option opHost = Option.builder(HOST_ARGS).longOpt(HOST_NAME).required().argName(HOST_NAME)
- .hasArg()
- .desc("Host Name (required)").build();
- options.addOption(opHost);
-
- Option opPort = Option.builder(PORT_ARGS).longOpt(PORT_NAME).required().argName(PORT_NAME)
- .hasArg()
- .desc("Port (required)").build();
- options.addOption(opPort);
-
- Option opUsername = Option.builder(USERNAME_ARGS).longOpt(USERNAME_NAME).required()
- .argName(USERNAME_NAME)
- .hasArg().desc("Username (required)").build();
- options.addOption(opUsername);
-
- Option opPassword = Option.builder(PASSWORD_ARGS).longOpt(PASSWORD_NAME).optionalArg(true)
- .argName(PASSWORD_NAME).hasArg().desc("Password (optional)").build();
- options.addOption(opPassword);
+ Options options = createNewOptions();
Option opTargetFile = Option.builder(TARGET_DIR_ARGS).required().argName(TARGET_DIR_NAME)
.hasArg()
@@ -234,12 +206,7 @@ public class ExportCsv extends AbstractCsvTool {
String sql;
int index = 0;
while ((sql = reader.readLine()) != null) {
- try {
- dumpResult(sql, index);
- } catch (SQLException e) {
- System.out
- .println("Cannot dump data for statement " + sql + ", because : " + e.getMessage());
- }
+ dumpResult(sql, index);
index++;
}
}
@@ -248,12 +215,10 @@ public class ExportCsv extends AbstractCsvTool {
/**
* Dump files from database to CSV file.
*
- * @param sql export the result of executing the sql
+ * @param sql export the result of executing the sql
* @param index use to create dump file name
- * @throws SQLException if SQL is not valid
*/
- private static void dumpResult(String sql, int index)
- throws SQLException {
+ private static void dumpResult(String sql, int index) {
final String path = targetDirectory + targetFile + index + ".csv";
File tf = new File(path);
@@ -263,82 +228,73 @@ public class ExportCsv extends AbstractCsvTool {
return;
}
} catch (IOException e) {
- System.out.println("Cannot create dump file " + path + "because: " + e.getMessage());
+ System.out.println("Cannot create dump file " + path + " " + "because: " + e.getMessage());
return;
}
System.out.println("Start to export data from sql statement: " + sql);
- try (Statement statement = connection.createStatement();
- ResultSet rs = statement.executeQuery(sql);
- BufferedWriter bw = new BufferedWriter(new FileWriter(tf))) {
- ResultSetMetaData metadata = rs.getMetaData();
+ try (BufferedWriter bw = new BufferedWriter(new FileWriter(tf))) {
+ SessionDataSet sessionDataSet = session.executeQueryStatement(sql);
long startTime = System.currentTimeMillis();
-
- int count = metadata.getColumnCount();
// write data in csv file
- writeMetadata(bw, count, metadata);
+ writeMetadata(bw, sessionDataSet.getColumnNames());
- int line = writeResultSet(rs, bw, count);
+ int line = writeResultSet(sessionDataSet, bw);
System.out
- .println(String.format("Statement [%s] has dumped to file %s successfully! It costs "
- + "%dms to export %d lines.", sql, path, System.currentTimeMillis() - startTime,
- line));
- } catch (IOException e) {
+ .printf("Statement [%s] has dumped to file %s successfully! It costs "
+ + "%dms to export %d lines.%n", sql, path, System.currentTimeMillis() - startTime,
+ line);
+ } catch (IOException | StatementExecutionException | IoTDBConnectionException e) {
System.out.println("Cannot dump result because: " + e.getMessage());
}
}
- private static void writeMetadata(BufferedWriter bw, int count, ResultSetMetaData metadata)
- throws SQLException, IOException {
- for (int i = 1; i <= count; i++) {
- if (i < count) {
- bw.write(metadata.getColumnLabel(i) + ",");
- } else {
- bw.write(metadata.getColumnLabel(i) + "\n");
- }
- typeList.add(metadata.getColumnType(i));
+ private static void writeMetadata(BufferedWriter bw, List<String> columnNames)
+ throws IOException {
+ if (!columnNames.get(0).equals("Time")) {
+ bw.write("Time" + ",");
+ }
+ for (int i = 0; i < columnNames.size() - 1; i++) {
+ bw.write(columnNames.get(i) + ",");
}
+ bw.write(columnNames.get(columnNames.size() - 1) + "\n");
}
- private static int writeResultSet(ResultSet rs, BufferedWriter bw, int count)
- throws SQLException, IOException {
+ private static int writeResultSet(SessionDataSet rs, BufferedWriter bw)
+ throws IOException, StatementExecutionException, IoTDBConnectionException {
int line = 0;
long timestamp = System.currentTimeMillis();
- while (rs.next()) {
- if (rs.getString(1) == null ||
- "null".equalsIgnoreCase(rs.getString(1))) {
- bw.write(",");
- } else {
- writeTime(rs, bw);
- writeValue(rs, count, bw);
- }
+ while (rs.hasNext()) {
+ RowRecord rowRecord = rs.next();
+ List<Field> fields = rowRecord.getFields();
+ writeTime(rowRecord.getTimestamp(), bw);
+ writeValue(fields, bw);
line++;
if (line % EXPORT_PER_LINE_COUNT == 0) {
long tmp = System.currentTimeMillis();
- System.out.println(
- String.format("%d lines have been exported, it takes %dms", line, (tmp - timestamp)));
+ System.out.printf("%d lines have been exported, it takes %dms%n", line, (tmp - timestamp));
timestamp = tmp;
}
}
return line;
}
- private static void writeTime(ResultSet rs, BufferedWriter bw) throws SQLException, IOException {
+ private static void writeTime(Long time, BufferedWriter bw) throws IOException {
ZonedDateTime dateTime;
+ String timestampPrecision = "ms";
switch (timeFormat) {
case "default":
- long timestamp = rs.getLong(1);
String str = AbstractCli
- .parseLongToDateWithPrecision(DateTimeFormatter.ISO_OFFSET_DATE_TIME, timestamp, zoneId,
- TIMESTAMP_PRECISION);
+ .parseLongToDateWithPrecision(DateTimeFormatter.ISO_OFFSET_DATE_TIME, time, zoneId,
+ timestampPrecision);
bw.write(str + ",");
break;
case "timestamp":
case "long":
case "nubmer":
- bw.write(rs.getLong(1) + ",");
+ bw.write(time + ",");
break;
default:
- dateTime = ZonedDateTime.ofInstant(Instant.ofEpochMilli(rs.getLong(1)),
+ dateTime = ZonedDateTime.ofInstant(Instant.ofEpochMilli(time),
zoneId);
bw.write(dateTime.format(DateTimeFormatter.ofPattern(timeFormat)) + ",");
break;
@@ -346,29 +302,49 @@ public class ExportCsv extends AbstractCsvTool {
}
@SuppressWarnings("squid:S3776") // Suppress high Cognitive Complexity warning
- private static void writeValue(ResultSet rs, int count, BufferedWriter bw)
- throws SQLException, IOException {
- for (int j = 2; j <= count; j++) {
- if (j < count) {
- if ("null".equals(rs.getString(j))) {
- bw.write(",");
- } else {
- if(typeList.get(j-1) == Types.VARCHAR) {
- bw.write("\'" + rs.getString(j) + "\'"+ ",");
+ private static void writeValue(List<Field> fields, BufferedWriter bw) throws IOException {
+ for (int j = 0; j < fields.size() - 1; j++) {
+ String value = fields.get(j).getStringValue();
+ if ("null".equalsIgnoreCase(value)) {
+ bw.write(",");
+ } else {
+ if (fields.get(j).getDataType() == TSDataType.TEXT) {
+ int location = value.indexOf("\"");
+ if (location > -1) {
+ if (location == 0 || value.charAt(location - 1) != '\\') {
+ bw.write("\"" + value.replace("\"", "\\\"") + "\",");
+ } else {
+ bw.write("\"" + value + "\",");
+ }
+ } else if (value.contains(",")) {
+ bw.write("\"" + value + "\",");
} else {
- bw.write(rs.getString(j) + ",");
+ bw.write(value + ",");
}
- }
- } else {
- if ("null".equals(rs.getString(j))) {
- bw.write("\n");
} else {
- if(typeList.get(j-1) == Types.VARCHAR) {
- bw.write("\'" + rs.getString(j) + "\'"+ "\n");
+ bw.write(value + ",");
+ }
+ }
+ }
+ String lastValue = fields.get(fields.size() - 1).getStringValue();
+ if ("null".equalsIgnoreCase(lastValue)) {
+ bw.write("\n");
+ } else {
+ if (fields.get(fields.size() - 1).getDataType() == TSDataType.TEXT) {
+ int location = lastValue.indexOf("\"");
+ if (location > -1) {
+ if (location == 0 || lastValue.charAt(location - 1) != '\\') {
+ bw.write("\"" + lastValue.replace("\"", "\\\"") + "\"\n");
} else {
- bw.write(rs.getString(j) + "\n");
+ bw.write("\"" + lastValue + "\"\n");
}
+ } else if (lastValue.contains(",")) {
+ bw.write("\"" + lastValue + "\"\n");
+ } else {
+ bw.write(lastValue + "\n");
}
+ } else {
+ bw.write(lastValue + "\n");
}
}
}
diff --git a/cli/src/main/java/org/apache/iotdb/tool/ImportCsv.java b/cli/src/main/java/org/apache/iotdb/tool/ImportCsv.java
index ee83572..bd4a55f 100644
--- a/cli/src/main/java/org/apache/iotdb/tool/ImportCsv.java
+++ b/cli/src/main/java/org/apache/iotdb/tool/ImportCsv.java
@@ -19,22 +19,16 @@
package org.apache.iotdb.tool;
import java.io.BufferedReader;
-import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
-import java.io.FileWriter;
import java.io.IOException;
import java.io.LineNumberReader;
-import java.sql.DriverManager;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
-import java.util.Iterator;
import java.util.List;
import java.util.Map;
+import java.util.Map.Entry;
import jline.console.ConsoleReader;
import me.tongfei.progressbar.ProgressBar;
import org.apache.commons.cli.CommandLine;
@@ -44,34 +38,27 @@ import org.apache.commons.cli.HelpFormatter;
import org.apache.commons.cli.Option;
import org.apache.commons.cli.Options;
import org.apache.commons.cli.ParseException;
-import org.apache.commons.io.FileUtils;
import org.apache.iotdb.exception.ArgsErrorException;
-import org.apache.iotdb.jdbc.Config;
-import org.apache.iotdb.jdbc.IoTDBConnection;
-import org.apache.iotdb.rpc.TSStatusCode;
-import org.apache.thrift.TException;
+import org.apache.iotdb.rpc.IoTDBConnectionException;
+import org.apache.iotdb.rpc.StatementExecutionException;
+import org.apache.iotdb.session.Session;
+import org.apache.iotdb.tsfile.common.constant.TsFileConstant;
/**
* read a CSV formatted data File and insert all the data into IoTDB.
*/
public class ImportCsv extends AbstractCsvTool {
+
private static final String FILE_ARGS = "f";
private static final String FILE_NAME = "file or folder";
private static final String FILE_SUFFIX = "csv";
private static final String TSFILEDB_CLI_PREFIX = "ImportCsv";
- private static final String ERROR_INFO_STR = "csvInsertError.error";
-
- private static final String STRING_DATA_TYPE = "TEXT";
- private static final int BATCH_EXECUTE_COUNT = 100;
-
- private static String errorInsertInfo = "";
- private static boolean errorFlag;
-
- private static String IOTDB_CLI_HOME = "IOTDB_CLI_HOME";
+ private static final String ILLEGAL_PATH_ARGUMENT = "Path parameter is null";
- private static int count;
- private static Statement statement;
+ // put these variable in here, because sonar fails. have to extract some code into a function. nextNode method.
+ private static int i;
+ private static int startIndex;
/**
* create the commandline options.
@@ -79,24 +66,7 @@ public class ImportCsv extends AbstractCsvTool {
* @return object Options
*/
private static Options createOptions() {
- Options options = new Options();
-
- Option opHost = Option.builder(HOST_ARGS).longOpt(HOST_NAME).required()
- .argName(HOST_NAME).hasArg().desc("Host Name (required)").build();
- options.addOption(opHost);
-
- Option opPort = Option.builder(PORT_ARGS).longOpt(PORT_NAME).required()
- .argName(PORT_NAME).hasArg().desc("Port (required)").build();
- options.addOption(opPort);
-
- Option opUsername = Option.builder(USERNAME_ARGS).longOpt(USERNAME_NAME)
- .required().argName(USERNAME_NAME)
- .hasArg().desc("Username (required)").build();
- options.addOption(opUsername);
-
- Option opPassword = Option.builder(PASSWORD_ARGS).longOpt(PASSWORD_NAME)
- .optionalArg(true).argName(PASSWORD_NAME).hasArg().desc("Password (optional)").build();
- options.addOption(opPassword);
+ Options options = createNewOptions();
Option opFile = Option.builder(FILE_ARGS).required().argName(FILE_NAME).hasArg().desc(
"If input a file path, load a csv file, "
@@ -119,8 +89,7 @@ public class ImportCsv extends AbstractCsvTool {
/**
* Data from csv To tsfile.
*/
- private static void loadDataFromCSV(File file, int index) {
- statement = null;
+ private static void loadDataFromCSV(File file) {
int fileLine;
try {
fileLine = getFileLineCount(file);
@@ -128,287 +97,78 @@ public class ImportCsv extends AbstractCsvTool {
System.out.println("Failed to import file: " + file.getName());
return;
}
- File errorFile = new File(errorInsertInfo + index);
- if (!errorFile.exists()) {
- try {
- errorFile.createNewFile();
- } catch (IOException e) {
- System.out.println("Cannot create a errorFile because: " + e.getMessage());
- return;
- }
- }
System.out.println("Start to import data from: " + file.getName());
- errorFlag = true;
- try(BufferedReader br = new BufferedReader(new FileReader(file));
- BufferedWriter bw = new BufferedWriter(new FileWriter(errorFile));
+ try (BufferedReader br = new BufferedReader(new FileReader(file));
ProgressBar pb = new ProgressBar("Import from: " + file.getName(), fileLine)) {
pb.setExtraMessage("Importing...");
String header = br.readLine();
-
- bw.write("From " + file.getAbsolutePath());
- bw.newLine();
- bw.newLine();
- bw.write(header);
- bw.newLine();
- bw.newLine();
-
- // storage csv table head info
- Map<String, ArrayList<Integer>> deviceToColumn = new HashMap<>();
- // storage csv table head info
- List<String> colInfo = new ArrayList<>();
- // storage csv device sensor info, corresponding csv table head
- List<String> headInfo = new ArrayList<>();
-
- String[] strHeadInfo = header.split(",");
- if (strHeadInfo.length <= 1) {
- System.out.println("The CSV file "+ file.getName() +" illegal, please check first line");
+ String[] cols = splitCsvLine(header);
+ if (cols.length <= 1) {
+ System.out.println("The CSV file " + file.getName() + " illegal, please check first line");
return;
}
- long startTime = System.currentTimeMillis();
- Map<String, String> timeseriesDataType = new HashMap<>();
+ List<String> devices = new ArrayList<>();
+ List<Long> times = new ArrayList<>();
+ List<List<String>> measurementsList = new ArrayList<>();
+ List<List<String>> valuesList = new ArrayList<>();
+ Map<String, List<Integer>> devicesToPositions = new HashMap<>();
+ Map<String, List<String>> devicesToMeasurements = new HashMap<>();
- boolean success = queryDatabaseMeta(strHeadInfo, file, bw, timeseriesDataType, headInfo,
- deviceToColumn, colInfo);
- if (!success) {
- errorFlag = false;
- return;
+ for (int i = 1; i < cols.length; i++) {
+ splitColToDeviceAndMeasurement(cols[i], devicesToPositions, devicesToMeasurements, i);
}
- statement = connection.createStatement();
-
+ int lineNumber = 0;
+ String line;
+ while ((line = br.readLine()) != null) {
+ cols = splitCsvLine(line);
+ lineNumber++;
+ for (Entry<String, List<Integer>> deviceToPositions : devicesToPositions
+ .entrySet()) {
+ String device = deviceToPositions.getKey();
+ devices.add(device);
+
+ times.add(Long.parseLong(cols[0]));
+
+ List<String> values = new ArrayList<>();
+ for (int position : deviceToPositions.getValue()) {
+ values.add(cols[position]);
+ }
+ valuesList.add(values);
- List<String> tmp = new ArrayList<>();
- success = readAndGenSqls(br, timeseriesDataType, deviceToColumn, colInfo, headInfo,
- bw, tmp, pb);
- if (!success) {
- return;
+ measurementsList.add(devicesToMeasurements.get(device));
+ }
+ if (lineNumber % 10000 == 0) {
+ session.insertRecords(devices, times, measurementsList, valuesList);
+ devices = new ArrayList<>();
+ times = new ArrayList<>();
+ measurementsList = new ArrayList<>();
+ valuesList = new ArrayList<>();
+ }
}
-
- executeSqls(bw, tmp, startTime, file);
+ session.insertRecords(devices, times, measurementsList, valuesList);
+ System.out.println("Insert csv successfully!");
pb.stepTo(fileLine);
} catch (FileNotFoundException e) {
- System.out.println("Cannot find " + file.getName() + " because: "+e.getMessage());
+ System.out.println("Cannot find " + file.getName() + " because: " + e.getMessage());
} catch (IOException e) {
System.out.println("CSV file read exception because: " + e.getMessage());
- } catch (SQLException e) {
- System.out.println("Database connection exception because: " + e.getMessage());
+ } catch (IoTDBConnectionException | StatementExecutionException e) {
+ System.out.println("Meet error when insert csv because " + e.getMessage());
} finally {
try {
- if (statement != null) {
- statement.close();
+ if (session != null) {
+ session.close();
}
- if (errorFlag) {
- FileUtils.forceDelete(errorFile);
- } else {
- System.out.println("Format of some lines in "+ file.getAbsolutePath() + " error, please "
- + "check "+errorFile.getAbsolutePath()+" for more information");
- }
- } catch (SQLException e) {
+ } catch (IoTDBConnectionException e) {
System.out.println("Sql statement can not be closed because: " + e.getMessage());
- } catch (IOException e) {
- System.out.println("Close file error because: " + e.getMessage());
- }
- }
- }
-
- private static void executeSqls(BufferedWriter bw, List<String> tmp, long startTime, File file)
- throws IOException {
- try {
- int[] result = statement.executeBatch();
- for (int i = 0; i < result.length; i++) {
- if (result[i] != TSStatusCode.SUCCESS_STATUS.getStatusCode() && i < tmp.size()) {
- bw.write(tmp.get(i));
- bw.newLine();
- errorFlag = false;
- }
- }
- statement.clearBatch();
- tmp.clear();
- } catch (SQLException e) {
- bw.write(e.getMessage());
- bw.newLine();
- errorFlag = false;
- System.out.println("Cannot execute sql because: " + e.getMessage());
- }
- }
-
- private static boolean readAndGenSqls(BufferedReader br, Map<String, String> timeseriesDataType,
- Map<String, ArrayList<Integer>> deviceToColumn, List<String> colInfo,
- List<String> headInfo, BufferedWriter bw, List<String> tmp, ProgressBar pb) throws IOException {
- String line;
- count = 0;
- while ((line = br.readLine()) != null) {
- List<String> sqls;
- try {
- sqls = createInsertSQL(line, timeseriesDataType, deviceToColumn, colInfo, headInfo);
- } catch (Exception e) {
- bw.write(String.format("error input line, maybe it is not complete: %s", line));
- bw.newLine();
- System.out.println("Cannot create sql for " + line + " because: " + e.getMessage());
- errorFlag = false;
- return false;
- }
- boolean success = addSqlsToBatch(sqls, tmp, bw);
- pb.step();
- if (!success) {
- return false;
- }
- }
- return true;
- }
-
- private static boolean addSqlsToBatch(List<String> sqls, List<String> tmp, BufferedWriter bw)
- throws IOException {
- for (String str : sqls) {
- try {
- count++;
- statement.addBatch(str);
- tmp.add(str);
- checkBatchSize(bw, tmp);
-
- } catch (SQLException e) {
- bw.write(e.getMessage());
- bw.newLine();
- errorFlag = false;
- System.out.println("Cannot execute sql because: " + e.getMessage());
- return false;
- }
- }
- return true;
- }
-
-
- private static void checkBatchSize(BufferedWriter bw, List<String> tmp)
- throws SQLException, IOException {
- if (count == BATCH_EXECUTE_COUNT) {
- int[] result = statement.executeBatch();
- for (int i = 0; i < result.length; i++) {
- if (result[i] != Statement.SUCCESS_NO_INFO && i < tmp.size()) {
- bw.write(tmp.get(i));
- bw.newLine();
- errorFlag = false;
- }
- }
- statement.clearBatch();
- count = 0;
- tmp.clear();
- }
- }
-
- private static boolean queryDatabaseMeta(String[] strHeadInfo, File file, BufferedWriter bw,
- Map<String, String> timeseriesDataType, List<String> headInfo,
- Map<String, ArrayList<Integer>> deviceToColumn,
- List<String> colInfo)
- throws SQLException, IOException {
- try (Statement statement = connection.createStatement()) {
-
- for (int i = 1; i < strHeadInfo.length; i++) {
- statement.execute("show timeseries " + strHeadInfo[i]);
- ResultSet resultSet = statement.getResultSet();
- try {
- if (resultSet.next()) {
- /*
- * now the resultSet is like following, so the index of dataType is 4
- * +--------------+-----+-------------+--------+--------+-----------+
- | timeseries|alias|storage group|dataType|encoding|compression|
- +--------------+-----+-------------+--------+--------+-----------+
- |root.fit.d1.s1| null| root.fit.d1| INT32| RLE| SNAPPY|
- |root.fit.d1.s2| null| root.fit.d1| TEXT| PLAIN| SNAPPY|
- +--------------+-----+-------------+--------+--------+-----------+
- */
- timeseriesDataType.put(strHeadInfo[i], resultSet.getString(4));
- } else {
- String errorInfo = String.format("Database cannot find %s in %s, stop import!",
- strHeadInfo[i], file.getAbsolutePath());
- System.out.println("Database cannot find " + strHeadInfo[i] + " in " + file.getAbsolutePath() + ", "
- + "stop import!");
- bw.write(errorInfo);
- return false;
- }
- } finally {
- resultSet.close();
- }
- headInfo.add(strHeadInfo[i]);
- String deviceInfo = strHeadInfo[i].substring(0, strHeadInfo[i].lastIndexOf('.'));
-
- if (!deviceToColumn.containsKey(deviceInfo)) {
- deviceToColumn.put(deviceInfo, new ArrayList<>());
- }
- // storage every device's sensor index info
- deviceToColumn.get(deviceInfo).add(i - 1);
- colInfo.add(strHeadInfo[i].substring(strHeadInfo[i].lastIndexOf('.') + 1));
- }
- }
- return true;
- }
-
- private static List<String> createInsertSQL(String line, Map<String, String> timeseriesToType,
- Map<String, ArrayList<Integer>> deviceToColumn,
- List<String> colInfo, List<String> headInfo) {
- String[] data = line.split(",", headInfo.size() + 1);
- List<String> sqls = new ArrayList<>();
- Iterator<Map.Entry<String, ArrayList<Integer>>> it = deviceToColumn.entrySet().iterator();
- while (it.hasNext()) {
- Map.Entry<String, ArrayList<Integer>> entry = it.next();
- String sql = createOneSql(entry, data, colInfo, timeseriesToType, headInfo);
- if (sql != null) {
- sqls.add(sql);
}
}
- return sqls;
}
- private static String createOneSql(Map.Entry<String, ArrayList<Integer>> entry, String[] data,
- List<String> colInfo, Map<String, String> timeseriesToType, List<String> headInfo) {
- StringBuilder sbd = new StringBuilder();
- ArrayList<Integer> colIndex = entry.getValue();
- sbd.append("insert into ").append(entry.getKey()).append("(timestamp");
- int skipCount = 0;
- for (int j = 0; j < colIndex.size(); ++j) {
- if ("".equals(data[entry.getValue().get(j) + 1])) {
- skipCount++;
- continue;
- }
- sbd.append(", ").append(colInfo.get(colIndex.get(j)));
- }
- // define every device null value' number, if the number equal the
- // sensor number, the insert operation stop
- if (skipCount == entry.getValue().size()) {
- return null;
- }
-
- // TODO when timestampsStr is empty
- String timestampsStr = data[0];
- sbd.append(") values(").append(timestampsStr.trim().isEmpty()
- ? "NO TIMESTAMP" : timestampsStr);
-
- for (int j = 0; j < colIndex.size(); ++j) {
- if ("".equals(data[entry.getValue().get(j) + 1])) {
- continue;
- }
- if (timeseriesToType.get(headInfo.get(colIndex.get(j))).equals(STRING_DATA_TYPE)) {
- /**
- * like csv line 1,100,'hello',200,300,400, we will read the third field as 'hello',
- * so, if we add '', the field will be ''hello'', and IoTDB will be failed
- * to insert the field.
- * Now, if we meet the string which is enclosed in quotation marks, we should not add ''
- */
- if ((data[colIndex.get(j) + 1].startsWith("'") && data[colIndex.get(j) + 1].endsWith("'")) ||
- (data[colIndex.get(j) + 1].startsWith("\"") && data[colIndex.get(j) + 1].endsWith("\""))) {
- sbd.append(",").append(data[colIndex.get(j) + 1]);
- } else {
- sbd.append(", \'").append(data[colIndex.get(j) + 1]).append("\'");
- }
- } else {
- sbd.append(",").append(data[colIndex.get(j) + 1]);
- }
- }
- sbd.append(")");
- return sbd.toString();
- }
- public static void main(String[] args) throws IOException, SQLException {
+ public static void main(String[] args) throws IOException {
Options options = createOptions();
HelpFormatter hf = new HelpFormatter();
hf.setOptionComparator(null);
@@ -459,18 +219,10 @@ public class ImportCsv extends AbstractCsvTool {
public static void importCsvFromFile(String ip, String port, String username,
String password, String filename,
- String timeZone) throws SQLException {
- String property = System.getProperty(IOTDB_CLI_HOME);
- if (property == null) {
- errorInsertInfo = ERROR_INFO_STR;
- } else {
- errorInsertInfo = property + File.separatorChar + ERROR_INFO_STR;
- }
+ String timeZone) {
try {
- Class.forName(Config.JDBC_DRIVER_NAME);
- connection = (IoTDBConnection) DriverManager.getConnection(Config.IOTDB_URL_PREFIX
- + ip + ":" + port + "/",
- username, password);
+ session = new Session(ip, Integer.parseInt(port), username, password);
+ session.open(false);
timeZoneID = timeZone;
setTimeZone();
@@ -480,33 +232,33 @@ public class ImportCsv extends AbstractCsvTool {
} else if (file.isDirectory()) {
importFromDirectory(file);
}
-
- } catch (ClassNotFoundException e) {
- System.out.println("Failed to import data because cannot find IoTDB JDBC Driver, "
- + "please check whether you have imported driver or not: " + e.getMessage());
- } catch (TException e) {
+ } catch (IoTDBConnectionException e) {
System.out.println("Encounter an error when connecting to server, because " + e.getMessage());
- } catch (SQLException e){
- System.out.println("Encounter an error when importing data, error is: " + e.getMessage());
- } catch (Exception e) {
- System.out.println("Encounter an error, because: " + e.getMessage());
+ } catch (StatementExecutionException e) {
+ System.out
+ .println("Encounter an error when executing the statement, because " + e.getMessage());
} finally {
- if (connection != null) {
- connection.close();
+ if (session != null) {
+ try {
+ session.close();
+ } catch (IoTDBConnectionException e) {
+ System.out
+ .println("Encounter an error when closing the connection, because " + e.getMessage());
+ }
}
}
}
private static void importFromSingleFile(File file) {
if (file.getName().endsWith(FILE_SUFFIX)) {
- loadDataFromCSV(file, 1);
+ loadDataFromCSV(file);
} else {
- System.out.println("File "+ file.getName() +" should ends with '.csv' if you want to import");
+ System.out
+ .println("File " + file.getName() + " should ends with '.csv' if you want to import");
}
}
private static void importFromDirectory(File file) {
- int i = 1;
File[] files = file.listFiles();
if (files == null) {
return;
@@ -515,17 +267,17 @@ public class ImportCsv extends AbstractCsvTool {
for (File subFile : files) {
if (subFile.isFile()) {
if (subFile.getName().endsWith(FILE_SUFFIX)) {
- loadDataFromCSV(subFile, i);
- i++;
+ loadDataFromCSV(subFile);
} else {
- System.out.println("File " + file.getName() + " should ends with '.csv' if you want to import");
+ System.out
+ .println("File " + file.getName() + " should ends with '.csv' if you want to import");
}
}
}
}
private static int getFileLineCount(File file) throws IOException {
- int line = 0;
+ int line;
try (LineNumberReader count = new LineNumberReader(new FileReader(file))) {
while (count.skip(Long.MAX_VALUE) > 0) {
// Loop just in case the file is > Long.MAX_VALUE or skip() decides to not read the entire file
@@ -535,4 +287,84 @@ public class ImportCsv extends AbstractCsvTool {
}
return line;
}
+
+ private static void splitColToDeviceAndMeasurement(String col, Map<String, List<Integer>> devicesToPositions, Map<String, List<String>> devicesToMeasurements, int position) {
+ if (col.length() > 0) {
+ if (col.charAt(col.length() - 1) == TsFileConstant.DOUBLE_QUOTE) {
+ int endIndex = col.lastIndexOf('"', col.length() - 2);
+ // if a double quotes with escape character
+ while (endIndex != -1 && col.charAt(endIndex - 1) == '\\') {
+ endIndex = col.lastIndexOf('"', endIndex - 2);
+ }
+ if (endIndex != -1 && (endIndex == 0 || col.charAt(endIndex - 1) == '.')) {
+ putDeviceAndMeasurement(col.substring(0, endIndex - 1), col.substring(endIndex),
+ devicesToPositions, devicesToMeasurements, position);
+ } else {
+ throw new IllegalArgumentException(ILLEGAL_PATH_ARGUMENT);
+ }
+ } else if (col.charAt(col.length() - 1) != TsFileConstant.DOUBLE_QUOTE
+ && col.charAt(col.length() - 1) != TsFileConstant.PATH_SEPARATOR_CHAR) {
+ int endIndex = col.lastIndexOf(TsFileConstant.PATH_SEPARATOR_CHAR);
+ if (endIndex < 0) {
+ putDeviceAndMeasurement("", col, devicesToPositions, devicesToMeasurements, position);
+ } else {
+ putDeviceAndMeasurement(col.substring(0, endIndex), col.substring(endIndex + 1),
+ devicesToPositions, devicesToMeasurements, position);
+ }
+ } else {
+ throw new IllegalArgumentException(ILLEGAL_PATH_ARGUMENT);
+ }
+ } else {
+ putDeviceAndMeasurement("", col, devicesToPositions, devicesToMeasurements, position);
+ }
+ }
+
+ private static void putDeviceAndMeasurement(String device, String measurement, Map<String, List<Integer>> devicesToPositions, Map<String, List<String>> devicesToMeasurements, int position) {
+ if (devicesToMeasurements.get(device) == null && devicesToPositions.get(device) == null) {
+ List<String> measurements = new ArrayList<>();
+ measurements.add(measurement);
+ devicesToMeasurements.put(device, measurements);
+ List<Integer> positions = new ArrayList<>();
+ positions.add(position);
+ devicesToPositions.put(device, positions);
+ } else {
+ devicesToMeasurements.get(device).add(measurement);
+ devicesToPositions.get(device).add(position);
+ }
+ }
+
+ public static String[] splitCsvLine(String path) {
+ List<String> nodes = new ArrayList<>();
+ startIndex = 0;
+ for (i = 0; i < path.length(); i++) {
+ if (path.charAt(i) == ',') {
+ nodes.add(path.substring(startIndex, i));
+ startIndex = i + 1;
+ } else if (path.charAt(i) == '"') {
+ nextNode(path, nodes, '"');
+ } else if (path.charAt(i) == '\'') {
+ nextNode(path, nodes, '\'');
+ }
+ }
+ if (startIndex <= path.length() - 1) {
+ nodes.add(path.substring(startIndex));
+ }
+ return nodes.toArray(new String[0]);
+ }
+
+ public static void nextNode(String path, List<String> nodes, char enclose) {
+ int endIndex = path.indexOf(enclose, i + 1);
+ // if a double quotes with escape character
+ while (endIndex != -1 && path.charAt(endIndex - 1) == '\\') {
+ endIndex = path.indexOf(enclose, endIndex + 1);
+ }
+ if (endIndex != -1 && (endIndex == path.length() - 1 || path.charAt(endIndex + 1) == ',')) {
+ nodes.add(path.substring(startIndex + 1, endIndex));
+ i = endIndex + 1;
+ startIndex = endIndex + 2;
+ } else {
+ throw new IllegalArgumentException("Illegal csv line" + path);
+ }
+ }
+
}
diff --git a/cli/src/test/java/org/apache/iotdb/cli/AbstractScript.java b/cli/src/test/java/org/apache/iotdb/cli/AbstractScript.java
index 1843c08..b938efb 100644
--- a/cli/src/test/java/org/apache/iotdb/cli/AbstractScript.java
+++ b/cli/src/test/java/org/apache/iotdb/cli/AbstractScript.java
@@ -38,6 +38,10 @@ public abstract class AbstractScript {
if (line == null) {
break;
} else {
+ // remove thing after "connection refused", only for test
+ if(line.contains("Connection refused")) {
+ line = line.substring(0, line.indexOf("Connection refused") + "Connection refused".length());
+ }
outputList.add(line);
}
}
diff --git a/cli/src/test/java/org/apache/iotdb/tool/CsvLineSplitTest.java b/cli/src/test/java/org/apache/iotdb/tool/CsvLineSplitTest.java
new file mode 100644
index 0000000..ec9011e
--- /dev/null
+++ b/cli/src/test/java/org/apache/iotdb/tool/CsvLineSplitTest.java
@@ -0,0 +1,32 @@
+/*
+ * 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.tool;
+
+import org.junit.Assert;
+import org.junit.Test;
+
+public class CsvLineSplitTest {
+
+ @Test
+ public void testSplit() {
+ Assert.assertArrayEquals(new String[]{"", "a", "b", "c", "\\\""}, ImportCsv.splitCsvLine(",a,b,c,\"\\\"\""));
+ Assert.assertArrayEquals(new String[]{"", "a", "b", "\\'"}, ImportCsv.splitCsvLine(",a,b,\"\\'\""));
+ }
+
+}
diff --git a/cli/src/test/java/org/apache/iotdb/tool/ExportCsvTestIT.java b/cli/src/test/java/org/apache/iotdb/tool/ExportCsvTestIT.java
index 96ea030..2673979 100644
--- a/cli/src/test/java/org/apache/iotdb/tool/ExportCsvTestIT.java
+++ b/cli/src/test/java/org/apache/iotdb/tool/ExportCsvTestIT.java
@@ -28,11 +28,11 @@ import org.junit.Test;
public class ExportCsvTestIT extends AbstractScript{
@Before
- public void setUp() throws Exception {
+ public void setUp() {
}
@After
- public void tearDown() throws Exception {
+ public void tearDown() {
}
@Test
@@ -50,8 +50,8 @@ public class ExportCsvTestIT extends AbstractScript{
final String[] output = {"````````````````````````````````````````````````",
"Starting IoTDB Client Export Script",
"````````````````````````````````````````````````",
- "Encounter an error when exporting data, error is: Connection Error, "
- + "please check whether the network is available or the server has started."};
+ "Connect failed because org.apache.thrift.transport.TTransportException: "
+ + "java.net.ConnectException: Connection refused"};
String dir = getCliPath();
ProcessBuilder builder = new ProcessBuilder("cmd.exe", "/c",
dir + File.separator + "tools" + File.separator + "export-csv.bat",
@@ -64,8 +64,8 @@ public class ExportCsvTestIT extends AbstractScript{
final String[] output = {"------------------------------------------",
"Starting IoTDB Client Export Script",
"------------------------------------------",
- "Encounter an error when exporting data, error is: Connection Error, "
- + "please check whether the network is available or the server has started."};
+ "Connect failed because org.apache.thrift.transport.TTransportException: "
+ + "java.net.ConnectException: Connection refused"};
String dir = getCliPath();
ProcessBuilder builder = new ProcessBuilder("sh",
dir + File.separator + "tools" + File.separator + "export-csv.sh",
diff --git a/cli/src/test/java/org/apache/iotdb/tool/ImportCsvTestIT.java b/cli/src/test/java/org/apache/iotdb/tool/ImportCsvTestIT.java
index 4b3bb26..6f7d2f4 100644
--- a/cli/src/test/java/org/apache/iotdb/tool/ImportCsvTestIT.java
+++ b/cli/src/test/java/org/apache/iotdb/tool/ImportCsvTestIT.java
@@ -28,11 +28,11 @@ import org.junit.Test;
public class ImportCsvTestIT extends AbstractScript {
@Before
- public void setUp() throws Exception {
+ public void setUp() {
}
@After
- public void tearDown() throws Exception {
+ public void tearDown() {
}
@Test
@@ -50,8 +50,8 @@ public class ImportCsvTestIT extends AbstractScript {
final String[] output = {"````````````````````````````````````````````````",
"Starting IoTDB Client Import Script",
"````````````````````````````````````````````````",
- "Encounter an error when importing data, error is: Connection Error, please check whether "
- + "the network is available or the server has started."};
+ "Encounter an error when connecting to server, because org.apache.thrift.transport.TTransportException: "
+ + "java.net.ConnectException: Connection refused"};
String dir = getCliPath();
ProcessBuilder builder = new ProcessBuilder("cmd.exe", "/c",
dir + File.separator + "tools" + File.separator + "import-csv.bat",
@@ -64,8 +64,8 @@ public class ImportCsvTestIT extends AbstractScript {
final String[] output = {"------------------------------------------",
"Starting IoTDB Client Import Script",
"------------------------------------------",
- "Encounter an error when importing data, error is: Connection Error, please check whether "
- + "the network is available or the server has started."};
+ "Encounter an error when connecting to server, because org.apache.thrift.transport.TTransportException: "
+ + "java.net.ConnectException: Connection refused"};
String dir = getCliPath();
ProcessBuilder builder = new ProcessBuilder("sh",
dir + File.separator + "tools" + File.separator + "import-csv.sh",
diff --git a/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/AbstractScript.java b/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/AbstractScript.java
index ff7bef2..4280839 100644
--- a/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/AbstractScript.java
+++ b/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/AbstractScript.java
@@ -49,7 +49,7 @@ public abstract class AbstractScript {
p.destroy();
- System.out.println("Expected output:");
+ System.out.println("should contains:");
for (String s : output) {
System.out.println(s);
}
@@ -59,7 +59,7 @@ public abstract class AbstractScript {
System.out.println(out);
}
- assertTrue(actualOutput.get(actualOutput.size() - 1).startsWith(output[output.length - 1]));
+ assertTrue(actualOutput.get(actualOutput.size() - 1).contains(output[output.length - 1]));
}
protected String getCliPath() {
diff --git a/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/ExportCsvTestIT.java b/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/ExportCsvTestIT.java
new file mode 100644
index 0000000..5185097
--- /dev/null
+++ b/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/ExportCsvTestIT.java
@@ -0,0 +1,193 @@
+/*
+ * 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.cross.tests.tools.importCsv;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+
+import java.io.BufferedReader;
+import java.io.BufferedWriter;
+import java.io.File;
+import java.io.FileReader;
+import java.io.FileWriter;
+import java.io.IOException;
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.iotdb.db.utils.EnvironmentUtils;
+import org.apache.iotdb.rpc.IoTDBConnectionException;
+import org.apache.iotdb.rpc.StatementExecutionException;
+import org.apache.iotdb.session.Session;
+import org.junit.After;
+import org.junit.Before;
+import org.junit.Test;
+
+public class ExportCsvTestIT extends AbstractScript{
+
+ private final String SQL_FILE = "target" + File.separator + "sql.txt";
+
+ private final String EXPORT_FILE = "target" + File.separator + "dump0.csv";
+
+ @Before
+ public void setUp() {
+ EnvironmentUtils.closeStatMonitor();
+ EnvironmentUtils.envSetUp();
+ }
+
+ @After
+ public void tearDown() throws Exception {
+ EnvironmentUtils.cleanEnv();
+ }
+
+ @Override
+ protected void testOnWindows() throws IOException {
+ final String[] output = {
+ "------------------------------------------",
+ "Starting IoTDB Client Export Script",
+ "------------------------------------------",
+ "Start to export data from sql statement",
+ "successfully",
+ };
+ String dir = getCliPath();
+ ProcessBuilder builder = new ProcessBuilder("cmd.exe", "/c",
+ dir + File.separator + "tools" + File.separator + "export-csv.bat",
+ "-h", "127.0.0.1", "-p", "6667", "-u", "root", "-pw", "root", "-td", "./target",
+ "-s", SQL_FILE);
+ testOutput(builder, output);
+ }
+
+ @Override
+ protected void testOnUnix() throws IOException {
+ final String[] output = {
+ "------------------------------------------",
+ "Starting IoTDB Client Export Script",
+ "------------------------------------------",
+ "Start to export data from sql statement",
+ "successfully",
+ };
+ String dir = getCliPath();
+ ProcessBuilder builder = new ProcessBuilder("sh",
+ dir + File.separator + "tools" + File.separator + "export-csv.sh",
+ "-h", "127.0.0.1", "-p", "6667", "-u", "root", "-pw", "root", "-td", "./target",
+ "-s", SQL_FILE);
+ testOutput(builder, output);
+ }
+
+ private boolean generateSQLFile(String[] sql) {
+ BufferedWriter writer;
+ try {
+ writer = new BufferedWriter(new FileWriter(SQL_FILE));
+ writer.write("");
+ for (String s : sql) {
+ writer.write(s);
+ writer.newLine();
+ }
+ writer.flush();
+ writer.close();
+ return true;
+ } catch (IOException e) {
+ System.out.println("failed to create test csv");
+ }
+ return false;
+ }
+
+ @Test
+ public void testRawDataQuery() throws IOException, StatementExecutionException, IoTDBConnectionException {
+ final String[] expectCsv = new String[]{"Time,root.sg1.d1.s3,root.sg1.d1.s1,root.sg1.d1.s2",
+ "abbe's,1.0,\"\\\"abc\\\",aa\""};
+ prepareData();
+ String os = System.getProperty("os.name").toLowerCase();
+ String[] sql = {"select * from root"};
+ assertTrue(generateSQLFile(sql));
+ if (os.startsWith("windows")) {
+ testOnWindows();
+ } else {
+ testOnUnix();
+ }
+ FileReader fileReader = new FileReader(EXPORT_FILE);
+ BufferedReader br = new BufferedReader(fileReader);
+ String line = br.readLine();
+ int i = 0;
+ while(line != null) {
+ if(i == 0) {
+ assertEquals(expectCsv[i], line);
+ } else {
+ String lineWithoutTime = line.substring(line.indexOf(',') + 1);
+ assertEquals(expectCsv[i], lineWithoutTime);
+ }
+ i++;
+ line = br.readLine();
+ }
+ File file = new File(EXPORT_FILE);
+ if (file.exists()) {
+ file.delete();
+ }
+ }
+
+ @Test
+ public void testAggregationQuery() throws StatementExecutionException, IoTDBConnectionException, IOException {
+ final String[] expectCsv = new String[]{"Time,count(root.sg1.d1.s3),count(root.sg1.d1.s1),count(root.sg1.d1.s2)",
+ "1,1,1"};
+ prepareData();
+ String os = System.getProperty("os.name").toLowerCase();
+ String[] sql = {"select count(*) from root"};
+ generateSQLFile(sql);
+ if (os.startsWith("windows")) {
+ testOnWindows();
+ } else {
+ testOnUnix();
+ }
+ FileReader fileReader = new FileReader(EXPORT_FILE);
+ BufferedReader br = new BufferedReader(fileReader);
+ String line = br.readLine();
+ int i = 0;
+ while(line != null) {
+ if(i == 0) {
+ assertEquals(expectCsv[i], line);
+ } else {
+ String lineWithoutTime = line.substring(line.indexOf(',') + 1);
+ assertEquals(expectCsv[i], lineWithoutTime);
+ }
+ i++;
+ line = br.readLine();
+ }
+ File file = new File(EXPORT_FILE);
+ if (file.exists()) {
+ file.delete();
+ }
+ }
+
+ private void prepareData() throws IoTDBConnectionException, StatementExecutionException {
+ Session session = new Session("127.0.0.1", 6667, "root", "root");
+ session.open();
+
+ String deviceId = "root.sg1.d1";
+ List<String> measurements = new ArrayList<>();
+ measurements.add("s1");
+ measurements.add("s2");
+ measurements.add("s3");
+
+ List<String> values = new ArrayList<>();
+ values.add("1.0");
+ values.add("\"abc\",aa");
+ values.add("abbe's");
+ session.insertRecord(deviceId, 1L, measurements, values);
+ }
+
+}
diff --git a/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/ImportCsvTestIT.java b/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/ImportCsvTestIT.java
index b439de7..22924b5 100644
--- a/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/ImportCsvTestIT.java
+++ b/cross-tests/src/test/java/org/apache/iotdb/cross/tests/tools/importCsv/ImportCsvTestIT.java
@@ -20,11 +20,8 @@
package org.apache.iotdb.cross.tests.tools.importCsv;
-import org.apache.iotdb.db.utils.EnvironmentUtils;
-import org.apache.iotdb.jdbc.Config;
-import org.junit.After;
-import org.junit.Before;
-import org.junit.Test;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
import java.io.BufferedWriter;
import java.io.File;
@@ -32,16 +29,23 @@ import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
-
-import static org.junit.Assert.assertTrue;
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.iotdb.db.utils.EnvironmentUtils;
+import org.apache.iotdb.jdbc.Config;
+import org.junit.After;
+import org.junit.Before;
+import org.junit.Test;
public class ImportCsvTestIT extends AbstractScript {
private final String CSV_FILE = "target" + File.separator + "test.csv";
- private static String[] sqls = new String[]{
+ private static final String[] sqls = new String[]{
"SET STORAGE GROUP TO root.fit.d1",
"SET STORAGE GROUP TO root.fit.d2",
"SET STORAGE GROUP TO root.fit.p",
@@ -53,11 +57,9 @@ public class ImportCsvTestIT extends AbstractScript {
};
@Before
- public void setUp() throws Exception {
+ public void setUp() {
EnvironmentUtils.closeStatMonitor();
EnvironmentUtils.envSetUp();
-
- createSchema();
}
@After
@@ -80,7 +82,23 @@ public class ImportCsvTestIT extends AbstractScript {
}
@Test
- public void test() throws IOException {
+ public void test() throws IOException, ClassNotFoundException {
+ createSchema();
+ String os = System.getProperty("os.name").toLowerCase();
+ assertTrue(generateTestCSV());
+ if (os.startsWith("windows")) {
+ testOnWindows();
+ } else {
+ testOnUnix();
+ }
+ File file = new File(CSV_FILE);
+ if (file.exists()) {
+ file.delete();
+ }
+ }
+
+ @Test
+ public void testWithoutCreateSchema() throws IOException, ClassNotFoundException {
String os = System.getProperty("os.name").toLowerCase();
assertTrue(generateTestCSV());
if (os.startsWith("windows")) {
@@ -89,6 +107,71 @@ public class ImportCsvTestIT extends AbstractScript {
testOnUnix();
}
File file = new File(CSV_FILE);
+ Class.forName(Config.JDBC_DRIVER_NAME);
+ try (Connection connection = DriverManager
+ .getConnection(Config.IOTDB_URL_PREFIX + "127.0.0.1:6667/", "root", "root");
+ Statement statement = connection.createStatement()) {
+ if(statement.execute("select * from root")) {
+ ResultSet resultSet = statement.getResultSet();
+ testResult(resultSet, 6,3);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ if (file.exists()) {
+ file.delete();
+ }
+ }
+
+ @Test
+ public void testBigCsvFile() throws IOException, ClassNotFoundException {
+ String os = System.getProperty("os.name").toLowerCase();
+ assertTrue(generateBigCsvFile());
+ if (os.startsWith("windows")) {
+ testOnWindows();
+ } else {
+ testOnUnix();
+ }
+ File file = new File(CSV_FILE);
+ Class.forName(Config.JDBC_DRIVER_NAME);
+ try (Connection connection = DriverManager
+ .getConnection(Config.IOTDB_URL_PREFIX + "127.0.0.1:6667/", "root", "root");
+ Statement statement = connection.createStatement()) {
+ if(statement.execute("select s1 from root.fit.d1")) {
+ ResultSet resultSet = statement.getResultSet();
+ testResult(resultSet, 2,25000);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ if (file.exists()) {
+ file.delete();
+ }
+ }
+
+ private static void testResult(ResultSet resultSet, int expectedColumnNumber, int expectedRowNumber) throws SQLException {
+ if (resultSet != null) {
+ final ResultSetMetaData metaData = resultSet.getMetaData();
+ final int columnCount = metaData.getColumnCount();
+ assertEquals(expectedColumnNumber, columnCount);
+ int actualRowNumber = 0;
+ while (resultSet.next()) {
+ actualRowNumber++;
+ }
+ assertEquals(expectedRowNumber, actualRowNumber);
+ }
+ }
+
+ @Test
+ public void testImportHeaderCSV() throws IOException {
+ String os = System.getProperty("os.name").toLowerCase();
+ assertTrue(generateHeaderTestCSV());
+ if (os.startsWith("windows")) {
+ testOnWindows();
+ } else {
+ testOnUnix();
+ }
+ File file = new File(CSV_FILE);
if (file.exists()) {
file.delete();
}
@@ -98,8 +181,51 @@ public class ImportCsvTestIT extends AbstractScript {
String[] csvText = {
"Time,root.fit.d1.s1,root.fit.d1.s2,root.fit.d2.s1,root.fit.d2.s3,root.fit.p.s1",
"1,100,'hello',200,300,400",
- "2,500,'world',600,700,800",
- "3,900,'IoTDB',1000,1100,1200"};
+ "2,500,'',600,700,800",
+ "3,900,'Io\"TDB',1000,1100,1200"};
+ BufferedWriter writer;
+ try {
+ writer = new BufferedWriter(new FileWriter(CSV_FILE));
+ writer.write("");
+ for (String s : csvText) {
+ writer.write(s);
+ writer.newLine();
+ }
+ writer.flush();
+ writer.close();
+ return true;
+ } catch (IOException e) {
+ System.out.println("failed to create test csv");
+ }
+ return false;
+ }
+
+ private boolean generateBigCsvFile() {
+ List<String> csvText = new ArrayList<>();
+ csvText.add("Time,root.fit.d1.s1,root.fit.d1.s2,root.fit.d2.s1");
+ for(int i = 0; i < 25000; i++) {
+ csvText.add(i+","+i+","+i+","+i);
+ }
+ BufferedWriter writer;
+ try {
+ writer = new BufferedWriter(new FileWriter(CSV_FILE));
+ writer.write("");
+ for (String s : csvText) {
+ writer.write(s);
+ writer.newLine();
+ }
+ writer.flush();
+ writer.close();
+ return true;
+ } catch (IOException e) {
+ System.out.println("failed to create test csv");
+ }
+ return false;
+ }
+
+ private boolean generateHeaderTestCSV() {
+ String[] csvText = {
+ "Time,root.fit.d1.\"s1\",root.fit.d1.s2,root.fit.d2.s1,root.fit.d2.s3,root.fit.p.s1"};
BufferedWriter writer;
try {
writer = new BufferedWriter(new FileWriter(CSV_FILE));
diff --git a/docs/UserGuide/System Tools/CSV Tool.md b/docs/UserGuide/System Tools/CSV Tool.md
new file mode 100644
index 0000000..29fc167
--- /dev/null
+++ b/docs/UserGuide/System Tools/CSV Tool.md
@@ -0,0 +1,85 @@
+<!--
+
+ 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.
+
+-->
+# Csv tool
+
+Csv tool is that you can import csv file into IoTDB or export csv file from IoTDB.
+
+## Usage of import-csv.sh
+
+### Create metadata (optional)
+
+```
+SET STORAGE GROUP TO root.fit.d1;
+SET STORAGE GROUP TO root.fit.d2;
+SET STORAGE GROUP TO root.fit.p;
+CREATE TIMESERIES root.fit.d1.s1 WITH DATATYPE=INT32,ENCODING=RLE;
+CREATE TIMESERIES root.fit.d1.s2 WITH DATATYPE=TEXT,ENCODING=PLAIN;
+CREATE TIMESERIES root.fit.d2.s1 WITH DATATYPE=INT32,ENCODING=RLE;
+CREATE TIMESERIES root.fit.d2.s3 WITH DATATYPE=INT32,ENCODING=RLE;
+CREATE TIMESERIES root.fit.p.s1 WITH DATATYPE=INT32,ENCODING=RLE;
+```
+
+IoTDB has the ability of type inference, so it is not necessary to create metadata before data import. However, we still recommend creating metadata before importing data using the CSV import tool, as this can avoid unnecessary type conversion errors.
+
+### An example of import csv file
+
+```
+Time,root.fit.d1.s1,root.fit.d1.s2,root.fit.d2.s1,root.fit.d2.s3,root.fit.p.s1
+1,100,hello,200,300,400
+2,500,world,600,700,800
+3,900,"hello, \"world\"",1000,1100,1200
+```
+> Note that the following special characters in fields need to be checked before importing:
+> 1. `,` : fields containing `,` should be quoted by a pair of `"` or a pair of `'`.
+> 2. `"` : `"` in fields should be replaced by `\"` or fields should be enclosed by `'`.
+> 3. `'` : `'` in fields should be replaced by `\'` or fields should be enclosed by `"`.
+
+### Run import shell
+```
+# Unix/OS X
+> tools/import-csv.sh -h <ip> -p <port> -u <username> -pw <password> -f <xxx.csv>
+
+# Windows
+> tools\import-csv.bat -h <ip> -p <port> -u <username> -pw <password> -f <xxx.csv>
+```
+
+## Usage of export-csv.sh
+
+### Run export shell
+```
+# Unix/OS X
+> tools/export-csv.sh -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format> -s <sqlfile>]
+
+# Windows
+> tools\export-csv.bat -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format> -s <sqlfile>]
+```
+
+After running export shell, you need to input a data query, like `select * from root`. or specify a sql file. If you have multi sql in a sql file, sql should be split by new line character.
+
+an example sql file:
+
+```
+select * from root.fit.d1
+select * from root.sg1.d1
+```
+> Note that if fields exported by the export tool have the following special characters:
+> 1. `,`: the field will be enclosed by `"`.
+> 2. `"`: the field will be enclosed by `"` and the original characters `"` in the field will be replaced by `\"`.
diff --git a/docs/zh/UserGuide/System Tools/CSV Tool.md b/docs/zh/UserGuide/System Tools/CSV Tool.md
new file mode 100644
index 0000000..2817c3f
--- /dev/null
+++ b/docs/zh/UserGuide/System Tools/CSV Tool.md
@@ -0,0 +1,88 @@
+<!--
+
+ 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.
+
+-->
+
+# CSV 工具
+
+Csv工具是您可以导入csv文件到IoTDB或从IoTDB导出csv文件。
+
+## 使用 import-csv.sh
+
+### 创建元数据（可选）
+
+```
+SET STORAGE GROUP TO root.fit.d1;
+SET STORAGE GROUP TO root.fit.d2;
+SET STORAGE GROUP TO root.fit.p;
+CREATE TIMESERIES root.fit.d1.s1 WITH DATATYPE=INT32,ENCODING=RLE;
+CREATE TIMESERIES root.fit.d1.s2 WITH DATATYPE=TEXT,ENCODING=PLAIN;
+CREATE TIMESERIES root.fit.d2.s1 WITH DATATYPE=INT32,ENCODING=RLE;
+CREATE TIMESERIES root.fit.d2.s3 WITH DATATYPE=INT32,ENCODING=RLE;
+CREATE TIMESERIES root.fit.p.s1 WITH DATATYPE=INT32,ENCODING=RLE;
+```
+IoTDB具有类型推断的能力，因此在数据导入前创建元数据不是必须的。但我们仍然推荐在使用CSV导入工具导入数据前创建元数据，因为这可以避免不必要的类型转换错误。
+
+### 从 csv 文件导入数据的示例
+
+```
+Time,root.fit.d1.s1,root.fit.d1.s2,root.fit.d2.s1,root.fit.d2.s3,root.fit.p.s1
+1,100,hello,200,300,400
+2,500,world,600,700,800
+3,900,"hello, \"world\"",1000,1100,1200
+```
+
+> 注意，在导入数据前，需要特殊处理下列的字符：
+> 1. `,` : 包含`,`的字段需要使用单引号或者双引号括起来
+> 2. `"` : "字段中的`"`需要被替换成转义字符`\"`或者用`\'`将字段括起来。
+> 3. `'` : "字段中的`'`需要被替换成转义字符`\'`或者用`\"`将字段括起来。
+
+### 运行 import shell
+```
+# Unix/OS X
+> tools/import-csv.sh -h <ip> -p <port> -u <username> -pw <password> -f <xxx.csv>
+
+# Windows
+> tools\import-csv.bat -h <ip> -p <port> -u <username> -pw <password> -f <xxx.csv>
+```
+
+## 使用 export-csv.sh
+
+### 运行 export shell
+
+```
+# Unix/OS X
+> tools/export-csv.sh -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format> -s <sqlfile>]
+
+# Windows
+> tools\export-csv.bat -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format> -s <sqlfile>]
+```
+
+在运行导出脚本之后，您需要输入一些查询或指定一些sql文件。如果在一个sql文件中有多个sql, sql应该被换行符分割。
+
+一个sql文件例子
+
+```
+select * from root.fit.d1
+select * from root.sg1.d1
+```
+
+> 注意，如果导出字段存在如下特殊字符：
+> 1. `,` : 整个字段会被用`"`括起来。
+> 2. `"` : 整个字段会被用`"`括起来且`"`会被替换为`\"`。
\ No newline at end of file