clickhouse.sql 40.6 KB
Newer Older
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
--  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());

WangJinfeng committed
127 128 129 130 131 132
DROP TABLE dwh.audience_merge ON CLUSTER cluster_1st;

CREATE TABLE dwh.audience_merge_v1 (`dt` Date, `hour` FixedString(2), `devid` String, `audience_id` Array(Int32), `device_type` String DEFAULT '') ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/audience_merge_v1', '{replica}') PARTITION BY (toYYYYMMDD(dt), hour) ORDER BY (dt, hour, devid) TTL dt + toIntervalDay(2) SETTINGS index_granularity = 8192,use_minimalistic_part_header_in_zookeeper = 1;

CREATE TABLE dwh.audience_merge_v1_all (`dt` Date,`hour` FixedString(2),`devid` String,`audience_id` Array(Int32),`device_type` String) ENGINE = Distributed('cluster_1st', 'dwh', 'audience_merge_v1', rand());

WangJinfeng committed
133 134 135 136 137 138 139 140 141 142
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) TTL dt + toIntervalWeek(1) SETTINGS index_granularity = 8192,use_minimalistic_part_header_in_zookeeper = 1;

DROP TABLE dwh.etl_iqiyi_daily ON CLUSTER cluster_1st;

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) TTL dt + toIntervalWeek(1) SETTINGS index_granularity = 8192,use_minimalistic_part_header_in_zookeeper = 1;

DROP TABLE dmp.youku_laxin_daily ON CLUSTER cluster_1st;

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) TTL dt + toIntervalWeek(1) SETTINGS index_granularity = 8192,use_minimalistic_part_header_in_zookeeper = 1;

wang-jinfeng committed
143 144
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;

WangJinfeng committed
145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174
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, use_minimalistic_part_header_in_zookeeper = 1;

CREATE TABLE dataplatform_job_meta.yarn_application_metric ON CLUSTER cluster_1st (`application_time` UInt64,`master_name` String,`master_address` String,`user_name` String,`queue_name` String,`application_id` UInt64,`application_name` String,`application_type` String,`application_status` String,`application_core` Int32,`application_memory` Float32,`application_start_time` String,`application_stop_time` String,`application_elapsed_time` String,`application_read_byte` String,`application_write_byte` String,`application_input_path` String,`application_output_path` String,`email` String,`direct_leader` String,`department` String) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/12c9fb56-db0d-4bc6-9aea-0b722551f983/{shard}', '{replica}', application_id) PARTITION BY toYYYYMMDD(toDate(application_time / 1000)) ORDER BY (application_time, master_name, queue_name, user_name, application_id) SETTINGS index_granularity = 8192, use_minimalistic_part_header_in_zookeeper = 1;

CREATE TABLE dataplatform_job_meta.yarn_application_msg ON CLUSTER cluster_1st (`application_time` UInt64,`master_name` String,`master_address` String,`user_name` String,`queue_name` String,`application_id` String,`application_name` String,`application_type` String,`application_status` String,`application_core` UInt64,`application_memory` Float32,`application_start_time` UInt64,`application_stop_time` UInt64,`application_elapsed_time` UInt64,`application_read_byte` UInt64,`application_write_byte` UInt64,`application_input_path` String,`application_output_path` String,`email` String,`direct_leader` String,`department` String) ENGINE = MySQL('ambari-cluster.c5yzcdreb1xr.us-east-1.rds.amazonaws.com:3306', 'dataplatform_job_meta', 'yarn_application_msg', 'dataplatform', 'CNV6tQBb2Mb64Hv');

DROP TABLE dwh.joypac_daily ON CLUSTER cluster_1st;

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, use_minimalistic_part_header_in_zookeeper = 1;

DROP TABLE dwh.sss_tracking_daily ON CLUSTER cluster_1st;

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, use_minimalistic_part_header_in_zookeeper = 1;

DROP TABLE dwh.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 = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/sss_tracking_insight_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, log_type) TTL dt + toIntervalYear(1) SETTINGS index_granularity = 8192, use_minimalistic_part_header_in_zookeeper = 1;

DROP TABLE dwh.adn_tracking_daily ON CLUSTER cluster_1st;

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) TTL dt + toIntervalWeek(1) SETTINGS index_granularity = 8192, use_minimalistic_part_header_in_zookeeper = 1;

DROP TABLE dwh.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 = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/adn_tracking_insight_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, device_id, event_type, log_type) TTL dt + toIntervalYear(1) SETTINGS index_granularity = 8192, use_minimalistic_part_header_in_zookeeper = 1;

DROP TABLE dwh.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 = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/joypac_insight_daily', '{replica}') PARTITION BY toYYYYMMDD(dt) ORDER BY (dt, country, age, gender) SETTINGS index_granularity = 8192;

wang-jinfeng committed
175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191
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());