红泥小火炉


clickHouse(基本sql+mergeTree引擎)

Nathaniel 2021-04-26 548浏览 0条评论
首页/正文
分享到: / / / /

数据增删改查

数据查询

clickHouse对于SQL语句的解析是大小写敏感的。

[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename]
[FORMAT format]
WITH字句

ClickHouse支持CTE(Common Table Expression,公共表表达式),以增强查询语句的表达。

  • 定义变量,在后面的子查询中可以被使用到。
with 10 as start
select number from system.numbers where number > start limit 10;
  • 可以访问SELECT子句中的列字段,并调用函数做进一步的加工处理
with sum(data_uncompressed_bytes) as bytes
select database,formatReadableSize(bytes) as format from system.columns
group by database
order by bytes desc;
  • 定义子查询,子查询只能返回一条记录,否则会提示异常
with (select sum(data_uncompressed_bytes) from system.columns) as total_bytes
select database,(sum(data_compressed_bytes)/total_bytes) * 100 as database_disk_usage from system.columns
group by database
order by database_disk_usage desc;
  • 在子查询中重复使用WITH,也就是嵌套使用。
with (round(database_disk_usage)) as database_disk_usage_v1
select database,database_disk_usage_v1,database_disk_usage from (
with(select sum(data_uncompressed_bytes) from system.columns) as total_bytes
select database,(sum(data_uncompressed_bytes)/total_bytes) * 100 as database_disk_usage from system.columns
group by database
order by database_disk_usage desc
)
FROM字句

from字句也可以在表函数中取值;

select number from numbers(5);

如果没有from,则从虚拟表中查询,clickHouse中的虚拟表为system.one

在FROM子句后,可以使用Final修饰符。它可以配合CollapsingMergeTree和Versioned-CollapsingMergeTree等表引擎进行查询操作,以强制在查询过程中合并,但由于Final修饰符会降低查询性能,所以应该尽可能避免使用它。

SAMPLE子句

实现数据采样,返回采样数据。适合在那些可以接受近似查询结果的场合使用。

SAMPLE BY所声明的表达式必须同时包含在主键的声明内,只能用于MergeTree系列引擎的数据表。

Sample Key必须是Int类型。

  • SAMPLE factor(采样因子)

factor设置为0或者1,则效果等同于不进行数据采样。采样结果需要乘以采样系数,_sample_factor可以返回当前查询所对应的采样系数

  • SAMPLE rows(样本数量)

表示至少采样多少行数据。

  • SAMPLE factor OFFSET n(按照采样因子系数和偏移量)

此处的n为比例,表示从总数据量的百分比开始进行采样。

ARRAY JOIN字句

允许在数据表的内部,与数组或嵌套类型的字段进行JOIN操作,从而将一行数组展开为多行。

在一条SELECT语句中,只能存在一个ARRAY JOIN(使用子查询除外)。在默认情况下采用的是INNER JOIN策略。

当同时对多个数组字段进行ARRAY JOIN操作时,查询的计算逻辑是按行合并而不是产生笛卡儿积。

JOIN字句

通过join_default_strictness配置参数修改默认的连接精度类型。

ALL:相当于关联右表中满足on条件的所有记录;a.id=b.id

ANY:如果左表内的一行数据,在右表中有多行数据与之连接匹配,则仅返回右表中第一行连接的数据

ASOF:一种模糊连接,它允许在连接键之后追加定义一个模糊连接的匹配条件asof_column。ASOF支持使用USING的简写形式,USING后声明的最后一个字段会被自动转换成asof_colum模糊连接条件。asof_colum必须是整型、浮点型和日期型这类有序序列的数据类型;asof_colum不能是数据表内的唯一字段,换言之,连接键(JOIN KEY)和asof_colum不能是同一个字段。

LEFT JOIN:左表的数据总是能够全部返回;

RIGHT JOIN:右表的数据总是能够全部返回;

FULL JOIN:返回两表的并集;

CROSS JOIN:交叉连接,它会返回左表与右表两个数据集合的笛卡儿积;

注意事项:

  • 应该遵循左大右小的原则;
  • JOIN查询目前没有缓存的支持;
  • 如果是在大量维度属性补全的查询场景中,则建议使用字典代替JOIN查询;
  • 连接查询的空值策略是通过join_use_nulls参数指定的,默认为0。当参数值为0时,空值由数据类型的默认值填充;而当参数值为1时,空值由Null填充。
  • 当数据表的连接字段名称相同时,可以使用USING语法简写。
WHERE与PREWHERE子句

PREWHERE目前只能用于MergeTree系列的表引擎。

只会读取PREWHERE指定的列字段数据,用于数据过滤的条件判断。待数据过滤之后再读取SELECT声明的列字段以补全其余属性。

where不会优化为prewhere字句的场景:

  • 使用了常量表达式;
  • 使用了默认值为ALIAS类型的字段;
  • 包含了arrayJoin、globalIn、globalNotIn或者indexHint的查询;
  • SELECT查询的列字段与WHERE谓词相同;
  • 使用了主键字段;
GROUP BY子句
  • WITH ROLLUP:按照聚合键从右向左上卷数据,基于聚合函数依次生成分组小计和总计。如果设聚合键的个数为n,则最终会生成小计的个数为n+1;
  • WITH CUBE:基于聚合键之间所有的组合生成小计信息;如果设聚合键的个数为n,则最终小计组合的个数为2的n次方;
  • WITH TOTALS:基于聚合函数对所有数据进行总计;
HAVING子句

需要与GROUP BY同时出现,不能单独使用,实现在聚合计算之后实现二次过滤数据。用法同mysql数据库;

如果需要按照聚合值进行过滤,就必须借助HAVING实现。

ORDER BY字句

对于NULL值处理有两种策略,一种是NULL最后,一种是NULL优先;

LIMIT BY子句

运行于ORDER BY之后和LIMIT之前,能够按照指定分组,最多返回前n行数据,适用于TopN的查询场景;

LIMIT n By 表达式
LIMIT y n 表达式(y为偏移量,即跳过偏移量的数据进行查询)
LIMIT子句

用于返回指定的前n行数据,常用于分页场景,用法同mysql数据库

LIMIT n
LIMIT n OFFSET y
LIMIT y,n
SELECT子句

指定查询的数据字段,同mysql数据库

DISTINCT子句

去除重复的字段,同mysql数据库,与ORDER BY 同时存在时,优先执行DISTINCT,再执行ORDER BY字句;

UNION ALL子句

联合左右两边的两组子查询,将结果一并返回。

列字段的数量必须相同,列字段的数据类型必须相同或相兼容,列字段的名称可以不同,查询结果中的列名会以左边的子查询为准。

执行计划查看

通过将ClickHouse服务日志设置到DEBUG或者TRACE级别,可以变相实现EXPLAIN查询,以分析SQL的执行日志。

clickhouse-client -h host主机 --send_logs_level=trace <<< 'sql语句' > /dev/null

数据新增

  • 使用VALUES格式的常规语法

    支持加入表达式或函数

    INSERT INTO table_name VALUES('A0014',toString(1+2),now());
    
  • 使用指定格式的语法:比如CSV等

    INSERT INTO table_name FORMAT format_name data_set
    INSERT INTO table_name FORMAT csv \'A00017','www.baidu.com','2019-10-01'\'A0018','www.baidu.com','2019-10-02'
    
  • 使用SELECT字句形式

    支持加入表达式或函数

    INSERT INTO table_name SELECT ...
    

    支持加入表达式或函数会带来额外的开销,导致写性能的下降,应该避免。

    insert语句在单个数据块写入过程中是有原子特性的,但只限定于在服务端执行,通过JDBC等形式写入不生效。默认每个数据块最多写入1048576行数据(max_insert_block_size参数控制)。

数据更新

数据更新和删除适用于批量数据的修改和删除,不支持事务,语句执行是一个异步的过程,提交之后立即返回,具体的执行结果需要查看system.mutations查询。分区键和主键不能作为修改字段。

执行一次更新或删除,会在系统表中生成一条执行计划,is_done=1时标识执行完毕,在数据表根目录下,会生成与之对应的日志文件记录相关操作。

ALTER TABLE table_name UPDATE column1 = expr1... WHERE filter_expr;

数据删除

ALTER TABLE table_name DELETE WHERE filter_expr;

数据字典

数据字典本质是存储媒介,以K-V的形式定义数据,适合保存常量或维度表,以避免不必要的JOIN查询。

内置字典

内置字典Yandex.Metrica字典,按照字典规范导入数据即可。默认是禁用状态。开启方式:设置config.xml中的path_to_regions_hierarchy_file和path_to_regions_names_files。

path_to_regions_hierarchy_file为主表,由1个regions_hierarchy.txt和多个regions_hierarchy_[name].txt区域层次的数据文件共同组成,缺一不可

path_to_regions_names_files等同于区域数据的维度表,记录了与区域ID对应的区域名称。

访问内置字典的函数为regionToName;

外部扩展字典

由用户自行定义数据模式及数据来源。目前扩展字典支持7种类型的内存布局和4类数据来源。

扩展字典的配置文件在config.xml中的dictionaries_config配置项指定;在默认的情况下,ClickHouse会自动识别并加载/etc/clickhouse-server目录下所有以_dictionary.xml结尾的配置文件。同时ClickHouse也能够动态感知到此目录下配置文件的各种变化,并支持不停机在线更新配置文件。

配置文件中单个字典由dictionary元素组成,内部由5个子元素构成

name:字典名称,确定字典的唯一标识,全局唯一。

structure:字典的数据结构

<dictionary>
    <structure>
        <key>
            <name>数值型的key定义,支持flat、hashed、range_hashed和cache类型</name>
            <!--复合型,使用Tuple元组定义,支持complex_key_hashed、complex_key_cache和ip_trie类型-->
            <attribute>
            	<name>field1</name>
                <type>String</type>
            </attribute>
        </key>
        <attribute></attribute>
    </structure>
</dictionary>

属性字段name、type、null_value为必填。

layout:字典类型,决定了数据以何种结构组织和存储。

flat:只能使用UInt64数值型key,在内存中使用数组结构保存,数组的初始大小为1024,上限为500000,这意味着它最多只能保存500000行数据。

hashed:只能够使用UInt64数值型key,在内存中通过散列结构保存,且没有存储上限的制约。

range_hashed:数据会以散列结构存储并按照时间排序。时间区间通过range_min和range_max元素指定,所指定的字段必须是Date或者DateTime类型。

cache:在内存中会通过固定长度的向量数组保存。定长的向量数组又称cells,它的数组长度由size_in_cells指定。而size_in_cells的取值大小必须是2的整数倍,如若不是,则会自动向上取为2的倍数的整数.如果cache字典使用本地文件作为数据源,则必须使用executable的形式设置。

complex_key_hashed:同hashed。

complex_key_cache:同cache。

ip_trie:只能指定单个String类型的字段,用于指代IP前缀,在内存中使用trie树结构保存,且专门用于IP前缀查询的场景。

source:字典的数据源。目前扩展字典共拥有文件、数据库和其他三类数据来源。

文件:适用静态数据场合

  • 本地文件
<source>
	<file>
    	<path>文件的绝对路径</path>
        <format>数据格式</format>
    </file>
</source>
  • 可执行文件
<source>
    <executable>
        <command>cat 文件的绝对路径</command>
        <format>数据格式</format>
    </executable>
</source>
  • 远程文件:如果是https,那么标签是否需要变更为https------待验证
<source>
	<http>
        <url>http://xxxx</url>
        <format>数据格式</format>
    </http>
</source>

数据库

可以配置多种数据源作为字典来源,mysql、CH、MongoDB等,配置方式可以参见官网描述,大同小异。

其他

lifetime:字典的更新时间,单位为秒,min和max均为0时,禁用更新。对于cache字典,该值反映了缓存失效时间。扩展字典目前并不支持增量更新。

<lifetime>
	<min>300</min>
    <max>360</max>
</lifetime>

MergeTree

写入数据时,数据会以数据片段的形式写入磁盘,且数据片段不可修改,后台线程会定期合并这些数据片段,属于相同分区的数据片段会被合并为一个新的数据片段。

ENGINE=MergeTree()
属性名称 说明
PARTITION BY 【选填】指定分区键,不指定默认生成all的分区
ORDER BY 【必填】指定排序,默认主键与该键相同,
PRIMARY KEY 【选填】指定主键,mergeTree主键允许存在重复数据
SAMPLE BY 【选填】指定抽样规则,如果申明,主键配置也需要申明相同的表达式,需要配合SAMPLE子查询使用
SETTINGS: index_granularity 【选填】索引粒度,默认为8192,表示每隔8192行数据生成一条索引
SETTINGS: index_granularity_bytes 【选填】数据体量,用于动态划分间隔大小,默认为10M,设置为0表示不启动自适应
SETTINGS: enable_mixed_granularity_parts 【选填】指定是否开启自适应索引间隔,默认开启

存储结构

数据按照分区目录的形式存入磁盘。分为三级:数据表目录分区目录以及分区下具体的数据文件

目录 说明
checksums.txt 校验文件,保存了文件大小以及大小的哈希值,用于检验文件的完整性
columns.txt 列记录文件,记录了该分区下的数据列名称及类型
count.txt 行数记录文件,记录了该分区下的数据总行数
primary.idx 一级索引文件,用于存放稀疏索引
[Column].bin 数据文件,以字段名称.bin为名称,LZ4压缩,存储某一列的数据
[Column].mrk 列字段标记文件,记录了.bin文件中数据的偏移量信息,与.bin文件一一对应
[Column].mrk2 列字段标记文件,作用同.mrk文件,使用了自适应索引间隔的话,会生成该文件
partition.dat 记录分区表达式最终生成的值,在有分区的情况下生成
minmax_[Column].idx 记录分区字段原始数据的最大值和最小值,在有分区的情况下生成
skp_idx_[Column].idx 二级索引,也叫跳数索引,目前拥有minmax、set、ngrambf_v1和tokenbf_v1四种类型
skp_idx_[Column].mrk 二级索引的标记文件

分区规则

分区ID的生成规则:

  • 不指定分区键,为默认的all
  • 整型且无法转换为日期类型,则之间使用该整型的字符形式作为分区ID
  • 日期类型或者是能够转换为日期类型的整型,按照YYYYMMDD形式格式化之后的字符形式作为分区ID
  • 其他类型:通过128位Hash算法取其Hash值作为分区ID
  • 元组形式的多个分区字段,则每个字段按照上述规则生成分区ID,以“-”连接

命名规则

分区ID_MinBlockNum_MaxBlockNum_Level

MinBlockNum_MaxBlockNum 最小数据块编号与最大数据块编号,是自增的整型编号,新创建一个分区目录,即会累加。

Level:合并的层级,可以理解为合并的次数

合并之后的名称为,分区ID _同一分区内所有目录中最小的MinBlockNum值_同一分区内所有目录中最大的MaxBlockNum值_取同一分区内最大Level值并加1

一级索引

稀疏索引:每一行索引标记一段数据记录的起始位置

稠密索引:每一行索引标记一行数据记录的位置

稀疏索引仅需使用少量的索引标记就能够记录大量数据的区间位置信息,且数据量越大优势越为明显。

索引数据生成规则:主键字段编号1+主键字段编号2(间隔8192列)...

索引查询过程

  • 生成查询条件区间:将查询条件转换为区间
  • 递归交集判断,以递归的形式,依次对MarkRange的数值区间与条件区间做交集判断:
    • 从最大的区间开始【0,+无穷)开始,如果不存在交集,则直接通过剪枝算法优化此整段MarkRange;
    • 如果有交集,且MarkRange步长大于8(end - start),则将此区间进一步拆分成8个子区间(由merge_tree_coarse_index_granularity指定,默认值为8),并重复此规则,继续做递归交集判断。
    • 如果存在交集,且MarkRange不可再分解(步长小于8),则记录MarkRange并返回
    • 合并MarkRange区间:将最终匹配的MarkRange聚在一起,合并它们的范围。

数据存储(.bin)

一个压缩数据块由头信息和压缩数据两部分组成。头信息固定使用9位字节表示,具体由1个UInt8(1字节)整型和2个UInt32(4字节)整型组成,分别代表使用的压缩算法类型、压缩后的数据大小和压缩前的数据大小.

每个压缩数据块的体积,被限制在min_compress_block_size(65536,64K)和max_compress_block_size(默认为1048576,1M)之间.

数据标记(.mrk)

数据标记和索引区间是对齐的,均按照index_granularity的粒度间隔。一行标记数据使用一个元组表示,元组内包含两个整型数值的偏移量信息。它们分别表示在此段数据区间内,在对应的.bin压缩文件中,压缩数据块的起始偏移量;以及将该数据压缩块解压后,其未压缩数据的起始偏移量。使用LRU(最近最少使用)缓存策略加快其取用速度。

数据TTL

数据的过期时间,分为列TTL和表TTL

TTL 时间字段+INTERVAL 时间间隔 时间单位(SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR)

强制触发TTL清理命令

OPTIMIZE TABLE 表名 FINAL(如果不加FINAL,则触发一个分区合并,否则触发所有分区合并)

修改现有表或者表字段TTL

ALTER TABLE 表名 MODIFY COLUMN 字段 字段类型 TTL 时间字段+INTERVAL 时间间隔 时间单位(SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR)

表TTL

新建的时候指定即可,时间触发之后,这些时间段之后的数据将会被删除。

修改表级别的TTL

ALTER TABLE 表名 MODIFY TTL 时间字段+INTERVAL 时间间隔 时间单位(SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR)

只有在mergeTree合并分区的时候才会触发删除TTL过期数据的逻辑。

TTL默认的合并频率由MergeTree的merge_with_ttl_timeout参数控制,默认86400秒,即1天。

控制全局TTL合并任务的启停

SYSTEM START/STOP TTL MERGES

多路径存储策略

  • 默认策略:所有分区自动保存到config.xml中配置的path指定的路径下
  • JBOD策略:适合服务器挂载了多块磁盘,但没有做RAID的场景,是一种轮询的策略,每执行一次INSERT或者MERGE,所产生的新分区会轮询写入各个磁盘。
  • HOT/COLD策略:HOT区域使用SSD这类高性能存储媒介,注重存取性能;COLD区域则使用HDD这类高容量存储媒介,注重存取经济性

存储配置不支持动态更新,配置之后需要重启clickHouse-server服务。

ReplacingMergeTree【Replacing--更换】

可以在合并分区(相同的数据分区内重复数据可以删除,不同数据分区之间的重复数据不会被删除)时删除重复的数据。

ENGINE=ReplacingMergeTree(ver) // ver是选填参数,会指定一个UInt*、Date或者DateTime类型的字段作为版本号。这个参数决定了数据去重时所使用的算法

排序键ORDER BY所声明的表达式是后续作为判断数据是否重复的依据。

去重策略

  • 没有设置ver,保留同一组重复数据中的最后一条;
  • 设置了ver,保留同一组重复数据中的ver字段取值最大的那一行。

SummingMergeTree【Summing--求和】

在合并分区的时候按照预先定义的条件聚合汇总数据,将同一分组下的多行数据汇总合并成一行。

通常只会在使用SummingMergeTree或AggregatingMergeTree时才会出现ORDER BY 和PRIMARY KEY不同。因为主键与聚合的条件定义分离,要为修改聚合条件留下空间。

如果同时声明了ORDER BY与PRIMARY KEY, MergeTree会强制要求PRIMARYKEY列字段必须是ORDER BY的前缀。

修改排序键

ALTER TABLE 表名称 MODIFY ORDER BY (排序键1,排序键2...) // 适用于在现有的基础上减少字段,如果是新增排序键,则只能添加通过ALTER ADD COLUMN新增的字段

定义表引擎

ENGINE=SummingMergeTree((col1,col2...)) // col1、col2...为指定SUM汇总的列字段,如果不填,则会将所有非主键的数值类型字段进行SUM汇总。

支持嵌套类型的字段,在使用嵌套类型字段时,需要被SUM汇总的字段名称必须以Map后缀结尾。默认会以嵌套类型中第一个字段作为聚合条件Key。除第一个字段以外,任何名称以Key、Id或Type为后缀结尾的字段,都将和第一个字段一起组成复合Key。

以数据分区为单位来聚合数据。当分区合并时,同一数据分区内聚合Key相同的数据会被合并汇总,而不同分区之间的数据则不会被汇总。

在汇总数据时,同一分区内,相同聚合Key的多行数据会合并成一行。其中,汇总字段会进行SUM计算;对于那些非汇总字段,则会使用第一行数据的取值。

AggregatingMergeTree【Aggregating--聚合】

能够在合并分区的时候,按照预先定义的条件聚合数据。同时,根据预先定义的聚合函数计算数据并通过二进制的格式存入表内。

ENGINE=AggregatingMergeTree() // 何种聚合方式,针对那些列进行计算需要定义AggregateFunction函数来实现。

AggregateFunction在写入数据时,需要调用State函数;而在查询数据时,则需要调用相应的Merge函数。其中,*表示定义时使用的聚合函数

AggregatingMergeTree更为常见的应用方式是结合物化视图使用,将它作为物化视图的表引擎。

以数据分区为单位来聚合数据。当分区合并时,同一数据分区内聚合Key相同的数据会被合并计算,而不同分区之间的数据则不会被计算。

在聚合数据时,同一分区内,相同聚合Key的多行数据会合并成一行。对于那些非主键、非AggregateFunction类型字段,则会使用第一行数据的取值。

CollapsingMergeTree【Collapsing--折叠】

一种通过以增代删的思路,支持行级数据修改和删除的表引擎。

定义一个sign标记位字段,记录数据行的状态,该字段为1时,该行为有效数据,为-1时,该行需要被删除,分区合并时,在同一个数据分区中,标记为1和-1的一组数据会被抵消删除。

ENGINE=CollapsingMergeTree(sign) // sign用于指定一个Int8类型的标志位字段

修改:新增数据与源数据相同,其他字段不同

删除:新增数据与源数据相同,其他字段也相同

折叠规则

  • 如果sign=1比sign=-1的数据多一行,则保留最后一行sign=1的数据
  • 如果sign=-1比sign=1的数据多一行,则保留第一行sign=-1的数据
  • 如果sign=1和sign=-1的数据行一样多,并且最后一行是sign=1,则保留第一行sign=-1和最后一行sign=1的数据
  • 如果sign=1和sign=-1的数据行一样多,并且最后一行是sign=-1,则什么也不保留
  • 其余情况,ClickHouse会打印警告日志,但不会报错,在这种情形下,查询结果不可预知

CollapsingMergeTree对于写入数据的顺序有着严格要求,如果先sign=1,后-1,则正常折叠,否则不能正常折叠,该注意点适用单线程写入数据,不适用于多线程写入数据。

VersionedCollapsingMergeTree【VersionedCollapsing--版本折叠】

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

ENGINE=VersionedCollapsingMergeTree(sign,ver) // 指定一个UInt8类型的ver版本号

合并分区的时候,会将ver字段作为排序键。

最后修改:2021-04-26 23:45:21 © 著作权归作者所有
上一篇

评论列表

还没有人评论哦~赶快抢占沙发吧~

博客信息

  • 文章数目 12
  • 标签数目 7
  • 运行天数
  • 最后活动

广告