ClickHouse函数 1.算术,比较,取整,逻辑,哈希,条件,URL 函数


1. 算数函数

1.1 求和 plus

SELECT plus(12, 21), plus(10, -10), plus(-10, -10) ;
┌─plus(12, 21)─┬─plus(10, -10)─┬─plus(-10, -10)─┐
│           330-20 │
└──────────────┴───────────────┴────────────────┘

1,2 求差值 minus

SELECT minus(10, 5), minus(10, -10),minus(-10, -10); 
┌─minus(10, 5)─┬─minus(10, -10)─┬─minus(-10, -10)─┐
│            5200 │
└──────────────┴────────────────┴─────────────────┘

1.3 求积 multiply

SELECT multiply(12, 2), multiply(12, -2), multiply(-12, -2);
┌─multiply(12, 2)─┬─multiply(12, -2)─┬─multiply(-12, -2)─┐
│              24-2424 │
└─────────────────┴──────────────────┴───────────────────┘

1.4 求商 divide

SELECT divide(12, 4), divide(10, 3), divide(2, 4); 
┌─divide(12, 4)─┬──────divide(10, 3)─┬─divide(2, 4)─┐
│             33.33333333333333350.5 │
└───────────────┴────────────────────┴──────────────┘

SELECT divide(-4, -2), divide(-4, 2), divide(-4.5, 3); 
┌─divide(-4, -2)─┬─divide(-4, 2)─┬─divide(-4.5, 3)─┐
│              2-2-1.5 │
└────────────────┴───────────────┴─────────────────┘

-- 除数与被除数都为 0, 返回 nan
SELECT divide(0, 0);
┌─divide(0, 0)─┐
│          nan │
└──────────────┘

-- 当被除数为 0 时,结果会出现无穷大符号 “ ∞ ”或“ -∞ ”
SELECT divide(10, 0), divide(-10, 0);
┌─divide(10, 0)─┬─divide(-10, 0)─┐
│           inf │           -inf │
└───────────────┴────────────────┘

-- 使用 intDivOrZero 函数计算 除法,当被除数为 0 时,结果返回 0
 SELECT intDivOrZero(10, 0);
┌─intDivOrZero(10, 0)─┐
│                   0 │
└─────────────────────┘

SELECT intDivOrZero(-4, -2), intDivOrZero(-4, 2), intDivOrZero(-4.5, 3); 
┌─intDivOrZero(-4, -2)─┬─intDivOrZero(-4, 2)─┬─intDivOrZero(-4.5, 3)─┐
│                    2-2-1 │
└──────────────────────┴─────────────────────┴───────────────────────┘

SELECT divide(-4, -2), divide(-4, 2), divide(-4.5, 3); 
┌─divide(-4, -2)─┬─divide(-4, 2)─┬─divide(-4.5, 3)─┐
│              2-2-1.5 │
└────────────────┴───────────────┴─────────────────┘

1.5 求余数 modulo

SELECT modulo(10, 3), modulo(10.5, 3);
┌─modulo(10, 3)─┬─modulo(10.5, 3)─┐
│             11.5 │
└───────────────┴─────────────────┘

1.6 取反 negate

SELECT negate(10), negate(-10);
┌─negate(10)─┬─negate(-10)─┐
│        -1010 │
└────────────┴─────────────┘

1.7 绝对值 abs

SELECT abs(-10), abs(10);
┌─abs(-10)─┬─abs(10)─┐
│       1010 │
└──────────┴─────────┘

1.8 最大公约数 gcd

SELECT gcd(12, 24), gcd(-12, -24), gcd(10, 6);
┌─gcd(12, 24)─┬─gcd(-12, -24)─┬─gcd(10, 6)─┐
│          12122 │
└─────────────┴───────────────┴────────────┘

1.9 最小公倍数 lcm

SELECT lcm(12, 24), lcm(-12, -24), lcm(-3, 4); 
┌─lcm(12, 24)─┬─lcm(-12, -24)─┬─lcm(-3, 4)─┐
│          242412 │
└─────────────┴───────────────┴────────────┘

1.10 随机数 rand

  • 随机函数使用非加密方式生成伪随机数字。
  • 所有随机函数都只接受一个参数或不接受任何参数。
  • 您可以向它传递任何类型的参数,但传递的参数将不会使用在任何随机数生成过程中。
  • 此参数的唯一目的是防止公共子表达式消除,以便在相同的查询中使用相同的随机函数生成不同的随机数
函数 说明
rand 返回一个UInt32类型的随机数字,所有UInt32类型的数字被生成的概率均相等
rand64 返回一个UInt64类型的随机数字,所有UInt64类型的数字被生成的概率均相等。
randConstant 返回一个UInt32类型的随机数字,该函数不同之处在于仅为每个数据块产生一个随机数。
SELECT rand(),rand(10);
┌────rand()─┬─rand(10)─┐
│ 41461905198368807 │
└───────────┴──────────┘

SELECT  rand64(), rand64(10);
┌────────────rand64()─┬──────────rand64(10)─┐
│ 17285807561031814457736991780466826633 │
└─────────────────────┴─────────────────────┘

select randConstant();
┌─randConstant()─┐
│     3801492456 │
└────────────────┘

2. 比较函数

2.1 相等 equals

SELECT equals(12, 12), equals(12, 13),  equals('aa', 'aa'), equals('aa','bb');

┌─equals(12, 12)─┬─equals(12, 13)─┬─equals('aa', 'aa')─┬─equals('aa', 'bb')─┐
│              1010 │
└────────────────┴────────────────┴────────────────────┴────────────────────┘

2.2 不等 notEquals

SELECT notEquals(12, 12), notEquals(12, 13),  notEquals('aa', 'aa'), notEquals('aa','bb');

┌─notEquals(12, 12)─┬─notEquals(12, 13)─┬─notEquals('aa', 'aa')─┬─notEquals('aa', 'bb')─┐
│                 0101 │
└───────────────────┴───────────────────┴───────────────────────┴───────────────────────┘

2.3 大于 greater

SELECT greater(12, 10), greater(10, 12), greater(12, 12);

┌─greater(12, 10)─┬─greater(10, 12)─┬─greater(12, 12)─┐
│               100 │
└─────────────────┴─────────────────┴─────────────────┘

2.4 大于等于 greaterOrEquals

SELECT greaterOrEquals(12,10), greaterOrEquals(12,12);

┌─greaterOrEquals(12, 10)─┬─greaterOrEquals(12, 12)─┐
│                       11 │
└─────────────────────────┴─────────────────────────┘

2.5 小于 less

SELECT less(12, 21), less(12, 10), less(120, 120);

┌─less(12, 21)─┬─less(12, 10)─┬─less(120, 120)─┐
│            100 │
└──────────────┴──────────────┴────────────────┘

2.6 小于等于 lessOrEquals

SELECT lessOrEquals(12, 120), lessOrEquals(12, 11); 

┌─lessOrEquals(12, 120)─┬─lessOrEquals(12, 11)─┐
│                     10 │
└───────────────────────┴──────────────────────┘

3. 取整函数

3.1 向下取整:floor

  • floor(x[,N])
-- 向下取整
SELECT floor(1.2345);
┌─floor(1.2345)─┐
│             1 │
└───────────────┘

-- 向下取整 并保留两位小数
SELECT floor(1.2345, 2);
┌─floor(1.2345, 2)─┐
│             1.23 │
└──────────────────┘

-- 将字符串转成 float 然后向下取整,并保留两位小数
SELECT floor(toFloat32('1.2345'), 2);
┌─floor(toFloat32('1.2345'), 2)─┐
│                          1.23 │
└───────────────────────────────┘

3.2 向上取整:ceil

  • ceil(x[, N]) 或者 ceiling(x[, N])
-- 向上取整
select ceil(1.234),ceil(5.678);
┌─ceil(1.234)─┬─ceil(5.678)─┐
│           26 │
└─────────────┴─────────────┘

select ceil(1.234,0),ceil(5.678,0);
┌─ceil(1.234, 0)─┬─ceil(5.678, 0)─┐
│              26 │
└────────────────┴────────────────┘

-- 向上取整,保留 2 位小数
select ceil(1.234,2),ceil(5.678,2);
┌─ceil(1.234, 2)─┬─ceil(5.678, 2)─┐
│           1.245.68 │
└────────────────┴────────────────┘

3.3 四舍五入:round

  • round(expression [, decimal_places])

  • decimal_places = 0:取整数,0 可省略

  • decimal_places > 0:将值舍入小数点右侧

  • decimal_places < 0:将小数点左侧的值四舍五入

-- 四舍五入取整
select round(1.234,0),round(5.678,0);
┌─round(1.234, 0)─┬─round(5.678)─┐
│               16 │
└─────────────────┴──────────────┘

-- 四舍五入取整 同上,省略 decimal_places参数 0 
select round(1.234),round(5.678);
┌─round(1.234)─┬─round(5.678)─┐
│            16 │
└──────────────┴──────────────┘

-- 四舍五入保留两位小数
select round(1.234,2),round(5.678,2);
┌─round(1.234, 2)─┬─round(5.678, 2)─┐
│            1.235.68 │
└─────────────────┴─────────────────┘

-- 四舍五入,小数点向左移动一位
select round(1.234,-1),round(5.678,-1);
┌─round(1.234, -1)─┬─round(5.678, -1)─┐
│                010 │
└──────────────────┴──────────────────┘

3.4 向下舍入到2^x roundToExp2

  • 接受一个数字。它将数字向下舍入到最接近的(整个非负)2的x次幂。
SELECT roundToExp2(9);

┌─roundToExp2(9)─┐
│              8-- 2^3
└────────────────┘

4. 逻辑函数

逻辑函数可以接受任何数字类型的参数,并返回UInt8类型的 0 或 1

当向函数传递零时,函数将判定为 false ,否则,任何其他非零的值都将被判定为 true。

4.1 与 and

SELECT and(equals(12, 12), notEquals(12, 10));
┌─and(equals(12, 12), notEquals(12, 10))─┐
│                                      1 │
└────────────────────────────────────────┘

4.2 或 or

 SELECT or(equals(12, 12), notEquals(12, 10));
┌─or(equals(12, 12), notEquals(12, 10))─┐
│                                     1 │
└───────────────────────────────────────┘

4.3 非 not

SELECT not 12, not 0;
┌─not(12)─┬─not(0)─┐
│       01 │
└─────────┴────────┘

SELECT not(12), not(0);
┌─not(12)─┬─not(0)─┐
│       01 │
└─────────┴────────┘

4.4 异或 xor

SELECT xor(0, 1, 1);
┌─xor(0, 1, 1)─┐
│            0 │
└──────────────┘

5. Hash函数

5.1 MD5

SELECT MD5('HELLO WORLD!');
┌─MD5('HELLO WORLD!')─┐
│ ���}dA�g����*�u            │
└─────────────────────┘

5.2 halfMD5

  • 计算字符串的MD5值
SELECT halfMD5('HELLO WORLD!');
┌─halfMD5('HELLO WORLD!')─┐
│    13086268085575473511 │
└─────────────────────────┘

5.3 intHash

  • 为任何类型的整数计算32位的哈希。
  • 这是相对高效的非加密Hash函数
  • 从任何类型的整数计算64位哈希码。 它的工作速度比intHash32函数快。
  • 计算任意数量字符串的CityHash64或使用特定实现的Hash函数计算任意数量其他类型的Hash。
SELECT
intHash32(123) AS intHash32,
intHash64(123) AS intHash64,
cityHash64('123') AS cityHash64;
┌──intHash32─┬────────────intHash64─┬───────────cityHash64─┐
│ 14658379211258532638514545409511844464045149276331 │
└────────────┴──────────────────────┴──────────────────────┘

5.4 sha加密

  • 使用sha1或者sha224加密的话,只能用于字符串
SELECT
SHA1('123') AS sha1,
SHA224('123') AS sha224,
SHA256('123') AS sha256;

┌─sha1───────┬─sha224────────┬─sha256─────────┐
│ @�_�Qe2���\^�۾� │ x�]hJ�.��#u�<ׁH��x�$f │ �e�Y B/�A~Hg��O��J?��~�����z� │
└────────────┴───────────────┴────────────────┘

5.5 farmHash

  • farmHash64(s) 计算字符串的FarmHash64。
  • 接受一个String类型的参数。
  • 返回UInt64。
SELECT farmHash64('hello world')

┌─farmHash64('hello world')─┐
│       6381520714923946011 │
└───────────────────────────┘

5.6 javaHash

  • javaHash(s) 计算字符串的JavaHash。
  • 接受一个String类型的参数。
  • 返回Int32。
SELECT javaHash('hello world');
┌─javaHash('hello world')─┐
│              1794106052 │
└─────────────────────────┘

5.7 hiveHash

  • hiveHash(s) 计算字符串的HiveHash。
  • 与JavaHash相同,但不会返回负数
  • 接受一个String类型的参数。
  • 返回Int32。
SELECT hiveHash('hello world');
┌─hiveHash('hello world')─┐
│              1794106052 │
└─────────────────────────┘

6. 类型转换函数

6.1 转换为数值

SELECT toInt8(12.3334343), toFloat32(10.001), toFloat64(1.000040);
┌─toInt8(12.3334343)─┬─toFloat32(10.001)─┬─toFloat64(1.00004)─┐
│                 1210.0011.00004 │
└────────────────────┴───────────────────┴────────────────────┘

6.2 转换为字符串

-- 时间转换字符串
SELECT toString(now())as n,toTypeName(n) as typeName;
┌─n───────────────────┬─typeName─┐
│ 2021-08-10 14:52:03 │ String   │
└─────────────────────┴──────────┘

-- 时间转换字符串加时区
SELECT
    now() AS now_local,
    toString(now(), 'Asia/Shanghai') AS now_Shanghai,
    toString(now(), 'Asia/Tokyo') AS now_Toyko

Query id: 40014843-cdd1-49d4-81be-259d3e05e99f

┌───────────now_local─┬─now_Shanghai────────┬─now_Toyko───────────┐
│ 2021-08-10 16:24:042021-08-10 16:24:042021-08-10 17:24:04 │
└─────────────────────┴─────────────────────┴─────────────────────┘

6.3 转换为值类型

SELECT
    toTypeName(0),
    toTypeName(-10),
    toTypeName(12.43),
    toTypeName('hello'),
    toTypeName(toDateTime(1629788289))

┌─toTypeName(0)─┬─toTypeName(-10)─┬─toTypeName(12.43)─┬─toTypeName('hello')─┬─toTypeName(toDateTime(1629788289))─┐
│ UInt8         │ Int8            │ Float64           │ String              │ DateTime                           │
└───────────────┴─────────────────┴───────────────────┴─────────────────────┴────────────────────────────────────┘

7 条件函数

7.1 三元运算符

  • if ( cond, then, else )

  • cond != 0 则返回 then,如果 cond == 0 则返回else 。

  • cond 必须是 UInt8 类型,then 和 else 必须存在最低的共同类型。

  • then 和 else 可以是 NULL

  • 中文字符使用双引号,英文字符可不使用引号也可使用当引号或双引号

SELECT
12 > 10 ? 'desc' : 'asc' AS "三元操作符",
if(12 > 10, 'desc' , 'asc') AS "if()函数",
if(12 > 10, NULL, NULL);

┌─三元操作符─┬─if()函数─┬─if(greater(12, 10), NULL, NULL)─┐
│ descdesc     │ ᴺᵁᴸᴸ                            │
└────────────┴──────────┴─────────────────────────────────┘

7.2 多个条件判断 multiIf

  • multiIf( cond_1, then_1, cond_2, then_2, …, else )

  • cond_N— 函数返回的条件then_N

  • then_N — 函数执行时的结果。

  • else — 不满足任何条件时的函数结果。

  • 编写类似 case 的运算符,可以接收 2n+1 个参数

  • 该函数返回值之一then_Nor else,具体取决于条件cond_N

SELECT
    10 as left,
    11 as right,
    multiIf(
      left < right, 'left is smaller', 
      left > right, 'left is greater', 
      left = right, 'Both equal', 
      'Null value'
    ) AS result;

┌─left─┬─right─┬─result──────────┐
│   1011left is smaller │
└──────┴───────┴─────────────────┘

8. UUID函数

8.1 generateUUIDv4 (生成随机UUID)

  • generateUUIDv4() 返回 UUID类型的值。
-- 随机生成一个UUIDv4的字符串
SELECT generateUUIDv4() as randomUUID;
┌─randomUUID───────────────────────────┐
│ 878c378e-fce3-4323-9486-45ce0d57af60 │
└──────────────────────────────────────┘

-- 随机生成一个UUIDv4的字符串 并删除 '-' 符号
SELECT replaceAll(toString(generateUUIDv4()), '-', '') AS replaceUUID;
┌─replaceUUID──────────────────────┐
│ fc1aff33bd4c42aeb8061e6162b19e74 │
└──────────────────────────────────┘

8.2 toUUID(s) (String 转 UUID)

SELECT
    toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS uuid,
    toTypeName(uuid) AS typeName

┌─uuid─────────────────────────────────┬─typeName─┐
│ 61f0c404-5cb3-11e7-907b-a6006ad3dba0 │ UUID     │
└──────────────────────────────────────┴──────────┘

8.3 UUIDNumToString() (FixedString(16) 转换成 UUID)

  • 接受一个FixedString(16)类型的值,返回其对应的String表现形式。
SELECT 'abcdefghijklmnop' AS bytes,UUIDNumToString(toFixedString(bytes, 16)) AS uuid;
┌─bytes────────────┬─uuid─────────────────────────────────┐
│ abcdefghijklmnop │ 61626364-6566-6768-696a-6b6c6d6e6f70 │
└──────────────────┴──────────────────────────────────────┘

SELECT 'a' AS bytes,UUIDNumToString(toFixedString(bytes, 16)) AS uuid;
┌─bytes─┬─uuid─────────────────────────────────┐
│ a61000000-0000-0000-0000-000000000000 │
└───────┴──────────────────────────────────────┘

8.4 UUIDStringToNum (将 UUID 转换成FixedString(16))

  • 接受一个String类型的值,

  • 其中包含36个字符且格式为xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,

  • 将其转换为UUID的数值并以FixedString(16)将其返回。

SELECT '61626364-6566-6768-696a-6b6c6d6e6f70' AS uuid,UUIDStringToNum(uuid) AS bytes;
┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 61626364-6566-6768-696a-6b6c6d6e6f70 │ abcdefghijklmnop │
└──────────────────────────────────────┴──────────────────┘

SELECT '61000000-0000-0000-0000-000000000000' AS uuid,UUIDStringToNum(uuid) AS bytes;
┌─uuid─────────────────────────────────┬─bytes─┐
│ 61000000-0000-0000-0000-000000000000a     │
└──────────────────────────────────────┴───────┘

9. URL函数

https://clickhouse.tech/docs/zh/sql-reference/functions/url-functions/#cuturlparameterurl-name

9.1 protocol(截取传输协议)

-- 截取协议
SELECT protocol('http://www.baidu.com'); 
┌─protocol('http://www.baidu.com')─┐
│ http                             │
└──────────────────────────────────┘

-- 没有协议的时候返回空字符串
SELECT protocol('www.baidu.com');
┌─protocol('www.baidu.com')─┐
│                           │
└───────────────────────────┘

9.2 domain (获取域名)

SELECT domain('http://www.baidu.com');
┌─domain('http://www.baidu.com')─┐
│ www.baidu.com                  │
└────────────────────────────────┘

9.3 domainWithoutWWW (返回域名并删除首个3w)

SELECT domainWithoutWWW('http://www.baidu.com'),domainWithoutWWW('www.baidu.com');
┌─domainWithoutWWW('http://www.baidu.com')─┬─domainWithoutWWW('www.baidu.com')─┐
│ baidu.com                                │ baidu.com                         │
└──────────────────────────────────────────┴───────────────────────────────────┘

9.4 topLevelDomain(获取顶级域名)

SELECT topLevelDomain('http://www.google.com.cn');
┌─topLevelDomain('http://www.google.com.cn')─┐
│ cn                                         │
└────────────────────────────────────────────┘
SELECT topLevelDomain('http://www.google.com');
┌─topLevelDomain('http://www.google.com')─┐
│ com                                     │
└─────────────────────────────────────────┘

9.5 firstSignificantSubdomain(第一个有效子域名)

-- 返回“第一个有效子域名”
-- 如果顶级域名为‘com’,‘net’,‘org’或者‘co’则第一个有效子域名为二级域名。否则则返回三级域名
SELECT firstSignificantSubdomain('https://news.yandex.com.tr/');
┌─firstSignificantSubdomain('https://news.yandex.com.tr/')─┐
│ yandex                                                   │
└──────────────────────────────────────────────────────────┘

SELECT firstSignificantSubdomain('http://www.google.com.cn');
┌─firstSignificantSubdomain('http://www.google.com.cn')─┐
│ google                                                │
└───────────────────────────────────────────────────────┘

9.6 path(返回URL路径)

-- 返回URL路径
SELECT path('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10');
┌─path('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10')─┐/aaa/bbb/ccc/ddd                                   │
└────────────────────────────────────────────────────┘

9.7 pathFull(返回URL路径包括参数)

SELECT pathFull('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10');
┌─pathFull('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10')─┐/aaa/bbb/ccc/ddd?i=10                                  │
└────────────────────────────────────────────────────────┘

9.8 queryString(请求参数)

SELECT queryString('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10');
┌─queryString('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10')─┐
│ i=10                                                      │
└───────────────────────────────────────────────────────────┘

9.9 fragment (fragment标识)

返回URL的fragment标识。fragment不包含#。

SELECT fragment('https://clickhouse.yandex/#quick-start');
┌─fragment('https://clickhouse.yandex/#quick-start')─┐quick-start                                        │
└────────────────────────────────────────────────────┘

9.10 queryStringAndFragment(请求参数和fragment标识)

SELECT queryStringAndFragment('https://www.baidu.com/s?ie=utf-8&rsv_sug7=100#eiai');
┌─queryStringAndFragment('https://www.baidu.com/s?ie=utf-8&rsv_sug7=100#eiai')─┐
│ ie=utf-8&rsv_sug7=100#eiai                                                   │
└──────────────────────────────────────────────────────────────────────────────┘

9.11 cutWWW(删除 3w)

删除URL中的部分内容 (如果URL中不包含指定的部分,则URL不变。)

SELECT cutWWW('https://www.baidu.com');
┌─cutWWW('https://www.baidu.com')─┐
│ https://baidu.com               │
└─────────────────────────────────┘

9.12 cutQueryString(删除请求参数)

SELECT cutQueryString('http://www.baidu.com/1?page=1');

┌─cutQueryString('http://www.baidu.com/1?page=1')─┐
│ http://www.baidu.com/1                          │
└─────────────────────────────────────────────────┘

9.13 cutFragment (删除fragment标识)

SELECT cutFragment('http://www.baidu.com/#quick-demo');
┌─cutFragment('http://www.baidu.com/#quick-demo')─┐
│ http://www.baidu.com/                           │
└─────────────────────────────────────────────────┘
-- '#' 同样也会被删除。

9.14 cutQueryStringAndFragment(删除请求参数以及fragment标识)

SELECT cutQueryStringAndFragment('http://www.baidu.com/1?page=23#we'); 

┌─cutQueryStringAndFragment('http://www.baidu.com/1?page=23#we')─┐
│ http://www.baidu.com/1                                         │
└────────────────────────────────────────────────────────────────┘

-- '?','#'都会被删除。

9.15 cutURLParameter(删除URL参数)

  • cutURLParameter(URL, name) 删除URL中名称为‘name’的参数。
SELECT cutURLParameter('http://www.baidu.com/1?page=1#erre&resv=23&name=user','name');
┌─cutURLParameter('http://www.baidu.com/1?page=1#erre&resv=23&name=user', 'name')─┐
│ http://www.baidu.com/1?page=1#erre&resv=23                                      │
└─────────────────────────────────────────────────────────────────────────────────┘

-- 删除前 
http://www.baidu.com/1?page=1#erre&resv=23&name=user
-- 删除后
http://www.baidu.com/1?page=1#erre&resv=23

10. 字典函数

https://clickhouse.tech/docs/en/sql-reference/functions/ext-dict-functions/

10.1 创建字典

  1. 数据格式,以 csv 格式上传到服务器
id code name
1 a0001 研发部
2 a0002 产品部
3 a0003 数据部
  1. 编辑配置文件
vim /etc/clickhouse-server/flat_dictionary.xml

<!-- 通过手动创建的flat字典配置 -->
<dictionaries>
    <dictionary>
        <name>test_flat_dict</name>
        <source>
            <!-- 准备好的测试数据 -->
            <file>
                <path>/etc/clickhouse-server/dict_data/organization.csv</path>
                <format>CSV</format>
            </file>
        </source>
        <layout>
              <flat/>
        </layout>
        <!-- 与测试数据的结构对应 -->
        <structure>
            <id>
                  <name>id</name>
            </id>
            <attribute>
                <name>code</name>
                <type>String</type>
                <null_value></null_value>
            </attribute>
            <attribute>
                <name>name</name>
                <type>String</type>
                <null_value></null_value>
            </attribute>
        </structure>
        <lifetime>
            <min>300</min>
            <max>360</max>
        </lifetime>
    </dictionary>
</dictionaries>
  1. 验证字典是否创建成功

在上述的配置中,source数据源是 CSV 格式的文件,structure 数据结构与其对应。将配置文件复制到 ClickHouse 服务节点的 /etc/clickhouseserver 目录后,即完成了对该字典的创建过程。查验 system.dictionaries 系统表后,能够看到flat字典已经创建成功。

SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;


┌─name───────────┬─type─┬─key.names─┬─key.types──┬─attribute.names─┬─attribute.types─────┬─origin─────────────────────────────────────┐
│ test_flat_dict │      │ ['id']['UInt64']['code','name']['String','String']/etc/clickhouse-server/flat_dictionary.xml │
└────────────────┴──────┴───────────┴────────────┴─────────────────┴─────────────────────┴────────────────────────────────────────────┘

10.2 dictGet

  • dictGet(‘dict_name’, attr_names, id_expr)
  • dict_name— 字典名称。字符串文字
  • attr_names— 字典列的名称,字符串文字,或列名称元组
  • id_expr- ID表达式 返回字典键类型值或元组类型值,具体取决于字典配置。
  • 如果字典中没有 id 键,则返回字典描述中指定的默认值。
select dictGet('test_flat_dict','name',toUInt64(1)) as dept_name;
┌─dept_name─┐
│ 研发部    │
└───────────┘

10.3 dictGetTOrDefault

  • dictGetOrDefault(‘dict_name’, attr_names, id_expr, default_value_expr)
  • dict_name— 字典名称。字符串文字
  • attr_names— 字典列的名称,字符串文字,或列名称元组
  • id_expr- ID表达式 返回字典键类型值或元组类型值,具体取决于字典配置。
  • default_value_expr— 如果字典不包含带有id_expr键的行,则返回值。表达式元组( Expression ),返回为attr_names属性配置的数据类型中的一个或多个值。
SELECT dictGetOrDefault('test_flat_dict', 'name', toUInt64(1),'未找到') AS dept_name;
┌─dept_name─┐
│ 研发部     │
└───────────┘

SELECT dictGetOrDefault('test_flat_dict', 'name', toUInt64(50),'未找到') AS dept_name;
┌─dept_name─┐
│ 未找到    │
└───────────┘

10.4 dictGetXX (获取指定类型数据)

获取整型数据 获取整型数据 获取浮点数据 获取日期数据 获取字符串数据
dictGetUInt8 dictGetInt8 dictGetFloat32 dictGetDate dictgetuid
dictGetUInt16 dictGetInt16 dictGetFloat64 dictGetDateTime dictGetString
dictGetUInt32 dictGetInt32
dictGetUInt64 dictGetInt64
SELECT dictGetString('test_flat_dict', 'name', toUInt64(1)) AS dept_name;

┌─dept_name─┐
│ 研发部     │
└───────────┘

10.5 dictHas

  • dictHas(‘dict_name’, id_expr)
  • dict_name— 字典名称。字符串文字
  • id_expr- 核心价值。表达式 返回字典键类型值或 元组 类型值,具体取决于字典配置。
  • 检查字典是否存在指定的 id
  • 如果不存在,则返回0
  • 如果存在,则返回1
SELECT dictHas('test_flat_dict',toUInt64(1)) AS hasVal,dictHas('test_flat_dict',toUInt64(50)) AS hasVal2;
┌─hasVal─┬─hasVal2─┐
│      10 │
└────────┴─────────┘

文章作者: hnbian
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !
评论
 上一篇
记录 Ambari 安装 Agent 异常 记录 Ambari 安装 Agent 异常
新添加的 ambari -agent 组件添加hbase regionserver 服务失败, 报错日志如下; 日志内容Traceback (most recent call last): File "/usr/lib/ambari-a
2021-08-12
下一篇 
ClickHouse 14.常用操作符介绍 ClickHouse 14.常用操作符介绍
1. 介绍 ClickHouse 的操作符所有的操作符(运算符)都会在查询时,依据他们的优先级及其结合顺序,在被解析时转换为对应的函数。下面按优先级从高到低列出各组运算符及其对应的函数: 2. 下标运算符-- 数组中的下标运算符 -- a[
2021-08-05
  目录