ClickHouse-SQL语法

SQL DDL 操作

库操作

-- 创建库
-- CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = MySQL(...)|Atomic|Ordinary];
CREATE DATABASE mysql_db on cluster clickHouse_cluster_3shards_1replicas ENGINE = MySQL('192.168.7.17:3306', 'chauncy', 'meifute', 'meifute');
show create database mysql_db;

create database mycl on cluster clickHouse_cluster_3shards_1replicas ENGINE = Atomic;
create database mycl_ord on cluster clickHouse_cluster_3shards_1replicas ENGINE = Ordinary;
-- 会创建目录/var/lib/clickhouse/data/mycl


-- 删除库
-- DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster]
drop database mysql_db;
DROP DATABASE IF EXISTS mysql_db ON CLUSTER clickHouse_cluster_3shards_1replicas;

表操作

创建表

-- 创建表
    -- 第一种 分布式表
    CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
    (
        name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
        name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
        ...
    ) ENGINE = engine;
    
    -- 第二种 表结构一样的表
    CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine];
    
    -- 第三种 create select类型数据填充
    CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...;

-- 第一种方式创建表
create database IF NOT EXISTS newdb on cluster clickHouse_cluster_3shards_1replicas engine = Ordinary;
show tables in newdb;
create table if not exists newdb.t1
on cluster clickHouse_cluster_3shards_1replicas
(
id UInt8 default 0 comment '编号',
name String default '无姓名' comment '姓名',
age UInt8 default 18 comment '年龄',
gender Enum8('男'=1,'女'=0) default 1 comment '性别',
create_time DateTime default now() comment '创建时间'
)engine = MergeTree()
order by (id,name,age)
partition by toYYYYMMDD(create_time);
insert into newdb.t1(id,name,age) values(1,'chauncy1',18)(2,'chuancy2',19);
insert into newdb.t1 values
(3,'chauncy3',20,'女',toDateTime('2022-03-03 12:28:38')),
(4,'chauncy4',21,'男',toDateTime('2022-03-04 12:28:38')),
(5,'chauncy5',22,'女',toDateTime('2022-03-05 14:48:08')),
(6,'chauncy6',23,'男',toDateTime('2022-03-06 14:48:08'));
-- 查看分区
select table ,partition ,name ,active from system.parts where table = 't1';
-- 第二种方式创建表
create table if not exists t2 engine = Memory as newdb.t1;

-- 第三种方式创建表
create table if not exists t3 engine = Memory as select * from newdb.t1 where id >2;

-- 查询表t3数据
select * from t3;

查看表

SHOW TABLES;
SHOW TABLES IN default;
SHOW CREATE TABLE XXX;
desc t3;

删除表

DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]

修改表

ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...IN Partition 'xxx';
RENAME TABLE [db11.]name11 TO [db12.]name12, [db21.]name21 TO [db22.]name22, ... [ON CLUSTER cluster];

-- 添加列
--newdb 库，创建表 t1,使用MergeTree引擎
create table if not exists newdb.t1
on cluster clickHouse_cluster_3shards_1replicas
(
id UInt8 default 0 comment '编号',
name String default '无姓名' comment '姓名',
age UInt8 default 18 comment '年龄',
gender Enum8('男'=1,'女'=0) default 1 comment '性别',
create_time DateTime default now() comment '创建时间'
)engine = MergeTree()
order by (id,name,age)
partition by toYYYYMMDD(create_time);
insert into newdb.t1(id,name,age) values(1,'chauncy1',18)(2,'chuancy2',19);
insert into newdb.t1 values
(3,'chauncy3',20,'女',toDateTime('2022-03-03 12:28:38')),
(4,'chauncy4',21,'男',toDateTime('2022-03-04 12:28:38')),
(5,'chauncy5',22,'女',toDateTime('2022-03-05 14:48:08')),
(6,'chauncy6',23,'男',toDateTime('2022-03-06 14:48:08'));
-- 查看分区
select table ,partition ,name ,active from system.parts where table = 't1';
-- 查看表test1表结构
desc t1;

-- 添加表字段
alter table t1 add column hobby Array(String); -- 节点上增加字段
alter table t1 ON CLUSTER clickHouse_cluster_3shards_1replicas add column hobby Array(String);
-- 删除表字段
alter table t1 drop column hobby;
-- 清空某一列= 设置默认值 主键涉及的列无法清空
alter table t1 ON CLUSTER clickHouse_cluster_3shards_1replicas clear column gender;
-- 清空某个分区的列数据
alter table t1 
ON CLUSTER clickHouse_cluster_3shards_1replicas 
clear column gender
in partition '20220304';
-- 删除列
alter table t1 ON CLUSTER clickHouse_cluster_3shards_1replicas drop column gender;

-- 修改列类型
alter table t1 ON CLUSTER clickHouse_cluster_3shards_1replicas modify column hobby Array(String);

-- 表重命名
RENAME TABLE [db11.]name11 TO [db12.]name12, [db21.]name21 TO [db22.]name22, ... [ON CLUSTER cluster];
RENAME TABLE newdb.t1 TO newdb.t01 ON CLUSTER clickHouse_cluster_3shards_1replicas;

--查看表结构，添加字段成功
desc t1; 

分区表的DDL操作

-- ClickHouse中只有MergeTree家族引擎下的表才能分区
-- 创建分区表
create table if not exists newdb.tp1
(
id UInt8 default 0 comment '编号',
name String default '无姓名' comment '姓名',
age UInt8 default 18 comment '年龄',
gender Enum8('男'=1,'女'=0) default 1 comment '性别',
create_time DateTime default now() comment '创建时间'
)engine = MergeTree()
order by (id,name,age)
partition by toYYYYMMDD(create_time);
insert into newdb.tp1(id,name,age) values(1,'chauncy1',18)(2,'chuancy2',19);
insert into newdb.tp1 values
(3,'chauncy3',20,'女',toDateTime('2022-03-03 12:28:38')),
(4,'chauncy4',21,'男',toDateTime('2022-03-04 12:28:38')),
(5,'chauncy5',22,'女',toDateTime('2022-03-05 14:48:08')),
(6,'chauncy6',23,'男',toDateTime('2022-03-06 10:48:08')),
(7,'chauncy7',24,'女',toDateTime('2022-03-07 14:48:08')),
(8,'chauncy8',25,'女',toDateTime('2022-03-03 05:48:08')),
(9,'chauncy9',26,'男',toDateTime('2022-03-06 11:48:08')),
(10,'chauncy10',27,'女',toDateTime('2022-03-06 08:48:08'));

-- 查看分区信息
select database,table,name,partition from system.parts where table = 'tp1';
-- 合并
optimize table tp1 final;

-- 卸载分区
select * from tp1;
ALTER TABLE tp1 DETACH PARTITION '20220303';
select database,table,name,partition from system.parts where table = 'tp1';

-- 装载分区
ALTER TABLE table_name ATTACH PARTITION partition_expr;
select * from tp1;
ALTER TABLE tp1 ATTACH PARTITION '20220303';

--删除分区
ALTER TABLE table_name DROP PARTITION partition_expr;
--执行删除分区命名是直接将对应分区数据删除，不会放入detached目录。该操作会将分区标记为不活跃的，然后在大约10分钟内删除全部数据
ALTER TABLE tp1 DROP PARTITION '20220303';

-- 替换分区 从不同的表复制数据
ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1;

-- 移动分区
ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest;

-- 重置分区列
ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr;

临时表

-- 当会话结束或者链接中断时，临时表将随会话一起消失。
-- 临时表仅能够使用Memory表引擎，创建临时表时不需要指定表引擎。
-- 无法为临时表指定数据库。它是在数据库之外创建的，与会话绑定。
-- 如果临时表与另一个表名称相同，那么当在查询时没有显式的指定db的情况下，将优先使用临时表。
-- 对于分布式处理，查询中使用的临时表将被传递到远程服务器。 
   
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)
-- 注意：不需要指定表引擎，默认是Memory

视图

-- 普通视图
    -- 普通视图不存储数据，它只是一层select 查询映射，类似于表的别名或者同义词，能简化查询，
    -- 对原有表的查询性能没有增强的作用，具体性能依赖视图定义的语句，
    -- 当从视图中查询时，视图只是替换了映射的查询语句。普通视图当基表删除后不可用。
    CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT ...;

-- 物化视图 物化视图是查询结果集的一份持久化存储
    -- 更新策略: 
        -- Populate在创建视图时同步原表所有的数据 在创建物化视图的过程中同时写入的数据不能被插入物化视图
        -- 无Populate创建视图后的写入数据
    -- 物化视图不支持alter 操作
    -- 预计算逻辑+显式缓存，典型的空间换时间思路。显著提升查询的性能
    CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...;

--

普通视图

create table if not exists newdb.pt1
(
id UInt8 default 0 comment '编号',
name String default '无姓名' comment '姓名',
age UInt8 default 18 comment '年龄',
gender Enum8('男'=1,'女'=0) default 1 comment '性别',
create_time DateTime default now() comment '创建时间'
)engine = MergeTree()
order by (id,name,age)
partition by toYYYYMMDD(create_time);
insert into newdb.pt1(id,name,age) values(1,'chauncy1',18)(2,'chuancy2',19);
insert into newdb.pt1 values
(3,'chauncy3',20,'女',toDateTime('2022-03-03 12:28:38')),
(4,'chauncy4',21,'男',toDateTime('2022-03-04 12:28:38')),
(5,'chauncy5',22,'女',toDateTime('2022-03-05 14:48:08')),
(6,'chauncy6',23,'男',toDateTime('2022-03-06 10:48:08')),
(7,'chauncy7',24,'女',toDateTime('2022-03-07 14:48:08')),
(8,'chauncy8',25,'女',toDateTime('2022-03-03 05:48:08')),
(9,'chauncy9',26,'男',toDateTime('2022-03-06 11:48:08')),
(10,'chauncy10',27,'女',toDateTime('2022-03-06 08:48:08'));

-- 创建普通视图
create view tp_view as select * from newdb.pt1;

-- 删除视图
drop table newdb.pt1;

物化视图

-- 创建雾化视图
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...;
create materialized view  t_view1 engine = Log POPULATE as select * from newdb.pt1;
create materialized view  t_view2 engine = Log POPULATE as select count(*) as cnt from newdb.pt1;
insert into newdb.pt1(id,name,age) values(11,'chauncy11',28)(12,'chuancy12',29);
insert into newdb.pt1(id,name,age) values(13,'chauncy11',28)(14,'chuancy12',29);


-- 使用MergerTree引擎做物化视图
create table t_mer as newdb.pt1 engine =MergeTree() order by (id,name);
create materialized view  t_view3 to t_mer as select * from newdb.pt1;
select * from t_mer;
select * from t_view3;
insert into newdb.pt1(id,name,age) values(15,'chauncy15',18)(16,'chuancy16',19);
-- 查看分区 没有分区就是 tuple()
select database,table,name,partition from system.parts where table = 't_mer';

SQL DML 操作

-- DML操作仅适用MergeTree引擎

-- Insert 插入
    INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
    或者
    INSERT INTO [db.]table Select ...

-- update 更新
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr;
alter table t_update update age = 22 where name = '张三';

-- delete 删除
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr;
alter table t_delete delete where name = '张三';

导入导出数据

-- ClickHouse中支持多种数据格式数据导入和导出，
-- 支持格式有ORC,Parquet,Avro,Protobuf,xml,json,csv
-- 导入数据
create table t_csv (id UInt8,name String,age UInt8) engine = MergeTree order by id;
cat > /root/data.csv <<-EOF
1,chuancy1,18
2,chuancy2,19
3,chuancy3,20
4,chuancy4,21
5,chuancy5,22
EOF

-- 导入数据
clickhouse-client --format_csv_delimiter="," --query="INSERT INTO newdb.t_csv FORMAT CSV" < /root/csvdata

--导出数据
clickhouse-client --format_csv_delimiter="|" --query="select * from newdb.t_csv FORMAT CSV" > /root/result
---