APIJSON连接ClickHouse的问题 #2
Description
APIJSON连接ClickHouse的问题
ClickHouse不支持REGEXP关键字，ClickHouse默认区分大小写
请求：
{
"[]": {
"join": "&/User/id@",
"Moment": {
"@column": "id,userId,content"
},
"User": {
"name~": [
"a",
"t"
],
"@column": "id,name",
"id@": "/Moment/userId"
}
}
}生成SQL:
SELECT Moment.id,Moment.userId,Moment.content, User.id,User.name FROM sys.Moment AS Moment INNER JOIN sys.apijson_user AS User ON User.id = Moment.userId WHERE ( ( ( (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't') ) ) ) LIMIT 10ClickHouse执行出错：
Syntax error: failed at position 229 ('REGEXP') (line 2, col 112):
SELECT Moment.id,Moment.userId,Moment.content, User.id,User.name FROM sys.Moment AS Moment
INNER JOIN sys.apijson_user AS User ON User.id = Moment.userId WHERE ( ( ( (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't') ) ) ) LIMIT 10
Expected one of: DoubleColon, LIKE, GLOBAL NOT IN, AS, DIV, IS, UUID, OR, QuestionMark, BETWEEN, NOT LIKE, MOD, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, token, NOT IN, GLOBAL IN
ClickHouse不支持json_length，json_contains等函数
"ClickHouse exception, code: 46, host: null, port: 0; Code: 46, e.displayText() = DB::Exception: Unknown function json_contains. Maybe you meant: ['mapContains']: While processing SELECT id, sex, name, tag, head, contactIdList, pictureList, date FROM sys.apijson_user WHERE isNotNull(contactIdList) AND json_contains(contactIdList, '38710') LIMIT 3 (version 21.7.3.14 (official build)) "
"ClickHouse exception, code: 46, host: null, port: 0; Code: 46, e.displayText() = DB::Exception: Unknown function json_length. Maybe you meant: ['JSONLength','CHAR_LENGTH']: While processing SELECT userId, pictureList FROM sys.Moment WHERE (userId = 82002) AND (json_length(pictureList) > 0) LIMIT 3 (version 21.7.3.14 (official build)) "
ClickHouse不支持JSON格式存储数据
前：
"contactIdList": [ 82005,
82001,
38710 ] 后：
"contactIdList": "[82005, 82001, 38710]" 解释：
ClickHouse不支持Json格式的数据类型，建表时使用了字符串类型，但使用FORMAT JSON命令可以以JSON格式输出数据
注释：
受ClickHouse支持的输入格式可用于提交给INSERT语句、从文件表(File,URL,HDFS或者外部目录)执行SELECT语句，受支持的输出格式可用于格式化SELECT语句的返回结果，或者通过INSERT写入到文件表
| 格式 | 输入 | 输出 |
|---|---|---|
| JSON | ✗ | ✔ |
| JSONAsString | ✔ | ✗ |
| JSONAsString | ✗ | ✔ |