Data Import

1. Functional Overview

IoTDB supports three methods for data import:

Data Import Tool: Use the import-data.sh/bat script in the tools directory to manually import CSV, SQL, or TsFile (open-source time-series file format) data into IoTDB.
TsFile Auto-Loading Feature
Load TsFile SQL

File Format	IoTDB Tool	Description
CSV	import-data.sh/bat	Can be used for single or batch import of CSV files into IoTDB
SQL		Can be used for single or batch import of SQL files into IoTDB
TsFile		Can be used for single or batch import of TsFile files into IoTDB
	TsFile Auto-Loading Feature	Can automatically monitor a specified directory for newly generated TsFiles and load them into IoTDB
	Load SQL	Can be used for single or batch import of TsFile files into IoTDB

The table model TsFile import currently only supports local import.
Since version V2.0.9.2, the import-data.sh/bat script supports the Object data type when importing TsFile files.

2. Data Import Tool

2.1 Common Parameters

Short	Full Parameter	Description	Required	Default
`-ft`	`--file_type`	File type: `csv`, `sql`, `tsfile`.	Yes	-
`-h`	`--host`	IoTDB server hostname.	No	`127.0.0.1`
`-p`	`--port`	IoTDB server port.	No	`6667`
`-u`	`--username`	Username.	No	`root`
`-pw`	`--password`	Password. 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`	Target database , applies only to `-sql_dialect=table`	Yes when `-sql_dialect = table`; Starting from version V2.0.9.2, this parameter is optional when the file format is SQL. A prompt will be issued if the target database is not explicitly specified in either the parameter or the SQL statement.	-
`-table`	`--table`	Target table , required for CSV imports in table model	No	-
`-s`	`--source`	Local path to the file/directory to import. Supported formats: CSV, SQL, TsFile. Unsupported formats trigger error: `The file name must end with "csv", "sql", or "tsfile"!`	Yes	-
`-tn`	`--thread_num`	Maximum parallel threads	No	`8` Range: 0 to Integer.Max(2147483647).
`-tz`	`--timezone`	Timezone (e.g., `+08:00`, `-01:00`).	No	System default
`-help`	`--help`	Display help (general or format-specific: `-help csv`).	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/import-data.sh -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table> 
     [-h <host>] [-p <port>] [-u <username>] [-pw <password>]
       -s <source> [-fd <fail_dir>] [-lpf <lines_per_failed_file>] [-aligned <use the aligned interface>] 
      [-ti <type_infer>] [-tp <timestamp precision (ms/us/ns)>] [-tz <timezone>] [-batch <batch_size>] 
      [-tn <thread_num>]
      
# Windows
# Before version V2.0.4.x 
> tools\import-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
        [-h <host>] [-p <port>] [-u <username>] [-pw <password>]
       -s <source> [-fd <fail_dir>] [-lpf <lines_per_failed_file>] [-aligned <use the aligned interface>] 
      [-ti <type_infer>] [-tp <timestamp precision (ms/us/ns)>] [-tz <timezone>] [-batch <batch_size>] 
      [-tn <thread_num>]
      
# V2.0.4.x and later versions
> tools\windows\import-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
        [-h <host>] [-p <port>] [-u <username>] [-pw <password>]
       -s <source> [-fd <fail_dir>] [-lpf <lines_per_failed_file>] [-aligned <use the aligned interface>] 
      [-ti <type_infer>] [-tp <timestamp precision (ms/us/ns)>] [-tz <timezone>] [-batch <batch_size>] 
      [-tn <thread_num>]

2.2.2 CSV-Specific Parameters

Short	Full Parameter	Description	Required	Default
`-fd`	`--fail_dir`	Directory to save failed files.	No	YOUR_CSV_FILE_PATH
`-lpf`	`--lines_per_failed_file`	Max lines per failed file.	No	`100000` Range: 0 to Integer.Max(2147483647).
`-aligned`	`--use_aligned`	Import as aligned time series.	No	`false`
`-batch`	`--batch_size`	Rows processed per API call.	No	`100000` Range: 0 to Integer.Max(2147483647).
`-ti`	`--type_infer`	Type mapping (e.g., `BOOLEAN=text,INT=long`).	No	-
`-tp`	`--timestamp_precision`	Timestamp precision: `ms`, `us`, `ns`.	No	`ms`

2.2.3 Examples

# Valid Example
> tools/import-data.sh -ft csv -sql_dialect table -s ./csv/dump0_0.csv  -db database1 -table table1

# Error Example
> tools/import-data.sh -ft csv -sql_dialect table -s ./csv/dump0_1.csv  -table table1
Parse error: Missing required option: db

> tools/import-data.sh -ft csv -sql_dialect table -s ./csv/dump0_1.csv -db database1 -table table5
There are no tables or the target table table5 does not exist

2.2.4 Import Notes

CSV Import Specifications

Special Character Escaping Rules: If a text-type field contains special characters (e.g., commas ,), they must be escaped using a backslash (\).
Supported Time Formats: yyyy-MM-dd'T'HH:mm:ss, yyyy-MM-dd HH:mm:ss, or yyyy-MM-dd'T'HH:mm:ss.SSSZ.
Timestamp Column Requirement: The timestamp column must be the first column in the data file.

CSV File Example

time,region,device,model,temperature,humidity
1970-01-01T08:00:00.001+08:00,"SH","101","F",90.0,35.2
1970-01-01T08:00:00.002+08:00,"SH","101","F",90.0,34.8

2.3 SQL Format

2.3.1 Command

# Unix/OS X
> tools/import-data.sh -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table> 
         [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
        -s<source> [-fd <fail_dir>] [-lpf <lines_per_failed_file>] [-tz <timezone>] 
        [-batch <batch_size>] [-tn <thread_num>]
      
# Windows
# Before version V2.0.4.x
> tools\import-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
        [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
        -s<source> [-fd <fail_dir>] [-lpf <lines_per_failed_file>] [-tz <timezone>] 
        [-batch <batch_size>] [-tn <thread_num>]
        
# V2.0.4.x and later versions
> tools\windows\import-data.bat -ft<format> [-sql_dialect<sql_dialect>] -db<database> -table<table>  
        [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
        -s<source> [-fd <fail_dir>] [-lpf <lines_per_failed_file>] [-tz <timezone>] 
        [-batch <batch_size>] [-tn <thread_num>]

2.3.2 SQL-Specific Parameters

Short	Full Parameter	Description	Required	Default
`-fd`	`--fail_dir`	Directory to save failed files.	No	YOUR_CSV_FILE_PATH
`-lpf`	`--lines_per_failed_file`	Max lines per failed file.	No	`100000` Range: 0 to Integer.Max(2147483647).
`-batch`	`--batch_size`	Rows processed per API call.	No	`100000` Range: 0 to Integer.Max(2147483647).

2.3.3 Examples

# Valid Example
> tools/import-data.sh -ft sql -sql_dialect table -s ./sql/dump0_0.sql -db database1 

# Error Example
> tools/import-data.sh -ft sql -sql_dialect table -s ./sql/dump1_1.sql -db database1
Source file or directory ./sql/dump1_1.sql does not exist

# When the target table exists but metadata is incompatible or data is malformed, the system will generate a .failed file and log error details. 
# Log Example
Fail to insert measurements '[column.name]' caused by [data type is not consistent, input '[column.value]', registered '[column.DataType]']

2.4 TsFile Format

2.4.1 Command

# Unix/OS X
> tools/import-data.sh -ft <format> [-sql_dialect<sql_dialect>] -db<database> -table<table> 
         [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
        -s <source> [-o <object_file_paths>] -os <on_success> [-sd <success_dir>] -of <on_fail> [-fd <fail_dir>]
        [-tn <thread_num> ] [-tz <timezone>] [-tp <timestamp precision (ms/us/ns)>]
      
# Windows
# Before version V2.0.4.x
> tools\import-data.bat -ft <format> [-sql_dialect<sql_dialect>] -db<database> -table<table> 
         [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
        -s <source> -os <on_success> [-sd <success_dir>] -of <on_fail> [-fd <fail_dir>]
        [-tn <thread_num> ] [-tz <timezone>] [-tp <timestamp precision (ms/us/ns)>]
        
# V2.0.4.x and later versions        
> tools\windows\import-data.bat -ft <format> [-sql_dialect<sql_dialect>] -db<database> -table<table> 
         [-h <host>] [-p <port>] [-u <username>] [-pw <password>] 
        -s <source> [-o <object_file_paths>] -os <on_success> [-sd <success_dir>] -of <on_fail> [-fd <fail_dir>]
        [-tn <thread_num> ] [-tz <timezone>] [-tp <timestamp precision (ms/us/ns)>]

2.4.2 TsFile-Specific Parameters

Short	Full Parameter	Description	Required	Default
`-os`	`--on_success`	Action for successful files: `none`: Do not delete the file. `mv`: Move the successful file to the target directory. `cp`:Create a hard link (copy) of the successful file to the target directory. `delete`:Delete the file.	Yes	-
`-sd`	`--success_dir`	Target directory for `mv`/`cp` actions on success. Required if `-os` is `mv`/`cp`. The file name will be flattened and concatenated with the original file name.	Conditional	`${EXEC_DIR}/success`
`-of`	`--on_fail`	Action for failed files: `none`:Skip the file. `mv`:Move the failed file to the target directory. `cp`:Create a hard link (copy) of the failed file to the target directory. `delete`:Delete the file..	Yes	-
`-fd`	`--fail_dir`	Target directory for `mv`/`cp` actions on failure. Required if `-of` is `mv`/`cp`. The file name will be flattened and concatenated with the original file name.	Conditional	`${EXEC_DIR}/fail`
`-tp`	`--timestamp_precision`	TsFile timestamp precision: `ms`, `us`, `ns`. For non-remote TsFile imports: Use -tp to specify the timestamp precision of the TsFile. The system will manually verify if the timestamp precision matches the server. If it does not match, an error will be returned. For remote TsFile imports: Use -tp to specify the timestamp precision of the TsFile. The Pipe system will automatically verify if the timestamp precision matches. If it does not match, a Pipe error will be returned.	No	`ms`
`-o`	`--object-file-paths`	Storage path for Object files. Default mode: If this parameter is not specified, the script automatically identifies and imports Object files located in the subdirectory with the same name as the TsFile. Absolute path mode: Explicitly specifies the external storage root directory for Object files; the tool creates an associated data index based on this path. Note: This parameter is supported since V2.0.9.2	No

2.4.3 Examples

# Valid Example
> tools/import-data.sh -ft tsfile -sql_dialect table -s ./tsfile -db database1 -os none -of none

# Error Example
> tools/import-data.sh -ft tsfile -sql_dialect table -s ./tsfile -db database1 
Parse error: Missing required options: os, of

Object Type Import

Import Directory Structure

Default Mode

target_dir
    ├── tsfile.tsfile
    └── tsfile/ (matches the TsFile name)
        ├── regionID/tableName/tag1/tag2/field/timestamp1.bin
        ├── regionID/tableName/tag1/tag2/field/timestamp2.bin
        └── regionID/tableName1/tag3/tag4/field/timestamp1.bin

Specified Object Directory

target_dir
    ├── tsfile.tsfile
object_dir
    ├── regionID/tableName/tag1/tag2/field/timestamp1.bin
    ├── regionID/tableName/tag1/tag2/field/timestamp2.bin
    └── regionID/tableName1/tag3/tag4/field/timestamp1.bin

Command Line Examples

Basic Import (automatically identifies Object files in the TsFile-named directory)

./import-data.sh -sql_dialect table -ft tsfile -s /data/import/sensor_v1.tsfile -db database1 -os none -of none

Batch Directory Import (specify concurrent threads and post-success action)

./import-data.sh -sql_dialect table -ft tsfile -s /data/raw_data/ -tn 16 -os mv -sd /data/archive/

Table Model Associated Import (specify external Object storage path and target database)

./import-data.sh -sql_dialect table -ft tsfile -s /data/import/ -db factory_db -o /mnt/object_storage/ -of mv -fd /data/error_log/

3. TsFile Auto-Loading

This feature enables IoTDB to automatically monitor a specified directory for new TsFiles and load them into the database without manual intervention.

3.1 Configuration

Add the following parameters to iotdb-system.properties (template: iotdb-system.properties.template):

Parameter	Description	Value Range	Required	Default	Hot-Load?
`load_active_listening_enable`	Enable auto-loading.	`true`/`false`	Optional	`true`	Yes
`load_active_listening_dirs`	Directories to monitor (subdirectories included). Multiple paths separated by commas. Note: In the table model, the directory name where the file is located will be used as the database.	String	Optional	`ext/load/pending`	Yes
`load_active_listening_fail_dir`	Directory to store failed TsFiles. Only can set one.	String	Optional	`ext/load/failed`	Yes
`load_active_listening_max_thread_num`	Maximum Threads for TsFile Loading Tasks:The default value for this parameter, when commented out, is max(1, CPU cores / 2). If the value set by the user falls outside the range [1, CPU cores / 2], it will be reset to the default value of max(1, CPU cores / 2).	`1` to `Long.MAX_VALUE`	Optional	`max(1, CPU_CORES / 2)`	No (restart required)
`load_active_listening_check_interval_seconds`	Active Listening Polling Interval (in seconds):The active listening feature for TsFiles is implemented through polling the target directory. This configuration specifies the time interval between two consecutive checks of the `load_active_listening_dirs`. After each check, the next check will be performed after `load_active_listening_check_interval_seconds` seconds. If the polling interval set by the user is less than 1, it will be reset to the default value of 5 seconds.	`1` to `Long.MAX_VALUE`	Optional	`5`	No (restart required)

3.2 Examples

load_active_listening_dir/
├─sensors/
│  ├─temperature/
│  │   └─temperature-table.TSFILE

Table model TsFile
- temperature-table.TSFILE: will be imported into the temperature database (because it is located in the sensors/temperature/ directory)

3.3 Notes

Mods Files: If TsFiles have associated .mods files, move .mods files to the monitored directory before their corresponding TsFiles. Ensure .mods and TsFiles are in the same directory.
Restricted Directories: Do NOT set Pipe receiver directories, data directories, or other system paths as monitored directories.
Directory Conflicts: Ensure load_active_listening_fail_dir does not overlap with load_active_listening_dirs or its subdirectories.
Permissions: The monitored directory must have write permissions. Files are deleted after successful loading; insufficient permissions may cause duplicate loading.

4. Load SQL

IoTDB supports importing one or multiple TsFile files containing time series into another running IoTDB instance directly via SQL execution through the CLI.

4.1 Command

load '<path/dir>' with (
    'attribute-key1'='attribute-value1',
    'attribute-key2'='attribute-value2',
)

<path/dir> : The path to a TsFile or a folder containing multiple TsFiles.
<attributes>: Optional parameters, as described below.

Key	Key Description	Value Type	Value Range	Value is Required	Default Value
`database-level`	When the database corresponding to the TsFile does not exist, the database hierarchy level can be specified via the `database-level` parameter. The default is the level set in `iotdb-common.properties`. For example, setting level=1 means the prefix path of level 1 in all time series in the TsFile will be used as the database.	Integer	`[1: Integer.MAX_VALUE]`	No	1
`on-success`	Action for successfully loaded TsFiles: `delete` (delete the TsFile after successful import) or `none` (retain the TsFile in the source folder).	String	`delete / none`	No	delete
`model`	Specifies whether the TsFile uses the `table` model or `tree` model. This parameter becomes invalid starting from V2.0.2.1. The system automatically identifies whether the data model is tree-based or table-based.	String	`tree / table`	No	Aligns with `-sql_dialect`
`database-name`	Table model only: Target database for import. Automatically created if it does not exist. The database-name must not include the `root.` prefix (an error will occur if included).	String	`-`	No	null
`convert-on-type-mismatch`	Whether to perform type conversion during loading if data types in the TsFile mismatch the target schema.	Boolean	`true / false`	No	true
`verify`	Whether to validate the schema before loading the TsFile.	Boolean	`true / false`	No	true
`tablet-conversion-threshold`	Size threshold (in bytes) for converting TsFiles into tablet format during loading. Default: `-1` (no conversion for any TsFile).	Integer	`[-1,0 :``Integer.MAX_VALUE]`	No	-1
`async`	Whether to enable asynchronous loading. If enabled, TsFiles are moved to an active-load directory and loaded into the `database-name` asynchronously.	Boolean	`true / false`	No	false

4.2 Example

-- Create target database: database2
IoTDB> create database database2
Msg: The statement is executed successfully.

IoTDB> use database2
Msg: The statement is executed successfully.

IoTDB:database2> show tables details
+---------+-------+------+-------+
|TableName|TTL(ms)|Status|Comment|
+---------+-------+------+-------+
+---------+-------+------+-------+
Empty set.

-- Import tsfile by excuting load sql 
IoTDB:database2> load '/home/dump0.tsfile' with ( 'on-success'='none', 'database-name'='database2')
Msg: The statement is executed successfully.

-- Verify whether the import was successful
IoTDB:database2> select * from table2
+-----------------------------+------+--------+---------+-----------+--------+------+-----------------------------+
|                         time|region|plant_id|device_id|temperature|humidity|status|                 arrival_time|
+-----------------------------+------+--------+---------+-----------+--------+------+-----------------------------+
|2024-11-30T00:00:00.000+08:00|  上海|    3002|      101|       90.0|    35.2|  true|                         null|
|2024-11-29T00:00:00.000+08:00|  上海|    3001|      101|       85.0|    35.1|  null|2024-11-29T10:00:13.000+08:00|
|2024-11-27T00:00:00.000+08:00|  北京|    1001|      101|       85.0|    35.1|  true|2024-11-27T16:37:01.000+08:00|
|2024-11-29T11:00:00.000+08:00|  上海|    3002|      100|       null|    45.1|  true|                         null|
|2024-11-28T08:00:00.000+08:00|  上海|    3001|      100|       85.0|    35.2| false|2024-11-28T08:00:09.000+08:00|
|2024-11-26T13:37:00.000+08:00|  北京|    1001|      100|       90.0|    35.1|  true|2024-11-26T13:37:34.000+08:00|
+-----------------------------+------+--------+---------+-----------+--------+------+-----------------------------+

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Data Import

1. Functional Overview

2. Data Import Tool

2.1 Common Parameters

2.2 CSV Format

2.2.1 Command

2.2.2 CSV-Specific Parameters

2.2.3 Examples

2.2.4 Import Notes

2.3 SQL Format

2.3.1 Command

2.3.2 SQL-Specific Parameters

2.3.3 Examples

2.4 TsFile Format

2.4.1 Command

2.4.2 TsFile-Specific Parameters

2.4.3 Examples

3. TsFile Auto-Loading

3.1 Configuration

3.2 Examples

3.3 Notes

4. Load SQL

4.1 Command

4.2 Example

Uh oh!

FilesExpand file tree

Data-Import-Tool_timecho.md

Latest commit

History

Data-Import-Tool_timecho.md

File metadata and controls

Data Import

1. Functional Overview

2. Data Import Tool

2.1 Common Parameters

2.2 CSV Format

2.2.1 Command

2.2.2 CSV-Specific Parameters

2.2.3 Examples

2.2.4 Import Notes

2.3 SQL Format

2.3.1 Command

2.3.2 SQL-Specific Parameters

2.3.3 Examples

2.4 TsFile Format

2.4.1 Command

2.4.2 TsFile-Specific Parameters

2.4.3 Examples

3. TsFile Auto-Loading

3.1 Configuration

3.2 Examples

3.3 Notes

4. Load SQL

4.1 Command

4.2 Example