ClickHouse不支持事务。
ClickHouse提供了多种表引擎，例如MergeTree系列引擎、MaterializedView引擎、Dictionary引擎、Distributed引擎等。ClickHouse还提供了丰富的数据类型和函数，例如数组、Map和嵌套数据结构、近似计算、bitmap、高阶函数和URI函数等。
一、建库与数据库引擎
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]
默认情况下，ClickHouse使用自己的database engine. 还有一个lazy引擎
1、MySQL
MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换
MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。
但您无法对其执行以下操作：RENAME、CREATE TABLE、ALTER
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
db01 :) create database to_mysql engine=MySQL('172.16.220.10:3306','test','cy','P@ssw0rd');
CREATE DATABASE to_mysql
ENGINE = MySQL('172.16.220.10:3306', 'test', 'cy', 'P@ssw0rd')
Query id: 40b29856-bb0d-4329-9568-d775faa3493c
Ok.
0 rows in set. Elapsed: 0.010 sec.
db01 :) use to_mysql;
USE to_mysql
Query id: bca71829-9169-4428-bf44-159621b44900
Ok.
0 rows in set. Elapsed: 0.001 sec.
db01 :) show tables;
SHOW TABLES
Query id: ce48eb19-b4df-4c18-857d-79900479937e
┌─name─┐
│ t1 │
└──────┘
1 row in set. Elapsed: 0.005 sec.
db01 :) select * from t1;
SELECT *
FROM t1
Query id: 5f1ae65c-4a6f-44ab-a4e9-9f7a50178d23
┌─id─┬─name─┐
│ 3 │ wz │
│ 4 │ szs │
│ 1 │ yz │
│ 6 │ gy │
│ 7 │ yn │
│ 8 │ yc │
│ 5 │ ww │
└────┴──────┘
7 rows in set. Elapsed: 0.011 sec.
mysql端查看
(cy@localhost)[test]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 24 | cy | localhost | test | Query | 0 | init | show processlist |
| 25 | cy | db01:9302 | test | Sleep | 51 | | NULL |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
db01 :) select @@version;
SELECT globalVariable('version') AS `@@version`
Query id: e1a57ffc-d025-433e-a685-560df4b3fe1d
┌─@@version─┐
│ 5.7.30 │
└───────────┘
1 row in set. Elapsed: 0.002 sec.
2、MaterializeMySQL
创建ClickHouse数据库，包含MySQL中所有的表，以及这些表中的所有数据。
ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询。
这是一个实验性的特性，不应该在生产中使用.
1、语法
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
[TABLE OVERRIDE table1 (...), TABLE OVERRIDE table2 (...)]
- max_rows_in_buffer — 允许在内存中缓存数据的最大行数(对于单个表和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值:65 505。
- max_bytes_in_buffer - 允许在内存中缓存数据的最大字节数(对于单个表和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 1 048 576 。
- max_rows_in_buffers - 允许在内存中缓存数据的最大行数(用于数据库和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 65 505。
- max_bytes_in_buffers - 允许在内存中缓存数据的最大字节数(用于数据库和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 1 048 576。
- max_flush_data_time - 允许数据在内存中缓存的最大毫秒数(对于数据库和无法查询的缓存数据)。当超过这个时间，数据将被物化。默认值: 1000。
- max_wait_time_when_mysql_unavailable - MySQL不可用时的重试间隔(毫秒)。负值禁用重试。默认值:1000。
- allows_query_when_mysql_lost 允许在MySQL丢失时查询物化表。默认值:0(false)。
2、服务器端配置
mysql配置
default_authentication_plugin = mysql_native_password
gtid_mode = on
enforce_gtid_consistency = on
clickhouse配置
set allow_experimental_database_materialized_mysql = 1;
3、兼容性
mysql每张表应该有主键
MySQL DDL 语句会被转换成对应的ClickHouse DDL 语句，比如： (ALTER, CREATE, DROP, RENAME). 如果ClickHouse 无法解析某些语句DDL 操作，则会跳过。
4、测试
db01 :) create database mv_mysql engine=MaterializedMySQL('172.16.220.10:3306','test','cy','P@ssw0rd') settings allows_query_when_mysql_lost=true;
CREATE DATABASE mvmysql
ENGINE = MaterializedMySQL('172.16.220.10:3306', 'test', 'cy', 'P@ssw0rd')
SETTINGS allows_query_when_mysql_lost = 1
Query id: f2aed20a-fe69-431b-9348-65bd7876c98e
Ok.
0 rows in set. Elapsed: 0.019 sec.
db01 :) show tables;
SHOW TABLES
Query id: 0d5c1190-8c82-4ca5-ad25-eb37d95d2ebb
┌─name─┐
│ t1 │
└──────┘
1 row in set. Elapsed: 0.002 sec.
3、PostgreSQL
允许连接到远程PostgreSQL服务。支持读写操作(SELECT和INSERT查询)，以在ClickHouse和PostgreSQL之间交换数据。
在SHOW TABLES和DESCRIBE TABLE查询的帮助下，从远程PostgreSQL实时访问表列表和表结构。
支持表结构修改(ALTER TABLE … ADD|DROP COLUMN)。如果use_table_cache参数(参见下面的引擎参数)设置为1，则会缓存表结构，不会检查是否被修改，但可以用DETACH和ATTACH查询进行更新。
1、语法
CREATE DATABASE test_database
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `use_table_cache`]);
2、测试
注意：官方文档此处隐藏的一个参数是schema
CREATE DATABASE to_postgres
ENGINE = PostgreSQL('172.16.220.10:5432', 'test', 'cy', 'P@ssw0rd', 'public');
db01 :) show tables;
SHOW TABLES
Query id: 992261c0-e3f1-40d9-9a1a-036ad9693560
Ok.
0 rows in set. Elapsed: 0.012 sec.
db01 :) select * from t1;
SELECT *
FROM t1
Query id: 9bb80bfd-5191-40c2-a45e-2c19d6b3c864
Elapsed: 0.004 sec.
Received exception from server (version 23.12.2):
Code: 36. DB::Exception: Received from localhost:9000. DB::Exception: PostgreSQL array cannot be NULL: "public"."t1"."enumvals": while fetching postgresql table structure. (BAD_ARGUMENTS)
不能为null，有点鬼
db01 :) show tables;
SHOW TABLES
Query id: 94e16b36-e50d-4849-aa72-a24e4de30748
┌─name─┐
│ t1 │
└──────┘
1 row in set. Elapsed: 0.007 sec.
4、MaterializedPostgreSQL
依然是实验功能，不要投入生产
使用PostgreSQL数据库表的初始数据转储创建ClickHouse数据库，并启动复制过程，即执行后台作业，以便在远程PostgreSQL数据库中的PostgreSQL数据库表上发生新更改时应用这些更改。
ClickHouse服务器作为PostgreSQL副本工作。它读取WAL并执行DML查询。DDL不是复制的，但可以处理
1、语法
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
2、配置参数
materialized_postgresql_max_block_size
materialized_postgresql_tables_list
materialized_postgresql_allow_automatic_update
3、必备条件
在postgresql配置文件中将
postgresql.conf
wal_level=logical
max_replication_slots=2。
每个复制表必须具有以下一个replica identity:default (主键)、唯一索引
4、测试
db01 :) CREATE DATABASE mv_postgres
ENGINE = MaterializedPostgreSQL('172.16.220.10:5432', 'test', 'cy', 'P@ssw0rd')
SETTINGS materialized_postgresql_max_block_size = 65536,
materialized_postgresql_tables_list = 't1';
CREATE DATABASE mv_postgres
ENGINE = MaterializedPostgreSQL('172.16.220.10:5432', 'test', 'cy', 'P@ssw0rd')
SETTINGS materialized_postgresql_max_block_size = 65536, materialized_postgresql_tables_list = 't1'
Query id: bc7550be-7654-4650-a9c0-18941b3b5d7e
Ok.
0 rows in set. Elapsed: 0.003 sec.
db01 :) use mv_postgres;
USE mv_postgres
Query id: 0614ffdc-1ac4-475b-a79c-3700c268a49a
Ok.
0 rows in set. Elapsed: 0.001 sec.
db01 :) show tables;
SHOW TABLES
Query id: d2c36151-594c-4c93-baf7-9aababa0e47c
┌─name─┐
│ t1 │
└──────┘
1 row in set. Elapsed: 0.002 sec.
5、Lazy
在最后一次访问之后，只在RAM中保存expiration_time_in_seconds秒。只能用于*Log表。
它是为存储许多小的*Log表而优化的，对于这些表，访问之间有很长的时间间隔
CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);
6、Atomic
它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。默认情况下使用Atomic数据库引擎。
数据库Atomic中的所有表都有唯一的UUID，并将数据存储在目录/clickhouse_path/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy是该表的UUID。
通常，UUID是自动生成的，但用户也可以在创建表时以相同的方式显式指定UUID(不建议这样做)。可以使用 show_table_uuid_in_table_create_query_if_not_nil设置。显示UUID的使用SHOW CREATE查询
- RENAME TABLES
RENAME查询是在不更改UUID和移动表数据的情况下执行的。这些查询不会等待使用表的查询完成，而是会立即执行。
RENAME TABLE new_table TO tmp, old_table TO new_table, tmp TO old_table;
- DROP/DETACH TABLES
在DROP TABLE上，不删除任何数据，数据库Atomic只是通过将元数据移动到/clickhouse_path/metadata_dropped/将表标记为已删除，并通知后台线程。最终表数据删除前的延迟由database_atomic_delay_before_drop_table_sec设置指定。
- EXCHANGE TABLES
EXCHANGE以原子方式交换表。因此，不是这种非原子操作：
RENAME TABLE new_table TO tmp, old_table TO new_table, tmp TO old_table;
EXCHANGE TABLES new_table AND old_table;
7、SQLite
允许连接到SQLite数据库，并支持ClickHouse和SQLite交换数据， 执行 INSERT 和 SELECT 查询。
CREATE DATABASE sqlite_database
ENGINE = SQLite('db_path')
SQLite将整个数据库(定义、表、索引和数据本身)存储为主机上的单个跨平台文件。在写入过程中，SQLite会锁定整个数据库文件，因此写入操作是顺序执行的。读操作可以是多任务的。 SQLite不需要服务管理(如启动脚本)或基于GRANT和密码的访问控制。访问控制是通过授予数据库文件本身的文件系统权限来处理的。
8、Replicated
实验功能
该引擎基于Atomic引擎。它支持通过将DDL日志写入ZooKeeper并在给定数据库的所有副本上执行的元数据复制。
一个ClickHouse服务器可以同时运行和更新多个复制的数据库。但是同一个复制的数据库不能有多个副本。
CREATE DATABASE testdb ENGINE = Replicated('zoo_path', 'shard_name', 'replica_name') [SETTINGS ...]
