> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-3a82795f.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

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

# Functions

export const CloudNotSupportedBadge = () => {
  return <div className="cloudNotSupportedBadge">
            <div className="cloudNotSupportedIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.5" d="M6.33366 12.6666L12.3739 12.6667C13.6593 12.6667 14.7073 11.6187 14.7073 10.3334C14.7073 9.04804 13.6593 8.00003 12.3739 8.00003C12.3739 8.00003 12.3337 7.66659 12.0003 7.33325M10.667 5.33322C8.00033 2.33325 4.45395 4.78537 4.14195 6.68203C2.55728 6.7627 1.29395 8.06203 1.29395 9.6667C1.29395 11.3234 2.66699 12.6666 4.00033 12.6666" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.5" d="M2.66699 14L12.0003 4.66663" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>

        </div>
            Not supported in ClickHouse Cloud
        </div>;
};

export const ExperimentalBadge = () => {
  return <div className="experimentalBadge">
            <div className="experimentalIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.25" d="M5.5 2H10.5" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.25" d="M9.50015 2V6.19625L13.4283 12.7425C13.4738 12.8183 13.4985 12.9049 13.4996 12.9934C13.5008 13.0818 13.4785 13.169 13.435 13.246C13.3914 13.323 13.3283 13.3871 13.2519 13.4317C13.1755 13.4764 13.0886 13.4999 13.0002 13.5H3.00015C2.91164 13.5 2.8247 13.4766 2.74822 13.432C2.67174 13.3874 2.60847 13.3233 2.56487 13.2463C2.52126 13.1693 2.49889 13.082 2.50004 12.9935C2.50119 12.905 2.52582 12.8184 2.5714 12.7425L6.50015 6.19625V2" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.25" d="M4.47656 9.56754C5.30344 9.41254 6.47656 9.47942 7.99969 10.25C10.0153 11.2707 11.4216 11.0569 12.2184 10.7282" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>
        </div>
            Experimental feature. <u><a href="/docs/beta-and-experimental-features#experimental-features">Learn more.</a></u>
        </div>;
};

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

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

<Info>
  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](/reference/settings/session-settings#allow_experimental_time_series_table) setting.
  Input the command `set allow_experimental_time_series_table = 1`.
</Info>

<h2 id="syntax">
  Syntax
</h2>

```sql theme={null}
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)]
```

<h2 id="usage">
  Usage
</h2>

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

```sql theme={null}
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](/concepts/features/interfaces/prometheus#remote-write)
* [prometheus remote-read](/concepts/features/interfaces/prometheus#remote-read)

<h2 id="target-tables">
  Target tables
</h2>

A `TimeSeries` table doesn't have its own data, everything is stored in its target tables.
This is similar to how a [materialized view](/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:

<h3 id="data-table">
  Data table
</h3>

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`             |

<h3 id="tags-table">
  Tags table
</h3>

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`                                                           |

<h3 id="metrics-table">
  Metrics table
</h3>

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.

<h2 id="creation">
  Creation
</h2>

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

```sql theme={null}
CREATE TABLE my_table ENGINE=TimeSeries
```

will actually create the following table (you can see that by executing `SHOW CREATE TABLE my_table`):

```sql theme={null}
CREATE TABLE my_table
(
    `id` UUID DEFAULT reinterpretAsUUID(sipHash128(metric_name, all_tags)),
    `timestamp` DateTime64(3),
    `value` Float64,
    `metric_name` LowCardinality(String),
    `tags` Map(LowCardinality(String), String),
    `all_tags` Map(String, String),
    `min_time` Nullable(DateTime64(3)),
    `max_time` Nullable(DateTime64(3)),
    `metric_family_name` String,
    `type` String,
    `unit` String,
    `help` String
)
ENGINE = TimeSeries
DATA ENGINE = MergeTree ORDER BY (id, timestamp)
DATA INNER UUID '01234567-89ab-cdef-0123-456789abcdef'
TAGS ENGINE = AggregatingMergeTree PRIMARY KEY metric_name ORDER BY (metric_name, id)
TAGS INNER UUID '01234567-89ab-cdef-0123-456789abcdef'
METRICS ENGINE = ReplacingMergeTree ORDER BY metric_family_name
METRICS INNER UUID '01234567-89ab-cdef-0123-456789abcdef'
```

So the columns were generated automatically and also there are three inner UUIDs in this statement -
one per each inner target table that was created.
(Inner UUIDs are not shown normally until setting
[show\_table\_uuid\_in\_table\_create\_query\_if\_not\_nil](/reference/settings/session-settings#show_table_uuid_in_table_create_query_if_not_nil)
is set.)

Inner target tables have names like `.inner_id.data.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`,
`.inner_id.tags.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`, `.inner_id.metrics.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
and each target table has columns which is a subset of the columns of the main `TimeSeries` table:

```sql theme={null}
CREATE TABLE default.`.inner_id.data.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `id` UUID,
    `timestamp` DateTime64(3),
    `value` Float64
)
ENGINE = MergeTree
ORDER BY (id, timestamp)
```

```sql theme={null}
CREATE TABLE default.`.inner_id.tags.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `id` UUID DEFAULT reinterpretAsUUID(sipHash128(metric_name, all_tags)),
    `metric_name` LowCardinality(String),
    `tags` Map(LowCardinality(String), String),
    `all_tags` Map(String, String) EPHEMERAL,
    `min_time` SimpleAggregateFunction(min, Nullable(DateTime64(3))),
    `max_time` SimpleAggregateFunction(max, Nullable(DateTime64(3)))
)
ENGINE = AggregatingMergeTree
PRIMARY KEY metric_name
ORDER BY (metric_name, id)
```

```sql theme={null}
CREATE TABLE default.`.inner_id.metrics.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `metric_family_name` String,
    `type` String,
    `unit` String,
    `help` String
)
ENGINE = ReplacingMergeTree
ORDER BY metric_family_name
```

<h2 id="adjusting-column-types">
  Adjusting types of columns
</h2>

You can adjust the types of almost any column of the inner target tables by specifying them explicitly
while defining the main table. For example,

```sql theme={null}
CREATE TABLE my_table
(
    timestamp DateTime64(6)
) ENGINE=TimeSeries
```

will make the inner [data](#data-table) table store timestamp in microseconds instead of milliseconds:

```sql theme={null}
CREATE TABLE default.`.inner_id.data.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `id` UUID,
    `timestamp` DateTime64(6),
    `value` Float64
)
ENGINE = MergeTree
ORDER BY (id, timestamp)
```

<h2 id="id-column">
  The `id` column
</h2>

The `id` column contains identifiers, every identifier is calculated for a combination of a metric name and tags.
The DEFAULT expression for the `id` column is an expression which will be used to calculate such identifiers.
Both the type of the `id` column and that expression can be adjusted by specifying them explicitly:

```sql theme={null}
CREATE TABLE my_table
(
  id UInt64 DEFAULT sipHash64(metric_name, all_tags)
)
ENGINE=TimeSeries
```

<h2 id="tags-and-all-tags">
  The `tags` and `all_tags` columns
</h2>

There are two columns containing maps of tags - `tags` and `all_tags`. In this example they mean the same, however they can be different
if setting `tags_to_columns` is used. This setting allows to specify that a specific tag should be stored in a separate column instead of storing
in a map inside the `tags` column:

```sql theme={null}
CREATE TABLE my_table
ENGINE = TimeSeries 
SETTINGS tags_to_columns = {'instance': 'instance', 'job': 'job'}
```

This statement will add columns:

```sql theme={null}
`instance` String,
`job` String
```

to the definition of both `my_table` and its inner [tags](#tags-table) target table. In this case the `tags` column will not contain tags `instance` and `job`,
but the `all_tags` column will contain them. The `all_tags` column is ephemeral and its only purpose to be used in the DEFAULT expression
for the `id` column.

The types of columns can be adjusted by specifying them explicitly:

```sql theme={null}
CREATE TABLE my_table (
  instance LowCardinality(String),
  job LowCardinality(Nullable(String))
)
ENGINE=TimeSeries
SETTINGS tags_to_columns = {'instance': 'instance', 'job': 'job'}
```

<h2 id="inner-table-engines">
  Table engines of inner target tables
</h2>

By default inner target tables use the following table engines:

* the [data](#data-table) table uses [MergeTree](/reference/engines/table-engines/mergetree-family/mergetree);
* the [tags](#tags-table) table uses [AggregatingMergeTree](/reference/engines/table-engines/mergetree-family/aggregatingmergetree) because the same data is often inserted multiple times to this table so we need a way
  to remove duplicates, and also because it's required to do aggregation for columns `min_time` and `max_time`;
* the [metrics](#metrics-table) table uses [ReplacingMergeTree](/reference/engines/table-engines/mergetree-family/replacingmergetree) because the same data is often inserted multiple times to this table so we need a way
  to remove duplicates.

Other table engines also can be used for inner target tables if it's specified so:

```sql theme={null}
CREATE TABLE my_table ENGINE=TimeSeries
DATA ENGINE=ReplicatedMergeTree
TAGS ENGINE=ReplicatedAggregatingMergeTree
METRICS ENGINE=ReplicatedReplacingMergeTree
```

<h2 id="external-target-tables">
  External target tables
</h2>

It's possible to make a `TimeSeries` table use a manually created table:

```sql theme={null}
CREATE TABLE data_for_my_table
(
    `id` UUID,
    `timestamp` DateTime64(3),
    `value` Float64
)
ENGINE = MergeTree
ORDER BY (id, timestamp);

CREATE TABLE tags_for_my_table ...

CREATE TABLE metrics_for_my_table ...

CREATE TABLE my_table ENGINE=TimeSeries DATA data_for_my_table TAGS tags_for_my_table METRICS metrics_for_my_table;
```

<h2 id="settings">
  Settings
</h2>

Here is a list of settings which can be specified while defining a `TimeSeries` table:

| Name                                 | Type | Default | Description                                                                                                                                                                                                                                        |
| ------------------------------------ | ---- | ------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `tags_to_columns`                    | Map  | {}      | Map specifying which tags should be put to separate columns in the [tags](#tags-table) table. Syntax: `{'tag1': 'column1', 'tag2' : column2, ...}`                                                                                                 |
| `use_all_tags_column_to_generate_id` | Bool | true    | When generating an expression to calculate an identifier of a time series, this flag enables using the `all_tags` column in that calculation                                                                                                       |
| `store_min_time_and_max_time`        | Bool | true    | If set to true then the table will store `min_time` and `max_time` for each time series                                                                                                                                                            |
| `aggregate_min_time_and_max_time`    | Bool | true    | When creating an inner target `tags` table, this flag enables using `SimpleAggregateFunction(min, Nullable(DateTime64(3)))` instead of just `Nullable(DateTime64(3))` as the type of the `min_time` column, and the same for the `max_time` column |
| `filter_by_min_time_and_max_time`    | Bool | true    | If set to true then the table will use the `min_time` and `max_time` columns for filtering time series                                                                                                                                             |

Here is a list of functions supporting a `TimeSeries` table as an argument:

* [timeSeriesData](/reference/functions/table-functions/timeSeriesData)
* [timeSeriesTags](/reference/functions/table-functions/timeSeriesTags)
* [timeSeriesMetrics](/reference/functions/table-functions/timeSeriesMetrics)