Databases
Overview
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 (*_log
tables); -
INFORMATION_SCHEMA
(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 theDefault database
configuration parameter of the ADQMDB service).
To get a list of all databases on the ADQM server, use the SHOW DATABASES query:
SHOW DATABASES;
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.
Atomic specifics
Table UUID
Each table in an Atomic database has UUID. UUID format — xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy
.
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.
RENAME TABLE
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.
EXCHANGE TABLES/DICTIONARIES
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;
DROP/DETACH 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).
The 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 DROP
and DETACH
queries). In this case, a table will be deleted immediately after the completion of SELECT
, INSERT
and other queries that use the table.