CREATE HANDLER

​

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|...] ...

​

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

​

Parameters

• 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.

​

Access control

​

Storage

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

​

ALTER HANDLER

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

​

DROP HANDLER

DROP HANDLER [IF EXISTS] name

​

Introspection

​

Example

CREATE HANDLER my_handler URL '/my_handler' AS SELECT version();

$ curl 'http://localhost:8123/my_handler'

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

$ curl 'http://localhost:8123/users/42'

​

Related statements