Data Export

About 5 min

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.

File Format	IoTDB Tool	Description
CSV	export-data.sh/bat	Plain text format for storing structured data. Must follow the CSV format specified below.
SQL		File containing custom SQL statements.
TsFile		Open-source time-series file format.
TsFile	tsfile-backup.sh/bat	Open-source time-series file format.

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` )
`-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>  [-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>  [-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>  [-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
> tools/export-data.sh -ft csv -h 127.0.0.1 -p 6667 -u root -pw TimechoDB@2021 -t /path/export/dir 
              -pfn exported-data.csv -dt true -lpf 1000 -tf "yyyy-MM-dd HH:mm:ss"
              -tz +08:00 -q "SELECT * FROM root.ln" -timeout 20000    
                
# Error Example
> tools/export-data.sh -ft csv -h 127.0.0.1 -p 6667 -u root -pw TimechoDB@2021
Parse error: Missing required option: t

# Note: Before version V2.0.6, the default value for the -pw parameter was root.

2.3 SQL Format

2.3.1 Command

# Unix/OS X
> tools/export-data.sh -ft<format> [-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> [-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> [-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
> tools/export-data.sh -ft sql -h 127.0.0.1 -p 6667 -u root -pw TimechoDB@2021 -t /path/export/dir 
              -pfn exported-data.csv -aligned true -lpf 1000 -tf "yyyy-MM-dd HH:mm:ss"
              -tz +08:00 -q "SELECT * FROM root.ln" -timeout 20000    
                
# Error Example
> tools/export-data.sh -ft sql -h 127.0.0.1 -p 6667 -u root -pw TimechoDB@2021
Parse error: Missing required option: t

# Note: Before version V2.0.6, the default value for the -pw parameter was root.

2.4 TsFile Format

2.4.1 Command

# Unix/OS X
> tools/export-data.sh -ft<format>  [-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>  [-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>  [-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 -h 127.0.0.1 -p 6667 -u root -pw TimechoDB@2021 -t /path/export/dir 
              -pfn export-data.tsfile -q "SELECT * FROM root.ln.**"  -timeout 10000
   
# Error Example
> tools/export-data.sh -ft tsfile -h 127.0.0.1 -p 6667 -u root -pw TimechoDB@2021
Parse error: Missing required option: t

# Note: Before version V2.0.6, the default value for the -pw parameter was root.

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.

Note: 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 (for example, on all DataNode hosts).

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, that is, the IP address of the IoTDB instance where the data resides.	No	`127.0.0.1`
`-p`	`--port`	Port number of 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 files are 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. Supports ISO8601 format (for example, `2026-01-01T00:00:00`) or a millisecond timestamp. Only data from this time onwards is exported.	No	-
`-e`	`--end_time`	End time. The format is the same 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`	Remote host username for SSH/SCP login.	No	-
`-tpw`	`--target_host_pw`	Remote host password for authentication (hidden input supported).	No	-
`-tp`	`--target_host_port`	Remote SSH port.	No	`22`
`--rate_limit`	`--rate_limit`	Transfer rate limit in bytes per second (Bytes/s), to prevent the export task from consuming excessive network bandwidth.	No	-
`--plugin_jar`	`--plugin_jar`	Path to the Pipe plugin JAR file.	No	-
`-help`	`--help`	Display 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: Specify the Pipe 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