Alias table engine

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

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

The `Alias` engine creates a proxy to another table. All read and write operations are forwarded to the target table, while the alias itself stores no data and only maintains a reference to the target table.

References

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

## Creating a Table[​](#creating-a-table "Direct link to Creating a Table")

```
CREATE TABLE [db_name.]alias_name
ENGINE = Alias(target_table)
```

Or with explicit database name:

```
CREATE TABLE [db_name.]alias_name
ENGINE = Alias(target_db, target_table)
```

Note

The `Alias` table does not support explicit column definitions. Columns are automatically inherited from the target table. This ensures that the alias always matches the target table's schema.

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

- **`target_db (optional)`** — Name of the database containing the target table.
- **`target_table`** — Name of the target table.

Note

When `target_db` is omitted and `target_table` is not fully qualified (e.g., `Alias('my_table')`), the target is resolved to the same database as the alias itself, not the session's current database.

## Supported Operations[​](#supported-operations "Direct link to Supported Operations")

The `Alias` table engine supports all major operations.

### Operations on Target Table[​](#operations-on-target "Direct link to Operations on Target Table")

These operations are proxied to the target table:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Support Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `SELECT` ✅ Read data from target table|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `INSERT` ✅ Write data to target table|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `INSERT SELECT` ✅ Batch insert into target table|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ALTER TABLE ADD COLUMN` ✅ Add columns to target table|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ALTER TABLE MODIFY SETTING` ✅ Modify target table settings|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ALTER TABLE PARTITION` ✅ Partition operations (DETACH/ATTACH/DROP) on target|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ALTER TABLE UPDATE` ✅ Update rows in target table (mutation)|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ALTER TABLE DELETE` ✅ Delete rows from target table (mutation)|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `OPTIMIZE TABLE` ✅ Optimize target table (merge parts)|  |  |  | | --- | --- | --- | | `TRUNCATE TABLE` ✅ Truncate target table | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Operations on Alias Itself[​](#operations-on-alias "Direct link to Operations on Alias Itself")

These operations only affect the alias, **not** the target table:

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Support Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `DROP TABLE` ✅ Drop the alias only, target table remains unchanged|  |  |  | | --- | --- | --- | | `RENAME TABLE` ✅ Rename the alias only, target table remains unchanged | | | | | | | | |

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

### Basic Alias Creation[​](#basic-alias-creation "Direct link to Basic Alias Creation")

Create a simple alias in the same database:

```
-- Create source table
CREATE TABLE source_data (
    id UInt32,
    name String,
    value Float64
) ENGINE = MergeTree
ORDER BY id;

-- Insert some data
INSERT INTO source_data VALUES (1, 'one', 10.1), (2, 'two', 20.2);

-- Create alias
CREATE TABLE data_alias ENGINE = Alias('source_data');

-- Query through alias
SELECT * FROM data_alias;
```

```
┌─id─┬─name─┬─value─┐
│  1 │ one  │  10.1 │
│  2 │ two  │  20.2 │
└────┴──────┴───────┘
```

### Cross-Database Alias[​](#cross-database-alias "Direct link to Cross-Database Alias")

Create an alias pointing to a table in a different database:

```
-- Create databases
CREATE DATABASE db1;
CREATE DATABASE db2;

-- Create source table in db1
CREATE TABLE db1.events (
    timestamp DateTime,
    event_type String,
    user_id UInt32
) ENGINE = MergeTree
ORDER BY timestamp;

-- Create alias in db2 pointing to db1.events
CREATE TABLE db2.events_alias ENGINE = Alias('db1', 'events');

-- Or using database.table format
CREATE TABLE db2.events_alias2 ENGINE = Alias('db1.events');

-- Both aliases work identically
INSERT INTO db2.events_alias VALUES (now(), 'click', 100);
SELECT * FROM db2.events_alias2;
```

### Write Operations Through Alias[​](#write-operations "Direct link to Write Operations Through Alias")

All write operations are forwarded to the target table:

```
CREATE TABLE metrics (
    ts DateTime,
    metric_name String,
    value Float64
) ENGINE = MergeTree
ORDER BY ts;

CREATE TABLE metrics_alias ENGINE = Alias('metrics');

-- Insert through alias
INSERT INTO metrics_alias VALUES 
    (now(), 'cpu_usage', 45.2),
    (now(), 'memory_usage', 78.5);

-- Insert with SELECT
INSERT INTO metrics_alias 
SELECT now(), 'disk_usage', number * 10 
FROM system.numbers 
LIMIT 5;

-- Verify data is in the target table
SELECT count() FROM metrics;  -- Returns 7
SELECT count() FROM metrics_alias;  -- Returns 7
```

### Schema Modification[​](#schema-modification "Direct link to Schema Modification")

Alter operations modify the target table schema:

```
CREATE TABLE users (
    id UInt32,
    name String
) ENGINE = MergeTree
ORDER BY id;

CREATE TABLE users_alias ENGINE = Alias('users');

-- Add column through alias
ALTER TABLE users_alias ADD COLUMN email String DEFAULT '';

-- Column is added to target table
DESCRIBE users;
```

```
┌─name──┬─type───┬─default_type─┬─default_expression─┐
│ id    │ UInt32 │              │                    │
│ name  │ String │              │                    │
│ email │ String │ DEFAULT      │ ''                 │
└───────┴────────┴──────────────┴────────────────────┘
```

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

UPDATE and DELETE operations are supported:

```
CREATE TABLE products (
    id UInt32,
    name String,
    price Float64,
    status String DEFAULT 'active'
) ENGINE = MergeTree
ORDER BY id;

CREATE TABLE products_alias ENGINE = Alias('products');