Data Export

About 9 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 format: `csv`, `sql`, or `tsfile`.	Yes	-
`-sql_dialect`	`--sql_dialect`	Select the data model: `tree` or `table`. This option can be omitted for the Tree Model.	No	`tree`
`-h`	`--host`	Server address.	No	`127.0.0.1`
`-p`	`--port`	RPC port.	No	`6667`
`-u`	`--username`	Username.	No	`root`
`-pw`	`--password`	Password. When `-pw` is specified without a value, the password is entered interactively and hidden.	No	`TimechoDB@2021`
`-t`	`--target`	Output directory. It is created automatically when it does not exist.	Yes	-
`-pfn`	`--prefix_file_name`	Prefix for exported file names. For example, `abc` generates `abc_0.tsfile`, `abc_1.tsfile`, and so on.	No	`dump_0.tsfile`
`-q`	`--query`	Query statement. When omitted, the query is entered interactively. Multiple Tree Model queries can be separated with semicolons.	No	None
`-timeout`	`--query_timeout`	Query timeout in milliseconds.	No	`Long.MAX_VALUE`
`-mfs`	`--rpc_max_frame_size`	Maximum RPC frame size in bytes.	No	`536870912`
`-help`	`--help`	Display help for a format, for example, `-help csv`.	No	-
`-usessl`	`--use_ssl`	Whether to enable SSL. The option must be followed by `true` or `false`, for example, `-usessl true`.	No	-
`-ts`	`--trust_store`	TrustStore path.	No	-
`-tpw`	`--trust_store_password`	TrustStore password. Hidden input is supported.	No	-
`-ks`	`--key_store`	KeyStore path for mutual TLS.	No	-
`-kpw`	`--key_store_password`	KeyStore password.	No	-
`-ssl_protocol`	`--ssl_protocol`	SSL/TLS protocol.	No	-

2.2 CSV Format

2.2.1 Command

# Unix/OS X
tools/export-data.sh -ft csv [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] \
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] \
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> \
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>] \
  [-dt <true|false>] [-lpf <lines_per_file>] [-tf <time_format>] [-tz <timezone>]
# Windows
# Before version V2.0.4.x
tools\export-data.bat -ft csv [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] ^
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] ^
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> ^
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>] ^
  [-dt <true|false>] [-lpf <lines_per_file>] [-tf <time_format>] [-tz <timezone>]

# V2.0.4.x and later versions           
tools\windows\export-data.bat -ft csv [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] ^
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] ^
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> ^
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>] ^
  [-dt <true|false>] [-lpf <lines_per_file>] [-tf <time_format>] [-tz <timezone>]

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 sql [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] \
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] \
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> \
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>] \
  [-aligned <true|false>] [-lpf <lines_per_file>] [-tf <time_format>] [-tz <timezone>]
      
# Windows
# Before version V2.0.4.x
tools\export-data.bat -ft sql [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] ^
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] ^
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> ^
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>] ^
  [-aligned <true|false>] [-lpf <lines_per_file>] [-tf <time_format>] [-tz <timezone>]
          
# V2.0.4.x and later versions          
tools\windows\export-data.bat -ft sql [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] ^
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] ^
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> ^
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>] ^
  [-aligned <true|false>] [-lpf <lines_per_file>] [-tf <time_format>] [-tz <timezone>]

2.3.2 SQL-Specific Parameters

Short	Full Parameter	Description	Required	Default
`-aligned`	`--use_aligned`	Compatibility option. The current implementation does not use this value to decide whether aligned insert statements are generated.	No	`true`
`-lpf`	`--lines_per_file`	Number of rows per exported file.	No	`10000` (Range：0～Integer.Max=2147483647)
`-tf`	`--time_format`	SQL files always use numeric timestamps; this option does not change the timestamp format in SQL.	No	`ISO8601`
`-tz`	`--timezone`	Session time zone.	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 tsfile [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] \
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] \
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> \
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>]
      
# Windows
# Before version V2.0.4.x
tools\export-data.bat -ft tsfile [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] ^
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] ^
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> ^
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>]
        
# V2.0.4.x and later versions        
tools\windows\export-data.bat -ft tsfile [-sql_dialect tree] [-h <host>] [-p <port>] [-u <username>] [-pw <password>] ^
  [-usessl <true|false>] [-ts <trust_store>] [-tpw <trust_store_password>] [-ks <key_store>] ^
  [-kpw <key_store_password>] [-ssl_protocol <ssl_protocol>] -t <target_directory> ^
  [-pfn <prefix_file_name>] [-q <query_command>] [-timeout <query_timeout>] [-mfs <rpc_max_frame_size>]

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.

2.5 Scenario Examples

2.5.1 Choosing a Scenario

Requirement	Recommended format	Query characteristics	Output
View, analyze, or exchange raw data for a device and time range	CSV	Detail query with a time range in `WHERE`	Readable by Excel, Python, Spark, and other tools
Combine multiple devices into one device view	CSV	Add `ALIGN BY DEVICE` at the end of the query	Includes a `Device` column for comparison
Generate a time-windowed statistics report	CSV	Use aggregate functions and `GROUP BY` time windows	One statistics row per time window
Migrate, repair, or audit a small amount of data from non-aligned devices	SQL	Query raw data from non-aligned devices	Regular `INSERT` statements
Migrate a small amount of data from aligned devices	SQL	Query with `ALIGN BY DEVICE`	`INSERT` statements with `ALIGNED`
Migrate or archive a large amount of data while preserving series properties and alignment	TsFile	Query non-aggregated raw time-series data	Binary TsFile files
Export multiple queries in one run and control file size	CSV, SQL, or TsFile	Separate queries with semicolons in `-q` and use `-lpf` as needed	Files named by query and split indexes

2.5.2 Preparing Data

d1 uses ordinary time series, and d2 uses aligned time series.

CREATE DATABASE root.export_demo;

CREATE TIMESERIES root.export_demo.line1.d1.temperature FLOAT ENCODING=RLE COMPRESSOR=SNAPPY;
CREATE TIMESERIES root.export_demo.line1.d1.status BOOLEAN ENCODING=PLAIN COMPRESSOR=SNAPPY;
CREATE TIMESERIES root.export_demo.line1.d1.remark TEXT ENCODING=PLAIN COMPRESSOR=SNAPPY;

CREATE ALIGNED TIMESERIES root.export_demo.line1.d2(
  temperature FLOAT ENCODING=RLE COMPRESSOR=SNAPPY,
  status BOOLEAN ENCODING=PLAIN COMPRESSOR=SNAPPY,
  remark TEXT ENCODING=PLAIN COMPRESSOR=SNAPPY
);

INSERT INTO root.export_demo.line1.d1(timestamp,temperature,status,remark)
VALUES(1767225600000,72.5,true,'normal');
INSERT INTO root.export_demo.line1.d1(timestamp,temperature,status,remark)
VALUES(1767225660000,75.0,true,'normal');
INSERT INTO root.export_demo.line1.d1(timestamp,temperature,status,remark)
VALUES(1767225720000,81.5,false,'high_temperature');
INSERT INTO root.export_demo.line1.d1(timestamp,temperature,status,remark)
VALUES(1767225780000,79.0,true,'recovered');
INSERT INTO root.export_demo.line1.d1(timestamp,temperature,status,remark)
VALUES(1767225840000,83.0,false,'high_temperature');

INSERT INTO root.export_demo.line1.d2(timestamp,temperature,status,remark)
ALIGNED VALUES(1767225600000,70.0,true,'normal');
INSERT INTO root.export_demo.line1.d2(timestamp,temperature,status,remark)
ALIGNED VALUES(1767225660000,71.5,true,'normal');
INSERT INTO root.export_demo.line1.d2(timestamp,temperature,status,remark)
ALIGNED VALUES(1767225720000,73.0,true,'normal');
INSERT INTO root.export_demo.line1.d2(timestamp,temperature,status,remark)
ALIGNED VALUES(1767225780000,76.5,true,'warming');
INSERT INTO root.export_demo.line1.d2(timestamp,temperature,status,remark)
ALIGNED VALUES(1767225840000,78.0,false,'inspection');

Use the following query to view all raw data used in this section:

SELECT temperature, status, remark FROM root.export_demo.line1.* ALIGN BY DEVICE;

The query returns 10 rows for the two devices.

2.5.3 CSV Export with a Time Range

Use CSV when extracting raw data for a device and time range. In the Tree Model, specify the time range directly in the query.

tools/export-data.sh ^
  -sql_dialect tree -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -t /export_demo/csv_raw -pfn d1_range ^
  -q "select temperature,status,remark from root.export_demo.line1.d1 where time >= 1767225660000 and time <= 1767225780000" ^
  -timeout 20000

The command prints Export completely! and generates /export_demo/csv_raw/d1_range0_0.csv:

Time,root.export_demo.line1.d1.temperature,root.export_demo.line1.d1.status,root.export_demo.line1.d1.remark
2026-01-01T08:01:00.000+08:00,75.0,true,"normal"
2026-01-01T08:02:00.000+08:00,81.5,false,"high_temperature"
2026-01-01T08:03:00.000+08:00,79.0,true,"recovered"

2.5.4 CSV Export by Device

Add ALIGN BY DEVICE to the query when multiple devices must share one column layout and be distinguished by a Device column.

tools/export-data.sh ^
  -sql_dialect tree -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -t /export_demo/csv_aligned -pfn devices ^
  -q "select temperature,status,remark from root.export_demo.line1.* align by device" ^
  -timeout 20000

The command generates /export_demo/csv_aligneddevices0_0.csv with the columns Time,Device,temperature,status,remark and the 10 device rows from the prepared data.

2.5.5 Aggregate Report CSV Export

CSV can export aggregate queries for reports. The following query calculates the count and average temperature for both devices in two-minute windows.

/tools/export-data.sh ^
  -sql_dialect tree -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -t /export_demo/csv_aggregate -pfn temperature_by_2m ^
  -q "select count(temperature),avg(temperature) from root.export_demo.line1.* group by ([1767225600000,1767225900000), 2m)" ^
  -timeout 20000

The generated temperature_by_2m0_0.csv contains the window start time, counts, and averages.

2.5.6 SQL Export for a Non-aligned Device

For small logical migrations, data repair, or manual audits, export raw data from a non-aligned device as regular INSERT statements. Create the same database and time series on the target first.

/tools/export-data.sh ^
  -sql_dialect tree -h 127.0.0.1 -p 6667 -u root ^
  -ft sql -t /export_demo/sql_raw -pfn d1_range ^
  -q "select temperature,status,remark from root.export_demo.line1.d1 where time >= 1767225660000 and time <= 1767225780000" ^
  -timeout 20000

The generated /export_demo/sql_raw/d1_range0_0.sql contains regular INSERT INTO statements.

2.5.7 SQL Export for an Aligned Device

Use ALIGN BY DEVICE when migrating an aligned device. The tool uses the Device column to generate INSERT statements with ALIGNED.

/tools/export-data.sh ^
  -sql_dialect tree -h 127.0.0.1 -p 6667 -u root ^
  -ft sql -t /export_demo/sql_aligned -pfn d2_aligned ^
  -q "select temperature,status,remark from root.export_demo.line1.d2 align by device" ^
  -timeout 20000

2.5.8 TsFile Migration and Archiving

Use TsFile for large migrations or offline archiving when data types, encodings, compression, and device alignment must be preserved.

/tools/export-data.sh ^
  -sql_dialect tree -h 127.0.0.1 -p 6667 -u root ^
  -ft tsfile -t /export_demo/tsfile -pfn export_demo ^
  -q "select * from root.export_demo.**" ^
  -timeout 20000

The command generates /export_demo/tsfile/export_demo0.tsfile. TsFile is binary, so its rows cannot be displayed directly like CSV or SQL rows.

2.5.9 Multi-query CSV Export with Splitting

Separate multiple queries with semicolons in -q and use -lpf to limit the number of data rows per CSV file. The following command exports temperatures from two devices, with at most two rows per file.

/tools/export-data.sh ^
  -sql_dialect tree -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -t /export_demo/csv_multi_split -pfn multi -lpf 2 ^
  -q "select temperature from root.export_demo.line1.d1;select temperature from root.export_demo.line1.d2" ^
  -timeout 20000

Both queries print Export completely! and generate six files. The first number in each name is the query index and the second is the split index.

File	Size	Data rows	Range
`multi0_0.csv`	116 bytes	2	`d1` rows 1-2
`multi0_1.csv`	116 bytes	2	`d1` rows 3-4
`multi0_2.csv`	80 bytes	1	`d1` row 5
`multi1_0.csv`	116 bytes	2	`d2` rows 1-2
`multi1_1.csv`	116 bytes	2	`d2` rows 3-4
`multi1_2.csv`	80 bytes	1	`d2` row 5

For example, multi0_0.csv contains:

Time,root.export_demo.line1.d1.temperature
2026-01-01T08:00:00.000+08:00,72.5
2026-01-01T08:01:00.000+08:00,75.0

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: Remotely export data under a specified Tree Model path. When -tpw has no value, the password is entered interactively and hidden.

./tsfile-backup.sh -sql_dialect tree -path root.factory.** -t /remote/archive/ \
  -th 192.168.1.100 -tu backup_user -tpw

Example 2: Specify the Pipe plugin JAR.

./tsfile-backup.sh -sql_dialect tree -path root.factory.** -t /tmp/backup \
  --plugin_jar /local/lib/tsfile-remote-sink-<version>-jar-with-dependencies.jar