ADQM supports a set of database engines optimized for specific tasks:
Atomic — the main database engine that ADQM uses by default (see the Atomic specifics section below);
MySQL — engine that allows connecting to databases on a remote MySQL server;
MaterializedMySQL — experimental engine that allows replicating a MySQL database to ADQM;
PostgreSQL — engine that allows connecting to databases on a remote PostgreSQL server;
MaterializedPostgreSQL — experimental engine that allows replicating a PostgreSQL database to ADQM;
SQLite — engine that allows connecting to SQLite databases;
Lazy — in-memory database engine for working with Log tables;
Replicated — experimental engine that is based on Atomic and supports replication of an entire database.
For detailed information on all database engines, see the ClickHouse documentation.
ADQM contains the following databases by default:
system— a database with system tables that contain information about server states, processes, and environment, as well as system logs (
information_schema) — a system database containing views that you can use to get metadata of database objects;
default— an Atomic database that is set by default during ADQM installation (via the
Default databaseconfiguration parameter of the ADQMDB service).
To get a list of all databases on the ADQM server, use the SHOW DATABASES query:
For example, the output can look like:
--name---------------- │ INFORMATION_SCHEMA │ │ default │ │ information_schema │ │ system │ │ test_database │ ----------------------
Create a database
To create a database, use the CREATE DATABASE query:
CREATE DATABASE <database_name> [ENGINE = <database_engine>];
If you skip the
ENGINE parameter, an Atomic database is created. Specifics of the Atomic database engine are listed below.
Each table in an Atomic database has UUID. UUID format —
A table stores data in the /var/lib/clickhouse/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy directory, to which the following link refers: /var/lib/clickhouse/data/<database_name>/<table_name>, where <database_name> and <table_name> are database and table names.
As a table name is a link in the file system, the
RENAME TABLE and
DROP TABLE queries are quick and non-blocking. The
EXCHANGE TABLES and
EXCHANGE DICTIONARIES atomic operations are also available.
The RENAME TABLE query renames a table. It is executed without changing UUID or moving table data. This query does not wait for queries that use the table to complete and is executed instantly — when a new name of a table is specified, the corresponding new link to a directory with table data appears, and the old link is then deleted.
The EXCHANGE query exchanges the names of two tables or two dictionaries atomically (a dictionary is a key/value data store that is fully or partially stored in the RAM of the ADQM server and can be used as a reference to substitute data values by keys in the final sample).
For example, you can use a single atomic query:
EXCHANGE TABLES new_table AND old_table;
instead of the following non-atomic operation:
RENAME TABLE new_table TO tmp, old_table TO new_table, tmp TO old_table;
You can use the DROP TABLE query to delete a table or the DETACH TABLE query to detach it (make the table "invisible" to the server).
DROP TABLE query does not remove data immediately. An Atomic database marks a table as dropped (it cannot receive new queries), moves metadata to the /var/lib/clickhouse/metadata_dropped/ directory, and notifies the background thread about this. The database_atomic_delay_before_drop_table_sec setting defines a delay before the final deletion of data.
You can use the
SYNC modifier to enable the synchronous mode (the database_atomic_wait_for_drop_and_detach_synchronously setting adds the
SYNC modifier to all
DETACH queries). In this case, a table will be deleted immediately after the completion of
INSERT and other queries that use the table.