ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/order-by.md)

```
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY ORDER BY new_expression
```

The command changes the [sorting key](/docs/engines/table-engines/mergetree-family/mergetree) of the table to `new_expression` (an expression or a tuple of expressions). Primary key remains the same.

The command is lightweight in a sense that it only changes metadata. To keep the property that data part rows are ordered by the sorting key expression you cannot add expressions containing existing columns to the sorting key (only columns added by the `ADD COLUMN` command in the same `ALTER` query, without default column value).

Note

It only works for tables in the [`MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree) family (including [replicated](/docs/engines/table-engines/mergetree-family/replication) tables).

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/role.md)

Changes roles.

Syntax:

```
ALTER ROLE [IF EXISTS] name1 [RENAME TO new_name |, name2 [,...]] 
    [ON CLUSTER cluster_name]
    [DROP ALL PROFILES]
    [DROP ALL SETTINGS]
    [DROP PROFILES 'profile_name' [,...] ]
    [DROP SETTINGS variable [,...] ]
    [ADD|MODIFY SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | PROFILE 'profile_name'] [,...]
    [ADD PROFILES 'profile_name' [,...] ]
```

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/row-policy.md)

Changes row policy.

Syntax:

```
ALTER [ROW] POLICY [IF EXISTS] name1 [ON CLUSTER cluster_name1] ON [database1.]table1 [RENAME TO new_name1]
        [, name2 [ON CLUSTER cluster_name2] ON [database2.]table2 [RENAME TO new_name2] ...]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [FOR SELECT]
    [USING {condition | NONE}][,...]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]
```

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/sample-by.md)

The following operations are available:

## MODIFY[​](#modify "Direct link to MODIFY")

```
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY SAMPLE BY new_expression
```

The command changes the [sampling key](/docs/engines/table-engines/mergetree-family/mergetree) of the table to `new_expression` (an expression or a tuple of expressions). The primary key must contain the new sample key.

## REMOVE[​](#remove "Direct link to REMOVE")

```
ALTER TABLE [db].name [ON CLUSTER cluster] REMOVE SAMPLE BY
```

The command removes the [sampling key](/docs/engines/table-engines/mergetree-family/mergetree) of the table.

The commands `MODIFY` and `REMOVE` are lightweight in the sense that they only change metadata or remove files.

Note

It only works for tables in the [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree) family (including [replicated](/docs/engines/table-engines/mergetree-family/replication) tables).

- [MODIFY](#modify)- [REMOVE](#remove)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/setting.md)

There is a set of queries to change table settings. You can modify settings or reset them to default values. A single query can change several settings at once.
If a setting with the specified name does not exist, then the query raises an exception.

**Syntax**

```
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY|RESET SETTING ...
```

Note

These queries can be applied to [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree) tables only.

## MODIFY SETTING[​](#modify-setting "Direct link to MODIFY SETTING")

Changes table settings.

**Syntax**

```
MODIFY SETTING setting_name=value [, ...]
```

**Example**

```
CREATE TABLE example_table (id UInt32, data String) ENGINE=MergeTree() ORDER BY id;

ALTER TABLE example_table MODIFY SETTING max_part_loading_threads=8, max_parts_in_total=50000;
```

## RESET SETTING[​](#reset-setting "Direct link to RESET SETTING")

Resets table settings to their default values. If a setting is in a default state, then no action is taken.

**Syntax**

```
RESET SETTING setting_name [, ...]
```

**Example**

```
CREATE TABLE example_table (id UInt32, data String) ENGINE=MergeTree() ORDER BY id
    SETTINGS max_part_loading_threads=8;

ALTER TABLE example_table RESET SETTING max_part_loading_threads;
```

**See Also**

- [MergeTree settings](/docs/operations/settings/merge-tree-settings)

- [MODIFY SETTING](#modify-setting)- [RESET SETTING](#reset-setting)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/settings-profile.md)

Changes settings profiles.

Syntax:

```
ALTER SETTINGS PROFILE [IF EXISTS] name1 [RENAME TO new_name |, name2 [,...]] 
    [ON CLUSTER cluster_name]
    [DROP ALL PROFILES]
    [DROP ALL SETTINGS]
    [DROP SETTINGS variable [,...] ]
    [DROP PROFILES 'profile_name' [,...] ]
    [ADD|MODIFY SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | INHERIT 'profile_name'] [,...]
    [TO {{role1 | user1 [, role2 | user2 ...]} | NONE | ALL | ALL EXCEPT {role1 | user1 [, role2 | user2 ...]}}]
    [ADD PROFILES 'profile_name' [,...] ]
```

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/skipping-index.md)

The following operations are available:

## ADD INDEX[​](#add-index "Direct link to ADD INDEX")

`ALTER TABLE [db.]table_name [ON CLUSTER cluster] ADD INDEX [IF NOT EXISTS] name expression TYPE type [GRANULARITY value] [FIRST|AFTER name]` - Adds index description to tables metadata.

## DROP INDEX[​](#drop-index "Direct link to DROP INDEX")

`ALTER TABLE [db.]table_name [ON CLUSTER cluster] DROP INDEX [IF EXISTS] name` - Removes index description from tables metadata and deletes index files from disk. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

## MATERIALIZE INDEX[​](#materialize-index "Direct link to MATERIALIZE INDEX")

`ALTER TABLE [db.]table_name [ON CLUSTER cluster] MATERIALIZE INDEX [IF EXISTS] name [IN PARTITION partition_name]` - Rebuilds the secondary index `name` for the specified `partition_name`. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations). If `IN PARTITION` part is omitted then it rebuilds the index for the whole table data.

## CLEAR INDEX[​](#clear-index "Direct link to CLEAR INDEX")

`ALTER TABLE [db.]table_name [ON CLUSTER cluster] CLEAR INDEX [IF EXISTS] name [IN PARTITION partition_name]` - Deletes the secondary index files from disk without removing description. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

The commands `ADD`, `DROP`, and `CLEAR` are lightweight in the sense that they only change metadata or remove files.
Also, they are replicated, syncing indices metadata via ClickHouse Keeper or ZooKeeper.

Note

Index manipulation is supported only for tables with [`*MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree) engine (including [replicated](/docs/engines/table-engines/mergetree-family/replication) variants).

- [ADD INDEX](#add-index)- [DROP INDEX](#drop-index)- [MATERIALIZE INDEX](#materialize-index)- [CLEAR INDEX](#clear-index)

Was this page helpful?
Manipulating Column Statistics

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/statistics.md)

Not supported in ClickHouse Cloud

The following operations are available:

- `ALTER TABLE [db].table ADD STATISTICS [IF NOT EXISTS] (column list) TYPE (type list)` - Adds statistic description to tables metadata.
- `ALTER TABLE [db].table MODIFY STATISTICS (column list) TYPE (type list)` - Modifies statistic description to tables metadata.
- `ALTER TABLE [db].table DROP STATISTICS [IF EXISTS] (column list)` - Removes statistics from the metadata of the specified columns and deletes all statistics objects in all parts for the specified columns.
- `ALTER TABLE [db].table CLEAR STATISTICS [IF EXISTS] (column list)` - Deletes all statistics objects in all parts for the specified columns. Statistics objects can be rebuild using `ALTER TABLE MATERIALIZE STATISTICS`.
- `ALTER TABLE [db.]table MATERIALIZE STATISTICS (ALL | [IF EXISTS] (column list))` - Rebuilds the statistic for columns. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

The first two commands are lightweight in a sense that they only change metadata or remove files.

Also, they are replicated, syncing statistics metadata via ZooKeeper.

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

Adding two statistics types to two columns:

```
ALTER TABLE t1 MODIFY STATISTICS c, d TYPE TDigest, Uniq;
```

Note

Statistic are supported only for [`*MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree) engine tables (including [replicated](/docs/engines/table-engines/mergetree-family/replication) variants).

- [Example:](#example)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/ttl.md)

Note

If you are looking for details on using TTL for managing old data, check out the [Manage Data with TTL](/docs/guides/developer/ttl) user guide. The docs below demonstrate how to alter or remove an existing TTL rule.

## MODIFY TTL[​](#modify-ttl "Direct link to MODIFY TTL")

You can change [table TTL](/docs/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl) with a request of the following form:

```
ALTER TABLE [db.]table_name [ON CLUSTER cluster] MODIFY TTL ttl_expression;
```

## REMOVE TTL[​](#remove-ttl "Direct link to REMOVE TTL")

TTL-property can be removed from table with the following query:

```
ALTER TABLE [db.]table_name [ON CLUSTER cluster] REMOVE TTL
```

**Example**

Consider the table with table `TTL`:

```
CREATE TABLE table_with_ttl
(
    event_time DateTime,
    UserID UInt64,
    Comment String
)
ENGINE MergeTree()
ORDER BY tuple()
TTL event_time + INTERVAL 3 MONTH
SETTINGS min_bytes_for_wide_part = 0;

INSERT INTO table_with_ttl VALUES (now(), 1, 'username1');

INSERT INTO table_with_ttl VALUES (now() - INTERVAL 4 MONTH, 2, 'username2');
```

Run `OPTIMIZE` to force `TTL` cleanup:

```
OPTIMIZE TABLE table_with_ttl FINAL;
SELECT * FROM table_with_ttl FORMAT PrettyCompact;
```

Second row was deleted from table.

```
┌─────────event_time────┬──UserID─┬─────Comment──┐
│   2020-12-11 12:44:57 │       1 │    username1 │
└───────────────────────┴─────────┴──────────────┘
```

Now remove table `TTL` with the following query:

```
ALTER TABLE table_with_ttl REMOVE TTL;
```

Re-insert the deleted row and force the `TTL` cleanup again with `OPTIMIZE`:

```
INSERT INTO table_with_ttl VALUES (now() - INTERVAL 4 MONTH, 2, 'username2');
OPTIMIZE TABLE table_with_ttl FINAL;
SELECT * FROM table_with_ttl FORMAT PrettyCompact;
```

The `TTL` is no longer there, so the second row is not deleted:

```
┌─────────event_time────┬──UserID─┬─────Comment──┐
│   2020-12-11 12:44:57 │       1 │    username1 │
│   2020-08-11 12:44:57 │       2 │    username2 │
└───────────────────────┴─────────┴──────────────┘
```

**See Also**

- More about the [TTL-expression](/docs/sql-reference/statements/create/table#ttl-expression).
- Modify column [with TTL](/docs/sql-reference/statements/alter/ttl).

- [MODIFY TTL](#modify-ttl)- [REMOVE TTL](#remove-ttl)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/update.md)

```
ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr
```

Manipulates data matching the specified filtering expression. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

Note

The `ALTER TABLE` prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use.

The `filter_expr` must be of type `UInt8`. This query updates values of specified columns to the values of corresponding expressions in rows for which the `filter_expr` takes a non-zero value. Values are cast to the column type using the `CAST` operator. Updating columns that are used in the calculation of the primary or the partition key is not supported.

One query can contain several commands separated by commas.

The synchronicity of the query processing is defined by the [mutations\_sync](/docs/operations/settings/settings#mutations_sync) setting. By default, it is asynchronous.

**See also**

- [Mutations](/docs/sql-reference/statements/alter#mutations)
- [Synchronicity of ALTER Queries](/docs/sql-reference/statements/alter#synchronicity-of-alter-queries)
- [mutations\_sync](/docs/operations/settings/settings#mutations_sync) setting
- [Lightweight `UPDATE`](/docs/sql-reference/statements/update) - Alternative lightweight update using patch parts
- [`APPLY PATCHES`](/docs/sql-reference/statements/alter/apply-patches) - Manually apply patches from lightweight updates

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

- Blog: [Handling Updates and Deletes in ClickHouse](https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse)

- [Related content](#related-content)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/check-grant.md)

The `CHECK GRANT` query is used to check whether the current user/role has been granted a specific privilege.

## Syntax[​](#syntax "Direct link to Syntax")

The basic syntax of the query is as follows:

```
CHECK GRANT privilege[(column_name [,...])] [,...] ON {db.table[*]|db[*].*|*.*|table[*]|*}
```

- `privilege` — Type of privilege.

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

If the user used to be granted the privilege, the response`check_grant` will be `1`. Otherwise, the response `check_grant` will be `0`.

If `table_1.col1` exists and current user is granted by privilege `SELECT`/`SELECT(con)` or role(with privilege), the response is `1`.

```
CHECK GRANT SELECT(col1) ON table_1;
```

```
┌─result─┐
│      1 │
└────────┘
```

If `table_2.col2` doesn't exists, or current user is not granted by privilege `SELECT`/`SELECT(con)` or role(with privilege), the response is `0`.

```
CHECK GRANT SELECT(col2) ON table_2;
```

```
┌─result─┐
│      0 │
└────────┘
```

## Wildcard[​](#wildcard "Direct link to Wildcard")

Specifying privileges you can use asterisk (`*`) instead of a table or a database name. Please check [WILDCARD GRANTS](/docs/sql-reference/statements/grant#wildcard-grants) for wildcard rules.

- [Syntax](#syntax)- [Examples](#examples)- [Wildcard](#wildcard)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/create/index.md)

CREATE queries create (for example) new [databases](/docs/sql-reference/statements/create/database), [tables](/docs/sql-reference/statements/create/table) and [views](/docs/sql-reference/statements/create/view).

Was this page helpful?
direct dictionary layout

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/create/dictionary/layouts/direct.md)

## direct[​](#direct "Direct link to direct")

The dictionary is not stored in memory and directly goes to the source during the processing of a request.

The dictionary key has the [UInt64](/docs/sql-reference/data-types/int-uint) type.

All types of [sources](/docs/sql-reference/statements/create/dictionary/sources#dictionary-sources), except local files, are supported.

Configuration example:

- DDL- Configuration file

```
LAYOUT(DIRECT())
```

```
<layout>
  <direct />
</layout>
```

  

## complex\_key\_direct[​](#complex_key_direct "Direct link to complex_key_direct")

This type of storage is for use with composite [keys](/docs/sql-reference/statements/create/dictionary/attributes#composite-key). Similar to `direct`.

- [direct](#direct)- [complex\_key\_direct](#complex_key_direct)

Was this page helpful?
flat dictionary layout

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/create/dictionary/layouts/flat.md)

With the `flat` layout, the dictionary is completely stored in memory in the form of flat arrays.
The amount of memory used is proportional to the size of the largest key (in space used).

Tip

This layout type provides the best performance among all available methods of storing a dictionary.

The dictionary key has the [UInt64](/docs/sql-reference/data-types/int-uint) type and the value is limited to `max_array_size` (by default — 500,000).
If a larger key is discovered when creating the dictionary, ClickHouse throws an exception and does not create the dictionary.
The initial size of dictionary flat arrays are controlled by the `initial_array_size` setting (by default — 1024).

All types of sources are supported.
When updating the dictionary, data (from a file or from a table) is read in its entirety.

Configuration example:

- DDL- Configuration file

```
LAYOUT(FLAT(INITIAL_ARRAY_SIZE 50000 MAX_ARRAY_SIZE 5000000))
```

```
<layout>
  <flat>
    <initial_array_size>50000</initial_array_size>
    <max_array_size>5000000</max_array_size>
  </flat>
</layout>
```

Was this page helpful?
hashed_array dictionary layout types

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/create/dictionary/layouts/hashed-array.md)

## hashed\_array[​](#hashed_array "Direct link to hashed_array")

The dictionary is completely stored in memory. Each attribute is stored in an array. The key attribute is stored in the form of a hashed table where value is an index in the attributes array. The dictionary can contain any number of elements with any identifiers. In practice, the number of keys can reach tens of millions of items.

The dictionary key has the [UInt64](/docs/sql-reference/data-types/int-uint) type.

All types of sources are supported. When updating, data (from a file or from a table) is read in its entirety.

Configuration example:

- DDL- Configuration file

```
LAYOUT(HASHED_ARRAY([SHARDS 1]))
```

```
<layout>
  <hashed_array>
  </hashed_array>
</layout>
```

  

## complex\_key\_hashed\_array[​](#complex_key_hashed_array "Direct link to complex_key_hashed_array")

This type of storage is for use with composite [keys](/docs/sql-reference/statements/create/dictionary/attributes#composite-key). Similar to [hashed\_array](#hashed_array).

Configuration example:

- DDL- Configuration file

```
LAYOUT(COMPLEX_KEY_HASHED_ARRAY([SHARDS 1]))
```

```
<layout>
  <complex_key_hashed_array />
</layout>
```

- [hashed\_array](#hashed_array)- [complex\_key\_hashed\_array](#complex_key_hashed_array)

Was this page helpful?
Hierarchical dictionaries

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/create/dictionary/layouts/hierarchical.md)

## Hierarchical dictionaries[​](#hierarchical-dictionaries "Direct link to Hierarchical dictionaries")

ClickHouse supports hierarchical dictionaries with a [numeric key](/docs/sql-reference/statements/create/dictionary/attributes#numeric-key).

Look at the following hierarchical structure:

```
0 (Common parent)
│
├── 1 (Russia)
│   │
│   └── 2 (Moscow)
│       │
│       └── 3 (Center)
│
└── 4 (Great Britain)
    │
    └── 5 (London)
```

This hierarchy can be expressed as the following dictionary table.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| region\_id parent\_region region\_name|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 0 Russia|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 2 1 Moscow|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 3 2 Center|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | 4 0 Great Britain|  |  |  | | --- | --- | --- | | 5 4 London | | | | | | | | | | | | | | | | | |

This table contains a column `parent_region` that contains the key of the nearest parent for the element.

ClickHouse supports the hierarchical property for external dictionary attributes. This property allows you to configure the hierarchical dictionary similar to described above.

The [dictGetHierarchy](/docs/sql-reference/functions/ext-dict-functions#dictGetHierarchy) function allows you to get the parent chain of an element.

For our example, the structure of the dictionary can be the following:

- DDL- Configuration file

```
CREATE DICTIONARY regions_dict
(
    region_id UInt64,
    parent_region UInt64 DEFAULT 0 HIERARCHICAL,
    region_name String DEFAULT ''
)
PRIMARY KEY region_id
SOURCE(...)
LAYOUT(HASHED())
LIFETIME(3600);
```

```
<dictionary>
    <structure>
        <id>
            <name>region_id</name>
        </id>

        <attribute>
            <name>parent_region</name>
            <type>UInt64</type>
            <null_value>0</null_value>
            <hierarchical>true</hierarchical>
        </attribute>

        <attribute>
            <name>region_name</name>
            <type>String</type>
            <null_value></null_value>
        </attribute>

    </structure>
</dictionary>
```

- [Hierarchical dictionaries](#hierarchical-dictionaries)

Was this page helpful?