CREATE TEMPORARY TABLE

Temporary table support

Please note that temporary tables are not replicated. As a result, there is no guarantee that data inserted into a temporary table will be available in other replicas. The primary use case where temporary tables can be useful is for querying or joining small external datasets during a single session.

ClickHouse supports temporary tables which have the following characteristics:

Temporary tables disappear when the session ends, including if the connection is lost.

A temporary table uses the Memory table engine when engine is not specified and it may use any table engine except Replicated and KeeperMap engines.

The DB can’t be specified for a temporary table. It is created outside of databases.

Impossible to create a temporary table with distributed DDL query on all cluster servers (by using ON CLUSTER): this table exists only in the current session.

If a temporary table has the same name as another one and a query specifies the table name without specifying the DB, the temporary table will be used.

For distributed query processing, temporary tables with Memory engine used in a query are passed to remote servers.

Syntax

To create a temporary table, use the following syntax:

CREATE [OR REPLACE] TEMPORARY TABLE [IF NOT EXISTS] table_name
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) [ENGINE = engine]

In most cases, temporary tables are not created manually, but when using external data for a query, or for distributed (GLOBAL) IN. For more information, see the appropriate sections

It’s possible to use tables with ENGINE = Memory instead of temporary tables.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Temporary table support

Syntax

​Temporary table support

​Syntax

Temporary table support

Syntax