How to enable Timeseries support on TiDB

[sykp241095]

Original author: jvmtoamit
Original time: 2023-08-21 18:25:21.55003
Original ID/Slug: 672 / how-to-enable-timeseries-support-on-tidb

Application environment: LAB

TiDB version: TiDB v4.0.11

Problem:

I have use case of timeseries data, I am using Timescale DB and wanted to migrate all data from Timescale DB to TiDB. As per understanding TiDB does not support Timeseries functions by default. Could you please suggest how we can enable timeseries support on TiDB

[sykp241095]

Original author: TiDB-AI-Assistant
Original time: 2023-08-21 18:25:39.932633

TiDB does not have built-in support for Timeseries data, but it provides a feature called External Storage that allows you to use other storage engines to store and query Timeseries data. You can use TimescaleDB as an external storage engine for TiDB to enable Timeseries support. Here are the steps to enable Timeseries support on TiDB using TimescaleDB:

1. Install TimescaleDB on your system. You can follow the installation guide provided by TimescaleDB to install it.

2. Create a TimescaleDB database and table to store your Timeseries data. You can follow the TimescaleDB documentation to create a database and table.

3. Enable the External Storage feature in TiDB by setting the tidb_enable_external_storage system variable to true. You can set this variable globally or for a specific session. When this feature is enabled, TiDB can use external storage engines to store and query data.

4. Create an external table in TiDB that points to the TimescaleDB table you created in step 2. You can use the CREATE EXTERNAL TABLE statement to create an external table. Here is an example:

   CREATE EXTERNAL TABLE ts_data (
     time TIMESTAMP NOT NULL,
     value DOUBLE NOT NULL
   )
   ENGINE 'TIMESCALEDB'
   CONNECTION 'host=timescaledb_host port=timescaledb_port user=timescaledb_user password=timescaledb_password dbname=timescaledb_dbname'
   OPTIONS (
     'timescaledb.table' 'ts_data'
   );
   

   In this example, ts_data is the name of the TimescaleDB table that stores your Timeseries data. You need to replace timescaledb_host, timescaledb_port, timescaledb_user, timescaledb_password, and timescaledb_dbname with the actual values for your TimescaleDB installation.

5. Query the external table in TiDB to retrieve your Timeseries data. You can use standard SQL queries to query the external table. Here is an example:

   SELECT * FROM ts_data WHERE time >= '2022-01-01' AND time < '2022-02-01';
   

   This query retrieves all data from the ts_data table in TimescaleDB that has a timestamp in January 2022.

That’s it! With these steps, you can use TimescaleDB as an external storage engine for TiDB to enable Timeseries support.

: Timescale Documentation | Install self-hosted TimescaleDB
: Timescale Documentation | Hypertables

[sykp241095]

Original author: winkyao
Original time: 2023-08-22 02:33:21.764097

The answer from bot is not correct. TiDB don’t support Timeseries functions yet. tidb is a general purpose database, just like MySQL. So when you want to migrate data from timescaledb to TiDB, it is same with migrating to MySQL.

[sykp241095]

Original author: jvmtoamit
Original time: 2023-08-22 04:25:52.588201

Can’t we use an alternate functions in TiDB for timeseries functions like time_bucket_gapfill, time_sum, time_min, time_avg, time_bucket, create_continuous_agg etc?

[sykp241095]

Original author: winkyao
Original time: 2023-08-22 06:27:14.30234

jvmtoamit:

time_bucket_gapfill

TiDB don’t have such functions. May I know why you want to migrate data from a time series database to TiDB?