clickhouse--JSON解析案例

JSON解析案例

clickhouse加载文件类型
json解析字符串函数

--建表
drop table if exists tb_ods_log;
create table tb_ods_log(line String)engine=Log();

--导入数据
clickhouse-client --port 9500 --password lekeBigdata -q 'insert into tb_ods_log format JSONAsString' <event.log
select * from tb_ods_log limit 1;
┌─line────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 
{"account":"14d9TM","appId":"cn.doitedu.app1","appVersion":"2.0","carrier":"C"deviceId":"KV1ABkbMqqwn","deviceType":"REDMI-
5","eventId":"adClick","ip":"180.62.91.180","latitude":25.213352855705107,"longitude":100.58718180057615,"netType":"WIFI","osName":"android","osVersion":"6.5","properties":
{"adCampain":"7","adId":"6","adLocation":"3","pageId":"41"},"releaseChannel":""resolution":"1024*768","sessionId":"rGeXt8NOrD1","timeStamp":1602063058368}    
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

--解析account,deviceId,ip,sessionId,eventId,properties,timestamp
with 
visitParamExtractString(line,'account') as account,
visitParamExtractString(line,'deviceId') as deviceId,
visitParamExtractString(line,'ip') as ip,
visitParamExtractString(line,'sessionId') as sessionId,
visitParamExtractString(line,'eventId') as eventId,
visitParamExtractRaw(line,'properties') as properties,
visitParamExtractInt(line,'timestamp') as timestamp
select account,deviceId,ip,sessionId,eventId,properties,timestamp from tb_ods_log;