clickhouse创建kafka引擎表

最新推荐文章于 2024-05-14 22:01:07 发布

数智达人

最新推荐文章于 2024-05-14 22:01:07 发布

阅读量949

点赞数

文章标签： kafka clickhouse

本文链接：https://blog.csdn.net/qq_25043977/article/details/127542386

版权

1 创建日志明细表

create table default.yinew_detail

(
account String , appId String , appVersion String , carrier String , deviceId String , deviceType String , 
eventId String ,
ip String ,
latitude Float64 ,
longitude Float64 , netType String , osName String , osVersion String , 
properties Map(String,String), 
releaseChannel String, resolution String, sessionId String, 
timeStamp Int64 , INDEX u (deviceId) TYPE minmax GRANULARITY 3, INDEX t (timeStamp) TYPE minmax GRANULARITY 3

) ENGINE = MergeTree()

ORDER BY (deviceId,timeStamp)
;

特别注意:clickhouse只在v21.1.2.15及之后版本支持Map类型,并且需要

set allow_experimental_map_type = 1;

2创建kafka引擎表

create table default.event_detail_kafka

(
account String , appId String , appVersion String , carrier String , deviceId String , deviceType String , eventId String ,
ip String ,
latitude Float64 ,
longitude Float64 , netType String , osName String , osVersion String , properties Map(String,String), releaseChannel String, resolution String, sessionId String, 
timeStamp Int64
) ENGINE = Kafka('linux01:9092,linux02:9092,linux03:9092','app_log','group1','JSONEachRow');

其中’linux01:9092,linux02:9092,linux03:9092’ 为kafka集群,'app_log’为kafka的topic,'group1’为消费者组,'JSONEachRow’为日志格式.

3创建物化视图,将kafka引擎表关联到日志明细表

create MATERIALIZED VIEW event_view TO yinew_detail

as
select

account , appId , appVersion , carrier , deviceId , deviceType , eventId ,
ip ,
latitude ,
longitude , netType , osName , osVersion , properties , releaseChannel , resolution , sessionId , 
timeStamp

from event_detail_kafka
;