ClickHouse 实战:ClickHouse 高级数据类型极简教程_编程语言

1. ClickHouse高级数据类型

在上一章节中我们主要介绍了 ClickHouse 基础数据类型。

“数据类型”是编程的酵母,若是少了它,程序难以被计算机“消化”。数据类型是每一种语言都需要掌握的。我们通过“数据类型”走进丰富的程序世界。ClickHouse 中除了基础数据类型之外,还提供了数组、元组、枚举和嵌套类型,总共四种复合类型。另外,我们还可以使用聚合函数类型动态自定义类型,比如说 Bitmap 类型。这些类型通常都是其他数据库原生不具备的特性,拥有了复合类型之后,ClickHouse 的数据模型表达能力就更强了。另外,ClickHouse 还提供了丰富的函数,以实现编程级的SQL 功能。本章就来详细介绍 ClickHouse 高级数据类型。在下一章中,介绍常用函数的使用。

1.1. Array数组类型

在计算机科学中,最早的数据类型只有整数和浮点数类型,用来区分这两种数字的计算,如在 Fortran(Formula Translation,意为“公式翻译”。由约翰·巴克斯1954 年提出) 语言中。后来到了 1960 年代,数据类型中又多了结构体和记录数组。在 1970 年代,引入了几个更丰富的概念:抽象数据类型,多态泛型,模块系统和子类型等。

数组类型是最常用、最普遍的数据类型,几乎在所有程序设计语言中都支持。本节就来介绍 ClickHouse 中的数组类型。

1.1.1. 数组类型定义

数组Array(T) 是有序的元素序列,用于储存多个相同数据类型T的集合。

数组的特点:

1.数组是相同数据类型的元素的集合。

2.数组中的各元素的存储是有先后顺序的,它们在内存中按照这个先后顺序连续存放在一起。

3.数组元素用整个数组的名字和它自己在数组中的顺序位置来表示。

一般编程语言中,下标都是从 0 开始。例如,a[0]表示名字为a的数组中的第一个元素,a[1]代表数组a的第二个元素,以此类推。但是,需要特别注意的是,在 ClickHouse 中数组下标从 1 开始。也就是第一个元素是 a[1]。

1.1.2. 创建数组

创建数组

我们可以使用 array(T) 或者 [ T ] 创建一个数组。

SQL 实例:

select array(1,2,3) as a1, [4,5,6] as a2 Format Vertical

输出:

a1: [1,2,3]

a2: [4,5,6]

数组中的数据类型

当数组中的元素都不为空,数组类型就是不可空的。

SQL 实例:

SELECT

    [1, 2, 3] AS a,

    toTypeName(a)

FORMAT Vertical

输出:

a:                          [1,2,3]

toTypeName(array(1, 2, 3)): Array(UInt8)

如果数组中元素有一个NULL空值,类型会相应地变成可空元素类型。

SQL 实例:

SELECT

    [1, 2, NULL] AS x,

    toTypeName(x)

FORMAT Vertical

输出:

x:                             [1,2,NULL]

toTypeName(array(1, 2, NULL)): Array(Nullable(UInt8))

如果创建了不兼容的数据类型数组,ClickHouse 将引发异常:

:) select [1,'a']

Received exception from server (version 21.12.1):

Code: 386. DB::Exception: Received from localhost:9000. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not: While processing [1, 'a']. (NO_COMMON_TYPE)

1.1.3. 数组基础操作

判断数组是否为空

函数:empty(array)

功能:判断数组 array 是否为空。

返回值:1:空,0:非空(UInt8类型)。

SQL 实例:

SELECT

    empty([]) AS isEmpty,

    empty([1]) AS notEmpty

FORMAT Vertical

输出:

isEmpty:  1

notEmpty: 0

还有一个 notEmpty()函数,逻辑相反,此处不赘述。

计算数组长度

函数:length(array)

功能:计算数组元素个数。

返回值:数组长度,类型为UInt64。空数组返回 0,NULL 值返回 NULL。该函数同样适用于计算 String 字符串的长度。

SQL 实例:

SELECT

    length([1, 2, 3]) AS a1,

    length([]) AS a2,

    length(NULL) AS a3

FORMAT Vertical

输出:

a1: 3

a2: 0

a3: ᴺᵁᴸᴸ

获取数组元素

直接使用 a[i] 下标访问数组元素,需要注意的是,i 是从 1 开始,也就是说,数组 a 中第一个元素是 a[1]。

实例 SQL:

SELECT

    [1, 2, 3] AS x, --- 等价于array(1,2,3)

    toTypeName(x),

    length(x) AS size,

    x[0] AS x0, ---错误用法,ClickHouse 数组元素下标是从 1 开始的

    x[1] AS x1,

    x[2] AS x2,

    x[3] AS x3

FORMAT Vertical

输出:

x:                          [1,2,3]

toTypeName(array(1, 2, 3)): Array(UInt8)

size:                       3

x0:                         0

x1:                         1

x2:                         2

x3:                         3

ClickHouse 以最小存储代价为原则,进行类型推断。所以,我们看到上面的 array(1, 2, 3) 的类型是Array(UInt8)。

判断某个元素是否存在

函数:has(arr, x)

功能说明:判断元素 x 是否在数组 arr 中存在。

返回值:1: 存在, 0: 不存在。

SQL实例:

SELECT has([1, 2, 3], 1) AS hasIt

输出:

┌─hasIt─┐

│     1    │

└─────┘

数组切片

函数:arraySlice(array, offset[, length])

参数功能说明:

array:数组.

offset:标偏移量。正数表示从左数,负数表示从右数。记住,ClickHouse 中数组的下标是从 1 开始的。

length: 切片长度。不填,默认就是从偏移量到最后一个元素。

返回值:返回一个子数组。

SQL 实例:

SELECT

    arraySlice([10, 20, 30, 40, 50], 1, 2) AS res1,

    arraySlice([10, 20, 30, 40, 50], 1) AS res2

FORMAT Vertical

输出:

res1: [10,20]

res2: [10,20,30,40,50]

数组元素展开 

函数:arrayJoin(arr)

功能说明:使用arrayJoin(arr)函数,可以把数组arr中的每一个元素,展开到多行(unfold)。具体讲,就是 arrayJoin(arr) 函数以Array类型数据arr作为输入,然后对数组中的数据进行迭代,返回多行结果,一行一个数组元素值。简单说,就是“行转列”,把一个数组“炸开”到一列中的多行中去。跟 Hive 中的 explode() 函数功能类似。这么说有点抽象,直接用下面的例子来说明。

SQL 实例:

SELECT

    arrayJoin([1, 2, 3] AS src) AS element,

    src,

    'a'

输出:

┌─element┬─src─┬─'a'─┐

│       1 │ [1,2,3] │ a   │

│       2 │ [1,2,3] │ a   │

│       3 │ [1,2,3] │ a   │

└─────┴─────┴───┘

数组元素去重

函数:arrayDistinct(arr)

功能说明:从数组中删除所有重复的元素。

返回值: 删除所有重复元素之后的子数组。

SQL 实例:

SELECT arrayDistinct([1, 1, nan, nan, 2, 3, 3, 3, NULL, NULL, 3, 4, 5, 6, 6, 6]) AS c

FORMAT Vertical

输出:

c: [1,nan,2,3,4,5,6]

删除连续重复元素

函数:arrayCompact(arr)

功能说明:从数组中删除连续重复的元素。结果值的顺序由源数组中的顺序决定。

返回值: 删除掉连续重复元素之后的子数组。

SQL 实例:

SELECT arrayCompact([1, 1, nan, nan, 2, 3, 3, 3, NULL, NULL, 3, 4, 5, 6, 6, 6]) AS c

FORMAT Vertical

输出:

c: [1,nan,2,3,NULL,3,4,5,6]

连接多个数组

函数:arrayConcat(arr1,arr2,...)

功能说明:连接多个数组为一个数组。

返回值:连接之后的数组(不会去重)。

SQL 实例:

SELECT arrayConcat([1, 2], [3, 4], [5, 6, 2, 3]) AS res

输出:

┌─res───────────────┐

│ [1,2,3,4,5,6,2,3]              │

└──────────────────┘

如果想要去重,可以嵌套使用arrayDistinct()函数,如下所示。

SQL 实例:

SELECT arrayDistinct(arrayConcat([1, 2], [3, 4], [5, 6, 2, 3])) AS res

输出:

┌─res───────────┐

│ [1,2,3,4,5,6]           │

└──────────────┘

数组倒序

函数:arrayReverse(arr)

功能说明:数组逆序。

返回值:输入数组元素逆序之后的新数组。

SQL 实例:

SELECT arrayReverse([1, 2, 3])

FORMAT Vertical

输出:

arrayReverse([1, 2, 3]): [3,2,1]

数组拍平

函数: arrayFlatten(arr1,arr2,...)

功能说明:将多维数组元素拍平到一个一维数组。适用于任何深度的嵌套数组。一维数组拍平还是一维数组。

返回值:拍平之后一维数组,包含所有源数组的所有元素。

SQL 实例:

SELECT

    arrayFlatten([[[1]], [[2], [3, 4, 5]]] AS src) AS flatArr,

    src

FORMAT Vertical

输出:

flatArr: [1,2,3,4,5]

src:     [[[1]],[[2],[3,4,5]]]

数组元素映射

函数:arrayMap(func, arr1, …)

功能说明:对数组 arr1中的每个元素应用函数 func 计算成新值,然后返回一个新数组。

返回值:新值数组。

SQL 实例:

SELECT arrayMap(x -> (x * x), [1, 2, 3]) AS res

FORMAT Vertical

输出:

res: [1,4,9]

需要注意的是,arrayMap()是一个高阶函数,lambda函数是一个必传参数。

数组元素过滤

函数:arrayFilter(func, arr1,...)

功能说明:根据谓词判断函数 func,过滤出满足条件的数组arr1 中的元素。

返回值:满足条件的子数组。如果没有元素满足条件,返回空数组[]。

SQL 实例:

SELECT arrayFilter(x -> ((x % 2) = 0), [1, 2, 3, 4, 5, 6]) AS res

FORMAT Vertical

输出:

res: [2,4,6]

SQL 实例:

SELECT arrayFilter(x -> (x > 10), [1, 2, 3, 4, 5, 6]) AS res

FORMAT Vertical

输出:

res: []

数组聚合分析

函数:arrayReduce(agg_func, arr1, arr2, ..., arrN)

功能说明:将聚合函数agg_func应用于数组元素并返回其结果。聚合函数的名称以单引号'max'、'sum' 的形式作为字符串传递。当使用带参数的聚合函数时,参数放到括号里,例如 'uniqCombined(17)'。

返回值:聚合结果值,类型为UInt64。

SQL 实例:

SELECT arrayReduce('uniq', [1, 1, 2, 2, 3, 4, 5, 6, 6, 7])

输出: 7

SQL 实例:

SELECT

    arrayReduce('uniqCombined(17)', [1, 1, 2, 2, 3, 4, 5, 6, 6, 7]) AS res,

    toTypeName(res) AS t

FORMAT Vertical

输出:

res: 7

t:  UInt64

计算数组交集

函数:arrayIntersect(arr1,arr2,...)

功能说明:计算 arr1,arr2等数组元素交集。

返回值:交集元素子数组,结果去重。

SQL 实例:

SELECT arrayIntersect([1, 2, 3, 3], [4, 5, 6])          AS noIntersect,

        arrayIntersect([1, 2, 3, 3], [2, 2, 3, 4, 5, 6]) AS hasIntersect

FORMAT Vertical

输出:

noIntersect:  []

hasIntersect: [3,2]

计算数组并集

组合使用函数来实现arrayDistinct(arrayConcat(a, b))。

SQL 实例:

SELECT

    [1, 2] AS a,

    [2, 3] AS b,

    arrayDistinct(arrayConcat(a, b)) AS res

FORMAT Vertical

输出:

a:   [1,2]

b:   [2,3]

res: [1,2,3]

计算数组差集

差集的实现要有一些技术含量了(感觉 ClickHouse 后面应该内置数组差集计算函数,实现类似arrayExcept() 函数),需要使用数组交集函数arrayIntersect() 结合高阶函数 arrayMap()和 arrayFilter()来组合实现。

SQL 实例:

SELECT

    arrayIntersect([1, 2, 3], [4, 5, 6]) AS noIntersect,

    arrayIntersect([1, 2, 3], [2, 3, 4, 5, 6]) AS hasIntersect

FORMAT Vertical

SELECT

    [1, 2] AS a,

    [2, 3] AS b,

    arrayFilter(x -> (x IS NOT NULL), arrayMap(x -> multiIf(x NOT IN arrayIntersect(a, b), x, NULL), a)) AS res

FORMAT Vertical

输出:

a:   [1,2]

b:   [2,3]

res: [1]

另外, ClickHouse 中有集合交(INTERSECT)、并(UNION)、差(EXCEPT)的SQL子句关键字,可以实现数组的交并差运算。实例 SQL 如下。

交集SQL

SELECT a.i

FROM

(

    SELECT arrayJoin([1, 2]) AS i

) AS a

INTERSECT

SELECT b.i

FROM

(

    SELECT arrayJoin([2, 3]) AS i

) AS b

输出:2

并集 SQL

SET union_default_mode = 'ALL';

SELECT DISTINCT t.i

FROM

(

    SELECT a.i

    FROM

    (

        SELECT arrayJoin([1, 2]) AS i

    ) AS a

    UNION

    SELECT b.i

    FROM

    (

        SELECT arrayJoin([2, 3]) AS i

    ) AS b

) AS t

输出:

1

2

3

差集 SQL

SELECT a.i

FROM

(

    SELECT arrayJoin([1, 2]) AS i

) AS a

EXCEPT

SELECT b.i

FROM

(

    SELECT arrayJoin([2, 3]) AS i

) AS b

输出:1

Ø Tips:ClickHouse 系统内置聚合函数

我们可以使用下面的 SQL 列出 ClickHouse 中聚合函数清单:

SELECT

    name,

    is_aggregate,

    case_insensitive,

    alias_to

FROM system.functions

WHERE is_aggregate = 1

输出:

name

is_aggregate

case_insensitive

alias_to

lagInFrame

1

0


dense_rank

1

1


rank

1

1


exponentialMovingAverage

1

0


sparkbar

1

0


singleValueOrNull

1

0


studentTTest

1

0


rankCorr

1

0


aggThrow

1

0


categoricalInformationValue

1

0


groupArrayMovingAvg

1

0


groupArrayMovingSum

1

0


simpleLinearRegression

1

0


entropy

1

0


quantilesBFloat16

1

0


maxIntersectionsPosition

1

0


groupBitmapXor

1

0


groupBitmap

1

0


skewPop

1

0


groupBitXor

1

0


groupBitOr

1

0


groupBitmapAnd

1

0


topKWeighted

1

0


stochasticLinearRegression

1

0


corr

1

1


uniqCombined64

1

0


intervalLengthSum

1

0


uniqCombined

1

0


quantileExactWeighted

1

0


sumMapFilteredWithOverflow

1

0


sumMapFiltered

1

0


minMappedArrays

1

0


sumMappedArrays

1

0


histogram

1

0


quantiles

1

0


sum

1

1


covarPop

1

0


row_number

1

1


kurtPop

1

0


kurtSamp

1

0


skewSamp

1

0


uniqExact

1

0


sumMapWithOverflow

1

0


stddevSamp

1

0


varPop

1

0


corrStable

1

0


quantileTimingWeighted

1

0


covarPopStable

1

0


stddevSampStable

1

0


varSamp

1

0


topK

1

0


last_value

1

1


mannWhitneyUTest

1

0


maxIntersections

1

0


quantilesExact

1

0


uniqHLL12

1

0


quantileBFloat16

1

0


uniq

1

0


min

1

1


sequenceNextNode

1

0


quantilesTimingWeighted

1

0


boundingRatio

1

0


any

1

0


anyLast

1

0


deltaSum

1

0


retention

1

0


sequenceMatch

1

0


uniqUpTo

1

0


windowFunnel

1

0


deltaSumTimestamp

1

0


varSampStable

1

0


uniqTheta

1

0


quantilesExactWeighted

1

0


max

1

1


quantilesBFloat16Weighted

1

0


quantileBFloat16Weighted

1

0


sumKahan

1

0


quantilesTDigestWeighted

1

0


groupBitAnd

1

0


quantileTDigest

1

0


quantileTDigestWeighted

1

0


argMax

1

0


quantileDeterministic

1

0


quantilesTDigest

1

0


stochasticLogisticRegression

1

0


argMin

1

0


avg

1

1


covarSampStable

1

0


quantilesTiming

1

0


welchTTest

1

0


covarSamp

1

0


varPopStable

1

0


quantileTiming

1

0


quantileExactInclusive

1

0


quantileExactHigh

1

0


groupArraySample

1

0


quantilesExactLow

1

0


groupBitmapOr

1

0


first_value

1

1


quantileExactExclusive

1

0


quantileExact

1

0


sumCount

1

0


groupArrayInsertAt

1

0


quantilesExactHigh

1

0


sumWithOverflow

1

0


sequenceCount

1

0


quantilesDeterministic

1

0


groupUniqArray

1

0


groupArray

1

0


anyHeavy

1

0


maxMappedArrays

1

0


stddevPop

1

0


quantile

1

0


leadInFrame

1

0


quantilesExactExclusive

1

0


count

1

1


quantilesExactInclusive

1

0


stddevPopStable

1

0


quantileExactLow

1

0


avgWeighted

1

0


BIT_AND

1

1

groupBitAnd

VAR_SAMP

1

1

varSamp

COVAR_SAMP

1

1

covarSamp

VAR_POP

1

1

varPop

medianTDigest

1

0

quantileTDigest

medianBFloat16

1

0

quantileBFloat16

medianTimingWeighted

1

0

quantileTimingWeighted

medianTiming

1

0

quantileTiming

medianExactHigh

1

0

quantileExactHigh

BIT_OR

1

1

groupBitOr

medianDeterministic

1

0

quantileDeterministic

STDDEV_POP

1

1

stddevPop

STDDEV_SAMP

1

1

stddevSamp

medianExactLow

1

0

quantileExactLow

medianTDigestWeighted

1

0

quantileTDigestWeighted

medianExact

1

0

quantileExact

COVAR_POP

1

1

covarPop

medianBFloat16Weighted

1

0

quantileBFloat16Weighted

medianExactWeighted

1

0

quantileExactWeighted

BIT_XOR

1

1

groupBitXor

median

1

0

quantile

1.2. Tuple元组类型

在数学中,元组是元素的有限有序列表(序列)。n元组是n 个元素的序列。例如,(4, 2, 8, 5, 7) 表示一个 5 元组。在计算机科学(特别是在程序设计语言和数据库的关系模型),多元组通常被定义为从字段名到特定值的有限函数(值组)。其目的和在数学中一样,就是指出特定的实体。在计算机编程语言类型理论中,元组具有乘积类型,而且是固定的长度,每个元素具备固定的类型。形式化表达就是:

本节主要介绍 ClickHouse 中的元组数据类型。

1.2.1. 元组定义

元组(tuple)其实就是一组数据元素的序列,每个元素都可以有自己独立的类型。

元组是关系数据库中的基本概念:关系是一张表,表中的每行(即数据库中的每条记录)就是一个元组,每列就是一个属性。 在二维表里,元组也称为行。笛卡尔积中每一个元素(d1,d2,…,dn),叫作一个n元组(n-tuple)或简称元组。当关系是一张表,二维表中的行表中的每行(即数据库中的每条记录)就是一个元组,每列就是一个属性。在二维表里,元组也称为记录。

1.2.2. 创建元组

可以使用函数 tuple(T1, T2, ...) 来创建元组。

创建元组的示例:

SELECT tuple(1,'a',NULL) AS x, toTypeName(x) as tp Format Vertical

输出:

SELECT

    (1, 'a', NULL) AS x,

    toTypeName(x) AS tp

FORMAT Vertical

x:  (1,'a',NULL)

tp: Tuple(UInt8, String, Nullable(Nothing))

从上面的输出结果中可以看出,tuple()函数创建与使用圆括号创建元组等价。

实例 SQL:

SELECT

    (1, 2, 'a', 0.1, -0.9, NULL) AS x,

    toTypeName(x) AS tp

FORMAT Vertical

输出:

x:  (1,2,'a',0.1,-0.9,NULL)

tp: Tuple(UInt8, UInt8, String, Float64, Float64, Nullable(Nothing))

在动态创建元组时,ClickHouse会进行类型推断,自动将每个参数的类型赋值为可以存储该参数值的最小类型。如果参数为NULL,那么这个元组元素的类型为Nullable。

元组中的元素可以嵌套元组,例如:

SELECT

    (1, 2, tuple(3)) AS x,

    toTypeName(x) AS tp

FORMAT Vertical

输出:

x:  (1,2,(3))

tp: Tuple(UInt8, UInt8, Tuple(UInt8))

1.2.3. 使用元组

元组可以用在分区键、排序键中。例如:ORDER BY (CounterlD, EventDate) 。元组通常用作IN运算符参数,或创建lambda函数形参列表。

获取元组中的元素

函数:tupleElement(tuple, n)

功能:获取元组中元素的下标为 n的元素。下标从 1 开始。

对应的函数是实例 SQL:

select (1,'a',3,4) as tpl, tupleElement(tpl,2) as t2 Format Vertical

输出:

SELECT

    (1, 'a', 3, 4) AS tpl,

    tpl.2 AS t2

FORMAT Vertical

tpl: (1,'a',3,4)

t2:  a

从上面的输出我们可以看到 tpl.2 这个运算符。tupleElement(x,N) 等价于 x.N,该函数实现了算子 x.N。

还有一个 untuple(tuple) 函数,解开元组中所有元素:

SELECT

    (1, 'a', 3, 4) AS tpl,

    untuple(tpl)

FORMAT Vertical

输出:

tpl: (1,'a',3,4)

tupleElement((1, 'a', 3, 4), 1): 1

tupleElement((1, 'a', 3, 4), 2): a

tupleElement((1, 'a', 3, 4), 3): 3

tupleElement((1, 'a', 3, 4), 4): 4

元组支持写入表

元组在早期的 ClickHouse版本中,由于复杂数据类型的序列化/反序列化功能未实现,不支持写入到表(内存表除外)中。但是,当前最新版本已经支持了。实例如下。

(1)创建一张简单的表:

CREATE TABLE t1

(

    `a` Date,

    `b` UInt8,

    `c` Tuple(UInt8, String)

)

ENGINE = MergeTree(a, b, 8192)

(2)往表里插入 Tuple 类型的数据:

insert into t1(a,b,c)

values(now(), 1, (1,'a'));

(3)然后,查询表数据:

SELECT

    a,

    b,

    c,

    toTypeName(a) AS at,

    toTypeName(b) AS bt,

    toTypeName(c) AS ct

FROM t1

FORMAT Vertical

输出:

a:  2022-03-04

b:  1

c:  (1,'a')

at: Date

bt: UInt8

ct: Tuple(UInt8, String)

1.3. Nested 嵌套数据类型

大部分程序设计语言都提供各种方法,实现基于基础数据类型,构建出复合数据类型。嵌套结构在关系数据库管理系统中并不常见。通常,它只是一张平的表。ClickHouse 提供了一种灵活的数据存储方式。虽然是列式数据库,但它可以实现较底层的结构化,并提供各种函数来提取和聚合数据。ClickHouse支持嵌套数据类型。一张表中可以包含任意多个嵌套数据结构的列,但该嵌套列仅支持一级嵌套。ClickHouse 中的嵌套类型是一个典型的列存多维数组模式(参见第 1 章“1.2.1深度列存储”中关于列存储的数据结构的介绍)。

本节就来介绍 ClickHouse 的嵌套数据类型。

1.3.1. 嵌套类型定义

在ClickHouse中存储嵌套数据,通常有如下用两种方式:

1.存 JSON 格式的String类型(使用JSONExtract(json[, indices_or_keys…], Return_type) 函数操作数据,但是性能可能要差)

2.使用嵌套数据类型(Nested Data Type)。

ClickHouse 嵌套数据类型的关键字是 Nested,只支持一级嵌套。数据结构类似于表格单元格里面嵌套“一张表格”。如下所示:

ID:UInt64

Name:String

AcademicPerformance: Nested

(course:String,  score:Float64)

1

皓轩

2

语轩

语文

95.5

数学

97

科学

99

1.3.2. 创建嵌套类型

在一张表里定义一个嵌套数据类型字段,指定该字段类型是 Nested。上节中的表格数据,如果存储到 ClickHouse 一张带嵌套数据类型字段的表 student_grades 里,我们可以用下面的 SQL 来建表:

CREATE TABLE mydb.student_grades

(

    ID UInt64,

    Name String,

    AcademicPerformance Nested

    (

        course String,

        score Float64

    )

) ENGINE = MergeTree()

ORDER BY (ID,Name);

1.3.3. 嵌套类型的使用

往表里插入两条数据:

insert into mydb.student_grades (ID, Name, `AcademicPerformance.course`, `AcademicPerformance.score`)

VALUES (1, '皓轩', ['语文','数学','科学'], [95.5,97,99]),

       (2, '语轩', ['语文','数学','科学'], [95,100,98]);

查询结果:

SELECT t.*

FROM mydb.student_grades AS t

输出:

┌ID┬Name┬AcademicPerformance.course┬AcademicPerformance.score┐

│1 │ 皓轩 │['语文','数学','科学']      │ [95.5,97,99]              │

│2 │ 语轩 │['语文','数学','科学']      │ [95,100,98]               │

└─┴──┴────────────────┴───────────────┘

比如说,计算两位同学的学科平均分,可以使用 arrayReduce 函数,SQL 如下:

SELECT

    t.Name,

    toTypeName(t.AcademicPerformance.score),

    arrayReduce('avg', t.AcademicPerformance.score)

FROM mydb.student_grades AS t

FORMAT Vertical

输出:

Row 1:

──────

Name:                                          皓轩

toTypeName(AcademicPerformance.score):         Array(Float64)

arrayReduce('avg', AcademicPerformance.score): 97.16666666666667

Row 2:

──────

Name:                                          语轩

toTypeName(AcademicPerformance.score):         Array(Float64)

arrayReduce('avg', AcademicPerformance.score): 97.66666666666667

需要注意的是,AcademicPerformance.score 字段的类型是 Array。嵌套类型是列式存储,本质上是一个多维数组的结构。

1.4. Map数据类型

一切皆是映射。曾有人说,如果世界上只有一种数据结构,那么我选择HashMap。数组本质上是 key 值顺序递增的 Map。本节介绍 ClickHouse 中的 Map 数据类型。

1.4.1. Map 类型定义

Map是由键值对(key:value Pairs)组成的无序集合,类似于其它语言中的字典或HashMap。

语法:Map(key, value)

功能说明:

key:键,数据类型可以是字符串String、整型Int系列、LowCardinality 或 FixedString等。

value: 值,数据类型可以是字符串String、整型Int系列、 数组 Array、LowCardinality 或 FixedString等。

1.4.2. 创建 Map 数据类型

下面创建一张带有 Map 数据类型字段的表。SQL如下:

CREATE TABLE t_map

(

    `c` Map(String, UInt64)

)

ENGINE = Memory

我们可以使用 map(k1,v1,k2,v2,...) 函数,构建一个具体的 Map 实例值,例如,往表里插入1 行数据,SQL 如下:

INSERT INTO t_map VALUES (map('k1', 1, 'k2', 2, 'k3', 3));

也可以使用花括号 {‘key1’:value1, ‘key2’:value2,...} 的方式构建 Map 实例值,SQL实例如下:

INSERT INTO t_map VALUES ({'k4':4,'k5':5}), ({'k6': 6});

1.4.3. Map常用操作

查询Map数据

SELECT *

FROM t_map

FORMAT Vertical

输出:

Row 1:

──────

c: {'k1':1,'k2':2,'k3':3}

Row 2:

──────

c: {'k4':4,'k5':5}

Row 3:

──────

c: {'k6':6}

通常情况下,我们不直接这么查询,因为 scan 的数据量比较大,性能较差。常用策略是适当选择 Map 中的主 key,单独放到一列中做索引,通过最左匹配原则,先过滤出最小集,再通过 Map 中的其他 key 进一步过滤,从而实现高性能查询。

访问Map 中的 key 值

假设,我们有一个列名为 c 的 Map 数据类型字段,那么在 ClickHouse 中使用c['key1'] 语法获取键key1对应的 value 值,该操作的复杂度是线性的。

假设当前表 t_map 中的值如下:

c: Map(String,UInt64)

{'k1':1,'k2':2,'k3':3}

{'k4':4}

{'k5':5}

{'k4':4,'k5':5}

{'k6':6}

此时,我们访问 c 列中键为 k1的数据行,SQL 如下:

SELECT c['k1']

FROM t_map

FORMAT Vertical

输出:

Row 1:

──────

arrayElement(c, 'k1'): 1

Row 2:

──────

arrayElement(c, 'k1'): 0

Row 3:

──────

arrayElement(c, 'k1'): 0

Row 4:

──────

arrayElement(c, 'k1'): 0

Row 5:

──────

arrayElement(c, 'k1'): 0

可以发现,输出了 5 行记录。而实际上,我们只需要第 1 行有效记录。因为其他的数据行中根本就没有键为k1 的记录。但是,却作为 value = 0 而返回了。因为,我们建表的时候,c 字段的类型是 `c` Map(String, UInt64) , value 值是不可空的,故被填充了默认值 0。这种情况下,就需要根据实际的业务场景来决定默认值是否能用 0 来填充了,还是设置成 Nullable 类型。

获取所有 key

SELECT c.keys

FROM t_map

输出:

┌─c.keys─────┐

│['k1','k2','k3']│

└─────────┘

获取所有 value

SELECT c.values

FROM t_map

输出:

┌c.values─┐

│ [1,2,3]  │

└─────┘

mapContains

函数:mapContains(map, key)

功能说明:判断 map 中是否有对应 key 记录。

返回值:UInt8类型,1 - 包含, 0 - 不包含。

实例 SQL:

SELECT mapContains(c, 'k3')

FROM t_map

FORMAT Vertical

输出:

Row 1:

──────

mapContains(c, 'k3'): 1

Row 2:

──────

mapContains(c, 'k3'): 0

Row 3:

──────

mapContains(c, 'k3'): 0

Row 4:

──────

mapContains(c, 'k3'): 0

Row 5:

──────

mapContains(c, 'k3'): 0

mapContainsKeyLike

函数:mapContainsKeyLike(map, pattern)

功能说明:map - Map 类型字段,pattern - 字符串匹配模式

返回值: UInt8类型,1 - 包含, 0 - 不包含。

实例 SQL:

SELECT mapContainsKeyLike(c, 'k%')

FROM t_map

FORMAT Vertical

输出:

Row 1:

──────

mapContainsKeyLike(c, 'k%'): 1

Row 2:

──────

mapContainsKeyLike(c, 'k%'): 1

Row 3:

──────

mapContainsKeyLike(c, 'k%'): 1

Row 4:

──────

mapContainsKeyLike(c, 'k%'): 1

Row 5:

──────

mapContainsKeyLike(c, 'k%'): 1

Tuple转Map

可以使用CAST 函数,把一个 Tuple(keyArray, valueArray) 转成一个 Map,实例 SQL:

SELECT CAST(([1, 2, 3], ['One', 'Two', 'Three']), 'Map(UInt8, String)') AS map

输出:

┌─map────────┐

│{1:'One',2:'Two',3:'Three'}│

└───────────┘

1.5. Nullable类型

ClickHouse支持Nullable类型,该类型允许用NULL来表示缺失值。Nullable字段不能作为索引列使用,在ClickHouse的表中存储Nullable列时,会对性能产生一定影响。

默认情况下,字段是不允许为NULL的。例如有个Int64类型的字段,在插入数据时有可能为NULL,需要将字段类型声明为Nullable(Int64)。

例如,我们创建一张表,表里有 1 个Map类型的字段 c,key 为 String 不可空,value为 UInt64可空,SQL 如下:

CREATE TABLE default.t_map_nullable

(

    `c` Map(String, Nullable(UInt64))

)

ENGINE = Memory

插入3 行数据:

INSERT INTO default.t_map_nullable (c)

VALUES (map('k1', 1, 'k2', 2, 'k3', 3)),

       (map('k2', 7, 'k3', 8)),

       (map('k3', 5, 'k5', 10));

查询k2值记录:

SELECT t.c['k2'] AS v2

FROM default.t_map_nullable AS t

输出:

┌─v2─┐

│    2 │

│    7 │

│   ᴺᵁᴸᴸ│

└───┘

3 rows in set. Elapsed: 0.002 sec.

过滤掉NULL 值记录:

SELECT t.c['k2'] AS v2

FROM default.t_map_nullable AS t

WHERE v2 IS NOT NULL

输出:

┌─v2┐

│  2 │

│  7 │

└──┘

1.6. 聚合函数类型

聚合函数: 顾名思义就是对一组数据执行聚合计算并返回结果的函数。这类函数在数据库之中很常见,如:count, max, min, sum等。ClickHouse 中专门提供了AggregateFunction数据类型,用于在数据库中存储聚合状态,以提供更好的性能和更加灵活的数据处理方式。AggregateFunction类型的字段使用二进制存储。在写入数据时,需要调用State函数(例如,与uniq、sum对应的uniqState和sumState函数); 而在查询数据时,则需要调用相应的Merge函数(例如,与uniq、sum对应的uniqMerge、sumMerge函数)。AggregateFunction底层的原理是预计算(数据立方体的方案,以空间换时间),也就是每写入一批数据,trigger就会触发一次计算结果更新视图。所以在海量数据的场景下这种查询效率也是非常高的。

1.6.1. 聚合函数类型定义

函数:AggregateFunction(func_name,types_of_arguments,...)

功能说明:用于存储聚合函数的中间状态。通过聚合函数名称加-State后缀的形式写入数据。与此同时,以相同的聚合函数名加-Merge后缀的形式来读取最终状态数据。

为了演示聚合函数的使用,我们去下载 ClickHouse 官网提供的测试数据包:

​https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz​

​https://datasets.clickhouse.com/visits/tsv/visits_v1.tsv.xz​

然后,通过命令行导入数据到数据表里:

clickhouse client --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" --max_insert_block_size=100000 < hits_v1.tsv

clickhouse client --query "INSERT INTO tutorial.visits_v1 FORMAT TSV" --max_insert_block_size=100000 < visits_v1.tsv

上述详细步骤,参考:https://clickhouse.com/docs/en/getting-started/tutorial/#import-sample-dataset

1.6.2. 使用-State函数聚合物化视图指标

ClickHouse支持物化视图(Materialized View)。物化视图,说白了,就是物理表,只不过这张表通过数据库的内部机制可以定期更新,将一些大的耗时的表连接用物化视图实现,实现高性能查询。

首先,我们基于tutorial.hits_v1物理源表创建一张物化视图指标表。假设我们需要分析每天的请求总数和独立用户数,那么这个物化视图的数据表结构如下:

CREATE MATERIALIZED VIEW tutorial.hit_event

ENGINE = AggregatingMergeTree()

PARTITION BY EventDate

ORDER BY (CounterID, EventDate)

POPULATE

AS

SELECT CounterID,

       EventDate,

       sumState(RequestNum) AS RequestNums, --使用 sumState 函数写入时聚合函数类型字段值

       uniqState(UserID)    AS Users        --使用 uniqState 函数写入时聚合函数类型字段值

FROM tutorial.hits_v1

GROUP BY CounterID, EventDate;

其中,RequestNums字段类型是:AggregateFunction(sum, UInt32),Users字段类型是AggregateFunction(uniq, UInt64)。

另外,POPULATE 修饰符指定物化视图创建的过程中,直接同步好源表数据。如果不使用POPULATE 修饰符,那么刚建好的物化视图中无数据,源表后面再写入的数据,才会同步到物化视图表中。源表数据删除,物化视图的数据不会同步删除,仍然保留。

我们的tutorial.hit_event 物化视图已经创建成功,可以在schemas 目录下面,数据库实例 tutorial 的 materialized views 目录下面看到对应的视图结构 tutorial.hit_event ,另外,还发现在tables 目录下面有对应的一张.inner_id.xxxx 表,模型结构完全相同:

ClickHouse 实战:ClickHouse 高级数据类型极简教程_python_02

对应到磁盘文件目录结构如下:

ClickHouse 实战:ClickHouse 高级数据类型极简教程_数据库_03

物化视图本质上是一张特殊的内置数据表。使用 SHOW TABLES 命令查看数据库下面的所有表中,可以看到:

SHOW TABLES

┌─name─────────────────────────┐

.inner_id.9c72d8c8-05fd-4bc0-9c72-d8c805fdcbc0 │

hit_event                                              │

│ hits_v1                                                │

│ visits_v1                                              │

└─────────────────────────────┘

去 ClickHouse 的数据文件目录下面可以找到这张物化视图表,目录如下:

ClickHouse 实战:ClickHouse 高级数据类型极简教程_数据库_04

其中,columns.txt 文件内容如下:

columns format version: 1

4 columns:

`CounterID` UInt32

`EventDate` Date

`RequestNums` AggregateFunction(sum, UInt32)

`Users` AggregateFunction(uniq, UInt64)

1.6.3. 使用-Merge 函数读取聚合结果值

查询聚合指标结果。

SQL 实例:

SELECT

    EventDate,

    sumMerge(RequestNums) AS RequestNums,

    uniqMerge(Users) AS Users

FROM tutorial.hit_event

GROUP BY EventDate

ORDER BY EventDate ASC

输出:

┌─EventDate┬─RequestNums┬─Users─┐

│ 2014-03-17 │  4376297862 │ 36613 │

│ 2014-03-18 │  4041848250 │ 36531 │

│ 2014-03-19 │  3570583458 │ 36940 │

│ 2014-03-20 │  3965384078 │ 36462 │

│ 2014-03-21 │  3769787648 │ 35447 │

│ 2014-03-22 │  2388852824 │ 31555 │

│ 2014-03-23 │  2260252282 │ 31200 │

└───────┴───────┴─────┘

7 rows in set. Elapsed: 0.049 sec. Processed 239.80 thousand rows, 36.76 MB (4.92 million rows/s., 754.97 MB/s.)

上面的 SQL 计算结果值,跟直接在  源表上使用 sum(RequestNum),uniq(UserID)  查询的结果是相同的。但是,我们关注的是性能数据。实例如下。

SQL 实例:

SELECT

    EventDate,

    sum(RequestNum) AS RequestNums,

    uniq(UserID) AS Users

FROM tutorial.hits_v1

GROUP BY EventDate

输出:

┌EventDate─┬─RequestNums┬─Users─┐

│ 2014-03-17 │  4376297862 │ 36613 │

│ 2014-03-18 │  4041848250 │ 36531 │

│ 2014-03-19 │  3570583458 │ 36940 │

│ 2014-03-20 │  3965384078 │ 36462 │

│ 2014-03-21 │  3769787648 │ 35447 │

│ 2014-03-22 │  2388852824 │ 31555 │

│ 2014-03-23 │  2260252282 │ 31200 │

└───────┴────────┴────┘

7 rows in set. Elapsed: 0.117 sec. Processed 17.75 million rows, 248.47 MB (151.62 million rows/s., 2.12 GB/s.)

性能数据对比表如下:

对比项

源表计算

物化视图+聚合函数

倍数

处理数据行数

17750000

239800

74.0

处理数据大小(MB)

248.47

36.76

6.8

时间消耗(s)

0.117

0.049

2.4

在实践中,我们通常使用 AggregatingMergeTree 表引擎的物化视图结合聚合函数类型AggregateFunction 来实现预聚合指标的计算,从而实现高性能 OLAP 分析。

1.7. LowCardinality低基数类型

1.7.1. 为什么叫低基数?

1.7.2. 创建低基数类型

1.7.3. 低基数类型常用操作

1.7.4. 低基数计算原理

1.8. Bitmap数据类型

在计算机科学中,位图(Bitmap)是从某个域(例如,整数范围)到位的映射。它也称为位数组或位图索引。位图思想应用广泛,比如Linux内核(如inode,磁盘块)、Bloom Filter算法等,其优势是可以在一个非常高的空间利用率下保存大量0-1状态,进而实现大数据量的高效内存计算。

本节介绍 ClickHouse 中的 Bitmap 数据类型的使用。

1.8.1. Bitmap简介

位图数据结构的思想非常巧妙,堪称计算机二进制领域的“经典之作”。例如,一个Int8数组 a = [1,3,5,7],按照常规的顺序存储结构,4 个Int8 的数组元素,每个 Int8 元素占用 1 字节,那么总共占用 4 * 4 = 16 个字节。如下图所示。

数组a

1

3

5

7

下标

0

1

2

3

而如果我们跳出常规思维,把一个字节( 1Byte = 8bit)的bit位下标 i=1,3,5,7用状态位 1 来表示,其余的用 0 表示,即是 Bitmap 最简单朴素的思想表达了。如下图所示。

数组 a

0

1

0

1

0

1

0

1

bit 位

0

1

2

3

4

5

6

7

那么数组 a 就可以映射成01010101来表达,只占用 1 个字节,数据存储空间大大降低,这样,我们就可以把更多的数据放到内存中计算,实现更高的性能。通常,在 OLAP 引擎中还会使用恰当的数据压缩算法,目的是相同的。从这个角度看,Bitmap 也可以当做一种数据编码压缩算法。

Bitmap 应用非常广泛。例如,通过 Bitmap 可以完成精确去重操作,通过多个 Bitmap 的 and、or、xor、andnot 等位操作可以实现留存分析、漏斗分析、用户画像分析等场景的计算。

Ø Tips: 关于Bit 位

比特(Bit,亦称二进制位)指二进制中的一位,是信息的最小单位。Bit是Binary digit(二进制数位)的混成词,由数学家John Wilder Tukey提出可能是1946年提出,但有资料称1943年就提出了)。这个术语第一次被正式使用,是在香农著名的论文《通信的数学理论》(A Mathematical Theory of Communication)第1页中。

假设一事件以A或B的方式发生,且A、B发生的概率相等,都为0.5,则一个二进位可用来代表A或B之一。例如:

1. 二进位可以用来表示一个简单的正负

2. 有两种状态的开关(如电灯开关)

3. 晶体管的通断

4. 某根导线上电压的有无

5. 一个抽像的逻辑上的是否

除二进位外,在电脑上常用的还有八进制、十进制和十六进制等进制的八进位、十进位和十六进位等。1字节由8比特组成。

1.8.2. 创建 Bitmap数据类型

ClickHouse 中Bitmap位图对象,本质上是聚合函数类型AggregateFunction (groupBitmap, UInt*),它有两种构造方法。

1.通过数组创建Bitmap 对象。同时还可以将位图对象转化为数组对象。

2.使用聚合函数groupBitmapState创建Bitmap 对象。

通过数组创建 Bitmap 对象

函数:bitmapBuild(array)

功能说明:使用一个无符号整数数组创建一个位图对象。

返回值:返回一个聚合函数类型对象AggregateFunction(groupBitmap, UInt*)

SQL 实例:

SELECT

    bitmapBuild([1, 2, 3]) AS res,

    toTypeName(res) AS type

FORMAT Vertical

输出:

res:  

type: AggregateFunction(groupBitmap, UInt8)

使用聚合函数groupBitmapState创建 Bitmap 对象

函数:groupBitmapState(expr)

功能说明:通过一个计算表达式 expr,从无符号整数列进行位图计算,返回一个Bitmap 对象。

返回值:由该整数列元素组成的 bitmap 对象。

SQL实例:

SELECT

    groupBitmapState(UserID) AS res,

    toTypeName(res) AS type

FROM hits_v1

WHERE UserID IN (610708775678702928, 1042176457941735373)

FORMAT Vertical

输出:

res:  P??Se??W???v

type: AggregateFunction(groupBitmap, UInt64)

另外,直接调用函数 groupBitmap(expr) ,返回类型为UInt64的基数,该结果是精确去重了的。例如,执行下面的 SQL,输出结果为:

SELECT groupBitmap(UserID) AS res

FROM hits_v1

Query id: a08af447-5fac-4a5f-a714-c36e792109b3

┌─res─┐

│119689 │

└────┘

1 rows in set. Elapsed: 0.906 sec. Processed 17.75 million rows, 141.98 MB (19.59 million rows/s., 156.75 MB/s.)

1.8.3. Bitmap常用操作函数

Bitmap位图函数用于对两个位图对象进行计算,结果依然是位图对象类型,例如and,or,xor,not等。Bitmap常用操作函数如下表。

 函数

功能说明

 SQL 实例

bitmapBuild(array)参数:array – 无符号整数数组返回值:Bitmap对象,类型为AggregateFunction(groupBitmap, UInt*)

从无符号整数数组构建Bitmap对象

SELECT    bitmapBuild([1, 2, 3]) AS res,    toTypeName(res) AS type输出:┌─res─┬─type──────────────────────────────────┐│     │ AggregateFunction(groupBitmap, UInt8) │└─────┴───────────────────────────────────────┘

bitmapToArray(bitmap)参数:bitmap – Bitmap对象返回值:数组

将Bitmap转换为整数数组。

SELECT    bitmapBuild([1, 2, 3]) AS res,    toTypeName(res) AS type1,    bitmapToArray(res) AS arr,    toTypeName(arr) AS type2FORMAT Vertical输出:res:   type1: AggregateFunction(groupBitmap, UInt8)arr:   [1,2,3]type2: Array(UInt8)

bitmapCardinality(bitmap)参数:bitmap - Bitmap 对象返回值:bitmap 对象的基数值,UInt64类型。

计算 bitmap 对象的基数值(去重),UInt64类型。

SELECT bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5, 5, 5])) AS res输出:┌─res─┐│   5 │└─────┘

bitmapSubsetInRange(bitmap, range_start, range_end)参数:bitmap – Bitmap对象.range_start – 范围起始点(含),UInt32类型range_end – 范围结束点(不含),UInt32类型返回值:子集Bitmap对象

将Bitmap指定范围[range_start, range_end)转换为另一个Bitmap。

SELECT bitmapToArray(bitmapSubsetInRange(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10]), toUInt32(1), toUInt32(10))) AS res;输出:┌─res─────────────────┐│ [1,2,3,4,5,6,7,8,9] │└─────────────────────┘

bitmapSubsetLimit(bitmap, range_start, cardinality_limit)参数:bitmap – Bitmap 对象.range_start – 范围起始点(含),UInt32类型cardinality_limit – 子集 Bitmap 对象的基数上限,UInt32类型返回值:子集Bitmap对象

根据范围起始点(含)和基数上限创建子 Bitmap 对象。

SELECT bitmapToArray(bitmapSubsetLimit(bitmapBuild([0, 1, 2, 3, 11, 15, 7, 18, 19, 7, 21]), toUInt32(10), toUInt32(3))) AS res输出:┌─res────────┐│ [11,15,18] │└────────────┘

subBitmap(bitmap, offset, cardinality_limit)参数:bitmap – Bitmap 对象.offset – 开始元素的下标(从 0 开始计算),UInt32类型.cardinality_limit – 子集 Bitmap 对象的基数上限,UInt32类型.返回值:子集 Bitmap 对象

返回子集 Bitmap 对象,从偏移位置开始。返回元素的数量受到cardinality_limit参数的限制。类似字符串函数 substring。

SELECT bitmapToArray(subBitmap(bitmapBuild([0, 1, 2, 3, 11, 15, 7, 18, 19, 7, 21]), toUInt32(4), toUInt32(3))) AS res输出:┌─res───────┐│ [11,15,7] │└───────────┘

bitmapContains(bitmap, x)参数:bitmap – 目标检索的Bitmap 对象x – 搜索的值, UInt32类型返回值:0 — 不包含1 — 包含UInt8类型.

检查bitmap对象中是否包含元素 x,包含返回 1,不包含返回 0.

SELECT    bitmapContains(bitmapBuild([1, 3, 5, 7, 9]), toUInt32(3)) AS res1,    bitmapContains(bitmapBuild([1, 3, 5, 7, 9]), toUInt32(4)) AS res2输出:┌─res1─┬─res2─┐│    1 │    0 │└──────┴──────┘

bitmapHasAny(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2。返回值:1 - Bitmap对象1,Bitmap对象2有任何公共元素0 - Bitmap对象1,Bitmap对象2无公共元素。

与hasAny(array1,array2)类似,如果Bitmap对象1,Bitmap对象2之间,有任何公共元素则返回1,否则返回0。对于空位图,返回0。

SELECT    bitmapHasAny(bitmapBuild([1, 2, 3]), bitmapBuild([3, 4, 5])) AS res1,    bitmapHasAny(bitmapBuild([1, 2, 3]), bitmapBuild([4, 5])) AS res2输出:┌─res1─┬─res2─┐│    1 │    0 │└──────┴──────┘

bitmapHasAll(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:1 - Bitmap对象1包含Bitmap对象2所有元素.0 - 反之,则为 0.

与hasAll(array1,array2)类似,如果第一个位图包含第二个位图的所有元素,则返回1,否则返回0。如果第二个参数是空位图对象,则返回1。

SELECT    bitmapHasAll(bitmapBuild([1, 2, 3]), bitmapBuild([2, 3])) AS res1,    bitmapHasAll(bitmapBuild([1, 2, 3]), bitmapBuild([2, 3, 4])) AS res2,    bitmapHasAll(bitmapBuild([1, 2, 3]), bitmapBuild(emptyArrayUInt8())) AS res3输出:┌─res1─┬─res2─┬─res3─┐│    1 │    0 │    1 │└──────┴──────┴──────┘

bitmapMin(bitmap)参数:bitmap – Bitmap 对象返回值:bitmap中的最小元素值。

计算 bitmap 对象中元素最小值。空对象返回 0.

SELECT    bitmapMin(bitmapBuild([1, 2, 3, 4, 5])) AS res1,    bitmapMin(bitmapBuild(emptyArrayUInt8())) AS res2输出:┌─res1─┬─res2─┐│    1 │    0 │└──────┴──────┘

bitmapMax(bitmap)参数:bitmap – Bitmap 对象返回值:bitmap中的最大元素值。

计算 bitmap 对象中元素最大值。空对象返回 0.

SELECT    bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) AS res1,    bitmapMax(bitmapBuild(emptyArrayUInt8())) AS res2输出:┌─res1─┬─res2─┐│    5 │    0 │└──────┴──────┘

bitmapAnd(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:bitmap1,bitmap2交集元素组成的新 Bitmap 对象

计算bitmap1 ∩ bitmap2 交集元素,返回交集元素组成的新的 Bitmap 对象。

SELECT bitmapToArray(bitmapAnd(bitmapBuild([1, 2, 3]), bitmapBuild([2, 3, 4, 5]))) AS res输出:┌─res───┐│ [2,3] │└───────┘

bitmapOr(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:bitmap1,bitmap2并集元素组成的新 Bitmap 对象

计算bitmap1 ∪ bitmap2并集元素,返回并集元素组成的新的 Bitmap 对象。

SELECT bitmapToArray(bitmapOr(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res;输出:┌─res─────────┐│ [1,2,3,4,5] │└─────────────┘

bitmapAndnot(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:bitmap1,bitmap2差集元素组成的新 Bitmap 对象

计算 bitmap1 - bitmap2 差集元素(从 bitmap1 中减掉  (bitmap1 ∩ bitmap2) ,返回差集元素组成的新的 Bitmap 对象。

SELECT bitmapToArray(bitmapAndnot(bitmapBuild([1,2,3]), bitmapBuild([2,3,4,5]))) AS res;输出:┌─res─┐│ [1] │└─────┘

bitmapXor(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:bitmap1,bitmap2元素先并,然后减去交集元素,组成的新 Bitmap 对象.

计算 (bitmap1 ∪ bitmap2 ) - (bitmap1 ∩ bitmap2) 元素,返回差集元素组成的新的 Bitmap 对象。

SELECT bitmapToArray(bitmapXor(bitmapBuild([1,2,3]),bitmapBuild([2,3,4,5]))) AS res;输出:┌─res───────┐│ [1,4,5] │└───────────┘其中,a xor b 异或运算的意思是:如果a、b两个值不相同,则异或结果为1。如果a、b两个值相同,异或结果为0。异或运算用于快速比较两值是否相等。

bitmapAndCardinality(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:bitmap1,bitmap2交集元素个数。

计算 bitmap1,bitmap2交集元素个数。

SELECT bitmapAndCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;输出:┌─res─┐│   1 │└─────┘

bitmapOrCardinality(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:bitmap1,bitmap2并集元素个数。

计算 bitmap1,bitmap2并集元素个数。

SELECT bitmapOrCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;输出:┌─res─┐│   5 │└─────┘

bitmapAndnotCardinality(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:bitmap1,bitmap2差集元素个数。

计算 bitmap1,bitmap2差集元素个数。

SELECT bitmapAndnotCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;输出:┌─res─┐│   2 │└─────┘

bitmapXorCardinality(bitmap1,bitmap2)参数:bitmap1, bitmap2 – Bitmap对象1,Bitmap对象2.返回值:bitmap1,bitmap2异或集元素个数。

计算 bitmap1,bitmap2异或集元素个数。

SELECT bitmapXorCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;输出:┌─res─┐│   4 │└─────┘

bitmapTransform(bitmap, from_array, to_array)参数:bitmap-Bitmap对象。from_array-UInt32数组。 to_array-UInt32数组,其大小应与from_array相同。返回值:经过替换处理之后的新Bitmap对象。

对于范围为[0,from_array.size() ) 的idx,如果位图包含from_array[idx],那么用to_array[idx]替换它。请注意,如果from_array和to_array之间有共同的元素,结果取决于数组顺序。

SELECT bitmapToArray(               bitmapTransform(bitmapBuild([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]),                               [5,999,2],                               [2,888,20])           )  AS res;输出:[1,3,4,6,7,8,9,10,20]其中,先替换源数组中的元素 5=>2, 此时,源数组变为:[1, 2, 3, 4, 2, 6, 7, 8, 9, 10];然后,处理 999 =>888元素映射,发现源数组中无 999 元素,紧接着下一个元素替换:2=>20, 最后,源数组被处理成了:  [1, 20, 3, 4, 20, 6, 7, 8, 9, 10],最后一步去重,输出结果。

Ø Tips: RoaringBitmap算法

为了解决位图稀疏存储空间占用的问题,计算机科学家们提出了多种算法对稀疏位图进行压缩,减少内存占用并提高效率。比较有代表性的有WAH、EWAH、Concise,以及RoaringBitmap。前三种算法都是基于行程长度编码(Run-length encoding, RLE)做压缩的,而RoaringBitmap算是它们的改进版。

RoaringBitmap算法于2016年由S. Chambi、D. Lemire、O. Kaser等人在论文《Better bitmap performance with Roaring bitmaps》(https://arxiv.org/pdf/1402.6407.pdf)与《Consistently faster and smaller compressed bitmaps with Roaring》(https://arxiv.org/pdf/1603.06549.pdf)中提出。

RoaringBitmap的核心思想是, 将32位无符号整数按照高16位分桶,即最多可能有2^16=65536个桶,论文内称为container。存储数据时,按照数据的高16位找到container(找不到就会新建一个),再将低16位放入container中。也就是说,一个RoaringBitmap就是很多container的集合。为了方便理解,引用论文中的示例图,如下所示。

ClickHouse 实战:ClickHouse 高级数据类型极简教程_python_05

图中展示了三个container:

bits:0x0000 - 高16位为0000H的array container(数组容器),存储0×2^16=0, 基数=1000个62的倍数的数字分别是[0:999]× 62,其中,999 × 62 = 61938。

bits:0x0001 - 高16位为0001H的array container(数组容器),存储[1×2^16+0, 1×2^16+99] 区间内的100个数 (基数=100)。

bits:0x0002 - 高16位为0002H的bitmap container(位图容器),存储[2×2^16, 3×2^16)区间内[2×2^16, 2×2^16 + 1, 2×2^16 + 2, 2×2^16 + 3, ... ,  3×2^16 - 2,  3×2^16 - 1]的所有偶数,共2^15个偶数 (基数=2^15),算上奇数个数,总共就是2^16 bits,在这个 2^16 bits 大小的位图容器中,第 0 位是 1,表示偶数 2×2^16。 第 1 位是 0,表示奇数2×2^16+1 。以此类推。第2^16-1位是 0,表示奇数3×2^16-1。

RoaringBitmap 的Container一共有3种,分别是ArrayContainer、BitmapContainer、RunContainer。

1.ArrayContainer

当桶内数据的基数小于等于4096时,会采用它来存储,其本质上是一个unsigned short类型的有序数组。数组初始长度为4,随着数据的增多会自动扩容(但最大长度就是4096)。另外还维护有一个计数器,用来实时记录基数。上图中的前两个container基数都没超过4096,所以均为ArrayContainer。

2.BitmapContainer

当桶内数据的基数大于4096时,会采用位图来存储。BitmapContainer用长度固定为1024的unsigned long型数组表示,亦即位图的大小固定为216位(8KB)。它同样有一个计数器。上图中的第 3 个container基数远远大于4096,所以用BitmapContainer存储。

3.RunContainer

RunContainer存储使用行程长度编码(RLE)压缩后的数据,是一个可变长度的unsigned short数组。举个例子,连续的整数序列11, 12, 13, 14, 15, 12800, 12801, 12802 会被RLE压缩为两个二元组11, 4, 12800, 2,表示11后面紧跟着4个连续递增的值,27后面跟着2个连续递增的值。可见,RunContainer的压缩效果与数据分布连续性有关。考虑极端情况:如果所有数据都是连续的,那么最终只需要4字节;如果所有数据都不连续(比如全是奇数或全是偶数),那么不仅不会压缩,还会膨胀成原来的两倍大。所以,RoaringBitmap引入RunContainer是作为ArrayContainer和BitmapContainer的折衷方案。

ClickHouse使用RoaringBitmap数据结构存储位图对象,当基数小于或等于32时,使用Set保存。当基数大于32时,使用RoaringBitmap保存。这也是为什么低基数集的存储更快的原因。

1.9. 本章小结

本章主要介绍了 ClickHouse 中的数组、元组、嵌套类型、Map、AggregateFunction、Bitmap 数据类型、低基数、Nullable类型等高级数据类型,以及它们的基本使用操作。掌握了这些高级数据类型,对我们灵活使用 ClickHouse 进行数据计算处理,会有很大帮助。例如,Bitmap 结合数组类型,可以实现 DMP 用户画像平台人群圈选和洞察——我们在后面的项目实战章节中会具体介绍。