Skip to content
ClickHouse Docs
49.6kGet Started

CREATE QUOTA

View as MarkdownEdit this pageReport an issueSet up docs MCP

Autogenerated from ClickHouse system tables

Creates a quota that can be assigned to a user or a role.

Syntax:

CREATE QUOTA [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
    [IN access_storage_type]
    [KEYED BY {user_name | ip_address | forwarded_ip_address | client_key | client_key,user_name | client_key,ip_address | normalized_query_hash} | NOT KEYED]
    [IPV4_PREFIX_BITS number]
    [IPV6_PREFIX_BITS number]
    [FOR [RANDOMIZED] INTERVAL number {second | minute | hour | day | week | month | quarter | year}
        {MAX { {queries | query_selects | query_inserts | errors | result_rows | result_bytes | read_rows | read_bytes | written_bytes | execution_time | failed_sequential_authentications | queries_per_normalized_hash} = number } [,...] |
         NO LIMITS | TRACKING ONLY} [,...]]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

Keys user_name, ip_address, forwarded_ip_address, client_key, client_key, user_name, client_key, ip_address, and normalized_query_hash correspond to the fields in the system.quotas table.

IPV4_PREFIX_BITS and IPV6_PREFIX_BITS options can only be used when KEYED BY is ip_address or forwarded_ip_address. They correspond to the field in the system.quotas table.

Parameters queries, query_selects, query_inserts, errors, result_rows, result_bytes, read_rows, read_bytes, written_bytes, execution_time, failed_sequential_authentications, queries_per_normalized_hash correspond to the fields in the system.quotas_usage table.

ON CLUSTER clause allows creating quotas on a cluster, see Distributed DDL.

CREATE QUOTA requires the CREATE QUOTA privilege. OR REPLACE throws away an existing quota of the same name, including which roles it applies to, so it additionally requires the DROP QUOTA privilege. The DROP QUOTA privilege is required whether or not the quota already exists, so the statement cannot be used to find out which quotas exist.

Examples

Limit the maximum number of queries for the current user with 123 queries in 15 months constraint:

CREATE QUOTA qA FOR INTERVAL 15 month MAX queries = 123 TO CURRENT_USER;

For the default user limit the maximum execution time with half a second in 30 minutes, and limit the maximum number of queries with 321 and the maximum number of errors with 10 in 5 quarters:

CREATE QUOTA qB FOR INTERVAL 30 minute MAX execution_time = 0.5, FOR INTERVAL 5 quarter MAX queries = 321, errors = 10 TO default;

Create a quota where each distinct normalized query pattern gets its own bucket, limited to 100 executions per hour:

CREATE QUOTA qC KEYED BY normalized_query_hash FOR INTERVAL 1 hour MAX queries = 100 TO default;

Limit any single normalized query pattern to at most 50 executions per hour (regardless of the quota key type):

CREATE QUOTA qD FOR INTERVAL 1 hour MAX queries_per_normalized_hash = 50 TO default;

Further examples, using the xml configuration (not supported in ClickHouse Cloud), can be found in the Quotas guide.

Related Content

Navigation