IoTDB 入门教程 基础篇⑪——Data导入导出工具

一、前文

IoTDB入门教程——导读

如果是使用开源版本IoTDB，那么有如下三种数据同步和备份的方法，可供选择。

• IoTDB 入门教程 基础篇⑨——TsFile导入导出工具
• IoTDB 入门教程 基础篇⑩——CSV导入导出工具
• IoTDB 入门教程 基础篇⑪——Data导入导出工具

如果是使用企业版本IoTDB（TimechoDB），那么一种数据同步的方法就够了，简单方便更好用。

• IoTDB 入门教程 企业篇③——数据同步和增量备份

• 数据库备份与迁移是数据库运维中的核心任务，其重要性不言而喻。
• 确保备份过程既简单快捷又稳定可靠，对于保障数据安全与业务连续性至关重要。

• 注意：IoTDB V1.3.2及之后版本使用tools/export-data、tools/import-data
• 注意：IoTDB V1.3.1及之前版本使用tools/export-csv、tools/import-csv

tools/import-data支持两种数据格式

• CSV：纯文本格式，存储格式化数据，需按照下文指定 CSV 格式进行构造
• SQL：包含自定义 SQL 语句的文件

二、导出

2.1 准备导出服务器

• 登录数据库

[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash sbin/start-cli.sh 
---------------------
Starting IoTDB Cli
---------------------
 _____       _________  ______   ______    
|_   _|     |  _   _  ||_   _ `.|_   _ \   
  | |   .--.|_/ | | \_|  | | `. \ | |_) |  
  | | / .'`\ \  | |      | |  | | |  __'.  
 _| |_| \__. | _| |_    _| |_.' /_| |__) | 
|_____|'.__.' |_____|  |______.'|_______/  version 1.3.2 (Build: aa0ff4a)
                                           

Successfully login at 127.0.0.1:6667
1
2
3
4
5
6
7
8
9
10
11
12
13

• 创建数据库

IoTDB> CREATE DATABASE root.test.test
Msg: The statement is executed successfully.
IoTDB> show databases
+--------------+----+-----------------------+---------------------+---------------------+
|      Database| TTL|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|
+--------------+----+-----------------------+---------------------+---------------------+
|root.test.test|null|                      1|                    1|            604800000|
+--------------+----+-----------------------+---------------------+---------------------+
Total line number = 1
It costs 0.006s
1
2
3
4
5
6
7
8
9
10

• 写入数据库

IoTDB> INSERT INTO root.test.test(status) values(1)
Msg: The statement is executed successfully.
IoTDB> INSERT INTO root.test.test(status) values(2)
Msg: The statement is executed successfully.
IoTDB> INSERT INTO root.test.test(status) values(3)
Msg: The statement is executed successfully.
1
2
3
4
5
6

• 查询数据库

IoTDB> select * from root.test.test order by time desc
+-----------------------------+---------------------+
|                         Time|root.test.test.status|
+-----------------------------+---------------------+
|2024-08-04T09:29:08.893+08:00|                  3.0|
|2024-08-04T09:29:06.757+08:00|                  2.0|
|2024-08-04T09:29:04.169+08:00|                  1.0|
+-----------------------------+---------------------+
Total line number = 3
It costs 0.141s
1
2
3
4
5
6
7
8
9
10

2.2 导出命令

bash tools/export-data.sh  -h <ip> -p <port> -u <username> -pw <password> -td <directory> [-tf <time-format> -datatype <true/false> -q <query command> -s <sql file>]
1

usage: ExportData -h <host> -p <port> -u <username> [-pw <password>] -td <targetDirectory>
       [-f <targetFile>] [-s <sqlfile>] [-tf <timeformat>] [-tz <timeZone>] [-datatype
       <datatype>] [-q <queryCommand>] [-type <exportType>] [-aligned <export aligned insert
       sql>] [-linesPerFile <Lines Per File>] [-help] [-t <arg>]
 -h,--host <host>                       Host Name (required)
 -p,--port <port>                       Port (required)
 -u,--username <username>               Username (required)
 -pw,--password <password>              Password (required)
 -td <targetDirectory>                  Target File Directory (required)
 -f <targetFile>                        Export file name (optional)
 -s <sqlfile>                           SQL File Path (optional)
 -tf <timeformat>                       Output time Format in csv file. You can choose 1)
                                        timestamp, number, long 2) ISO8601, default 3)
                                        user-defined pattern like yyyy-MM-dd HH:mm:ss,
                                        default ISO8601.
                                        OutPut timestamp in sql file, No matter what time
                                        format is set(optional)
 -tz <timeZone>                         Time Zone eg. +08:00 or -01:00 (optional)
 -datatype <datatype>                   Will the data type of timeseries be printed in the
                                        head line of the CSV file?
                                        You can choose true) or false) . (optional)
 -q <queryCommand>                      The query command that you want to execute.
                                        (optional)
 -type <exportType>                     Export file type ?
                                        You can choose csv) or sql) . (optional)
 -aligned <export aligned insert sql>   Whether export to sql of aligned (only sql optional)
 -linesPerFile <Lines Per File>         Lines per dump file.
 -help,--help                           Display help information
 -t,--timeout <arg>                     Timeout for session query
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

2.3 执行命令

[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash tools/export-data.sh -h '127.0.0.1' -p 6667 -u root -pw root -td ./ -q 'select * from root.test.test order by time desc' -type sql
------------------------------------------
Starting IoTDB Client Export Script
------------------------------------------
Export completely!
1
2
3
4
5

2.4 sql文件

• dump0_0.sql就是本次导出的sql文件。

三、导入

3.1 准备导入服务器

• 登录数据库

[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash sbin/start-cli.sh 
---------------------
Starting IoTDB Cli
---------------------
 _____       _________  ______   ______    
|_   _|     |  _   _  ||_   _ `.|_   _ \   
  | |   .--.|_/ | | \_|  | | `. \ | |_) |  
  | | / .'`\ \  | |      | |  | | |  __'.  
 _| |_| \__. | _| |_    _| |_.' /_| |__) | 
|_____|'.__.' |_____|  |______.'|_______/  version 1.3.2 (Build: aa0ff4a)
                                           

Successfully login at 127.0.0.1:6667
1
2
3
4
5
6
7
8
9
10
11
12
13

• 删除数据

IoTDB> DELETE FROM root.test.test.status where time < 40000000000000
Msg: The statement is executed successfully.
1
2

• 查询数据库，没有数据

IoTDB> select status from root.test.test
+----+
|Time|
+----+
+----+
Empty set.
It costs 0.184s
1
2
3
4
5
6
7

3.2 上传sql文件

将在2.4小节获得的dump0_0.sql上传到导入服务器中

3.3 导入命令

bash tools/import-data.sh -h <ip> -p <port> -u <username> -pw <password> -s <xxx.csv/sql> [-fd <./failedDirectory> -aligned <true/false> -batch <int> -tp <ms/ns/us> -typeInfer <boolean=text,float=double...> -lpf <int>]
1

usage: ImportData -h <host> -p <port> -u <username> [-pw <password>] -f <file or folder>
       [-fd <failed file directory>] [-aligned <use the aligned interface>] [-help] [-tz
       <timeZone>] [-batch <batch point size>] [-tp <timestamp precision (ms/us/ns)>]
       [-typeInfer <type infer>] [-linesPerFailedFile <Lines Per FailedFile>]
 -h,--host <host>                             Host Name (required)
 -p,--port <port>                             Port (required)
 -u,--username <username>                     Username (required)
 -pw,--password <password>                    Password (required)
 -f <file or folder>                          If input a file path, load a csv file,
                                              otherwise load all csv file under this
                                              directory (required)
 -fd <failed file directory>                  Specifying a directory to save failed file,
                                              default YOUR_CSV_FILE_PATH (optional)
 -aligned <use the aligned interface>         Whether to use the interface of aligned(only
                                              csv optional)
 -help,--help                                 Display help information
 -tz <timeZone>                               Time Zone eg. +08:00 or -01:00 (optional)
 -batch <batch point size>                    100000 (optional)
 -tp <timestamp precision (ms/us/ns)>         Timestamp precision (ms/us/ns)
 -typeInfer <type infer>                      Define type info by
                                              option:"boolean=text,int=long, ...
 -linesPerFailedFile <Lines Per FailedFile>   Lines per failed file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3.4 执行命令

• 导入sql文件

[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash tools/import-data.sh -f ./dump0_0.sql -h 127.0.0.1 -p 6667 -u root -pw root
------------------------------------------
Starting IoTDB Client Import Script
------------------------------------------
dump0_0.sql Import completely!
1
2
3
4
5

• 登录数据库

[root@iZgw0bdpdtyqxyz77dha9nZ apache-iotdb-1.3.2-all-bin]# bash sbin/start-cli.sh 
---------------------
Starting IoTDB Cli
---------------------
 _____       _________  ______   ______    
|_   _|     |  _   _  ||_   _ `.|_   _ \   
  | |   .--.|_/ | | \_|  | | `. \ | |_) |  
  | | / .'`\ \  | |      | |  | | |  __'.  
 _| |_| \__. | _| |_    _| |_.' /_| |__) | 
|_____|'.__.' |_____|  |______.'|_______/  version 1.3.2 (Build: aa0ff4a)
                                           

Successfully login at 127.0.0.1:6667
1
2
3
4
5
6
7
8
9
10
11
12
13

• 查询数据是否成功导入

IoTDB> select * from root.test.test order by time desc
+-----------------------------+---------------------+
|                         Time|root.test.test.status|
+-----------------------------+---------------------+
|2024-08-04T09:29:08.893+08:00|                  3.0|
|2024-08-04T09:29:06.757+08:00|                  2.0|
|2024-08-04T09:29:04.169+08:00|                  1.0|
+-----------------------------+---------------------+
Total line number = 3
It costs 0.073s
1
2
3
4
5
6
7
8
9
10

五、参考

数据导入导出脚本 | IoTDB Website

觉得好，就一键三连呗（点赞+收藏+关注）