playlet/sql/central/clickhouse_central.sql

-- central
-- 新增广告主维度实时消耗数据表
create table advertiser_rolling_report
(
    report_date     DateTime('Asia/Shanghai') default now(),
    add_time        DateTime('Asia/Shanghai') default now(),
    tg_platform     Int32                     default 0,
    advertiser_id   Int64                     default 0,
    advertiser_name String                    default '',
    cost            Float32                   default 0.,
    show            Int32                     default 0,
    click           Int32                     default 0,
    convert         Int32                     default 0
)
    engine = MergeTree PARTITION BY toYYYYMM(add_time)
        ORDER BY (report_date, add_time, tg_platform, advertiser_id, advertiser_name)
        SETTINGS index_granularity = 8192;

-- 新增广告主维度每日消耗数据表
create table advertiser_daily_report
(
    report_date     DateTime('Asia/Shanghai') default now(),
    add_time        DateTime('Asia/Shanghai') default now(),
    tg_platform     Int32                     default 0,
    advertiser_id   Int64                     default 0,
    advertiser_name String                    default '',
    cost            Float32                   default 0.,
    show            Int32                     default 0,
    click           Int32                     default 0,
    convert         Int32                     default 0
)
    engine = MergeTree PARTITION BY toYYYYMM(add_time)
        ORDER BY (report_date, add_time, tg_platform, advertiser_id, advertiser_name)
        SETTINGS index_granularity = 8192;

-- 广告事件
create table ad_event
(
    id                     String                    default '',
    package_name           String                    default '',
    channel_name           String                    default '',
    app_version_code       String                    default '',
    ssid                   String                    default '',
    device_id              String                    default '',
    ip                     String                    default '',
    brand                  String                    default '',
    model                  String                    default '',
    os_version             String                    default '',
    reg_time               DateTime('Asia/Shanghai') default now(),
    request_id             String                    default '',
    trace_id               String                    default '',
    ad_event_type          String                    default '',
    ad_type                String                    default '',
    ad_func_id             String                    default '',
    ecpm                   Float32                   default 0.,
    ad_err                 String                    default '',
    ad_platform            String                    default '',
    position_id            String                    default '',
    real_ad_platform       String                    default '',
    real_position_id       String                    default '',
    extra_app_name         String                    default '',
    extra_package_name     String                    default '',
    extra_app_version_code String                    default '',
    extra_developer        String                    default '',
    create_time            DateTime('Asia/Shanghai') default now(),
    event_time             DateTime('Asia/Shanghai') default now(),
    event_ts               Int64                     default 0
)
    engine = ReplacingMergeTree(event_ts)
        PARTITION BY toYYYYMM(event_time)
        ORDER BY (id, event_ts)
        SETTINGS index_granularity = 8192;