Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

CREATE HANDLER

View as MarkdownEdit this pageReport an issueSet up docs MCP

Creates a custom HTTP handler defined from SQL, without editing the server configuration file. SQL-defined handlers are an alternative to the configuration-based HTTP interface handlers.

Syntax

CREATE HANDLER [IF NOT EXISTS] name [ON CLUSTER cluster]
[PROTOCOL protocol_name|ANY]
URL [PREFIX|REGEXP] '/path'
[METHODS (GET, POST)]
[TYPE query]
AS [SELECT|INSERT|...] ...

Creates a handler with a specified name. The name is used for managing handlers with SQL queries, for diagnostic messages, and for ordering handlers.

Clauses

  • PROTOCOL — optional. If a protocol name is specified, the handler is active only for the specified composable protocol. Otherwise, the handler is active on all HTTP endpoints: the built-in http/https ports and every HTTP-type composable protocol listener. PROTOCOL ANY explicitly selects the latter default behavior; in ALTER HANDLER it removes a previously set protocol restriction. A protocol literally named any can be referenced with back quotes: PROTOCOL `any` .
  • URL — mandatory. Can be in the form of an exact URL, a URL PREFIX, or a URL REGEXP. For exact URLs and prefixes, ambiguity is checked at creation/alter time and an exception is thrown if there is ambiguity. For regexp, ambiguity cannot be checked. The URL is matched without the ? query string and the # fragment identifier. A URL PREFIX is matched as a base path, on a path-segment boundary — the same semantics as the url_prefix rule of configuration-defined handlers: URL PREFIX '/api/v1' matches /api/v1, /api/v1/ and /api/v1/write, but not /api/v1beta. A trailing / in the prefix is ignored, so '/api/v1/' and '/api/v1' behave the same.
  • METHODS — optional. The list of allowed HTTP methods. By default, it is only GET. The supported methods are GET, POST, PUT and DELETE. The mutating methods POST, PUT and DELETE are allowed to run modifying queries; the safe methods such as GET and HEAD are always executed in readonly mode. Consequently, a handler whose query modifies data (for example INSERT or DDL) must allow at least one mutating method - creating such a handler with only read-only methods (for example the default GET) throws an exception. Queries whose side effects survive the readonly mode are a special case: BACKUP and RESTORE have durable side effects, the session-mutating statements SET, SET ROLE, USE, BEGIN TRANSACTION, COMMIT, ROLLBACK and SET TRANSACTION SNAPSHOT change session or transaction state that persists across requests when session_id is in use, and CREATE TEMPORARY TABLE / CREATE TEMPORARY VIEW create an object living in the session - yet the readonly mode of safe methods blocks none of them. Mutations of an existing temporary table are equally unblocked by the readonly mode, so queries that may target one are treated the same way: an INSERT whose target table is not qualified with a database (an unqualified name may resolve to a session temporary table), a DROP TEMPORARY TABLE, a DROP TABLE / TRUNCATE TABLE of a table not qualified with a database, and an ALTER of a table not qualified with a database (ALTER TEMPORARY TABLE is the same statement). A database-qualified target can never be a temporary table, so such queries are not subject to this rule. HTTP requires safe methods to be side-effect-free (a handler declared for GET is also served for HEAD, where the response body is suppressed and the effect would be invisible). So a handler running such a query must list only mutating methods - creating or altering it to include a safe method throws an exception. Composite statements are looked through: for statement1 PARALLEL WITH statement2 ... and EXECUTE AS <user> <statement> the rules above apply to the wrapped statements, because they are the ones that run (each under a copy of the handler’s context, which keeps the readonly mode). A bare EXECUTE AS <user> makes the whole session run as another user, so it counts as session-mutating itself. In addition, any EXECUTE AS handler - bare or wrapping a statement - must allow at least one mutating method: impersonation needs the IMPERSONATE privilege, which the readonly mode of safe methods denies.
  • TYPE — optional. The only supported type for now is query.
  • AS — the SQL query that will be invoked by this handler. The query can be parameterized. The query is parsed for syntactic correctness during handler creation/alter, but not analyzed - for example, the tables referenced by the query can be missing at the time of the handler creation. The FORMAT and similar clauses belong to the query, not to the whole CREATE/ALTER statement. The query can be put in parentheses for disambiguation. An INSERT query must not contain inline data after the VALUES or FORMAT clause - creating or altering such a handler throws an exception, because the inline payload cannot be preserved in the handler definition; the data is expected to be provided in the HTTP body (or computed by an INSERT ... SELECT). A request to a handler whose query reads the body - an INSERT taking its data from the body, or a query using the _request_body parameter - must declare its length: a non-chunked request without a Content-Length header is answered with 411 Length Required, because the body would otherwise be read until end of stream and a dropped connection would be accepted as a complete request. Every method of such a handler must also be body-carrying (POST, PUT or DELETE) - creating it with a safe method in the METHODS clause (for example the default GET) throws an exception, because a safe method never supplies a request body and the query would silently read an empty one; a declared GET is served for HEAD too, so mixing safe and body-carrying methods would keep those invocations reachable. An INSERT ... SELECT does not read the body (its data comes from the SELECT), so it is not subject to these requirements - unless its SELECT reads from the input table function, which is fed from the request body. A body-reading INSERT must be the handler’s own query: EXECUTE AS and PARALLEL WITH run the statements they wrap without the request body, so wrapping one in them is rejected at creation instead of silently discarding every upload. A body-reading query also must not use the _request_body parameter: there is a single request body, and binding _request_body consumes it before the query reads its input data, so such a handler is rejected at creation instead of silently losing every upload - use either the query’s own body input or _request_body, not both. Handlers that do not read the body have no such requirement; the body of a request to such a handler is ignored and is never appended to the handler’s query. The stored query text is re-parsed by the server with unlimited parser depth and backtracks whenever the handler is reloaded or invoked, so a handler created in a session with raised max_parser_depth / max_parser_backtracks stays loadable and invokable under ordinary session limits.

Priority

Handlers defined in the server configuration have priority over SQL-defined handlers. SQL-defined handlers are matched in the lexicographical order of their names.

Parameters

Query parameters for parameterized queries are supplied, just as with configuration-defined handlers, from:

  • HTTP URL parameters in the query string, using the param_<name> convention (for example ?param_id=42 binds {id:Type});
  • named capture groups in a URL REGEXP (for example URL REGEXP '/users/(?P<id>\d+)' binds {id:Type});
  • form fields of the request body, for a handler whose query declares parameters: an application/x-www-form-urlencoded body (for example curl -d 'param_id=42') and the fields of a multipart/form-data body bind {name:Type} parameters the same way as URL parameters, on the body-carrying methods POST, PUT and DELETE. A parameter present both in the URL and in the body takes its value from the URL. A body parsed as a form is consumed by the handler layer: it is not fed to the query as INSERT data. A handler whose only body use is _request_body gets the raw body instead of form parsing; a handler that declares _request_body alongside other parameters gets both - a copy of the raw, unparsed body is preserved in _request_body (subject to http_max_request_param_data_size) before the body is parsed as a form.

Standard ClickHouse HTTP headers (such as X-ClickHouse-Database, X-ClickHouse-User, X-ClickHouse-Key) are honored as usual when invoking a handler.

The functions currentHandler and currentRequestURL can be used to customize query behavior depending on the invoked handler and request URL.

Access control

CREATE HANDLER, DROP HANDLER and ALTER HANDLER require the CREATE HANDLER, DROP HANDLER and ALTER HANDLER grants respectively.

Reading the system.handlers table requires the SHOW HANDLERS grant. Secrets that may be embedded in a handler’s query are masked there unless the user is additionally allowed to see secrets (see system.handlers).

Invoking a handler does not require any separate grant, but grants are checked as usual during the query invocation, and authentication works in the usual way. To encapsulate access to certain queries, create a VIEW with SQL SECURITY DEFINER and define a handler that selects from that view.

Storage

Handlers are saved in a storage, which can be a local or Keeper storage, similarly to named collections, configured in the query_rules_storage section of the configuration file:

<query_rules_storage>
    <type>local</type> <!-- or zookeeper -->
    <path>/var/lib/clickhouse/handlers/</path>
</query_rules_storage>

With Keeper storage, handlers are kept in sync across all replicas automatically, so an explicit ON CLUSTER clause is redundant and would make every replica try to create the same handler. Enable the ignore_on_cluster_for_replicated_handler_queries setting to make CREATE, ALTER and DROP HANDLER ignore ON CLUSTER when the storage is replicated, mirroring ignore_on_cluster_for_replicated_named_collections_queries.

ALTER HANDLER

ALTER HANDLER name
[PROTOCOL protocol_name|ANY]
[URL [PREFIX|REGEXP] '/path']
[METHODS (GET, POST)]
[TYPE query]
[AS SELECT ...]

Replaces the handler with a new one. The ALTER query can include only a subset of clauses, e.g., it can be used to only change the URL or the query. The unspecified clauses keep their previous values. PROTOCOL ANY removes an existing protocol restriction, making the handler active on all HTTP endpoints again.

DROP HANDLER

DROP HANDLER [IF EXISTS] name

Drops the handler with the specified name.

Introspection

The system.handlers table lists all SQL-defined handlers. The system.query_log table records the handler name and the HTTP request path (without the query string) of each query in the http_handler_name and http_request_url columns.

Example

CREATE HANDLER my_handler URL '/my_handler' AS SELECT version();
$ curl 'http://localhost:8123/my_handler'

A parameterized handler with a regexp URL:

CREATE HANDLER get_user URL REGEXP '/users/(?P<id>\d+)' AS SELECT * FROM users WHERE id = {id:UInt64};
$ curl 'http://localhost:8123/users/42'

CREATE HANDLER is part of the CREATE statement family and is related to ALTER and DROP.

Navigation