当前位置：首页 > news >正文

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

news 来源：原创 2024/9/20 15:41:49

文章目录

一、前文
二、导出
- 2.1 准备导出服务器
- 2.2 导出命令
- 2.3 执行命令
- 2.4 sql文件
三、导入
- 3.1 准备导入服务器
- 3.2 上传sql文件
- 3.3 导入命令
- 3.4 执行命令
五、参考

一、前文

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

创建数据库

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

写入数据库

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.

查询数据库

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

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>]

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 insertsql>] [-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 timeformat 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 thehead 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

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!

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

删除数据

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

查询数据库，没有数据

IoTDB> select status from root.test.test
+----+
|Time|
+----+
+----+
Empty set.
It costs 0.184s

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>]

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 thisdirectory (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(onlycsv 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 byoption:"boolean=text,int=long, ...-linesPerFailedFile <Lines Per FailedFile>   Lines per failed file

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!

登录数据库

[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

查询数据是否成功导入

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