ClickHouse Quotas – SQL reference

Quotas are used to track and limit the resource usage for users/roles in ClickHouse. We can set the quotas inside the users.xml file or use SQL-driven workflow. Let us focus on the SQL-driven workflow in this article. We can restrict the following parameters in ClickHouse.

• queries – Upper limit for the queries that can be executed by the user/role
• query_selects – Upper limit for the SELECT queries that can be executed by the user/role
• query_inserts – Upper limit for the INSERT queries that can be executed by the user/role
• errors – Upper limit for the  queries for the user/role that can throw an exception/error
• result_rows – Upper limit for the number of rows in the result set
• read_rows – Upper limit for the rows that can be read for a query (including remote servers)
• execution_time – Upper limit for the query execution time in seconds

Let us look at an example to create a quota to limit the number of queries that can be executed by a user called ‘test_user’ in a day. Ensure that you have access management privilege before we create the quotas.

CREATE QUOTA daily_limit FOR INTERVAL 1 DAY MAX QUERIES 100 TO test_user

The quota is named ‘daily_limit’ and the user can execute up to 100 queries.

To alter the quota, we can use ALTER statement.

ALTER QUOTA daily_limit FOR INTERVAL 2 DAY MAX QUERIES 50

The quota will now allow only 50 queries for a 2-day interval.

To delete the quota, we can use the DROP statement.

DROP QUOTA daily_limit;

References

https://clickhouse.com/docs/en/operations/quotas

https://kb.altinity.com/altinity-kb-setup-and-maintenance/rbac/