# 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](/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/).

> [!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](/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/):

**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-<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](/product/dbaas/service-specific/clickhouse/reference/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:

```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`](/product/dbaas/service-specific/clickhouse/how-to/query-databases/#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](/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/) 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;
```