ClickHouse使用（九）

1 Kafka表引擎（kafka数据接入clickHouse）

一、创建缓存队列

如：
– heaven_eye_event_log.app_page_load_log_queue definition

CREATE TABLE heaven_eye_event_log.app_page_load_log_queue
(

`devId` Nullable(String),

`eventTime` String

)
ENGINE = Kafka
SETTINGS kafka_broker_list = ‘apmkafka0.tencent.ziroom.com:9092,
\napmkafka1.tencent.ziroom.com:9092,
\napmkafka2.tencent.ziroom.com:9092’,
kafka_topic_list = ‘app-pageLoad-log’,
kafka_group_name = ‘heaven_eye_clickhouse_test1’,
kafka_format = ‘JSONEachRow’,
kafka_num_consumers = 6;

（1）特殊配置：
kafka_skip_broken_message:当出现错误数据时可以忽略的错误数据条数
kafka_row_delimeter:标识判定一行数据的结束符
kafka_commit_every_batch:提交offset的频率

二、创建存储数据表

CREATE TABLE heaven_eye_event_log.app_page_load_log
(

`devId` Nullable(String),

`eventTime` String

)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(parseDateTimeBestEffortOrZero(substring(eventTime,
1,
19)))
ORDER BY eventTime
SETTINGS index_granularity = 8192;

三、创建消费视图

– heaven_eye_event_log.app_page_load_log_consumer source

CREATE MATERIALIZED VIEW heaven_eye_event_log.app_page_load_log_consumer TO heaven_eye_event_log.app_page_load_log
AS
SELECT
devId,

eventTime

FROM heaven_eye_event_log.app_page_load_log_queue;

注意：在选择分区字段时，注意分区字段不能出现空，或者异常值，否则会影响消费者正常同步数据，使得，数据同步终止

四.集群环境创建表

CREATE TABLE event_production on cluster app_cluster (
event String,
event_time String,
time UInt64,
dt String
) ENGINE = ReplicatedMergeTree(’/clickhouse/tables/{layer}-{shard}/heaven_eye_event_log/event_production’, ‘{replica}’)
partition by dt
order by event

注意：ENGINE = ReplicatedMergeTree(’/clickhouse/tables/{layer}-{shard}/heaven_eye_event_log/event_production’, ‘{replica}’),使用集群写法

五.集群删除表操作语句

/** 删除表 /
drop table event_test_jiafu_test on cluster app_cluster;
/* 添加字段 /
alter table app_crash_log_test ON CLUSTER app_cluster add column branch String default ‘’;
/* 删除字段 /
alter table event_test_production ON CLUSTER app_cluster DROP column test;
/* 修改字段类型 */
alter table app_network_log_test ON CLUSTER app_cluster modify column dnsState Nullable(String);

六.JDBC引擎

（1）相对于Mysql引擎而言，jdbc引擎可以支持多种数据库，依赖名称为clickhouse-jdbc-bridge 的查询代理服务。clickhouse-jdbc-bridge 是Java实现的Sql代理服务

项目地址:https://github.com/ClickHouse/clickhouse-jdbc-bridge
自动转换数据类型

（2）ClickHouse jdbc标准库

Int8 TINYINT
Int16 SMALLINT
Int32 INTEGER
Int64 BIGINT
Float32 Float
Float32 REAL
Float64 DOUBLE
DateTime TIMESTAMP
DateTime TIME
Date DATE
UInt8 BIT
UInt8 BOOLEAN
String CHAR
String VARCHAR
String LONGVARCHAR

（3） 下载运行 clickhouse-jdbc-bridge

java -jar ./clickhouse-jdbc-bridge-1.0.jar --driver-path /chbase/jdbc-bridge --listen-host ch5.nauu.com
–driver-path 用于指定数据库驱动的目录
–listen-host 用于代理服务的监听端口
config全局代理服务

ch5.nauu.com
9019

(4)创建jdbc表

crate table table_name（
id Int32,
name String
）engine = jdbc(‘jdbc:…’)

(5)查询表

select id,name form table_name