Data Export

1. Function Overview

IoTDB supports two methods for data export:

Data Export Tool: export-data.sh/bat is located in the tools directory. It can export the query results of specified SQL statements into CSV, SQL, and TsFile (open-source time-series file format) files.
PIPE Framework-based TsFileBackup: tsfile-backup.sh/bat is located in the tools directory. It can export specified data files into TsFile format using the PIPE framework.

2. Data Export Tool

2.1 Common Parameters

Short	Full Parameter	Description	Required	Default
`-ft`	`--file_type`	Export file type: `csv`, `sql`, `tsfile`.	Yes	-
`-h`	`--host`	Hostname of the IoTDB server.	No	`127.0.0.1`
`-p`	`--port`	Port number of the IoTDB server.	No	`6667`
`-u`	`--username`	Username for authentication.	No	`root`
`-pw`	`--password`	Password for authentication. Supported for hidden input since V2.0.9.1	No	`TimechoDB@2021`(Before V2.0.6 it is root)
`-sql_dialect`	`--sql_dialect`	Select server model : tree or table	No	tree
`-db`	`--database`	The target database to be exported only takes effect when `-sql_dialect` is of the table type.	Yes when `-sql_dialect = table`	-
`-table`	`--table`	The target table to be exported only takes effect when `-sql_dialect` is of the table type. If the `-q` parameter is specified, this parameter will not take effect. If the export type is tsfile/sql, this parameter is mandatory.	No	-
`-start_time`	`--start_time`	The start time of the data to be exported only takes effect when `-sql_dialect` is of the table type. If `-q` is specified, this parameter will not take effect. The supported time formats are the same as those for the `-tf` parameter.	No	-
`-end_time`	`--end_time`	The end time of the data to be exported only takes effect when `-sql_dialect` is set to the table type. If `-q` is specified, this parameter will not take effect.	No	-
`-t`	`--target`	Target directory for the output files. If the path does not exist, it will be created.	Yes	-
`-pfn`	`--prefix_file_name`	Prefix for the exported file names. For example, `abc` will generate files like `abc_0.tsfile`, `abc_1.tsfile`.	No	`dump_0.tsfile`
`-q`	`--query`	SQL query command to execute. Starting from v2.0.8, semicolons in SQL statements are automatically removed, and query execution proceeds normally.	No	-
`-timeout`	`--query_timeout`	Query timeout in milliseconds (ms).	No	`-1` (before v2.0.8) `Long.MAX_VALUE` (v2.0.8 and later) (Range: `-1~Long.MAX_VALUE`)
`-help`	`--help`	Display help information.	No	-
`-usessl`	`--use_ssl`	Use SSL protocol. Supported since V2.0.9.1	No	-
`-ts`	`--trust_store`	Trust store. Supports hidden input. Supported since V2.0.9.1	No	-
`-tpw`	`--trust_store_password`	Trust store password. Supports hidden input. Supported since V2.0.9.1	No	-

2.2 CSV Format

2.2.1 Command

# Unix/OS X
> tools/export-data.sh -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
                -t <target_directory> [-pfn <prefix_file_name>] [-dt <datatype>] [-lpf <lines_per_file>] [-tf <time_format>] 
               [-tz <timezone>] [-q <query_command>] [-timeout <query_timeout>]
# Windows
# Before version V2.0.4.x
> tools\export-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
                -t <target_directory> [-pfn <prefix_file_name>] [-dt <datatype>] [-lpf <lines_per_file>] [-tf <time_format>] 
               [-tz <timezone>] [-q <query_command>] [-timeout <query_timeout>]
               
# V2.0.4.x and later versions
> tools\windows\export-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
                -t <target_directory> [-pfn <prefix_file_name>] [-dt <datatype>] [-lpf <lines_per_file>] [-tf <time_format>] 
               [-tz <timezone>] [-q <query_command>] [-timeout <query_timeout>]

2.2.2 CSV-Specific Parameters

Short	Full Parameter	Description	Required	Default
`-dt`	`--datatype`	Whether to include data types in the CSV file header (`true` or `false`).	No	`false`
`-lpf`	`--lines_per_file`	Number of rows per exported file.	No	`10000` (Range：0～Integer.Max=2147483647)
`-tf`	`--time_format`	Time format for the CSV file. Options: 1) Timestamp (numeric, long), 2) ISO8601 (default), 3) Custom pattern (e.g., `yyyy-MM-dd HH:mm:ss`). SQL file timestamps are unaffected by this setting.	No	`ISO8601`
`-tz`	`--timezone`	Timezone setting (e.g., `+08:00`, `-01:00`).	No	System default

2.2.3 Examples

# Valid Example
> export-data.sh -ft csv -sql_dialect table -t /path/export/dir -db database1 -q "select * from table1"

# Error Example
> export-data.sh -ft csv -sql_dialect table -t /path/export/dir -q "select * from table1"
Parse error: Missing required option: db

2.3 SQL Format

2.3.1 Command

# Unix/OS X
> tools/export-data.sh -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
          -t <target_directory> [-pfn <prefix_file_name>] [-aligned <export aligned insert sql>]
          -lpf <lines_per_file> - [-tf <time_format>] [-tz <timezone>] [-q <query_command>] [-timeout <query_timeout>]
      
# Windows
# Before version V2.0.4.x 
> tools\export-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host> -p <port> -u <username> -pw <password>] 
          -t <target_directory> [-pfn <prefix_file_name>  -aligned <export aligned insert sql>  
          -lpf <lines_per_file> -tf <time_format> -tz <timezone> -q <query_command> -timeout <query_timeout>]
          
# V2.0.4.x and later versions
> tools\windows\export-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host> -p <port> -u <username> -pw <password>] 
          -t <target_directory> [-pfn <prefix_file_name>  -aligned <export aligned insert sql>  
          -lpf <lines_per_file> -tf <time_format> -tz <timezone> -q <query_command> -timeout <query_timeout>]

2.3.2 SQL-Specific Parameters

Short	Full Parameter	Description	Required	Default
`-aligned`	`--use_aligned`	Whether to export as aligned SQL format (`true` or `false`).	No	`true`
`-lpf`	`--lines_per_file`	Number of rows per exported file.	No	`10000` (Range：0～Integer.Max=2147483647)
`-tf`	`--time_format`	Time format for the CSV file. Options: 1) Timestamp (numeric, long), 2) ISO8601 (default), 3) Custom pattern (e.g., `yyyy-MM-dd HH:mm:ss`). SQL file timestamps are unaffected by this setting.	No	`ISO8601`
`-tz`	`--timezone`	Timezone setting (e.g., `+08:00`, `-01:00`).	No	System default

2.3.3 Examples

# Valid Example
> export-data.sh -ft sql -sql_dialect table -t /path/export/dir -db database1 -start_time 1

# Error Example
> export-data.sh -ft sql -sql_dialect table -t /path/export/dir -start_time 1
Parse error: Missing required option: db

2.4 TsFile Format

2.4.1 Command

# Unix/OS X
> tools/export-data.sh -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host>] [-p <port>] [-u <username>] [-pw <password>]
        -t <target_directory> [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>]
      
# Windows
# Before version V2.0.4.x 
> tools\export-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host>] [-p <port>] [-u <username>] [-pw <password>]
        -t <target_directory> [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>]
        
# V2.0.4.x and later versions
> tools\windows\export-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
                [-start_time<start_time>] [-end_time<end_time>] [-h <host>] [-p <port>] [-u <username>] [-pw <password>]
        -t <target_directory> [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>]

2.4.2 TsFile-Specific Parameters

None

2.4.3 Examples

# Valid Example
> /tools/export-data.sh -ft tsfile -sql_dialect table -t /path/export/dir -db database1 -start_time 0

# Error Example
> /tools/export-data.sh -ft tsfile -sql_dialect table -t /path/export/dir -start_time 0
Parse error: Missing required option: db

3. TsFileBackup Based on PIPE Framework

Since V2.0.9.2, IoTDB supports the tsfile-backup.sh/bat script. This script can automatically generate and send the CREATE PIPE SQL command to the server, exporting specified data files to TsFile format.

Notes:

To use this script, contact the Timecho Team to obtain the JAR package(tsfile-remote-sink-<version>-jar-with-dependencies.jar), and place it in a path accessible to IoTDB (e.g., all Data Node hosts).
This script supports exporting Object-type data to TsFile files.

3.1 Execution Commands

# Unix/OS X
> tools/tsfile-backup.sh [-sql_dialect <sql_dialect>] [-h <host>] [-p <port>]
       [-u <username>] [-pw <password>] [-path <path>] [-db <db>] [-table
       <table>] [-s <start_time>] [-e <end_time>] [-t <target_directory>] 
       [-th <target_host>] [-tu <target_host_user>] [-tp <target_host_port>]
       [--rate_limit] [--plugin_jar] [-help]

# Windows
> tools\windows\tsfile-backup.bat [-sql_dialect <sql_dialect>] [-h <host>] [-p <port>]
       [-u <username>] [-pw <password>] [-path <path>]  [-db <db>] [-table
       <table>] [-s <start_time>] [-e <end_time>] [-t <target_directory>] 
       [-th <target_host>] [-tu <target_host_user>] [-tp <target_host_port>]
       [--rate_limit] [--plugin_jar] [-help]

3.2 Script Parameters

Abbreviation	Full Name	Description	Required	Default
`-sql_dialect`	`--sql_dialect`	Specifies the data model type. Valid values: `tree` (Tree Model) or `table` (Table Model).	Yes	-
`-h`	`--host`	Local host address (IP of the IoTDB instance where the data resides).	No	`127.0.0.1`
`-p`	`--port`	Port number for the IoTDB RPC service.	No	`6667`
`-u`	`--user`	Username for IoTDB authentication.	No	`root`
`-pw`	`--password`	Password for IoTDB authentication (hidden input supported).	No	`root`
`-t`	`--target`	Export target directory. In SCP mode, this is an absolute physical path on the remote server. TsFile and associated Object directories will be exported here.	Yes	-
`-db`	`--database`	Database name (optional for Table Model).	No	`.*`
`-table`	`--table`	Table name (optional for Table Model).	No	`.*`
`-s`	`--start_time`	Start time (ISO8601 format e.g. `2026-01-01T00:00:00` or millisecond timestamp). Only data from this time onwards is exported.	No	-
`-e`	`--end_time`	End time (same format as above). Only data before this time is exported.	No	-
`-th`	`--target_host`	Remote target host IP. If specified, the script automatically configures Pipe to use SCP for data transfer.	No	-
`-tu`	`--target_host_user`	Username for SSH/SCP login to the remote server.	No	-
`-tpw`	`--target_host_pw`	Password for remote authentication (hidden input supported).	No	-
`-tp`	`--target_host_port`	Remote SSH port.	No	`22`
`--rate_limit`	`--rate_limit`	Transfer rate limit (unit: Bytes/s) to prevent excessive bandwidth usage.	No	-
`--plugin_jar`	`--plugin_jar`	Path to the Pipe plugin JAR file.	No	-
`--object-parallelism`	`--object-parallelism`	Specifies the maximum parallelism for object file transmission.	No	-
`--object-batch-size`	`--object-batch-size`	Limits the total byte size of each object file upload batch, used to control memory usage and single SCP transfer size.	No	-
`-help`	`--help`	Show help information.	No	-

3.3 Execution Examples

Example 1: SCP Remote Export (Send Data to Another Server)

./tsfile-backup.sh -sql_dialect table -db test_db -t /remote/archive/ -th 192.168.1.100 -tu backup_user -tpw ComplexPass123!

Example 2: Remote Object Data Export with Rate Limiting

./tsfile-backup.sh -sql_dialect table -t /mnt/backup/ -th 10.0.0.5 -tu iot_admin -tpw Admin@2026 --rate_limit 5242880

Example 3: Specify Pipe Plugin JAR Directory

./tsfile-backup.sh -sql_dialect table -db test -table .* -tu luoluoyuyu -tpw -t /tmp/backup --plugin_jar /local/lib/tsfile-remote-sink-2.0.8-SNAPSHOT-jar-with-dependencies.jar

Note: When exporting Object-type data in SCP mode, to avoid handshake exceptions, connection failures, or frequent Pipe restarts, it is recommended to take any of the following measures:

Appropriately lower the configuration parameter object-parallelism
Increase the MaxStartups value on the target machine as needed. After modification, execute sshd reload or sshd restart for the configuration to take effect.