title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/integrations/time-series.md)

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

Not supported in ClickHouse Cloud

A table engine storing time series, i.e. a set of values associated with timestamps and tags (or labels):

```
metric_name1[tag1=value1, tag2=value2, ...] = {timestamp1: value1, timestamp2: value2, ...}
metric_name2[...] = ...
```

References

This is an experimental feature that may change in backwards-incompatible ways in the future releases.
Enable usage of the TimeSeries table engine
with [allow\_experimental\_time\_series\_table](/docs/operations/settings/settings#allow_experimental_time_series_table) setting.
Input the command `set allow_experimental_time_series_table = 1`.

## Syntax[​](#syntax "Direct link to Syntax")

```
CREATE TABLE name [(columns)] ENGINE=TimeSeries
[SETTINGS var1=value1, ...]
[DATA db.data_table_name | DATA ENGINE data_table_engine(arguments)]
[TAGS db.tags_table_name | TAGS ENGINE tags_table_engine(arguments)]
[METRICS db.metrics_table_name | METRICS ENGINE metrics_table_engine(arguments)]
```

## Usage[​](#usage "Direct link to Usage")

It's easier to start with everything set by default (it's allowed to create a `TimeSeries` table without specifying a list of columns):

```
CREATE TABLE my_table ENGINE=TimeSeries
```

Then this table can be used with the following protocols (a port must be assigned in the server configuration):

- [prometheus remote-write](/docs/interfaces/prometheus#remote-write)
- [prometheus remote-read](/docs/interfaces/prometheus#remote-read)

## Target tables[​](#target-tables "Direct link to Target tables")

A `TimeSeries` table doesn't have its own data, everything is stored in its target tables.
This is similar to how a [materialized view](/docs/sql-reference/statements/create/view#materialized-view) works,
with the difference that a materialized view has one target table
whereas a `TimeSeries` table has three target tables named [data](#data-table), [tags](#tags-table), and [metrics](#metrics-table).

The target tables can be either specified explicitly in the `CREATE TABLE` query
or the `TimeSeries` table engine can generate inner target tables automatically.

The target tables are the following:

### Data table[​](#data-table "Direct link to Data table")

The *data* table contains time series associated with some identifier.

The *data* table must have columns:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Name Mandatory? Default type Possible types Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `id` [x] `UUID` any Identifies a combination of a metric names and tags|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `timestamp` [x] `DateTime64(3)` `DateTime64(X)` A time point|  |  |  |  |  | | --- | --- | --- | --- | --- | | `value` [x] `Float64` `Float32` or `Float64` A value associated with the `timestamp` | | | | | | | | | | | | | | | | | | | |

### Tags table[​](#tags-table "Direct link to Tags table")

The *tags* table contains identifiers calculated for each combination of a metric name and tags.

The *tags* table must have columns:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Name Mandatory? Default type Possible types Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `id` [x] `UUID` any (must match the type of `id` in the [data](#data-table) table) An `id` identifies a combination of a metric name and tags. The DEFAULT expression specifies how to calculate such an identifier| `metric_name` [x] `LowCardinality(String)` `String` or `LowCardinality(String)` The name of a metric|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `<tag_value_column>` [ ] `String` `String` or `LowCardinality(String)` or `LowCardinality(Nullable(String))` The value of a specific tag, the tag's name and the name of a corresponding column are specified in the [tags\_to\_columns](#settings) setting| `tags` [x] `Map(LowCardinality(String), String)` `Map(String, String)` or `Map(LowCardinality(String), String)` or `Map(LowCardinality(String), LowCardinality(String))` Map of tags excluding the tag `__name__` containing the name of a metric and excluding tags with names enumerated in the [tags\_to\_columns](#settings) setting| `all_tags` [ ] `Map(String, String)` `Map(String, String)` or `Map(LowCardinality(String), String)` or `Map(LowCardinality(String), LowCardinality(String))` Ephemeral column, each row is a map of all the tags excluding only the tag `__name__` containing the name of a metric. The only purpose of that column is to be used while calculating `id`| `min_time` [ ] `Nullable(DateTime64(3))` `DateTime64(X)` or `Nullable(DateTime64(X))` Minimum timestamp of time series with that `id`. The column is created if [store\_min\_time\_and\_max\_time](#settings) is `true`| `max_time` [ ] `Nullable(DateTime64(3))` `DateTime64(X)` or `Nullable(DateTime64(X))` Maximum timestamp of time series with that `id`. The column is created if [store\_min\_time\_and\_max\_time](#settings) is `true` | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Metrics table[​](#metrics-table "Direct link to Metrics table")

The *metrics* table contains some information about metrics been collected, the types of those metrics and their descriptions.

The *metrics* table must have columns:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Name Mandatory? Default type Possible types Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `metric_family_name` [x] `String` `String` or `LowCardinality(String)` The name of a metric family|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `type` [x] `String` `String` or `LowCardinality(String)` The type of a metric family, one of "counter", "gauge", "summary", "stateset", "histogram", "gaugehistogram"|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `unit` [x] `String` `String` or `LowCardinality(String)` The unit used in a metric|  |  |  |  |  | | --- | --- | --- | --- | --- | | `help` [x] `String` `String` or `LowCardinality(String)` The description of a metric | | | | | | | | | | | | | | | | | | | | | | | | |

Any row inserted into a `TimeSeries` table will be in fact stored in those three target tables.
A `TimeSeries` table contains all those columns from the [data](#data-table), [tags](#tags-table), [metrics](#metrics-table) tables.

## Creation[​](#creation "Direct link to Creation")

There are multiple ways to create a table with the `TimeSeries` table engine.
The simplest statement

```
CREATE TABLE my_table ENGINE=TimeSeries
```