大数据ClickHouse（五）：数据库引擎介绍与实例演示

2022-08-12 08:38:18 浏览数 (2)

​数据库引擎介绍与实例演示

ClickHouse中支持在创建数据库时指定引擎，目前比较常用的两种引擎为默认引擎和MySQL数据库引擎。

一、Ordinary默认数据库引擎

Ordinary就是ClickHouse中默认引擎，如果不指定数据库引擎创建的就是Ordinary数据库引擎，在这种数据库下面可以使用任意表引擎。创建时需要注意，Ordinary首字母需要大写，不然会抛出异常。

  • 示例：
代码语言：javascript复制
#在ClickHouse中创建数据库test1,指定数据库引擎为Ordinary
node1 :) create database test1 engine = Ordinary;

CREATE DATABASE test1
ENGINE = Ordinary
Ok.
0 rows in set. Elapsed: 0.013 sec. 

#查看test1数据库建库信息，显示数据库引擎为Ordinary
node1 :) show create database test1;

SHOW CREATE DATABASE test1
┌─statement──────────────────────────────┐
│ CREATE DATABASE test1                  │
│ ENGINE = Ordinary                      │
└────────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec. 

#在ClickHouse中创建数据库test2，这里默认不指定任何数据库引擎
node1 :) create database test2;

CREATE DATABASE test2
Ok.
0 rows in set. Elapsed: 0.003 sec. 

#查看test2数据库建库信息，显示数据库引擎为Ordinary
node1 :) show create database test2;

SHOW CREATE DATABASE test2
┌─statement─────────────────────────────┐
│ CREATE DATABASE test2                 │
│ ENGINE = Ordinary                     │
└───────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.

二、MySQL数据库引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许对表进行INSERT插入和SELECT查询，方便在ClickHouse与MySQL之间进行数据交换。这里不会将MySQL的数据同步到ClickHouse中，ClickHouse就像一个壳子，可以将MySQL的表映射成ClickHouse表，使用ClickHouse查询MySQL中的数据，在MySQL中进行的CRUD操作，可以同时映射到ClickHouse中。

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作，但是不允许创建表、修改表、删除数据、重命名操作。

ClickHouse中创建库使用MySQL引擎语法如下：

代码语言：javascript复制
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

以上参数解释：

  • host:port — 链接的MySQL地址。
  • database — 链接的MySQL数据库。
  • user — 链接的MySQL用户。
  • password — 链接的MySQL用户密码。

注意：“MySQL”引擎的写法严格区分大小写。

在ClickHouse中使用MySQL引擎建库，将MySQL库中数据映射到ClickHouse中，mysql库中表字段类型与ClickHouse表字段类型的映射如下，这里每种类型在ClickHouse中都支持Nullable,即可空。

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

注意：

  • 在Mysql中UNSIGNED 表示无符号，就是没有负数。
  • TINYINT代表有符号的范围是-128-127，无符号的范围是从0到255的整型数据，占位大小为1字节。
  • SMALLINT:一个小整数。有符号的范围是-2^15(-32,768) 到 2^15-1(32,767)的整型数据，无符号的范围是0到65535，占位大小为2个字节。
  • MEDIUMINT：一个中等大小整数，有符号的范围是-8388608到8388607，无符号的范围是0到16777215，占位大小为3个字节。
  • INT:一个正常大小整数。有符号的范围是-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据（所有数字），无符号的范围是0到4294967295,占位大小为 4 个字节。

示例：ClickHouse映射MySQL中的表

代码语言：javascript复制
#登录mysql 在mysql中创建test数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

#在mysql test库中新建表 mysql_table
mysql> use test;
mysql> create table mysql_table(id int ,name varchar(255));

#向mysql表 mysql_table中插入两条数据
mysql> insert into mysql_table values (1,"zs"),(2,"ls");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

#在ClickHouse中创建mysql引擎的数据库，与MySQL交换数据
node1 :) CREATE DATABASE mysql_db ENGINE = MySQL('node2:3306', 'test', 'root', '123456');

CREATE DATABASE mysql_db
ENGINE = MySQL('node2:3306', 'test', 'root', '123456')
Ok.
0 rows in set. Elapsed: 0.032 sec. 

#在ClickHouse中使用mysql_db库，并展示表，看是否映射MySQL中的表
node1 :) use mysql_db;
node1 :) show tables;

SHOW TABLES
┌─name────────┐
│ mysql_table │
└─────────────┘
1 rows in set. Elapsed: 0.005 sec. 

#在ClickHouse中查询表mysql_table
node1 :) select * from mysql_table;

SELECT *
FROM mysql_table
┌─id─┬─name─┐
│  1 │ zs   │
│  2 │ ls   │
└────┴──────┘
2 rows in set. Elapsed: 0.012 sec. 

#在ClickHouse中查看表 mysql_table的描述，发现映射成ClickHouse中的字段类型。
node1 :) desc mysql_table;

DESCRIBE TABLE mysql_table
┌─name─┬─type────────────┬
│ id   │ Nullable(Int32) │
│ name │ Nullable(String)│
└──────┴─────────────────┴
2 rows in set. Elapsed: 0.005 sec.

示例：在MySQL对应的表中插入删除数据，对应的在ClickHouse中也能插入和删除的数据

代码语言：javascript复制
#在MySQL中 test库下的mysql_table中插入和删除一条数据
mysql> insert into mysql_table values (3,"ww");
Query OK, 1 row affected (0.01 sec)

mysql> delete from mysql_table where id = 1;
Query OK, 1 row affected (0.01 sec)

#在ClickHouse中 mysql_db库下查询表mysql_table
node1 :) select * from mysql_table;

SELECT *
FROM mysql_table
┌─id─┬─name─┐
│  2  │ ls  │
│  3  │ ww  │
└────┴──────┘
2 rows in set. Elapsed: 0.005 sec.

示例：在MySQL对应的库test下创建新的表a，ClickHouse中也可以展示

代码语言：javascript复制
#在MySQL中 test库下创建库a,并插入数据
mysql> create table a (id int,name varchar(255),age int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into a values (1,"zhangsan",18),(2,"lisi",19);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

#在ClickHouse中 mysql_db库下查询表a是否存在，同时查看数据
node1 :) show tables;

SHOW TABLES
┌─name────────┐
│ a           │
│ mysql_table │
└─────────────┘
2 rows in set. Elapsed: 0.005 sec. 

node1 :) select * from a;

SELECT *
FROM a
┌─id─┬─name─────┬─age─┐
│  1 │ zhangsan │  18 │
│  2 │ lisi     │  19 │
└────┴──────────┴─────┘
2 rows in set. Elapsed: 0.025 sec.

示例：在ClickHouse中向表a中插入数据，可以在msyql中查询到。但是ClickHouse中不支持创建表和删除数据操作。

代码语言：javascript复制
#在ClickHouse中向表a中插入数据
node1 :) insert into a values(3,'wangwu',20);

#在MySQL中查询表a数据
mysql> select * from a;
 ------ ---------- ------ 
| id    | name      | age  |
 ------ ---------- ------ 
|    1  | zhangsan  |   18 |
|    2  | lisi      |   19 |
|    3  | wangwu    |   20 |
 ------ ---------- ------ 
3 rows in set (0.00 sec)
云数据仓库ClickHouse

0 人点赞