导图社区 ClickHouse代码语法知识点自学

ClickHouse代码语法知识点自学

ClickHouse代码语法知识点自学：SQL语法：INSERT INTO、DESCRIBE TABLE、CHECK TABLE；案例：从HDFS导入数据。

编辑于2022-11-10 17:48:39 广东

代码语法

凝

他的近期作品查看更多>>

ClickHouse代码语法知识点自学

社区模板帮助中心，点此进入>>

凝

他的近期作品查看更多>>

相似推荐
大纲

论语孔子简单思维导图
- 88.9k
- 943
- 1.1k
- 487
- 1
MindMaster
《傅雷家书》思维导图
- 134.4k
- 1.7k
- 2.7k
- 1.3k
- 0
MindMaster
《童年》读书笔记
- 45.8k
- 488
- 986
- 336
- 0
MindMaster
《茶馆》思维导图
- 13.0k
- 175
- 181
- 40
- 0
MindMaster
《朝花夕拾》篇目思维导图
- 26.2k
- 532
- 1.2k
- 300
- 0
MindMaster
《昆虫记》思维导图
- 34.1k
- 272
- 778
- 277
- 0
MindMaster
《安徒生童话》思维导图
- 19.0k
- 276
- 264
- 66
- 0
MindMaster
《鲁滨逊漂流记》读书笔记
- 22.0k
- 310
- 550
- 166
- 0
MindMaster
《这样读书就够了》读书笔记
- 98.6k
- 12.9k
- 9.0k
- 2.2k
- 0
Ethan
妈妈必读：一张0-1岁孩子认知发展的精确时间表
- 10.7k
- 1.7k
- 409
- 39
- 0
Ethan

ClickHouse代码语法知识点自学

ClickHouse概述

定义：列式存储数据库（DBMS），主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告

数据类型

整型

Int8(byte)

Int16(short)

Int32（int）

Int64（long）

浮点型

Float32(float)

Float64(double)

布尔型

无（boolean)

枚举类

Enum8，Enum16

'String' = Int8

'String' = int16

使用实例

CREATE TABLE t_enum

(

x Enum8('hello' = 1, 'world' = 2)

)

ENGINE = TinyLog

INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')

数组

array（T）

SELECT array(1, 2) AS x, toTypeName(x)

SELECT [1, 2] AS x, toTypeName(x)

元组

元组，其中每个元素都有单独的类型

Tuple(T1, T2, ...)

SELECT tuple(1,'a') AS x, toTypeName(x)

String（varchar)

DateTime(timestamp)

与MySQL和Hive框架比较

表引擎

表引擎的作用

数据的存储方式和位置

支持哪些查询及如何支持

并发数据访问

索引的使用

是否可以执行多线程请求

数据复制参数

表引擎的类型（节选）

TinyLog

概述

最简单的表引擎，用于将数据存储在磁盘上

每列都存储在单独的压缩文件中，写入时，数据将附加到文件末尾

特点

没有并发控制

同时从表中读取和写入数据，则读取操作将抛出异常

同时写入多个查询中的表，则数据将被破坏

不支持索引

案例

create table t (a UInt16, b String) ENGINE=TinyLog;

Memory

概述

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失

特点

读写操作不会相互阻塞

不支持索引

Merge

概述

本身不存储数据，但可用于同时从任意多个其他的表中读取数据

特点

读是自动并行的，不支持写入

那些被真正读取到数据的表的索引（如果有的话）会被使用

案例

创建小表

create table t1 (id UInt16, name String) ENGINE=TinyLog;

create table t2 (id UInt16, name String) ENGINE=TinyLog;

create table t3 (id UInt16, name String) ENGINE=TinyLog;

插入数据

insert into t1(id, name) values (1, 'first');

insert into t2(id, name) values (2, 'second');

insert into t3(id, name) values (3, 'i am in t3');

创建Merge大表

create table t (id UInt16, name String) ENGINE=Merge(currentDatabase(), '^t');

MergeTree

概述

当你有巨量数据要插入到表中，你要高效地一批批写入数据片段，并希望这些数据片段在后台按照一定规则合并。相比在插入时不断修改（重写）数据进存储，这种策略会高效很多

特点

数据按主键排序

可以使用分区（如果指定了主键）

支持数据副本

支持数据采样

案例

建表

create table mt_table(date Date, id UInt8, name String) engine=MergeTree() partition by date order by (id,name) settings index_granularity=8192;

插入数据

insert into mt_table values ('2019-05-01', 1, 'zhangsan');

insert into mt_table values ('2019-06-01', 2, 'lisi');

insert into mt_table values ('2019-05-03', 3, 'wangwu');

ReplacingMergeTree

概述

基于MergeTree基础，添加了处理重复数据的功能，会删除相同主键的重复项

特点

对数据大量的读写，实际不推荐使用

案例

建表

create table rmt_table (date Date, id UInt8, name String,point UInt8) ENGINE=ReplacingMergeTree(point) partition by date order by (id,name);

插入数据

insert into rmt_table values ('2019-07-10', 1, 'a', 20);

insert into rmt_table values ('2019-07-10', 1, 'a', 30);

insert into rmt_table values ('2019-07-11', 1, 'a', 20);

insert into rmt_table values ('2019-07-11', 1, 'a', 30);

insert into rmt_table values ('2019-07-11', 1, 'a', 10);

SummingMergeTree

概述

基于MergeTree，将相同主键的行合并为一

案例

建表

create table smt_table (date Date, name String, sum UInt16, not_sum UInt16) engine=SummingMergeTree(sum) partition by date order by (date,name)

插入数据

insert into smt_table values ('2019-07-10', 'a', 1, 2);

insert into smt_table values ('2019-07-10', 'b', 2, 1);

insert into smt_table values ('2019-07-11', 'b', 3, 9);

insert into smt_table values ('2019-07-11', 'b', 3, 8);

insert into smt_table values ('2019-07-11', 'a', 3, 1);

insert into smt_table values ('2019-07-12', 'c', 1, 3);

Distributed

概述

分布式引擎，本身不存储数据, 但可以在多个服务器上进行分布式查询

特点

读是自动并行的

读取时，远程服务器表的索引（如果有的话）会被使用

案例

在各机上建表

create table t(id UInt16, name String) ENGINE=TinyLog;

insert into t(id, name) values (1, 'zhangsan');

insert into t(id, name) values (2, 'lisi');

建立分布式表

create table dis_table(id UInt16, name String) ENGINE=Distributed(clickhouse_cluster, default, t, id);

插入数据

insert into dis_table dis_table values(3, 'wangwu')

SQL语法

CREATE

CREATE DATABASE

CREATE TABLE

直接创建

create table t1(id UInt16,name String) engine=TinyLog

创建一个与其他表具有相同结构的表

create table t2 as t1 engine=Memory

使用指定的引擎创建一个与SELECT子句结果具有相同结构的表

create table t3 engine=TinyLog as select * from t1

INSERT INTO

insert into t1 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu'）

insert into t2 select * from t3

ALTER

ALTER只支持MergeTree系列，Merge和Distributed引擎的表

ADD COLUMN

alter table mt_table add column age UInt8

DROP COLUMN

alter table mt_table drop column ag

MODIFY COLUMN

alter table mt_table modify column age UInt16

DESCRIBE TABLE

desc mt_table

CHECK TABLE

该命令只支持Log，TinyLog和StripeLog引擎

0 – 数据已损坏

1 – 数据完整

优化

参数调优

max_table_size_to_drop

路径

/etc/clickhouse-server/config.xml

作用

随时清理分区或表

默认50GB，改为0

max_memory_usage

删除多个节点上的同一张表

on cluster关键字

drop table t on cluster clickhouse_cluster

自动数据备份（只适用于MergeTree）

https://clickhouse.yandex/docs/zh/operations/table_engines/replication/

Replicated前缀（ReplicatedMergeTree）

配置metrika.xml

hadoop1: shard=01, replica=01 hadoop2: shard=01, replica=02 hadoop3: shard=02, replica=01 hadoop4: shard=02, replica=02 hadoop5: shard=03, replica=01 hadoop6: shard=03, replica=02

<internal_replication>true</internal_replication>

<host>hadoop1</host>

<host>hadoop2</host>

<internal_replication>true</internal_replication>

<host>hadoop3</host>

<host>hadoop4</host>

<internal_replication>true</internal_replication>

<host>hadoop5</host>

<host>hadoop6</host>

<host>hadoop102</host>

<host>hadoop103</host>

<host>hadoop104</host>

<min_part_size>10000000000</min_part_size>

<min_part_size_ratio>0.01</min_part_size_ratio>

创建表

/clickhouse/tables/为公共前缀，Zookeeper的节点地址 {shard}为分片标识，会自动获取macros中的配置 table_name为该表在Zookeeper中的名字，一般与表名相同 {replica}为副本标识，会自动获取macros中的配置

CREATE TABLE table_name

(

...

)

ENGINE=ReplicatedMergeTree('/clickhouse/tables/{shard}/table_name', '{replica}')

PARTITION BY expr

ORDER BY expr

SAMPLE BY expr

案例：从HDFS导入数据

查询HDFS上的CSV文件

准备CSV文件

mkdir /opt/module/datas

cd datas/

vim student.csv

1,zhangsan

2,lisi

3,wangwu

上传HDFS

hadoop fs -put student.csv /

ClickHouse建表

clickhouse-client -m

create table hdfs_student_csv

(

id Int8,

name String

)

Engine=HDFS('hdfs://hadoop102:9000/student.csv','CSV');

导入HDFS上的数据

ClickHouse建表

create table student_local

(

id Int8,

name String

)

Engine=TinyLog;

HDFS导入数据

insert into student_local select * from hdfs_student_csv;