v22.8.1.3-clib
Release v22.8.1.3-clib
Image is published at icr.io/clickhouse/clickhouse:22.8.1.3-1-clib-ibm
KQL implemented features
August 15, 2022
double quote support
print res = strcat("double ","quote")
Aggregate functions
-
bin_at
print res = bin_at(6.5, 2.5, 7)
print res = bin_at(1h, 1d, 12h)
print res = bin_at(datetime(2017-05-15 10:20:00.0), 1d, datetime(1970-01-01 12:00:00.0))
print res = bin_at(datetime(2017-05-17 10:20:00.0), 7d, datetime(2017-06-04 00:00:00.0))
-
array_index_of
Supports only basic lookup. Do not support start_index, length and occurrence
print output = array_index_of(dynamic(['John', 'Denver', 'Bob', 'Marley']), 'Marley')
print output = array_index_of(dynamic([1, 2, 3]), 2)
-
array_sum
print output = array_sum(dynamic([2, 5, 3]))
print output = array_sum(dynamic([2.5, 5.5, 3]))
-
array_length
print output = array_length(dynamic(['John', 'Denver', 'Bob', 'Marley']))
print output = array_length(dynamic([1, 2, 3]))
Conversion
-
tobool / toboolean
print tobool(true) == true
print toboolean(false) == false
print tobool(0) == false
print toboolean(19819823) == true
print tobool(-2) == true
print isnull(toboolean('a'))
print tobool('true') == true
print toboolean('false') == false
-
todouble / toreal
print todouble(4) == 4
print toreal(4.2) == 4.2
print isnull(todouble('a'))
print toreal('-0.3') == -0.3
-
toint
print isnull(toint('a'))
print toint(4) == 4
print toint('4') == 4
print isnull(toint(4.2))
-
tostring
print tostring(123) == '123'
print tostring('asd') == 'asd'
DateType
-
dynamic
Supports only 1D array
print output = dynamic(['a', 'b', 'c'])
print output = dynamic([1, 2, 3])
-
bool,boolean
print bool(1)
print boolean(0)
-
datetime
print datetime(2015-12-31 23:59:59.9)
print datetime('2015-12-31 23:59:59.9')
print datetime("2015-12-31:)
-
guid
print guid(74be27de-1e4e-49d9-b579-fe0b331d3642)
print guid('74be27de-1e4e-49d9-b579-fe0b331d3642')
print guid('74be27de1e4e49d9b579fe0b331d3642')
-
int
print int(1)
-
long
print long(16)
-
real
print real(1)
-
timespan ,time
Note the timespan is used for calculating datatime, so the output is in seconds. e.g. time(1h) = 3600
print 1d
print 30m
print time('0.12:34:56.7')
print time(2h)
print timespan(2h)
StringFunctions
- base64_encode_fromguid
print Quine = base64_encode_fromguid('ae3133f2-6e22-49ae-b06a-16e6a9b212eb')
- base64_decode_toarray
print base64_decode_toarray('S3VzdG8=')
- base64_decode_toguid
print base64_decode_toguid('YWUzMTMzZjItNmUyMi00OWFlLWIwNmEtMTZlNmE5YjIxMmVi')
- replace_regex
print replace_regex('Hello, World!', '.', '\\0\\0')
- has_any_index
print idx = has_any_index('this is an example', dynamic(['this', 'example']))
- translate
print translate('krasp', 'otsku', 'spark')
- trim
print trim('--', '--https://bing.com--')
- trim_end
print trim_end('.com', 'bing.com')
- trim_start
print trim_start('[^\\w]+', strcat('- ','Te st1','// $'))
DateTimeFunctions
-
startofyear
print startofyear(datetime(2017-01-01 10:10:17), -1)
print startofyear(datetime(2017-01-01 10:10:17), 0)
print startofyear(datetime(2017-01-01 10:10:17), 1)
-
weekofyear
print week_of_year(datetime(2020-12-31))
print week_of_year(datetime(2020-06-15))
print week_of_year(datetime(1970-01-01))
print week_of_year(datetime(2000-01-01))
-
startofweek
print startofweek(datetime(2017-01-01 10:10:17), -1)
print startofweek(datetime(2017-01-01 10:10:17), 0)
print startofweek(datetime(2017-01-01 10:10:17), 1)
-
startofmonth
print startofmonth(datetime(2017-01-01 10:10:17), -1)
print startofmonth(datetime(2017-01-01 10:10:17), 0)
print startofmonth(datetime(2017-01-01 10:10:17), 1)
-
startofday
print startofday(datetime(2017-01-01 10:10:17), -1)
print startofday(datetime(2017-01-01 10:10:17), 0)
print startofday(datetime(2017-01-01 10:10:17), 1)
-
monthofyear
print monthofyear(datetime("2015-12-14"))
-
hourofday
print hourofday(datetime(2015-12-14 18:54:00))
-
getyear
print getyear(datetime(2015-10-12))
-
getmonth
print getmonth(datetime(2015-10-12))
-
dayofyear
print dayofyear(datetime(2015-12-14))
-
dayofmonth
print (datetime(2015-12-14))
-
unixtime_seconds_todatetime
print unixtime_seconds_todatetime(1546300800)
-
dayofweek
print dayofweek(datetime(2015-12-20))
-
now
print now()
print now(2d)
print now(-2h)
print now(5microseconds)
print now(5seconds)
print now(6minutes)
print now(-2d)
print now(time(1d))
Binary functions
- binary_and
print binary_and(15, 3) == 3
print binary_and(1, 2) == 0
- binary_not
print binary_not(1) == -2
- binary_or
print binary_or(3, 8) == 11
print binary_or(1, 2) == 3
- binary_shift_left
print binary_shift_left(1, 1) == 2
print binary_shift_left(1, 64) == 1
- binary_shift_right
print binary_shift_right(1, 1) == 0
print binary_shift_right(1, 64) == 1
- binary_xor
print binary_xor(1, 3) == 2
- bitset_count_ones
print bitset_count_ones(42) == 3
IP functions
- format_ipv4
print format_ipv4('192.168.1.255', 24) == '192.168.1.0'
print format_ipv4(3232236031, 24) == '192.168.1.0'
- format_ipv4_mask
print format_ipv4_mask('192.168.1.255', 24) == '192.168.1.0/24'
print format_ipv4_mask(3232236031, 24) == '192.168.1.0/24'
- ipv4_compare
print ipv4_compare('127.0.0.1', '127.0.0.1') == 0
print ipv4_compare('192.168.1.1', '192.168.1.255') < 0
print ipv4_compare('192.168.1.1/24', '192.168.1.255/24') == 0
print ipv4_compare('192.168.1.1', '192.168.1.255', 24) == 0
- ipv4_is_match
print ipv4_is_match('127.0.0.1', '127.0.0.1') == true
print ipv4_is_match('192.168.1.1', '192.168.1.255') == false
print ipv4_is_match('192.168.1.1/24', '192.168.1.255/24') == true
print ipv4_is_match('192.168.1.1', '192.168.1.255', 24) == true
- ipv6_compare
print ipv6_compare('::ffff:7f00:1', '127.0.0.1') == 0
print ipv6_compare('fe80::85d:e82c:9446:7994', 'fe80::85d:e82c:9446:7995') < 0
print ipv6_compare('192.168.1.1/24', '192.168.1.255/24') == 0
print ipv6_compare('fe80::85d:e82c:9446:7994/127', 'fe80::85d:e82c:9446:7995/127') == 0
print ipv6_compare('fe80::85d:e82c:9446:7994', 'fe80::85d:e82c:9446:7995', 127) == 0
- ipv6_is_match
print ipv6_is_match('::ffff:7f00:1', '127.0.0.1') == true
print ipv6_is_match('fe80::85d:e82c:9446:7994', 'fe80::85d:e82c:9446:7995') == false
print ipv6_is_match('192.168.1.1/24', '192.168.1.255/24') == true
print ipv6_is_match('fe80::85d:e82c:9446:7994/127', 'fe80::85d:e82c:9446:7995/127') == true
print ipv6_is_match('fe80::85d:e82c:9446:7994', 'fe80::85d:e82c:9446:7995', 127) == true
- parse_ipv4_mask
print parse_ipv4_mask('127.0.0.1', 24) == 2130706432
print parse_ipv4_mask('192.1.168.2', 31) == 3221334018
print parse_ipv4_mask('192.1.168.3', 31) == 3221334018
print parse_ipv4_mask('127.2.3.4', 32) == 2130838276
- parse_ipv6_mask
print parse_ipv6_mask('127.0.0.1', 24) == '0000:0000:0000:0000:0000:ffff:7f00:0000'
print parse_ipv6_mask('fe80::85d:e82c:9446:7994', 120) == 'fe80:0000:0000:0000:085d:e82c:9446:7900'