ClickHouse MySQL引擎

ClickHouse关于mysql有库引擎跟表引擎.

1 MySQL库引擎

直接映射mysql的数据库

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

随便找了一个测试的库

创建MySQL引擎数据库

 create database mysqlDB engine = MySQL('node03:3306','test','root','123456');
 
 show tables from mysqlDB;
┌─name───────────┐
│ sqooptest      │
│ sqooptest_copy │
│ test           │
└────────────────┘

select * from test;
┌─id─┬─name─────┐
│  1 │ zhangsan │
│  2 │ wangwu   │
│  3 │ lisi     │
└────┴──────────┘

insert into test values(4,'zhaoliu');

node01.hadoop.com :) select * from test;

┌─id─┬─name─────┐
│  1 │ zhangsan │
│  2 │ wangwu   │
│  3 │ lisi     │
│  4 │ zhaoliu  │
└────┴──────────┘

插入后mysql表:

MySQL库引擎中,所有的表都是MySQL表引擎,通过库引擎的方式创建的表无法进行更新/删除操作,也无法进行表的DDL操作.
表允许删除(drop)与卸载(detach),但是不会删除mysql中的表,并且删除或者卸载之后,允许通过装载(attach)再将表装载回来.

2 MySQL表引擎:

上方中的MySQL库引擎中自动创建的表都是MySQL表引擎的表.

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

host:port:Mysql地址和端口
database:数据库
table:表名
user/password:账号密码
replace_query:默认0 设置为1所有insert into' 语句变更为replace into` 语句
on_duplicate_clause:默认关闭,对应于on duplicate key 后面的语句,设置值的话replace_query必须设置为0

基于上方的test表,继续测试:
删除1创建的MySQL库引擎:

drop database mysqlDB;

创建ClickHouse中的test表:

create table default.test (id Int16,name String)
engine =MySQL('node03:3306','test','test','root','123456');

select * from default.test;
┌─id─┬─name─────┐
│  1 │ zhangsan │
│  2 │ wangwu   │
│  3 │ lisi     │
│  4 │ zhaoliu  │
└────┴──────────┘

同样也支持插入语句,下面测试replace_query
mysql中的test表 id是主键:

drop table default.test;
create table default.test (id Int16,name String)
engine =MySQL('node03:3306','test','test','root','123456',1);

insert into default.test values(4,'xiaoshiyi');
select * from default.test;
┌─id─┬─name──────┐
│  1 │ zhangsan  │
│  2 │ wangwu    │
│  3 │ lisi      │
│  4 │ xiaoshiyi │
└────┴───────────┘

以上,字段已经根据replace语句更新
on_duplicate_clause:就是on duplicate key 后面的语句

drop table default.test;
create table default.test (id Int16,name String)
engine =MySQL('node03:3306','test','test','root','123456',0,
'update name = values(name)');

 select * from default.test;
┌─id─┬─name─────┐
│  1 │ zhangsan │
│  2 │ wangwu   │
│  3 │ lisi     │
│  4 │ zhaoliu  │
└────┴──────────┘
insert into default.test values(4,'') ;
┌─id─┬─name─────┐
│  1 │ zhangsan │
│  2 │ wangwu   │
│  3 │ lisi     │
│  4 │          │
└────┴──────────┘

创建MySQL表引擎时,指定的字段可以是一部分字段中,但必须要与mysql表总的字段名一致,至于字段的类型请参考

mysql type	CH type
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
varchar,char	String

3 mysql表函数

用于直接从mysql中查询数据:

select * from mysql('node03:3306','test','test','root','123456');

┌─id─┬─name─────┐
│  1 │ zhangsan │
│  2 │ wangwu   │
│  3 │ lisi     │
│  4 │          │
└────┴──────────┘