Data Export

About 15 min

Data Export

1. Function Overview

IoTDB supports three 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.
Copy SQL Export to TsFile: Writes query results back to a TsFile at the specified path via SQL.

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-backup.sh/bat	An open-source time-series data file format,and this script supports the Object data type.
	Copy SQL	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	-
`-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. When `-pw` is specified without a value, the password is entered interactively and hidden.	No	`TimechoDB@2021`
`-sql_dialect`	`--sql_dialect`	Select the model: `tree` or `table`. The Table Model must explicitly specify `table`.	Yes	`tree`
`-db`	`--database`	Target database. The Table Model requires this option for CSV, SQL, and TsFile exports.	Yes	-
`-table`	`--table`	Target table. When omitted, the tool traverses all tables in the database.	No	All tables in the database
`-start_time`	`--start_time`	Start time. The automatically generated query includes `time >= start_time`.	No	-
`-end_time`	`--end_time`	End time. The automatically generated query includes `time <= end_time`.	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`	Custom query. It is recommended for CSV; when specified, put the time range directly in the SQL.	No	Automatically generated query
`-timeout`	`--query_timeout`	Query timeout in milliseconds.	No	`Long.MAX_VALUE`
`-help`	`--help`	Display help for a format, for example, `-sql_dialect table -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	-
`-mfs`	`--rpc_max_frame_size`	Maximum RPC frame size in bytes.	No	`536870912`
`-ks`	`--key_store`	KeyStore path for mutual TLS.	No	-
`-kpw`	`--key_store_password`	KeyStore password.	No	-
`-ssl_protocol`	`--ssl_protocol`	SSL/TLS protocol.	No	-

The Table Model requires -sql_dialect table and the target database -db. When -table is omitted, all tables in the database are exported. -start_time and -end_time form an inclusive range and can be specified independently.

2.2 CSV Format

2.2.1 Command

# Unix/OS X
tools/export-data.sh -ft csv -sql_dialect table -db <database> [-table <table>] \
  [-start_time <start_time>] [-end_time <end_time>] [-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 table -db <database> [-table <table>] ^
  [-start_time <start_time>] [-end_time <end_time>] [-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 table -db <database> [-table <table>] ^
  [-start_time <start_time>] [-end_time <end_time>] [-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 header. The current implementation does not reliably change the Table Model header; do not depend on this option.	No	`false`
`-lpf`	`--lines_per_file`	Number of data rows per CSV file. This option is effective for the Table Model.	No	`10000`
`-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 sql -sql_dialect table -db <database> [-table <table>] \
  [-start_time <start_time>] [-end_time <end_time>] [-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>] \
  [-lpf <lines_per_file>] [-tf <time_format>] [-tz <timezone>]
      
# Windows
# Before version V2.0.4.x 
tools\export-data.bat -ft sql -sql_dialect table -db <database> [-table <table>] ^
  [-start_time <start_time>] [-end_time <end_time>] [-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>] ^
  [-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 table -db <database> [-table <table>] ^
  [-start_time <start_time>] [-end_time <end_time>] [-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>] ^
  [-lpf <lines_per_file>] [-tf <time_format>] [-tz <timezone>]

2.3.2 SQL-Specific Parameters

Short	Full Parameter	Description	Required	Default
`-lpf`	`--lines_per_file`	Number of `INSERT` rows per SQL file. This option is effective for the Table Model.	No	`10000`
`-tf`	`--time_format`	Table Model SQL files use the ISO8601 time returned by the query. This option currently does not change the time format in SQL.	No	`ISO8601`
`-tz`	`--timezone`	Time zone. The current implementation does not apply this value to the Table Model session.	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 tsfile -sql_dialect table -db <database> [-table <table>] \
  [-start_time <start_time>] [-end_time <end_time>] [-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>] [-tf <time_format>] [-tz <timezone>]
      
# Windows
# Before version V2.0.4.x 
tools\export-data.bat -ft tsfile -sql_dialect table -db <database> [-table <table>] ^
  [-start_time <start_time>] [-end_time <end_time>] [-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>] [-tf <time_format>] [-tz <timezone>]
        
# V2.0.4.x and later versions
tools\windows\export-data.bat -ft tsfile -sql_dialect table -db <database> [-table <table>] ^
  [-start_time <start_time>] [-end_time <end_time>] [-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>] [-tf <time_format>] [-tz <timezone>]

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

2.5 Scenario Examples

2.5.1 Choosing a Scenario

Requirement	Recommended format	Scope selection	Output
Export every table in a database	CSV	Specify `-db` without `-table`	One CSV file per table
Export all data from one table	CSV	Specify `-db` and `-table` without a time range	CSV file for the selected table
Extract raw data from one table by time range	CSV	Use `-table`, `-start_time`, and `-end_time`	Readable by Excel, Python, Spark, and other tools
Filter columns and rows by business conditions	CSV	Write a custom query with `-q`	Only selected columns and rows
Generate a time-windowed device statistics report	CSV	Use aggregates and a time window in `-q`	One row per device and time window
Perform a small migration, repair, or audit by time range	SQL	Use `-table` and the start and end times	Executable `INSERT INTO` statements
Perform a large single-table migration or offline archive	TsFile	Use `-table`, optionally with a time range	Binary TsFile with table schema

2.5.2 Preparing Data

The table model uses plant_id and device_id to identify devices, model for a device attribute, and the remaining columns for time-series data.

CREATE DATABASE export_demo_table;

CREATE TABLE export_demo_table.machine_metrics (
  plant_id STRING TAG,
  device_id STRING TAG,
  model STRING ATTRIBUTE,
  temperature FLOAT FIELD,
  status BOOLEAN FIELD,
  remark STRING FIELD
);

CREATE TABLE export_demo_table.alert_events (
  plant_id STRING TAG,
  device_id STRING TAG,
  severity INT32 FIELD,
  message STRING FIELD
);

INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225600000,'P01','D01','M-A',72.5,true,'normal');
INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225660000,'P01','D01','M-A',75.0,true,'normal');
INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225720000,'P01','D01','M-A',81.5,false,'high_temperature');
INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225780000,'P01','D01','M-A',79.0,true,'recovered');
INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225840000,'P01','D01','M-A',83.0,false,'high_temperature');

INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225600000,'P01','D02','M-B',70.0,true,'normal');
INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225660000,'P01','D02','M-B',71.5,true,'normal');
INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225720000,'P01','D02','M-B',73.0,true,'normal');
INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225780000,'P01','D02','M-B',76.5,true,'warming');
INSERT INTO export_demo_table.machine_metrics(time,plant_id,device_id,model,temperature,status,remark)
VALUES(1767225840000,'P01','D02','M-B',78.0,false,'inspection');

INSERT INTO export_demo_table.alert_events(time,plant_id,device_id,severity,message)
VALUES(1767225720000,'P01','D01',2,'temperature_high');
INSERT INTO export_demo_table.alert_events(time,plant_id,device_id,severity,message)
VALUES(1767225840000,'P01','D02',1,'inspection_required');

Use the following query to view the raw data:

USE export_demo_table;

SELECT time, plant_id, device_id, model, temperature, status, remark
FROM machine_metrics
ORDER BY device_id, time;

The query returns 10 rows:

time	plant_id	device_id	model	temperature	status	remark
2026-01-01T08:00:00.000+08:00	P01	D01	M-A	72.5	true	normal
2026-01-01T08:01:00.000+08:00	P01	D01	M-A	75.0	true	normal
2026-01-01T08:02:00.000+08:00	P01	D01	M-A	81.5	false	high_temperature
2026-01-01T08:03:00.000+08:00	P01	D01	M-A	79.0	true	recovered
2026-01-01T08:04:00.000+08:00	P01	D01	M-A	83.0	false	high_temperature
2026-01-01T08:00:00.000+08:00	P01	D02	M-B	70.0	true	normal
2026-01-01T08:01:00.000+08:00	P01	D02	M-B	71.5	true	normal
2026-01-01T08:02:00.000+08:00	P01	D02	M-B	73.0	true	normal
2026-01-01T08:03:00.000+08:00	P01	D02	M-B	76.5	true	warming
2026-01-01T08:04:00.000+08:00	P01	D02	M-B	78.0	false	inspection

The alert_events table contains two alert rows, which are used to verify that a full-database export creates a separate file for each table:

time	plant_id	device_id	severity	message
2026-01-01T08:02:00.000+08:00	P01	D01	2	temperature_high
2026-01-01T08:04:00.000+08:00	P01	D02	1	inspection_required

2.5.3 Full-database CSV Export

Specify only -db to export every table in a database. The tool traverses the database and generates one CSV file per table.

/tools/export-data.sh ^
  -sql_dialect table -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -db export_demo_table ^
  -t /export_demo_table/csv_all_tables -pfn all_tables -timeout 20000

Both tables print Export completely!. The following files are generated:

File	Size	Data rows
`all_tables0_0.csv`	173 bytes	2
`all_tables1_0.csv`	768 bytes	10

all_tables0_0.csv corresponds to alert_events, and all_tables1_0.csv corresponds to machine_metrics. File indexes follow the table order returned by show tables.

The content of all_tables0_0.csv is as follows:

time,plant_id,device_id,severity,message
2026-01-01T08:02:00.000+08:00,"P01","D01",2,"temperature_high"
2026-01-01T08:04:00.000+08:00,"P01","D02",1,"inspection_required"

2.5.4 Full CSV Export for One Table

Add -table without a time range when all data from one table is needed.

/tools/export-data.sh ^
  -sql_dialect table -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -db export_demo_table -table machine_metrics ^
  -t /export_demo_table/csv_one_table -pfn machine_metrics -timeout 20000

The console prints Export completely! and generates only /export_demo_table/csv_one_table/machine_metrics0_0.csv. The file is 768 bytes and contains one header row and 10 data rows.

2.5.5 Time-range CSV Export

Use -table, -start_time, and -end_time to extract raw data in an inclusive time range.

/tools/export-data.sh ^
  -sql_dialect table -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -db export_demo_table -table machine_metrics ^
  -start_time 1767225660000 -end_time 1767225780000 ^
  -t /export_demo_table/csv_range -pfn metrics_range -timeout 20000

The console prints Export completely! and generates /export_demo_table/csv_range/metrics_range0_0.csv:

time,plant_id,device_id,model,temperature,status,remark
2026-01-01T08:01:00.000+08:00,"P01","D02","M-B",71.5,true,"normal"
2026-01-01T08:02:00.000+08:00,"P01","D02","M-B",73.0,true,"normal"
2026-01-01T08:03:00.000+08:00,"P01","D02","M-B",76.5,true,"warming"
2026-01-01T08:01:00.000+08:00,"P01","D01","M-A",75.0,true,"normal"
2026-01-01T08:02:00.000+08:00,"P01","D01","M-A",81.5,false,"high_temperature"
2026-01-01T08:03:00.000+08:00,"P01","D01","M-A",79.0,true,"recovered"

2.5.6 Filtered CSV Export

Use -q to select columns and filter rows. The following command exports records whose temperature is above 80.

/tools/export-data.sh ^
  -sql_dialect table -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -db export_demo_table ^
  -t /export_demo_table/csv_filter -pfn high_temperature ^
  -q "select time,plant_id,device_id,model,temperature,status,remark from machine_metrics where temperature > 80 order by time,device_id" ^
  -timeout 20000

When -q is specified, put the time conditions in the query; -table, -start_time, and -end_time are not used to construct the query.

The console prints Export completely! and generates /export_demo_table/csv_filter/high_temperature0_0.csv:

time,plant_id,device_id,model,temperature,status,remark
2026-01-01T08:02:00.000+08:00,"P01","D01","M-A",81.5,false,"high_temperature"
2026-01-01T08:04:00.000+08:00,"P01","D01","M-A",83.0,false,"high_temperature"

2.5.7 Aggregate Report CSV Export

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

/tools/export-data.sh ^
  -sql_dialect table -h 127.0.0.1 -p 6667 -u root ^
  -ft csv -db export_demo_table ^
  -t /export_demo_table/csv_aggregate -pfn temperature_by_2m ^
  -q "select date_bin(2m,time,2026-01-01T08:00:00) as window_start,device_id,count(temperature) as point_count,avg(temperature) as avg_temperature from machine_metrics group by 1,2 order by 1,2" ^
  -timeout 20000

The console prints Export completely! and generates /export_demo_table/csv_aggregate/temperature_by_2m0_0.csv:

window_start,device_id,point_count,avg_temperature
2026-01-01T08:00:00.000+08:00,"D01",2,73.75
2026-01-01T08:00:00.000+08:00,"D02",2,70.75
2026-01-01T08:02:00.000+08:00,"D01",2,80.25
2026-01-01T08:02:00.000+08:00,"D02",2,74.75
2026-01-01T08:04:00.000+08:00,"D01",1,83.0
2026-01-01T08:04:00.000+08:00,"D02",1,78.0

2.5.8 Incremental SQL Export

Use SQL export for a small migration, repair, or audit by time range. Create a compatible database and table on the target first.

/tools/export-data.sh ^
  -sql_dialect table -h 127.0.0.1 -p 6667 -u root ^
  -ft sql -db export_demo_table -table machine_metrics ^
  -start_time 1767225660000 -end_time 1767225780000 ^
  -t /export_demo_table/sql_range -pfn metrics_range -timeout 20000

The console prints Export completely! and generates /export_demo_table/sql_range/metrics_range0_0.sql:

insert into machine_metrics(time,plant_id,device_id,model,temperature,status,remark) values(2026-01-01T08:01:00.000+08:00,'P01','D02','M-B',71.5,true,'normal');
insert into machine_metrics(time,plant_id,device_id,model,temperature,status,remark) values(2026-01-01T08:02:00.000+08:00,'P01','D02','M-B',73.0,true,'normal');
insert into machine_metrics(time,plant_id,device_id,model,temperature,status,remark) values(2026-01-01T08:03:00.000+08:00,'P01','D02','M-B',76.5,true,'warming');
insert into machine_metrics(time,plant_id,device_id,model,temperature,status,remark) values(2026-01-01T08:01:00.000+08:00,'P01','D01','M-A',75.0,true,'normal');
insert into machine_metrics(time,plant_id,device_id,model,temperature,status,remark) values(2026-01-01T08:02:00.000+08:00,'P01','D01','M-A',81.5,false,'high_temperature');
insert into machine_metrics(time,plant_id,device_id,model,temperature,status,remark) values(2026-01-01T08:03:00.000+08:00,'P01','D01','M-A',79.0,true,'recovered');

2.5.9 TsFile Migration and Archiving

Use TsFile for large single-table migrations or offline archives. The file carries the table schema and can be loaded into a compatible instance.

/tools/export-data.sh ^
  -sql_dialect table -h 127.0.0.1 -p 6667 -u root ^
  -ft tsfile -db export_demo_table -table machine_metrics ^
  -t /export_demo_table/tsfile -pfn machine_metrics -timeout 20000

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

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. When -tpw has no value, the password is entered interactively and hidden.

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

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 --rate_limit 5242880

Example 3: Specify Pipe Plugin JAR Directory

./tsfile-backup.sh -sql_dialect table -db test -table .* -t /tmp/backup \
  --plugin_jar /local/lib/tsfile-remote-sink-<version>-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.

4. Copy SQL

Note: This feature is supported since V2.0.11.1.

4.1 Command

// ---------------------------------------- Copy Statement ---------------------------------------------------------
copyToStatement
    : COPY '(' query ')' TO fileName=string ((WITH)? copyToStatementOptions)?
    | COPY tableName=qualifiedName ('(' tableColumns=identifierList ')')? TO fileName=string ((WITH)? copyToStatementOptions)?
    ;

copyToStatementOptions
    : '(' copyToStatementOption (',' copyToStatementOption)* ')'
    ;

copyToStatementOption
    : FORMAT identifier
    | TABLE identifier
    | TAGS '(' identifierList ')'
    | TIME identifier
    | MEMORY_THRESHOLD memory=INTEGER_VALUE
    ;

Parameters

Name	Description	Default
FORMAT	Export format. Currently only `TsFile` is supported.	TsFile
TABLE	Specifies the table name in the generated TsFile.	If the query SQL involves only one table, that table name is used; otherwise `default` is used.
TIME	Specifies which column in the result set is used as the TIME column. When manually specified: an error is reported if the column type is not TIMESTAMP or the specified column cannot be found. When not manually specified: an error is also reported if a column named `time` exists but its type is not TIMESTAMP. The time column is constructed with the following priority: 1. A column with the same name as the time column of the single table involved in the query. 2. A column named "time" with type TIMESTAMP is used as the time column. 3. The current row count written by the corresponding device is used as the time to generate the time column, with the column name "time".	-
TAGS	Specifies which columns are TAG columns. When there are multiple TAG columns, the order in the final generated table is consistent with the specified order. When manually specified: an error is reported if the column does not exist, the column type is not STRING, or duplicate column names exist. If the query involves only one table and all tag columns of that table can be found in the query result set, they are inferred as the tag columns of that table; otherwise, the default value is an empty list, meaning all columns except the TIME column are treated as FIELD columns.	Empty list
MEMORY_THRESHOLD	Used for memory control when generating TsFile (unit: byte). An error is reported when the manually specified value is less than or equal to 0.	32MB

Result Set

Column	Data Type	Description
path	STRING	The absolute path of the generated target file
row_count	INT64	Total number of rows written
device_count	INT64	Number of devices generated
size_in_bytes	INT64	Size of the generated target file
table_name	STRING	The table name in the target file. If it is auto-generated, it is marked with `(auto_gen)`.
time_column	STRING	The name of the time column in the table of the target file. If it is auto-generated, it is marked with `(auto_gen)`.
tag_columns	STRING	The names of the tag columns in the table of the target file, separated by `,`.

Other Notes

File generation location:
- If a file name is specified, the generated TsFile is saved under ${dn_data_dirs}/copy_to of the DataNode directly connected to the client. When multiple directories are configured, the file is generated according to the strategy specified by the configuration item dn_multi_dir_strategy.
- If a path is specified, the file is saved under the specified path.
Possible exceptions during execution:
- An error is reported when out-of-order timestamps exist while writing to TsFile according to the given schema.
- An error is reported for illegal file names or when the target file already exists.
- Duplicate column names exist in the query result.
- Insufficient disk space.

4.2 Examples

Taking table1 in the Sample Data as an example.

Export all data from table1 to the file copysql1.tsfile via a SELECT statement.

TimechoDB:database1> copy (select * from table1) to 'copysql1.tsfile'
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-----------------------------+
|                                                 path|row_count|device_count|size_in_bytes|table_name|time_column|                  tag_columns|
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-----------------------------+
|/timechodb/data/datanode/data/copy_to/copysql1.tsfile|       18|           6|         4636|    table1|       time|[region, plant_id, device_id]|
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-----------------------------+
Total line number = 1
It costs 0.336s

Export all data from table1 to the file copysql2.tsfile via the table name.

TimechoDB:database1> copy table1 to 'copysql2.tsfile'
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-----------------------------+
|                                                 path|row_count|device_count|size_in_bytes|table_name|time_column|                  tag_columns|
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-----------------------------+
|/timechodb/data/datanode/data/copy_to/copysql2.tsfile|       18|           6|         4636|    table1|       time|[region, plant_id, device_id]|
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-----------------------------+
Total line number = 1
It costs 0.048s

Export partial data from table1 to the file copysql3.tsfile via table name (columns).

TimechoDB:database1> copy table1 (device_id,temperature) to 'copysql3.tsfile'
+-----------------------------------------------------+---------+------------+-------------+----------+--------------+-----------+
|                                                 path|row_count|device_count|size_in_bytes|table_name|   time_column|tag_columns|
+-----------------------------------------------------+---------+------------+-------------+----------+--------------+-----------+
|/timechodb/data/datanode/data/copy_to/copysql3.tsfile|       18|           1|          558|    table1|time(auto_gen)|         []|
+-----------------------------------------------------+---------+------------+-------------+----------+--------------+-----------+
Total line number = 1
It costs 0.064s

Export the aggregation result of partial data from table1 to the file copysql4.tsfile via a SELECT statement.

TimechoDB:database1> copy (select count(temperature), count(humidity) from table1 group by device_id) to 'copysql4.tsfile'
+-----------------------------------------------------+---------+------------+-------------+----------+--------------+-----------+
|                                                 path|row_count|device_count|size_in_bytes|table_name|   time_column|tag_columns|
+-----------------------------------------------------+---------+------------+-------------+----------+--------------+-----------+
|/timechodb/data/datanode/data/copy_to/copysql4.tsfile|        2|           1|          543|    table1|time(auto_gen)|         []|
+-----------------------------------------------------+---------+------------+-------------+----------+--------------+-----------+
Total line number = 1
It costs 0.155s

Export partial data from table1 to the file copysql5.tsfile via a SELECT statement, with the target table, time column, and tag columns specified.

TimechoDB:database1> copy (select time,region,device_id,temperature from table1 order by time) to 'copysql5.tsfile' (TABLE copytable, TIME time, TAGS (region,device_id))
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-------------------+
|                                                 path|row_count|device_count|size_in_bytes|table_name|time_column|        tag_columns|
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-------------------+
|/timechodb/data/datanode/data/copy_to/copysql5.tsfile|       18|           4|         1199| copytable|       time|[region, device_id]|
+-----------------------------------------------------+---------+------------+-------------+----------+-----------+-------------------+
Total line number = 1
It costs 0.047s