Data Export

About 6 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	An open-source time-series data file format,and this script supports the Object data type.

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 customized installation package timechodb-<version>-extension.
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	-
`-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