Databases

Elena Dvoryadkina

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 the Default 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.

Found a mistake? Seleсt text and press Ctrl+Enter to report it