ClickHouse Parameterized Views: Query Parameters in Views and Custom HTTP Endpoints

ClickHouse parameterized views are views whose query contains placeholders that are resolved at call time, letting you reuse one view definition with different filter values. You query them like a table function — SELECT * FROM my_view(param = value) — which makes them the closest thing ClickHouse offers to a stored procedure or prepared statement. They were introduced in ClickHouse 23.1 and work on any table engine.

If you only need a static, reusable SELECT with no runtime inputs, a plain view is enough. Reach for parameterized views when the same logic needs to run with different values — date ranges, tenant IDs, category filters — without you hard-coding a separate view per case.

Syntax: Defining a Parameterized View

A parameter is declared inline in the query using the placeholder syntax {name:DataType}, the same syntax ClickHouse uses for query parameters everywhere else:

CREATE VIEW sales_by_date AS
SELECT
    date,
    sum(revenue) AS total_revenue
FROM sales
WHERE date BETWEEN {start_date:Date} AND {end_date:Date}
GROUP BY date;

Each placeholder names a parameter (start_date, end_date) and gives it an explicit type (Date). The type is enforced at call time, so values are bound safely rather than interpolated as raw strings.

Querying a Parameterized View

You invoke the view as if it were a table function, passing arguments by name:

SELECT *
FROM sales_by_date(start_date = '2024-01-01', end_date = '2024-01-31');

Arguments are matched by name, not position, so order does not matter. Any parameter referenced in the view body must be supplied — there is no implicit default (see the workaround below).

Parameters are not limited to simple scalars. They can be arrays, used in subqueries, or anywhere a literal would normally appear:

CREATE OR REPLACE VIEW v AS
SELECT 1::UInt32 AS x WHERE x IN ({xx:Array(UInt32)});

SELECT * FROM v(xx = [1, 2, 3]);

CREATE VIEW deals_by_category AS
SELECT *
FROM deals
WHERE category_id IN (
    SELECT category_id FROM deal_categories
    WHERE category = {category:String}
);

SELECT * FROM deals_by_category(category = 'hot deals');

Default Parameter Values

ClickHouse has no native default-value syntax for view parameters. The standard workaround is to declare the parameter as Nullable and fall back with coalesce:

CREATE OR REPLACE VIEW recent_orders AS
SELECT *
FROM orders
WHERE status = coalesce({status:Nullable(String)}, 'completed');

Calling recent_orders(status = NULL) (or any value) works, and NULL triggers the 'completed' default. Note you still have to pass the parameter — the Nullable trick supplies a fallback value, not an optional argument. If you omit the parameter entirely, the query errors.

Parameterized Views over Custom HTTP Endpoints

Because view parameters reuse ClickHouse's general query-parameter mechanism, you can drive them through the HTTP interface using the param_ prefix. Given the sales_by_date view above, a self-hosted ClickHouse server accepts:

curl "http://localhost:8123/?param_start_date=2024-01-01&param_end_date=2024-01-31" \
  --data-binary "SELECT * FROM sales_by_date(start_date={start_date:Date}, end_date={end_date:Date}) FORMAT JSON"

The param_<name> URL values substitute the matching {name:Type} placeholders before the query is parsed, so the view receives bound, typed arguments. You can also bind parameters from clickhouse-client:

clickhouse-client \
  --param_start_date='2024-01-01' \
  --param_end_date='2024-01-31' \
  --query="SELECT * FROM sales_by_date(start_date={start_date:Date}, end_date={end_date:Date})"

For a fully managed, no-code endpoint, ClickHouse Cloud lets you publish any saved SQL query (including one over a parameterized view) as a Query API Endpoint; detected {name:Type} placeholders become typed request variables passed via a queryVariables object. For self-hosted setups, the equivalent is wiring a dynamic_query_handler with <query_param_name> bindings — see ClickHouse HTTP handlers and custom endpoints.

Parameterized Views vs. Stored Procedures

ClickHouse has no procedural language (no BEGIN ... END, loops, or OUT parameters). Parameterized views cover the most common reason people reach for stored procedures — a reusable, parameterized read query — but they are read-only and declarative.

For multi-step or write logic, the ClickHouse-native equivalents are application code, scheduled queries, or materialized views for incremental transformation pipelines.

Parameterized Views vs. Materialized Views

These solve different problems and are easy to confuse:

A parameterized view never stores data — it is a saved query template expanded at call time. If you need pre-computed results, use a materialized view instead; the two can be combined (a parameterized view that reads from a materialized view's target table).

Best Practices

1. Always type your parameters precisely. {user_id:UInt64} rejects malformed input and prevents injection; {user_id:String} interpolated by hand does not. Let ClickHouse bind the value.

2. Keep one view per query shape, not per value. The whole point is to collapse orders_2024_jan, orders_2024_feb, … into a single orders_in_range(start, end).

3. Use Nullable + coalesce for optional filters. This is the only reliable way to express "default if not specified" today.

4. Name parameters clearly. Arguments are passed by name; view(from = …, to = …) is far more maintainable than cryptic single letters.

5. Push parameterized views behind HTTP/Query API endpoints rather than letting clients send raw SQL. You get reuse, typing, and a stable contract.

Common Issues

• "Missing columns" / unresolved parameter errors. Every placeholder in the view body must be supplied at call time. There is no implicit default — omitting an argument fails. Use the Nullable workaround if a value is genuinely optional.

• Calling without parentheses. SELECT * FROM sales_by_date (no args) does not work for a parameterized view; it must be invoked as sales_by_date(...).

• Trying to make it a materialized view. Parameterized views are read-time query templates. They cannot be materialized — there is no fixed result to store because the result depends on runtime parameters.

• Column inference with the legacy custom-settings approach. On pre-23.1 servers, people emulated parameters with getSetting() and custom setting prefixes; that path requires explicitly declaring column names and is obsolete. On 23.x/24.x/25.x, use the native {name:Type} syntax.

How Pulse Helps

Parameterized views are a clean abstraction, but they can mask expensive query patterns — a view called thousands of times per minute with full-scan filters, or one whose parameters bypass the primary key and read far more data than expected. Pulse (pulse.support) continuously monitors your ClickHouse clusters, surfaces the slow and high-frequency queries behind your views and HTTP endpoints, and flags when a parameterized query would benefit from a better sort key, a projection, or a materialized view. It is run by ClickHouse and search-infrastructure experts who help teams design query layers — views, endpoints, and the tables underneath them — that stay fast as data and traffic grow.

Frequently Asked Questions

Q: What ClickHouse version added parameterized views?

Native parameterized views were introduced in ClickHouse 23.1. On 23.x and later (including current 24.x/25.x releases) use the {name:Type} placeholder syntax. Older versions could only emulate them with custom settings and getSetting().

Q: How do I pass a default value for a view parameter?

ClickHouse has no native default syntax. Declare the parameter as Nullable and wrap it in coalesce, e.g. coalesce({status:Nullable(String)}, 'completed'). You still must pass the argument (it can be NULL); the fallback supplies the default value, not an optional argument.

Q: Can a parameterized view be a materialized view?

No. Materialized views compute and store results at insert time, which is incompatible with parameters resolved at query time. Use a parameterized view to filter dynamically over a regular table or over a materialized view's target table.

Q: How are parameterized views different from regular views?

A regular view is a fixed saved query you read with SELECT * FROM view. A parameterized view contains placeholders and is invoked like a table function, SELECT * FROM view(p = v), expanding to a different concrete query on each call.

Q: Can I expose a parameterized view as an HTTP API?

Yes. Over the HTTP interface, pass values with the param_ prefix in the URL (e.g. ?param_start_date=2024-01-01). In ClickHouse Cloud you can publish a saved query as a Query API Endpoint; self-hosted, configure a predefined_query_handler. See ClickHouse HTTP handlers and custom endpoints.

Q: Are parameterized views safe against SQL injection?

Yes, when you use typed placeholders. Values bound through {name:Type} are parsed as the declared type rather than concatenated into SQL text, which prevents injection — one of the main reasons to prefer parameterized views over hand-built query strings.