Manage Managed ClickHouse® databases and tables

Create and work with databases and tables in Managed ClickHouse®.

Create a database

You can create a database using an SQL client such as the ClickHouse client.

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:

CREATE DATABASE DATABASE_NAME
ENGINE = Replicated

For example:

CREATE DATABASE transactions
ENGINE = Replicated;

Delete a database

You can delete a database using an SQL client such as the ClickHouse client:

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:

DROP DATABASE DATABASE_NAME

For example:

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:

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:

s_5e98d    my-clickhouse-1.exoscale-<uuid>.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 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:

ENGINE = ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
ORDER BY Date
SETTINGS index_granularity = 8192

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 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;