title_wip

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

The table engine (type of table) determines:

- How and where data is stored, where to write it to, and where to read it from.
- Which queries are supported, and how.
- Concurrent data access.
- Use of indexes, if present.
- Whether multithread request execution is possible.
- Data replication parameters.

## Engine families[​](#engine-families "Direct link to Engine families")

### MergeTree[​](#mergetree "Direct link to MergeTree")

The most universal and functional table engines for high-load tasks. The property shared by these engines is quick data insertion with subsequent background data processing. `MergeTree` family engines support data replication (with [Replicated\*](/docs/engines/table-engines/mergetree-family/replication) versions of engines), partitioning, secondary data-skipping indexes, and other features not supported in other engines.

Engines in the family:

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| MergeTree Engines|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree)| [ReplacingMergeTree](/docs/engines/table-engines/mergetree-family/replacingmergetree)| [SummingMergeTree](/docs/engines/table-engines/mergetree-family/summingmergetree)| [AggregatingMergeTree](/docs/engines/table-engines/mergetree-family/aggregatingmergetree)| [CollapsingMergeTree](/docs/engines/table-engines/mergetree-family/collapsingmergetree)| [VersionedCollapsingMergeTree](/docs/engines/table-engines/mergetree-family/versionedcollapsingmergetree)| [GraphiteMergeTree](/docs/engines/table-engines/mergetree-family/graphitemergetree)| [CoalescingMergeTree](/docs/engines/table-engines/mergetree-family/coalescingmergetree) | | | | | | | | |

### Log[​](#log "Direct link to Log")

Lightweight [engines](/docs/engines/table-engines/log-family) with minimum functionality. They're the most effective when you need to quickly write many small tables (up to approximately 1 million rows) and read them later as a whole.

Engines in the family:

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

### Integration engines[​](#integration-engines "Direct link to Integration engines")

Engines for communicating with other data storage and processing systems.

Engines in the family:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Integration Engines|  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [ODBC](/docs/engines/table-engines/integrations/odbc)| [JDBC](/docs/engines/table-engines/integrations/jdbc)| [MySQL](/docs/engines/table-engines/integrations/mysql)| [MongoDB](/docs/engines/table-engines/integrations/mongodb)| [Redis](/docs/engines/table-engines/integrations/redis)| [HDFS](/docs/engines/table-engines/integrations/hdfs)| [S3](/docs/engines/table-engines/integrations/s3)| [Kafka](/docs/engines/table-engines/integrations/kafka)| [EmbeddedRocksDB](/docs/engines/table-engines/integrations/embedded-rocksdb)| [RabbitMQ](/docs/engines/table-engines/integrations/rabbitmq)| [PostgreSQL](/docs/engines/table-engines/integrations/postgresql)| [S3Queue](/docs/engines/table-engines/integrations/s3queue)| [TimeSeries](/docs/engines/table-engines/special/time_series) | | | | | | | | | | | | | |

### Special engines[​](#special-engines "Direct link to Special engines")

Engines in the family:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Special Engines|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [Distributed](/docs/engines/table-engines/special/distributed)| [Dictionary](/docs/engines/table-engines/special/dictionary)| [Merge](/docs/engines/table-engines/special/merge)| [Executable](/docs/engines/table-engines/special/executable)| [File](/docs/engines/table-engines/special/file)| [Null](/docs/engines/table-engines/special/null)| [Set](/docs/engines/table-engines/special/set)| [Join](/docs/engines/table-engines/special/join)| [URL](/docs/engines/table-engines/special/url)| [View](/docs/engines/table-engines/special/view)| [Memory](/docs/engines/table-engines/special/memory)| [Buffer](/docs/engines/table-engines/special/buffer)| [External Data](/docs/engines/table-engines/special/external-data)| [GenerateRandom](/docs/engines/table-engines/special/generate)| [KeeperMap](/docs/engines/table-engines/special/keeper-map)| [FileLog](/docs/engines/table-engines/special/filelog) | | | | | | | | | | | | | | | | |

## Virtual columns[​](#table_engines-virtual_columns "Direct link to Virtual columns")

A virtual column is an integral table engine attribute that is defined in the engine source code.

You shouldn't specify virtual columns in the `CREATE TABLE` query, and you can't see them in `SHOW CREATE TABLE` and `DESCRIBE TABLE` query results. Virtual columns are also read-only, so you can't insert data into virtual columns.

To select data from a virtual column, you must specify its name in the `SELECT` query. `SELECT *` does not return values from virtual columns.

If you create a table with a column that has the same name as one of the table virtual columns, the virtual column becomes inaccessible. We do not recommend doing this. To help avoid conflicts, virtual column names are usually prefixed with an underscore.

- `_table` — Contains the name of the table from which data was read. Type: [String](/docs/sql-reference/data-types/string).

  Regardless of the table engine being used, each table includes a universal virtual column named `_table`.

  When querying a table with the merge table engine, you can set the constant conditions on `_table` in the `WHERE/PREWHERE` clause (for example, `WHERE _table='xyz'`). In this case the read operation is performed only for that tables where the condition on `_table` is satisfied, so the `_table` column acts as an index.

  When using queries formatted like `SELECT ... FROM (... UNION ALL ...)`, we can determine which actual table the returned rows originate from by specifying the `_table` column.

- [Engine families](#engine-families)
  - [MergeTree](#mergetree)- [Log](#log)- [Integration engines](#integration-engines)- [Special engines](#special-engines)- [Virtual columns](#table_engines-virtual_columns)

Was this page helpful?