Clickhouse数据库、创建数据表和定义字段默认值、临时表、视图

1. 数据库

语法：

• create database if not exists db_name engine = engine_name on cluster cluster_name
• drop database if exists db_name on cluster cluster_name

数据库引擎如下所示：

• Ordinary：默认引擎，此引擎数据库可以使用任何表引擎
• Dictionary：字典引擎，此引擎数据库会自动为所有数据字典创建数据表(todo)
• Memory: 内存引擎，此引擎数据库所有数据保存再内存中，服务重启，数据被清除
  -Lazy: 日志引擎：此引擎数据库的表，只能使用日志表引擎
• MySQL: MySQL引擎

clickhouse1 :) 
clickhouse1 :) create database mysql_test_db on cluster sharding_cluster
:-] engine = MySQL('clickhouse1:3306', 'test_db', 'root', 'Root_123');

CREATE DATABASE mysql_test_db ON CLUSTER sharding_cluster
ENGINE = MySQL('clickhouse1:3306', 'test_db', 'root', 'Root_123')

Query id: 62fc1f6d-122e-4077-8797-9413865a4330

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse1 │ 9000 │      0 │       │                   3 │                1 │
│ clickhouse3 │ 9000 │      0 │       │                   2 │                1 │
│ clickhouse4 │ 9000 │      0 │       │                   1 │                1 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.209 sec. 

clickhouse1 :) 

此引擎数据库将mysql中test_db数据库中的表，自动在clickhouse的mysql_test_db数据库下创建对应的MySQL引擎表

mysql中test_db数据库新增表、删除表, 表添加字段、删除字段、修改字段、insert、delete、update，clickhouse的mysql_test_db数据库都会自动相应的变化

2. 创建数据表和定义字段默认值

方式一：

clickhouse1 :) 
clickhouse1 :) create table if not exists default.table_test1(
:-] id UInt64 alias (rand() + 1),
:-] name String default city,
:-] city String materialized concat('city_', toString(rand()))
:-] ) engine = Memory();
clickhouse1 :) 

• 字段定义了默认值，则该字段可以不定义数据类型, 会从类型推断而来
• 字段定义了materialize或alias形式的默认值，该字段不能被insert, 只能靠计算得到值；且该字段不能被select *查询到
• 字段定义了alias形式的默认值，该字段数据不会保存到磁盘
• TinyLog、Memory表引擎完全不支持modify column操作

方式二：

clickhouse1 :) 
clickhouse1 :) create table if not exists default.table_test2 as default.table_test1
:-] engine = Log;
clickhouse1 :) 

• 表引擎默认和源表的表引擎一样

方式三：

clickhouse1 :) 
clickhouse1 :) create table if not exists default.table_test3 engine = TinyLog
:-] as select id, name from default.table_test1;
clickhouse1 :) 
clickhouse1 :) desc table_test3;
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ UInt64 │              │                    │         │                  │                │
│ name │ String │              │                    │         │                  │                │
│ city │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
clickhouse1 :) 
clickhouse1 :) drop table if exists default.table_test3;
clickhouse1 :) 

• 新表字段只有select查询到的字段，且没有字段的默认值设置
• select查询到的数据也会保存到新表

3. 临时表

todo

4. 视图

4.1 普通视图

clickhouse1 :) 
clickhouse1 :) create table data_table_local on cluster sharding_cluster(
:-] id UInt8,
:-] name String,
:-] city String
:-] ) engine = StripeLog;
clickhouse1 :) 
clickhouse1 :) create table data_table_all on cluster sharding_cluster as data_table_local
:-] engine = Distributed(sharding_cluster, default, data_table_local, id);
clickhouse1 :)
clickhouse1 :) insert into data_table_all(id, name, city) values(1, 'name1', 'Beijing');
clickhouse1 :)
clickhouse1 :) create view if not exists default.view_table_all on cluster sharding_cluster
:-] as select * from data_table_all;

CREATE VIEW IF NOT EXISTS default.view_table_all ON CLUSTER sharding_cluster AS
SELECT *
FROM data_table_all

Query id: 70daf3f9-884f-408e-b1d3-edd52a96a6b2

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   3 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.125 sec. 

clickhouse1 :) 
clickhouse1 :) select * from view_table_all;
┌─id─┬─name──┬─city────┐
│  1 │ name1 │ Beijing │
└────┴───────┴─────────┘
clickhouse1 :) 

• view_table_all表只是一层查询代理，没有表引擎，不储存数据

4.2 物化视图

• 物化视图储存数据
• select的源表，发生insert, 物化视图会同步；发生delete、update，物化视图不会同步
• 物化视图本质是一张表

方式一：to db.table_name

1. 创建源表

clickhouse1 :) 
clickhouse1 :) create table source_table_local on cluster sharding_ha(
:-] id UInt64,
:-] name String,
:-] city String
:-] ) engine = ReplicatedMergeTree('/clickhouse/tables/source_table/{shard}', '{replica}')
:-] order by id;
clickhouse1 :) 
clickhouse1 :) create table source_table_all on cluster sharding_ha as source_table_local
:-] engine = Distributed(sharding_ha, default, source_table_local, id);
clickhouse1 :) 

2. 创建目标表

clickhouse1 :) 
clickhouse1 :) create table target_table_local on cluster sharding_cluster(
:-] id UInt64,
:-] name String,
:-] city String
:-] ) engine = MergeTree()
:-] order by id;
clickhouse1 :) 
clickhouse1 :) create table target_table_all on cluster sharding_cluster as target_table_local
:-] engine = Distributed(sharding_cluster, default, target_table_local, id);
clickhouse1 :) 

3. 创建物化视图

clickhouse1 :) 
clickhouse1 :) create materialized view if not exists default.view_table_local to default.target_table_all as select * from source_table_all;

CREATE MATERIALIZED VIEW IF NOT EXISTS default.view_table_local TO default.target_table_all AS
SELECT *
FROM source_table_all

Query id: c2a7b492-37d6-420a-9c98-dd73fb4ed081

Ok.

0 rows in set. Elapsed: 0.003 sec. 

clickhouse1 :) 

4. 往源表插入数据

clickhouse1 :)
clickhouse1 :) insert into source_table_all(id, name, city) values(1, 'name1', 'Beijing');
clickhouse1 :) 

5. 查询物化视图和目标表

clickhouse1 :)
clickhouse1 :) select * from view_table_local;
┌─id─┬─name──┬─city────┐
│  1 │ name1 │ Beijing │
└────┴───────┴─────────┘
clickhouse1 :) 
clickhouse1 :) select * from target_table_all;
┌─id─┬─name──┬─city────┐
│  1 │ name1 │ Beijing │
└────┴───────┴─────────┘
clickhouse1 :) 

• 不支持表引擎
• 不支持populate

方式二：表引擎

1. 创建源表，并向源表插入数据

clickhouse1 :) 
clickhouse1 :) create table source_table_local on cluster sharding_ha(
:-] id UInt64,
:-] name String,
:-] city String
:-] ) engine = ReplicatedMergeTree('/clickhouse/tables/source_table/{shard}', '{replica}')
:-] order by id;
clickhouse1 :) 
clickhouse1 :) create table source_table_all on cluster sharding_ha as source_table_local
:-] engine = Distributed(sharding_ha, default, source_table_local, id);
clickhouse1 :) 
clickhouse1 :) insert into source_table_all(id, name, city) values(1, 'name1', 'Beijing');
clickhouse1 :) 

2. 创建分布式的本地表，用于储存物化视图的数据

clickhouse1 :) 
clickhouse1 :) create table view_data_local on cluster sharding_cluster(
:-] id UInt64,
:-] name String,
:-] city String
:-] ) engine = MergeTree()
:-] order by id;
clickhouse1 :) 

3. 创建物化视图，并查询物化视图

clickhouse1 :) 
clickhouse1 :) create materialized view if not exists default.view_table_local engine = Distributed(sharding_cluster, default, view_data_local, id) populate as select * from source_table_all;

CREATE MATERIALIZED VIEW IF NOT EXISTS default.view_table_local
ENGINE = Distributed(sharding_cluster, default, view_data_local, id) POPULATE AS
SELECT *
FROM source_table_all

Query id: 12c55999-feaf-44e5-b73b-1cdc4d0b0161

Ok.

0 rows in set. Elapsed: 0.014 sec. 

clickhouse1 :) 
clickhouse1 :) select * from view_table_local;
┌─id─┬─name──┬─city────┐
│  1 │ name1 │ Beijing │
└────┴───────┴─────────┘
clickhouse1 :) 
clickhouse1 :) 
clickhouse1 :) show tables;
┌─name──────────────────── ───────┐
│ ......省略部分....               │
│ view_table_local                │
│ ......省略部分....               │
└─────────────────────────────────┘
clickhouse1 :)

4. 删除物化视图

clickhouse1 :)
clickhouse1 :) drop table view_table_local;
clickhouse1 :)

• populate：创建物化视图的时候，会将select查询的数据插入新表；不指定则不会将select查询的数据插入新表