Database Engines
Lazy Engine
During the interval of expiration_time_in_seconds since the last access, the table is stored in memory. It is only applicable to &Log engine tables. Due to the long interval of access to such tables, it optimizes the storage of a large number of *Log engine tables. Create a database
CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);
Atomic
Supports non-blocking drop and rename table queries and atomic swap table t1 and t2 queries. By default, the Atomic database engine is used. Create a database
CREATE DATABASE test ENGINE = Atomic
MySQL
The MySQL engine maps tables from a remote MySQL server into ClickHouse and allows for insert and select queries on the tables, facilitating data exchange between ClickHouse and MySQL. The MySQL database engine converts queries into MySQL commands and sends them to the MySQL server, enabling operations like show tables or show create table. Operations that cannot be performed (rename, create table, alter) Create a database
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] ENGINE = MySQL('host:port',['database'|database],'user','password')
MySQL database engine parameters:
- host:port — MySQL connection address
- database — MySQL database to connect to
- user — MySQL user to connect with
- password — Password for the MySQL user
The mapping between MySQL data types and ClickHouse types is shown in the table below:
MySQL | ClickHouse |
---|---|
UNSIGNED TINYINT | UInt8 |
TINYINT | Int8 |
UNSIGNED SMALLINT | UInt16 |
SMALLINT | Int16 |
UNSIGNED INT, UNSIGNED MEDIUMINT | UInt32 |
INT, MEDIUMINT | Int32 |
UNSIGNED BIGINT | UInt64 |
BIGINT | Int64 |
FLOAT | Float32 |
DOUBLE | Float64 |
DATE | Date |
DATETIME, TIMESTAMP | DateTime |
BINARY | FixedString |