ClickHouse原理解析与应用实战

IT大咖说

发布于 2022-06-17 19:40:56

2K0

发布于 2022-06-17 19:40:56

文章被收录于专栏：IT大咖说

◆ ClickHouse概念

clickhouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)，由俄罗斯最大的搜索公司Yandex开发，于2016年开源，采用c++开发。

◆ OLAP 和 OLTP 这两个概念

OLAP（On-Line Analytical Processing）：联机分析处理OLAP（On-Line Analytical Processing),仓库型数据库，主要是读取数据，做复杂数据分析（多维），侧重技术决策支持，提供直观简单的结果,开源OLAP引擎包含Hive、SparkSQL、Presto、HAWQ、Druid、ClickHouse、Impala、Kylin、Greeplum等。
OLTP（On-Line transaction processing）：传统的关系型数据库，主要操作增删改查，强调事务一致性，比如银行系统、电商系统；

◆ OLAP介绍

◆ OLAP的前世今生

OLAP(Online analytical processing)，即联机分析处理，主要用于支持企业决策管理分析。数据库概念最初源于1962年Kenneth Iverson发表的名为“A Programming Language” (APL)的著作，它第一次提出了处理操作和多维变量的的数学表达式，后来APL语言由IBM实现。

OLAP委员会对联机分析处理的定义为:使分析人员、管理人员或执行人员能够从多种角度对从原始数据中转化出来的、能够真正为用户所理解的、并真实反映企业维特性的信息进行快速、一致、交互的存取，从而获得对数据更深入了解的一类软件技术.

第一款OLAP产品Express于1975年问世，随着被Oracle收购后繁荣发展了30余年，最后由继任者Oracle 9i替代。这么多年过去，基本的OLAP理念和数据模型仍然未变。

OLAP这个名词是数据库之父Edgar F. Codd于1993年在文章《Providing OLAP (On-Line Analytical Processing) to User-Analysts: An IT Mandate》提出，他总结了OLAP产品的12个原则，随后OLAP产品相继问世并逐渐形成今天的格局。

◆ OLAP产品分类

OLAP按存储器的数据存储格式分为基于多维数据库的MOLAP和交互分析的的ROLAP。

MOLAP（Multi-dimensional OLAP）以多维数组（Multi-dimensional Array）存储模型的OLAP，是OLAP发源最初的形态，某些方面也等同于OLAP。它的特点是数据需要预计算（pre-computaion），然后把预计算之后的结果（cube）存在多维数组里。
优点：cube包含所有维度的聚合结果，所以查询速度非常快，计算结果数据占用的磁盘空间相对关系型数据库更小、高性能、高并发。
缺点：空间和时间开销大，随着维度增加计算时间大幅增加、查询灵活度比较低，需要提前设计维度模型，查询分析的内容仅限于这些指定维度，增加维度需要重新计算、不支持明细数据查询。
代表：Kylin / Druid，适合对性能非常高的OLAP场景。
ROLAP（Relational OLAP）基于关系模型存放数据，一般要求事实表（fact table）和维度表（dimensition table）按一定关系设计，它不需要预计算，使用标准SQL就可以根据需要即时查询不同维度数据。
优点：扩展性强，适用于维度数量多的模型，MOLAP对于维度多的模型预计算慢，空间占用大、支持任意SQL表达、无数据冗余与预处理。
缺点：因为是即时计算，查询响应时间一般比预计算的MOLAP长、大数据量下分钟级响应，不支持实时数据。
代表：Presto / Impala / SparkSQL / Drill

◆OLAP场景的关键特征

大多数是读请求
数据总是以相当大批地写入(>1000rows)
不修改已添加的数据
每次查询都从数据中读取大量的行，但同时仅需要少量的列
宽表，即每个表包含大量的列
较少的查询(通常每台服务器每秒数百个查询或更少)
对于简单查询，允许延迟大约50ms
列中的数据相对较小，如数字和短字符串
处理单个查询时需要高吞吐量(每个服务器每秒高达数十亿行)
事务不是必须的
对于数据一致性要求低
每个查询除了一个大表外，其余都很小
查询结果明显小于源数据，或者说，数据被过滤或聚合后能够被盛放在内存中

◆列式存储更适合OLAP的原因

列式数据对于大多数查询而言，处理速度至少提高了100倍

行式

列式

针对分析类查询，通常只需要读取表中一小部分列。在列式数据库中你可以只读取你需要的数据。
由于数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储也容易压缩。
由于io的降低，这将帮助更多的数据被系统缓存。

◆ ClickHouse核心特性

◆ ClickHouse为什么这么快

◆ 行存储和列存储

分析场景中，我们一般会读大量的行而取少量的列，在列式存储结构下，我们只需要取对应的列数据就可以，不参与计算的列完全不会被扫描到，这会极大的降低磁盘 IO 的消耗。

◆ 数据压缩的本质

基于列式存储的结构，同一列中的数据属于同一类型，压缩效果会更加显著。列存储往有着高达十倍甚至更高的压缩比，节省了大量的存储空间，降低了存储成本。

◆ 向量化执行引擎

SIMD（Single Instruction Multiple Data）即单条指令操作多条数据，它是通过数据并行以提高性能的一种方式，可以简单理解为在寄存器层面对程序中的数据做并行处理，Clickhouse 在能够提升计算效率的地方大量使用了 SIMD，通过使用 SIMD，基本上能带来几倍的性能提升，像阿里云的 PolarDB-X 也引入了向量化执行引擎，为表达式计算带来了几十倍的性能提升。

◆多线程与分布式

分布式领域存在一条定律，计算移动比数据移动更加划算，这也是其核心所在，将数据的计算直接发放到数据所在的服务器，多机并行处理，再把最终的结果汇集在一起；另外 Clickhouse 也通过线程级别并行的方式为效率进一步提速，极致去利用服务器的资源。

◆多样化的表引擎

◆ ClickHouse的安装与部署

◆ ClickHouse单安装过程

ClickHouse可以在任何具有x86_64，AArch64或PowerPC64LE CPU架构的Linux，FreeBSD或Mac OS X上运行。

官方预构建的二进制文件通常针对x86_64进行编译，并利用SSE 4.2指令集，因此，除非另有说明，支持它的CPU使用将成为额外的系统需求。下面是检查当前CPU是否支持SSE 4.2的命令:

$ grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"

Ubuntu的官方预编译deb软件包。运行以下命令来安装包:

sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4echo "deb https://repo.clickhouse.com/deb/stable/ main/" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list
sudo apt-get updatesudo apt-get install -y clickhouse-server clickhouse-clientsudo service clickhouse-server startclickhouse-client

◆Mac下单机安装部署Clickhouse、使用Docker容器部署安装

1、安装docker

2、安装ClickHouse

客户端：docker pull yandex/clickhouse-client 服务端：docker pull yandex/clickhouse-server

3、命令启动镜像或者docker控制台启动

docker run -d --name ch-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server

4、启动命令

clickhouse-client

方法二：y

sudo yum install yum-utils
sudo rpm --import https://repo.clickhouse.com/CLICKHOUSE-KEY.GPGsudo yum-config-manager --add-repo https://repo.clickhouse.com/rpm/stable/x86_64

服务端启动命令：clickhouse-server

客户端启动命令：clickhouse-client

◆ ClickMergeTree原理解析

◆ MergeTree的创建方式与存储结构

创建的时候将ENGINE参数声明为MergeTree()

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
省略...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, 省略...]

PARTITION BY [选填]：分区键，用于指定表数据以何种标准进行分区。

ORDER BY[选填]：排序键

PRIMARY KEY [选填] ：主键

SAMPLE BY [选填]：抽样表达式，用于声明数据以何种标准进行采样

SETTINGS：index_granularity [选填]：它表示索引的粒度，默认值为 8192。也就是说，MergeTree的索引在默认情况下，每间隔8192行数据才生成一条索引，稀疏索引。

◆MergeTree的存储结构

partition：分区目录，下面存放这各类数据文件，相同分区的数据，会被合并到同一个分区目录，不同的分区，数据永远不会被合并到一起。

checksums.txt：校验文件，保存了分区各类文件的size大小以及hash值，主要用于快速校验文件的完整性和正确性。
columns.txt：列信息文件，使用明文格式存储，用于保存数据分区下的列字段信息。
count.txt：计数文件，用于记录当前数据分区目录下数据的总行数。
primary.idx：一级索引文件，使用二进制格式存储，存放稀疏索引。
[Column].bin：数据文件，存储某一列的数据，由于MergeTree采用列式存储，所以每一个列字段都拥有独立的.bin数据文件，并以列字段名称命名。
[Column].mrk：列字段标记文件，标记文件中保存了.bin文件中数据的偏移量信息，标记文件与稀疏索引对齐，又与.bin文件一一对应，所以MergeTree通过标记文件建立了 primary.idx稀疏索引与.bin数据文件之间的映射关系。
[Column].mrk2：如果使用了自适应大小的索引间隔，则标记文件会以.mrk2命名。它的工作原理和作用与.mrk标记文件相同。
partition.dat与minmax_[Column].idx：如果使用了分区键，例如PARTITION BY EventTime，则会额外生成partition.dat与minmax索引文件，它们均使用二进制格式存储，minmax记录当前分区下分区字段对应原始数据的最小和最大值，如2019-05-012019-05-05。
）skpidx[Column].idx与skpidx[Column].mrk：二级索引与标记文件，这些索引的最终目标与一级稀疏索引相同，都是为了进一步减少所需扫描的数据范围，以加速整个查询过程。

◆数据的分区规则

不指定分区键：即不使用PARTITION BY声明任何分区表达式，则分区ID默认取名为all，所有的数据都会被写入这个all分区
使用整型：如果分区键取值属于整型且无法转换为日期类型YYYYMMDD格式，则直接按照该整型的字符形式输出，作为分区ID的取值。
使用日期类型：如果分区键取值属于日期类型，或者是能够转换为YYYYMMDD格式的整型，则使用按照YYYYMMDD进行格式化后的字符形式输出，并作为分区ID的取值。
使用其他类型：如果分区键取值既不属于整型，也不属于日期类型，例如String、Float等，则通过128位Hash算法取其Hash值作为分区ID的取值

◆分区目录的命名规则

MergeTree分区目录的完整物理名称并不是只有ID而已，在ID之后还跟着一串奇怪的数字，例如 20190511_0，分区ID如何生成的。

命名规则

PartitionID_MinBlockNum_MaxBlockNum_Level

PartitionID：分区ID, 201905 表示分区目录的ID
MinBlockNum和MaxBlockNum：顾名思义，最小数据块编号与最大数据块编号,1_1 表示最小的数据块编码和最大的数据块编码.
Level :合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄。

◆分区目录的合并过程

属于同一个分区的多个目录，在合并之后会生成一个全新的目录，目录中的索引和数据文件也会相应地进行合并。新目录名称的合并方式遵循以下规则，其中：

MinBlockNum：取同一分区内所有目录中最小的MinBlockNum 值。
MaxBlockNum：取同一分区内所有目录中最大的MaxBlockNum 值。
Level：取同一分区内最大Level值并加1。

分区目录从创建、合并到删除的整个过程

分区目录在发生合并之后，旧的分区目录并没有被立即删除，而是会存留一段时间。但是旧的分区目录已不再是激活状态（active=0），所以在数据查询时，它们会被自动过滤掉。

◆ 一级索引

MergeTree的主键使用PRIMARY KEY定义，待主键定义之后， MergeTree会依据index_granularity间隔（默认8192行），为数据表生成一级索引并保存至primary.idx文件内，索引数据按照PRIMARY KEY排序。相比使用PRIMARY KEY定义，更为常见的简化形式是通过 ORDER BY指代主键。

◆ 稀疏索引

primary.idx文件内的一级索引采用稀疏索引实现。

稀疏索引的优势是显而易见的，它仅需使用少量的索引标记就能够记录大量数据的区间位置信息，且数据量越大优势越为明显。以默认的索引粒度（8192）为例，MergeTree只需要12208行索引标记就能为 1亿行数据记录提供索引。由于稀疏索引占用空间小，所以primary.idx 内的索引数据常驻内存，取用速度自然极快。

◆ 索引粒度

索引粒度通过index_granularity这个参数设置，默认是8092。

数据以indexgranularity的粒度（默认8192）被标记成多个小的区间，其中每个区间最多8192行数据。MergeTree使用MarkRange表示一个具体的区间，并通过start和end表示其具体的范围。indexgranularity 的命名虽然取了索引二字，但它不单只作用于一级索引（.idx），同时也会影响数据标记（.mrk）和数据文件（.bin）。

◆索引数据的生成规则

如果使用CounterID作为主键（ORDER BY CounterID），则每间隔8192行数据就会取一次 CounterID的值作为索引值，索引数据最终会被写入primary.idx文件进行保存

如果使用多个主键，例如ORDER BY(CounterID,EventDate)，则每间隔8192行可以同时取CounterID与EventDate两列的值作为索引值

◆索引的查询过程

MarkRange：一个具体的数据段，MarkRange与索引编号对应，使用start和end两个属性表示其区间范围。

索引查询其实就是两个数值区间的交集判断。其中，一个区间是由基于主键的查询条件转换而来的条件区间；而另一个区间是MarkRange对应的数值区间。

假如现在有一份测试数据，共192行记录。其中，主键ID为String类型，ID的取值从A000开始，后面依次为 A001、A002……直至A192为止。MergeTree的索引粒度 index_granularity=3，根据索引的生成规则，primary.idx文件内的索引数据会如下图所示。

根据索引数据，MergeTree会将此数据片段划分成192/3=64个小的 MarkRange，两个相邻MarkRange相距的步长为1。其中，所有 MarkRange（整个数据片段）的最大数值区间为[A000,+inf)，如下图所示。

查询过程

生成查询条件区间，将查询条件转换为区间的形势查询。
递归交集判断：以递归的形式，依次对MarkRange的数值区间与条件区间做交集判断
不存在交集，则直接通过剪枝算法优化此整段MarkRange
如果存在交集，且MarkRange步长大于8(end-start)，则将此区间进一步拆分成8个子区间，并重复此规则，继续做递归交集判断。
如果存在交集，且MarkRange不可再分解（步长小于8），则记录 MarkRange并返回。
合并MarkRange区间：将最终匹配的MarkRange聚在一起，合并它们的范围

◆二级索引

granularity与index_granularity的关系

indexgranularity定义了数据的粒度，而granularity定义了聚合信息汇总的粒度。换言之， granularity定义了一行跳数索引能够跳过多少个indexgranularity区间的数据。

二级索引类型

minmax：minmax索引记录了一段数据内的最小和最大极值，其索引的作用类似分区目录的minmax索引，能够快速跳过无用的数据区间。
set：set索引直接记录了声明字段或表达式的取值
ngrambfv1：ngrambfv1索引记录的是数据短语的布隆表过滤器，只支持String和FixedString数据类型
tokenbfv1：tokenbfv1索引是ngrambf_v1的变种，同样也是一种布隆过滤器索引

◆ 数据存储

压缩数据块

MergeTree在数据具体的写入过程中，会依照索引粒度，按批次获取数据并进行处理。如果把一批数据的未压缩大小设为size，压缩前数据字节大小，严格控制在64kb-1MB之间。

写入过程：

单个批次数据size<64KB ：如果单个批次数据小于64KB，则继续获取下一批数据，直至累积到size>=64KB时，生成下一个压缩数据块。
单个批次数据64KB<=size<=1MB ：如果单个批次数据大小恰好在64KB与1MB之间，则直接生成下一个压缩数据块。
单个批次数据size>1MB ：如果单个批次数据直接超过1MB，则首先按照1MB大小截断并生成下一个压缩数据块。

数据标记的生成规则

分区、索引、标记和压缩数据，现在将它们聚在一块进行一番总结。接下来，就分别从写入过程、查询过程，以及数据标记与压缩数据块的三种对应关系的角度展开介绍。

写入过程

生成一个新的分区目录
分区目录合并
按照索引粒度、生成一级索引
生成列字段的.mrk数据标记和.bin压缩数据文件

数据标记与压缩数据块的对应关系：一对一、多对一、一对多。

◆查询过程

◆ClickMergeTree系列表引擎

表引擎可以分为6个系列，分别是合并树、外部存储、内存、文件、接口和其他，每一个系列的表引擎都有着独自的特点与使用场景。

除了基础表引擎MergeTree之外，常用的表引擎还有ReplacingMergeTree、SummingMergeTree、 AggregatingMergeTree、CollapsingMergeTree和 VersionedCollapsingMergeTree

◆多路径存储策略

有三类存储策略

默认策略：MergeTree原本的存储策略，无须任何配置，所有分区会自动保存到config.xml配置中path指定的路径下
JBOD策略：这种策略适合服务器挂载了多块磁盘，但没有做 RAID的场景，它是一种轮询策略，每执行一次INSERT或者MERGE，所产生的新分区会轮询写入各个磁盘。
HOT/COLD策略：这种策略适合服务器挂载了不同类型磁盘的场景。将存储磁盘分为HOT与COLD两类区域。HOT区域使用SSD这类高性能存储媒介，注重存取性能。

◆ReplacingMergeTree

MergeTree拥有主键，但是它的主键却没有唯一键的约束，ReplacingMergeTree为了数据去重而设计的，它能够在合并分区时删除重复的数据。

创建ReplacingMergeTree表的方法，替换Engine即可

ENGINE = ReplacingMergeTree(ver)//ver是选填参数，会指定一个UInt*、Date或者DateTime类型的字段作为版本号

创建ReplacingMergeTree数据表方法

CREATE TABLE replace_table(        id String,
        code String,
        create_time DateTime
)ENGINE = ReplacingMergeTree()PARTITION BY toYYYYMM(create_time)ORDER BY (id,code) //根据id与code去重
PRIMARY KEY id

只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除

使用ORBER BY排序键作为判断重复数据的唯一键。(
只有在合并分区的时候才会触发删除重复数据的逻辑。
以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。
在进行数据去重时，因为分区内的数据已经基于ORBER BY 进行了排序，所以能够找到那些相邻的重复数据。
数据去重策略有两种：
如果没有设置ver版本号，则保留同一组重复数据中的最后一行。
如果设置了ver版本号，则保留同一组重复数据中ver字段取值最大的那一行。

总结：ReplacingMergeTree在去除重复数据时，确实是以ORDER BY排序键为基准的，而不是PRIMARY KEY。

◆SummingMergeTree

终端用户只需要查询数据的汇总结果，不关心明细数据,则使用SummingMergeTree 引擎

SummingMergeTree能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总合并成一行，这样既减少了数据行，又降低了后续汇总查询的开销。

CREATE TABLE summing_table(        id String,
        city String,
        v1 UInt32,
        v2 Float64,
create_time DateTime
)ENGINE = SummingMergeTree()PARTITION BY toYYYYMM(create_time)ORDER BY (id, city)
PRIMARY KEY id

执行optimize强制进行触发和合并操作：

optimize TABLE summing_table FINAL

用ORBER BY排序键作为聚合数据的条件Key。
只有在合并分区的时候才会触发汇总的逻辑。
以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。
如果在定义引擎时指定了columns汇总列（非主键的数值类型字段），则SUM汇总这些列字段；如果未指定，则聚合所有非主键的数值类型字段。
在进行数据汇总时，因为分区内的数据已经基于ORBER BY 排序，所以能够找到相邻且拥有相同聚合Key的数据。
在汇总数据时，同一分区内，相同聚合Key的多行数据会合并成一行。其中，汇总字段会进行SUM计算；对于那些非汇总字段，则会使用第一行数据的取值。
支持嵌套结构，但列字段名称必须以Map后缀结尾。嵌套类型中，默认以第一个字段作为聚合Key。除第一个字段以外，任何名称以Key、Id或Type为后缀结尾的字段，都将和第一个字段一起组成复合Key。

◆ AggregatingMergeTree

AggregatingMergeTree能够在合并分区的时候，按照预先定义的条件聚合数据。同时，根据预先定义的聚合函数计算数据并通过二进制的格式存入表内。将同一分组下的多行数据聚合成一行，既减少了数据行，又降低了后续聚合查询的开销。

AggregatingMergeTree更为常见的应用方式是结合物化视图使用，将它作为物化视图的表引擎。而这里的物化视图是作为其他数据表上层的一种查询视图。

◆定义

ENGINE = AggregatingMergeTree()

例子：

CREATE TABLE agg_table(id String,
city String,
code AggregateFunction(uniq,String),value AggregateFunction(sum,UInt32),
create_time DateTime
)ENGINE = AggregatingMergeTree()PARTITION BY toYYYYMM(create_time)ORDER BY (id,city)
PRIMARY KEY id

上例中列字段id和city是聚合条件，等同于下面的语义GROUP BY id，city,

用ORBER BY排序键作为聚合数据的条件Key。
使用AggregateFunction字段类型定义聚合函数的类型以及聚合的字段。
只有在合并分区的时候才会触发聚合计算的逻辑。
以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并计算，而不同分区之间的数据则不会被计算。
在进行数据计算时，因为分区内的数据已经基于ORBER BY 排序，所以能够找到那些相邻且拥有相同聚合Key的数据。
AggregateFunction类型的字段使用二进制存储，在写入数据时，需要调用State函数；而在查询数据时，则需要调用相应的Merge 函数。其中，*表示定义时使用的聚合函数。
AggregatingMergeTree通常作为物化视图的表引擎，与普通 MergeTree搭配使用。

◆CollapsingMergeTree

CollapsingMergeTree就是一种通过以增代删的思路，支持行级数据修改和删除的表引擎，通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除，相互抵消。

◆ 声明CollapsingMergeTree的方式

ENGINE = CollapsingMergeTree(sign)

例子：

CREATE TABLE collpase_table(        id String,
        code Int32,
        create_time DateTime,        sign Int8)ENGINE = CollapsingMergeTree(sign)PARTITION BY toYYYYMM(create_time)ORDER BY id

◆ 修改数据

--修改前的源数据, 它需要被修改INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',1)--镜像数据, ORDER BY字段与源数据相同(其他字段可以不同),sign取反为-1,它会和源数据折叠INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',-1)--修改后的数据 ,sign为1INSERT INTO TABLE collpase_table VALUES('A000',120,'2019-02-20 00:00:00',1)

◆ 删除数据

--修改前的源数据, 它需要被删除INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',1)--镜像数据, ORDER BY字段与源数据相同, sign取反为-1, 它会和源数据折叠INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',-1)

◆ VersionedCollapsingMergeTree

VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作.

◆ 定义

ENGINE = VersionedCollapsingMergeTree(sign,ver)

◆ 例子

CREATE TABLE ver_collpase_table(        id String,
        code Int32,
        create_time DateTime,        sign Int8,
        ver UInt8
)ENGINE = VersionedCollapsingMergeTree(sign,ver)PARTITION BY toYYYYMM(create_time)ORDER BY id

◆ 其他常见类型表引擎

◆ HDFS

Docker安装HDFS

拉取hadoop镜像

docker pull singularities/hadoop

创建docker-compose.yml文件

version: "2"services:  namenode:    image: singularities/hadoop    command: start-hadoop namenode    hostname: namenode    environment:      HDFS_USER: hdfsuser    ports:
      - "8020:8020"
      - "14000:14000"
      - "50070:50070"
      - "50075:50075"
      - "10020:10020"
      - "13562:13562"
      - "19888:19888"
  datanode:    image: singularities/hadoop    command: start-hadoop datanode namenode    environment:      HDFS_USER: hdfsuser    links:
      - namenode

执行

[root@localhost hadoop]# docker-compose up -dCreating network "hadoop_default" with the default driver
Creating hadoop_namenode_1 ... doneCreating hadoop_datanode_1 ... done

生成3个datanode

[root@localhost hadoop]# docker-compose scale datanode=3WARNING: The scale command is deprecated. Use the up command with the --scale flag instead.
Starting hadoop_datanode_1 ... doneCreating hadoop_datanode_2 ... doneCreating hadoop_datanode_3 ... done

列出容器查看

[root@localhost hadoop]# docker psCONTAINER ID        IMAGE                  COMMAND                  CREATED             STATUS              PORTS                                                                                                                                                                                                                                                NAMES19f9685e286f        singularities/hadoop   "start-hadoop data..."   48 seconds ago      Up 46 seconds       8020/tcp, 9000/tcp, 10020/tcp, 13562/tcp, 14000/tcp, 19888/tcp, 50010/tcp, 50020/tcp, 50070/tcp, 50075/tcp, 50090/tcp, 50470/tcp, 50475/tcp                                                                                                           hadoop_datanode_3e96b395f56e3        singularities/hadoop   "start-hadoop data..."   48 seconds ago      Up 46 seconds       8020/tcp, 9000/tcp, 10020/tcp, 13562/tcp, 14000/tcp, 19888/tcp, 50010/tcp, 50020/tcp, 50070/tcp, 50075/tcp, 50090/tcp, 50470/tcp, 50475/tcp                                                                                                           hadoop_datanode_25a26b1069dbb        singularities/hadoop   "start-hadoop data..."   8 minutes ago       Up 8 minutes        8020/tcp, 9000/tcp, 10020/tcp, 13562/tcp, 14000/tcp, 19888/tcp, 50010/tcp, 50020/tcp, 50070/tcp, 50075/tcp, 50090/tcp, 50470/tcp, 50475/tcp                                                                                                           hadoop_datanode_1a8656de09ecc        singularities/hadoop   "start-hadoop name..."   8 minutes ago       Up 8 minutes        0.0.0.0:8020->8020/tcp, 0.0.0.0:10020->10020/tcp, 0.0.0.0:13562->13562/tcp, 0.0.0.0:14000->14000/tcp, 9000/tcp, 50010/tcp, 0.0.0.0:19888->19888/tcp, 0.0.0.0:50070->50070/tcp, 50020/tcp, 50090/tcp, 50470/tcp, 0.0.0.0:50075->50075/tcp, 50475/tcp   hadoop_namenode_1

打开浏览器，查看效果图

http://localhost:50070/dfshealth.html#tab-overview

进入某台docker容器

//拿到container iddocker ps 执行命令进入容器docker exec -it e96b395f56e3 bash

执行HDFS命令

# 查看所有命令hadoop fs# 创建目录hadoop fs -mkdir /hdfs #在根目录下创建hdfs文件夹# 查看目录hadoop fs -ls  /   #列出根目录下的文件列表# 创建多级目录hadoop fs -mkdir -p /hdfs/d1/d2# 上传文件到HDFSecho "hello world" >> local.txt   #创建文件hadoop fs -put local.txt /hdfs/   #上传文件到hdfs# 下载hdfs文件hadoop fs -get /hdfs/local.txt# 删除hdfs中的文件hadoop fs -rm /hdfs/local.txt# 删除hdfs中的目录hadoop fs -rmdir /hdfs/d1/d2

docker 容器里安装一下clickhouse，进行通信

sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4echo "deb https://repo.clickhouse.com/deb/stable/ main/" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list
sudo apt-get updatesudo apt-get install -y clickhouse-server clickhouse-clientsudo service clickhouse-server startclickhouse-client

◆HDFS授权

hadoop fs -mkdir /clickhouse
hadoop fs -chown -R clickhouse:clickhouse /clickhouse

创建HDFS数据表

CREATE TABLE hdfs_table10(id UInt32,
code String,name String)ENGINE = HDFS('hdfs://namenode:8020/clickhouse/hdfs_table10','CSV')

写入数据

INSERT INTO hdfs_table10 SELECT number,concat('code',toString(number)),concat('n',toString(number)) FROM numbers(5)

-- 再次插入就会失败，报文件已存在。一般是csv文件已经在hdfs中存在了，我们直接建表直接去读

查询数据

select * from hdfs_table10

这种方式与使用Hive类似，我们直接可以将HDFS对应的文件映射成ClickHouse中的一张表，这样就可以使用SQL操作HDFS上的文件了。注意：ClickHouse并不能够删除HDFS上的数据，当我们在ClickHouse客户端中删除了对应的表，只是删除了表结构，HDFS上的文件并没有被删除，这一点跟Hive的外部表十分相似。

◆MySQL

服务器安装mysql

apt-get install mysql-server//启动服务service mysql start//进入服务mysql -uroot -p

MySQL表引擎可以与MySQL数据库中的数据表建立映射，并通过 SQL向其发起远程查询，包括SELECT和INSERT，它的声明方式如下：

ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[,
replace_query, 'on_duplicate_clause'])

·replacequery默认为0，对应MySQL的REPLACE INTO语法。如果将它设置为1，则会用REPLACE INTO代替INSERT INTO。·onduplicateclause默认为0，对应MySQL的ON DUPLICATE KEY 语法。如果需要使用该设置，则必须将replacequery设置成0。

◆clickhouse创建映射表

CREATE TABLE dolphin_scheduler_table00(id UInt32,name String)ENGINE = MySQL('127.0.0.1:3306', 'test','dolphin_scheduler_table', 'root', '')

插入数据

INSERT INTO TABLE dolphin_scheduler_table00 VALUES (1,'流程1')

查询Mysql 表 dolphinschedulertable ，发现数据已经被远程写入了。

◆Kafka

kafka表引擎的声明方式

ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'host:port,... ', //表示Broker服务的地址列表、多个地址之间使用逗号分隔，如broker_1,broker_2
    kafka_topic_list = 'topic1,topic2,...', //表示订阅消息主题的名称列表
    kafka_group_name = 'group_name', //表示消费组的名称，
    kafka_format = 'data_format'[,] //表示用于解析消息的数据格式
    [kafka_row_delimiter = 'delimiter_symbol'] //表示判定一行数据的结束符，默认值为'\0'
    [kafka_schema = ''] //对应Kafka的schema参数
    [kafka_num_consumers = N] //表示消费者的数量，默认值为1
    [kafka_skip_broken_messages = N]
    [kafka_commit_every_batch = N //表示执行Kafka commit的频率

 注意：带方括号的为选填项

◆创建数据表方式

CREATE TABLE kafka_test(        id UInt32,
        code String,        name String) ENGINE = Kafka()SETTINGS
        kafka_broker_list = 'hdp1.nauu.com:6667',
        kafka_topic_list = 'sales-queue',
        kafka_group_name = 'chgroup',
        kafka_format = 'JSONEachRow',
        kafka_skip_broken_messages = 100

◆ClickHouse数据查询实操

1、在生产环境中、或者在实际应用场景中、应当避免使用SELECT * 形式来查询数据，因为通配符*对于采用列式存储的ClickHouse而言没有任何好处。假如面对一张拥有数百个列字段的数据表，下面这两条 SELECT语句的性能可能会相差100倍之多,因为 * 会查询所有列字段。

--使用通配符*与按列按需查询相比，性能可能相差100倍SELECT * FROM datasets.hits_v1;SELECT WatchID FROM datasets.hits_v1;

◆各OLAP引擎对比

来源：

https://www.toutiao.com/article/7108275894850028032/?log_from=a264805d7c4ed_1655341720493

“IT大咖说”欢迎广大技术人员投稿，投稿邮箱：aliang@itdks.com

来都来了，走啥走，留个言呗~

IT大咖说 | 关于版权

由“IT大咖说（ID：itdakashuo）”原创的文章，转载时请注明作者、出处及微信公众号。投稿、约稿、转载请加微信：ITDKS10（备注：投稿），茉莉小姐姐会及时与您联系！

感谢您对IT大咖说的热心支持！

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-06-16，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

存储

大数据

本文分享自 IT大咖说微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！