# Exoscale Documentation — ClickHouse > Managed ClickHouse on Exoscale: an open-source, column-oriented (OLAP) database for real-time analytics. Quick-starts, overview, how-tos and reference. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/ --- # ClickHouse Managed ClickHouse® is a fully managed, distributed, column-oriented (OLAP) database for real-time analytics and data warehousing. Run sub-second SQL queries on large datasets without operating the infrastructure yourself. > [!NOTE] > Create and manage services from the [Exoscale Portal](https://portal.exoscale.com/), the API, > or the `exo dbaas` commands of the Exoscale CLI 1.100.0 or later. > See the [Quick Start](quick-start/) to get going. - [Quick Start - ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md) - Get started quickly with a guided setup to achieve your first successful use of the service, including the required steps and configuration. - [Overview - ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/index.md) - Terminology and key features at a glance, learn the core concepts, capabilities, and options of this service before you dive deeper. - [How-To - ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/index.md) - Step-by-step guides to configure the service and use specific features, with clear instructions for common tasks and problem solving. - [Service Boundaries - ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/index.md) - Service boundaries: quotas, limits, and the guaranteed service levels (SLA) for this product, including key constraints to plan and operate reliably. - [Reference - ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/index.md) - Reference documentation for API, CLI, and tools, browse comprehensive details, parameters, commands, and integration notes. *ClickHouse® is a registered trademark of ClickHouse, Inc. Exoscale is not affiliated with, sponsored by, or endorsed by ClickHouse, Inc.* --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/ --- # Quick Start - ClickHouse Start using Managed ClickHouse® by creating and configuring a service, connecting to it, and loading sample data. ## Prerequisites - [Exoscale CLI](https://community.exoscale.com/reference/cli/exo/index.md) version 1.100.0 or later, the first release with ClickHouse support in the `exo dbaas` commands. - [ClickHouse client](https://clickhouse.com/docs/concepts/features/interfaces/client) installed, or [Docker](https://docs.docker.com/desktop/) to run it from the official image without installing anything. ## Create a Managed ClickHouse® service You can create a service from the Portal or from the command line. The Portal is the quickest way to get a first service running; the CLI is what you script against. ### From the Exoscale Portal 1. Open the [Exoscale Portal](https://portal.exoscale.com/) and go to **Databases**. 2. Click **Add** and select **ClickHouse**. 3. Choose a [zone](https://community.exoscale.com/platform/dc-zones/index.md) and a [plan](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/index.md#plans). Plans with `Public` access are self-service, from **Startup** to **Premium**; plans with `Request Only` access are enabled per organization on request. 4. Under **IP filter**, restrict access to the addresses that need it. A service is reachable from anywhere by default, so set this before you load any data. 5. Click **Create**. The service reaches the `running` state in a few minutes, and the connection details appear on its overview page. ### From the command line Create a service with the `exo dbaas` commands of the [Exoscale CLI](https://community.exoscale.com/reference/cli/exo/index.md) (version 1.100.0 or later), choosing a [plan](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/index.md#plans) and a [zone](https://community.exoscale.com/platform/dc-zones/index.md), and restricting access to your IP address: ```bash exo dbaas create clickhouse startup-16 my-clickhouse -z ch-gva-2 \ --clickhouse-ip-filter /32 ``` Watch the service until the state is `running`: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --output-template '{{ .State }}' ``` Retrieve the `avnadmin` password: ```bash exo dbaas user reveal-secrets my-clickhouse avnadmin -z ch-gva-2 ``` A service exposes several endpoints on the same host, one per protocol. Show the service to read its connection URI and, under **Components**, each endpoint with its port: ```bash exo dbaas show my-clickhouse -z ch-gva-2 ``` | Component | Protocol | | --- | --- | | `clickhouse` | Native protocol over TLS, used by `clickhouse-client` | | `clickhouse_https` | HTTPS interface, used by most drivers and by `curl` | | `clickhouse_mysql` | MySQL wire protocol | | `clickhouse_arrowflight` | Arrow Flight SQL | > [!NOTE] > `exo dbaas update` and `exo dbaas delete` ask for a confirmation before they run. The > examples in this documentation add `-f` to skip it. Always add it in scripts: without a > terminal to answer the prompt, the command exits without applying the change. ## Configure the service Update the service configuration with `exo dbaas update`. For example, change the allowed IP range: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f \ --clickhouse-ip-filter 203.0.113.0/24 ``` The flag replaces the whole list. To allow several ranges, separate them with commas or repeat the flag. Or set the automatic maintenance window, giving the day and the time together: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f \ --maintenance-dow sunday --maintenance-time 04:00:00 ``` ## Connect to the service {#connect-to-service} [Connect to your new service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md) using the [ClickHouse client](https://clickhouse.com/docs/concepts/features/interfaces/client). > [!TIP] > Discover more tools for connecting to Managed ClickHouse in > [Connect to Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md). ### Connection times out If the client hangs and then fails with a socket timeout, your source address is not allowed by the service `ip-filter`: ```text Code: 209. DB::NetException: Timeout: connect timed out: 203.0.113.10:21699 (SOCKET_TIMEOUT) ``` The filter drops the packets rather than rejecting them, which is why you get a timeout instead of a connection refused. This is the most common failure when connecting from a home or office line, where the public IP address changes over time and no longer matches the one used when the service was created. Check the current filter: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --output-template '{{ .Clickhouse.IPFilter }}' ``` Then set it to your current public IP address: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f \ --clickhouse-ip-filter "$(curl -s -4 https://ifconfig.me)/32" ``` The new filter applies immediately, without restarting the service. ## Load a dataset Managed ClickHouse® can read directly from S3-compatible object storage, so the quickest way to get a real dataset in is to let the service pull it itself: nothing is downloaded to your machine, and the transfer happens between the object storage and your service. This example uses the New York City taxi dataset, roughly two million trips with fares, tips, distances and pickup or dropoff neighborhoods. 1. Create the `trips` table in the `default` database, which was created automatically with your service. ```sql CREATE TABLE trips ( `trip_id` UInt32, `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15), `pickup_date` Date, `pickup_datetime` DateTime, `dropoff_date` Date, `dropoff_datetime` DateTime, `store_and_fwd_flag` UInt8, `rate_code_id` UInt8, `pickup_longitude` Float64, `pickup_latitude` Float64, `dropoff_longitude` Float64, `dropoff_latitude` Float64, `passenger_count` UInt8, `trip_distance` Float64, `fare_amount` Float32, `extra` Float32, `mta_tax` Float32, `tip_amount` Float32, `tolls_amount` Float32, `ehail_fee` Float32, `improvement_surcharge` Float32, `total_amount` Float32, `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4), `trip_type` UInt8, `pickup` FixedString(25), `dropoff` FixedString(25), `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3), `pickup_nyct2010_gid` Int8, `pickup_ctlabel` Float32, `pickup_borocode` Int8, `pickup_ct2010` String, `pickup_boroct2010` String, `pickup_cdeligibil` String, `pickup_ntacode` FixedString(4), `pickup_ntaname` String, `pickup_puma` UInt16, `dropoff_nyct2010_gid` UInt8, `dropoff_ctlabel` Float32, `dropoff_borocode` UInt8, `dropoff_ct2010` String, `dropoff_boroct2010` String, `dropoff_cdeligibil` String, `dropoff_ntacode` FixedString(4), `dropoff_ntaname` String, `dropoff_puma` UInt16 ) ENGINE = MergeTree PARTITION BY toYYYYMM(pickup_date) ORDER BY pickup_datetime; ``` The statement returns one row per node rather than empty output, because databases use the `Replicated` engine. See [Read the output of a DDL statement](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md#read-the-output-of-a-ddl-statement). > [!NOTE] > Keep the column types exactly as they are. The `pickup_nyct2010_gid` column is `Int8` > because the source files genuinely contain negative values in that column, and widening > it to an unsigned type makes the load fail with > `Cannot parse input ... is not like UInt16`. Those negative rows have no matching > neighborhood, which is why looking them up in a taxi zone dictionary returns unknown. 1. Load the data. The `s3` table function reads the two compressed files and streams the rows straight into your table: ```sql INSERT INTO trips SELECT * FROM s3( 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz', 'TabSeparatedWithNames', " `trip_id` UInt32, `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15), `pickup_date` Date, `pickup_datetime` DateTime, `dropoff_date` Date, `dropoff_datetime` DateTime, `store_and_fwd_flag` UInt8, `rate_code_id` UInt8, `pickup_longitude` Float64, `pickup_latitude` Float64, `dropoff_longitude` Float64, `dropoff_latitude` Float64, `passenger_count` UInt8, `trip_distance` Float64, `fare_amount` Float32, `extra` Float32, `mta_tax` Float32, `tip_amount` Float32, `tolls_amount` Float32, `ehail_fee` Float32, `improvement_surcharge` Float32, `total_amount` Float32, `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4), `trip_type` UInt8, `pickup` FixedString(25), `dropoff` FixedString(25), `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3), `pickup_nyct2010_gid` Int8, `pickup_ctlabel` Float32, `pickup_borocode` Int8, `pickup_ct2010` String, `pickup_boroct2010` String, `pickup_cdeligibil` String, `pickup_ntacode` FixedString(4), `pickup_ntaname` String, `pickup_puma` UInt16, `dropoff_nyct2010_gid` UInt8, `dropoff_ctlabel` Float32, `dropoff_borocode` UInt8, `dropoff_ct2010` String, `dropoff_boroct2010` String, `dropoff_cdeligibil` String, `dropoff_ntacode` FixedString(4), `dropoff_ntaname` String, `dropoff_puma` UInt16 ") SETTINGS input_format_try_infer_datetimes = 0; ``` > [!NOTE] > The main service user `avnadmin` already holds the `S3` and `URL` grants, so this runs > without any `GRANT` beforehand. Other users need them explicitly. See > [Run federated queries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md). 1. Check the load: ```sql SELECT count() FROM trips ``` The table holds `1999657` rows. On a `startup-16` service in `ch-gva-2`, that ingest took about six seconds and read 164 MiB. To measure it on your own service, read the statement back from `system.query_log`, as described in [Fetch query statistics](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/fetch-query-statistics/index.md). ## Query data Once the data is loaded, you can run queries against the sample data you imported. - Count the trips: ```sql SELECT count() FROM trips ``` - Read the average tip: ```sql SELECT round(avg(tip_amount), 2) FROM trips ``` - Compare the average total amount paid by number of passengers: ```sql SELECT passenger_count, ceil(avg(total_amount), 2) AS average_total_amount FROM trips GROUP BY passenger_count ORDER BY passenger_count ``` - Group trips by duration in minutes, longest first: ```sql SELECT avg(tip_amount) AS avg_tip, avg(fare_amount) AS avg_fare, avg(passenger_count) AS avg_passenger, count() AS count, truncate(date_diff('second', pickup_datetime, dropoff_datetime) / 60) AS trip_minutes FROM trips WHERE trip_minutes > 0 GROUP BY trip_minutes ORDER BY trip_minutes DESC LIMIT 20 ``` - Find the rides that ended at LaGuardia or JFK: ```sql SELECT pickup_datetime, dropoff_datetime, total_amount, CASE WHEN dropoff_nyct2010_gid = 138 THEN 'LGA' WHEN dropoff_nyct2010_gid = 132 THEN 'JFK' END AS airport_code FROM trips WHERE dropoff_nyct2010_gid IN (132, 138) ORDER BY pickup_datetime LIMIT 20 ``` To map those neighborhood identifiers to borough names without a join against a table, load them into a dictionary. See [Create dictionaries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/index.md). ## Clean up When you are done experimenting, delete the service to stop the billing. Services created with the CLI are protected against termination by default, so lift the protection first: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f --termination-protection=false exo dbaas delete my-clickhouse -z ch-gva-2 -f ``` ## Next steps - [Service architecture](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-architecture/index.md) - [Secure a Managed ClickHouse® service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/secure-service/index.md) - [Manage Managed ClickHouse® users and roles](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/index.md) - [Manage Managed ClickHouse® database and tables](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md) - [Run federated queries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md) - [Create dictionaries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/ --- # Overview - ClickHouse Exoscale **Managed ClickHouse®** delivers a fully managed, distributed deployment of the open-source ClickHouse database. ClickHouse is a column-oriented (OLAP) engine built for real-time analytics: it stores data by column and uses a vectorized query engine to aggregate billions of rows in milliseconds. The service is aimed at teams that need fast analytics on large datasets without operating the infrastructure themselves. ## Terminology Service : A running Managed ClickHouse® cluster (one or more nodes) managed by Exoscale DBaaS. Node : A single ClickHouse server that stores data and answers queries. Shard : A horizontal slice of a table, distributed across nodes to spread storage and query load. Replica : A copy of a shard kept on another node for high availability and read scaling. Plan : A size and redundancy tier (for example `startup-*`, `business-*`, `premium-*`). Table engine : The storage strategy for a table, such as the `MergeTree` family used for analytical tables. Database engine : How a database is backed, including engines that read directly from external systems. Materialized view : A query whose results are written to a target table as new data arrives, used to pre-aggregate data. Tiered storage : Automatic movement of older data from fast local disk to Exoscale Object Storage in the same zone. ## Features Managed cluster lifecycle : Automated provisioning, patching and version upgrades. Columnar analytics engine : Column-oriented storage and a vectorized engine for sub-second queries on large datasets. High availability : Multi-node clusters with sharding and replication; 99.99% SLA on Business and Premium plans. Tiered storage : Keep recent data on fast local disk and move history to S3-compatible Exoscale Object Storage, in the same European zone, to control cost. Integrations : Read from Managed PostgreSQL and query S3-compatible Object Storage directly in SQL with federated queries. Strong security : TLS for data in transit, encryption at rest, and IP filtering to restrict access. Observability : Metrics and logs for your cluster. API first : The full feature set is exposed through the Exoscale API. The Exoscale CLI manages services with the `exo dbaas` commands, and reaches every API operation with `exo x`. ## Concepts in depth ### Core concepts - [ClickHouse® as a columnar database](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/columnar-databases/index.md) - [Online analytical processing](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/olap/index.md) - [Service architecture](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-architecture/index.md) ### Data modeling and performance - [Databases, tables, and views](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/databases-tables-views/index.md) - [Indexing and data processing](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/indexing/index.md) - [ORDER BY and partition keys for MergeTree](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/choose-order-by-key/index.md) - [String data type](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/strings/index.md) ### Querying and integrations - [Querying external data](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/federated-queries/index.md) ### Operations - [Service management](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-management/index.md) - [Disaster recovery](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/disaster-recovery/index.md) - [Tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/choose-order-by-key/ --- # Choose ORDER BY and partition keys for MergeTree tables In Managed ClickHouse®, the `ORDER BY`, `PRIMARY KEY`, and `PARTITION BY` for a `MergeTree` table work together to control how data is sorted, indexed, and grouped on disk. ClickHouse uses this layout to skip irrelevant data during queries and store similar values together for better compression. Use this guidance when you [create MergeTree tables manually](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md) or review table definitions before loading data. For background on how the sparse primary index uses the sort order, see [Indexing and data processing](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/indexing/index.md). > [!WARNING] > You cannot directly change the `ORDER BY` key after table creation. To use a different key, > create a table with the new key and reload the data. ## Choose columns for the ORDER BY key Choose columns based on your most common query filters. A good `ORDER BY` key usually has three to five columns. Columns that appear first in the key benefit most from data skipping because ClickHouse uses a [sparse primary index](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/indexing/index.md#clickhouse-primary-index). Use these guidelines: 1. **Start with common filter columns.** Include columns that appear often in `WHERE` clauses and remove large amounts of data from queries. 1. **Put lower-cardinality filter columns first.** Good candidates include tenant, organization, region, environment, service name, event type, status, or category. 1. **Add higher-cardinality columns later.** Add columns such as site ID, source ID, device ID, or timestamp only when they help with filtering, grouping, compression, or deduplication. 1. **Include a timestamp for time-series data.** Put the timestamp after the main filtering dimensions, for example `ORDER BY (tenant_id, event_type, event_time)`. 1. **Avoid mostly unique IDs as the first column.** Columns such as event IDs, request IDs, trace IDs, or UUIDs usually do not group related rows together. Do not choose an `ORDER BY` key only because a column is unique. In ClickHouse, the key is used for sorting and data skipping, not for enforcing uniqueness. If you often filter on columns that are not in the `ORDER BY` key, consider adding a [data skipping index](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/indexing/index.md#clickhouse-data-skipping-indexes). ## Example: Event analytics table The following table stores event data from multiple tenants: ```sql CREATE TABLE events ( tenant_id String, event_type String, event_time DateTime, event_id UUID, payload String ) ENGINE = MergeTree ORDER BY (tenant_id, event_type, event_time); ``` > [!NOTE] > In Managed ClickHouse®, tables created with `MergeTree` are automatically remapped to > `ReplicatedMergeTree`, and the stored definition also records the default > `SETTINGS index_granularity = 8192`. The remap is unconditional: it also happens on a > single-node service, where there is one replica and therefore no high availability to gain > from it. Write `CREATE TABLE` statements using `MergeTree`; Exoscale applies the required > table engine. For details, see > [Service architecture](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-architecture/index.md). This key works well for queries that filter by tenant, event type, and time range: ```sql SELECT count() FROM events WHERE tenant_id = 'tenant-a' AND event_type = 'purchase' AND event_time >= now() - INTERVAL 7 DAY; ``` ClickHouse can use the sort order to skip data ranges that do not match the tenant, event type, or time range. The following key is less effective for this query pattern because `event_id` is almost always unique: ```sql ORDER BY (event_id, tenant_id, event_time) ``` ## Separate ORDER BY and PRIMARY KEY By default, the `PRIMARY KEY` is the same as `ORDER BY`. You can define a shorter `PRIMARY KEY` to keep the sparse index compact while still sorting data by additional columns using `ORDER BY`. The `PRIMARY KEY` must be a prefix of the `ORDER BY` expression. In other words, the `PRIMARY KEY` columns must appear at the start of `ORDER BY` in the same order. For example: ```sql ORDER BY (service_name, log_level, timestamp, request_id) PRIMARY KEY (service_name, log_level, timestamp) ``` In this example, ClickHouse sorts data by all four columns, but builds the sparse index from the first three columns. Use a separate `PRIMARY KEY` only when later columns help with compression or deduplication but are not common query filters. For `ReplacingMergeTree` tables, put the unique row identifier at the end of `ORDER BY` and exclude it from `PRIMARY KEY` if it is not used as a filter: ```sql ORDER BY (tenant_id, site_id, event_id) PRIMARY KEY (tenant_id, site_id) ``` ## Choose PARTITION BY separately Partitioning complements the `ORDER BY` key. Use `PARTITION BY` for retention, cleanup, and data management. Do not use it as a replacement for `ORDER BY`. For time-series data, start with monthly or weekly partitions: ```sql PARTITION BY toYYYYMM(event_time) ORDER BY (tenant_id, event_type, event_time) ``` Avoid high-cardinality partition keys, such as user IDs, request IDs, trace IDs, or event IDs. They can create too many partitions and make table operations slower. Aim for dozens or hundreds of partitions, not thousands. For small tables or tables without a clear retention pattern, you can omit `PARTITION BY`. ## ORDER BY patterns by use case | Use case | Recommended `ORDER BY` pattern | | --- | --- | | Multi-tenant analytics | `(tenant_id, site_id, event_time)` | | Event analytics | `(tenant_id, event_type, event_time)` | | Application logs | `(service_name, log_level, timestamp)` | | Clickstream or session data | `(session_id, event_type, timestamp)` | | Hierarchical data | `(continent, country, city)` | | Deduplication with `ReplacingMergeTree` | `(..., unique_row_id)`; prefix `PRIMARY KEY` | For clickstream data, `session_id` can lead the key when queries filter by session rather than across all sessions. ## Related pages - [Manage databases and tables](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md) - [Indexing and data processing](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/indexing/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/ --- # Tiered storage in Managed ClickHouse® The tiered storage feature introduces a method of organizing and storing data in two tiers for improved efficiency and cost optimization. The data is automatically moved to an appropriate tier based on your database's disk usage. On top of this default data allocation mechanism, you can control the tier your data is stored in using custom data retention periods. ## Tiered storage architecture The tiered storage in Managed ClickHouse® consists of the following two layers: - Network-attached block storage (cloud provider-managed disks) - the first tier: Fast storage with limited capacity, optimized for fresh and frequently queried data, and relatively costly compared to object storage - Object storage - the second tier: Affordable storage with unlimited capacity, better suited for historical and more rarely queried data, and relatively slower. The first tier uses Exoscale network-attached block storage, and the second tier uses [Exoscale Object Storage](https://community.exoscale.com/product/storage/object-storage/index.md) (S3-compatible), in the same European zone as your service: tiered data never leaves Exoscale infrastructure. Managed ClickHouse's tiered storage supports [local on-disk cache for remote files](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/local-cache-tiered-storage/index.md), which is enabled by default. You can [disable the cache](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/local-cache-tiered-storage/index.md#disable-the-cache) or [drop it](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/local-cache-tiered-storage/index.md#free-up-space) to free up the space it occupies. ## Why use it By [enabling](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) and properly [configuring](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) the tiered storage feature in Managed ClickHouse, you can use storage resources efficiently and, therefore, significantly reduce storage costs of your Managed ClickHouse instance. ## How it works After you [enable](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) the tiered storage feature, Managed ClickHouse by default stores data on network-attached block storage until it reaches 80% of its capacity. After exceeding this [configurable size-based threshold](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md#size-based-threshold), data is stored in object storage. Optionally, you can [configure the time-based threshold](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) for your storage. Based on the time-based threshold, the data moves from network-attached block storage to object storage after a specified time period. ```mermaid sequenceDiagram Application->>+Network-attached block storage: writing data Network-attached block storage->>Object storage: moving data based
on storage policies par Application to Network-attached block storage Application-->>Network-attached block storage: querying data and Application to Object storage Application-->>Object storage: querying data end alt if stored in Object storage Object storage->>Application: reading data else if stored in Network-attached block storage Network-attached block storage->>Application: reading data end ``` > [!NOTE] > Exoscale backs up data that resides on network-attached block storage and in object > storage. ## Typical use case In your Managed ClickHouse service, a significant amount of data sits unused for a long time and is rarely accessed. That data is stored on network-attached block storage, which is relatively costly. You decide to [enable](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) tiered storage to make your data storage more efficient and reduce the costs. For that purpose, you [enable](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) the feature on tables to be optimized. You [configure](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) the time-based threshold to control how your data is stored between the two layers. ## Limitations {#tiered-storage-limitations} - When [enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md), the tiered storage feature cannot be deactivated. > [!TIP] > As a workaround, you can create a table (without enabling tiered > storage on it) and copy the data from the original table (with the > tiered storage feature > [enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md)) to the new table. As > soon as the data is copied to the > new table, you can remove the original table. - With the tiered storage feature [enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md), it's not possible to connect to an external existing object storage or cloud storage bucket. - There can be unused data in object storage, for example before Managed ClickHouse performs a merge of parts or when a backup is performed before your table changes. Such unused data is removed once a day. ## What's next - [Enable tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) - [Configure data retention thresholds for tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) - [Check data volume distribution between different disks](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/check-data-tiered-storage/index.md) - [Transfer data between network-attached block storage and object storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/transfer-data-tiered-storage/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/columnar-databases/ --- # ClickHouse® as a columnar database ClickHouse® is a columnar database that handles data with specific benefits. ## Fast data reading Compared to traditional row-oriented solutions, columnar database management systems store data tables by columns to provide better performance and efficiency in certain applications. As a truly columnar database, ClickHouse® also stores the values of the same column physically next to each other. This further increases the speed of retrieving the values of a column. However, it also makes it slower to retrieve complete rows, as the values of a single row are stored across different physical locations. ## Enhanced query performance Storing the data of each column independently minimizes disk access and improves query performance by reading only the data columns that are relevant to a specific query. ## Data compression and queries aggregation This storage approach also provides better options for data compression, for example, by the ability to better utilize similarities between adjacent data. Columnar databases are also better at aggregating queries involving large data sets. ## Massive and complex read operations Columnar databases such as ClickHouse are therefore best suited for analytical applications that require big data processing or data warehousing, as these usually involve fewer write operations but more - or more complex - read operations that focus on subsets of the stored data. However, applications where queries mainly affect entire rows in the data tables are less efficient in columnar databases. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/databases-tables-views/ --- # Databases, tables, and views in Managed ClickHouse® Databases, tables, and views organize data in Managed ClickHouse®. A database groups related tables and other database objects. Tables store data using table engines that define how data is stored, replicated, and queried. Views and materialized views help query, transform, or precompute data. You can create and manage databases and tables with SQL. ## Databases A database groups related tables, views, and other objects. Databases you create with SQL on Managed ClickHouse always use the `Replicated` engine, which keeps their metadata consistent across the nodes of the service. ## Tables A table stores data in rows and columns. Each table uses a table engine that controls storage behavior, indexing, replication, and data lifecycle features. ## Views and materialized views Views and materialized views help query or transform data. A materialized view stores query results and can improve performance for repeated queries or pre-aggregated data. ## Engines How data is stored and queried depends on the engines you choose: - [Table engines](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-engines/index.md) define how data is stored on disk or read from external sources. - [Database engines](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-database-engines/index.md) control how a database manages its tables and metadata. Engine availability can vary by Managed ClickHouse service version. ## Related pages - [Manage databases and tables](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md) - [Supported table engines](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-engines/index.md) - [Supported database engines](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-database-engines/index.md) - [Materialized views](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/materialized-views/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/disaster-recovery/ --- # Disaster recovery in Managed ClickHouse® Managed ClickHouse® prevents and mitigates emergencies or crises with multiple disaster recovery methods to keep your data safe and sound. Disaster recovery is a process of coping with emergencies or crises using dedicated methods for protecting resources and/or reestablishing their desired status. In the context of data infrastructure, well-established disaster recovery methods are of a particular importance for preventing data loss or corruption. Software failure, loss of a node, or datacenter outage are only a few examples of emergencies when disaster recovery comes in. ## High availability High availability (HA) is an entity's ability to continuously maintain a certain level of operational performance for a desired period of time. HA is typically achieved by redundancy - securing replicas of databases or services to be highly available. To support disaster recovery technologies, a database service needs to stay highly available, for example, by operating on a few nodes holding the same data. With Exoscale, HA for your service is supported in Business and Premium plans. See [Plan comparison](https://www.exoscale.com/pricing/#dbaas) for details. ## Backup and restore ### Service backup Backups of Managed ClickHouse services happen automatically on a daily basis. Backups cover the following: - Access entities (for example, users, roles, passwords, or secrets) stored in ZooKeeper - Database definitions - Table schemas - Table content (`part files`) - Dictionaries You can [restore your service from a selected backup](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/restore-backup/index.md). > [!NOTE] > **Part files** > With the ClickHouse's ReplicatedMergeTree table engine, each INSERT > query results in creating a new file, so-called part, written only once > and not modifiable. > > Using part files allows incremental backups in Managed ClickHouse: > only changed parts are backed up and files already available in the > object storage are left out from the backup. For more information on backups in Exoscale, see [Backups at Exoscale](https://community.exoscale.com/product/dbaas/how-to/backups-restore/index.md). ### Service recovery You can create a copy of your Managed ClickHouse service and [restore the data from a selected service backup](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/restore-backup/index.md). For this purpose, you create a fork from the original service. This spins up a new service that hosts the data recovered from the selected backup. ## Sharding Essentially, sharding is a technique of splitting database rows across multiple database nodes, which usually significantly increases performance. Each shard is in turn replicated across several nodes, so the rows it holds survive the loss of any single node. Replication at the shard level is what provides high availability and helps to achieve disaster recovery. With Managed ClickHouse [Business and Premium plans](https://www.exoscale.com/pricing/#dbaas), each shard is replicated across three nodes running on separate hosts in the zone. The service and the data stay available even if a node is lost. > [!NOTE] > Although sharding with replicated nodes can reduce failures, it still > cannot save a service from the loss of an entire zone. For information on how to work with shards in Managed ClickHouse, see [Enable reading and writing data across shards](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/use-shards-with-distributed-table/index.md). ## Limitations Managed ClickHouse has a few restrictions on the disaster recovery capability. - No backup to another zone - No point in time recovery (PITR) For all the restrictions and limits for Managed ClickHouse, see [Managed ClickHouse limits and limitations](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/index.md). ## Related pages - [Fork and restore from Managed ClickHouse® backups](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/restore-backup/index.md) - [Managed ClickHouse® service architecture](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-architecture/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/federated-queries/ --- # Querying external data in Managed ClickHouse® Discover federated queries and their capabilities in Managed ClickHouse® and how they simplify and speed up migrating into Exoscale from external data sources. Federated queries allow communication between Managed ClickHouse and S3-compatible object storages and web resources. The federated queries feature in Managed ClickHouse enables you to read and pull data from an external object storage that uses the S3 integration engine, or any web resource accessible over HTTP. > [!NOTE] > The federated queries feature in Managed ClickHouse is enabled by > default. ## Why use federated queries There are a few reasons why you might want to use federated queries: - Query remote data from your ClickHouse service. Ingest it into Managed ClickHouse® or only reference external data sources as part of an analytics query. In the context of an increasing footprint of connected data sources, federated queries can help you better understand how your customers use your products. - Simplify and speed up the import of your data into the Managed ClickHouse instance from a legacy data source, avoiding a long and sometimes complex migration path. - Improve the migration of data in Managed ClickHouse, and extend analysis over external data sources with a relatively low effort in comparison to enabling distributed tables and [the remote and remoteSecure functionalities](https://clickhouse.com/docs/reference/functions/table-functions/remote). > [!NOTE] > The `remote()` and `remoteSecure()` features are designed to read from > remote data sources or provide the ability to create a distributed table > across remote data sources but they are not designed to read from an > external S3 storage. ## How it works To run a federated query, the ClickHouse service user connecting to the cluster requires grants to the S3 and/or URL sources. The main service user is granted access to the sources by default, and new users can be allowed to use the sources via the CREATE TEMPORARY TABLE grant, which is required for both sources. For more information on how to enable new users to use the sources, see [Prerequisites](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md#prerequisites). Federated queries read from external S3-compatible object storage utilizing the ClickHouse S3 engine. Once you read from a remote S3-compatible storage, you can select from that storage and insert into a table in the Exoscale local instance, enabling migration of data into Exoscale. For more details on how to run federated queries in Managed ClickHouse, see [Run federated queries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md). ## Supported sources Managed ClickHouse queries S3-compatible object storage, Azure Blob Storage, HTTP resources and external databases such as PostgreSQL and MySQL. You can use them through table functions, or create persistent tables with the matching table engine (`S3`, `URL`, `AzureBlobStorage`, `PostgreSQL`, `MySQL`). Reading from an external source requires the privilege of that source, for example `GRANT S3` or `GRANT URL`, in addition to the `CREATE TEMPORARY TABLE` grant. ## Related pages - [Run federated queries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md) - [Table functions supported in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-functions/index.md) - [Integrating S3 | ClickHouse Docs](https://clickhouse.com/docs/integrations/connectors/data-ingestion/AWS/integrating-s3-with-clickhouse) - [remote, remoteSecure | ClickHouse Docs](https://clickhouse.com/docs/reference/functions/table-functions/remote) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/indexing/ --- # Indexing and data processing in ClickHouse® ClickHouse® processes data differently from other database management systems. ClickHouse uses sparse and skipping indexes and a vector computation engine. ## ClickHouse as a columnar database ClickHouse is a columnar database, which means that ClickHouse can read columns selectively, retrieving only necessary information and omitting columns that are not needed for the request. The smaller the number of the columns you read, the faster and more efficient the performance of the request. If you have to read many or all columns, using a columnar database becomes a less effective approach. Read more about characteristics of columnar databases and their features in [Columnar databases](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/columnar-databases/index.md). ## Reading data in blocks ClickHouse is designed to process substantial chunks of information for a single request. To make this possible, it shifts the focus from reading individual lines into scanning massive blocks of data for a request. This happens by using what is called the vector computation engine, which reads data in blocks that normally consist of thousands of rows selected for a small set of columns. ## ClickHouse primary index ClickHouse reads data in blocks. To find a specific block, ClickHouse uses its primary index, which has a few distinctive features that make it different from the primary indexes of the other systems. Instead of indexing every row, ClickHouse indexes every 10000th row (or, to be precise, every 8192nd row when using default settings). Such an index type is descriptively called *sparse index*, and the batch of rows is sometimes called *granule*. > [!NOTE] > The number of rows per granule is set by the `index_granularity` table setting. In > Managed ClickHouse®, a table created without an explicit value keeps the default, and its > stored definition reports `SETTINGS index_granularity = 8192`. To make sparse indexing possible, ClickHouse sorts items according to the primary key. The items are intentionally sorted physically on the disk to speed up reading and prevent jumping across the disk when processing data. Therefore, by selecting a primary key, you determine how items are sorted physically on the disk. Such an approach helps ClickHouse effectively work on regular hard drives and depend less on network-attached block storage in comparison to other DBMSs. > [!NOTE] > Even though ClickHouse sorts data by primary key, it is possible [to > choose a primary key that is different from the sorting > key](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree#choosing-a-primary-key-that-differs-from-the-sorting-key). Using sparse indexing has significant consequences for capabilities and limitations of ClickHouse. A primary key, as used in ClickHouse, does not ensure uniqueness for a single searched item since only every ten thousandth item is indexed. You need to iterate over thousands of items to find a specific row, which makes this approach inadequate when working with individual rows and suitable for processing millions or trillions of items. > [!NOTE] > **Example** > When analysing error rates based on a server log analysis, you don't > focus on individual lines but look at the overall picture to see trends. > Such requests allow approximate calculations using only a sample of data > to draw conclusions. A good primary index should help limit the number of items you need to read to process a query. > [!TIP] > Learn more about ClickHouse [primary indexes in the official > documentation of > ClickHouse](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree#primary-keys-and-indexes-in-queries). ## ClickHouse data skipping indexes Although skipping indexes are used in ClickHouse as secondary indexes, they work quite differently to secondary indexes used in other DBMSs. Skipping indexes help boost performance by skipping some irrelevant rows in advance, when it can be predicted that these rows do not satisfy query conditions. > [!NOTE] > **Example** > You have numeric column *number of page visits* and run a query to > select all rows where page visits are over 10000. To speed up such a > query, you can add a skipping index to store extremes of the field and > help ClickHouse to skip in advance values that do not satisfy the > request condition. Learn more about [skipping indexes in the official ClickHouse documentation](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-data_skipping-indexes). --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/olap/ --- # Online analytical processing Online analytical processing (OLAP) is an approach to producing real-time reports and insights, usually based on large amounts of source data. A major function of OLAP tools is to provide data-based intelligence to inform and support business decisions. The target data usually includes operational activities that are then analyzed for use by various functions, such as sales, marketing, and finance. From a technical perspective, most traditional, row-oriented database systems are better suited to online transactional processing (OLTP), but are not very efficient for OLAP scenarios. Column-oriented databases are more efficient, as they provide quicker access to subsets of the stored data. However, most database systems aim for a hybrid approach that primarily focuses on optimizing either OLAP or OLTP scenarios, while offering solutions to support the alternative scenario. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-architecture/ --- # Managed ClickHouse® service architecture Managed ClickHouse® is implemented as a multi-master cluster where data replication is managed by ClickHouse, schema and user replication is managed by ZooKeeper, and backup and restore are managed by Exoscale. Understand the technical design of Managed ClickHouse. ## Deployment modes Managed ClickHouse can be deployed either as a single node, a single shard of three nodes, or multiple shards of three nodes each. - With a single shard, all data is available on all nodes and all servers can be used for reads and writes (no main server, leader, or replica). - With multiple shards, the data is split between all shards and the data of each shard is present in all nodes of the shard. Each Managed ClickHouse service is exposed as a single server URL pointing to all servers with connections going randomly to any of the servers. ClickHouse is responsible for replicating the writes between the servers. For synchronizing critical low-volume information between servers, Managed ClickHouse relies on [ZooKeeper](#zookeeper), which runs on each ClickHouse server. ## Coordinating services Each Managed ClickHouse node runs ClickHouse and ZooKeeper. ### ZooKeeper ZooKeeper handles cross-node coordination and synchronization for these replication processes: - Replication of database changes across the cluster: CREATE, DROP, RENAME or ALTER TABLE (by ClickHouse's `Replicated` [database engine](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-architecture/index.md#replicated-database-engine)) - Replication of table data across the cluster (by ClickHouse's `ReplicatedMergeTree` [table engine](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-architecture/index.md#replicated-table-engine)). Data itself is not written to ZooKeeper but transferred directly between ClickHouse servers. - Replication of the storage of Users, Roles, Quotas, Row Policies for the whole cluster (by ClickHouse). > [!NOTE] > Storing entities such as Users, Roles, Quotas, and Row Policies in > ZooKeeper ensures that Role Based Access Control (RBAC) is applied > consistently over the entire cluster. This type of entity storage > is part of the upstream ClickHouse. ZooKeeper handles one process per node and is accessible only from within the cluster. ### Backup and restore [Backup and restore](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/disaster-recovery/index.md#service-backup) for the service is implemented and operated by Exoscale. Backups are coordinated across the cluster so that restores are applied safely and consistently. ## Data architecture Managed ClickHouse enforces - Full schema replication: all databases, tables, users, and grants are the same on all nodes. - Full data replication: table rows are the same on all nodes within a shard. For more information about backups and disaster recovery, see [Service backup](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/disaster-recovery/index.md#service-backup). ## Engines: database and table ClickHouse has engines in two flavors: Table engines and database engines. - The database engine manipulates tables and decides what happens when you list, create, or delete a table. It can also restrict a database to specific table engines or manage replication. - The table engine decides how data is stored on disk or how data is read from outside the disk and exposed as a virtual table. ### `Replicated` database engine The default ClickHouse database engine is the Atomic engine, responsible for creating table metadata on the disk and configuring which table engines are allowed in each database. Managed ClickHouse uses the `Replicated` database engine, which is a variant of Atomic. With this engine variant, queries for creating, updating, or altering tables are replicated to all other servers using [ZooKeeper](#zookeeper). As a result, all servers can have the same table schema, which makes them an actual data cluster and not multiple independent servers that can talk to each other. Because every DDL statement runs as a distributed DDL, it returns one row per node instead of the empty output stock ClickHouse prints. Each row carries the shard, the replica (node) that acknowledged the statement, its status, the number of hosts still to go, and the number of hosts currently active. For an example, see [Read the output of a DDL statement](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md#read-the-output-of-a-ddl-statement). ### `Replicated` table engine The table engine handles `INSERT` and `SELECT` queries. From a wide variety of available table engines, the most common ones belong to the `MergeTree` engine family, which Managed ClickHouse supports. For a list of all the table engines that you can use in Managed ClickHouse, see [Supported table engines in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-engines/index.md). #### `MergeTree` engine With the `MergeTree` engine, at least one new file is created for each INSERT query and each new file is written once and never modified. In the background, new files (called _parts_) are re-read, merged, and rewritten into compact form. Writing data in parts determines the performance profile of ClickHouse. - Batch INSERT queries to avoid creating many small parts. - Batch UPDATE and DELETE queries. Removing or updating a single row requires rewriting an entire part with all rows except the one being removed or updated. - SELECT queries are executed rapidly because all the data found in a part is valid and all files can be cached since they never change. #### `ReplicatedMergeTree` engine Each engine of the `MergeTree` family has a matching `ReplicatedMergeTree` engine, which additionally enables the replication of all writes using [ZooKeeper](#zookeeper). The data itself doesn't travel through ZooKeeper and is actually fetched from one ClickHouse server to another. A shared log of update queries is maintained with ZooKeeper. All nodes add entries to the queue and watch for changes to execute the queries. When a query to create a table using the `MergeTree` engine arrives, Managed ClickHouse automatically rewrites the query to use the `ReplicatedMergeTree` engine so that all tables are replicated and all servers of a shard have the same table data. The rewrite is unconditional. It also applies on a single-node Startup plan, where the service has a single replica and the replicated engine therefore brings no high availability on its own: only a multi-node plan turns the replication into a high-availability cluster. `SHOW CREATE TABLE` reveals the engine definition the server actually stored: ```text ENGINE = ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}') SETTINGS index_granularity = 8192 ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/service-management/ --- # Service management in Managed ClickHouse® Manage the security, configuration, and lifecycle of your Managed ClickHouse® service from the [Exoscale API](https://community.exoscale.com/reference/api/dbaas/index.md). Use this section to secure your service, manage service operations, control user access, upgrade ClickHouse versions, and review service limits and advanced configuration options. ## Security Protect your service by restricting network access and enabling termination protection. See [Secure a Managed ClickHouse® service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/secure-service/index.md) for details. ## Service operations Monitor, resize, fork, or delete your service using platform workflows. See [Manage your Managed ClickHouse service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-manage-cluster/index.md) for links to common service management tasks. ## Users and access Create users and roles and grant privileges to control who can access databases, tables, and administrative functions. See [Manage users and roles](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/index.md). ## Versions and configuration Choose a ClickHouse version when you create a service and upgrade to a newer supported version later. See [Manage versions in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-clickhouse-versions/index.md). ## Related pages - [Secure a Managed ClickHouse® service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/secure-service/index.md) - [Manage your Managed ClickHouse service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-manage-cluster/index.md) - [Manage users and roles](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/index.md) - [Manage versions](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-clickhouse-versions/index.md) - [Limits and limitations](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/strings/ --- # String data type in Managed ClickHouse® Managed ClickHouse® uses ClickHouse® databases, which can store diverse types of data, such as strings, decimals, booleans, or arrays. ## About strings in ClickHouse ClickHouse allows strings of any length. Strings can contain an arbitrary amount of bytes, which are stored and output as-is. The string type replaces the types VARCHAR, BLOB, CLOB, and others from other database management systems (DBMS). When creating tables, numeric parameters for string fields can be set (for example, TEXT(140)) but are ignored. ClickHouse supports the following aliases for strings: LONGTEXT, MEDIUMTEXT, TINYTEXT, TEXT, LONGBLOB, MEDIUMBLOB, TINYBLOB, BLOB, VARCHAR, CHAR. ## String-handling functions - [Functions for working with strings](https://clickhouse.com/docs/reference/functions/regular-functions/string-functions) - [Functions for searching in strings](https://clickhouse.com/docs/reference/functions/regular-functions/string-search-functions) > [!NOTE] > By default, the search is case-sensitive in these functions, but > case-insensitive search variants are also available. - [Functions for searching and replacing in strings](https://clickhouse.com/docs/reference/functions/regular-functions/string-replace-functions) - [Functions for splitting and merging strings and arrays](https://clickhouse.com/docs/reference/functions/regular-functions/splitting-merging-functions) ## String conversions Any plain string type can be cast to a different type using functions in [Type Conversion Functions](https://clickhouse.com/docs/reference/functions/regular-functions/type-conversion-functions). ## Strings and JSON ClickHouse supports a wide range of functions for working with JSON. With specific functions, you can use strings for extracting JSON. Learn more on [JSON functions in ClickHouse](https://clickhouse.com/docs/reference/functions/regular-functions/json-functions). > [!NOTE] > **Examples** > - `visitParamExtractString(params, name)`: Parse the string in double > quotes. > - `JSONExtractString(json[, indices_or_keys]…)`: Parse a JSON and > extract a string. > - `toJSONString`: Convert a value of any data type to its JSON > representation. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/ --- # How-To - ClickHouse - [Check data distribution between storage devices in Managed ClickHouse®'s tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/check-data-tiered-storage/index.md) - See how your table parts are spread between the block storage tier and object storage, and how much space each tier uses. - [Use query cache in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/clickhouse-query-cache/index.md) - Cache the result of repeated SELECT queries to cut latency, and learn the size and entry limits that apply. - [Configure data retention thresholds in Managed ClickHouse®'s tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) - Set TTL rules that move data to object storage as it ages, and control age based deletion. - [Visualize ClickHouse® data with Grafana®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-to-grafana/index.md) - Add your service as a Grafana data source with the official ClickHouse or Altinity plugin, and build dashboards. - [Connect to Managed ClickHouse® with clickhouse-client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md) - Open an interactive SQL session with the ClickHouse client over the native TLS port. - [Connect to Managed ClickHouse® with Go](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-go/index.md) - Connect from Go with clickhouse-go, over the native protocol or HTTPS. - [Connect to Managed ClickHouse® with Java](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-java/index.md) - Connect from Java using the ClickHouse JDBC driver over the HTTPS port. - [Connect to Managed ClickHouse® with the JDBC driver](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-jdbc/index.md) - Build the JDBC connection string for your service and open a connection from any JDBC client. - [Connect to Managed ClickHouse® with Node.js](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-nodejs/index.md) - Connect from Node.js with the official client over HTTPS, including the settings that trip people up. - [Connect to Managed ClickHouse® with PHP](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-php/index.md) - Connect from PHP with the smi2/phpclickhouse client over HTTPS. - [Connect to Managed ClickHouse® with Python](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-python/index.md) - Connect from Python with the native driver or plain HTTPS requests. - [Copy data between Managed ClickHouse® services](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/copy-data-across-instances/index.md) - Copy tables from one service to another with remoteSecure, and use the right port to avoid a failed transfer. - [Create dictionaries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/index.md) - Load reference data into memory as a dictionary to speed up lookups and joins. - [Enable tiered storage in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) - Turn on tiered storage on a table so cold data moves to object storage automatically. - [Fetch query statistics for Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/fetch-query-statistics/index.md) - Read execution times, memory use and errors from system.query_log, and keep them beyond the one day retention. - [Connect to Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md) - Connect to your Managed ClickHouse® service from the ClickHouse client, Python, Go, Java, JDBC, Node.js, PHP or Grafana. - [Manage your Managed ClickHouse® service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-manage-cluster/index.md) - Inspect, resize, schedule maintenance for, fork and delete your Managed ClickHouse® service. - [Manage local cache for remote files in Managed ClickHouse®'s tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/local-cache-tiered-storage/index.md) - Inspect and drop the local cache that keeps frequently read object storage data close to the compute. - [Manage versions in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-clickhouse-versions/index.md) - List the available ClickHouse versions and upgrade your service while it stays available, with only short interruptions. - [Manage Managed ClickHouse® databases and tables](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md) - Create, list and drop databases and tables, and learn which engine the service applies. - [Manage Managed ClickHouse® users and roles](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/index.md) - Create users, grant privileges per database and build roles, including the platform roles that already exist. - [Create materialized views in ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/materialized-views/index.md) - Pre-aggregate data at insert time with a materialized view writing to a destination table. - [Query Managed ClickHouse® databases](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/query-databases/index.md) - Run queries efficiently, and understand concurrency limits and routing on multi-node plans. - [Fork and restore from Managed ClickHouse® backups](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/restore-backup/index.md) - Restore a service by forking it from one of its daily backups. - [Run federated queries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md) - Query object storage, HTTP endpoints and external databases directly in SQL, without moving the data. - [Secure a Managed ClickHouse® service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/secure-service/index.md) - Restrict who can reach your service with an IP filter, and protect it against accidental deletion. - [Use SQL user defined functions in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/sql-user-defined-functions/index.md) - Define reusable SQL functions to simplify repeated expressions in your queries. - [Transfer data between storage devices in Managed ClickHouse®'s tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/transfer-data-tiered-storage/index.md) - Move partitions between block storage and object storage on demand, without waiting for TTL rules. - [Enable reading and writing data across shards in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/use-shards-with-distributed-table/index.md) - Read and write across all shards of a sharded plan with a distributed table. - [Tune the vector similarity index cache in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/vector-similarity-index-cache/index.md) - Size the cache that keeps HNSW vector indexes in memory, and spot evictions that slow vector search. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/check-data-tiered-storage/ --- # Check data distribution between storage devices in Managed ClickHouse®'s tiered storage Monitor how your data is distributed between the two layers of your tiered storage: Network-attached block storage and object storage. If you have the tiered storage feature [enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md), your data in Managed ClickHouse is distributed between two storage devices (tiers). You can check on what storage devices your databases and tables are stored. You can also preview their total sizes as well as part counts, minimum part sizes, median part sizes, and maximum part sizes. ## Prerequisites - Tiered storage [enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) - Command line tool ([ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md)) installed ## Run a data distribution check with the ClickHouse client 1. [Connect to your Managed ClickHouse service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md) using, for example, the ClickHouse client. 1. Run the following query: ```sql SELECT database, table, disk_name, formatReadableSize(sum(data_compressed_bytes)) AS total_size, count(*) AS parts_count, formatReadableSize(min(data_compressed_bytes)) AS min_part_size, formatReadableSize(median(data_compressed_bytes)) AS median_part_size, formatReadableSize(max(data_compressed_bytes)) AS max_part_size FROM system.parts GROUP BY database, table, disk_name ORDER BY database ASC, table ASC, disk_name ASC ``` You can expect output similar to the following, here for the `trips` table from the [quick start](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md) with its oldest partitions moved to object storage: ```text ┌─database─┬─table─┬─disk_name─┬─total_size─┬─parts_count─┬─min_part_size─┬─median_part_size─┬─max_part_size─┐ │ default │ trips │ default │ 39.52 MiB │ 2 │ 18.09 MiB │ 19.76 MiB │ 21.43 MiB │ │ default │ trips │ remote │ 117.53 MiB │ 3 │ 30.29 MiB │ 40.29 MiB │ 46.94 MiB │ └──────────┴───────┴───────────┴────────────┴─────────────┴───────────────┴──────────────────┴───────────────┘ ``` The query returns a table with data distribution details for all databases and tables that belong to your service: the storage device they use, their total sizes as well as parts counts and sizing. ## What's next - [Transfer data between network-attached block storage and object storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/transfer-data-tiered-storage/index.md) - [Configure data retention thresholds for tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) - [About tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/index.md) - [Enable tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/clickhouse-query-cache/ --- # Use query cache in Managed ClickHouse® Managed ClickHouse® provides a query cache mechanism that helps improve query performance by caching query results. ## How it works When the [ClickHouse query cache](https://clickhouse.com/docs/concepts/features/performance/caches/query-cache) is enabled, the result of a `SELECT` query is computed once and stored in the cache. Subsequent executions of the same query are served directly from the cache. > [!IMPORTANT] > By default, the Managed ClickHouse query cache is disabled for all `SELECT` queries. ## Why use it Using query cache in your Managed ClickHouse services can help reduce latency and resource consumption. Key use case for the Managed ClickHouse query cache are the following: - Performance enhancement: Reducing latency and load for frequently executed queries - Analytical workloads: Using complex repetitive queries that involve high data aggregation ## Enable query cache To enable the query cache for a query, set the `use_query_cache` setting for the query to `1`. You can achieve this by appending `SETTINGS use_query_cache = 1` to the end of your query using an SQL client (for example, the [ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md)): ```sql SELECT 1 SETTINGS use_query_cache = 1; ``` ## Configure query cache To configure the query cache settings, use an SQL client (for example, the [ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md)) and append a defined setting to your query, for example: ```sql SELECT 1 SETTINGS use_query_cache = 1, query_cache_min_query_runs = 5000; ``` You can configure the following query cache settings: - `enable_writes_to_query_cache` - `enable_reads_from_query_cache` - `query_cache_max_entries` - `query_cache_min_query_runs` - `query_cache_min_query_duration` - `query_cache_compress_entries` - `query_cache_squash_partial_results` - `query_cache_ttl` - `query_cache_share_between_users` ## Limitation - Cached results are not invalidated or discarded when the underlying data (the result of a `SELECT` query) changes, which might cause returning stale results. - Maximum query cache size: 64 MiB for each GiB of RAM (for example, 256 MiB for a 4-GiB service or 1 GiB for a 16-GiB service) - Maximum number of query cache entries: 1024 ## Related pages - [Querying external data in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/federated-queries/index.md) - [Query Managed ClickHouse® databases](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/query-databases/index.md) - [Fetch query statistics for Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/fetch-query-statistics/index.md) - [Create dictionaries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/ --- # Configure data retention thresholds in Managed ClickHouse®'s tiered storage Control how your data is distributed between storage devices in the tiered storage of a Managed ClickHouse® service. Configure tables so that ClickHouse automatically writes your data to network-attached block storage or object storage as needed. If you have [tiered storage enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) on your Managed ClickHouse service, Exoscale distributes your data between two storage devices (tiers). Data is stored either on network-attached block storage or in object storage, depending on whether and how you configure this behavior. By default, ClickHouse moves data from network-attached block storage to object storage when it reaches 80% of its capacity. You can [adjust that threshold](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md#size-based-threshold) at the service level, or override the behavior per table with a time-based rule. To change this data distribution behavior per table, [configure your table's schema by adding a TTL (time-to-live) clause](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md#time-based-retention-config). Such a configuration allows ignoring the capacity threshold for network-attached block storage and moving the data from it to object storage based on how long the data has been stored there. To enable this time-based data distribution mechanism, you can set up a retention policy (threshold) on a table level by using the TTL clause. For data retention control purposes, the TTL clause uses the following: - Data item of the `Date` or `DateTime` type as a reference point in time - INTERVAL clause as a time period to elapse between the reference point and the data transfer to object storage ## Prerequisites - [Tiered storage enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) - Command line tool installed ([ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md)) ## Configure the size-based threshold {#size-based-threshold} The size-based move is controlled by the `tiered_storage_move_factor` service setting: the fraction of free space on the block storage below which data starts moving to object storage. The default is `0.2`, meaning moves start when less than 20% of the local disk is free, in other words when it is 80% full. Values range from `0` to `1`. Read the current value: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --settings clickhouse ``` Change it, here to start moving data when less than 30% of local disk is free: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f \ --clickhouse-settings '{"tiered_storage_move_factor":0.3}' ``` The new value applies to the storage policy of the service without a restart. ## Configure time-based data retention {#time-based-retention-config} 1. [Connect to your Managed ClickHouse service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md) using, for example, the ClickHouse client. 1. Select a database for operations you intend to perform. ```sql USE DATABASE_NAME ``` ### Add or modify TTL **Add TTL to a new table** Create a table with the `storage_policy` setting set to `tiered` (to [enable tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md)) and TTL (time-to-live) configured to add a time-based data retention threshold on the table. ```sql CREATE TABLE example_table ( SearchDate Date, SearchID UInt64, SearchPhrase String ) ENGINE = MergeTree ORDER BY (SearchDate, SearchID) PARTITION BY toYYYYMM(SearchDate) TTL SearchDate + INTERVAL 1 WEEK TO VOLUME 'remote' SETTINGS storage_policy = 'tiered'; ``` > [!NOTE] > Managed ClickHouse remaps `MergeTree` to its replicated variant, unconditionally and > including on single-node services, so `SHOW CREATE TABLE` does not return the definition you > typed: the engine reads > `ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')` and > `index_granularity = 8192` is added to the `SETTINGS` clause. See > [Select a table engine](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md#select-a-table-engine). **Modify TTL on an existing table** Add or update a TTL definition with the `ALTER TABLE ... MODIFY TTL` statement: ```sql ALTER TABLE database_name.table_name MODIFY TTL ttl_expression; ``` After TTL is configured, ClickHouse moves data older than the specified time period from network-attached block storage to object storage, regardless of available capacity. ## Best practices for tiered storage TTL Follow these recommendations to optimize performance and efficiency when using TTL with tiered storage. ### Optimize part sizes for remote storage Avoid creating many small parts on remote storage, as this can negatively impact performance. When writing data that will be immediately moved to remote storage (such as during backfilling of historical data): - **Use large inserts**: Ensure your data inserts are large enough to create substantial parts on remote storage. - **Temporarily disable TTL moves**: Use the following commands to pause data movement while smaller parts merge together: ```sql -- Stop TTL-based data moves temporarily SYSTEM STOP MOVES; -- Perform your data operations (inserts, merges) -- ... your operations here ... -- Resume TTL-based data moves SYSTEM START MOVES; ``` > [!WARNING] > Remember to run `SYSTEM START MOVES` after your operations to resume normal TTL behavior. > Leaving moves disabled will prevent automatic data tiering. ### Configure efficient data deletion Use the `ttl_only_drop_parts` setting when using TTL for data **deletion**, not just for moving between tiers: ```sql CREATE TABLE example_table_deletion ( SearchDate Date, SearchID UInt64, SearchPhrase String ) ENGINE = MergeTree ORDER BY (SearchDate, SearchID) PARTITION BY toYYYYMM(SearchDate) TTL SearchDate + INTERVAL 1 MONTH DELETE SETTINGS storage_policy = 'tiered', ttl_only_drop_parts = 1; ``` #### How this helps - **Prevents inefficient partial drops**: Instead of repeatedly rewriting parts as individual rows expire, ClickHouse drops entire parts at once. - **Requires matching partition strategy**: Use a `PARTITION BY` expression that aligns with your TTL period so all data in a partition expires simultaneously. - **Improves performance**: Eliminates the overhead of multiple partial rewrites. #### Example of aligned partitioning and TTL ```sql CREATE TABLE example_with_deletion ( SearchDate Date, SearchID UInt64, SearchPhrase String ) ENGINE = MergeTree ORDER BY (SearchDate, SearchID) -- Partition by month, TTL deletes data older than 1 month PARTITION BY toYYYYMM(SearchDate) TTL SearchDate + INTERVAL 1 MONTH DELETE SETTINGS storage_policy = 'tiered', ttl_only_drop_parts = 1; ``` This ensures that when data expires, ClickHouse drops entire monthly partitions rather than removing individual rows from parts. ## What's next - [Check data volume distribution between different disks](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/check-data-tiered-storage/index.md) - [About tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/index.md) - [Enable tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) - [Transfer data between network-attached block storage and object storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/transfer-data-tiered-storage/index.md) - [Manage Data with TTL (Time-to-live)](https://clickhouse.com/docs/concepts/features/operations/delete/ttl) - [Create table statement, TTL documentation](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl) - [MergeTree - column TTL](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree#mergetree-column-ttl) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-to-grafana/ --- # Visualize ClickHouse® data with Grafana® You can visualise your ClickHouse® data using Grafana® and Exoscale can help you connect the two services. ## Prerequisites 1. [Exoscale CLI](https://community.exoscale.com/reference/cli/exo/index.md) version 1.100.0 or later, for the `exo dbaas` commands on this page. 1. Managed ClickHouse® service accessible by HTTPS 1. Managed Grafana service (see how to [get started with Managed Grafana](https://community.exoscale.com/product/dbaas/service-specific/grafana/index.md)) ## Variables You\'ll need a few variables for the setup. | Variable | Description | | ------------------------ | --------------------------------------------- | | `CLICKHOUSE_HOST` | Hostname of your ClickHouse service. | | `CLICKHOUSE_NATIVE_PORT` | Port of the native protocol endpoint, read from the `clickhouse` component. | | `CLICKHOUSE_HTTPS_URI` | HTTPS service URI of your ClickHouse service, in the form `https://CLICKHOUSE_HOST:PORT`, where `PORT` is read from the `clickhouse_https` component. | | `CLICKHOUSE_USER` | Username to access ClickHouse service. | | `CLICKHOUSE_PASSWORD` | Password to access ClickHouse service. | Read the host and the ports from the service **Components**: the native protocol port from the `clickhouse` entry, and the HTTPS port from the `clickhouse_https` entry. Ports are assigned per service, so read them from your own service rather than reusing a value from an example: ```bash exo dbaas show my-clickhouse -z ch-gva-2 ``` Retrieve the password of the `avnadmin` user: ```bash exo dbaas user reveal-secrets my-clickhouse avnadmin -z ch-gva-2 ``` ## Integrate ClickHouse® with Grafana® Managed Grafana ships with two ClickHouse data source plugins: the official **ClickHouse** plugin by Grafana Labs, and the **Altinity plugin for ClickHouse**. Both work with Managed ClickHouse®. ### With the official ClickHouse plugin 1. Log in to your [Managed Grafana®](https://community.exoscale.com/product/dbaas/service-specific/grafana/index.md) service. 1. From the left menu, select **Connections** > **Data sources** > **Add new data source**. 1. Find **ClickHouse** in the list and select it. 1. Set **Server address** to `CLICKHOUSE_HOST` and **Server port** to `CLICKHOUSE_NATIVE_PORT`. 1. Enable **Secure connection**, and set the protocol to **Native**. 1. Set your `CLICKHOUSE_USER` and `CLICKHOUSE_PASSWORD` in the credentials fields. 1. Select **Save & test**. Grafana reports that the data source is working. ### With the Altinity plugin 1. From the left menu, select **Connections** > **Data sources** > **Add new data source**. 1. Find **Altinity plugin for ClickHouse** in the list and select it. 1. Set **URL** to `CLICKHOUSE_HTTPS_URI`. 1. In **Auth** section, enable **Basic auth** and **With Credentials**. 1. In **Basic Auth Details**, set your `CLICKHOUSE_USER` and `CLICKHOUSE_PASSWORD`. 1. Select **Save & test**. Now you can create a dashboard and panels to work with the data from your Managed ClickHouse® service. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/ --- # Connect to Managed ClickHouse® with clickhouse-client It's recommended to connect to a ClickHouse® cluster with the ClickHouse® client. ## Use the ClickHouse® client To use the ClickHouse® client across different operating systems, we recommend utilizing [Docker](https://www.docker.com/). You can get the latest image of the ClickHouse server which contains the most recent ClickHouse client directly from [the dedicated page in Docker hub](https://hub.docker.com/r/clickhouse/clickhouse-server). > [!NOTE] > There are other installation options available for ClickHouse clients > for different operating systems. See them in [ClickHouse > local](https://clickhouse.com/docs/concepts/features/tools-and-utilities/clickhouse-local) > and [Install ClickHouse](https://clickhouse.com/docs/get-started/setup/install) in the > official ClickHouse documentation. ## Connection properties You will need to know the following properties to establish a secure connection with your Managed ClickHouse service: **Host**, **Port**, **User** and **Password**. Read the host and the port of the native protocol endpoint, the one the ClickHouse client uses, from the `clickhouse` entry of the service **Components**. A service also exposes an HTTPS, a MySQL and an Arrow Flight endpoint, each on its own port: ```bash exo dbaas show my-clickhouse -z ch-gva-2 ``` Rather than passing the password on the command line, where it lands in your shell history and in the process list, read it into an environment variable. The `--output-template` flag returns the bare value: ```bash export CH_PASS=$(exo dbaas user reveal-secrets my-clickhouse avnadmin -z ch-gva-2 \ --output-template '{{ .Password }}') ``` Export the host and the native port the same way, so the examples below can reuse them: ```bash export CH_HOST=$(exo dbaas show my-clickhouse -z ch-gva-2 --output-template \ '{{ range .Clickhouse.Components }}{{ if eq .Component "clickhouse" }}{{ .Host }}{{ end }}{{ end }}') export CH_PORT=$(exo dbaas show my-clickhouse -z ch-gva-2 --output-template \ '{{ range .Clickhouse.Components }}{{ if eq .Component "clickhouse" }}{{ .Port }}{{ end }}{{ end }}') ``` ## Command template The command to connect to the service reuses the three variables exported above, so nothing sensitive lands in your shell history. ### With a locally installed client If `clickhouse-client` is installed on your machine, connect to the native TLS port directly: ```bash clickhouse-client \ --user avnadmin \ --password "$CH_PASS" \ --host "$CH_HOST" \ --port "$CH_PORT" \ --secure ``` The `--secure` flag is required: the native endpoint only accepts TLS connections. ### With Docker Docker avoids installing anything and works the same way on every operating system: ```bash docker run -it \ --rm clickhouse/clickhouse-server clickhouse-client \ --user avnadmin \ --password "$CH_PASS" \ --host "$CH_HOST" \ --port "$CH_PORT" \ --secure ``` This example includes the `-it` option (a combination of `--interactive` and `--tty`) to take you inside the container and the `--rm` option to automatically remove the container after exiting. > [!TIP] > Wrap the whole command in a shell function to avoid repeating it. Every example below > then becomes a short `ch …` call: > > ```bash > ch() { docker run --rm --interactive clickhouse/clickhouse-server clickhouse-client --host "$CH_HOST" --port "$CH_PORT" --user avnadmin --password "$CH_PASS" --secure "$@"; } > ``` > > With it, `ch --query "SHOW DATABASES"` runs a single query and `ch < queries.sql` runs a > whole file of statements. The other parameters, such as `--user`, `--password`, `--host`, `--port`, `--secure`, and `--query` are arguments accepted by the ClickHouse client. You can see the full list of command line options in [the ClickHouse client documentation](https://clickhouse.com/docs/interfaces/client#command-line-options). > [!NOTE] > If this first command hangs and then fails with > `Code: 209. DB::NetException: Timeout: connect timed out … (SOCKET_TIMEOUT)`, your source > address is not allowed by the service `ip-filter`. The filter drops the packets rather > than rejecting them, hence a timeout rather than a connection refused. See > [Connection times out](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#connection-times-out). Once you're connected to the server, you can type queries directly within the client, for example, to see the list of existing databases, run ```sql SHOW DATABASES ``` > [!NOTE] > The `clickhouse/clickhouse-server` image tracks the latest ClickHouse release, so its > client is often newer than your service. When it is, every query prints a warning such > as `Unknown settings: 'allow_non_default_profile', skipping`. The client sends a > setting the server does not know, the server ignores it, and the query runs normally. > Pull an image tag matching your service version to silence it. Alternatively, sometimes you might want to run individual queries and be able to access the command prompt outside the docker container. In this case you can set `--interactive` and use `--query` parameter without entering the docker container: ```bash docker run --interactive \ --rm clickhouse/clickhouse-server clickhouse-client \ --user avnadmin \ --password "$CH_PASS" \ --host "$CH_HOST" \ --port "$CH_PORT" \ --secure \ --query="YOUR SQL QUERY GOES HERE" ``` Similar to above example, you can request the list of present databases directly: ```bash docker run --interactive \ --rm clickhouse/clickhouse-server clickhouse-client \ --user avnadmin \ --password "$CH_PASS" \ --host "$CH_HOST" \ --port "$CH_PORT" \ --secure \ --query="SHOW DATABASES" ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-go/ --- # Connect to Managed ClickHouse® with Go To connect to your Managed ClickHouse® service with Go, you can use the native protocol or the HTTPS protocol in specific cases. This article provides you with instructions for both scenarios. ## Prerequisites [Go 1.21 or later](https://go.dev/dl/), with toolchain auto-switching enabled (the current `clickhouse-go` release requires a recent Go toolchain) ## Install the ClickHouse Go module To install the ClickHouse Go module, run the following command: ```bash go get github.com/ClickHouse/clickhouse-go/v2 ``` > [!NOTE] > Check your toolchain with `go version` before installing. The current `clickhouse-go` > release requires the Go version listed in the prerequisites above, and `go get` fails on > an older toolchain. Upgrade Go rather than pinning an old driver release: the 2022-era > `v2.2` tag no longer receives fixes and does not track the protocol changes in > ClickHouse 25.x. ## Connect with the native protocol ### Connect to the service Replace the placeholders in the code with meaningful information on your service connection and run the code. ```go package main import "fmt" import "log" import "crypto/tls" import "github.com/ClickHouse/clickhouse-go/v2" func main() { host := "HOST" native_port := NATIVE_PORT database := "DATABASE_NAME" username := "USERNAME" password := "PASSWORD" tls_config := &tls.Config{} conn, err := clickhouse.Open(&clickhouse.Options{ Addr: []string{fmt.Sprintf("%s:%d", host, native_port)}, Auth: clickhouse.Auth{ Database: database, Username: username, Password: password, }, TLS: tls_config, }) if err != nil { log.Fatal(err) } v, err := conn.ServerVersion() if err != nil { log.Fatal(err) } fmt.Println(v) } ``` ## Connect with HTTPS > [!IMPORTANT] > By default, connections are established over the native protocol. Current versions of > `clickhouse-go` support HTTPS in both the native API and the database/SQL API: enable it either > by modifying the DSN to include the HTTPS protocol or by specifying the protocol in the > connection options. ### Connect to the service Replace the placeholders in the code with meaningful information on your service connection and run the code. ```go package main import "database/sql" import "fmt" import "log" import _ "github.com/ClickHouse/clickhouse-go/v2" func main() { host := "HOST" https_port := HTTPS_PORT username := "USERNAME" password := "PASSWORD" conn, err := sql.Open( "clickhouse", fmt.Sprintf( "https://%s:%d?username=%s&password=%s&secure", host, https_port, username, password)) if err != nil { log.Fatal(err) } rows, err := conn.Query("SELECT version()") if err != nil { log.Fatal(err) } defer rows.Close() for rows.Next() { var version string if err := rows.Scan(&version); err != nil { log.Fatal(err) } fmt.Println(version) } } ``` You have your service connection established and configured. You can proceed to [uploading data into your database](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#load-a-dataset). - For instructions on how to configure connection settings, see [Connection Details](https://clickhouse.com/docs/integrations/language-clients/go/index#connection-details). - For information on how to connect to the Managed ClickHouse service with the ClickHouse client, see [Connect with the ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-java/ --- # Connect to Managed ClickHouse® with Java Learn how to connect to your Managed ClickHouse® service with Java using the ClickHouse JDBC driver and the HTTPS port. ## Prerequisites - [Java 8](https://www.java.com/en/download/) or later - [ClickHouse JDBC driver](https://github.com/ClickHouse/clickhouse-jdbc/tree/master/clickhouse-jdbc) ## Identify connection information To run the code for connecting to your service, first identify values of the following variables: | Variable | Description | | ------------------------- | ------------------------------------------- | | `CLICKHOUSE_HTTPS_HOST` | `Host` for the ClickHouse connection | | `CLICKHOUSE_HTTPS_PORT` | `Port` for the ClickHouse connection | | `CLICKHOUSE_USER` | `User` for the ClickHouse connection | | `CLICKHOUSE_PASSWORD` | `Password` for the ClickHouse connection | Read the host and the port from the `clickhouse_https` entry of the service components. Ports are assigned per service, so read them from your own service rather than reusing a value from an example. This command prints them as `HOST:PORT`: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --output-template \ '{{ range .Clickhouse.Components }}{{ if eq .Component "clickhouse_https" }}{{ .Host }}:{{ .Port }}{{ end }}{{ end }}' ``` Rather than hard-coding the password in your source, read it into an environment variable. The `--output-template` flag returns the bare value: ```bash export CH_PASS=$(exo dbaas user reveal-secrets my-clickhouse avnadmin -z ch-gva-2 \ --output-template '{{ .Password }}') ``` ## Connect to the service 1. Add the ClickHouse JDBC driver to your Maven dependencies. ```xml com.clickhouse clickhouse-jdbc 0.3.2-patch11 all * * ``` 2. Build the JDBC connection string, replacing `CLICKHOUSE_HTTPS_HOST` and `CLICKHOUSE_HTTPS_PORT` with the host and port of the `clickhouse_https` component of your service. You use this string in the code below. ```text jdbc:ch://CLICKHOUSE_HTTPS_HOST:CLICKHOUSE_HTTPS_PORT?ssl=true ``` 3. Replace `CLICKHOUSE_USER` in the code with the user of your service, then run the code. The password is read from the `CH_PASS` environment variable exported above, so it stays out of your source and out of your shell history. ```java import com.clickhouse.jdbc.ClickHouseConnection; import com.clickhouse.jdbc.ClickHouseDataSource; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Main { public static void main(String[] args) throws SQLException { String connString = "jdbc:ch://CLICKHOUSE_HTTPS_HOST:CLICKHOUSE_HTTPS_PORT?ssl=true&sslmode=STRICT"; ClickHouseDataSource database = new ClickHouseDataSource(connString); ClickHouseConnection connection = database.getConnection("CLICKHOUSE_USER", System.getenv("CH_PASS")); Statement statement = connection.createStatement(); ResultSet result_set = statement.executeQuery("SELECT 1 AS one"); while (result_set.next()) { System.out.println(result_set.getInt("one")); } } } ``` Now you have your service connection set up and you can proceed to [uploading data into your database](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#load-a-dataset). For information on how to connect to the Managed ClickHouse service with the ClickHouse client, see [Connect with the ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-jdbc/ --- # Connect to Managed ClickHouse® with the JDBC driver You can use the [ClickHouse JDBC driver](https://github.com/ClickHouse/clickhouse-java/tree/main/clickhouse-jdbc) to connect a JDBC client, an application or a BI tool to your Managed ClickHouse database over HTTPS. You will need Managed ClickHouse® service, accessible by HTTPS. Read the HTTPS host and port from the `clickhouse_https` entry of the service components: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --output-template \ '{{ range .Clickhouse.Components }}{{ if eq .Component "clickhouse_https" }}{{ .Host }}:{{ .Port }}{{ end }}{{ end }}' ``` > [!NOTE] > To reach an *external* database *from* your service instead, see > [Run federated queries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md). | Variable | Description | | ----------------------- | ---------------------------------------------- | | `CLICKHOUSE_HTTPS_HOST` | HTTPS service host of your ClickHouse service. | | `CLICKHOUSE_HTTPS_PORT` | HTTPS service port of your ClickHouse service. | | `CLICKHOUSE_USER` | Username to access ClickHouse service. | | `CLICKHOUSE_PASSWORD` | Password to access ClickHouse service. | ## Connection string Replace `CLICKHOUSE_HTTPS_HOST` and `CLICKHOUSE_HTTPS_PORT` with your connection values: ``` jdbc:ch://CLICKHOUSE_HTTPS_HOST:CLICKHOUSE_HTTPS_PORT?ssl=true&sslmode=STRICT ``` You'll also need to provide user name and password to establish the connection. For example, if you use Java: ``` Connection connection = dataSource.getConnection("CLICKHOUSE_USER", "CLICKHOUSE_PASSWORD"); ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-nodejs/ --- # Connect to Managed ClickHouse® with Node.js Learn how to connect to your Managed ClickHouse® service with Node.js using the official Node.js client for connecting to ClickHouse and the HTTPS port. ## Prerequisites - [Node.js](https://nodejs.org/en/download/) in your environment - [Node.js client for connecting to ClickHouse](https://clickhouse.com/docs/integrations/language-clients/js/index#environment-requirements-nodejs) > [!TIP] > You can install the Node.js client for connecting to ClickHouse using > > ```bash > npm i @clickhouse/client > ``` ## Identify connection information To run the code for connecting to your service, first identify values of the following variables: | Variable | Description | | ----------------------- | ------------- | | `CLICKHOUSE_URL` | `https://HOST:HTTPS_PORT`, where `HOST` and `HTTPS_PORT` are the host and port of the `clickhouse_https` component of your service, not the native port shown in the service URI | | `CLICKHOUSE_USER` | `User` for the ClickHouse connection | | `CLICKHOUSE_PASSWORD` | `Password` for the ClickHouse connection | ## Connect to the service Replace the placeholders in the code with meaningful information on your service connection and run the code. ```javascript import { createClient } from "@clickhouse/client" const client = createClient({ url: "CLICKHOUSE_URL", username: "CLICKHOUSE_USER", password: "CLICKHOUSE_PASSWORD", database: "default", }) const response = await client.query({ query : "SELECT 1", format: "JSONEachRow", clickhouse_settings: { wait_end_of_query: 1, }, }) const data = await response.json() console.log(data) ``` > [!NOTE] > Save the file with the `.mjs` extension, or set `"type": "module"` in your `package.json`, > so that the `import` statement and the top-level `await` are accepted. Now you have your service connection set up and you can proceed to [uploading data into your database](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#load-a-dataset). For information on how to connect to the Managed ClickHouse service with the ClickHouse client, see [Connect with the ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-php/ --- # Connect to Managed ClickHouse® with PHP Learn how to connect to your Managed ClickHouse® service with PHP using the PHP ClickHouse client and the HTTPS port. ## Prerequisites - [PHP 7.4 or later](https://www.php.net/downloads) - `smi2/phpclickhouse` library - [Composer](https://getcomposer.org/) > [!TIP] > You can install `smi2/phpclickhouse` with the following command: > > ```bash > composer require smi2/phpclickhouse > ``` > > or > > ```bash > php composer.phar require smi2/phpclickhouse > ``` ## Identify connection information To run the code for connecting to your service, first identify values of the following variables: | Variable | Description | | ------------ | ------------------------------------------- | | `https` | Required to be set to `true` | | `host` | `Host` for the ClickHouse connection | | `port` | `Port` for the ClickHouse connection | | `username` | `User` for the ClickHouse connection | | `password` | `Password` for the ClickHouse connection | | `database` | `Database Name` in the ClickHouse service | ## Connect to the service Replace the placeholders in the code with meaningful information on your service connection and run the code. ```php true, 'host' => 'HOSTNAME', 'port' => 'HTTPS_PORT', 'username' => 'USERNAME', 'password' => 'PASSWORD' ]); $db->database('DATABASE'); $response = $db->select('SELECT 1'); print_r($response->rows()); ``` Now you have your service connection set up and you can proceed to [uploading data into your database](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#load-a-dataset). ## Related pages - [Connect with the ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md) - [Connect to Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-python/ --- # Connect to Managed ClickHouse® with Python To connect to your Managed ClickHouse® service with Python, you can use either the native protocol or the HTTPS protocol. ## Connect with the native protocol ### Prerequisites - [Python 3.9 or later](https://www.python.org/downloads/) - [ClickHouse Python Driver](https://pypi.org/project/clickhouse-driver/) ### Identify connection information To run the code for connecting to your service, first identify values of the following variables: | Variable | Description | | --------------- | -------------------------------------- | | `USERNAME` | `User` for the ClickHouse connection | | `PASSWORD` | `Password` for the ClickHouse connection | | `HOST` | `Host` for the ClickHouse connection | | `NATIVE_PORT` | `Port` for the ClickHouse connection | | `query` | Query to run, for example `SELECT 1` | Read the host and the native port from the `clickhouse` entry of the service components. Ports are assigned per service, so read them from your own service rather than reusing a value from an example. This command prints them as `HOST:PORT`: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --output-template \ '{{ range .Clickhouse.Components }}{{ if eq .Component "clickhouse" }}{{ .Host }}:{{ .Port }}{{ end }}{{ end }}' ``` ### Connect to the service Replace the placeholders in the code with meaningful information on your service connection and run the code. ```python from clickhouse_driver import Client client = Client(user="USERNAME", password="PASSWORD", host="HOST", port=NATIVE_PORT, secure=True) print(client.execute("SELECT 1")) ``` ## Connect with HTTPS ### Prerequisites - [Python 3.10 or later](https://www.python.org/downloads/) - [Requests HTTP library](https://pypi.org/project/requests/) ### Identify connection information To run the code for connecting to your service, first identify values of the following variables: | Variable | Description | | --------------------------- | ------------------------------------------------------------------ | | `https://HOST:HTTPS_PORT` | `Host` and `Port` for the ClickHouse connection | | `query` | Query to run, for example `SELECT 1` | | `X-ClickHouse-Database` | `Database Name`, for example `system` | | `X-ClickHouse-User` | `User` for the ClickHouse connection | | `X-ClickHouse-Key` | `Password` for the ClickHouse connection | | `X-ClickHouse-Format` | Format for the output from your query, for example `JSONCompact` | The HTTPS endpoint listens on a different port than the native protocol. Read the host and the port from the `clickhouse_https` entry of the service components: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --output-template \ '{{ range .Clickhouse.Components }}{{ if eq .Component "clickhouse_https" }}{{ .Host }}:{{ .Port }}{{ end }}{{ end }}' ``` ### Connect to the service Replace the placeholders in the code with meaningful information on your service connection and run the code. ```python import requests response = requests.post( "https://HOST:HTTPS_PORT", params={"query": "SELECT 1"}, headers={ "X-ClickHouse-Database": "system", "X-ClickHouse-User": "USERNAME", "X-ClickHouse-Key": "PASSWORD", "X-ClickHouse-Format": "JSONCompact", }) print(response.text) ``` Now you have your service connection set up and you can proceed to [uploading data into your database](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#load-a-dataset). For information on how to connect to the Managed ClickHouse service with the ClickHouse client, see [Connect with the ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/copy-data-across-instances/ --- # Copy data between Managed ClickHouse® services You can copy data from one ClickHouse® server to another using the `remoteSecure()` function. > [!IMPORTANT] > `remoteSecure()` speaks the ClickHouse native protocol over TLS, so `PORT` must be the port of > the source service `clickhouse` component, for example `21699`. Using the HTTPS port `21700` > fails with a protocol error. ## Prerequisites Details of the source (remote) server - Hostname - Port of the `clickhouse` component (native protocol over TLS) - Username - Password ## Copy data 1. From your target server, use the `remoteSecure()` function to select data from the source server. ```sql SELECT * FROM remoteSecure('HOSTNAME:PORT', db.remote_engine_table, 'USERNAME', 'PASSWORD') LIMIT 3; ``` 1. Insert the selected data into the target server. ```sql INSERT INTO [db.]table [(c1, c2, c3)] SELECT ... ``` For details on how to configure and use the INSERT query, see [Inserting the Results of SELECT](https://clickhouse.com/docs/reference/statements/insert-into#inserting-the-results-of-select). Your data has been copied from the remote (source) server to the new (target) server. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/ --- # Create dictionaries in Managed ClickHouse® Create dictionaries in Managed ClickHouse® to accelerate queries for better efficiency and performance. ## Dictionaries in Managed ClickHouse A dictionary is a key-attribute mapping useful for low latency lookup queries, when often looking up attributes for a particular key. Dictionary data resides fully in memory, which is why using a dictionary in JOINs is often much faster than using a MergeTree table. Dictionaries can be an efficient replacement for regular tables in your JOIN clauses. Managed ClickHouse supports [backup and restore](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/disaster-recovery/index.md#backup-and-restore) for dictionaries. Also, dictionaries in Managed ClickHouse are automatically replicated to all service nodes. Read more on dictionaries in the [upstream ClickHouse documentation](https://clickhouse.com/docs/reference/statements/create/dictionary). ## Prerequisites - Managed ClickHouse service created - SQL client installed - [Dictionary source](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/index.md#supported-sources) available, with its hostname, port and credentials if the source is an external database ## Limitations - Only TLS connections supported - If no host is specified in a dictionary with a ClickHouse source, the local host is assumed, and the dictionary is filled with data from a query against the local ClickHouse, for example: ```sql -- users table CREATE TABLE IF NOT EXISTS default.users ( id UInt64, username String, email String, country String ) ENGINE = MergeTree() ORDER BY id; CREATE OR REPLACE DICTIONARY default.users_dictionary ( id UInt64, username String, email String, country String ) PRIMARY KEY id SOURCE(CLICKHOUSE(DB 'default' TABLE 'users')) LAYOUT(FLAT()) LIFETIME(100); ``` In Managed ClickHouse, to fill the dictionary the table users are queried with the permissions of the `avnadmin` user even if another user creates the dictionary. In upstream ClickHouse, the same is true except the `default` user is used. - In Managed ClickHouse, the [`dictionaries_lazy_load`](https://clickhouse.com/docs/reference/settings/server-settings/settings/dictionaries#dictionaries_lazy_load) setting is set to `true`, which means that errors with dictionary source parameters may only become apparent when the dictionary is loaded on the first use, rather than when it is created. ### Supported layouts Managed ClickHouse supports the same [layouts that the upstream ClickHouse supports](https://clickhouse.com/docs/reference/statements/create/dictionary/layouts/overview) with two exceptions,`ssd_cache` and `complex_key_ssd_cache`, which are not supported. ### Supported sources - HTTP(s) - Remote ClickHouse - Managed ClickHouse - Remote MySQL® - Managed MySQL - Remote PostgreSQL® - Managed PostgreSQL ## Create a dictionary To create a dictionary with specified structure (attributes), source, layout, and lifetime, use the following syntax: ```sql CREATE [OR REPLACE] DICTIONARY [IF NOT EXISTS] [db.]dictionary_name ( key1 type1 [DEFAULT|EXPRESSION expr1] [IS_OBJECT_ID], key2 type2 [DEFAULT|EXPRESSION expr2], attr1 type2 [DEFAULT|EXPRESSION expr3] [HIERARCHICAL|INJECTIVE], attr2 type2 [DEFAULT|EXPRESSION expr4] [HIERARCHICAL|INJECTIVE] ) PRIMARY KEY key1, key2 SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN])) LAYOUT(LAYOUT_NAME([param_name param_value])) LIFETIME({MIN min_val MAX max_val | max_val}) SETTINGS(setting_name = setting_value, setting_name = setting_value, ...) COMMENT 'Comment' ``` ## Examples ### Speeding up `JOIN`s 1. Create tables in your ClickHouse database: ```sql CREATE TABLE users ( id UInt64, username String, email String, country String ) ENGINE = MergeTree() ORDER BY id; ``` ```sql CREATE TABLE transactions ( id UInt64, user_id UInt64, product_id UInt64, quantity Float64, price Float64 ) ENGINE = MergeTree() ORDER BY id; ``` 1. Create a dictionary for the `users` table: ```sql CREATE DICTIONARY users_dictionary ( id UInt64, username String, email String, country String ) PRIMARY KEY id SOURCE(CLICKHOUSE(DB 'default' TABLE 'users')) LAYOUT(FLAT()) LIFETIME(100); ``` You can do the same using the `QUERY` parameter: ```sql CREATE OR REPLACE DICTIONARY users_dictionary ( id UInt64, username String, email String, country String ) PRIMARY KEY id SOURCE(CLICKHOUSE(QUERY 'SELECT id, username, email, country FROM default.users')) LAYOUT(FLAT()) LIFETIME(100); ``` `JOIN`s are much faster as the data is pre-indexed in memory. ```sql SELECT t.id, u.username, t.product_id, t.quantity, t.price FROM transactions AS t ANY LEFT JOIN users_dictionary AS u ON t.user_id = u.id; ``` ### Mapping the taxi zones of the quick start The [quick start](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#query-data) leaves off with neighborhood identifiers such as `dropoff_nyct2010_gid` in the `trips` table. A dictionary turns them into names without a `JOIN` in every query. 1. Create the mapping table and load a few zones: ```sql CREATE TABLE default.taxi_zones ( gid UInt64, zone_name String ) ENGINE = MergeTree ORDER BY gid; INSERT INTO default.taxi_zones VALUES (132, 'JFK Airport'), (138, 'LaGuardia Airport'), (161, 'Midtown Center'), (237, 'Upper East Side South'); ``` 1. Create the dictionary on top of it: ```sql CREATE DICTIONARY default.taxi_zones_dict ( gid UInt64, zone_name String ) PRIMARY KEY gid SOURCE(CLICKHOUSE(DB 'default' TABLE 'taxi_zones')) LAYOUT(FLAT()) LIFETIME(600); ``` 1. Use `dictGet` directly in queries over `trips`: ```sql SELECT dictGet('default.taxi_zones_dict', 'zone_name', toUInt64(dropoff_nyct2010_gid)) AS zone, count() AS trips FROM default.trips WHERE dropoff_nyct2010_gid IN (132, 138) GROUP BY zone ``` On the quick-start dataset, this returns `27490` trips ending at JFK Airport and `17809` at LaGuardia Airport. ### Caching data from an external database or URL - Create a dictionary for the `pricing` table in your MySQL database using a composite key: ```sql CREATE DICTIONARY product_pricing ( product_id UInt64, region String, price Float64 DEFAULT 0.0 ) PRIMARY KEY product_id, region SOURCE(MYSQL(HOST 'mysql.example.com' PORT 3306 USER 'app' PASSWORD 'PASSWORD' DB 'product_db' TABLE 'pricing')) LAYOUT(COMPLEX_KEY_HASHED()) LIFETIME(MIN 600 MAX 900); ``` This will periodically query MySQL and store the data in memory. - Create a dictionary for the `pricing` table in your PostgreSQL database using the `FLAT` layout: ```sql CREATE DICTIONARY product_pricing ( product_id UInt64, price Float64 DEFAULT 0.0 ) PRIMARY KEY product_id SOURCE(POSTGRESQL(HOST 'pg.example.com' PORT 5432 USER 'app' PASSWORD 'PASSWORD' DB 'product_db' SCHEMA 'public' TABLE 'pricing')) LAYOUT(FLAT()) LIFETIME(0); ``` Because `LIFETIME` is `0`, it has to be manually refreshed as follows: ```sql SYSTEM RELOAD DICTIONARY product_pricing; ``` - Create a dictionary with `HTTP` as a source. The `FORMAT` value matches the layout of the served file, for example `CSVWithNames` for a CSV file whose first line holds the column names: ```sql CREATE DICTIONARY currency_rates ( currency_code String, rate Float64 DEFAULT 1.0 ) PRIMARY KEY currency_code SOURCE(HTTP(URL 'https://example.com/currency_rates.csv' FORMAT 'CSVWithNames')) LAYOUT(COMPLEX_KEY_HASHED()) LIFETIME(100); ``` Look values up with `dictGet`, test whether a key is present with `dictHas`, and supply the value to return for a missing key with `dictGetOrDefault`. Because the layout is a complex-key one, the key is passed as a tuple: ```sql SELECT dictGet('currency_rates', 'rate', tuple('USD')) AS usd_rate, dictHas('currency_rates', tuple('XYZ')) AS xyz_known, dictGetOrDefault('currency_rates', 'rate', tuple('XYZ'), 0.0) AS xyz_rate ``` To load the file once and never refresh it automatically, use `LIFETIME(MIN 0 MAX 0)` and reload the dictionary manually when the file changes. > [!NOTE] > Because > [`dictionaries_lazy_load` is enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/index.md#limitations), > `CREATE DICTIONARY` succeeds even when the URL is unreachable or the format is wrong: the > source is only contacted when the dictionary is first loaded, so the first `dictGet` is > what fails. To surface a broken source right away, force the load with > `SYSTEM RELOAD DICTIONARY currency_rates`. - Create a dictionary for the `users` table in a remote ClickHouse database using the `FLAT` layout: ```sql CREATE DICTIONARY users_dictionary_remote ( id UInt64, username String, email String, country String ) PRIMARY KEY id SOURCE(CLICKHOUSE(HOST 'remote.example.com' PORT 21699 SECURE 1 USER 'avnadmin' PASSWORD 'PASSWORD' DB 'default' TABLE 'users')) LAYOUT(FLAT()) LIFETIME(100); ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/ --- # Enable tiered storage in Managed ClickHouse® Enable the [tiered storage feature](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/index.md) on a table in your Managed ClickHouse® service. Before you enable tiered storage, review the [limitations](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/index.md#tiered-storage-limitations). ## Prerequisites - You have at least one Managed ClickHouse service. - SQL and an SQL client (for example, the [ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md)). - All maintenance updates are applied on your service. Check the pending list with `exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 -q 'maintenance'` and apply it with `exo x start-dbaas-clickhouse-maintenance my-clickhouse -z ch-gva-2`. ## Activate tiered storage on a table You can enable tiered storage both on new tables and on existing ones. 1. [Connect to your Managed ClickHouse service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md) using, for example, the ClickHouse client. 1. To activate the tiered storage feature on a specific table, set `storage_policy` to `tiered` on this table by executing the following SQL statement: ```sql ALTER TABLE database_name.table_name MODIFY SETTING storage_policy = 'tiered' ``` > [!NOTE] > A hyphen is not valid in an unquoted ClickHouse identifier: the parser reads it as a > subtraction and the statement fails with a syntax error. Quote names that contain one > with backticks, as in ``ALTER TABLE `my-db`.`my-table` ``. Tiered storage is activated on your table. From now on, data in this table is distributed between two tiers: Network-attached block storage and object storage. ## What's next - [Configure data retention thresholds for tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) - [Check data volume distribution between different disks](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/check-data-tiered-storage/index.md) - [About tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/index.md) - [Transfer data between network-attached block storage and object storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/transfer-data-tiered-storage/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/fetch-query-statistics/ --- # Fetch query statistics for Managed ClickHouse® Query statistics in ClickHouse are available in the `system.query_log` table, which stores statistics of each executed query, including memory usage, duration, rows and bytes read. In Managed ClickHouse®, `system.query_log` is accessible with standard SQL: ```sql SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes) AS data_read FROM system.query_log WHERE type = 'QueryFinish' ORDER BY event_time DESC LIMIT 10; ``` `type = 'QueryFinish'` matches only queries that completed successfully. Failed queries are logged with `type` set to `ExceptionBeforeStart` or `ExceptionWhileProcessing`, so errors need their own query: ```sql SELECT event_time, type, exception_code, exception, query FROM system.query_log WHERE type IN ('ExceptionBeforeStart', 'ExceptionWhileProcessing') ORDER BY event_time DESC LIMIT 10; ``` To inspect one specific statement, for example an ingest you have just run, filter on the query text and read the memory peak alongside the duration: ```sql SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes) AS data_read, formatReadableSize(memory_usage) AS memory_peak FROM system.query_log WHERE type = 'QueryFinish' AND query LIKE 'INSERT INTO trips%' ORDER BY event_time DESC LIMIT 1; ``` > [!NOTE] > System log tables in Managed ClickHouse keep data for 1 day. To retain query statistics for > longer, persist them with a materialized view. See > [ClickHouse system tables](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-system-tables/index.md) > for the retention details and the materialized view pattern. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/ --- # Connect to Managed ClickHouse® Connect to the Managed ClickHouse® service using various programming languages or tools. Your service exposes four interfaces: the ClickHouse native protocol, HTTPS, the MySQL protocol and Arrow Flight. Retrieve their hosts and ports from the `components` of your service, then pick the guide matching your client: - [Connect with clickhouse-client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md) - [Connect with Python](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-python/index.md) - [Connect with Go](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-go/index.md) - [Connect with Java](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-java/index.md) - [Connect with Node.js](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-nodejs/index.md) - [Connect with PHP](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-php/index.md) - [Connect to external databases via JDBC](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-jdbc/index.md) - [Visualize your data with Grafana](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-to-grafana/index.md) > [!NOTE] > TLS is required on every interface, and your client IP must be allowed in the service > [IP filter](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/secure-service/index.md). ## Related pages - [Supported interfaces and drivers](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-interfaces-drivers/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-manage-cluster/ --- # Manage your Managed ClickHouse® service Day-to-day operations on a Managed ClickHouse® service. ## Prerequisites - [Exoscale CLI](https://community.exoscale.com/reference/cli/exo/index.md) version 1.100.0 or later. Earlier releases don't support ClickHouse in the `exo dbaas` commands. > [!NOTE] > `exo dbaas update` and `exo dbaas delete` ask for a confirmation, which `-f` skips. > Applying maintenance updates on demand has no `exo dbaas` equivalent and uses `exo x`, > the API passthrough. ## Inspect a service Read the service description, including its state, plan, version, IP filter, components and users: ```bash exo dbaas show my-clickhouse -z ch-gva-2 ``` Add `--backups` to list the backups, or `--settings clickhouse` to read the service settings. For scripts, `-O json` returns the same details as JSON. ## Monitor a service Expose your service metrics to your own monitoring stack with an external endpoint. See [Managed ClickHouse® metrics available via Prometheus](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-metrics-prometheus/index.md) and [Managed ClickHouse® metrics available via Datadog](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-metrics-datadog/index.md). Query level statistics are available in the service itself, see [Fetch query statistics](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/fetch-query-statistics/index.md). ## Resize a service Change the plan of a running service to add capacity. See [Scaling a managed service](https://community.exoscale.com/product/dbaas/how-to/scaling/index.md): ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f --plan startup-32 ``` ## Schedule maintenance updates Set the weekly window during which Exoscale applies maintenance, giving the day and the time together: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f \ --maintenance-dow sunday --maintenance-time 04:00:00 ``` Apply the pending maintenance updates immediately, without waiting for the next window. This operation goes through `exo x`, the API passthrough: ```bash exo x start-dbaas-clickhouse-maintenance my-clickhouse -z ch-gva-2 ``` Maintenance replaces the service nodes, so expect a short interruption, longer on single-node plans. Some features expect a fully updated service: for example, all maintenance updates must be applied before you [enable tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md). See [Maintenance window](https://community.exoscale.com/product/dbaas/how-to/maintenance-windows/index.md) for the platform behavior. ## Fork a service Create a new service from a backup of an existing one, see [Fork and restore from Managed ClickHouse® backups](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/restore-backup/index.md). ## Delete a service If the service is protected against termination, the deletion is refused until you disable the protection. Services created with the CLI are protected by default, see [Secure a Managed ClickHouse® service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/secure-service/index.md): ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f --termination-protection=false exo dbaas delete my-clickhouse -z ch-gva-2 -f ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/local-cache-tiered-storage/ --- # Manage local cache for remote files in Managed ClickHouse®'s tiered storage Managed ClickHouse®'s tiered storage features local on-disk cache for remote files for improved query performance and reduced latency. To manage data, Managed ClickHouse's tiered storage uses local storage and remote storage. When remote storage is used, Managed ClickHouse leverages a local on-disk cache to avoid repeated remote fetches. ## How it works When a query requires parts of a table stored in the remote tier, Managed ClickHouse fetches the required parts from the remote storage. The fetched parts are automatically stored in a local cache directory on the disk to avoid repeated downloads for subsequent queries. For future queries, Managed ClickHouse checks the local cache first: - If the data is found in the cache, it is read directly from the local disk. - If the data is not found in the cache, it is fetched from the remote storage and stored in the local cache. Local on-disk cache for remote files is enabled by default for Managed ClickHouse's tiered storage. You can [drop it](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/local-cache-tiered-storage/index.md#free-up-space) to free up the space it occupies, or [disable it per query](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/local-cache-tiered-storage/index.md#disable-the-cache). ## Prerequisites - At least one Managed ClickHouse service using tiered storage - Command line tool ([ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md)) installed ## Disable the cache To disable the local cache for a query, set the `enable_filesystem_cache` setting for the query to `false`. You can achieve this by appending `SETTINGS enable_filesystem_cache = false` to the end of your query using an SQL client (for example, the [ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md)): ```sql SELECT 1 SETTINGS enable_filesystem_cache = false; ``` ## Inspect the cache Read the current size and entry count of the local cache from `system.filesystem_cache`: ```sql SELECT count() AS entries, formatReadableSize(sum(size)) AS cached FROM system.filesystem_cache ``` The cache fills as queries read data from the object storage tier, so an empty result simply means nothing has been read from that tier yet. ## Free up space To drop the local cache and free up the used space, use the following cache command: ```sql SYSTEM DROP FILESYSTEM CACHE 'remote_cache' ``` ## What's next - [About tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/index.md) - [Check data distribution between network-attached block storage and object storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/check-data-tiered-storage/index.md) - [Configure data retention thresholds for tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-clickhouse-versions/ --- # Manage versions in Managed ClickHouse® Managed ClickHouse® supports multiple ClickHouse versions. You can choose a version when you create a service and upgrade to a newer supported version later. ## Supported ClickHouse versions Managed ClickHouse supports two major versions at a time, the two most recent LTS releases offered on Exoscale. The most recent one is the default for new services. List the versions the platform currently offers, along with the default one: ```bash exo dbaas type show clickhouse ``` If you don't specify a version when you create a service, Exoscale uses the default version in effect on the creation date. To pick a version explicitly, pass `--clickhouse-version` with one of the available versions: ```bash exo dbaas create clickhouse startup-16 my-clickhouse -z ch-gva-2 \ --clickhouse-version CLICKHOUSE_VERSION \ --clickhouse-ip-filter /32 ``` > [!NOTE] > See [Quick Start](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md) for the full > creation walkthrough. Exoscale doesn't automatically upgrade an existing service when a newer LTS version becomes available. To upgrade the ClickHouse version of an existing service, see [Upgrade your service](#upgrade-your-service). For supported versions and end-of-life timelines, see the [Managed ClickHouse® version support policy](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/version-lifecycle/index.md). ## Before you upgrade Before upgrading your service, complete the following checks: - Review the upstream ClickHouse release notes. - Test the upgrade in a development or staging environment by forking the service and upgrading the fork first. - Verify that your applications and clients support the target version. - Ensure recent backups are available. - Avoid running long-running queries or heavy ingestion during the upgrade. Downgrading to an earlier ClickHouse version is not supported. ## How upgrades work During an upgrade, the platform replaces service nodes with new nodes running the selected version. - New service nodes are created with the selected ClickHouse version. - The new nodes start alongside the existing nodes. - Data is streamed from the existing nodes to the new nodes. - After the migration completes, the service switches to the upgraded nodes. - The previous nodes are removed. This process avoids modifying the existing nodes directly during the upgrade. During this process: - Queries connected to nodes being replaced can fail and require retries. - New connections are routed to upgraded nodes. ## Service availability during upgrades The service remains available during the upgrade. - New nodes are added and receive data before old nodes are removed, which helps keep the service reachable while the upgrade runs. - Short interruptions can occur. - Latency can increase and throughput can decrease during the upgrade. ## Upgrade your service Upgrade the service to a newer supported ClickHouse version with the [Exoscale CLI](https://community.exoscale.com/reference/cli/exo/index.md). 1. Check the available ClickHouse versions: ```bash exo dbaas type show clickhouse ``` 1. Upgrade the service: ```bash exo dbaas update SERVICE_NAME -z ZONE -f --clickhouse-version CLICKHOUSE_VERSION ``` Parameters: - `SERVICE_NAME`: Name of the service - `ZONE`: Exoscale zone of the service, for example `ch-gva-2` - `CLICKHOUSE_VERSION`: Target ClickHouse version, one of the versions listed in the previous step The service stays reachable during the upgrade. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/ --- # Manage Managed ClickHouse® databases and tables Create and work with databases and tables in Managed ClickHouse®. ## Create a database {#create-a-clickhouse-database} You can create a database using an SQL client such as the [ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). > [!NOTE] > Your Managed ClickHouse service can support up to 400 databases simultaneously. **Limitations** - Only the `avnadmin` user can create databases in SQL. - You can create a database in SQL with the `Replicated` database engine only. To create a database in SQL, run the following SQL command: ```sql CREATE DATABASE DATABASE_NAME ENGINE = Replicated ``` For example: ```sql CREATE DATABASE transactions ENGINE = Replicated; ``` ## Delete a database > [!IMPORTANT] > Deleting a database is irreversible and permanently removes the database along > with all its tables and data. You can delete a database using an SQL client such as the [ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md): **Limitation**: By default, only the `avnadmin` user can delete a database. The `avnadmin` user can grant the permission to delete a database to another user. To delete a database in SQL, run the following SQL command: ```sql DROP DATABASE DATABASE_NAME ``` For example: ```sql DROP DATABASE transactions; ``` ## Create a table Tables can be added with an SQL query using the ClickHouse client. The example below shows a query to add new table `expenses` to `transactions` database. To keep it simple, this example has an unrealistically small amount of columns: ```sql CREATE TABLE transactions.expenses ( Title String, Date DateTime, UserID UInt64, Amount UInt32 ) ENGINE = ReplicatedMergeTree ORDER BY Date; ``` ### Read the output of a DDL statement Databases in Managed ClickHouse use the `Replicated` engine, so every DDL statement runs as a distributed DDL and returns one row per node rather than the empty output stock ClickHouse prints: ```text s_5e98d my-clickhouse-1.exoscale-.aiven.local OK 0 0 ``` The columns are the shard, the replica (node) that acknowledged the statement, its status, the number of hosts still to go, and the number of hosts currently active. A row with `OK` on every node means the statement succeeded everywhere. Expect this output from any DDL statement, `CREATE TABLE` and `CREATE DICTIONARY` included. ### Select a table engine Part of the table definition includes a targeted table engine. See the full [list of supported table engines](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-engines/index.md) in Managed ClickHouse. Managed ClickHouse uses `replicated` variants of table engines to ensure high availability. Even if you select `MergeTree` engine, we will automatically use the replicated variant on our side. The rewrite is unconditional and also applies on single-node Startup plans, where there is one replica and therefore no high availability from it. Run `SHOW CREATE TABLE` to see what the server actually stored: ```text ENGINE = ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}') ORDER BY Date SETTINGS index_granularity = 8192 ``` > [!NOTE] > A non-replicated table, such as `system.query_log`, can be > [queried using `clusterAllReplicas`](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/query-databases/index.md#query-a-non-replicated-table). ## Delete a table You can remove a table of any size if you have the `DROP` permission since parameters `max_table_size_to_drop` and `max_partition_size_to_drop` are disabled for Exoscale services. Consider [granting](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/index.md) only necessary permissions to your database users. Run the following SQL command to remove your table: ```sql DROP TABLE NAME_OF_YOUR_DATABASE.NAME_OF_YOUR_TABLE; ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/ --- # Manage Managed ClickHouse® users and roles Create Managed ClickHouse® users and roles and grant them specific privileges to efficiently control or restrict access to your service. ## Manage users ### Add a user Create a user account for your service in one of two ways. To create a user using a hash and salt, run: ```sql CREATE USER username IDENTIFIED WITH sha256_hash BY 'hash' SALT 'salt'; ``` To create a user using a password directly, run: ```sql CREATE USER username IDENTIFIED BY 'password'; ``` Users' password digests are stored securely in ZooKeeper, out of reach of service users. The password digests and salts are stored in backup files so they can be recovered. ### Configure user settings Configure user settings to control resource usage and query behavior. You can set limits at the user level (applying to all queries by that user) or configure per-query constraints. #### Set user resource limits Configure user settings, for example, to restrict resource use per user. This can help you: - Prevent single users from monopolizing CPU threads and starving other users. - Ensure fair distribution of system resources among all active users. - Maintain system stability by preventing service overload. 1. Create a user named `limited_user`. ```sql CREATE USER limited_user IDENTIFIED WITH SHA256_PASSWORD BY 'PASSWORD'; ``` 1. Set resource limit `max_threads = 1` for this user. ```sql ALTER USER limited_user SETTINGS max_threads = 1; ``` 1. Grant read-only access to the `default` database to `limited_user`. ```sql GRANT SELECT ON default.* TO limited_user; ``` > [!NOTE] > On Managed ClickHouse®, data privileges cannot be granted globally with `ON *.*`. Grant > them per database instead, repeating the statement for each database the user needs. > Access management privileges such as `CREATE USER` are global and use `ON *.*`. 1. Log in as `limited_user` to test the configuration. ```bash clickhouse-client --host "$CH_HOST" --port "$CH_PORT" --user limited_user --password PASSWORD --secure ``` Replace `PASSWORD` with the password you set above. `$CH_HOST` and `$CH_PORT` are the variables exported in [Connect to Managed ClickHouse® with clickhouse-client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). 1. Verify the resource limit setting. ```sql SHOW SETTINGS LIKE 'max_threads'; ``` This displays the `max_threads` setting with a value of `1` for the `limited_user`. #### Set per-query resource limits Constrain resources on a per-query basis for specific users by setting query-level limits in their user profile. 1. Create a user with per-query memory and execution time limits. ```sql CREATE USER query_limited_user IDENTIFIED WITH SHA256_PASSWORD BY 'PASSWORD' SETTINGS max_memory_usage = 1000000000, max_execution_time = 30; ``` 1. Grant appropriate permissions. ```sql GRANT SELECT ON default.* TO query_limited_user; ``` 1. Test the per-query limits by logging in and running a query. ```bash clickhouse-client --host "$CH_HOST" --port "$CH_PORT" --user query_limited_user --password PASSWORD --secure ``` 1. Verify the query-level settings. ```sql SHOW SETTINGS LIKE '%max_%'; ``` **Common per-query settings:** - `max_memory_usage`: Maximum memory per query (in bytes) - `max_execution_time`: Maximum query execution time (in seconds) - `max_rows_to_read`: Maximum rows a query can examine - `max_result_rows`: Maximum rows in query result ## Manage roles and privileges Apart from the platform roles that carry the privileges of the main service user (`aiven_admin_role` and `aiven_readonly_role`), Managed ClickHouse has no predefined roles. All roles you create are custom roles you design for your purposes by granting specific privileges to particular roles. For example: you can create a role that allows only reading a single database, table, or column; or you can create another role that allows only inserting data, not deleting it. ClickHouse® supports a **Role Based Access Control** model and allows you to configure access privileges by using SQL statements. You can rely on [the command-line interface](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). The upstream ClickHouse documentation includes [detailed documentation for access rights](https://clickhouse.com/docs/concepts/features/security/access-rights). ### Create a role To create a role named **auditor**, run the following command: ```sql CREATE ROLE auditor; ``` Find more information on creating roles in the [upstream ClickHouse documentation](https://clickhouse.com/docs/reference/statements/create/role). ### Grant privileges You can grant privileges both to specific roles and to individual users. The grants can be also granular, targeting specific databases, tables, columns, or rows. > [!IMPORTANT] > You cannot grant additional privileges to the main service user. Exoscale may grant privileges > to the main service user during maintenance updates when adding new features for the service. > [!NOTE] > A user you create does not start with the privileges the platform grants to the main service > user (`avnadmin`). By default, `avnadmin` can already read from the `S3` and `URL` sources, and > it is the only user that can create a database in SQL. Grant the external-source privileges > explicitly to your own users, for example > `GRANT CREATE TEMPORARY TABLE, S3, URL ON *.* TO username`. See > [Run federated queries](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md). As an example, the following request grants the `auditor` role privileges to select data from the `transactions` database: ```sql GRANT SELECT ON transactions.* TO auditor; ``` You can limit the grant to a specified table: ```sql GRANT SELECT ON transactions.expenses TO auditor; ``` Or to particular columns of a table: ```sql GRANT SELECT(Date, Title, Amount) ON transactions.expenses TO auditor ``` To grant several roles to several users at once, first make sure they all exist: ```sql CREATE ROLE external; CREATE USER mary_anderson IDENTIFIED WITH sha256_password BY 'Password1?'; CREATE USER james_miller IDENTIFIED WITH sha256_password BY 'Password2?'; ``` Then run: ```sql GRANT auditor, external TO mary_anderson, james_miller; ``` To allow a role, such as `auditor`, to create new users: ```sql GRANT CREATE USER ON *.* TO auditor ``` There are a variety of privileges that you can grant. Find the full list in the [upstream ClickHouse documentation](https://clickhouse.com/docs/reference/statements/grant#privileges). > [!NOTE] > You can grant privileges to a table that does not yet exist. > [!NOTE] > Users can grant privileges according to their privileges. If the user > lacks the required privileges for a requested operation, they receive a > `Not enough privileges` exception. > [!WARNING] > Privileges are not revoked when a table or database is removed. They > continue to be active for any new table or database that is created with > the same name. Find all details on how the GRANT statement is supported in ClickHouse in the [upstream ClickHouse documentation](https://clickhouse.com/docs/reference/statements/grant). ### Set roles A single user can be assigned different roles, either individually or simultaneously. ```sql SET ROLE auditor; ``` You can also specify a role to be activated by default when the user logs in: ```sql SET DEFAULT ROLE auditor, external TO mary_anderson, james_miller; ``` ### Delete a role If you no longer need a role, you can remove it: ```sql DROP ROLE auditor; ``` ### Revoke privileges Remove all or specific privileges from users or roles: ```sql REVOKE SELECT ON transactions.expenses FROM mary_anderson; ``` Revoke all privileges to a table or database simultaneously: ```sql REVOKE ALL PRIVILEGES ON transactions.expenses FROM external; ``` See the ClickHouse documentation [for more information on revoking privileges](https://clickhouse.com/docs/reference/statements/revoke). ### Check privileges Run the following commands to see all available grants, users, and roles: ```sql SHOW GRANTS; ``` ```sql SHOW USERS; ``` ```sql SHOW ROLES; ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/materialized-views/ --- # Create materialized views in ClickHouse® Use materialized views to transform data automatically as it arrives. When a new block of data is inserted into a source table, the query defined by the materialized view runs on that block and writes the result to a destination table. This is the standard ClickHouse pattern for pre-aggregating data, maintaining rollups, or persisting transformed copies of raw events. The destination of the data (for example, a MergeTree family table) is defined by the `TO` clause. This process is illustrated in the following diagram: ```mermaid flowchart LR a[Inserts into
source table] b{{Source table}} c[Materialized view] d{{MergeTree
family table}} a --> b --> c --> d ``` ## Create a materialized view This example builds a daily rollup of the `default.trips` table loaded in the [quick start](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#load-a-dataset). Any MergeTree table with a date column works the same way. 1. Create the destination table that stores the transformed data: ```sql CREATE TABLE default.daily_totals ( pickup_date Date, total UInt64 ) ENGINE = SummingMergeTree ORDER BY pickup_date ``` 1. Create the materialized view on top of the source table: ```sql CREATE MATERIALIZED VIEW default.my_view TO default.daily_totals AS SELECT pickup_date, count() AS total FROM default.trips GROUP BY pickup_date ``` From this point on, every insert into `default.trips` automatically updates `default.daily_totals`. The view only processes new blocks of data, so the two million rows already in the table are not aggregated yet. Backfill them once with the same query: ```sql INSERT INTO default.daily_totals SELECT pickup_date, count() AS total FROM default.trips GROUP BY pickup_date ``` > [!NOTE] > The two `CREATE` statements are DDL, and databases in Managed ClickHouse use the `Replicated` > engine, so each one returns one row per node instead of the empty output stock ClickHouse > prints. See > [reading the output of a DDL statement](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md#read-the-output-of-a-ddl-statement). > [!IMPORTANT] > A materialized view aggregates within each inserted block, not across the whole destination > table, so it writes one partial row per key per insert. That is why the destination uses > `SummingMergeTree` rather than plain `MergeTree`: the engine collapses rows sharing the same > `ORDER BY` key. Collapsing happens during background merges, at an unpredictable time, so > always re-aggregate at read time rather than trusting a single row to be final: > > ```sql > SELECT pickup_date, sum(total) AS total > FROM default.daily_totals > GROUP BY pickup_date > ``` > > With a plain `MergeTree` destination the rows would never collapse at all, and a query > reading `total` directly would silently return one partial value per insert. > [!NOTE] > Tables declared with a `MergeTree` family engine are automatically replicated by the service, > so the data written by the materialized view is exchanged and replicated across all the nodes. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/query-databases/ --- # Query Managed ClickHouse® databases Run a query against a Managed ClickHouse® database using a tool of your choice. To ensure data security, stability, and its proper replication, we equip our Managed ClickHouse® service with specific features, some of them missing from the standard ClickHouse offer. Managed ClickHouse takes care of running queries in the distributed mode over the entire cluster. In the standard ClickHouse, the queries `CREATE`, `ALTER`, `RENAME` and `DROP` only affect the server where they are run. In contrast, we ensure the proper distribution across all cluster machines behind the scenes. You don't need to use `ON CLUSTER` for data definition queries against a `Replicated` database. > [!IMPORTANT] > There are limitations on the number of concurrent queries and the number of concurrent > connections in Managed ClickHouse: > > - `max_concurrent_queries` scales with the plan size, starting at `147` on the smallest plans. > - `max_connections` is `4000` per node. > > See > [Managed ClickHouse® limits and limitations](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/index.md) > for details. For querying your ClickHouse® databases, you can use [the ClickHouse® client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). > [!NOTE] > Two things in the output can surprise you on a first session: > > - A data definition query returns one row per node instead of the empty output stock > ClickHouse prints, because it runs as a distributed DDL. See > [Read the output of a DDL statement](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md#read-the-output-of-a-ddl-statement). > - A client newer than your service, such as the one in the `clickhouse/clickhouse-server` > latest image, prints an `Unknown settings` warning before every result. It is harmless > version skew, see > [Connect to Managed ClickHouse with clickhouse-client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). ## Query a database with a selected tool ### Examples of queries Retrieve a list of current databases: ```sql SHOW DATABASES ``` Count rows: ```sql SELECT COUNT(*) FROM transactions.expenses ``` Create a role: ```sql CREATE ROLE accountant ``` ## Query a non-replicated table Startup plans run a single node, so this section applies to the multi-node plans: Business plans run three nodes and Premium plans run six or more. On those plans, several nodes sit behind the DNS name of your Managed ClickHouse service. When you query a non-replicated table, for example a log table, requests are routed randomly to one of the nodes regardless of how data is distributed across them. A particular row is found only if your `SELECT` query is directed to the node which executed a `WRITE` on this row. To query a non-replicated table across all the service nodes, use `clusterAllReplicas` as follows: ```sql SELECT * FROM clusterAllReplicas(default, system.query_log) WHERE query_id = '1a2b3c4d5e6f7g8h9i0j1a2b3c4d5e6f7g8' ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/restore-backup/ --- # Fork and restore from Managed ClickHouse® backups Restore a Managed ClickHouse® service by forking it: the fork is a new service created from the [backup](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/disaster-recovery/index.md#service-backup) of an existing service. The source service keeps running and is not modified. ## List the available backups Backups are part of the service description. List them with their names, dates and sizes: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --backups ``` ## Fork the service Create a new service with `--clickhouse-fork-from` set to the name of the source service. Pick a plan with at least as many nodes as the source: `startup-16` below matches a Startup source, a Business source needs a Business or Premium plan. ```bash exo dbaas create clickhouse startup-16 my-clickhouse-fork -z ch-gva-2 \ --clickhouse-fork-from my-clickhouse \ --clickhouse-ip-filter /32 ``` Watch the fork until the state is `running`, then connect to it and check your data: ```bash exo dbaas show my-clickhouse-fork -z ch-gva-2 --output-template '{{ .State }}' ``` > [!IMPORTANT] > The fork is restored from a backup of the source service, not from its live state. By > default the latest backup is used; data written to the source after that backup is not > present in the fork. To fork from an older daily backup instead, add `--clickhouse-recovery-backup-name` with one of the backup names listed above: ```bash exo dbaas create clickhouse startup-16 my-clickhouse-fork -z ch-gva-2 \ --clickhouse-fork-from my-clickhouse \ --clickhouse-recovery-backup-name 2026-08-04T11:34:26+00:00 \ --clickhouse-ip-filter /32 ``` The API validates the name and refuses the creation with `Cannot find such backup to recover from.` when it does not match an existing backup. > [!IMPORTANT] > You cannot fork Managed ClickHouse services to a fewer number of nodes. > Reducing the number of nodes is only possible by switching the service plan from > **Business** to **Startup** on a running service: > > ```bash > exo dbaas update my-clickhouse -z ch-gva-2 -f --plan startup-16 > ``` > > See [Resize a service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-manage-cluster/index.md#resize-a-service). > The generic [scaling page](https://community.exoscale.com/product/dbaas/how-to/scaling/index.md) describes the platform behavior. Once the new fork service is running, you can set up your application's connection settings to point to this new fork service. ## Related pages - [Disaster recovery in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/disaster-recovery/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/ --- # Run federated queries in Managed ClickHouse® With federated queries in Managed ClickHouse®, you can read and pull data from S3-compatible object storage, Azure Blob Storage, any web resource accessible over HTTP, and external databases such as PostgreSQL and MySQL. Learn more about capabilities and applications of federated queries in [About querying external data in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/federated-queries/index.md). ## About running federated queries Federated queries are written using specific SQL statements and can be run from CLI, for instance. To run a federated query, just send a query over an external S3-compatible object storage including relevant S3 bucket details. A properly constructed federated query returns a specific output. ## Prerequisites The prerequisites depend on the table function or table engine used in your federated query. ### Access to S3 and URL sources To run a federated query, the ClickHouse service user connecting to the cluster requires grants to the S3 and/or URL sources. The main service user is granted access to the sources by default, and new users can be allowed to use the sources with the following query: ```sql GRANT CREATE TEMPORARY TABLE, S3, URL ON *.* TO username ``` The `CREATE TEMPORARY TABLE` grant is required for both sources. Append `WITH GRANT OPTION` to let the user pass the privileges on to others: ```sql GRANT CREATE TEMPORARY TABLE, S3, URL ON *.* TO username WITH GRANT OPTION ``` ### Azure Blob Storage access keys To run federated queries using the `azureBlobStorage` table function or the `AzureBlobStorage` table engine, get your Azure Blob Storage keys using one of the following tools: - [Azure portal](https://portal.azure.com/) From the portal menu, select **Storage accounts**, go to your account, and click **Security + Networking** > **Access keys**. View and copy your account access keys and connection strings. - [PowerShell](https://learn.microsoft.com/en-us/powershell/scripting/install/installing-powershell?view=powershell-7.4) - [Azure CLI](https://learn.microsoft.com/en-us/cli/azure/install-azure-cli#install) ## Supported sources Managed ClickHouse can query S3-compatible object storage, Azure Blob Storage, HTTP resources through the `url()` table function, and other databases such as PostgreSQL and MySQL. Both table functions and persistent tables backed by the matching table engine (`S3`, `URL`, `AzureBlobStorage`, `PostgreSQL`, `MySQL`) are available. > [!NOTE] > Reading from an external source also requires the matching privilege, for example > `GRANT S3` or `GRANT URL`, in addition to `CREATE TEMPORARY TABLE`. See > [Manage users and roles](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/index.md). ## Run a federated query See some examples of running federated queries to read and pull data from external sources. ### Query using the `azureBlobStorage` table function Pass the storage account connection parameters explicitly in the query. Before you start, fulfill relevant [prerequisites](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md#prerequisites), if any. #### SELECT and `azureBlobStorage` ```sql SELECT * FROM azureBlobStorage( 'DefaultEndpointsProtocol=https;AccountName=ident;AccountKey=secret;EndpointSuffix=core.windows.net', 'ownerresource', 'all_stock_data.csv', 'CSV', 'auto', 'Ticker String, Low Float64, High Float64' ) LIMIT 5 ``` #### INSERT and `azureBlobStorage` ```sql INSERT INTO FUNCTION azureBlobStorage( 'DefaultEndpointsProtocol=https;AccountName=ident;AccountKey=secret;EndpointSuffix=core.windows.net', 'ownerresource', 'test_funcwrite.csv', 'CSV', 'auto', 'key UInt64, data String' ) VALUES (1, 'column2-value'); ``` ### Query an external database Query a PostgreSQL database, such as [Managed PostgreSQL](https://community.exoscale.com/product/dbaas/service-specific/postgreSQL/index.md), directly with the `postgresql()` table function. The user needs the `POSTGRES` privilege in addition to `CREATE TEMPORARY TABLE`: ```sql SELECT * FROM postgresql( 'PG_HOST:PG_PORT', 'defaultdb', 'customers', 'PG_USER', 'PG_PASSWORD' ) LIMIT 5 ``` The `mysql()` table function works the same way for MySQL sources, with the `MYSQL` privilege. For dimensions you join constantly, create a persistent table with the `PostgreSQL` or `MySQL` table engine instead, so the connection details live in one place. ### Query using the `s3` table function Before you start, fulfill relevant [prerequisites](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md#prerequisites), if any. #### SELECT and `s3` SQL SELECT statements using the S3 and URL functions are able to query public resources using the URL of the resource. For instance, let's explore the network connectivity measurement data provided by the [Open Observatory of Network Interference (OONI)](https://ooni.org/data/). ```sql WITH ooni_data_sample AS ( SELECT * FROM s3('https://ooni-data-eu-fra.s3.eu-central-1.amazonaws.com/clickhouse_export/csv/fastpath_202308.csv.zstd') LIMIT 100000 ) SELECT probe_cc AS probe_country_code, test_name, countIf(anomaly = 't') AS total_anomalies FROM ooni_data_sample GROUP BY probe_country_code, test_name HAVING total_anomalies > 10 ORDER BY total_anomalies DESC LIMIT 50 ``` #### INSERT and `s3` When executing an INSERT statement into the S3 function, the rows are appended to the corresponding object if the table structure matches: ```sql INSERT INTO FUNCTION s3('https://bucket-name.s3.region-name.amazonaws.com/dataset-name/landing/raw-data.csv', 'CSVWithNames') VALUES (1, 'column2-value'); ``` ### Query a private S3 bucket Before you start, fulfill relevant [prerequisites](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md#prerequisites), if any. Private buckets can be accessed by providing the access token and secret as function parameters. ```sql SELECT * FROM s3( 'https://private-bucket.s3.eu-west-3.amazonaws.com/dataset-prefix/partition-name.csv', 'some_aws_access_key_id', 'some_aws_secret_access_key' ) ``` Depending on the format, the schema can be automatically detected. If it isn't, you may also provide the column types as function parameters. ```sql SELECT * FROM s3( 'https://private-bucket.s3.eu-west-3.amazonaws.com/orders-dataset/partition-name.csv', 'access_token', 'secret_token', 'CSVWithNames', 'order_id UInt64, quantity Decimal(18, 9), order_datetime DateTime' ) ``` ### Query using the `s3Cluster` table function Before you start, fulfill relevant [prerequisites](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md#prerequisites), if any. The `s3Cluster` function allows all cluster nodes to participate in the query execution. Using `default` for the cluster name parameter, we can compute the same aggregations as above as follows: ```sql WITH ooni_clustered_data_sample AS ( SELECT * FROM s3Cluster('default', 'https://ooni-data-eu-fra.s3.eu-central-1.amazonaws.com/clickhouse_export/csv/fastpath_202308.csv.zstd') LIMIT 100000 ) SELECT probe_cc AS probe_country_code, test_name, countIf(anomaly = 't') AS total_anomalies FROM ooni_clustered_data_sample GROUP BY probe_country_code, test_name HAVING total_anomalies > 10 ORDER BY total_anomalies DESC LIMIT 50 ``` ### Query using the `url` table function Before you start, fulfill relevant [prerequisites](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md#prerequisites), if any. #### SELECT and `url` Let's query the [Growth Projections and Complexity Rankings](https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/XTAQMC&version=4.0) dataset, courtesy of the [Atlas of Economic Complexity](https://atlas.cid.harvard.edu/) project. ```sql WITH economic_complexity_ranking AS ( SELECT * FROM url('https://dataverse.harvard.edu/api/access/datafile/7259657?format=tab', 'TSV') ) SELECT replace(code, '"', '') AS `ISO country code`, growth_proj AS `Forecasted annualized rate of growth`, toInt32(replace(sitc_eci_rank, '"', '')) AS `Economic Complexity Index ranking` FROM economic_complexity_ranking WHERE year = 2021 ORDER BY `Economic Complexity Index ranking` ASC LIMIT 20 ``` #### INSERT and `url` With the URL function, INSERT statements generate a POST request, which can be used to interact with APIs having public endpoints. For instance, if your application has a `ingest-csv` endpoint accepting CSV data, you can insert a row using the following statement: ```sql INSERT INTO FUNCTION url('https://app-name.company-name.cloud/api/ingest-csv', 'CSVWithNames') VALUES (1, 'column2-value'); ``` ### Query a virtual table Before you start, fulfill relevant [prerequisites](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md#prerequisites), if any. Instead of specifying the URL of the resource in every query, it's possible to create a virtual table using the URL table engine. This can be achieved by running a DDL CREATE statement similar to the following: ```sql CREATE TABLE trips_export_endpoint_table ( `trip_id` UInt32, `vendor_id` UInt32, `pickup_datetime` DateTime, `dropoff_datetime` DateTime, `trip_distance` Float64, `fare_amount` Float32 ) ENGINE = URL('https://app-name.company-name.cloud/api/trip-csv-export', CSV) ``` Once the table is defined, SELECT and INSERT statements execute GET and POST requests to the URL respectively: ```sql SELECT toDate(pickup_datetime) AS pickup_date, median(fare_amount) AS median_fare_amount, max(fare_amount) AS max_fare_amount FROM trips_export_endpoint_table GROUP BY pickup_date ``` ```sql INSERT INTO trips_export_endpoint_table VALUES (8765, 10, now() - INTERVAL 15 MINUTE, now(), 50, 20) ``` ## Related pages - [About querying external data in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/federated-queries/index.md) - [Table functions supported in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-functions/index.md) - [Integrating S3 with ClickHouse](https://clickhouse.com/docs/integrations/connectors/data-ingestion/AWS/integrating-s3-with-clickhouse) - [remote, remoteSecure \| ClickHouse Docs](https://clickhouse.com/docs/reference/functions/table-functions/remote) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/secure-service/ --- # Secure a Managed ClickHouse® service You can secure your Managed ClickHouse® service in a few different ways, for example by restricting network access and enabling service termination protection. ## Restrict network access to your service One of the most fundamental ways to keep your service secure is managing its network access properly. Access is controlled by the service IP filter, which lists the CIDR blocks allowed to connect. Set it when you create the service, or update it at any time: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f \ --clickhouse-ip-filter 203.0.113.0/24 ``` The flag replaces the whole list. To allow several ranges, separate them with commas or repeat the flag. Read the current filter back with: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --output-template '{{ .Clickhouse.IPFilter }}' ``` > [!NOTE] > The IP filter applies to every interface of the service, including the Prometheus scrape > endpoint. A connection from an address that is not allowed times out rather than returning an > authentication error. ## Protect a service from termination Exoscale services can be protected against accidental deletion or powering off by enabling the Termination Protection feature. Services created with the CLI have it enabled by default. To enable it on another service: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f --termination-protection ``` > [!NOTE] > Termination Protection has no effect on service migrations or upgrades. ### Terminate a protected service Before terminating a protected service, disable Termination Protection for this service: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f --termination-protection=false ``` > [!NOTE] > Running out of free Exoscale sign-up credits stops your services unless a > payment method has been added to your Exoscale organization. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/sql-user-defined-functions/ --- # Use SQL user defined functions in Managed ClickHouse® Use SQL user defined functions (UDFs) in Managed ClickHouse® to name repeated expressions once and keep your queries readable. You can define your own SQL UDFs, which are automatically replicated to all nodes in the cluster and contained in backups. > [!NOTE] > Managed ClickHouse supports SQL UDFs only. UDF types other than SQL UDFs, such as > [executable UDFs](https://clickhouse.com/docs/reference/functions/regular-functions/udf#executable-user-defined-functions), > are not supported in Managed ClickHouse. ## Create a UDF in SQL To create an SQL UDF in Managed ClickHouse, run the `CREATE FUNCTION` expression including function parameters, constants, operators, or other function calls. ```sql CREATE FUNCTION name AS (parameter0, ...) -> expression ``` ## Limitations - The name of your UDF needs to be unique among other functions. - Recursive functions are not supported. - All variables used by your UDF need to be defined in its parameter list. ## Example of using SQL UDFs 1. Create an SQL UDF. ```sql CREATE FUNCTION is_weekend AS (date) -> toDayOfWeek(date) IN (6, 7); ``` 1. Use your `is_weekend` SQL UDF in a `SELECT` query. This example counts the week-end rides in the `trips` table loaded in the [quick start](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/quick-start/index.md#load-a-dataset): ```sql SELECT count() FROM trips WHERE is_weekend(pickup_date) ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/transfer-data-tiered-storage/ --- # Transfer data between storage devices in Managed ClickHouse®'s tiered storage Moving data from network-attached block storage to object storage allows you to size down your block storage by selecting a service plan with less capacity. You can move the data back to network-attached block storage anytime. You can transfer data between storage devices in Managed ClickHouse® using SQL statements against your tables directly. ## Prerequisites - At least one Managed ClickHouse service - Command line tool ([ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md)) installed ## Transfer data from network-attached block storage to object storage **Automatic data transfer** If you [enable](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) the tiered storage feature on your table, by default your data is moved from network-attached block storage to object storage as soon as it reaches 80% of its capacity. 1. [Connect to your Managed ClickHouse service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md) using, for example, the ClickHouse client. 1. Run the following query: ```sql ALTER TABLE database_name.table_name MODIFY SETTING storage_policy = 'tiered' ``` Now, with the tiered storage feature [enabled](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md), your data is moved from network-attached block storage to object storage when it reaches 80% of its capacity. You can [adjust that threshold](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md#size-based-threshold) at the service level. > [!NOTE] > You can also > [configure your tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) > so that data is moved to object storage at a specific time. **Manual data transfer** To move data manually from network-attached block storage to object storage, first list the partitions of your table: ```sql SELECT partition, formatReadableSize(sum(data_compressed_bytes)) AS size FROM system.parts WHERE database = 'default' AND table = 'trips' AND active GROUP BY partition ORDER BY partition ``` Then move a partition by its value. On the quick-start `trips` table, partitioned by `toYYYYMM(pickup_date)`, a partition value looks like `201507`: ```sql ALTER TABLE default.trips MOVE PARTITION 201507 TO VOLUME 'remote' ``` To configure data retention thresholds to automatically move data from network-attached block storage to object storage, see [Configure data retention thresholds in Managed ClickHouse®'s tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md). ## Transfer data from object storage to network-attached block storage Use the [MOVE PARTITION\|PART](https://clickhouse.com/docs/reference/statements/alter/partition#move-partitionpart) statement to transfer data to network-attached block storage. 1. [Connect to your Managed ClickHouse service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md) using, for example, the ClickHouse client. 1. Select a database for operations you intend to perform. ```sql USE database_name ``` 1. Run the following query: ```sql ALTER TABLE table_name MOVE PARTITION partition_expr TO VOLUME 'default' ``` Your data has been moved to network-attached block storage. ## What's next - [Check data distribution between network-attached block storage and object storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/check-data-tiered-storage/index.md) - [Configure data retention thresholds for tiered storage](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/index.md) - [About tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/index.md) - [Enable tiered storage in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/use-shards-with-distributed-table/ --- # Enable reading and writing data across shards in Managed ClickHouse® If your Managed ClickHouse® service uses multiple shards, the data is replicated only between nodes of the same shard. When you read from one node, you see the data from the shard of this node. Creating a [distributed table](https://clickhouse.com/docs/reference/engines/table-engines/special/distributed) on top of your replicated table, while the distributed table itself doesn't store data allows you to see data from all the shards but also helps spread the data evenly across all the cluster nodes. ## Set up a sharded service with a database 1. Create a Managed ClickHouse® service with multiple shards. > [!NOTE] > Shards are created automatically when you create a Managed ClickHouse > service. The number of shards that your service gets depends on the > plan you select for your service. Startup plans run a single node with one shard, and > Business plans run three nodes that hold one shard. On Premium plans, the number of shards > is the number of nodes divided by three. > [!IMPORTANT] > Multi-shard plans are only available on Premium. Check the `authorized` column for your own > organization before you follow this page: > > ```bash > exo dbaas type show clickhouse --plans > ``` > > If the plan you need is not authorized, ask [our support](https://www.exoscale.com/contact/) > to enable it for your organization. See > [plan availability](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/index.md#plans). 1. [Create database](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md#create-a-clickhouse-database) `test_db` in your new service. ## Create a distributed table 1. [Connect to your database](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md). 1. Create a table with the [MergeTree engine](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree) as shown for the `cash_flows` table in the following example: ```sql CREATE TABLE test_db.cash_flows ( EventDate DateTime, SourceAccount UInt64, TargetAccount UInt64, Amount Float64 ) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, SourceAccount) ``` > [!NOTE] > With Managed ClickHouse, you can specify `ENGINE` either as > `MergeTree` or as `ReplicatedMergeTree`. Both of them create a > `ReplicatedMergeTree` table. 1. Create distributed table `cash_flows_distributed` with the distributed engine: ```sql CREATE TABLE test_db.cash_flows_distributed AS test_db.cash_flows ENGINE = Distributed(test_db, test_db, cash_flows, SourceAccount) ``` ## Verify your distributed table Check if the distributed table you created is available and if you can use it to access your data from all the shards. 1. Run a read query for the number of table rows: ```sql SELECT count() FROM test_db.cash_flows_distributed ``` As a response to this query, you can expect to receive a number of rows from all the shards. This is because when you connect on one node and read from the distributed table, ClickHouse® aggregates the data from all the shards and returns all of it. 1. Run a write query to insert new data into the distributed table: ```sql INSERT INTO test_db.cash_flows_distributed ( EventDate, SourceAccount, TargetAccount, Amount ) VALUES ( '2022-01-02 03:04:05', 123, 456, 100.0 ) ``` When you insert data into the distributed table, ClickHouse® decides on which node the data should be stored and write it to the correct node making sure that a similar volume of data is written on all the nodes. > [!NOTE] > Both queries above also run on a single-shard Startup plan, but there is only one shard to > fan out to, so they read from and write to that shard and nothing is spread. A distributed > table only becomes useful on a plan that has more than one shard. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/vector-similarity-index-cache/ --- # Tune the vector similarity index cache in Managed ClickHouse® Tune the vector similarity index cache in Managed ClickHouse® to improve vector search performance for Hierarchical Navigable Small World (HNSW) indexes. HNSW is a graph-based index that speeds up approximate nearest neighbor search on vector columns. Managed ClickHouse uses a segmented least recently used (SLRU) cache to keep HNSW index data in memory during vector search queries. If the cache evicts index data too often, ClickHouse reloads it from disk during queries, which can significantly increase query latency. ## How the cache works The size of the cache is set by `server_settings.vector_similarity_index_cache_size`, expressed as a fraction of the total server memory. It is the only vector cache setting you can change on Managed ClickHouse, and its default is `0.07`, meaning 7% of the memory allocated to the ClickHouse server. Setting it to `0` disables the cache, and the maximum accepted value is `0.5`. The cache itself is an SLRU cache with two segments: - **Protected segment**: Stores frequently used index entries. - **Probationary segment**: Stores newly loaded or less frequently used index entries. The share of the cache reserved for the protected segment is fixed by the platform at `0.5` through the read-only server setting `vector_similarity_index_cache_size_ratio`. You can read its value from `system.server_settings`, but you cannot change it. ClickHouse stores HNSW indexes per table part, a chunk of table data on disk. To avoid repeated evictions, the protected segment must be large enough to hold the largest per-part HNSW index used by your queries. > [!NOTE] > Table part structure affects cache behavior. A table with many smaller parts might cache > more efficiently than one with a single large merged part because each smaller per-part > HNSW index is more likely to fit within the protected segment. > > Monitor part structure if you continue to see evictions after increasing the cache size. ## Prerequisites - A Managed ClickHouse service running ClickHouse 25.8 or later. - A table with an HNSW vector similarity index and an active vector search workload. - A SQL client, such as the [ClickHouse client](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/index.md), to run the monitoring queries. ## Configure the cache size Set the cache size as a fraction of the server memory, between `0` and `0.5`: ```bash exo dbaas update my-clickhouse -z ch-gva-2 -f \ --clickhouse-settings '{"server_settings":{"vector_similarity_index_cache_size":0.15}}' ``` Read the effective value back from the service: ```sql SELECT name, value FROM system.server_settings WHERE name LIKE '%vector_similarity%'; ``` > [!CAUTION] > Vector search performance does not degrade gradually when the cache is too small. If the > protected segment cannot hold the largest per-part HNSW index, ClickHouse evicts and > reloads index entries repeatedly, causing a sharp increase in query latency. The best value depends on: - The size of the HNSW indexes. - The number and size of table parts. - Query concurrency and access patterns. - Table merges that create larger parts over time. As a rule of thumb, set the cache size so the protected SLRU segment, which is half of the cache, can hold the largest per-part HNSW index without eviction. ## Monitor cache health Use `system.events` to monitor vector similarity index cache activity: ```sql SELECT event, value FROM system.events WHERE event LIKE '%VectorSimilarity%' ORDER BY event; ``` Monitor `VectorSimilarityIndexCacheWeightLost`. If this value is greater than `0` during steady-state vector search queries, ClickHouse evicts vector index data. Increase `server_settings.vector_similarity_index_cache_size` and run the workload again. > [!NOTE] > `system.events` omits counters that are still at zero, so an event that does not appear in > the result has never fired. If `VectorSimilarityIndexCacheWeightLost` is absent, no eviction > has occurred. > [!NOTE] > Do not rely on hit rate alone to assess cache health. A high hit rate can coexist with > evictions if ClickHouse reloads and reinserts indexes repeatedly. For stable vector > search performance, keep `VectorSimilarityIndexCacheWeightLost` at `0` during > steady-state queries. ## Troubleshoot slow vector search queries If vector search queries are slower than expected: 1. Verify that the query uses the HNSW vector similarity index. 1. Run the `system.events` query to monitor vector similarity index cache activity. 1. Confirm whether `VectorSimilarityIndexCacheWeightLost` increases during steady-state queries. 1. If evictions occur, increase `server_settings.vector_similarity_index_cache_size`. 1. Monitor query latency and cache events again. If evictions continue after increasing the cache size, the protected segment might still be too small for the largest per-part HNSW index. Review the table part structure. ## Related pages - [Indexing and data processing in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/overview/indexing/index.md) - [Use query cache in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/clickhouse-query-cache/index.md) - [Fetch query statistics for Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/fetch-query-statistics/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/ --- # Service Boundaries - ClickHouse - [Limits and Quotas - ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/index.md) - Managed ClickHouse® limits and limitations - [SLA - ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/sla/index.md) - Managed ClickHouse® Service Level Agreement - [Pricing ↗︎](https://www.exoscale.com/pricing/#dbaas) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/ --- # Limits and Quotas - ClickHouse By respecting the Managed ClickHouse® restrictions and quotas, you can improve the security and productivity of your service workloads. ## Limitations From the information about restrictions on using Managed ClickHouse, you can draw conclusions on how to get your service to operate closer to its full potential. Use **Recommended approach** as guidelines on how to work around specific restrictions.
Name Description Recommended approach
Backups - one snapshot a day Since Managed ClickHouse service takes a single snapshot a day only:
  • Point-in-time recovery is not supported. A database can be restored to one of the daily backup states only.
  • When creating a database fork, you can only create a fork that matches the state of one of the backups.
  • Any data inserted before the next snapshot is lost if all nodes in a given shard malfunction and need to be replaced. This limitation doesn't apply to patches, migrations, or scaling, which are handled safely and automatically.
 N/A
Table engines support
  • Some special table engines are not supported in Managed ClickHouse.
  • Some engines are remapped to their Replicated alternatives, for example, MergeTree > ReplicatedMergeTree.
Use the available table engines listed in Supported table engines in Managed ClickHouse.
Log table engine support Log engine is not supported in Managed ClickHouse. For storing data, use a supported MergeTree family engine, which is what the Log family is usually standing in for in small tables. If what you need is to absorb many small inserts, put a Buffer table in front of a MergeTree table: Buffer is not a storage engine, it holds rows in memory and flushes them into a destination table that must already exist, and its contents are lost if the service restarts.
Kafka table engine support The Kafka table engine cannot be created directly in SQL. N/A
Cloud availability Managed ClickHouse® runs in Exoscale zones (its own European cloud), not on AWS, GCP or Azure. Choose an available Exoscale zone.
Querying all shards at once If you have a sharded plan, you must use a distributed table on top of your MergeTree table to query all the shards at the same time, and you should use it for inserts too. Use a distributed table with sharded plans. See Query data across shards.
Creating or deleting a database using SQL
  • Only the avnadmin user can create databases in SQL.
  • You can create a database in SQL with the Replicated database engine only.
  • By default, only the avnadmin user can delete a database. The avnadmin user can grant the permission to delete a database to another user.
 Create and drop databases in SQL as avnadmin, as shown in Manage Managed ClickHouse® databases and tables. Unlike Managed MySQL and Managed PostgreSQL, the DBaaS API and CLI expose no database-level operations for ClickHouse, so there is no API workaround.
Maximum number of databases per service Your Managed ClickHouse service can support up to 400 databases simultaneously. Instead of creating multiple databases of the same structure for isolation purposes, it's recommended to create one database where you add an extra column to filter by it. Consider including this column into the primary key or partitioning by it. You can also limit scope of data on which SQL queries can be run by using the additional_table_filters query setting.
## Limits Service limits are determined by a plan that this service uses. For the number of VMs, CPU per VM, RAM per VM, storage details per plan, see [Plans & pricing](https://www.exoscale.com/pricing/#dbaas). ### Plans > [!NOTE] > Disk figures are per node: allocated is the provisioned volume, usable excludes the 10% > reserved for the OS. The API `disk_space` figure corresponds to the usable disk multiplied > by the number of shards. List the plans at any time with `exo dbaas type show clickhouse --plans`. > Plans with `Request Only` access are available via [our support](https://www.exoscale.com/contact/). > > The figures in the tables are binary units (GiB), while the CLI and the API report the same > quantities in decimal units (GB). A `startup-16` service therefore shows 17 GB of RAM and > 386 GB of disk in `exo dbaas type show clickhouse --plans`, which is the very same 16 GiB > and 360 GiB listed here. > [!IMPORTANT] > Plans with `Public` access are self-service, from Startup to Premium. Plans with > `Request Only` access are enabled per organization on request. The `authorized` flag shows > which plans your organization can create: > > ```bash > exo dbaas type show clickhouse --plans > ``` > > Contact [our support](https://www.exoscale.com/contact/) to have a plan enabled for your > organization. **Standard plans** | Plan | Nodes | vCPU/node | RAM/node (GiB) | Allocated disk/node (GiB) | Usable disk/node (GiB) | Access | | --- | --- | --- | --- | --- | --- | --- | | `startup-8` | 1 | 4 | 8 | 200 | 180 | Public | | `startup-16` | 1 | 4 | 16 | 400 | 360 | Public | | `startup-32` | 1 | 8 | 32 | 400 | 360 | Public | | `startup-64` | 1 | 12 | 64 | 400 | 360 | Public | | `startup-128` | 1 | 16 | 128 | 800 | 720 | Public | | `startup-225` | 1 | 24 | 225 | 800 | 720 | Public | | `startup-320` | 1 | 40 | 320 | 3200 | 2880 | Public | | `business-8` | 3 | 4 | 8 | 300 | 270 | Public | | `business-16` | 3 | 4 | 16 | 600 | 540 | Public | | `business-32` | 3 | 8 | 32 | 600 | 540 | Public | | `business-64` | 3 | 12 | 64 | 600 | 540 | Public | | `business-128` | 3 | 16 | 128 | 1200 | 1080 | Public | | `business-225` | 3 | 24 | 225 | 1200 | 1080 | Public | | `business-320` | 3 | 40 | 320 | 3200 | 2880 | Public | | `premium-6x-16` | 6 | 4 | 16 | 800 | 720 | Public | | `premium-6x-32` | 6 | 8 | 32 | 800 | 720 | Public | | `premium-6x-64` | 6 | 12 | 64 | 800 | 720 | Public | | `premium-6x-128` | 6 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-6x-225` | 6 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-6x-320` | 6 | 40 | 320 | 15360 | 13824 | Request Only | | `premium-9x-16` | 9 | 4 | 16 | 800 | 720 | Public | | `premium-9x-32` | 9 | 8 | 32 | 800 | 720 | Public | | `premium-9x-64` | 9 | 12 | 64 | 800 | 720 | Public | | `premium-9x-128` | 9 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-9x-225` | 9 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-9x-320` | 9 | 40 | 320 | 15360 | 13824 | Request Only | | `premium-12x-16` | 12 | 4 | 16 | 800 | 720 | Public | | `premium-12x-32` | 12 | 8 | 32 | 800 | 720 | Public | | `premium-12x-64` | 12 | 12 | 64 | 800 | 720 | Public | | `premium-12x-128` | 12 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-12x-225` | 12 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-12x-320` | 12 | 40 | 320 | 15360 | 13824 | Request Only | | `premium-15x-16` | 15 | 4 | 16 | 800 | 720 | Public | | `premium-15x-32` | 15 | 8 | 32 | 800 | 720 | Public | | `premium-15x-64` | 15 | 12 | 64 | 800 | 720 | Public | | `premium-15x-128` | 15 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-15x-225` | 15 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-15x-320` | 15 | 40 | 320 | 15360 | 13824 | Request Only | | `premium-18x-16` | 18 | 4 | 16 | 800 | 720 | Public | | `premium-18x-32` | 18 | 8 | 32 | 800 | 720 | Public | | `premium-18x-64` | 18 | 12 | 64 | 800 | 720 | Public | | `premium-18x-128` | 18 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-18x-225` | 18 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-18x-320` | 18 | 40 | 320 | 15360 | 13824 | Request Only | | `premium-21x-16` | 21 | 4 | 16 | 800 | 720 | Public | | `premium-21x-32` | 21 | 8 | 32 | 800 | 720 | Public | | `premium-21x-64` | 21 | 12 | 64 | 800 | 720 | Public | | `premium-21x-128` | 21 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-21x-225` | 21 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-21x-320` | 21 | 40 | 320 | 15360 | 13824 | Request Only | | `premium-24x-16` | 24 | 4 | 16 | 800 | 720 | Public | | `premium-24x-32` | 24 | 8 | 32 | 800 | 720 | Public | | `premium-24x-64` | 24 | 12 | 64 | 800 | 720 | Public | | `premium-24x-128` | 24 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-24x-225` | 24 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-24x-320` | 24 | 40 | 320 | 15360 | 13824 | Request Only | | `premium-27x-16` | 27 | 4 | 16 | 800 | 720 | Public | | `premium-27x-32` | 27 | 8 | 32 | 800 | 720 | Public | | `premium-27x-64` | 27 | 12 | 64 | 800 | 720 | Public | | `premium-27x-128` | 27 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-27x-225` | 27 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-27x-320` | 27 | 40 | 320 | 15360 | 13824 | Request Only | | `premium-30x-16` | 30 | 4 | 16 | 800 | 720 | Public | | `premium-30x-32` | 30 | 8 | 32 | 800 | 720 | Public | | `premium-30x-64` | 30 | 12 | 64 | 800 | 720 | Public | | `premium-30x-128` | 30 | 16 | 128 | 1600 | 1440 | Request Only | | `premium-30x-225` | 30 | 24 | 225 | 1600 | 1440 | Request Only | | `premium-30x-320` | 30 | 40 | 320 | 15360 | 13824 | Request Only | **Memory-optimized plans** | Plan | Nodes | vCPU/node | RAM/node (GiB) | Allocated disk/node (GiB) | Usable disk/node (GiB) | Access | | --- | --- | --- | --- | --- | --- | --- | | `mo-startup-16` | 1 | 2 | 16 | 400 | 360 | Public | | `mo-startup-32` | 1 | 4 | 32 | 400 | 360 | Public | | `mo-startup-64` | 1 | 8 | 64 | 400 | 360 | Public | | `mo-startup-128` | 1 | 12 | 128 | 800 | 720 | Request Only | | `mo-business-16` | 3 | 2 | 16 | 600 | 540 | Public | | `mo-business-32` | 3 | 4 | 32 | 600 | 540 | Public | | `mo-business-64` | 3 | 8 | 64 | 600 | 540 | Public | | `mo-business-128` | 3 | 12 | 128 | 1200 | 1080 | Request Only | | `mo-premium-6x-16` | 6 | 2 | 16 | 800 | 720 | Public | | `mo-premium-6x-32` | 6 | 4 | 32 | 800 | 720 | Public | | `mo-premium-6x-64` | 6 | 8 | 64 | 800 | 720 | Public | | `mo-premium-6x-128` | 6 | 12 | 128 | 1600 | 1440 | Request Only | | `mo-premium-9x-16` | 9 | 2 | 16 | 800 | 720 | Request Only | | `mo-premium-9x-32` | 9 | 4 | 32 | 800 | 720 | Request Only | | `mo-premium-9x-64` | 9 | 8 | 64 | 800 | 720 | Request Only | | `mo-premium-9x-128` | 9 | 12 | 128 | 1600 | 1440 | Request Only | | `mo-premium-12x-16` | 12 | 2 | 16 | 800 | 720 | Request Only | | `mo-premium-12x-32` | 12 | 4 | 32 | 800 | 720 | Request Only | | `mo-premium-12x-64` | 12 | 8 | 64 | 800 | 720 | Request Only | | `mo-premium-12x-128` | 12 | 12 | 128 | 1600 | 1440 | Request Only | | `mo-premium-15x-16` | 15 | 2 | 16 | 800 | 720 | Request Only | | `mo-premium-15x-32` | 15 | 4 | 32 | 800 | 720 | Request Only | | `mo-premium-15x-64` | 15 | 8 | 64 | 800 | 720 | Request Only | | `mo-premium-15x-128` | 15 | 12 | 128 | 1600 | 1440 | Request Only | | `mo-premium-18x-16` | 18 | 2 | 16 | 800 | 720 | Request Only | | `mo-premium-18x-32` | 18 | 4 | 32 | 800 | 720 | Request Only | | `mo-premium-18x-64` | 18 | 8 | 64 | 800 | 720 | Request Only | | `mo-premium-18x-128` | 18 | 12 | 128 | 1600 | 1440 | Request Only | | `mo-premium-21x-16` | 21 | 2 | 16 | 800 | 720 | Request Only | | `mo-premium-21x-32` | 21 | 4 | 32 | 800 | 720 | Request Only | | `mo-premium-21x-64` | 21 | 8 | 64 | 800 | 720 | Request Only | | `mo-premium-21x-128` | 21 | 12 | 128 | 1600 | 1440 | Request Only | | `mo-premium-24x-16` | 24 | 2 | 16 | 800 | 720 | Request Only | | `mo-premium-24x-32` | 24 | 4 | 32 | 800 | 720 | Request Only | | `mo-premium-24x-64` | 24 | 8 | 64 | 800 | 720 | Request Only | | `mo-premium-24x-128` | 24 | 12 | 128 | 1600 | 1440 | Request Only | | `mo-premium-27x-16` | 27 | 2 | 16 | 800 | 720 | Request Only | | `mo-premium-27x-32` | 27 | 4 | 32 | 800 | 720 | Request Only | | `mo-premium-27x-64` | 27 | 8 | 64 | 800 | 720 | Request Only | | `mo-premium-27x-128` | 27 | 12 | 128 | 1600 | 1440 | Request Only | | `mo-premium-30x-16` | 30 | 2 | 16 | 800 | 720 | Request Only | | `mo-premium-30x-32` | 30 | 4 | 32 | 800 | 720 | Request Only | | `mo-premium-30x-64` | 30 | 8 | 64 | 800 | 720 | Request Only | | `mo-premium-30x-128` | 30 | 12 | 128 | 1600 | 1440 | Request Only | | Managed ClickHouse | Startup | Business | Premium | | --- | --- | --- | --- | | Maximum concurrent queries | Set by `max_concurrent_queries_for_all_users`, from 100 on the smallest plans | Set by `max_concurrent_queries_for_all_users` | Set by `max_concurrent_queries_for_all_users` | | Maximum concurrent connections | 4000 connections per node | 4000 connections per node | 4000 connections per node | > [!NOTE] > **Query concurrency limits** > > `max_concurrent_queries_for_all_users` is the limit your queries reach first: it caps how many > queries run at once across all users. The server-wide `max_concurrent_queries` setting is > higher, from 147 on the smallest plans, because it reserves extra capacity for monitoring, > backup, and operator queries. Both values are managed by Exoscale and scale with the service > size. For their full ranges, see > [Managed ClickHouse® 25.8 default settings](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/25-8-default-settings/index.md). > [!NOTE] > **Total storage with a plan** > > Total storage represents the maximum amount of data you can insert into a service. It does > not grow with the number of replicas, since replicas hold another copy of the same data, but > it does grow with the number of shards. > > The inserted data is replicated on all available nodes. How many times it's replicated > depends on the number of nodes and the number of shards: > > ```math > number_of_data_replication_times = number_of_nodes / number_of_shards > ``` > > **Examples** > > - Service plan with **one shard** > > A `startup-16` plan has 360 GiB of usable disk on its single node. A `business-16` plan has > 540 GiB usable per node across three nodes that replicate the same data, so the effective > capacity remains 540 GiB. > > - Service plan with **two shards** > > A `premium-6x-16` plan has six nodes organized as two shards of three replicas each, with > 720 GiB usable per node; each shard stores its own portion of the data, for 1440 GiB of total > usable capacity. > > [!TIP] > If you need a custom plan with capacity beyond the listed limits, > [contact us](https://www.exoscale.com/contact/). ## Related pages - [Plans and pricing, including the quotas of the Business and Premium tiers](https://www.exoscale.com/pricing/#dbaas) - [Managed ClickHouse® 25.8 default settings](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/25-8-default-settings/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/sla/ --- # SLA - ClickHouse Refer to the overall [DBaaS SLA](https://community.exoscale.com/product/dbaas/service-boundaries/sla/index.md). The DBaaS SLA applies to Managed ClickHouse®: Startup plans carry the 99.95% availability target and Business and Premium plans carry the 99.99% target, with the same service credits as the other DBaaS services. Managed ClickHouse has no Hobbyist tier, so the Hobbyist row of the shared SLA does not apply. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/ --- # Reference - ClickHouse > [!NOTE] > ClickHouse services are managed with the `exo dbaas` commands, which > require Exoscale CLI 1.100.0 or later. See > [Manage your Managed ClickHouse® service](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-manage-cluster/index.md). - [Managed ClickHouse® 25.8 default settings](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/25-8-default-settings/index.md) - The session, table and server settings that Exoscale manages on ClickHouse 25.8, with their default values. - [Managed ClickHouse® metrics available via Datadog](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-metrics-datadog/index.md) - Forward your service metrics to Datadog through an external endpoint, and find the metric list. - [Managed ClickHouse® metrics available via Prometheus](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-metrics-prometheus/index.md) - Enable the Prometheus scrape endpoint of your service and see which metric families it exposes. - [System tables in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-system-tables/index.md) - Which system tables are available on the managed service, which are not, and how long the log tables keep data. - [S3 table function file formats in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/s3-supported-file-formats/index.md) - The file formats you can read and write with the s3 table function. - [Supported database engines in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-database-engines/index.md) - The database engines the service exposes, and why SQL-created databases always use Replicated. - [Interfaces and drivers supported in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-interfaces-drivers/index.md) - The four protocols your service exposes, and the interfaces that are not available. - [Supported table engines in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-engines/index.md) - Every table engine available on the managed service, including the ones remapped to replicated variants. - [Table functions supported in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-functions/index.md) - The table functions you can call on the managed service, from s3 and url to postgresql. - [Managed ClickHouse® version support policy](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/version-lifecycle/index.md) - How ClickHouse LTS versions move from early availability to end of life on Exoscale. - [API DBaaS](https://community.exoscale.com/reference/api/dbaas/dbaas-management/index.md) - All operations on DBaaS. (external) - [CLI DBaaS](https://community.exoscale.com/reference/cli/exo/dbaas/index.md) - The `exo dbaas` commands, which manage ClickHouse services from Exoscale CLI 1.100.0. (external) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/25-8-default-settings/ --- # Managed ClickHouse® 25.8 default settings Managed ClickHouse® uses a managed configuration that differs from upstream ClickHouse defaults. These differences help keep services reliable, secure, and predictable in Exoscale-managed environments. The following sections list the settings that Exoscale manages or constrains on version 25.8, grouped by session, table, and server scope. Some of them keep the upstream default value while the platform prevents you from changing it. ## How to read the settings tables Each setting includes an Exoscale default value and indicates whether you can change it: - **Can be changed: No**: Exoscale manages the setting value. You cannot override it. - **Can be changed: Yes**: Exoscale sets the default value, but you can change it where the setting is configurable. Where Exoscale defaults vary by service plan or node resources, the **Exoscale default** column shows ranges or placeholders. The **Description** column explains why the default differs or lists any configurable bounds, such as an allowed range or minimum value. Values shown as ranges or placeholders, such as `[3..80, depending on CPU count]`, `{cpu_count}`, or `[~7% of RAM]`, are sized automatically based on your service plan and node resources. > [!NOTE] > For Exoscale-defined limits, see > [Limits and limitations](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/index.md). ## Session settings These settings apply to sessions and queries. | Setting | Exoscale default | Can be changed | Description | | ------------------------------------------------------- | --------------------------------------- | -------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `allow_deprecated_error_prone_window_functions` | `0` | No | Keeps deprecated, error-prone window functions disabled. | | `allow_deprecated_snowflake_conversion_functions` | `0` | No | Keeps deprecated Snowflake conversion functions disabled. | | `allow_experimental_kafka_offsets_storage_in_keeper` | `0` | No | Keeps experimental Kafka offset storage in Keeper disabled. | | `allow_introspection_functions` | `0` | Yes | Keeps sensitive debugging and introspection functions disabled by default. | | `allow_non_default_profile` | `0` | No | Prevents switching to unmanaged profiles. | | `cancel_http_readonly_queries_on_client_close` | `1` | Yes | Cancels read-only HTTP queries when clients disconnect. | | `cluster_function_process_archive_on_multiple_nodes` | `0` | Yes | Disables archive processing on multiple nodes by default. | | `compatibility` | `""` | Yes | Does not force a lower compatibility profile by default. | | `database_replicated_allow_replicated_engine_arguments` | `1` | No | Supports replicated database and table creation in Exoscale-managed services. | | `distributed_ddl_entry_format_version` | `7` | No | Pins the distributed DDL entry format to avoid inconsistencies during version upgrades. | | `distributed_ddl_output_mode` | `null_status_on_timeout` | Yes | Makes distributed DDL timeout reporting more resilient to recoverable errors. Because databases use the `Replicated` engine, every DDL statement runs as a distributed DDL, and any output mode other than `none` returns one row per node instead of the empty output stock ClickHouse prints. See [Read the output of a DDL statement](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/index.md#read-the-output-of-a-ddl-statement). | | `format_display_secrets_in_show_and_select` | `0` | Yes | Avoids exposing secrets in `SHOW` and `SELECT` output by default. | | `http_write_exception_in_output_format` | `0` | Yes | Keeps exceptions out of the requested output format so client errors are easier to detect. | | `max_autoincrement_series` | `1000` | No | Limits automatic sequence expansion. | | `max_concurrent_queries_for_all_users` | `[100..800, depending on service size]` | No | Enforces a service-wide concurrency limit. | | `max_http_get_redirects` | `10` | Yes | Limits HTTP redirect following. | | `max_insert_threads` | `2` | Yes | Allowed range: `1..{cpu_count}`. Caps insert parallelism so a single query does not use too many shared service resources. | | `max_threads` | `{cpu_count}` | Yes | Allowed range: `1..{cpu_count}`. Caps general query parallelism to the node CPU count. | | `memory_profiler_sample_probability` | `0` | No | Disables random memory profiler sampling. | | `memory_profiler_step` | `4194304` | Yes | Minimum value: `100000`. Keeps the upstream memory profiler granularity while preventing very low values. | | `min_free_disk_ratio_to_perform_insert` | `0.05` | No | Keeps a disk headroom guard before accepting inserts. | | `output_format_json_quote_64bit_integers` | `1` | Yes | Keeps JSON output compatible with clients that cannot safely represent 64-bit integers. This default can change in a future version. | | `postgresql_connection_attempt_timeout` | `10` | Yes | Limits each PostgreSQL connection attempt. | | `postgresql_connection_pool_connect_timeout` | `10` | Yes | Limits pooled PostgreSQL connection setup. | | `push_external_roles_in_interserver_queries` | `0` | No | External authorization is not supported. Roles must be synchronized between nodes. | | `query_profiler_cpu_time_period_ns` | `1000000000` | Yes | Minimum value: `1000000`. Keeps the upstream CPU profiler sampling period while preventing sub-millisecond values. | | `query_profiler_real_time_period_ns` | `1000000000` | Yes | Minimum value: `1000000`. Keeps the upstream real-time profiler sampling period while preventing sub-millisecond values. | | `readonly` | `0` | Yes | Controls read/write access for the session. You can change the value from `0` to `1`, but not from `1` to `0`. | | `stream_like_engine_allow_direct_select` | `1` | Yes | Allows direct `SELECT` queries on stream-like engines, which can have suboptimal performance or behavior. A future release returns this default to the upstream default. | | `write_full_path_in_iceberg_metadata` | `1` | Yes | Writes full Iceberg metadata paths for compatibility with mainstream ClickHouse behavior. | ## Replicated MergeTree table settings These settings apply to the `ReplicatedMergeTree` table engine family. On the Exoscale, `MergeTree` engines are remapped to their `ReplicatedMergeTree` variants. See [Supported table engines](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-engines/index.md). | Setting | Exoscale default | Can be changed | Description | | --------------------------------------------------------------------- | ------------- | -------------- | ------------------------------------------------------------------------------------------------------------------------------ | | `allow_remote_fs_zero_copy_replication` | `0` | No | Keeps zero-copy replication for remote filesystem storage disabled. Exoscale manages this storage behavior. | | `disable_detach_partition_for_zero_copy_replication` | `1` | No | Blocks `DETACH PARTITION` on zero-copy replicated tables. | | `disable_fetch_partition_for_zero_copy_replication` | `1` | No | Blocks `FETCH PARTITION` on zero-copy replicated tables. | | `disable_freeze_partition_for_zero_copy_replication` | `1` | No | Blocks `FREEZE PARTITION` on zero-copy replicated tables. | | `enable_max_bytes_limit_for_min_age_to_force_merge` | `0` | No | Leaves the byte-size limit unenforced when age-based forced merges are considered. | | `finished_mutations_to_keep` | `100` | Yes | Limits retained finished mutation metadata so ZooKeeper or Keeper state does not grow unnecessarily. | | `max_parts_in_total` | `100000` | Yes | Sets a cap on table part count to protect merge performance and metadata size. | | `number_of_free_entries_in_pool_to_execute_mutation` | `20` | Yes | Uses the upstream default while limiting the value below the service background pool capacity. | | `number_of_free_entries_in_pool_to_execute_optimize_entire_partition` | `25` | Yes | Uses the upstream default while limiting the value below the service background pool capacity. | | `number_of_free_entries_in_pool_to_lower_max_size_of_merge` | `8` | Yes | Uses the upstream default while limiting the value below the service background pool capacity. | | `old_parts_lifetime` | `480` | Yes | Reduces the lifetime of outdated merged parts to lower the number of ZooKeeper or Keeper metadata nodes. | ## Server settings These settings are configured at the server level and are managed by Exoscale. They are not configurable as session settings. Most settings are derived from your service plan. Some settings, such as `vector_similarity_index_cache_size`, are exposed through advanced configuration. | Setting | Exoscale default | Description | | ----------------------------------------------- | --------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | `background_pool_size` | `[8..32, depending on CPU count]` | Sizes the background merge and mutation pool based on service CPU capacity. If an explicit service-level override is configured, Exoscale preserves it. | | `cgroups_memory_usage_observer_wait_time` | `0` | Disables the ClickHouse cgroup memory usage observer because Exoscale sets explicit memory limits for the managed server process. | | `cluster_database` | `default` | Sets the database used by cluster-related helpers to the managed default database. | | `database_atomic_delay_before_drop_table_sec` | `0` | Removes the Atomic database delayed-drop wait to prevent race conditions in refreshable materialized views. | | `default_database` | `default` | Uses the managed default database unless another database is selected explicitly. | | `default_replica_name` | `{replica}` | Uses the Exoscale-provided replica macro in replicated table paths. | | `dictionary_user` | `avnadmin` | Runs dictionary queries through the managed admin user. | | `disable_internal_dns_cache` | `1` | Prevents ClickHouse from keeping stale internal DNS results in a managed environment where node addresses can change. | | `display_secrets_in_show_and_select` | `1` | Keeps server-level secret display available for privileged managed operations. User-facing profile settings hide secrets by default. | | `iceberg_catalog_threadpool_pool_size` | `[3..80, depending on CPU count]` | Scales Iceberg catalog worker concurrency with service CPU capacity. | | `iceberg_metadata_files_cache_size` | `[~7% of RAM]` | Sizes the Iceberg metadata file cache based on the managed memory budget. | | `index_mark_cache_size` | `[~7% of RAM]` | Sizes the secondary index mark cache based on the managed memory budget. | | `keep_alive_timeout` | `10` | Limits idle keep-alive connections to 10 seconds so clients cannot hold server resources indefinitely. | | `mark_cache_size` | `[~7% of RAM]` | Sizes the primary mark cache based on the managed memory budget. | | `max_concurrent_queries` | `[147..847, depending on service size]` | Sets a server-wide concurrency limit based on the service plan, with extra internal capacity reserved for monitoring, backup, and operator queries. | | `max_connections` | `4000` | Sets the connection limit per node. Measured at 4000 on the Startup plans. | | `max_database_num_to_throw` | `400` | Enforces a hard limit on the number of databases to protect metadata and reduce operational overhead. | | `max_database_num_to_warn` | `200` | Warns before the hard database-count limit is reached. | | `max_format_parsing_thread_pool_size` | `[3..80, depending on CPU count]` | Scales the format parsing thread pool with service CPU capacity. | | `max_partition_size_to_drop` | `0` | Removes the server-side partition-size guard for drops to avoid common operational issues. | | `max_prefixes_deserialization_thread_pool_size` | `[3..80, depending on CPU count]` | Scales the prefixes deserialization thread pool with service CPU capacity. | | `max_server_memory_usage` | `[65%..70% of host RAM, depending on service size]` | Caps ClickHouse memory use below total host RAM so the node retains memory for the operating system, caching, and service management operations. | | `max_table_size_to_drop` | `0` | Removes the server-side table-size guard for drops to avoid common operational issues. | | `memory_worker_correct_memory_tracker` | `1` | Lets the ClickHouse memory worker correct memory tracking drift. | | `prefetch_threadpool_pool_size` | `[3..80, depending on CPU count]` | Scales the prefetch thread pool with service CPU capacity. | | `prepare_system_log_tables_on_startup` | `1` | Ensures system log tables are ready immediately after startup for managed observability and diagnostics. | | `reserved_replicated_database_prefixes` | `["aiven", "endpoint_", "service_"]` | Reserves internal database name prefixes for Exoscale-managed objects. | | `series_keeper_path` | `/clickhouse/series` | Sets the Keeper path for `generateSerialID` counter nodes. | | `show_addresses_in_stack_traces` | `0` | Avoids exposing raw addresses in stack traces returned to users or logs. | | `threadpool_local_fs_reader_pool_size` | `[3..80, depending on CPU count]` | Scales local filesystem reader concurrency with service CPU capacity. | | `threadpool_remote_fs_reader_pool_size` | `[3..80, depending on CPU count]` | Scales remote filesystem reader concurrency with service CPU capacity. | | `threadpool_writer_pool_size` | `[3..80, depending on CPU count]` | Scales filesystem writer concurrency with service CPU capacity. | | `uncompressed_cache_size` | `[~7% of RAM]` | Sizes the uncompressed block cache based on the managed memory budget. | | `user_with_indirect_database_creation` | `avnadmin` | Restricts indirect database creation privileges to the managed admin user. | | `vector_similarity_index_cache_size` | `0.07` | Sizes the vector similarity index cache as a fraction of total server memory. The default is `0.07`, or 7% of RAM. Set to `0` to disable the cache. Maximum value: `0.5`. This setting can be changed in advanced configuration and applies to ClickHouse 25.8 or later. | --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-metrics-datadog/ --- # Managed ClickHouse® metrics available via Datadog Learn what metrics are available via Datadog for Managed ClickHouse® services. ## Send your metrics to Datadog Metrics are forwarded to Datadog through an external endpoint: create a Datadog external endpoint with your Datadog API key, then attach your Managed ClickHouse service to it. ```bash exo dbaas external-endpoint create datadog my-datadog --datadog-api-key DATADOG_API_KEY exo dbaas external-integration attach datadog \ --source-service-name my-clickhouse \ --destination-endpoint-id ENDPOINT_ID ``` See [Use External Integrations](https://community.exoscale.com/product/dbaas/how-to/external-integrations/index.md) for the full endpoint and integration workflow. ## Get a metrics list for your service Exoscale forwards the same metric set that the service exposes on its Prometheus endpoint, so the authoritative list for your own service is the one you scrape yourself. See [Managed ClickHouse® metrics available via Prometheus](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-metrics-prometheus/index.md) for the families of metrics collected and the command that lists them. For the metric names and semantics on the Datadog side, see the [ClickHouse integration metrics](https://docs.datadoghq.com/integrations/clickhouse/#metrics) in the Datadog documentation. > [!NOTE] > The Datadog Agent installation described in the Datadog documentation does not apply to > Managed ClickHouse: the service nodes are managed by Exoscale and are not accessible, so > metrics are forwarded by the platform through the external endpoint described above. --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-metrics-prometheus/ --- # Managed ClickHouse® metrics available via Prometheus Managed ClickHouse® services can expose their metrics on a Prometheus scrape endpoint. The endpoint is not enabled by default: you create a Prometheus external endpoint, attach your service to it, then scrape it. ## Enable the Prometheus endpoint 1. Create a Prometheus external endpoint with the credentials your Prometheus server uses: ```bash exo dbaas external-endpoint create prometheus my-prometheus \ --prometheus-basic-auth-username PROMETHEUS_USER \ --prometheus-basic-auth-password PROMETHEUS_PASSWORD ``` 1. Attach your Managed ClickHouse service to that endpoint, using the endpoint ID returned above (`exo dbaas external-endpoint list` shows it again): ```bash exo dbaas external-integration attach prometheus \ --source-service-name my-clickhouse \ --destination-endpoint-id ENDPOINT_ID ``` 1. Once the integration is active, read the scrape host and port from the service. They are published separately, and the port is not part of the service components: ```bash exo dbaas show my-clickhouse -z ch-gva-2 --output-template \ '{{ .Clickhouse.PrometheusURI.Host }}:{{ .Clickhouse.PrometheusURI.Port }}' ``` ## Scrape the endpoint The endpoint uses TLS with a certificate issued by the certificate authority of your organization's DBaaS services, and it is protected by the basic authentication credentials of the external endpoint. Retrieve the CA certificate first: ```bash exo dbaas ca-certificate -z ch-gva-2 > ca.pem ``` Use the host and port returned above as `CLICKHOUSE_HOSTNAME` and `PROMETHEUS_PORT`. The port was `9273` on the services used for this guide. Then request the metrics: ```bash curl --cacert ca.pem \ --user 'PROMETHEUS_USER:PROMETHEUS_PASSWORD' \ 'https://CLICKHOUSE_HOSTNAME:PROMETHEUS_PORT/metrics' ``` > [!IMPORTANT] > The scrape endpoint is governed by the service > [IP filter](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/secure-service/index.md): add the public > IP address of your Prometheus server to it. A scrape that hangs and then times out with no TLS > or authentication error means the scraping host is not allowed by the IP filter. The response is standard Prometheus exposition format, with one label set per service and node: ```text # HELP clickhouse_metrics_query Telegraf collected metric # TYPE clickhouse_metrics_query untyped clickhouse_metrics_query{cloud="exoscale-ch-gva-2",host="my-clickhouse-1",project="exoscale-ORGANIZATION_ID",service="my-clickhouse",service_type="clickhouse"} 6 ``` ## Exposed metrics The exposed metric set depends on the plan and the topology of your service: single-node services expose no Keeper or replication queue metrics, while multi-node services add them. The metrics come from the following families: | Family | Content | | --- | --- | | `clickhouse_*` | ClickHouse events, metrics and asynchronous metrics | | `zookeeper_*` | Keeper ensemble metrics (multi-node topologies) | | `diskio_*`, `mem_*`, `net_*`, `netstat_*`, `processes_*`, `system_*` | Host level metrics collected by the agent | Retrieve the exact list for your own service with the `curl` command above, for example: ```bash curl -s --cacert ca.pem --user 'PROMETHEUS_USER:PROMETHEUS_PASSWORD' \ 'https://CLICKHOUSE_HOSTNAME:PROMETHEUS_PORT/metrics' | grep '^# HELP' | awk '{print $3}' | sort -u ``` ## Related pages - [Managed ClickHouse® metrics available in Datadog](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-metrics-datadog/index.md) - [Use External Integrations](https://community.exoscale.com/product/dbaas/how-to/external-integrations/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-system-tables/ --- # System tables in Managed ClickHouse® Managed ClickHouse® supports multiple types of system tables, which store metadata and system-level information. Querying system tables allows you to check the configuration, performance, and state of your database. ## Supported system tables Managed ClickHouse supports [system tables available with the open-source ClickHouse](https://clickhouse.com/docs/reference/system-tables/overview), except the following: - `certificates` - `build_options` - `models` - `stack_trace` - `user_directories` - `warnings` - `zookeeper` - `dns_cache` - `symbols` - Several [system log tables](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/clickhouse-system-tables/index.md#supported-system-log-tables) ## Supported system log tables System log tables store data related to traces, queries, performance metrics, errors, and more. By recording logs and events, they allow you to monitor, debug, and audit your database. Managed ClickHouse supports [system log tables available with the open-source ClickHouse](https://clickhouse.com/docs/reference/system-tables/overview), except the following: - `asynchronous_metric_log` - `backup_log` - `blob_storage_log` - `crash_log` - `metric_log` - `opentelemetry_span_log` - `processors_profile_log` - `query_metric_log` - `query_thread_log` - `session_log` - `zookeeper_log` ### System log tables TTL In Managed ClickHouse, time-to-live (TTL) for system log tables is fixed to 1 day. This means data in system log tables is kept for 1 day before being automatically deleted. ### Persist data with materialized views You can work around the TTL of 1 day by creating a [materialized view](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/materialized-views/index.md) to save system log table data so that you can retrieve and use it later. To achieve this, create a materialized view similar to the following: ```sql CREATE MATERIALIZED VIEW query_log ENGINE = MergeTree PARTITION BY event_date ORDER BY event_time AS SELECT * FROM system.query_log; ``` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/s3-supported-file-formats/ --- # S3 table function file formats in Managed ClickHouse® The [S3 table function](https://clickhouse.com/docs/reference/functions/table-functions/s3) lets you select and insert data in S3-compatible storage. In Managed ClickHouse, the S3 table function supports the following file formats, among others: - `Arrow` - `CSV` - `JSON` - `TSV` - `Parquet` - `ORC` - `Avro` ## Related pages - [Table functions supported in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-functions/index.md) - [Run federated queries in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-database-engines/ --- # Supported database engines in Managed ClickHouse® A database engine controls how a database manages tables and metadata. It determines what happens when you list, create, or delete tables and can restrict a database to specific table engines or manage replication. This differs from [table engines](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-engines/index.md), which determine how data is stored on disk or read from external sources and exposed as virtual tables. Managed ClickHouse® supports different database engines depending on the service version. Engine availability can differ from upstream depending on features enabled in Managed ClickHouse®. > [!IMPORTANT] > Databases you create with SQL always use the `Replicated` engine: a `CREATE DATABASE` > statement with any other `ENGINE` clause is rejected with `Code: 497 ... Only Replicated > database can be created through SQL`. The engines below are the ones the service exposes, > for example for databases created by the platform, and they determine how existing > databases behave. For details about each engine, see the [ClickHouse database engines documentation](https://clickhouse.com/docs/reference/engines/database-engines). ## Supported database engines | Engine | Supported in | | ------ | ------------ | | `Atomic` | 25.3, 25.8 | | `Lazy` | 25.3, 25.8 | | `MaterializedPostgreSQL` | 25.3, 25.8 | | `Memory` | 25.3, 25.8 | | `MySQL` | 25.3, 25.8 | | `Ordinary` | 25.3, 25.8 | | `PostgreSQL` | 25.3, 25.8 | | `Replicated` | 25.3, 25.8 | --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-interfaces-drivers/ --- # Interfaces and drivers supported in Managed ClickHouse® Find out what technologies and tools you can use to interact with Managed ClickHouse®. ## Interfaces (protocols) {#clickhouse-interfaces} Managed ClickHouse® supports the following fundamental underlying interfaces (protocols): - `HTTPS` - `Native TCP` - `MySQL Interface` - `Arrow Flight` > [!NOTE] > `Arrow Flight` is available for new services running ClickHouse 25.8 LTS or later. Its endpoint, like > the endpoint of every other interface, is listed in the service components, see > [Endpoints for each interface](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-interfaces-drivers/index.md#clickhouse-endpoints). > [!NOTE] > For security reasons, TLS is required to connect to Managed ClickHouse. The following interfaces (protocols) are not supported: - `HTTP` - `gRPC` - `PostgreSQL` For the full list of interfaces and protocols supported in ClickHouse, see [Drivers and Interfaces](https://clickhouse.com/docs/concepts/features/interfaces/overview). ### Endpoints for each interface {#clickhouse-endpoints} Every interface is exposed as a component of your service: one host, with one port per interface. Ports are assigned per service, so read them from your own service instead of hardcoding them. The **Components** row of `exo dbaas show`, in the [Exoscale CLI](https://community.exoscale.com/reference/cli/exo/index.md) 1.100.0 or later, lists them: ```bash exo dbaas show my-clickhouse -z ch-gva-2 ``` | Component | Interface (protocol) | | --- | --- | | `clickhouse` | `Native TCP` over TLS, used by `clickhouse-client` | | `clickhouse_https` | `HTTPS`, used by most drivers and by `curl` | | `clickhouse_mysql` | `MySQL Interface` | | `clickhouse_arrowflight` | `Arrow Flight` | The same connection details are available through the [Exoscale API](https://community.exoscale.com/reference/api/dbaas/index.md). ## Drivers (libraries) There are a number of drivers (libraries) that use one of [the fundamental underlying interfaces supported in Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-interfaces-drivers/index.md#clickhouse-interfaces) under the hood. It's up to you to pick up a driver (library) of your choice and use it for connecting to your Managed ClickHouse service. - See [how to use different drivers (libraries) for connecting to Managed ClickHouse](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md). - For the full list of drivers and libraries that support connecting to ClickHouse, see [Drivers and Interfaces](https://clickhouse.com/docs/concepts/features/interfaces/overview). > [!NOTE] > You can connect to Managed ClickHouse with any driver that uses TLS > and one of the supported protocols. ## Related pages - [How to connect to Managed ClickHouse using different libraries](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/index.md) - [Drivers and interfaces supported in ClickHouse](https://clickhouse.com/docs/concepts/features/interfaces/overview) --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-engines/ --- # Supported table engines in Managed ClickHouse® Table engines define how data is stored and which queries a table supports. Managed ClickHouse® supports different table engines depending on the service version. Engine availability can differ from upstream depending on features enabled in Managed ClickHouse®. For details about each engine, see the [ClickHouse documentation](https://clickhouse.com/docs/reference/engines/table-engines). For database engines, see [Supported database engines](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-database-engines/index.md). > [!NOTE] > To support high availability and node replacement operations on the Exoscale > platform, some table engines are automatically remapped. Every database you > create with SQL uses the `Replicated` engine, so the remap always applies: > `MergeTree` engines are silently replaced with their corresponding > `ReplicatedMergeTree` variants, including on single-node services where there > is only one replica. ## MergeTree family | Engine | Replicated variant | Supported in | | ------ | ------------------- | ------------ | | `MergeTree` (remapped) | `ReplicatedMergeTree` | 25.3, 25.8 | | `AggregatingMergeTree` (remapped) | `ReplicatedAggregatingMergeTree` | 25.3, 25.8 | | `CoalescingMergeTree` (remapped) | `ReplicatedCoalescingMergeTree` | 25.8 | | `CollapsingMergeTree` (remapped) | `ReplicatedCollapsingMergeTree` | 25.3, 25.8 | | `GraphiteMergeTree` (remapped) | `ReplicatedGraphiteMergeTree` | 25.3, 25.8 | | `ReplacingMergeTree` (remapped) | `ReplicatedReplacingMergeTree` | 25.3, 25.8 | | `SummingMergeTree` (remapped) | `ReplicatedSummingMergeTree` | 25.3, 25.8 | | `VersionedCollapsingMergeTree` (remapped) | `ReplicatedVersionedCollapsingMergeTree` | 25.3, 25.8 | ## Integration engines | Engine | Supported in | | ------ | ------------ | | `AzureBlobStorage` | 25.3, 25.8 | | `AzureQueue` | 25.3, 25.8 | | `COSN` | 25.3, 25.8 | | `GCS` | 25.8 | | `Kafka` (cannot be created via SQL) | 25.3, 25.8 | | `MaterializedPostgreSQL` | 25.3, 25.8 | | `MySQL` | 25.3, 25.8 | | `OSS` | 25.3, 25.8 | | `PostgreSQL` | 25.3, 25.8 | | `S3` | 25.3, 25.8 | | `S3Queue` | 25.3, 25.8 | | `URL` | 25.3, 25.8 | ## Data lake engines | Engine | Supported in | | ------ | ------------ | | `DeltaLake` | 25.3, 25.8 | | `DeltaLakeAzure` | 25.8 | | `DeltaLakeS3` | 25.8 | | `Hudi` | 25.3, 25.8 | | `Iceberg` | 25.3, 25.8 | | `IcebergAzure` | 25.3, 25.8 | | `IcebergS3` | 25.3, 25.8 | ## View engines | Engine | Supported in | | ------ | ------------ | | `LiveView` | 25.3, 25.8 | | `MaterializedView` | 25.3, 25.8 | | `View` | 25.3, 25.8 | | `WindowView` | 25.3, 25.8 | ## Special-purpose engines | Engine | Supported in | | ------ | ------------ | | `Buffer` | 25.3, 25.8 | | `Dictionary` | 25.3, 25.8 | | `Distributed` | 25.3, 25.8 | | `Join` | 25.3, 25.8 | | `KeeperMap` | 25.3, 25.8 | | `Memory` | 25.3, 25.8 | | `Merge` | 25.3, 25.8 | | `Null` | 25.3, 25.8 | | `Set` | 25.3, 25.8 | ## Testing and utility engines | Engine | Supported in | | ------ | ------------ | | `FuzzJSON` | 25.3, 25.8 | | `FuzzQuery` | 25.3, 25.8 | | `GenerateRandom` | 25.3, 25.8 | | `Loop` (cannot be created via SQL, use the `loop` table function) | 25.3, 25.8 | --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/supported-table-functions/ --- # Table functions supported in Managed ClickHouse® [Table functions](https://clickhouse.com/docs/reference/functions/table-functions) can be used to construct tables, for example, in a FROM clause of a query or in an INSERT INTO TABLE FUNCTION statement. > [!NOTE] > **Sample usage of the Delta Lake table function** > ```sql > SELECT * > FROM deltaLake('s3://bucket/path/to/lake') > ``` > [!NOTE] > Occasionally, you may find specific table functions disabled for > security reasons. > [!NOTE] > Table functions that read from an external source, such as `s3` and `url`, require the > matching grants. The main service user `avnadmin` holds them by default, other users need an > explicit `GRANT`, see > [Prerequisites](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/index.md#prerequisites). Managed ClickHouse® supports the following table functions: - `azureBlobStorage` - `cluster` - `clusterAllReplicas` - `cosn` - `deltaLake` - `format` - `fuzzQuery` - `gcs` - `generateRandom` - `hudi` - `iceberg` - `input` - `loop` - `merge` - `mysql` - `null` - `numbers` - `numbers_mt` - `oss` - `postgresql` - `remoteSecure` - `s3` - `s3Cluster` - `url` - `values` - `view` - `viewExplain` - `viewIfPermitted` - `zeros` - `zeros_mt` --- url: https://community.exoscale.com/product/dbaas/service-specific/clickhouse/reference/version-lifecycle/ --- # Managed ClickHouse® version support policy Managed ClickHouse® follows the upstream ClickHouse long-term support, or LTS, release model. This helps you use stable, supported versions and plan upgrades before versions reach end of life. Managed ClickHouse versions move through defined lifecycle stages, from availability to end of life. Each stage determines whether you can create new services, receive security updates, or need to plan an upgrade. ## Upstream LTS releases Upstream ClickHouse typically releases two LTS versions each year, one in March and one in August. The March release carries the minor version `.3`, for example `25.3`, and the August release carries the minor version `.8`, for example `25.8`. Upstream LTS versions receive security updates for 12 months. ## Lifecycle stages Each Managed ClickHouse LTS version moves through the following stages: - **Limited Availability (LA):** The version is available for selected early testing. LA versions are not publicly announced. - **Early Availability (EA):** The version is stable and available to all customers. Some settings, features, and UI elements might still change. - **General Availability (GA):** The version is stable and fully available. - **End of Availability (EOA):** You can no longer create new services with the version. Exoscale doesn't guarantee security updates after EOA. EOA is announced ahead of End of Life. - **End of Life (EOL):** Exoscale removes the version from support and automatically upgrades existing services to the next supported version. ## Support policy Exoscale supports two active generally available LTS versions at a time. Exoscale announces each new EA and GA version in the [Exoscale changelog](https://changelog.exoscale.com/), together with the End of Availability and End of Life dates of the version it replaces. When a new version becomes available, new service creation stops for the oldest supported version, and that version enters EOA. After EOL, Exoscale upgrades services still running that version to the next supported version. > [!NOTE] > Not every upstream LTS release is offered on Exoscale, and Exoscale does not publish a > fixed delay between an upstream release and its availability here. The authoritative list > is always the one your organization can actually select, see > [Version lifecycle dates](#version-lifecycle-dates) below. ## Security updates Exoscale delivers security updates as maintenance updates that you apply during the [maintenance window](https://community.exoscale.com/product/dbaas/how-to/maintenance-windows/index.md). An update that addresses a critical vulnerability can be applied automatically if it is left pending. Once a version reaches EOA, Exoscale doesn't guarantee further security updates for it, which is why EOA dates are announced ahead of time in the changelog. ## Version lifecycle dates End of Availability and End of Life dates for each supported version are announced in the [Exoscale changelog](https://changelog.exoscale.com/). List the versions your organization can use at any time with `exo dbaas type show clickhouse`. ## Related pages - [Manage versions in Managed ClickHouse®](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/manage-clickhouse-versions/index.md) - [Maintenance window](https://community.exoscale.com/product/dbaas/how-to/maintenance-windows/index.md) - [Fork and restore from Managed ClickHouse® backups](https://community.exoscale.com/product/dbaas/service-specific/clickhouse/how-to/restore-backup/index.md)