Skip to content

Apache IoTDB for time series data hands-on

Preamble

Apache IoTDB is a time series database management system that primarily focus on managing and analyze IoT time series data. The first question I have asked myself is the difference with Apache Druid and this is coming directly from Apache IoTDB official documentation:

Apache Druid focuses on data analysis with OLAP workload

During my testing I have seen another big limitation for the dataset I have planned to use which is not a bug in itself but something written in the DNA of Apache IoTDB that we do not have in Apache Druid…

I have kept things simple and used a Stand-Alone deployment for my testing. My test server is running Red Hat Enterprise Linux release 8.10 (Ootpa) with 12 cores and 64GB of memory. I have used Java 17 and Apache IoTDB release 2.0.2.

I have to say that the Apache IoTDB official documentation is really poor and for most of my trials I had to access directly the GitHub repository to find some good references. For some important functions there is also no consistency in parameter names like cursor and session functions with username and user parameters. I rate it would be quite bold to go in production with Apache IoTDB at the time of writing this blog post.

Apache IoTDB installation

To keep things separated I create an iotdb account in dba group. Java 17 is required and this is what I have already installed for Apache Druid:

[iotdb@server01 ~]$ java -version
openjdk version "17.0.14" 2025-01-21 LTS
OpenJDK Runtime Environment (Red_Hat-17.0.14.0.7-1) (build 17.0.14+7-LTS)
OpenJDK 64-Bit Server VM (Red_Hat-17.0.14.0.7-1) (build 17.0.14+7-LTS, mixed mode, sharing)

I have downloaded apache-iotdb-2.0.2-all-bin.zip and unzip it to a dedicated VsFS in /iotdb. I have added /iotdb/apache-iotdb-2.0.2-all-bin/sbin to the PATH of my itodb Linux account.

Configure same memory quota for ConfigNode and DataNode:

[iotdb@server01 ~]$ grep ^MEMORY_SIZE /iotdb/apache-iotdb-2.0.2-all-bin/conf/confignode-env.sh
MEMORY_SIZE=5G
[iotdb@server01 ~]$ grep ^MEMORY_SIZE /iotdb/apache-iotdb-2.0.2-all-bin/conf/datanode-env.sh
MEMORY_SIZE=5G

Start ConfigNode with:

[iotdb@server01 ~]$ start-confignode.sh -d
----------------------------
Starting IoTDB ConfigNode
----------------------------
All parameters are -d
Notice: in some systems, ConfigNode must run in sudo mode to write data. The process may fail.
Checking whether the ports are already occupied...
Warning: If you do not use sudo, the checking may not detect all the occupied ports.
/iotdb/apache-iotdb-2.0.2-all-bin/sbin/../conf/confignode-env.sh: line 30: ulimit: open files: cannot modify limit: Operation not permitted
Warning: Failed to set max number of files to be 65535, maybe you need to use 'sudo ulimit -n 65535' to set it when you use iotdb ConfigNode in production environments.
WARN:
WARN: the value of net.core.somaxconn (=128) is too small, please set it to a larger value using the following command.
WARN:     sudo sysctl -w net.core.somaxconn=65535
WARN: The original net.core.somaxconn value will be set back when the os reboots.
WARN:
setting local JMX...
ConfigNode on heap memory size = 4096MB, off heap memory size = 1024MB
If you want to change this configuration, please check conf/confignode-env.sh.

Start DataNode with:

[iotdb@server01 ~]$ start-datanode.sh -d
All parameters are -d
Notice: in some systems, DataNode must run in sudo mode to write data. The process may fail.
Checking whether the ports are already occupied...
Warning: If you do not use sudo, the checking may not detect all the occupied ports.
---------------------
Starting IoTDB DataNode
---------------------
/iotdb/apache-iotdb-2.0.2-all-bin/sbin/../conf/datanode-env.sh: line 30: ulimit: open files: cannot modify limit: Operation not permitted
Warning: Failed to set max number of files to be 65535, maybe you need to use 'sudo ulimit -n 65535' to set it when you use iotdb in production environments.
WARN:
WARN: the value of net.core.somaxconn (=128) is too small, please set it to a larger value using the following command.
WARN:     sudo sysctl -w net.core.somaxconn=65535
WARN: The original net.core.somaxconn value will be set back when the os reboots.
WARN:
setting local JMX...
DataNode on heap memory size = 4096MB, off heap memory size = 1024MB
If you want to change this configuration, please check conf/datanode-env.sh.

You can now connect and perform your first query:

[iotdb@server01 ~]$ start-cli.sh -h server01.domain.com -p 6667
---------------------
Starting IoTDB Cli
---------------------
 _____       _________  ______   ______
|_   _|     |  _   _  ||_   _ `.|_   _ \
  | |   .--.|_/ | | \_|  | | `. \ | |_) |
  | | / .'`\ \  | |      | |  | | |  __'.
 _| |_| \__. | _| |_    _| |_.' /_| |__) |
|_____|'.__.' |_____|  |______.'|_______/  version 2.0.2 (Build: db89301)


Successfully login at server01.domain.com:6667
IoTDB> show cluster
+------+----------+-------+---------------+------------+-------+---------+
|NodeID|  NodeType| Status|InternalAddress|InternalPort|Version|BuildInfo|
+------+----------+-------+---------------+------------+-------+---------+
|     0|ConfigNode|Running|      127.0.0.1|       10710|  2.0.2|  db89301|
|     1|  DataNode|Running|      127.0.0.1|       10730|  2.0.2|  db89301|
+------+----------+-------+---------------+------------+-------+---------+
Total line number = 2
It costs 0.245s
IoTDB>

Apache IoTDB data loading

Schema modeling

Now I can create a database. I will use the name nyc for this database (database name must be prefix by root).

IoTDB> create database nyc
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 700: Error occurred while parsing SQL to physical plan: line 1:16 mismatched input 'nyc' expecting ROOT
IoTDB> create database root.nyc
Msg: The statement is executed successfully.
IoTDB> show databases
+--------+-----------------------+---------------------+-------------------+---------------------+
|Database|SchemaReplicationFactor|DataReplicationFactor|TimePartitionOrigin|TimePartitionInterval|
+--------+-----------------------+---------------------+-------------------+---------------------+
|root.nyc|                      1|                    1|                  0|            604800000|
+--------+-----------------------+---------------------+-------------------+---------------------+
Total line number = 1
It costs 0.110s

I create a bunch of time series to handle the data I plan to load, same as my other poste on same series (http). I’m not creating a time series for the started_at column as this column will be used as the mandatory internal Time column of Apache IoTDB:

IoTDB> create timeseries root.nyc.trips.ended_at with datatype=timestamp, encoding=TS_2DIFF, compressor=lz4;
Msg: The statement is executed successfully.
IoTDB> create timeseries root.nyc.trips.passenger_count with datatype=float, encoding=GORILLA, compressor=lz4;
Msg: The statement is executed successfully.
IoTDB> create timeseries root.nyc.trips.distance with datatype=float, encoding=GORILLA, compressor=lz4;
Msg: The statement is executed successfully.
IoTDB> create timeseries root.nyc.trips.tip_amount with datatype=float, encoding=GORILLA, compressor=lz4;
Msg: The statement is executed successfully.
IoTDB> create timeseries root.nyc.trips.total_amount with datatype=float, encoding=GORILLA, compressor=lz4;
Msg: The statement is executed successfully.
IoTDB> show timeseries root.nyc.**
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
|                    Timeseries|Alias|Database| DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
|       root.nyc.trips.distance| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|   root.nyc.trips.total_amount| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.nyc.trips.passenger_count| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|     root.nyc.trips.tip_amount| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|       root.nyc.trips.ended_at| null|root.nyc|TIMESTAMP|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 5
It costs 0.021s

You could also create them as an aligned time series with:

IoTDB> create aligned timeseries root.nyc.trips(ended_at timestamp encoding=ts_2diff compressor=lz4,
> passenger_count float encoding=gorilla compressor=lz4,
> distance float encoding=gorilla compressor=lz4,
> tip_amount float encoding=gorilla compressor=lz4,
> total_amount float encoding=gorilla compressor=lz4
> );
>
Msg: The statement is executed successfully.
IoTDB> show timeseries root.nyc.**;
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
|                    Timeseries|Alias|Database| DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
|       root.nyc.trips.distance| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|   root.nyc.trips.total_amount| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.nyc.trips.passenger_count| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|     root.nyc.trips.tip_amount| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|       root.nyc.trips.ended_at| null|root.nyc|TIMESTAMP|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 5
It costs 0.017s

To clean everything you can use:

IoTDB> show timeseries root.nyc.**
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
|                    Timeseries|Alias|Database| DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
|       root.nyc.trips.distance| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|   root.nyc.trips.total_amount| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.nyc.trips.passenger_count| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|     root.nyc.trips.tip_amount| null|root.nyc|    FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|       root.nyc.trips.ended_at| null|root.nyc|TIMESTAMP|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
+------------------------------+-----+--------+---------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 5
It costs 0.021s
IoTDB> delete timeseries root.nyc.trips.ended_at;
Msg: The statement is executed successfully.
IoTDB> delete timeseries root.nyc.trips.passenger_count;
Msg: The statement is executed successfully.
IoTDB> delete timeseries root.nyc.trips.distance;
Msg: The statement is executed successfully.
IoTDB> delete timeseries root.nyc.trips.tip_amount;
Msg: The statement is executed successfully.
IoTDB> delete timeseries root.nyc.trips.total_amount;
Msg: The statement is executed successfully.
IoTDB> show timeseries root.nyc.**;
+----------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|Timeseries|Alias|Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+----------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
+----------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
Empty set.
It costs 0.018s

If you do multiple loading you can clean the inserted data with:

IoTDB> delete from root.nyc.trips.**;
Msg: The statement is executed successfully.
IoTDB> select count(*) from root.nyc.trips;
+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
|count(root.nyc.trips.distance)|count(root.nyc.trips.total_amount)|count(root.nyc.trips.passenger_count)|count(root.nyc.trips.tip_amount)|count(root.nyc.trips.ended_at)|
+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
|                             0|                                 0|                                    0|                               0|                             0|
+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
Total line number = 1
It costs 0.046s

Write data

I have used the Python API to load data and this Python 3.13.2 script to try to load the month of January 2023:

import pandas as pd
import numpy as np
from iotdb.Session import Session
from iotdb.utils.NumpyTablet import NumpyTablet
from iotdb.utils.IoTDBConstants import TSDataType


directory = '/u01/nyc/'
filename = 'yellow_tripdata_2023-01.parquet'

df01 = pd.read_parquet(directory + filename)
df01 = df01[["tpep_pickup_datetime", "tpep_dropoff_datetime", "passenger_count", "trip_distance", "tip_amount", "total_amount"]]
df01.rename(columns = {'tpep_pickup_datetime': 'started_at', 'tpep_dropoff_datetime': 'ended_at', 'trip_distance': 'distance'}, inplace=True)
df01 = df01.astype({'started_at': 'int64', 'ended_at': 'int64'})

df01.fillna(0)

df01['started_at'] = df01['started_at'].div(1000)
df01['ended_at'] = df01['ended_at'].div(1000)

np01 = df01.to_numpy()

host_ = "server01.domain.com"
port_ = "6667"
user_ = "root"
password_ = "root"
zone_id_ = 'UTC'
session = Session(host = host_, port = port_, user = user_, password = password_, zone_id = zone_id_)
session.open(enable_rpc_compression=False)

measurements_ = ["ended_at", "passenger_count", "distance", "tip_amount", "total_amount"]
data_types_ = [ TSDataType.TIMESTAMP, TSDataType.FLOAT, TSDataType.FLOAT, TSDataType.FLOAT, TSDataType.FLOAT]

# np_values_ = np01[ :, [1,2,3,4,5]]
np_values_ = [ np01[ :, 1], np01[ :, 2], np01[ :, 3], np01[ :, 4], np01[ :, 5] ]

# np_timestamps_ = np.array(np01[ :, 0], np.dtype(">i8"))
np_timestamps_ = np01[ :, 0]

np_tablet_ = NumpyTablet("root.nyc.trips", measurements_, data_types_, np_values_, np_timestamps_)
session.insert_tablet(np_tablet_)
session.close()

Remark:
I had to divide the time column by 1000 and first convert them to integer because Apache IoTDB accept timestamp only in miliseconds.

Use this requirements.txt file:

apache-iotdb
pandas
pyarrow

But all the 3,066,766 rows of the source Parquet file have not been inserted:

IoTDB> select count(*) from root.nyc.trips;
+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
|count(root.nyc.trips.distance)|count(root.nyc.trips.total_amount)|count(root.nyc.trips.passenger_count)|count(root.nyc.trips.tip_amount)|count(root.nyc.trips.ended_at)|
+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
|                       1610975|                           1610975|                              1610975|                         1610975|                       1610975|
+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
Total line number = 1
It costs 0.045s

I initially though it was because of the non-ordered nature of the source data, so I have added an order by on the Pandas dataframe:

df01.sort_values(by='started_at')

But still same number of rows inserted…

Then I realized that the started_at column (or tpep_pickup_datetime in soruce Parquet file) contains many duplicate value, yes looking at the number of NYC yellow taxi some trip are starting at the exact same time:

>>> df01.groupby(['started_at']).count()
              ended_at  passenger_count  distance  tip_amount  total_amount
started_at
1.230765e+12         1                1         1           1             1
1.230765e+12         1                1         1           1             1
1.666633e+12         1                1         1           1             1
1.666642e+12         1                1         1           1             1
1.666648e+12         1                1         1           1             1
...                ...              ...       ...         ...           ...
1.675210e+12         1                1         1           1             1
1.675210e+12         1                1         1           1             1
1.675210e+12         1                1         1           1             1
1.675210e+12         1                1         1           1             1
1.675213e+12         1                1         1           1             1

[1610975 rows x 5 columns]

And *strangely* the number of unique rows is matching what I have been able to insert…

To find an example of this I have used:

>>> df01 = pd.read_parquet(directory + filename)
>>> df01 = df01[["tpep_pickup_datetime", "tpep_dropoff_datetime", "passenger_count", "trip_distance", "tip_amount", "total_amount"]]
>>> df01.rename(columns = {'tpep_pickup_datetime': 'started_at', 'tpep_dropoff_datetime': 'ended_at', 'trip_distance': 'distance'}, inplace=True)
>>> df01 = df01.astype({'started_at': 'datetime64[ns]', 'ended_at': 'datetime64[ns]'})
>>> df01['started_at'].duplicated()
0          False
1          False
2          False
3          False
4          False
           ...
3066761     True
3066762    False
3066763     True
3066764     True
3066765     True
Name: started_at, Length: 3066766, dtype: bool
>>> df01.query('started_at=="2023-01-31 23:07:32"')
python-input-124:1: FutureWarning: The behavior of 'isin' with dtype=datetime64[ns] and castable values (e.g. strings) is deprecated.
In a future version, these will not be considered matching by isin. Explicitly cast to the appropriate dtype before calling isin instead.
  df01.query('started_at=="2023-01-31 23:07:32"')
                 started_at            ended_at  passenger_count  distance  tip_amount  total_amount
2993233 2023-01-31 23:07:32 2023-01-31 23:35:30              1.0      6.03        7.06         42.36
3066765 2023-01-31 23:07:32 2023-01-31 23:21:56              NaN      2.85        2.00         21.97

And by design the Time column of a time series in Apache IoTDB is a primary key:

iotdb01
iotdb01

Query data

The Apache IoTDB query is like:

select
  avg(total_amount) as avg_total_amount,
  max_value(total_amount) as max_total_amount,
  min_value(total_amount) as min_total_amount
from root.nyc.trips
where total_amount > 0
group by ([2023-01-01T00:00:00, 2024-01-01T00:00:00), 1mo, 1mo);

I will not perform any performance comparison with other time series databases as the number of rows in Apache IoTDB will be much less. First create a UV environment:

[iotdb@server01 ~]$ export HTTPS_PROXY='http://proxy_account:proxy_password@proxy_server:proxy_port'
[iotdb@server01 ~]$ pip3.9 install uv
Defaulting to user installation because normal site-packages is not writeable
Collecting uv
  Downloading uv-0.6.16-py3-none-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (17.3 MB)
     |████████████████████████████████| 17.3 MB 10.4 MB/s
Installing collected packages: uv
Successfully installed uv-0.6.16
[iotdb@server01 ~]$ uv venv --python 3.13 iotdb
Using CPython 3.13.3
Creating virtual environment at: iotdb
Activate with: source iotdb/bin/activate
[iotdb@server01 ~]$ source iotdb/bin/activate
(iotdb) [iotdb@server01 ~]$ python -V
Python 3.13.3
(iotdb) [iotdb@server01 ~]$ uv pip install -r requirements.txt
Using Python 3.13.3 environment at: iotdb
Resolved 13 packages in 6.99s
      Built thrift==0.21.0
Prepared 13 packages in 9.76s
Installed 13 packages in 83ms
 + apache-iotdb==2.0.2
 + greenlet==3.2.1
 + numpy==2.2.5
 + pandas==2.2.3
 + pyarrow==19.0.1
 + python-dateutil==2.9.0.post0
 + pytz==2025.2
 + six==1.17.0
 + sqlalchemy==2.0.40
 + sqlalchemy-utils==0.41.2
 + thrift==0.21.0
 + typing-extensions==4.13.2
 + tzdata==2025.2
(iotdb) [iotdb@server01 ~]$ uv pip list
Using Python 3.13.3 environment at: iotdb
Package           Version
----------------- -----------
apache-iotdb      2.0.2
greenlet          3.2.1
numpy             2.2.5
pandas            2.2.3
pyarrow           19.0.1
python-dateutil   2.9.0.post0
pytz              2025.2
six               1.17.0
sqlalchemy        2.0.40
sqlalchemy-utils  0.41.2
thrift            0.21.0
typing-extensions 4.13.2
tzdata            2025.2

But just for the sake of seeing what would be the performance I used this script to load all Parquet files:

import os
import pandas as pd
import numpy as np
from iotdb.Session import Session
from iotdb.utils.NumpyTablet import NumpyTablet
from iotdb.utils.IoTDBConstants import TSDataType


directory = '/u01/nyc/'
parquet_files = [f for f in os.listdir(directory) if f.endswith('.parquet')]

host_ = "server01.domain.com"
port_ = "6667"
user_ = "root"
password_ = "root"
zone_id_ = 'UTC'
session = Session(host = host_, port = port_, user = user_, password = password_, zone_id = zone_id_)
session.open(enable_rpc_compression=False)

measurements_ = ["ended_at", "passenger_count", "distance", "tip_amount", "total_amount"]
data_types_ = [ TSDataType.TIMESTAMP, TSDataType.FLOAT, TSDataType.FLOAT, TSDataType.FLOAT, TSDataType.FLOAT]

for filename in parquet_files:
  print("Loading " + filename)
  df01 = pd.read_parquet(directory + filename)
  df01 = df01[["tpep_pickup_datetime", "tpep_dropoff_datetime", "passenger_count", "trip_distance", "tip_amount", "total_amount"]]
  df01.rename(columns = {'tpep_pickup_datetime': 'started_at', 'tpep_dropoff_datetime': 'ended_at', 'trip_distance': 'distance'}, inplace=True)
  df01 = df01.astype({'started_at': 'int64', 'ended_at': 'int64'})
  df01.fillna(0)
  df01['started_at'] = df01['started_at'].div(1000)
  df01['ended_at'] = df01['ended_at'].div(1000)
  np01 = df01.to_numpy()
  np_values_ = [ np01[ :, 1], np01[ :, 2], np01[ :, 3], np01[ :, 4], np01[ :, 5] ]
  np_timestamps_ = np01[ :, 0]
  np_tablet_ = NumpyTablet("root.nyc.trips", measurements_, data_types_, np_values_, np_timestamps_)
  try:
    session.insert_tablet(np_tablet_)
  except Exception as e:
    print(f"Error loading {filename}: {e}")
  print("Loaded " + filename)

session.close()

It has taken 20 sedonds to load full 2023 year, for this amount of (unique) data:

IoTDB> set time_zone=UTC
Time zone has set to utc
IoTDB> select count(*) from root.nyc.trips group by([2023-01-01T00:00:00, 2024-01-01T00:00:00), 1mo, 1mo);
+------------------------+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
|                    Time|count(root.nyc.trips.distance)|count(root.nyc.trips.total_amount)|count(root.nyc.trips.passenger_count)|count(root.nyc.trips.tip_amount)|count(root.nyc.trips.ended_at)|
+------------------------+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
|2023-01-01T00:00:00.000Z|                       1610929|                           1610929|                              1610929|                         1610929|                       1610929|
|2023-02-01T00:00:00.000Z|                       1493956|                           1493956|                              1493956|                         1493956|                       1493956|
|2023-03-01T00:00:00.000Z|                       1695439|                           1695439|                              1695439|                         1695439|                       1695439|
|2023-04-01T00:00:00.000Z|                       1642688|                           1642688|                              1642688|                         1642688|                       1642688|
|2023-05-01T00:00:00.000Z|                       1719035|                           1719035|                              1719035|                         1719035|                       1719035|
|2023-06-01T00:00:00.000Z|                       1659267|                           1659267|                              1659267|                         1659267|                       1659267|
|2023-07-01T00:00:00.000Z|                       1583500|                           1583500|                              1583500|                         1583500|                       1583500|
|2023-08-01T00:00:00.000Z|                       1563700|                           1563700|                              1563700|                         1563700|                       1563700|
|2023-09-01T00:00:00.000Z|                       1448873|                           1448873|                              1448873|                         1448873|                       1448873|
|2023-10-01T00:00:00.000Z|                       1726883|                           1726883|                              1726883|                         1726883|                       1726883|
|2023-11-01T00:00:00.000Z|                       1637679|                           1637679|                              1637679|                         1637679|                       1637679|
|2023-12-01T00:00:00.000Z|                       1666064|                           1666064|                              1666064|                         1666064|                       1666064|
+------------------------+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
Total line number = 12
It costs 7.457s

And the query is executed ion 2.5 – 3 seconds around:

IoTDB> select
>   avg(total_amount) as avg_total_amount,
>   max_value(total_amount) as max_total_amount,
>   min_value(total_amount) as min_total_amount
> from root.nyc.trips
> where total_amount > 0
> group by ([2023-01-01T00:00:00, 2024-01-01T00:00:00), 1mo, 1mo);
+------------------------+------------------+----------------+----------------+
|                    Time|  avg_total_amount|max_total_amount|min_total_amount|
+------------------------+------------------+----------------+----------------+
|2023-01-01T00:00:00.000Z|27.680326029629736|           667.1|            0.01|
|2023-02-01T00:00:00.000Z| 27.49360312232878|          2208.1|            0.01|
|2023-03-01T00:00:00.000Z|28.439924586759922|          982.95|             1.0|
|2023-04-01T00:00:00.000Z|28.903988255794978|          2451.0|            0.01|
|2023-05-01T00:00:00.000Z| 29.57254512294676|          6304.9|            0.06|
|2023-06-01T00:00:00.000Z|29.646406474458175|           944.3|            0.01|
|2023-07-01T00:00:00.000Z|29.370501641295636|           996.0|            0.01|
|2023-08-01T00:00:00.000Z| 29.38517431756829|           834.1|            0.01|
|2023-09-01T00:00:00.000Z|30.367014911153195|        12015.47|            0.01|
|2023-10-01T00:00:00.000Z|29.875522240550637|          6339.0|            0.01|
|2023-11-01T00:00:00.000Z| 29.35080909211478|         1299.44|            0.01|
|2023-12-01T00:00:00.000Z| 29.25594896914235|         4269.16|            0.01|
+------------------------+------------------+----------------+----------------+
Total line number = 12
It costs 2.714s

Issues encountered

Time zone

Same as with PostgreSQL (TimescaleDB) I ad to play witht he time zone to match the time zone of the source NYC yellow cab data:

IoTDB> show time_zone
Current time zone: Europe/Paris
IoTDB> set time_zone=UTC
Time zone has set to utc
IoTDB> select count(*) from root.nyc.trips group by([2023-01-01T00:00:00, 2024-01-01T00:00:00), 1mo);
+------------------------+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
|                    Time|count(root.nyc.trips.distance)|count(root.nyc.trips.total_amount)|count(root.nyc.trips.passenger_count)|count(root.nyc.trips.tip_amount)|count(root.nyc.trips.ended_at)|
+------------------------+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
|2023-01-01T00:00:00.000Z|                       1610927|                           1610927|                              1610927|                         1610927|                       1610927|
|2023-02-01T00:00:00.000Z|                            10|                                10|                                   10|                              10|                            10|
|2023-03-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-04-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-05-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-06-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-07-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-08-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-09-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-10-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-11-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
|2023-12-01T00:00:00.000Z|                             0|                                 0|                                    0|                               0|                             0|
+------------------------+------------------------------+----------------------------------+-------------------------------------+--------------------------------+------------------------------+
Total line number = 12
It costs 0.072s

Time column uniqueness

To demonstrate the primary key on Time column of a time series do the following. use something like https://www.epochconverter.com/ to convert date to Unix Timestamp::

IoTDB> create database root.test
Msg: The statement is executed successfully.
IoTDB> create timeseries root.test.test01 with datatype=int32, encoding=TS_2DIFF, compressor=lz4;
Msg: The statement is executed successfully.
IoTDB> select * from root.test;
+----+----------------+
|Time|root.test.test01|
+----+----------------+
+----+----------------+
Empty set.
It costs 0.051s
IoTDB> insert into root.test(timestamp, test01) values(1745404200000, 1);
Msg: The statement is executed successfully.
IoTDB> select * from root.test;
+-----------------------------+----------------+
|                         Time|root.test.test01|
+-----------------------------+----------------+
|2025-04-23T12:30:00.000+02:00|               1|
+-----------------------------+----------------+
Total line number = 1
It costs 0.027s
IoTDB> insert into root.test(timestamp, test01) values(1745404200000, 2);
Msg: The statement is executed successfully.
IoTDB> select * from root.test;
+-----------------------------+----------------+
|                         Time|root.test.test01|
+-----------------------------+----------------+
|2025-04-23T12:30:00.000+02:00|               2|
+-----------------------------+----------------+
Total line number = 1
It costs 0.027s

The second insert has not inserted a new row but instead update the value. It makes sense in case of real IoT data like when you want to overwrite wrong values but not for the dataset I have used…

References

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *