Skip to content

M
mobvista-dmp
Switch branch/tag
Find file
Normal viewHistoryPermalink
clickhouse.sql 33 KB
Newer Older
init mobvista-dmp
5f348fef
 
wang-jinfeng committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 
--  dwh
--  baichuan_exclude_ios_daily
CREATE TABLE dwh.baichuan_exclude_ios_daily (`dt` Date, `device_id` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id) SETTINGS index_granularity = 8192;
--  baichuan_exclude_ios_daily_all
CREATE TABLE dwh.baichuan_exclude_ios_daily_all (`dt` Date, `device_id` String) ENGINE = Distributed(cluster_1st, dwh, baichuan_exclude_ios_daily, rand());
--  baichuan_install_daily
CREATE TABLE dwh.baichuan_install_daily (`dt` Date, `device_id` String, `device_type` String, `platform` String, `package_name` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id) SETTINGS index_granularity = 8192;
--  baichuan_install_daily_all
CREATE TABLE dwh.baichuan_install_daily_all (`dt` Date, `device_id` String, `device_type` String, `platform` String, `package_name` String) ENGINE = Distributed(cluster_1st, dwh, baichuan_install_daily, rand());
--  etl_baichuan_daily
CREATE TABLE dwh.etl_baichuan_daily (`dt` Date, `device_id` String, `app_id` Int32, `app_os` Int32, `tag` Int32, `update_date` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, app_id, app_os, update_date) SETTINGS index_granularity = 8192;
--  etl_baichuan_daily_all
CREATE TABLE dwh.etl_baichuan_daily_all (`dt` Date, `device_id` String, `app_id` Int32, `app_os` Int32, `tag` Int32, `update_date` String) ENGINE = Distributed(cluster_1st, dwh, etl_baichuan_daily, rand());
--  ods_user_info
CREATE TABLE dwh.ods_user_info (`dt` Date, `device_id` String, `platform` String, `model` String, `os_version` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `behavior` String, `frequency` String, `tag_week` String, `tag_month` String, `update_date` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, platform, country, age, gender, update_date) SETTINGS index_granularity = 8192;
--  new
CREATE TABLE dwh.ods_user_info (`dt` Date, `device_id` String, `platform` String, `model` String, `os_version` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `behavior` String, `frequency` String, `tag_week` String, `tag_month` String, `region` Array(String), `update_date` String, `publish_date` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, platform, country, age, gender, update_date) SETTINGS index_granularity = 8192;
--  ods_user_info_all
CREATE TABLE dwh.ods_user_info_all (`dt` Date, `device_id` String, `platform` String, `model` String, `os_version` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `behavior` String, `frequency` String, `tag_week` String, `tag_month` String, `update_date` String) ENGINE = Distributed(cluster_1st, dwh, ods_user_info, rand());
--  new
CREATE TABLE dwh.ods_user_info_all (`dt` Date, `device_id` String, `platform` String, `model` String, `os_version` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `behavior` String, `frequency` String, `tag_week` String, `tag_month` String, `region` Array(String), `update_date` String, `publish_date` String) ENGINE = Distributed(cluster_1st, dwh, ods_user_info, rand());
--  realtime_service_hour
CREATE TABLE dwh.realtime_service_hour (`dt` Date, `hour` FixedString(2), `region` String, `device_id` String, `platform` String, `age` Int32, `gender` Int32, `country` FixedString(2), `interest` Array(String), `install_apps` Array(UInt32)) ENGINE = ReplacingMergeTree() PARTITION BY (toYYYYMMDD(dt), hour, region) ORDER BY (dt, hour, region, device_id, platform) SETTINGS index_granularity = 8192;
--  realtime_service_hour_all
CREATE TABLE dwh.realtime_service_hour_all (`dt` Date, `hour` FixedString(2), `region` String, `device_id` String, `platform` String, `age` Int32, `gender` Int32, `country` FixedString(2), `interest` Array(String), `install_apps` Array(UInt32)) ENGINE = Distributed(cluster_1st, dwh, realtime_service_hour, rand());
--  tracking_daily
CREATE TABLE dwh.tracking_daily (`dt` Date, `device_id` String, `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = MergeTree(dt, (device_id, offer_id, id, event_name, event_type, log_type), 8192);
--  tracking_daily_all
CREATE TABLE dwh.tracking_daily_all (`dt` Date, `device_id` String, `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, tracking_daily, rand());
--  tracking_insight_daily
CREATE TABLE dwh.tracking_insight_daily (`dt` Date, `device_id` String, `platform` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, platform, country, age, gender, offer_id, id, event_name, event_type, log_type) SETTINGS index_granularity = 8192;
--  tracking_insight_daily_all
CREATE TABLE dwh.tracking_insight_daily_all (`dt` Date, `device_id` String, `platform` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, tracking_insight_daily, rand());
--  joypac_insight_daily
CREATE TABLE dwh.joypac_insight_daily (`dt` Date, `device_id` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String)) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, country, age, gender) SETTINGS index_granularity = 8192;
--  joypac_insight_daily_all
CREATE TABLE dwh.joypac_insight_daily_all (`dt` Date, `device_id` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String)) ENGINE = Distributed(cluster_1st, dwh, joypac_insight_daily, rand());
--  sss_tracking_daily
CREATE TABLE dwh.sss_tracking_daily (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `offer_id` Array(String), `event_name` Array(String), `event_type` Array(String), `log_type` Int32) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.sss_tracking_daily_all(`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`offer_id` Array(String),`event_name` Array(String),`event_type` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, sss_tracking_daily, rand());
--  sss_tracking_insight_daily
CREATE TABLE dwh.sss_tracking_insight_daily (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` Array(String), `event_name` Array(String), `event_type` Array(String), `log_type` Int32) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.sss_tracking_insight_daily_all (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`age` Int32,`gender` Int32,`install_apps` Array(Int32),`interest` Array(String),`offer_id` Array(String),`event_name` Array(String),`event_type` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, sss_tracking_insight_daily, rand());
--  adn_tracking_daily
CREATE TABLE dwh.adn_tracking_daily (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `campaign_id` Array(String), `event_name` Array(String), `event_type` Array(String), `app_id` Array(String), `log_type` Int32) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, event_type, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.adn_tracking_daily_all (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`campaign_id` Array(String),`event_name` Array(String),`event_type` Array(String),`app_id` Array(String),`log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, adn_tracking_daily, rand());
--  adn_tracking_insight_daily
CREATE TABLE dwh.adn_tracking_insight_daily (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `campaign_id` Array(String), `event_name` Array(String), `event_type` Array(String), `app_id` Array(String), `log_type` Int32) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, event_type, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.adn_tracking_insight_daily_all (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`age` Int32,`gender` Int32,`install_apps` Array(Int32),`interest` Array(String),`campaign_id` Array(String),`event_name` Array(String),`event_type` Array(String),`app_id` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, adn_tracking_insight_daily, rand());
--  dwc
--  realtime_service_result
CREATE TABLE dwc.realtime_service_result (`dt` Date, `hour` FixedString(2), `region` String, `device_id` String, `age` Int32, `gender` Int32, `country` FixedString(2), `interest` Array(String), `install_apps` Array(UInt32), `frequency` String, `flag` UInt8) ENGINE = ReplacingMergeTree() PARTITION BY (toYYYYMMDD(dt), hour, region) ORDER BY (dt, hour, region, flag, device_id) SETTINGS index_granularity = 8192;
--  realtime_service_result_all
CREATE TABLE dwc.realtime_service_result_all (`dt` Date, `hour` FixedString(2), `region` String, `device_id` String, `age` Int32, `gender` Int32, `country` FixedString(2), `interest` Array(String), `install_apps` Array(UInt32), `frequency` String, `flag` UInt8) ENGINE = Distributed(cluster_1st, dwc, realtime_service_result, rand());

DROP TABLE dwh.ods_user_info;
DROP TABLE dwh.ods_user_info_all;

CREATE DATABASE dwh;
CREATE TABLE dwh.baichuan_exclude_ios_daily (`dt` Date, `device_id` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.baichuan_exclude_ios_daily_all (`dt` Date, `device_id` String) ENGINE = Distributed(cluster_1st, dwh, baichuan_exclude_ios_daily, rand());
CREATE TABLE dwh.baichuan_install_daily (`dt` Date, `device_id` String, `device_type` String, `platform` String, `package_name` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.baichuan_install_daily_all (`dt` Date, `device_id` String, `device_type` String, `platform` String, `package_name` String) ENGINE = Distributed(cluster_1st, dwh, baichuan_install_daily, rand());
CREATE TABLE dwh.etl_baichuan_daily (`dt` Date, `device_id` String, `app_id` Int32, `app_os` Int32, `tag` Int32, `update_date` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, app_id, app_os, update_date) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.etl_baichuan_daily_all (`dt` Date, `device_id` String, `app_id` Int32, `app_os` Int32, `tag` Int32, `update_date` String) ENGINE = Distributed(cluster_1st, dwh, etl_baichuan_daily, rand());
CREATE TABLE dwh.ods_user_info (`dt` Date, `device_id` String, `platform` String, `device_model` String, `os_version` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `behavior` String, `frequency` String, `tag_week` String, `tag_month` String, `region` Array(String), `update_date` String, `publish_date` String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, platform, country, age, gender, update_date) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.ods_user_info_all (`dt` Date, `device_id` String, `platform` String, `device_model` String, `os_version` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `behavior` String, `frequency` String, `tag_week` String, `tag_month` String, `region` Array(String), `update_date` String, `publish_date` String) ENGINE = Distributed(cluster_1st, dwh, ods_user_info, rand());
CREATE TABLE dwh.tracking_daily (`dt` Date, `device_id` String, `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = MergeTree(dt, (device_id, offer_id, id, event_name, event_type, log_type), 8192);
CREATE TABLE dwh.tracking_daily_all (`dt` Date, `device_id` String, `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, tracking_daily, rand());
CREATE TABLE dwh.tracking_insight_daily (`dt` Date, `device_id` String, `platform` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, platform, country, age, gender, offer_id, id, event_name, event_type, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.tracking_insight_daily_all (`dt` Date, `device_id` String, `platform` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, tracking_insight_daily, rand());
CREATE TABLE dwh.joypac_daily(`dt` Date,`device_id` String)ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt)ORDER BY (dt, device_id)SETTINGS index_granularity = 8192;
CREATE TABLE dwh.joypac_daily_all (`dt` Date,`device_id` String)ENGINE = Distributed(cluster_1st, dwh, joypac_daily, rand());
CREATE TABLE dwh.joypac_insight_daily (`dt` Date, `device_id` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String)) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, country, age, gender) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.joypac_insight_daily_all (`dt` Date, `device_id` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String)) ENGINE = Distributed(cluster_1st, dwh, joypac_insight_daily, rand());
CREATE TABLE dwh.sss_tracking_daily (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `offer_id` Array(String), `event_name` Array(String), `event_type` Array(String), `log_type` Int32) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.sss_tracking_daily_all(`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`offer_id` Array(String),`event_name` Array(String),`event_type` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, sss_tracking_daily, rand());
CREATE TABLE dwh.sss_tracking_insight_daily (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` Array(String), `event_name` Array(String), `event_type` Array(String), `log_type` Int32) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.sss_tracking_insight_daily_all (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`age` Int32,`gender` Int32,`install_apps` Array(Int32),`interest` Array(String),`offer_id` Array(String),`event_name` Array(String),`event_type` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, sss_tracking_insight_daily, rand());
CREATE TABLE dwh.adn_tracking_daily (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `campaign_id` Array(String), `event_name` Array(String), `event_type` Array(String), `app_id` Array(String), `log_type` Int32) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, event_type, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.adn_tracking_daily_all (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`campaign_id` Array(String),`event_name` Array(String),`event_type` Array(String),`app_id` Array(String),`log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, adn_tracking_daily, rand());
CREATE TABLE dwh.adn_tracking_insight_daily (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `campaign_id` Array(String), `event_name` Array(String), `event_type` Array(String), `app_id` Array(String), `log_type` Int32) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, event_type, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.adn_tracking_insight_daily_all (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`age` Int32,`gender` Int32,`install_apps` Array(Int32),`interest` Array(String),`campaign_id` Array(String),`event_name` Array(String),`event_type` Array(String),`app_id` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, adn_tracking_insight_daily, rand());

CREATE TABLE dwh.realtime_service_hour (`dt` Date, `hour` FixedString(2), `region` String, `device_id` String, `platform` String, `age` Int32, `gender` Int32, `country` FixedString(2), `interest` Array(String), `install_apps` Array(UInt32)) ENGINE = ReplacingMergeTree() PARTITION BY (toYYYYMMDD(dt), hour, region) ORDER BY (dt, hour, region, device_id, platform) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.realtime_service_hour_all (`dt` Date, `hour` FixedString(2), `region` String, `device_id` String, `platform` String, `age` Int32, `gender` Int32, `country` FixedString(2), `interest` Array(String), `install_apps` Array(UInt32)) ENGINE = Distributed(cluster_1st, dwh, realtime_service_hour, rand());
CREATE TABLE dwc.realtime_service_result (`dt` Date, `hour` FixedString(2), `region` String, `device_id` String, `age` Int32, `gender` Int32, `country` FixedString(2), `interest` Array(String), `install_apps` Array(UInt32), `frequency` String, `flag` UInt8) ENGINE = ReplacingMergeTree() PARTITION BY (toYYYYMMDD(dt), hour, region) ORDER BY (dt, hour, region, flag, device_id) SETTINGS index_granularity = 8192;
CREATE TABLE dwc.realtime_service_result_all (`dt` Date, `hour` FixedString(2), `region` String, `device_id` String, `age` Int32, `gender` Int32, `country` FixedString(2), `interest` Array(String), `install_apps` Array(UInt32), `frequency` String, `flag` UInt8) ENGINE = Distributed(cluster_1st, dwc, realtime_service_result, rand());


CREATE TABLE dwh.ods_user_info ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `platform` String, `device_model` String, `os_version` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `behavior` String, `frequency` String, `tag_week` String, `tag_month` String, `region` Array(String), `update_date` String, `publish_date` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods_user_info', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, platform, country, age, gender, update_date) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.ods_user_info_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `platform` String, `device_model` String, `os_version` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `behavior` String, `frequency` String, `tag_week` String, `tag_month` String, `region` Array(String), `update_date` String, `publish_date` String) ENGINE = Distributed(cluster_1st, dwh, ods_user_info, rand());

CREATE TABLE dwh.baichuan_exclude_ios_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/baichuan_exclude_ios_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.baichuan_exclude_ios_daily_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String) ENGINE = Distributed(cluster_1st, dwh, baichuan_exclude_ios_daily, rand());
CREATE TABLE dwh.baichuan_install_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_type` String, `platform` String, `package_name` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/baichuan_install_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.baichuan_install_daily_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_type` String, `platform` String, `package_name` String) ENGINE = Distributed(cluster_1st, dwh, baichuan_install_daily, rand());
CREATE TABLE dwh.etl_baichuan_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `app_id` Int32, `app_os` Int32, `tag` Int32, `update_date` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/etl_baichuan_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, app_id, app_os, update_date) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.etl_baichuan_daily_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `app_id` Int32, `app_os` Int32, `tag` Int32, `update_date` String) ENGINE = Distributed(cluster_1st, dwh, etl_baichuan_daily, rand());

CREATE TABLE dwh.tracking_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/tracking_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (device_id, offer_id, id, event_name, event_type, log_type) SETTINGS  index_granularity = 8192;
CREATE TABLE dwh.tracking_daily_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, tracking_daily, rand());
CREATE TABLE dwh.tracking_insight_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `platform` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/tracking_insight_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, platform, country, age, gender, offer_id, id, event_name, event_type, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.tracking_insight_daily_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `platform` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` String, `id` String, `event_name` String, `event_type` String, `log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, tracking_insight_daily, rand());
CREATE TABLE dwh.joypac_daily ON CLUSTER cluster_1st (`dt` Date,`device_id` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/joypac_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id)SETTINGS index_granularity = 8192;
CREATE TABLE dwh.joypac_daily_all ON CLUSTER cluster_1st (`dt` Date,`device_id` String)ENGINE = Distributed(cluster_1st, dwh, joypac_daily, rand());
CREATE TABLE dwh.joypac_insight_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String)) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/joypac_insight_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, country, age, gender) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.joypac_insight_daily_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `country` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String)) ENGINE = Distributed(cluster_1st, dwh, joypac_insight_daily, rand());
CREATE TABLE dwh.sss_tracking_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `offer_id` Array(String), `event_name` Array(String), `event_type` Array(String), `log_type` Int32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/sss_tracking_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.sss_tracking_daily_all ON CLUSTER cluster_1st (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`offer_id` Array(String),`event_name` Array(String),`event_type` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, sss_tracking_daily, rand());
CREATE TABLE dwh.sss_tracking_insight_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `offer_id` Array(String), `event_name` Array(String), `event_type` Array(String), `log_type` Int32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/sss_tracking_insight_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.sss_tracking_insight_daily_all ON CLUSTER cluster_1st (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`age` Int32,`gender` Int32,`install_apps` Array(Int32),`interest` Array(String),`offer_id` Array(String),`event_name` Array(String),`event_type` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, sss_tracking_insight_daily, rand());
CREATE TABLE dwh.adn_tracking_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `campaign_id` Array(String), `event_name` Array(String), `event_type` Array(String), `app_id` Array(String), `log_type` Int32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/adn_tracking_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, event_type, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.adn_tracking_daily_all ON CLUSTER cluster_1st (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`campaign_id` Array(String),`event_name` Array(String),`event_type` Array(String),`app_id` Array(String),`log_type` Int32) ENGINE = Distributed(cluster_1st, dwh, adn_tracking_daily, rand());
CREATE TABLE dwh.adn_tracking_insight_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_model` String, `os_version` String, `country` String, `city` String, `age` Int32, `gender` Int32, `install_apps` Array(Int32), `interest` Array(String), `campaign_id` Array(String), `event_name` Array(String), `event_type` Array(String), `app_id` Array(String), `log_type` Int32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/adn_tracking_insight_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, event_type, log_type) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.adn_tracking_insight_daily_all ON CLUSTER cluster_1st (`dt` Date,`device_id` String,`device_model` String,`os_version` String,`country` String,`city` String,`age` Int32,`gender` Int32,`install_apps` Array(Int32),`interest` Array(String),`campaign_id` Array(String),`event_name` Array(String),`event_type` Array(String),`app_id` Array(String),`log_type` Int32)ENGINE = Distributed(cluster_1st, dwh, adn_tracking_insight_daily, rand());

CREATE TABLE dwh.etl_iqiyi_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_type` String, `platform` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/etl_iqiyi_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, device_type, platform) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.etl_iqiyi_daily_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_type` String, `platform` String) ENGINE = Distributed(cluster_1st, dwh, etl_iqiyi_daily, rand());

CREATE TABLE dwh.etl_iqiyi_install_daily ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_type` String, `platform` String, `package_name` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/etl_iqiyi_install_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, device_type, platform, package_name) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.etl_iqiyi_install_daily_all ON CLUSTER cluster_1st (`dt` Date, `device_id` String, `device_type` String, `platform` String, `package_name` String) ENGINE = Distributed(cluster_1st, dwh, etl_iqiyi_install_daily, rand());

CREATE TABLE dwh.audience_merge ON CLUSTER cluster_1st(dt Date,hour FixedString(2),devid String,audience_id Array(Int32)) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/audience_merge', '{replica}') PARTITION BY (toYYYYMMDD(dt),hour) ORDER BY (dt, hour, devid) SETTINGS index_granularity = 8192;
CREATE TABLE dwh.audience_merge_all ON CLUSTER cluster_1st(dt Date, hour FixedString(2),devid String,audience_id Array(Int32)) ENGINE = Distributed(cluster_1st, dwh, audience_merge, rand());

CREATE TABLE dwh.audience_merge (dt Date,hour FixedString(2),devid String,audience_id Array(Int32)) ENGINE = MergeTree() PARTITION BY (toYYYYMMDD(dt),hour) ORDER BY (dt, hour, devid) SETTINGS index_granularity = 8192;

CREATE TABLE dmp.uc_lahuo_daily ON CLUSTER cluster_1st(dt Date,device_type String,device_ids String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/uc_lahuo_daily', '{replica}') PARTITION BY (toYYYYMMDD(dt),device_type) ORDER BY (dt,device_type,device_ids) TTL dt + toIntervalWeek(1) SETTINGS index_granularity = 8192;
CREATE TABLE dmp.uc_lahuo_daily_all ON CLUSTER cluster_1st(dt Date,device_type String,device_ids String) ENGINE = Distributed(cluster_1st, dmp, uc_lahuo_daily, rand());

CREATE TABLE dmp.uc_lahuo_result_daily ON CLUSTER cluster_1st(dt Date, hour FixedString(2), device_type String,device_ids String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/uc_lahuo_result_daily', '{replica}') PARTITION BY (toYYYYMMDD(dt), hour,device_type) ORDER BY (dt, hour,device_type,device_ids) TTL dt + toIntervalWeek(1) SETTINGS index_granularity = 8192;
CREATE TABLE dmp.uc_lahuo_result_daily_all ON CLUSTER cluster_1st(dt Date, hour FixedString(2), device_type String,device_ids String) ENGINE = Distributed(cluster_1st, dmp, uc_lahuo_result_daily, rand());

CREATE TABLE dmp.alipay_lahuo_daily ON CLUSTER cluster_1st(dt Date,hour FixedString(2),device_type String,device_ids String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/alipay_lahuo_daily', '{replica}') PARTITION BY (toYYYYMMDD(dt), hour,device_type) ORDER BY (dt, hour,device_type,device_ids) TTL dt + toIntervalWeek(1) SETTINGS index_granularity = 8192;
CREATE TABLE dmp.alipay_lahuo_daily_all ON CLUSTER cluster_1st(dt Date,hour FixedString(2),device_type String,device_ids String) ENGINE = Distributed(cluster_1st, dmp, alipay_lahuo_daily, rand());

CREATE TABLE dmp.alipay_lahuo_result_daily ON CLUSTER cluster_1st(dt Date, hour FixedString(2), device_type String,device_ids String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/alipay_lahuo_result_daily', '{replica}') PARTITION BY (toYYYYMMDD(dt), hour,device_type) ORDER BY (dt, hour,device_type,device_ids) TTL dt + toIntervalWeek(1) SETTINGS index_granularity = 8192;
CREATE TABLE dmp.alipay_lahuo_result_daily_all ON CLUSTER cluster_1st(dt Date, hour FixedString(2), device_type String,device_ids String) ENGINE = Distributed(cluster_1st, dmp, alipay_lahuo_result_daily, rand());

CREATE TABLE dmp.youku_laxin_daily ON CLUSTER cluster_1st(dt Date,device_type String,device_ids String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/youku_laxin_daily', '{replica}') PARTITION BY (toYYYYMMDD(dt),device_type) ORDER BY (dt,device_type,device_ids) SETTINGS index_granularity = 8192;
CREATE TABLE dmp.youku_laxin_daily_all ON CLUSTER cluster_1st(dt Date,device_type String,device_ids String) ENGINE = Distributed(cluster_1st, dmp, youku_laxin_daily, rand());

CREATE TABLE dmp.youku_laxin_result_daily ON CLUSTER cluster_1st(dt Date, hour FixedString(2), device_type String,device_ids String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/youku_laxin_result_daily', '{replica}') PARTITION BY (toYYYYMMDD(dt), hour,device_type) ORDER BY (dt, hour,device_type,device_ids) SETTINGS index_granularity = 8192;
CREATE TABLE dmp.youku_laxin_result_daily_all ON CLUSTER cluster_1st(dt Date, hour FixedString(2), device_type String,device_ids String) ENGINE = Distributed(cluster_1st, dmp, youku_laxin_result_daily, rand());