playlet/sql/central/clickhouse_central_update_20250219.sql

create table central.tb_event_ad_sdk_error
(
    `$product_id`       String                    default '',
    `$type`             String                    default '',
    `$event`            String                    default '',
    `$time`             Int64                     default 0,
    `$flush_time`       Int64                     default 0,
    `$package_name`     String                    default '',
    `$app_platform`     Int64                     default 0,
    `$channel_name`     String                    default '',
    `$app_version_name` String                    default '',
    `$app_version_code` String                    default '',
    `$oaid`             String                    default '',
    `$aaid`             String                    default '',
    `$android_id`       String                    default '',
    `$idfa`             String                    default '',
    `$imei`             String                    default '',
    `$idfv`             String                    default '',
    `$machine_id`       String                    default '',
    `$device_id`        String                    default '',
    `$ssid`             String                    default '',
    `$reg_time`         DateTime('Asia/Shanghai') default now(),
    `$brand`            String                    default '',
    `$model`            String                    default '',
    `$os_version`       String                    default '',
    `$ip`               String                    default '',
    `$city`             String                    default '',
    `$country`          String                    default '',

    `$app_code`         String                    default '',
    `$tg_platform`      Int64                     default 0,

    `$company`          String                    default '',
    `$advertiser_id`    String                    default '',
    `$gid`              String                    default '',
    `$aid`              String                    default '',
    `$cid`              String                    default '',

    `$adRequestId`      String                    default '',
    `$adTraceId`        String                    default '',
    `$adType`           Int64                     default 0,
    `$adErrorMsg`       String                    default '',
    `$adPlatform`       Int64                     default 0,
    `$adPositionId`     String                    default '',
    `$adFuncId`         String                    default '',
    `$adRealPlatform`   Int64                     default 0,
    `$adRealPositionId` String                    default '',
    `$adEcpm`           Float64                   default 0,

    `$adErrorCode`      String                    default ''
)
    engine = ReplacingMergeTree PARTITION BY toYYYYMM(fromUnixTimestamp64Milli(`$time`))
        PRIMARY KEY (`$product_id`, `$type`, `$event`, `$device_id`, `$time`)
        ORDER BY (`$product_id`, `$type`, `$event`, `$device_id`, `$time`,
                  `$flush_time`,
                  `$package_name`,
                  `$app_platform`,
                  `$channel_name`,
                  `$app_version_name`,
                  `$app_version_code`,
                  `$oaid`,
                  `$aaid`,
                  `$android_id`,
                  `$idfa`,
                  `$imei`,
                  `$idfv`,
                  `$machine_id`,
                  `$ssid`,
                  `$reg_time`,
                  `$brand`,
                  `$model`,
                  `$os_version`,
                  `$ip`,
                  `$city`,
                  `$country`,
                  `$app_code`,
                  `$tg_platform`,
                  `$company`,
                  `$advertiser_id`,
                  `$gid`,
                  `$aid`,
                  `$cid`
            )
        SETTINGS index_granularity = 8192;


-- 使用PRIMARY KEY + ORDER BY
create table central.tb_event_base
(
    `$product_id`        String                    default '',
    `$type`              String                    default '',
    `$event`             String                    default '',
    `$time`              Int64                     default 0,
    `$flush_time`        Int64                     default 0,
    `$package_name`      String                    default '',
    `$app_platform`      Int64                     default 0,
    `$channel_name`      String                    default '',
    `$app_version_name`  String                    default '',
    `$app_version_code`  String                    default '',
    `$oaid`              String                    default '',
    `$aaid`              String                    default '',
    `$android_id`        String                    default '',
    `$idfa`              String                    default '',
    `$imei`              String                    default '',
    `$idfv`              String                    default '',
    `$machine_id`        String                    default '',
    `$device_id`         String                    default '',
    `$ssid`              String                    default '',
    `$reg_time`          DateTime('Asia/Shanghai') default now(),
    `$brand`             String                    default '',
    `$model`             String                    default '',
    `$os_version`        String                    default '',
    `$ip`                String                    default '',
    `$city`              String                    default '',
    `$country`           String                    default '',

    `$app_code`          String                    default '',
    `$tg_platform`       Int64                     default 0,

    `$company`           String                    default '',
    `$advertiser_id`     String                    default '',
    `$gid`               String                    default '',
    `$aid`               String                    default '',
    `$cid`               String                    default '',

    `$app_used_time`     Int64                     default 0,
    `$payment_amount`    Int64                     default 0,
    `$payment_currency`  String                    default '',
    `$payment_method`    String                    default '',
    `$payment_item_name` String                    default '',
    `$page_name`         String                    default '',
    `$page_used_time`    Int64                     default 0,
    `$payment_order_no`  String                    default '',
    `$session_id`        String                    default ''
)
    engine = ReplacingMergeTree PARTITION BY toYYYYMM(fromUnixTimestamp64Milli(`$time`))
        PRIMARY KEY (`$product_id`, `$type`, `$event`, `$device_id`, `$time`)
        ORDER BY (`$product_id`, `$type`, `$event`, `$device_id`, `$time`,
                  `$flush_time`,
                  `$package_name`,
                  `$app_platform`,
                  `$channel_name`,
                  `$app_version_name`,
                  `$app_version_code`,
                  `$oaid`,
                  `$aaid`,
                  `$android_id`,
                  `$idfa`,
                  `$imei`,
                  `$idfv`,
                  `$machine_id`,
                  `$ssid`,
                  `$reg_time`,
                  `$brand`,
                  `$model`,
                  `$os_version`,
                  `$ip`,
                  `$city`,
                  `$country`,
                  `$app_code`,
                  `$tg_platform`,
                  `$company`,
                  `$advertiser_id`,
                  `$gid`,
                  `$aid`,
                  `$cid`
            )
        SETTINGS index_granularity = 8192;