Skip to main content

Manage Aiven for ClickHouse® databases and tables

Create and work with databases and tables in Aiven for ClickHouse®.

Create a database​

You can create a database either in the Aiven Console or using an SQL client such as the ClickHouse client.

  1. Log in to the Aiven Console, and select your service from the Services page.

  2. In the sidebar, click Databases and tables.

  3. Click Create database > ClickHouse database.

  4. In the Create ClickHouse database window, enter a name for your database and select Create database.

    The name of the database appears in the list of databases in the Databases and tables page. On our side, we enable necessary customizations and run secondary queries to grant access to the admin user.

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 either in the Aiven Console or using an SQL client such as the ClickHouse client:

  1. Log in to the Aiven Console, and select your service from the Services page.
  2. In the sidebar, click Databases and tables.
  3. In the Databases and tables list, find your database and click Actions > Delete database.

Create a table​

Tables can be added with an SQL query, either with the help of the web query editor or with CLI. In both cases, the SQL query looks the same. 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:

CREATE TABLE transactions.expenses (
    Title String,
    Date DateTime,
    UserID UInt64,
    Amount UInt32
)
ENGINE = ReplicatedMergeTree ORDER BY Date;

Select a table engine​

Part of the table definition includes a targeted table engine. See the full list of supported table engines in Aiven for ClickHouse.

Aiven for 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.

note

A non-replicated table, such as system.query_log, can be queried using clusterAllReplicas.

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 Aiven services. Consider granting only necessary permissions to your database users.

Run the following SQL command to remove your table:

DROP TABLE NAME_OF_YOUR_DATABASE.NAME_OF_YOUR_TABLE;