This is an automated email from the ASF dual-hosted git repository.
haonan 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 e3d48b5e1a0 Add the import and export function of SQl files on the
basis of CSV import and export scripts (#12248)
e3d48b5e1a0 is described below
commit e3d48b5e1a0658706b67de59b0004347b711a96a
Author: Summer <[email protected]>
AuthorDate: Fri Mar 29 17:35:31 2024 +0800
Add the import and export function of SQl files on the basis of CSV import
and export scripts (#12248)
Co-authored-by: 2b3c511 <[email protected]>
---
...{ExportCsvTestIT.java => ExportDataTestIT.java} | 59 ++++++-
...{ImportCsvTestIT.java => ImportDataTestIT.java} | 6 +-
iotdb-client/cli/pom.xml | 4 +
.../tools/{export-csv.bat => export-data.bat} | 2 +-
.../tools/{export-csv.sh => export-data.sh} | 2 +-
.../tools/{import-csv.bat => import-data.bat} | 2 +-
.../tools/{import-csv.sh => import-data.sh} | 2 +-
.../java/org/apache/iotdb/cli/AbstractCli.java | 4 +-
...{AbstractCsvTool.java => AbstractDataTool.java} | 7 +-
.../iotdb/tool/{ExportCsv.java => ExportData.java} | 178 +++++++++++++++++++--
.../iotdb/tool/{ImportCsv.java => ImportData.java} | 64 +++++++-
...riteCsvFileTest.java => WriteDataFileTest.java} | 6 +-
12 files changed, 303 insertions(+), 33 deletions(-)
diff --git
a/integration-test/src/test/java/org/apache/iotdb/tools/it/ExportCsvTestIT.java
b/integration-test/src/test/java/org/apache/iotdb/tools/it/ExportDataTestIT.java
similarity index 77%
rename from
integration-test/src/test/java/org/apache/iotdb/tools/it/ExportCsvTestIT.java
rename to
integration-test/src/test/java/org/apache/iotdb/tools/it/ExportDataTestIT.java
index e4aa06917c4..cb95adb363d 100644
---
a/integration-test/src/test/java/org/apache/iotdb/tools/it/ExportCsvTestIT.java
+++
b/integration-test/src/test/java/org/apache/iotdb/tools/it/ExportDataTestIT.java
@@ -40,7 +40,7 @@ import java.util.List;
@RunWith(IoTDBTestRunner.class)
@Category({LocalStandaloneIT.class, ClusterIT.class})
-public class ExportCsvTestIT extends AbstractScript {
+public class ExportDataTestIT extends AbstractScript {
private static String ip;
private static String port;
@@ -80,7 +80,7 @@ public class ExportCsvTestIT extends AbstractScript {
new ProcessBuilder(
"cmd.exe",
"/c",
- toolsPath + File.separator + "export-csv.bat",
+ toolsPath + File.separator + "export-data.bat",
"-h",
ip,
"-p",
@@ -106,7 +106,7 @@ public class ExportCsvTestIT extends AbstractScript {
new ProcessBuilder(
"cmd.exe",
"/c",
- toolsPath + File.separator + "export-csv.bat",
+ toolsPath + File.separator + "export-data.bat",
"-h",
ip,
"-p",
@@ -124,6 +124,32 @@ public class ExportCsvTestIT extends AbstractScript {
"%^errorlevel%");
builder1.environment().put("CLASSPATH", libPath);
testOutput(builder1, output1, 0);
+
+ prepareData();
+
+ final String[] output2 = {"Export completely!"};
+ ProcessBuilder builder2 =
+ new ProcessBuilder(
+ "cmd.exe",
+ "/c",
+ toolsPath + File.separator + "export-data.bat",
+ "-h",
+ ip,
+ "-p",
+ port,
+ "-u",
+ "root",
+ "-pw",
+ "root",
+ "-td",
+ "target",
+ "-q",
+ "select * from root.test.t2 where time > 1 and time <
1000000000000",
+ "&",
+ "exit",
+ "%^errorlevel%");
+ builder2.environment().put("CLASSPATH", libPath);
+ testOutput(builder2, output2, 0);
}
@Override
@@ -133,7 +159,7 @@ public class ExportCsvTestIT extends AbstractScript {
ProcessBuilder builder =
new ProcessBuilder(
"bash",
- toolsPath + File.separator + "export-csv.sh",
+ toolsPath + File.separator + "export-data.sh",
"-h",
ip,
"-p",
@@ -156,7 +182,7 @@ public class ExportCsvTestIT extends AbstractScript {
ProcessBuilder builder1 =
new ProcessBuilder(
"bash",
- toolsPath + File.separator + "export-tsfile.sh",
+ toolsPath + File.separator + "export-data.sh",
"-h",
ip,
"-p",
@@ -171,6 +197,29 @@ public class ExportCsvTestIT extends AbstractScript {
"select * from root.**");
builder1.environment().put("CLASSPATH", libPath);
testOutput(builder1, output1, 0);
+
+ prepareData();
+
+ final String[] output2 = {"Export completely!"};
+ // -h 127.0.0.1 -p 6667 -u root -pw root -td ./ -q "select * from root.**"
+ ProcessBuilder builder2 =
+ new ProcessBuilder(
+ "bash",
+ toolsPath + File.separator + "export-data.sh",
+ "-h",
+ ip,
+ "-p",
+ port,
+ "-u",
+ "root",
+ "-pw",
+ "root",
+ "-td",
+ "target",
+ "-q",
+ "select * from root.**");
+ builder2.environment().put("CLASSPATH", libPath);
+ testOutput(builder2, output2, 0);
}
public void prepareData() {
diff --git
a/integration-test/src/test/java/org/apache/iotdb/tools/it/ImportCsvTestIT.java
b/integration-test/src/test/java/org/apache/iotdb/tools/it/ImportDataTestIT.java
similarity index 94%
rename from
integration-test/src/test/java/org/apache/iotdb/tools/it/ImportCsvTestIT.java
rename to
integration-test/src/test/java/org/apache/iotdb/tools/it/ImportDataTestIT.java
index 58527217259..350aa9918e6 100644
---
a/integration-test/src/test/java/org/apache/iotdb/tools/it/ImportCsvTestIT.java
+++
b/integration-test/src/test/java/org/apache/iotdb/tools/it/ImportDataTestIT.java
@@ -36,7 +36,7 @@ import java.io.IOException;
@RunWith(IoTDBTestRunner.class)
@Category({LocalStandaloneIT.class, ClusterIT.class})
-public class ImportCsvTestIT extends AbstractScript {
+public class ImportDataTestIT extends AbstractScript {
private static String ip;
@@ -80,7 +80,7 @@ public class ImportCsvTestIT extends AbstractScript {
new ProcessBuilder(
"cmd.exe",
"/c",
- toolsPath + File.separator + "import-csv.bat",
+ toolsPath + File.separator + "import-data.bat",
"-h",
ip,
"-p",
@@ -106,7 +106,7 @@ public class ImportCsvTestIT extends AbstractScript {
ProcessBuilder builder =
new ProcessBuilder(
"bash",
- toolsPath + File.separator + "import-csv.sh",
+ toolsPath + File.separator + "import-data.sh",
"-h",
ip,
"-p",
diff --git a/iotdb-client/cli/pom.xml b/iotdb-client/cli/pom.xml
index 7f2a7d9c171..9e14c6239ee 100644
--- a/iotdb-client/cli/pom.xml
+++ b/iotdb-client/cli/pom.xml
@@ -92,6 +92,10 @@
<groupId>org.antlr</groupId>
<artifactId>antlr4-runtime</artifactId>
</dependency>
+ <dependency>
+ <groupId>org.apache.commons</groupId>
+ <artifactId>commons-collections4</artifactId>
+ </dependency>
<dependency>
<groupId>org.apache.thrift</groupId>
<artifactId>libthrift</artifactId>
diff --git a/iotdb-client/cli/src/assembly/resources/tools/export-csv.bat
b/iotdb-client/cli/src/assembly/resources/tools/export-data.bat
similarity index 99%
rename from iotdb-client/cli/src/assembly/resources/tools/export-csv.bat
rename to iotdb-client/cli/src/assembly/resources/tools/export-data.bat
index f4212330357..fe35bfc215e 100644
--- a/iotdb-client/cli/src/assembly/resources/tools/export-csv.bat
+++ b/iotdb-client/cli/src/assembly/resources/tools/export-data.bat
@@ -31,7 +31,7 @@ pushd %~dp0..
if NOT DEFINED IOTDB_HOME set IOTDB_HOME=%CD%
popd
-if NOT DEFINED MAIN_CLASS set MAIN_CLASS=org.apache.iotdb.tool.ExportCsv
+if NOT DEFINED MAIN_CLASS set MAIN_CLASS=org.apache.iotdb.tool.ExportData
if NOT DEFINED JAVA_HOME goto :err
@REM
-----------------------------------------------------------------------------
diff --git a/iotdb-client/cli/src/assembly/resources/tools/export-csv.sh
b/iotdb-client/cli/src/assembly/resources/tools/export-data.sh
similarity index 97%
rename from iotdb-client/cli/src/assembly/resources/tools/export-csv.sh
rename to iotdb-client/cli/src/assembly/resources/tools/export-data.sh
index 41daf679e37..8aa7491b945 100644
--- a/iotdb-client/cli/src/assembly/resources/tools/export-csv.sh
+++ b/iotdb-client/cli/src/assembly/resources/tools/export-data.sh
@@ -53,7 +53,7 @@ for f in ${IOTDB_HOME}/lib/*.jar; do
CLASSPATH=${CLASSPATH}":"$f
done
-MAIN_CLASS=org.apache.iotdb.tool.ExportCsv
+MAIN_CLASS=org.apache.iotdb.tool.ExportData
"$JAVA" -DIOTDB_HOME=${IOTDB_HOME} -cp "$CLASSPATH" "$MAIN_CLASS" "$@"
exit $?
\ No newline at end of file
diff --git a/iotdb-client/cli/src/assembly/resources/tools/import-csv.bat
b/iotdb-client/cli/src/assembly/resources/tools/import-data.bat
similarity index 99%
rename from iotdb-client/cli/src/assembly/resources/tools/import-csv.bat
rename to iotdb-client/cli/src/assembly/resources/tools/import-data.bat
index bc657ab9eea..d4270297c93 100644
--- a/iotdb-client/cli/src/assembly/resources/tools/import-csv.bat
+++ b/iotdb-client/cli/src/assembly/resources/tools/import-data.bat
@@ -31,7 +31,7 @@ pushd %~dp0..
if NOT DEFINED IOTDB_HOME set IOTDB_HOME=%CD%
popd
-if NOT DEFINED MAIN_CLASS set MAIN_CLASS=org.apache.iotdb.tool.ImportCsv
+if NOT DEFINED MAIN_CLASS set MAIN_CLASS=org.apache.iotdb.tool.ImportData
if NOT DEFINED JAVA_HOME goto :err
@REM
-----------------------------------------------------------------------------
diff --git a/iotdb-client/cli/src/assembly/resources/tools/import-csv.sh
b/iotdb-client/cli/src/assembly/resources/tools/import-data.sh
similarity index 97%
rename from iotdb-client/cli/src/assembly/resources/tools/import-csv.sh
rename to iotdb-client/cli/src/assembly/resources/tools/import-data.sh
index c90365d30c9..97102b854c7 100644
--- a/iotdb-client/cli/src/assembly/resources/tools/import-csv.sh
+++ b/iotdb-client/cli/src/assembly/resources/tools/import-data.sh
@@ -53,7 +53,7 @@ for f in ${IOTDB_HOME}/lib/*.jar; do
CLASSPATH=${CLASSPATH}":"$f
done
-MAIN_CLASS=org.apache.iotdb.tool.ImportCsv
+MAIN_CLASS=org.apache.iotdb.tool.ImportData
"$JAVA" -DIOTDB_HOME=${IOTDB_HOME} -cp "$CLASSPATH" "$MAIN_CLASS" "$@"
exit $?
\ No newline at end of file
diff --git
a/iotdb-client/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
b/iotdb-client/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
index d3adc7e7951..18bb7fa538d 100644
--- a/iotdb-client/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
+++ b/iotdb-client/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
@@ -26,7 +26,7 @@ import org.apache.iotdb.jdbc.IoTDBJDBCResultSet;
import org.apache.iotdb.rpc.IoTDBConnectionException;
import org.apache.iotdb.rpc.RpcUtils;
import org.apache.iotdb.service.rpc.thrift.ServerProperties;
-import org.apache.iotdb.tool.ImportCsv;
+import org.apache.iotdb.tool.ImportData;
import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.Option;
@@ -529,7 +529,7 @@ public abstract class AbstractCli {
}
ctx.getPrinter().println(cmd.split(" ")[1]);
try {
- return ImportCsv.importFromTargetPath(
+ return ImportData.importFromTargetPath(
host,
Integer.parseInt(port),
username,
diff --git
a/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/AbstractCsvTool.java
b/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/AbstractDataTool.java
similarity index 98%
rename from
iotdb-client/cli/src/main/java/org/apache/iotdb/tool/AbstractCsvTool.java
rename to
iotdb-client/cli/src/main/java/org/apache/iotdb/tool/AbstractDataTool.java
index a1f45c8051e..57ccada4cb6 100644
--- a/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/AbstractCsvTool.java
+++ b/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/AbstractDataTool.java
@@ -39,7 +39,7 @@ import java.io.PrintWriter;
import java.time.ZoneId;
import java.util.List;
-public abstract class AbstractCsvTool {
+public abstract class AbstractDataTool {
protected static final String HOST_ARGS = "h";
protected static final String HOST_NAME = "host";
@@ -116,12 +116,13 @@ public abstract class AbstractCsvTool {
protected static String timeZoneID;
protected static String timeFormat;
+ protected static String exportType;
protected static Session session;
private static final IoTPrinter ioTPrinter = new IoTPrinter(System.out);
- private static final Logger LOGGER =
LoggerFactory.getLogger(AbstractCsvTool.class);
+ private static final Logger LOGGER =
LoggerFactory.getLogger(AbstractDataTool.class);
- protected AbstractCsvTool() {}
+ protected AbstractDataTool() {}
protected static String checkRequiredArg(String arg, String name,
CommandLine commandLine)
throws ArgsErrorException {
diff --git
a/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ExportCsv.java
b/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ExportData.java
similarity index 69%
rename from iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ExportCsv.java
rename to iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ExportData.java
index 3e0414b0eca..bae7195a9b7 100644
--- a/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ExportCsv.java
+++ b/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ExportData.java
@@ -30,6 +30,8 @@ import org.apache.iotdb.rpc.RpcUtils;
import org.apache.iotdb.rpc.StatementExecutionException;
import org.apache.iotdb.session.Session;
import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
+import org.apache.iotdb.tsfile.read.common.Field;
+import org.apache.iotdb.tsfile.read.common.Path;
import org.apache.iotdb.tsfile.read.common.RowRecord;
import org.apache.commons.cli.CommandLine;
@@ -39,25 +41,31 @@ 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.collections4.CollectionUtils;
+import org.apache.commons.lang3.ObjectUtils;
import org.apache.thrift.TException;
import org.jline.reader.LineReader;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
+import java.io.FileWriter;
import java.io.IOException;
import java.time.Instant;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
+import java.util.Locale;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
/**
* Export CSV file.
*
* @version 1.0.0 20170719
*/
-public class ExportCsv extends AbstractCsvTool {
+public class ExportData extends AbstractDataTool {
private static final String TARGET_DIR_ARGS = "td";
private static final String TARGET_DIR_NAME = "targetDirectory";
@@ -74,6 +82,14 @@ public class ExportCsv extends AbstractCsvTool {
private static final String QUERY_COMMAND_ARGS = "q";
private static final String QUERY_COMMAND_NAME = "queryCommand";
+ private static final String EXPORT_TYPE_ARGS = "type";
+
+ private static final String EXPORT_TYPE_NAME = "exportType";
+
+ private static final String EXPORT_SQL_TYPE_NAME = "sql";
+
+ private static final String ALIGNED_ARGS = "aligned";
+ private static final String ALIGNED_NAME = "create the aligned insert sql";
private static final String LINES_PER_FILE_ARGS = "linesPerFile";
private static final String LINES_PER_FILE_ARGS_NAME = "Lines Per File";
@@ -193,6 +209,7 @@ public class ExportCsv extends AbstractCsvTool {
targetFile = commandLine.getOptionValue(TARGET_FILE_ARGS);
needDataTypePrinted =
Boolean.valueOf(commandLine.getOptionValue(DATA_TYPE_ARGS));
queryCommand = commandLine.getOptionValue(QUERY_COMMAND_ARGS);
+ exportType = commandLine.getOptionValue(EXPORT_TYPE_ARGS);
String timeoutString = commandLine.getOptionValue(TIMEOUT_ARGS);
if (timeoutString != null) {
timeout = Long.parseLong(timeoutString);
@@ -287,6 +304,22 @@ public class ExportCsv extends AbstractCsvTool {
.build();
options.addOption(opQuery);
+ Option opTypeQuery =
+ Option.builder(EXPORT_TYPE_ARGS)
+ .argName(EXPORT_TYPE_NAME)
+ .hasArg()
+ .desc("Export file type ?" + '\n' + "You can choose csv) or sql) .
(optional)")
+ .build();
+ options.addOption(opTypeQuery);
+
+ Option opAligned =
+ Option.builder(ALIGNED_ARGS)
+ .argName(ALIGNED_NAME)
+ .hasArg()
+ .desc("Whether to use the interface of aligned (optional)")
+ .build();
+ options.addOption(opAligned);
+
Option opLinesPerFile =
Option.builder(LINES_PER_FILE_ARGS)
.argName(LINES_PER_FILE_ARGS_NAME)
@@ -337,24 +370,31 @@ public class ExportCsv extends AbstractCsvTool {
* @param index used to create dump file name
*/
private static void dumpResult(String sql, int index) {
+ if (EXPORT_SQL_TYPE_NAME.equalsIgnoreCase(exportType)) {
+ legalCheck(sql);
+ }
final String path = targetDirectory + targetFile + index;
try {
SessionDataSet sessionDataSet = session.executeQueryStatement(sql,
timeout);
List<Object> headers = new ArrayList<>();
List<String> names = sessionDataSet.getColumnNames();
List<String> types = sessionDataSet.getColumnTypes();
- if (Boolean.TRUE.equals(needDataTypePrinted)) {
- for (int i = 0; i < names.size(); i++) {
- if (!"Time".equals(names.get(i)) && !"Device".equals(names.get(i))) {
- headers.add(String.format("%s(%s)", names.get(i), types.get(i)));
- } else {
- headers.add(names.get(i));
+ if (EXPORT_SQL_TYPE_NAME.equalsIgnoreCase(exportType)) {
+ writeSqlFile(sessionDataSet, path, names, linesPerFile);
+ } else {
+ if (Boolean.TRUE.equals(needDataTypePrinted)) {
+ for (int i = 0; i < names.size(); i++) {
+ if (!"Time".equals(names.get(i)) &&
!"Device".equals(names.get(i))) {
+ headers.add(String.format("%s(%s)", names.get(i), types.get(i)));
+ } else {
+ headers.add(names.get(i));
+ }
}
+ } else {
+ headers.addAll(names);
}
- } else {
- headers.addAll(names);
+ writeCsvFile(sessionDataSet, path, headers, linesPerFile);
}
- writeCsvFile(sessionDataSet, path, headers, linesPerFile);
sessionDataSet.closeOperationHandle();
ioTPrinter.println("Export completely!");
} catch (StatementExecutionException | IoTDBConnectionException |
IOException e) {
@@ -362,6 +402,16 @@ public class ExportCsv extends AbstractCsvTool {
}
}
+ private static void legalCheck(String sql) {
+ String aggregatePattern =
+
"\\b(count|sum|avg|extreme|max_value|min_value|first_value|last_value|max_time|min_time|stddev|stddev_pop|stddev_samp|variance|var_pop|var_samp|max_by|min_by)\\b\\s*\\(";
+ Pattern pattern = Pattern.compile(aggregatePattern,
Pattern.CASE_INSENSITIVE);
+ Matcher matcher = pattern.matcher(sql.toUpperCase(Locale.ROOT));
+ if (matcher.find()) {
+ ioTPrinter.println("The sql you entered is invalid, please don't use
aggregate query.");
+ }
+ }
+
public static String timeTrans(Long time) {
switch (timeFormat) {
case "default":
@@ -420,4 +470,112 @@ public class ExportCsv extends AbstractCsvTool {
csvPrinterWrapper.close();
}
}
+
+ public static void writeSqlFile(
+ SessionDataSet sessionDataSet, String filePath, List<String> headers,
int linesPerFile)
+ throws IOException, IoTDBConnectionException,
StatementExecutionException {
+ int fileIndex = 0;
+ String deviceName = null;
+ boolean writeNull = false;
+ List<String> seriesList = new ArrayList<>(headers);
+ if (CollectionUtils.isEmpty(headers) || headers.size() <= 1) {
+ writeNull = true;
+ } else {
+ if (headers.contains("Device")) {
+ seriesList.remove("Time");
+ seriesList.remove("Device");
+ } else {
+ Path path = new Path(seriesList.get(1), true);
+ deviceName = path.getDevice();
+ seriesList.remove("Time");
+ for (int i = 0; i < seriesList.size(); i++) {
+ String series = seriesList.get(i);
+ path = new Path(series, true);
+ seriesList.set(i, path.getMeasurement());
+ }
+ }
+ }
+ boolean hasNext = true;
+ while (hasNext) {
+ int i = 0;
+ final String finalFilePath = filePath + "_" + fileIndex + ".sql";
+ FileWriter writer = new FileWriter(finalFilePath);
+ if (writeNull) {
+ break;
+ }
+ while (i++ < linesPerFile) {
+ if (sessionDataSet.hasNext()) {
+ RowRecord rowRecord = sessionDataSet.next();
+ List<Field> fields = rowRecord.getFields();
+ List<String> headersTemp = new ArrayList<>(seriesList);
+ List<String> timeseries = new ArrayList<>();
+ if (headers.contains("Device")) {
+ deviceName = fields.get(0).toString();
+ if (deviceName.startsWith("root.__system")) {
+ continue;
+ }
+ for (String header : headersTemp) {
+ timeseries.add(deviceName + "." + header);
+ }
+ } else {
+ if (headers.get(1).startsWith("root.__system")) {
+ continue;
+ }
+ timeseries.addAll(headers);
+ timeseries.remove(0);
+ }
+ String sqlMiddle = null;
+ if (Boolean.TRUE.equals(ALIGNED_ARGS)) {
+ sqlMiddle = " ALIGNED VALUES (" +
timeTrans(rowRecord.getTimestamp()) + ",";
+ } else {
+ sqlMiddle = " VALUES (" + timeTrans(rowRecord.getTimestamp()) +
",";
+ }
+ List<String> values = new ArrayList<>();
+ if (headers.contains("Device")) {
+ fields.remove(0);
+ }
+ for (int index = 0; index < fields.size(); index++) {
+ RowRecord next =
+ session
+ .executeQueryStatement("SHOW TIMESERIES " +
timeseries.get(index), timeout)
+ .next();
+ if (ObjectUtils.isNotEmpty(next)) {
+ List<Field> timeseriesList = next.getFields();
+ String value = fields.get(index).toString();
+ if (value.equals("null")) {
+ headersTemp.remove(seriesList.get(index));
+ continue;
+ }
+ if
("TEXT".equalsIgnoreCase(timeseriesList.get(3).getStringValue())) {
+ values.add("\"" + value + "\"");
+ } else {
+ values.add(value);
+ }
+ } else {
+ headersTemp.remove(seriesList.get(index));
+ continue;
+ }
+ }
+ if (CollectionUtils.isNotEmpty(headersTemp)) {
+ writer.write(
+ "INSERT INTO "
+ + deviceName
+ + " (TIMESTAMP,"
+ + String.join(",", headersTemp)
+ + ")"
+ + sqlMiddle
+ + String.join(",", values)
+ + ");\n");
+ }
+
+ } else {
+ hasNext = false;
+ break;
+ }
+ }
+ fileIndex++;
+ writer.flush();
+ writer.close();
+ }
+ }
}
diff --git
a/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ImportCsv.java
b/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ImportData.java
similarity index 94%
rename from iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ImportCsv.java
rename to iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ImportData.java
index 81dc8561029..5e33f956bee 100644
--- a/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ImportCsv.java
+++ b/iotdb-client/cli/src/main/java/org/apache/iotdb/tool/ImportData.java
@@ -27,6 +27,7 @@ import org.apache.iotdb.db.utils.DateTimeUtils;
import org.apache.iotdb.db.utils.constant.SqlConstant;
import org.apache.iotdb.exception.ArgsErrorException;
import org.apache.iotdb.isession.SessionDataSet;
+import org.apache.iotdb.jdbc.Config;
import org.apache.iotdb.rpc.IoTDBConnectionException;
import org.apache.iotdb.rpc.StatementExecutionException;
import org.apache.iotdb.session.Session;
@@ -47,10 +48,16 @@ import org.apache.commons.csv.CSVRecord;
import org.apache.commons.lang3.StringUtils;
import org.apache.thrift.annotation.Nullable;
+import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
+import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
@@ -66,6 +73,7 @@ import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;
+import static org.apache.iotdb.jdbc.Config.IOTDB_ERROR_PREFIX;
import static org.apache.iotdb.tsfile.file.metadata.enums.TSDataType.BOOLEAN;
import static org.apache.iotdb.tsfile.file.metadata.enums.TSDataType.DOUBLE;
import static org.apache.iotdb.tsfile.file.metadata.enums.TSDataType.FLOAT;
@@ -73,7 +81,7 @@ import static
org.apache.iotdb.tsfile.file.metadata.enums.TSDataType.INT32;
import static org.apache.iotdb.tsfile.file.metadata.enums.TSDataType.INT64;
import static org.apache.iotdb.tsfile.file.metadata.enums.TSDataType.TEXT;
-public class ImportCsv extends AbstractCsvTool {
+public class ImportData extends AbstractDataTool {
private static final String FILE_ARGS = "f";
private static final String FILE_NAME = "file or folder";
@@ -90,6 +98,8 @@ public class ImportCsv extends AbstractCsvTool {
private static final String CSV_SUFFIXS = "csv";
private static final String TXT_SUFFIXS = "txt";
+ private static final String SQL_SUFFIXS = "sql";
+
private static final String TIMESTAMP_PRECISION_ARGS = "tp";
private static final String TIMESTAMP_PRECISION_NAME = "timestamp precision
(ms/us/ns)";
@@ -122,6 +132,7 @@ public class ImportCsv extends AbstractCsvTool {
private static final String DATATYPE_TEXT = "text";
private static final String DATATYPE_NULL = "null";
+ private static int fetchSize = 1000;
private static final String INSERT_CSV_MEET_ERROR_MSG = "Meet error when
insert csv because ";
@@ -377,7 +388,11 @@ public class ImportCsv extends AbstractCsvTool {
File file = new File(targetPath);
if (file.isFile()) {
- importFromSingleFile(file);
+ if (file.getName().endsWith(SQL_SUFFIXS)) {
+ importFromSqlFile(file);
+ } else {
+ importFromSingleFile(file);
+ }
} else if (file.isDirectory()) {
File[] files = file.listFiles();
if (files == null) {
@@ -386,7 +401,11 @@ public class ImportCsv extends AbstractCsvTool {
for (File subFile : files) {
if (subFile.isFile()) {
- importFromSingleFile(subFile);
+ if (subFile.getName().endsWith(SQL_SUFFIXS)) {
+ importFromSqlFile(subFile);
+ } else {
+ importFromSingleFile(subFile);
+ }
}
}
} else {
@@ -442,6 +461,45 @@ public class ImportCsv extends AbstractCsvTool {
}
}
+ private static void importFromSqlFile(File file) {
+ try (BufferedReader br = new BufferedReader(new
FileReader(file.getAbsolutePath()))) {
+ String line;
+ while ((line = br.readLine()) != null) {
+ executeSql(line);
+ }
+ ioTPrinter.println(file.getName() + " Import completely!");
+ } catch (IOException e) {
+ ioTPrinter.println("SQL file read exception because: " + e.getMessage());
+ }
+ }
+
+ private static void executeSql(String sql) {
+ try (Statement statement = getConnection().createStatement()) {
+ statement.setFetchSize(fetchSize);
+ statement.execute(sql.trim());
+ } catch (SQLException e) {
+ ioTPrinter.println(IOTDB_ERROR_PREFIX + " Can't execute sql because " +
e.getMessage());
+ System.exit(CODE_ERROR);
+ }
+ }
+
+ private static Connection getConnection() {
+ // JDBC driver name and database URL
+ String driver = org.apache.iotdb.jdbc.IoTDBDriver.class.getName();
+ String url = Config.IOTDB_URL_PREFIX + host + ":" + port + "/";
+
+ Connection connection = null;
+ try {
+ Class.forName(driver);
+ connection = DriverManager.getConnection(url, username, password);
+ } catch (ClassNotFoundException e) {
+ e.printStackTrace();
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ return connection;
+ }
+
/**
* if the data is aligned by time, the data will be written by this method.
*
diff --git
a/iotdb-client/cli/src/test/java/org/apache/iotdb/tool/WriteCsvFileTest.java
b/iotdb-client/cli/src/test/java/org/apache/iotdb/tool/WriteDataFileTest.java
similarity index 87%
rename from
iotdb-client/cli/src/test/java/org/apache/iotdb/tool/WriteCsvFileTest.java
rename to
iotdb-client/cli/src/test/java/org/apache/iotdb/tool/WriteDataFileTest.java
index c5b27191693..c198b530c4e 100644
--- a/iotdb-client/cli/src/test/java/org/apache/iotdb/tool/WriteCsvFileTest.java
+++
b/iotdb-client/cli/src/test/java/org/apache/iotdb/tool/WriteDataFileTest.java
@@ -27,7 +27,7 @@ import java.util.List;
import static org.junit.Assert.assertTrue;
-public class WriteCsvFileTest {
+public class WriteDataFileTest {
@Test
public void writeCsvFileTest() {
List<String> headerNames =
@@ -38,7 +38,7 @@ public class WriteCsvFileTest {
List<Object> row3 = new ArrayList<>(Arrays.asList(3, "100", "hello
world!!!", false));
ArrayList<List<Object>> records = new ArrayList<>(Arrays.asList(row1,
row2, row3));
- assertTrue(AbstractCsvTool.writeCsvFile(headerNames, records,
"./target/test0.csv"));
- assertTrue(AbstractCsvTool.writeCsvFile(null, records,
"./target/test1.csv"));
+ assertTrue(AbstractDataTool.writeCsvFile(headerNames, records,
"./target/test0.csv"));
+ assertTrue(AbstractDataTool.writeCsvFile(null, records,
"./target/test1.csv"));
}
}
