1. 表函数

1.1. S3

clickhouse官网-使用文档 (opens new window)

语法：
select column1 , column2
from s3(path, [aws_access_key_id, aws_secret_access_key], format,structure, [compression]);

• path — 带有文件路径的 Bucket url。在只读模式下支持以下通配符: *, ?, {abc,def} 和 {N..M} 其中 N, M 是数字, 'abc', 'def' 是字符串. 更多信息见下文.
• format — 文件的格式.常用CSV、TSV、CustomSeparated
• structure — 表的结构. 格式像这样 'column1_name column1_type, column2_name column2_type, ...'.
• compression — 压缩类型. 支持的值: none, gzip/gz, brotli/br, xz/LZMA, zstd/zst. 参数是可选的. 默认情况下，通过文件扩展名自动检测压缩类型.

1.1.1. 读外部表

以cos为例

1. 示例1(CSV默认分隔符 , )

select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String');

2. 示例2(自定义输入CSV文件分隔符：format_csv_delimiter)

select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String')
settings format_csv_delimiter='|';

3. 示例3(指定允许异常跳过： input_format_allow_errors_num，input_format_allow_errors_ratio )

select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String')
settings format_csv_delimiter='|',
         input_format_allow_errors_num=500;

1.1.2. 写外部表

以cos为例

1. 示例1(CSV默认分隔符 , )

INSERT INTO FUNCTION
s3('http://demo.cos.ap-shanghai.myqcloud.com/out.csv','CSV','id String, name String')
select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String');

2. 示例2(自定义输出文件分隔符：format_custom_field_delimiter)

INSERT INTO FUNCTION
s3('http://demo.cos.ap-shanghai.myqcloud.com/out.csv','CustomSeparated','id String, name String')
select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String')
settings format_csv_delimiter='|',
         format_custom_field_delimiter='|';

1.2. postgreSQL

clickhouse官网-使用文档 (opens new window)

语法：
postgresql('host:port', 'database', 'table', 'user', 'password'[, `schema`])

• host:port — PostgreSQL 服务器地址.
• database — 远程数据库名称.
• table — 远程表名称.
• user — PostgreSQL 用户.
• password — 用户密码.
• schema — 非默认的表结构. 可选.

示例
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');

1.3. mysql

clickhouse官网-使用文档 (opens new window)

语法：
mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

• host:port — MySQL服务器地址.
• database — 远程数据库名称.
• table — 远程表名称.
• user — MySQL用户.
• password — 用户密码.

示例
SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123')

2. 常用函数

2.1. 时间函数

UTC转北京时间：

###字符串转时间###
toDateTime();

###时间截取###
select toTimeZone(toDateTime(substring('2023-06-01 11:02:00.000', 1, 19), 'UTC'), 'Asia/Shanghai');

###时间补位###
select toTimeZone(toDateTime(rightPad('2023-06-01 11:02', 19, ':00'), 'UTC'), 'Asia/Shanghai');

格式化时间：

toYYYYMMDD();
formatDateTime(datetime, '%Y-%m-%d');
formatDateTime(datetime, '%Y-%m-%d %H:%M:%S');
formatDateTime(date_add(hour, -4, now('UTC')), '%Y/%m/%d/%H');

2.2. json解析函数

--数可以随意的在多层嵌套结构下查找字段。如果存在多个匹配字段，则返回第一个匹配字段

--提取json   : 
	visitParamExtractRaw(参数,'名称')
--提取String : 
	visitParamExtractString(参数,'名称')
--提取Int    : 
	visitParamExtractInt(参数,'名称')
--提取Float  : 
	visitParamExtractFloat(参数,'名称')
--提取Bool   : 
	visitParamExtractBool(参数,'名称')

--逐层解析函数：
--提取json   : 
	JSONExtractRaw(参数,'名称')
--提取String : 
	JSONExtractString(参数,'名称')
--提取Int    : 
	JSONExtractInt(参数,'名称')
--提取Float  : 
	JSONExtractFloat(参数,'名称')
--提取Bool   : 
	JSONExtractBool(参数,'名称')

2.3. 数据类型转换函数

toString()
toInt32()     toInt64()     toUInt64()
toFloat32()   toFloat64()   

2.4. 字符串函数

select splitByChar(',',str)
select splitByString('asdf',str)
select subString(str,index_start,index_end)
select toStartOfDay(today() -1) ,  toStartOfDay(today())
select fromUnixTimestamp(toUInt64(1698537596000/1000), '%Y-%m-%d %R:%S')
select toTimeZone(toDateTime(substring('2023-06-01 11:02:00.000', 1, 19), 'UTC'), 'Asia/Shanghai');

-- MD5 加密
lower(hex(MD5())

--保留小数位数 select floor(10000/4648,5)

2.5. Array函数

-- 创建数组
	array(x1, ...)
-- arrayJoin()
	例：SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src

-- groupArray()
-- groupUniqArray()

-- has(arr,elem)
	例：SELECT has([1, 2, NULL], NULL)
-- hasAny(array1, array2)
	--array1 – 具有一组元素的任何类型的数组。
	--array2 – 具有一组元素的任何类型的数组。
	SELECT hasAny([-128, 1., 512], [1])       --返回 1.
	SELECT hasAny([[1, 2, 3, 4]], ['a', 'c']) --返回 0.
	
-- 子集 hasAll(set, subset)：检查一个数组是否是另一个数组的子集
	--set – 具有一组元素的任何类型的数组。
	--subset – 任何类型的数组，其元素应该被测试为set的子集。
	SELECT hasAll([1.0, 2, 3, 4], [1, 3]) --返回1
	SELECT hasAll([1], ['a'])             --返回0
	
-- 排序 arraySort() --升序      arrayReverseSort() 降序
	SELECT arraySort([1, 3, 3, 0]);
	
-- 去重 arrayDistinct() 
	SELECT arrayDistinct([1, 2, 2, 3, 1]);
-- 求和 arraySum(arr)
	SELECT arraySum([2, 3]) AS res;

2.6 条件函数

	if(cond, then, else)

	multiIf(cond_1, then_1, cond_2, then_2, ..., else)   --CASE的紧凑写法
	
	CASE [x] WHEN a THEN b [WHEN ... THEN ...] [ELSE c] END