-- 创建数据库
:) CREATE DATABASE IF NOT EXISTS dwd;

-- 创建数据同步表
:) DROP TABLE IF EXISTS dwd.event_middle;
:) CREATE TABLE IF NOT EXISTS dwd.event_middle (
       eid UInt64,
       etype String,
       ename String,
       esource String,
       etime DateTime,
       userid UInt64,
       aid UInt64,
       aname String,
       tid String,
       tname String,
       context String
   ) ENGINE = Kafka() SETTINGS 
     kafka_broker_list = '172.16.185.176:9092',
     kafka_topic_list = 'eventbus',
     kafka_group_name = 'clickhouse',
     kafka_format = 'JSONEachRow';

-- 创建物化视图
:) DROP VIEW IF EXISTS dwd.event_materialized_view;
:) CREATE MATERIALIZED VIEW IF NOT EXISTS dwd.event_materialized_view
   TO dwd.event_table
   SELECT
       userid, ename, etime
   FROM dwd.event_middle;

-- 创建用户行为数据表
:) DROP TABLE IF EXISTS dwd.event_table;
:) CREATE TABLE IF NOT EXISTS dwd.event_table (
       userid UInt64,
       ename String,
       etime DateTime
   ) ENGINE = MergeTree()
     ORDER BY userId;