# 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.

<table style="text-align: left;">
  <tbody>
     <tr>   <th>File Format</th>
            <th>IoTDB Tool</th>        
            <th>Description</th>
      </tr>
      <tr>
            <td>CSV</td>  
            <td rowspan="3">export-data.sh/bat</td> 
            <td>Plain text format for storing structured data. Must follow the CSV format specified below.</td> 
      </tr>
      <tr>
            <td>SQL</td>   
            <td>File containing custom SQL statements.</td> 
      </tr>
       <tr>
            <td rowspan="2">TsFile</td> 
            <td>Open-source time-series file format.</td>
      </tr> 
      <tr>
            <td>tsfile-backup.sh/bat</td>
            <td>An open-source time-series data file format,and this script supports the Object data type.</td>
      </tr>
</tbody>
</table>


## 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)<br>`Long.MAX_VALUE` (v2.0.8 and later)<br>(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

```Shell
# 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

```Shell
# 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
```Shell
# 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
```Shell
# 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

```Shell
# 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

```Shell
# 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:**
1. **To use this script, contact the Timecho Team to obtain the customized installation package `timechodb-<version>-extension`.**
2. **This script supports exporting Object-type data to TsFile files.**


### 3.1 Execution Commands
```Shell
# 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)

```Bash
./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

```Bash
./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

```Bash
./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
```