ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/log-family/index.md)

Not supported in ClickHouse Cloud

These engines were developed for scenarios when you need to quickly write many small tables (up to about 1 million rows) and read them later as a whole.

Engines of the family:

|  |  |  |  |
| --- | --- | --- | --- |
| Log Engines|  |  |  | | --- | --- | --- | | [StripeLog](/docs/engines/table-engines/log-family/stripelog)| [Log](/docs/engines/table-engines/log-family/log)| [TinyLog](/docs/engines/table-engines/log-family/tinylog) | | | |

`Log` family table engines can store data to [HDFS](/docs/engines/table-engines/integrations/hdfs) or [S3](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-s3) distributed file systems.

This engine is not for log data.

Despite the name, \*Log table engines are not meant for the storage of log data. They should only be used for small volumes which need to be written quickly.

## Common properties[​](#common-properties "Direct link to Common properties")

Engines:

- Store data on a disk.
- Append data to the end of file when writing.
- Support locks for concurrent data access.

  During `INSERT` queries, the table is locked, and other queries for reading and writing data both wait for the table to unlock. If there are no data writing queries, any number of data reading queries can be performed concurrently.
- Do not support [mutations](/docs/sql-reference/statements/alter#mutations).
- Do not support indexes.

  This means that `SELECT` queries for ranges of data are not efficient.
- Do not write data atomically.

  You can get a table with corrupted data if something breaks the write operation, for example, abnormal server shutdown.

## Differences[​](#differences "Direct link to Differences")

The `TinyLog` engine is the simplest in the family and provides the poorest functionality and lowest efficiency. The `TinyLog` engine does not support parallel data reading by several threads in a single query. It reads data slower than other engines in the family that support parallel reading from a single query and it uses almost as many file descriptors as the `Log` engine because it stores each column in a separate file. Use it only in simple scenarios.

The `Log` and `StripeLog` engines support parallel data reading. When reading data, ClickHouse uses multiple threads. Each thread processes a separate data block. The `Log` engine uses a separate file for each column of the table. `StripeLog` stores all the data in one file. As a result, the `StripeLog` engine uses fewer file descriptors, but the `Log` engine provides higher efficiency when reading data.

- [Common properties](#common-properties)- [Differences](#differences)

Was this page helpful?
title_wip

#### Performance Considerations[​](#qbit-performance "Direct link to Performance Considerations")

The performance benefit of `QBit` comes from reduced I/O operations, as less data needs to be read from storage when using lower precision. Moreover, when the `QBit` contains `Float32` data, if the precision parameter is 16 or below, there will be additional benefits from reduced computation. The precision parameter directly controls the trade-off between accuracy and speed:

- **Higher precision** (closer to the original data width): More accurate results, slower queries
- **Lower precision**: Faster queries with approximate results, reduced memory usage

### References[​](#references "Direct link to References")

Blogs:

- [Vector Search with ClickHouse - Part 1](https://clickhouse.com/blog/vector-search-clickhouse-p1)
- [Vector Search with ClickHouse - Part 2](https://clickhouse.com/blog/vector-search-clickhouse-p2)
- [We built a vector search engine that lets you choose precision at query time](https://clickhouse.com/blog/qbit-vector-search)

- [Exact vector search](#exact-nearest-neighbor-search)
  - [Example](#exact-nearest-neighbor-search-example)- [Approximate vector search](#approximate-nearest-neighbor-search)
    - [Vector Similarity Indexes](#vector-similarity-index)- [Quantized Bit (QBit)](#approximate-nearest-neighbor-search-qbit)- [References](#references)

Was this page helpful?
title_wip

Note that on the operating server, you cannot manually change the set of parts or their data on the file system, since the server will not know about it. For non-replicated tables, you can do this when the server is stopped, but it isn't recommended. For replicated tables, the set of parts cannot be changed in any case.

ClickHouse allows you to perform operations with the partitions: delete them, copy from one table to another, or create a backup. See the list of all operations in the section [Manipulations With Partitions and Parts](/docs/sql-reference/statements/alter/partition).

## Group By optimisation using partition key[​](#group-by-optimisation-using-partition-key "Direct link to Group By optimisation using partition key")

For some combinations of table's partition key and query's group by key it might be possible to execute aggregation for each partition independently.
Then we'll not have to merge partially aggregated data from all execution threads at the end,
because we provided with the guarantee that each group by key value cannot appear in working sets of two different threads.

The typical example is:

```
CREATE TABLE session_log
(
    UserID UInt64,
    SessionID UUID
)
ENGINE = MergeTree
PARTITION BY sipHash64(UserID) % 16
ORDER BY tuple();

SELECT
    UserID,
    COUNT()
FROM session_log
GROUP BY UserID;
```

Note

Performance of such a query heavily depends on the table layout. Because of that the optimisation is not enabled by default.

The key factors for a good performance:

- number of partitions involved in the query should be sufficiently large (more than `max_threads / 2`), otherwise query will under-utilize the machine
- partitions shouldn't be too small, so batch processing won't degenerate into row-by-row processing
- partitions should be comparable in size, so all threads will do roughly the same amount of work

References

It's recommended to apply some hash function to columns in `partition by` clause in order to distribute data evenly between partitions.

Relevant settings are:

- `allow_aggregate_partitions_independently` - controls if the use of optimisation is enabled
- `force_aggregate_partitions_independently` - forces its use when it's applicable from the correctness standpoint, but getting disabled by internal logic that estimates its expediency
- `max_number_of_partitions_for_independent_aggregation` - hard limit on the maximal number of partitions table could have

- [Group By optimisation using partition key](#group-by-optimisation-using-partition-key)

Was this page helpful?
title_wip

### Configuration Example with rules types[​](#configuration-typed-example "Direct link to Configuration Example with rules types")

```
<graphite_rollup>
    <version_column_name>Version</version_column_name>
    <pattern>
        <rule_type>plain</rule_type>
        <regexp>click_cost</regexp>
        <function>any</function>
        <retention>
            <age>0</age>
            <precision>5</precision>
        </retention>
        <retention>
            <age>86400</age>
            <precision>60</precision>
        </retention>
    </pattern>
    <pattern>
        <rule_type>tagged</rule_type>
        <regexp>^((.*)|.)min\?</regexp>
        <function>min</function>
        <retention>
            <age>0</age>
            <precision>5</precision>
        </retention>
        <retention>
            <age>86400</age>
            <precision>60</precision>
        </retention>
    </pattern>
    <pattern>
        <rule_type>tagged</rule_type>
        <regexp><![CDATA[^someName\?(.*&)*tag1=value1(&|$)]]></regexp>
        <function>min</function>
        <retention>
            <age>0</age>
            <precision>5</precision>
        </retention>
        <retention>
            <age>86400</age>
            <precision>60</precision>
        </retention>
    </pattern>
    <pattern>
        <rule_type>tag_list</rule_type>
        <regexp>someName;tag2=value2</regexp>
        <retention>
            <age>0</age>
            <precision>5</precision>
        </retention>
        <retention>
            <age>86400</age>
            <precision>60</precision>
        </retention>
    </pattern>
    <default>
        <function>max</function>
        <retention>
            <age>0</age>
            <precision>60</precision>
        </retention>
        <retention>
            <age>3600</age>
            <precision>300</precision>
        </retention>
        <retention>
            <age>86400</age>
            <precision>3600</precision>
        </retention>
    </default>
</graphite_rollup>
```

Note

Data rollup is performed during merges. Usually, for old partitions, merges are not started, so for rollup it is necessary to trigger an unscheduled merge using [optimize](/docs/sql-reference/statements/optimize). Or use additional tools, for example [graphite-ch-optimizer](https://github.com/innogames/graphite-ch-optimizer).

- [Creating a table](#creating-table)- [Rollup configuration](#rollup-configuration)
    - [Required columns](#required-columns)- [Patterns](#patterns)- [Configuration Example without rules types](#configuration-example)- [Configuration Example with rules types](#configuration-typed-example)

Was this page helpful?
title_wip

```
CREATE TABLE rmt_example
(
    `number` UInt16
)
ENGINE = ReplacingMergeTree
ORDER BY number

INSERT INTO rmt_example SELECT floor(randUniform(0, 100)) AS number
FROM numbers(1000000000)

0 rows in set. Elapsed: 19.958 sec. Processed 1.00 billion rows, 8.00 GB (50.11 million rows/s., 400.84 MB/s.)
```

Querying without `FINAL` produces an incorrect count (exact result will vary depending on merges):

```
SELECT count()
FROM rmt_example

┌─count()─┐
│     200 │
└─────────┘

1 row in set. Elapsed: 0.002 sec.
```

Adding final produces a correct result:

```
SELECT count()
FROM rmt_example
FINAL

┌─count()─┐
│     100 │
└─────────┘

1 row in set. Elapsed: 0.002 sec.
```

For further details on `FINAL`, including how to optimize `FINAL` performance, we recommend reading our [detailed guide on ReplacingMergeTree](/docs/guides/replacing-merge-tree).

- [Creating a table](#creating-a-table)- [ReplacingMergeTree parameters](#replacingmergetree-parameters)
    - [`ver`](#ver)- [`is_deleted`](#is_deleted)- [Query clauses](#query-clauses)- [Query time de-duplication & FINAL](#query-time-de-duplication--final)

Was this page helpful?
title_wip

┌─site─┬─browser─┬─impressions─┬─clicks─┐
│   12 │ Chrome  │          20 │      1 │
│   12 │ Firefox │          11 │      3 │
│   12 │ IE      │          22 │      0 │
│   12 │ Opera   │           5 │      1 │
│   10 │ Chrome  │           4 │      3 │
└──────┴─────────┴─────────────┴────────┘
```

When requesting data, use the [sumMap(key, value)](/docs/sql-reference/aggregate-functions/reference/summap) function for aggregation of `Map`.

For nested data structure, you do not need to specify its columns in the tuple of columns for summation.

## Related content[​](#related-content "Direct link to Related content")

- Blog: [Using Aggregate Combinators in ClickHouse](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)

- [Creating a table](#creating-a-table)
  - [Parameters of SummingMergeTree](#parameters-of-summingmergetree)- [Query clauses](#query-clauses)- [Usage example](#usage-example)- [Data processing](#data-processing)
      - [Common rules for summation](#common-rules-for-summation)- [The summation in the AggregateFunction columns](#the-summation-in-the-aggregatefunction-columns)- [Nested structures](#nested-structures)- [Related content](#related-content)

Was this page helpful?
Dictionary table engine

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

The `Dictionary` engine displays the [dictionary](/docs/sql-reference/statements/create/dictionary) data as a ClickHouse table.

## Example[​](#example "Direct link to Example")

As an example, consider a dictionary of `products` with the following configuration:

```
<dictionaries>
    <dictionary>
        <name>products</name>
        <source>
            <odbc>
                <table>products</table>
                <connection_string>DSN=some-db-server</connection_string>
            </odbc>
        </source>
        <lifetime>
            <min>300</min>
            <max>360</max>
        </lifetime>
        <layout>
            <flat/>
        </layout>
        <structure>
            <id>
                <name>product_id</name>
            </id>
            <attribute>
                <name>title</name>
                <type>String</type>
                <null_value></null_value>
            </attribute>
        </structure>
    </dictionary>
</dictionaries>
```

Query the dictionary data:

```
SELECT
    name,
    type,
    key,
    attribute.names,
    attribute.types,
    bytes_allocated,
    element_count,
    source
FROM system.dictionaries
WHERE name = 'products'
```

```
┌─name─────┬─type─┬─key────┬─attribute.names─┬─attribute.types─┬─bytes_allocated─┬─element_count─┬─source──────────┐
│ products │ Flat │ UInt64 │ ['title']       │ ['String']      │        23065376 │        175032 │ ODBC: .products │
└──────────┴──────┴────────┴─────────────────┴─────────────────┴─────────────────┴───────────────┴─────────────────┘
```

You can use the [dictGet\*](/docs/sql-reference/functions/ext-dict-functions) functions to get the dictionary data in this format.

This view isn't helpful when you need to get raw data, or when performing a `JOIN` operation. For these cases, you can use the `Dictionary` engine, which displays the dictionary data in a table.

Syntax:

```
CREATE TABLE %table_name% (%fields%) engine = Dictionary(%dictionary_name%)`
```

Usage example:

```
CREATE TABLE products (product_id UInt64, title String) ENGINE = Dictionary(products);
```

Ok

Take a look at what's in the table.

```
SELECT * FROM products LIMIT 1;
```

```
┌────product_id─┬─title───────────┐
│        152689 │ Some item       │
└───────────────┴─────────────────┘
```

**See Also**

- [Dictionary function](/docs/sql-reference/table-functions/dictionary)

- [Example](#example)

Was this page helpful?