src/UserGuide/develop/Ecosystem-Integration/Duck-TsFile.md - tsfile - Git at Google

 <!--

     Licensed to the Apache Software Foundation (ASF) under one
     or more contributor license agreements.  See the NOTICE file
     distributed with this work for additional information
     regarding copyright ownership.  The ASF licenses this file
     to you under the Apache License, Version 2.0 (the
     "License"); you may not use this file except in compliance
     with the License.  You may obtain a copy of the License at

         http://www.apache.org/licenses/LICENSE-2.0

     Unless required by applicable law or agreed to in writing,
     software distributed under the License is distributed on an
     "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
     KIND, either express or implied.  See the License for the
     specific language governing permissions and limitations
     under the License.

 -->

 # DuckDB TsFile Extension

 This guide is intended for data engineers, platform developers, and analytics developers who need to analyze or generate Apache TsFile data in DuckDB. The TsFile extension is available as a DuckDB Community Extension. It makes TsFile both a SQL data source and a `COPY` output format, providing a complete read-query-write workflow.

 **Remember two things:** use `read_tsfile(path, table_name)` to read data, and use `COPY ... TO ... (FORMAT tsfile)` to write data. The current version supports local, Table-model TsFiles. Before writing, sort the data by the TAG columns and then by the time column.

 ## Quick Start

 ### Start DuckDB and Load the Extension

 For regular use, install and load the DuckDB Community Extension:

 ```sql
 INSTALL tsfile FROM community;
 ```

 ```sql
 LOAD tsfile;
 ```

 Install the Community Extension the first time you use it. After that, you only need to run `LOAD tsfile;` whenever you start DuckDB.

 ### Read Your First TsFile

 ```sql
 SELECT *
 FROM read_tsfile('/data/measurements.tsfile', 'sensors');
 ```

 The first argument to `read_tsfile` is a local file path, and the second is the name of the table stored in the TsFile. Each call reads one Table-model table from one file.

 ## Reading and Querying

 ### Select Columns

 Select the columns you need just as you would from a regular DuckDB table. Projection is pushed down to the TsFile scan whenever possible, reducing the amount of data read:

 ```sql
 SELECT time, device_id, temperature
 FROM read_tsfile('/data/measurements.tsfile', 'sensors')
 LIMIT 10;
 ```

 ### Filter by Time

 The global TsFile time axis is exposed in DuckDB as a `BIGINT`. The time unit is determined by the file or protocol; milliseconds are common. The following operators can be pushed down to the scanner: `=`, `<`, `<=`, `>`, `>=`, and `BETWEEN`.

 ```sql
 SELECT time, device_id, temperature
 FROM read_tsfile('/data/measurements.tsfile', 'sensors')
 WHERE time BETWEEN 1700000000000 AND 1700003600000;
 ```

 ### Filter by TAG

 String TAG columns support equality and range comparisons, null checks, and combinations using `AND` and `OR`. Supported operations include `=`, `!=`, `<`, `<=`, `>`, `>=`, inclusive `BETWEEN`, `IS NULL`, and `IS NOT NULL`.

 ```sql
 SELECT time, device_id, temperature
 FROM read_tsfile('/data/measurements.tsfile', 'sensors')
 WHERE (device_id = 'device-01' OR device_id = 'device-02')
   AND time BETWEEN 1700000000000 AND 1700003600000;
 ```

 FIELD filters and TAG expressions that cannot be pushed down are still evaluated by DuckDB after the scan. A `NOT (...)` expression in any form is not pushed down.

 ### Check Filter Pushdown

 Use `EXPLAIN` to inspect the query plan. If pushdown succeeds, the `READ_TSFILE` node shows `Time Range` and `TAG Filter`:

 ```sql
 EXPLAIN
 SELECT time, device_id, temperature
 FROM read_tsfile('/data/measurements.tsfile', 'sensors')
 WHERE device_id = 'device-01'
   AND time >= 1700000000000;
 ```

 ## Writing TsFile

 ### Write with COPY

 Writing uses DuckDB's standard `COPY` interface. Clean, cast, and sort the data in a subquery before writing it to TsFile:

 ```sql
 COPY (
     SELECT time, device_id, temperature, humidity
     FROM measurements
     ORDER BY device_id, time
 )
 TO '/data/measurements.tsfile'
 (
     FORMAT tsfile,
     TABLE_NAME sensors,
     TIME_COLUMN time,
     TAG_COLUMNS (device_id)
 );
 ```

 `TIME_COLUMN` identifies the time axis. Columns listed in `TAG_COLUMNS` are written as TAG columns, and all other columns are written as FIELD columns. Identifier syntax is recommended for `TABLE_NAME` and `TIME_COLUMN`; use double quotes when an identifier contains special characters.

 ### Write Options

 |Option|Required|Description|
 |---|---|---|
 |`FORMAT tsfile`|Yes|Selects the TsFile `COPY` writer.|
 |`TABLE_NAME`|No|The local table name in the output file. The default is `default_table`.|
 |`TIME_COLUMN`|No|The input column used as the time axis. The default is `time`, and its type must be `BIGINT`.|
 |`TAG_COLUMNS`|No|A list of TAG column names. If omitted, all non-time columns are written as FIELD columns.|
 |`OVERWRITE true`|No|Use this option to replace an existing target. Keep the default temporary-file handling enabled.|

 ### TAG\_COLUMNS Constraints

 `TAG_COLUMNS` accepts a list of column names, not a list of values:

 ```sql
 TAG_COLUMNS (device_id, region)
 ```

 - Each column must exist in the input query.

 - Each column must have the `VARCHAR` type.

 - A TAG column cannot also be the `TIME_COLUMN`.

 - Column-name matching is case-insensitive.

 If you do not need TAG columns, you can write only the time and FIELD columns:

 ```sql
 COPY (
     SELECT time, temperature, humidity
     FROM measurements
     ORDER BY time
 )
 TO '/data/field-only.tsfile'
 (
     FORMAT tsfile,
     TABLE_NAME sensors,
     TIME_COLUMN time
 );
 ```

 ### Type Mapping and NULL Values

 |DuckDB Type|TsFile Type|Notes|
 |---|---|---|
 |BOOLEAN|BOOLEAN|FIELD NULL values are preserved.|
 |INTEGER|INT32|FIELD NULL values are preserved.|
 |BIGINT|INT64|FIELD NULL values are preserved; this type is also used for the time axis.|
 |FLOAT|FLOAT|FIELD NULL values are preserved.|
 |DOUBLE|DOUBLE|FIELD NULL values are preserved.|
 |VARCHAR|STRING|FIELD NULL values are preserved.|
 |BLOB|BLOB|FIELD NULL values are preserved.|
 |TIMESTAMP\_NS|TIMESTAMP|Read as DuckDB `TIMESTAMP_NS`.|

 ## End-to-End Example

 The following workflow reads a time range for device `a` from an existing TsFile, renames columns, writes a new TsFile, and then reads it back for verification:

 ```sql
 LOAD tsfile;

 COPY (
     SELECT time,
            s0 AS device_id,
            s2 AS value,
            CAST(s8 AS VARCHAR) AS day
     FROM read_tsfile('test/data/simple_table_t1.tsfile', 'test')
     WHERE s0 = 'a'
       AND time BETWEEN 1760106022000 AND 1760106024000
     ORDER BY device_id, time
 )
 TO '/tmp/tsfile_subset.tsfile'
 (
     FORMAT tsfile,
     TABLE_NAME subset,
     TIME_COLUMN time,
     TAG_COLUMNS (device_id)
 );

 SELECT time, device_id, value, day
 FROM read_tsfile('/tmp/tsfile_subset.tsfile', 'subset')
 ORDER BY device_id, time;
 ```

 The query returns three rows with timestamps `1760106022000`, `1760106023000`, and `1760106024000`.

 ## Current Limitations and Troubleshooting

 ### Pre-Write Checklist

 1. Confirm that `TIME_COLUMN` exists and has the `BIGINT` type.

 2. Confirm that the TIME column contains no NULL values.

 3. Confirm that the input is sorted by all TAG columns and then by the time column.

 4. If the target file already exists, keep temporary-file handling enabled and set `OVERWRITE true`.

 ### Known Limitations

 - The current version supports one Table-model table in one local TsFile.

 - FIELD filters are not pushed down. A `NOT (...)` TAG expression in any form is not pushed down.

 - Writing DATE FIELD columns is temporarily disabled to avoid conversions that depend on the local time zone. DATE columns in existing TsFiles can still be read. To write such a column, first cast it to `VARCHAR`.

 - When writing directly with `USE_TMP_FILE false`, the target path must not already exist.

 ## Quick Reference

 |Task|Entry Point|
 |---|---|
 |Read TsFile|`read_tsfile('/path/file.tsfile', 'table')`|
 |Inspect columns and data|`SELECT ... FROM read_tsfile(...)`|
 |Push down time and TAG conditions|Use supported comparisons and logical combinations in `WHERE`|
 |Inspect pushdown|`EXPLAIN SELECT ...`|
 |Write TsFile|`COPY (...) TO 'file.tsfile' (FORMAT tsfile, ...)`|
 |Replace an existing file|Default temporary-file handling + `OVERWRITE true`|
	<!--

	Licensed to the Apache Software Foundation (ASF) under one
	or more contributor license agreements. See the NOTICE file
	distributed with this work for additional information
	regarding copyright ownership. The ASF licenses this file
	to you under the Apache License, Version 2.0 (the
	"License"); you may not use this file except in compliance
	with the License. You may obtain a copy of the License at

	http://www.apache.org/licenses/LICENSE-2.0

	Unless required by applicable law or agreed to in writing,
	software distributed under the License is distributed on an
	"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	KIND, either express or implied. See the License for the
	specific language governing permissions and limitations
	under the License.

	-->

	# DuckDB TsFile Extension

	This guide is intended for data engineers, platform developers, and analytics developers who need to analyze or generate Apache TsFile data in DuckDB. The TsFile extension is available as a DuckDB Community Extension. It makes TsFile both a SQL data source and a `COPY` output format, providing a complete read-query-write workflow.

	Remember two things: use `read_tsfile(path, table_name)` to read data, and use `COPY ... TO ... (FORMAT tsfile)` to write data. The current version supports local, Table-model TsFiles. Before writing, sort the data by the TAG columns and then by the time column.

	## Quick Start

	### Start DuckDB and Load the Extension

	For regular use, install and load the DuckDB Community Extension:

	```sql
	INSTALL tsfile FROM community;
	```

	```sql
	LOAD tsfile;
	```

	Install the Community Extension the first time you use it. After that, you only need to run `LOAD tsfile;` whenever you start DuckDB.

	### Read Your First TsFile

	```sql
	SELECT *
	FROM read_tsfile('/data/measurements.tsfile', 'sensors');
	```

	The first argument to `read_tsfile` is a local file path, and the second is the name of the table stored in the TsFile. Each call reads one Table-model table from one file.

	## Reading and Querying

	### Select Columns

	Select the columns you need just as you would from a regular DuckDB table. Projection is pushed down to the TsFile scan whenever possible, reducing the amount of data read:

	```sql
	SELECT time, device_id, temperature
	FROM read_tsfile('/data/measurements.tsfile', 'sensors')
	LIMIT 10;
	```

	### Filter by Time

	The global TsFile time axis is exposed in DuckDB as a `BIGINT`. The time unit is determined by the file or protocol; milliseconds are common. The following operators can be pushed down to the scanner: `=`, `<`, `<=`, `>`, `>=`, and `BETWEEN`.

	```sql
	SELECT time, device_id, temperature
	FROM read_tsfile('/data/measurements.tsfile', 'sensors')
	WHERE time BETWEEN 1700000000000 AND 1700003600000;
	```

	### Filter by TAG

	String TAG columns support equality and range comparisons, null checks, and combinations using `AND` and `OR`. Supported operations include `=`, `!=`, `<`, `<=`, `>`, `>=`, inclusive `BETWEEN`, `IS NULL`, and `IS NOT NULL`.

	```sql
	SELECT time, device_id, temperature
	FROM read_tsfile('/data/measurements.tsfile', 'sensors')
	WHERE (device_id = 'device-01' OR device_id = 'device-02')
	AND time BETWEEN 1700000000000 AND 1700003600000;
	```

	FIELD filters and TAG expressions that cannot be pushed down are still evaluated by DuckDB after the scan. A `NOT (...)` expression in any form is not pushed down.

	### Check Filter Pushdown

	Use `EXPLAIN` to inspect the query plan. If pushdown succeeds, the `READ_TSFILE` node shows `Time Range` and `TAG Filter`:

	```sql
	EXPLAIN
	SELECT time, device_id, temperature
	FROM read_tsfile('/data/measurements.tsfile', 'sensors')
	WHERE device_id = 'device-01'
	AND time >= 1700000000000;
	```

	## Writing TsFile

	### Write with COPY

	Writing uses DuckDB's standard `COPY` interface. Clean, cast, and sort the data in a subquery before writing it to TsFile:

	```sql
	COPY (
	SELECT time, device_id, temperature, humidity
	FROM measurements
	ORDER BY device_id, time
	)
	TO '/data/measurements.tsfile'
	(
	FORMAT tsfile,
	TABLE_NAME sensors,
	TIME_COLUMN time,
	TAG_COLUMNS (device_id)
	);
	```

	`TIME_COLUMN` identifies the time axis. Columns listed in `TAG_COLUMNS` are written as TAG columns, and all other columns are written as FIELD columns. Identifier syntax is recommended for `TABLE_NAME` and `TIME_COLUMN`; use double quotes when an identifier contains special characters.

	### Write Options

	\|Option\|Required\|Description\|
	\|---\|---\|---\|
	\|`FORMAT tsfile`\|Yes\|Selects the TsFile `COPY` writer.\|
	\|`TABLE_NAME`\|No\|The local table name in the output file. The default is `default_table`.\|
	\|`TIME_COLUMN`\|No\|The input column used as the time axis. The default is `time`, and its type must be `BIGINT`.\|
	\|`TAG_COLUMNS`\|No\|A list of TAG column names. If omitted, all non-time columns are written as FIELD columns.\|
	\|`OVERWRITE true`\|No\|Use this option to replace an existing target. Keep the default temporary-file handling enabled.\|

	### TAG\_COLUMNS Constraints

	`TAG_COLUMNS` accepts a list of column names, not a list of values:

	```sql
	TAG_COLUMNS (device_id, region)
	```

	- Each column must exist in the input query.

	- Each column must have the `VARCHAR` type.

	- A TAG column cannot also be the `TIME_COLUMN`.

	- Column-name matching is case-insensitive.

	If you do not need TAG columns, you can write only the time and FIELD columns:

	```sql
	COPY (
	SELECT time, temperature, humidity
	FROM measurements
	ORDER BY time
	)
	TO '/data/field-only.tsfile'
	(
	FORMAT tsfile,
	TABLE_NAME sensors,
	TIME_COLUMN time
	);
	```

	### Type Mapping and NULL Values

	\|DuckDB Type\|TsFile Type\|Notes\|
	\|---\|---\|---\|
	\|BOOLEAN\|BOOLEAN\|FIELD NULL values are preserved.\|
	\|INTEGER\|INT32\|FIELD NULL values are preserved.\|
	\|BIGINT\|INT64\|FIELD NULL values are preserved; this type is also used for the time axis.\|
	\|FLOAT\|FLOAT\|FIELD NULL values are preserved.\|
	\|DOUBLE\|DOUBLE\|FIELD NULL values are preserved.\|
	\|VARCHAR\|STRING\|FIELD NULL values are preserved.\|
	\|BLOB\|BLOB\|FIELD NULL values are preserved.\|
	\|TIMESTAMP\_NS\|TIMESTAMP\|Read as DuckDB `TIMESTAMP_NS`.\|

	## End-to-End Example

	The following workflow reads a time range for device `a` from an existing TsFile, renames columns, writes a new TsFile, and then reads it back for verification:

	```sql
	LOAD tsfile;

	COPY (
	SELECT time,
	s0 AS device_id,
	s2 AS value,
	CAST(s8 AS VARCHAR) AS day
	FROM read_tsfile('test/data/simple_table_t1.tsfile', 'test')
	WHERE s0 = 'a'
	AND time BETWEEN 1760106022000 AND 1760106024000
	ORDER BY device_id, time
	)
	TO '/tmp/tsfile_subset.tsfile'
	(
	FORMAT tsfile,
	TABLE_NAME subset,
	TIME_COLUMN time,
	TAG_COLUMNS (device_id)
	);

	SELECT time, device_id, value, day
	FROM read_tsfile('/tmp/tsfile_subset.tsfile', 'subset')
	ORDER BY device_id, time;
	```

	The query returns three rows with timestamps `1760106022000`, `1760106023000`, and `1760106024000`.

	## Current Limitations and Troubleshooting

	### Pre-Write Checklist

	1. Confirm that `TIME_COLUMN` exists and has the `BIGINT` type.

	2. Confirm that the TIME column contains no NULL values.

	3. Confirm that the input is sorted by all TAG columns and then by the time column.

	4. If the target file already exists, keep temporary-file handling enabled and set `OVERWRITE true`.

	### Known Limitations

	- The current version supports one Table-model table in one local TsFile.

	- FIELD filters are not pushed down. A `NOT (...)` TAG expression in any form is not pushed down.

	- Writing DATE FIELD columns is temporarily disabled to avoid conversions that depend on the local time zone. DATE columns in existing TsFiles can still be read. To write such a column, first cast it to `VARCHAR`.

	- When writing directly with `USE_TMP_FILE false`, the target path must not already exist.

	## Quick Reference

	\|Task\|Entry Point\|
	\|---\|---\|
	\|Read TsFile\|`read_tsfile('/path/file.tsfile', 'table')`\|
	\|Inspect columns and data\|`SELECT ... FROM read_tsfile(...)`\|
	\|Push down time and TAG conditions\|Use supported comparisons and logical combinations in `WHERE`\|
	\|Inspect pushdown\|`EXPLAIN SELECT ...`\|
	\|Write TsFile\|`COPY (...) TO 'file.tsfile' (FORMAT tsfile, ...)`\|
	\|Replace an existing file\|Default temporary-file handling + `OVERWRITE true`\|