CREATE QUOTA

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 | execution_time | written_bytes | written_rows | failed_sequential_authentications | queries_per_normalized_hash | profile_event_name} = 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, execution_time, written_bytes, written_rows, failed_sequential_authentications, queries_per_normalized_hash correspond to the fields in the system.quotas_usage table. In addition to the predefined resource types above, a limit can be set on any profile event (see the system.events table) by its name, for example MAX S3GetObject = 1000. Profile events are accounted once per query, at the moment the query finishes (successfully or with an error): a query that crosses the limit is still allowed to finish, and the subsequent queries are rejected until the interval ends. The current consumption is reported in the profile_events column of 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;

Limit the number of S3GetObject requests and the number of failed queries per day using profile events:

CREATE QUOTA qE FOR INTERVAL 1 day MAX S3GetObject = 100000, FailedQuery = 50 TO default;

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

Blog: Building single page applications with ClickHouse

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

​Related Content

Related Content