create database scrm default character set utf8mb4 collate utf8mb4_unicode_ci;

mysql -h 192.168.52.150 -P 3306 -uroot -p

source G:\知行教育大數據平台\講義\完整原始數據\scrm.sql

CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_relationship_id` int(11) DEFAULT NULL COMMENT '當前意向id',
  `create_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被刪除（禁用）',
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '姓名',
  `idcard` varchar(24) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT '身份證號',
  `birth_year` int(5) DEFAULT NULL COMMENT '出生年份',
  `gender` varchar(8) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'MAN' COMMENT '性別',
  `phone` varchar(24) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '手機號',
  `wechat` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT '微信',
  `qq` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT 'qq號',
  `email` varchar(56) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT '郵箱',
  `area` varchar(128) DEFAULT '' COMMENT '所在區域',
  `leave_school_date` date DEFAULT NULL COMMENT '離校時間',
  `graduation_date` date DEFAULT NULL COMMENT '畢業時間',
  `bxg_student_id` varchar(64) DEFAULT NULL COMMENT '博學谷學員ID，可能未關聯到，不存在',
  `creator` int(11) DEFAULT NULL COMMENT '創建人ID',
  `origin_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '數據來源',
  `origin_channel` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '來源渠道',
  `tenant` int(11) NOT NULL DEFAULT '0',
  `md_id` int(11) DEFAULT '0' COMMENT '中台id',
  PRIMARY KEY (`id`),
  KEY `employee_id` (`creator`) USING BTREE,
  KEY `customer_relationship_id` (`customer_relationship_id`) USING BTREE,
  KEY `index_idcard` (`idcard`) USING BTREE,
  KEY `index_phone` (`phone`) USING BTREE,
  KEY `index_create_time` (`create_date_time`) USING BTREE,
  KEY `index_qq` (`qq`) USING BTREE,
  KEY `idx_update_time` (`update_date_time`) USING BTREE,
  CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`creator`) REFERENCES `employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2061222 DEFAULT CHARSET=utf8;

CREATE TABLE `customer_relationship` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被刪除（禁用）',
  `customer_id` int(11) NOT NULL DEFAULT '0' COMMENT '所屬客户id',
  `first_id` int(11) DEFAULT NULL COMMENT '第一條客户關係id',
  `belonger` int(11) DEFAULT NULL COMMENT '歸屬人',
  `belonger_name` varchar(10) DEFAULT NULL COMMENT '歸屬人姓名',
  `initial_belonger` int(11) DEFAULT NULL COMMENT '初始歸屬人',
  `distribution_handler` int(11) DEFAULT NULL COMMENT '分配處理人',
  `business_scrm_department_id` int(11) DEFAULT '0' COMMENT '歸屬部門',
  `last_visit_time` datetime DEFAULT NULL COMMENT '最後回訪時間',
  `next_visit_time` datetime DEFAULT NULL COMMENT '下次回訪時間',
  `origin_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '數據來源',
  `itcast_school_id` int(11) DEFAULT NULL COMMENT '校區Id',
  `itcast_subject_id` int(11) DEFAULT NULL COMMENT '學科Id',
  `intention_study_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '意向學習方式',
  `anticipat_signup_date` date DEFAULT NULL COMMENT '預計報名時間',
  `level` varchar(8) DEFAULT NULL COMMENT '客户級別',
  `creator` int(11) DEFAULT NULL COMMENT '創建人',
  `current_creator` int(11) DEFAULT NULL COMMENT '當前創建人：初始==創建人，當在公海拉回時為 拉回人',
  `creator_name` varchar(32) DEFAULT '' COMMENT '創建者姓名',
  `origin_channel` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '來源渠道',
  `comment` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT '備註',
  `first_customer_clue_id` int(11) DEFAULT '0' COMMENT '第一條線索id',
  `last_customer_clue_id` int(11) DEFAULT '0' COMMENT '最後一條線索id',
  `process_state` varchar(32) DEFAULT NULL COMMENT '處理狀態',
  `process_time` datetime DEFAULT NULL COMMENT '處理狀態變動時間',
  `payment_state` varchar(32) DEFAULT NULL COMMENT '支付狀態',
  `payment_time` datetime DEFAULT NULL COMMENT '支付狀態變動時間',
  `signup_state` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '報名狀態',
  `signup_time` datetime DEFAULT NULL COMMENT '報名時間',
  `notice_state` varchar(32) DEFAULT NULL COMMENT '通知狀態',
  `notice_time` datetime DEFAULT NULL COMMENT '通知狀態變動時間',
  `lock_state` bit(1) DEFAULT b'0' COMMENT '鎖定狀態',
  `lock_time` datetime DEFAULT NULL COMMENT '鎖定狀態修改時間',
  `itcast_clazz_id` int(11) DEFAULT NULL COMMENT '所屬ems班級id',
  `itcast_clazz_time` datetime DEFAULT NULL COMMENT '報班時間',
  `payment_url` varchar(1024) DEFAULT '' COMMENT '付款鏈接',
  `payment_url_time` datetime DEFAULT NULL COMMENT '支付鏈接生成時間',
  `ems_student_id` int(11) DEFAULT NULL COMMENT 'ems的學生id',
  `delete_reason` varchar(64) DEFAULT NULL COMMENT '刪除原因',
  `deleter` int(11) DEFAULT NULL COMMENT '刪除人',
  `deleter_name` varchar(32) DEFAULT NULL COMMENT '刪除人姓名',
  `delete_time` datetime DEFAULT NULL COMMENT '刪除時間',
  `course_id` int(11) DEFAULT NULL COMMENT '課程ID',
  `course_name` varchar(64) DEFAULT NULL COMMENT '課程名稱',
  `delete_comment` varchar(255) DEFAULT '' COMMENT '刪除原因説明',
  `close_state` varchar(32) DEFAULT NULL COMMENT '關閉裝填',
  `close_time` datetime DEFAULT NULL COMMENT '關閉狀態變動時間',
  `appeal_id` int(11) DEFAULT NULL COMMENT '申訴id',
  `tenant` int(11) NOT NULL DEFAULT '0' COMMENT '租户',
  `total_fee` decimal(19,0) DEFAULT NULL COMMENT '報名費總金額',
  `belonged` int(11) DEFAULT NULL COMMENT '小週期歸屬人',
  `belonged_time` datetime DEFAULT NULL COMMENT '歸屬時間',
  `belonger_time` datetime DEFAULT NULL COMMENT '歸屬時間',
  `transfer` int(11) DEFAULT NULL COMMENT '轉移人',
  `transfer_time` datetime DEFAULT NULL COMMENT '轉移時間',
  `follow_type` int(4) DEFAULT '0' COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  `transfer_bxg_oa_account` varchar(64) DEFAULT NULL COMMENT '轉移到博學谷歸屬人OA賬號',
  `transfer_bxg_belonger_name` varchar(64) DEFAULT NULL COMMENT '轉移到博學谷歸屬人OA姓名',
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`) USING BTREE,
  KEY `appeal_id` (`appeal_id`) USING BTREE,
  KEY `create_date_time` (`create_date_time`) USING BTREE,
  KEY `next_visit_time` (`next_visit_time`) USING BTREE,
  KEY `last_visit_time` (`last_visit_time`) USING BTREE,
  KEY `itcast_school_id` (`itcast_school_id`) USING BTREE,
  KEY `index_delete` (`delete_time`) USING BTREE,
  KEY `index_class_id` (`itcast_clazz_id`) USING BTREE,
  KEY `belonger` (`belonger`) USING BTREE,
  KEY `creator` (`creator`) USING BTREE,
  KEY `index_itcast_subject_id` (`itcast_subject_id`) USING BTREE,
  KEY `idex_distribution` (`distribution_handler`) USING BTREE,
  CONSTRAINT `customer_relationship_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2060127 DEFAULT CHARSET=utf8;

CREATE TABLE `customer_clue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被刪除（禁用）',
  `customer_id` int(11) DEFAULT NULL COMMENT '客户id',
  `customer_relationship_id` int(11) DEFAULT NULL COMMENT '客户關係id',
  `session_id` varchar(48) COLLATE utf8_bin DEFAULT '' COMMENT '七陌會話id',
  `sid` varchar(48) COLLATE utf8_bin DEFAULT '' COMMENT '訪客id',
  `status` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '狀態（undeal待領取 deal 已領取 finish 已關閉 changePeer 已流轉）',
  `user` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '所屬坐席',
  `create_time` datetime DEFAULT NULL COMMENT '七陌創建時間',
  `platform` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '平台來源 （pc-網站諮詢|wap-wap諮詢|sdk-app諮詢|weixin-微信諮詢）',
  `s_name` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '用户名稱',
  `seo_source` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '搜索來源',
  `seo_keywords` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '關鍵字',
  `ip` varchar(48) COLLATE utf8_bin DEFAULT '' COMMENT 'IP地址',
  `referrer` text COLLATE utf8_bin COMMENT '上級來源頁面',
  `from_url` text COLLATE utf8_bin COMMENT '會話來源頁面',
  `landing_page_url` text COLLATE utf8_bin COMMENT '訪客着陸頁面',
  `url_title` varchar(1024) COLLATE utf8_bin DEFAULT '' COMMENT '諮詢頁面title',
  `to_peer` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '所屬技能組',
  `manual_time` datetime DEFAULT NULL COMMENT '人工開始時間',
  `begin_time` datetime DEFAULT NULL COMMENT '坐席領取時間 ',
  `reply_msg_count` int(11) DEFAULT '0' COMMENT '客服回覆消息數',
  `total_msg_count` int(11) DEFAULT '0' COMMENT '消息總數',
  `msg_count` int(11) DEFAULT '0' COMMENT '客户發送消息數',
  `comment` varchar(1024) COLLATE utf8_bin DEFAULT '' COMMENT '備註',
  `finish_reason` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '結束類型',
  `finish_user` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '結束坐席',
  `end_time` datetime DEFAULT NULL COMMENT '會話結束時間',
  `platform_description` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '客户平台信息',
  `browser_name` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '瀏覽器名稱',
  `os_info` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '系統名稱',
  `area` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '區域',
  `country` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '所在國家',
  `province` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '省',
  `city` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '城市',
  `creator` int(11) DEFAULT '0' COMMENT '創建人',
  `name` varchar(64) COLLATE utf8_bin DEFAULT '' COMMENT '客户姓名',
  `idcard` varchar(24) COLLATE utf8_bin DEFAULT '' COMMENT '身份證號',
  `phone` varchar(24) COLLATE utf8_bin DEFAULT '' COMMENT '手機號',
  `itcast_school_id` int(11) DEFAULT NULL COMMENT '校區Id',
  `itcast_school` varchar(128) COLLATE utf8_bin DEFAULT '' COMMENT '校區',
  `itcast_subject_id` int(11) DEFAULT NULL COMMENT '學科Id',
  `itcast_subject` varchar(128) COLLATE utf8_bin DEFAULT '' COMMENT '學科',
  `wechat` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '微信',
  `qq` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT 'qq號',
  `email` varchar(56) COLLATE utf8_bin DEFAULT '' COMMENT '郵箱',
  `gender` varchar(8) COLLATE utf8_bin DEFAULT 'MAN' COMMENT '性別',
  `level` varchar(8) COLLATE utf8_bin DEFAULT NULL COMMENT '客户級別',
  `origin_type` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '數據來源渠道',
  `information_way` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '資訊方式',
  `working_years` date DEFAULT NULL COMMENT '開始工作時間',
  `technical_directions` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '技術方向',
  `customer_state` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '當前客户狀態',
  `valid` bit(1) DEFAULT b'0' COMMENT '該線索是否是網資有效線索',
  `anticipat_signup_date` date DEFAULT NULL COMMENT '預計報名時間',
  `clue_state` varchar(32) COLLATE utf8_bin DEFAULT 'NOT_SUBMIT' COMMENT '線索狀態',
  `scrm_department_id` int(11) DEFAULT NULL COMMENT 'SCRM內部部門id',
  `superior_url` text COLLATE utf8_bin COMMENT '諸葛獲取上級頁面URL',
  `superior_source` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '諸葛獲取上級頁面URL標題',
  `landing_url` text COLLATE utf8_bin COMMENT '諸葛獲取着陸頁面URL',
  `landing_source` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '諸葛獲取着陸頁面URL來源',
  `info_url` text COLLATE utf8_bin COMMENT '諸葛獲取留諮頁URL',
  `info_source` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '諸葛獲取留諮頁URL標題',
  `origin_channel` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '投放渠道',
  `course_id` int(32) DEFAULT NULL,
  `course_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `zhuge_session_id` varchar(500) COLLATE utf8_bin DEFAULT NULL,
  `is_repeat` int(4) NOT NULL DEFAULT '0' COMMENT '是否重複線索(手機號維度) 0:正常 1：重複',
  `tenant` int(11) NOT NULL DEFAULT '0' COMMENT '租户id',
  `activity_id` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '活動id',
  `activity_name` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '活動名稱',
  `follow_type` int(4) DEFAULT '0' COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  `shunt_mode_id` int(11) DEFAULT NULL COMMENT '匹配到的技能組id',
  `shunt_employee_group_id` int(11) DEFAULT NULL COMMENT '所屬分流員工組',
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`) USING BTREE,
  KEY `customer_relationship_id` (`customer_relationship_id`) USING BTREE,
  KEY `phone` (`phone`) USING BTREE,
  KEY `idcard` (`idcard`) USING BTREE,
  KEY `session_id` (`session_id`) USING BTREE,
  KEY `index_date_time` (`create_date_time`) USING BTREE,
  KEY `index_creator` (`creator`) USING BTREE,
  CONSTRAINT `customer_clue_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`),
  CONSTRAINT `customer_clue_ibfk_2` FOREIGN KEY (`customer_relationship_id`) REFERENCES `customer_relationship` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2060711 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

create table employee
(
    id                  int auto_increment
        primary key,
    email               varchar(64)                           not null comment '公司郵箱，OA登錄賬號',
    real_name           varchar(32)                           not null comment '員工的真實姓名',
    phone               varchar(32)                           not null comment '手機號，目前還沒有使用；隱私問題OA接口沒有提供這個屬性，',
    department_id       varchar(64) default '0'               null comment 'OA中的部門編號，有負值',
    department_name     varchar(64) default ''                null comment 'OA中的部門名',
    remote_login        bit                                   not null comment '員工是否可以遠程登錄',
    job_number          varchar(64)                           null comment '員工工號',
    cross_school        bit                                   not null comment '是否有跨校區權限',
    last_login_date     datetime                              not null comment '最後登錄日期',
    creator             int(32)                               null comment '創建人',
    create_date_time    datetime    default CURRENT_TIMESTAMP not null comment '創建時間',
    update_date_time    timestamp   default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '最後更新時間',
    deleted             bit         default b'0'              not null comment '是否被刪除（禁用）',
    scrm_department_id  int(32)                               null comment 'SCRM內部部門id',
    leave_office        bit                                   null comment '離職狀態',
    leave_office_time   datetime                              null comment '離職時間',
    reinstated_time     datetime                              null comment '復職時間',
    superior_leaders_id int                                   null comment '上級領導ID',
    tdepart_id          int                                   null comment '直屬部門',
    tenant              int         default 0                 not null,
    ems_user_name       varchar(32)                           null
)
    comment '員工信息表';

CREATE TABLE `scrm_department` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部門id',
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '部門名稱',
  `parent_id` int(11) DEFAULT NULL COMMENT '父部門id',
  `create_date_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_date_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `deleted` bit(1) DEFAULT b'0' COMMENT '刪除標誌',
  `id_path` varchar(1000) COLLATE utf8_bin DEFAULT NULL COMMENT '編碼全路徑',
  `tdepart_code` int(11) DEFAULT NULL COMMENT '直屬部門',
  `creator` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '創建者',
  `depart_level` int(4) DEFAULT NULL COMMENT '部門層級',
  `depart_sign` int(4) DEFAULT NULL COMMENT '部門標誌，暫時默認1',
  `depart_line` int(11) DEFAULT NULL COMMENT '業務線，存儲業務線編碼',
  `depart_sort` int(5) DEFAULT NULL COMMENT '排序字段',
  `disable_flag` int(1) DEFAULT NULL COMMENT '禁用標誌',
  `tenant` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `itcast_school` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被刪除（禁用）',
  `name` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '校區名稱',
  `code` varchar(32) COLLATE utf8_bin NOT NULL,
  `tenant` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `itcast_subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_date_time` datetime NOT NULL COMMENT '創建時間',
  `update_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被刪除（禁用）',
  `name` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '學科名稱',
  `code` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `tenant` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)
   ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

create table test_buck(id int, name string)

clustered by(id) sorted by (id asc) into 6 buckets

row format delimited fields terminated by '\t';

desc formatted test_buck;

--啓用桶表

set hive.enforce.bucketing=true;

insert into table test_buck select id, name from temp_buck;

--限制對桶表進行load操作

set hive.strict.checks.bucketing = true;

--創建臨時表

create table temp_buck(id int, name string)

row format delimited fields terminated by '\t';

--導入數據

load data local inpath '/tools/test_buck.txt' into table temp_buck;

--啓用桶表

set hive.enforce.bucketing=true;

--限制對桶表進行load操作

set hive.strict.checks.bucketing = true;

--insert select

insert into table test_buck select id, name from temp_buck;

--分桶成功

select * from table tablesample(bucket x out of y on column)

select * from test_buck tablesample(bucket 1 out of 10 on id);

set hive.auto.convert.join=true;

--舊版本為hive.mapjoin.smalltable.filesize

set hive.auto.convert.join.noconditionaltask.size=512000000

select f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802)  

select /*+ mapjoin(A)*/ f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802) 

select A.a ,A.b from A join B where A.a>B.a

bucket mapjoin

SMB join

set hive.optimize.bucketmapjoin = true;

set hive.optimize.bucketmapjoin = true;

set hive.auto.convert.sortmerge.join=true;

set hive.optimize.bucketmapjoin.sortedmerge = true;

set hive.auto.convert.sortmerge.join.noconditionaltask=true;

一個表的bucket數是另一個表bucket數的整數倍

小表的bucket數=大表bucket數

bucket列 == join列

Bucket 列 == Join 列 == sort 列

必須是應用在map join的場景中

必須是應用在bucket mapjoin 的場景中

create table test_smb_2(mid string,age_id string)

CLUSTERED BY(mid) SORTED BY(mid) INTO 500 BUCKETS;

--寫入數據強制分桶

set hive.enforce.bucketing=true;

--寫入數據強制排序

set hive.enforce.sorting=true;

--開啓bucketmapjoin

set hive.optimize.bucketmapjoin = true;

--開啓SMB Join

set hive.auto.convert.sortmerge.join=true;

set hive.auto.convert.sortmerge.join.noconditionaltask=true;

set hive.exec.orc.compression.strategy=COMPRESSION;

DROP TABLE itcast_ods.`customer_relationship`;
CREATE TABLE IF NOT EXISTS itcast_ods.`customer_relationship` (
  `id` int COMMENT '客户關係id',
  `create_date_time` STRING COMMENT '創建時間',
  `update_date_time` STRING COMMENT '最後更新時間',
  `deleted` int COMMENT '是否被刪除（禁用）',
  `customer_id` int COMMENT '所屬客户id',
  `first_id` int COMMENT '第一條客户關係id',
  `belonger` int COMMENT '歸屬人',
  `belonger_name` STRING COMMENT '歸屬人姓名',
  `initial_belonger` int COMMENT '初始歸屬人',
  `distribution_handler` int COMMENT '分配處理人',
  `business_scrm_department_id` int COMMENT '歸屬部門',
  `last_visit_time` STRING COMMENT '最後回訪時間',
  `next_visit_time` STRING COMMENT '下次回訪時間',
  `origin_type` STRING COMMENT '數據來源',
  `itcast_school_id` int COMMENT '校區Id',
  `itcast_subject_id` int COMMENT '學科Id',
  `intention_study_type` STRING COMMENT '意向學習方式',
  `anticipat_signup_date` STRING COMMENT '預計報名時間',
  `level` STRING COMMENT '客户級別',
  `creator` int COMMENT '創建人',
  `current_creator` int COMMENT '當前創建人：初始==創建人，當在公海拉回時為 拉回人',
  `creator_name` STRING COMMENT '創建者姓名',
  `origin_channel` STRING COMMENT '來源渠道',
  `comment` STRING COMMENT '備註',
  `first_customer_clue_id` int COMMENT '第一條線索id',
  `last_customer_clue_id` int COMMENT '最後一條線索id',
  `process_state` STRING COMMENT '處理狀態',
  `process_time` STRING COMMENT '處理狀態變動時間',
  `payment_state` STRING COMMENT '支付狀態',
  `payment_time` STRING COMMENT '支付狀態變動時間',
  `signup_state` STRING COMMENT '報名狀態',
  `signup_time` STRING COMMENT '報名時間',
  `notice_state` STRING COMMENT '通知狀態',
  `notice_time` STRING COMMENT '通知狀態變動時間',
  `lock_state` STRING COMMENT '鎖定狀態',
  `lock_time` STRING COMMENT '鎖定狀態修改時間',
  `itcast_clazz_id` int COMMENT '所屬ems班級id',
  `itcast_clazz_time` STRING COMMENT '報班時間',
  `payment_url` STRING COMMENT '付款鏈接',
  `payment_url_time` STRING COMMENT '支付鏈接生成時間',
  `ems_student_id` int COMMENT 'ems的學生id',
  `delete_reason` STRING COMMENT '刪除原因',
  `deleter` int COMMENT '刪除人',
  `deleter_name` STRING COMMENT '刪除人姓名',
  `delete_time` STRING COMMENT '刪除時間',
  `course_id` int COMMENT '課程ID',
  `course_name` STRING COMMENT '課程名稱',
  `delete_comment` STRING COMMENT '刪除原因説明',
  `close_state` STRING COMMENT '關閉裝填',
  `close_time` STRING COMMENT '關閉狀態變動時間',
  `appeal_id` int COMMENT '申訴id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '報名費總金額',
  `belonged` int COMMENT '小週期歸屬人',
  `belonged_time` STRING COMMENT '歸屬時間',
  `belonger_time` STRING COMMENT '歸屬時間',
  `transfer` int COMMENT '轉移人',
  `transfer_time` STRING COMMENT '轉移時間',
  `follow_type` int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  `transfer_bxg_oa_account` STRING COMMENT '轉移到博學谷歸屬人OA賬號',
  `transfer_bxg_belonger_name` STRING COMMENT '轉移到博學谷歸屬人OA姓名',
  `end_time` STRING COMMENT '有效截止時間')
comment '客户關係表'
PARTITIONED BY(start_time STRING)
clustered by(id) sorted by(id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

DROP TABLE itcast_ods.customer_clue;
CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '創建時間',
  update_date_time STRING COMMENT '最後更新時間',
  deleted STRING COMMENT '是否被刪除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户關係id',
  session_id STRING COMMENT '七陌會話id',
  sid STRING COMMENT '訪客id',
  status STRING COMMENT '狀態（undeal待領取 deal 已領取 finish 已關閉 changePeer 已流轉）',
  users STRING COMMENT '所屬坐席',
  create_time STRING COMMENT '七陌創建時間',
  platform STRING COMMENT '平台來源 （pc-網站諮詢|wap-wap諮詢|sdk-app諮詢|weixin-微信諮詢）',
  s_name STRING COMMENT '用户名稱',
  seo_source STRING COMMENT '搜索來源',
  seo_keywords STRING COMMENT '關鍵字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上級來源頁面',
  from_url STRING COMMENT '會話來源頁面',
  landing_page_url STRING COMMENT '訪客着陸頁面',
  url_title STRING COMMENT '諮詢頁面title',
  to_peer STRING COMMENT '所屬技能組',
  manual_time STRING COMMENT '人工開始時間',
  begin_time STRING COMMENT '坐席領取時間 ',
  reply_msg_count int COMMENT '客服回覆消息數',
  total_msg_count int COMMENT '消息總數',
  msg_count int COMMENT '客户發送消息數',
  comment STRING COMMENT '備註',
  finish_reason STRING COMMENT '結束類型',
  finish_user STRING COMMENT '結束坐席',
  end_time STRING COMMENT '會話結束時間',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '瀏覽器名稱',
  os_info STRING COMMENT '系統名稱',
  area STRING COMMENT '區域',
  country STRING COMMENT '所在國家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '創建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份證號',
  phone STRING COMMENT '手機號',
  itcast_school_id int COMMENT '校區Id',
  itcast_school STRING COMMENT '校區',
  itcast_subject_id int COMMENT '學科Id',
  itcast_subject STRING COMMENT '學科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq號',
  email STRING COMMENT '郵箱',
  gender STRING COMMENT '性別',
  level STRING COMMENT '客户級別',
  origin_type STRING COMMENT '數據來源渠道',
  information_way STRING COMMENT '資訊方式',
  working_years STRING COMMENT '開始工作時間',
  technical_directions STRING COMMENT '技術方向',
  customer_state STRING COMMENT '當前客户狀態',
  valid STRING COMMENT '該線索是否是網資有效線索',
  anticipat_signup_date STRING COMMENT '預計報名時間',
  clue_state STRING COMMENT '線索狀態',
  scrm_department_id int COMMENT 'SCRM內部部門id',
  superior_url STRING COMMENT '諸葛獲取上級頁面URL',
  superior_source STRING COMMENT '諸葛獲取上級頁面URL標題',
  landing_url STRING COMMENT '諸葛獲取着陸頁面URL',
  landing_source STRING COMMENT '諸葛獲取着陸頁面URL來源',
  info_url STRING COMMENT '諸葛獲取留諮頁URL',
  info_source STRING COMMENT '諸葛獲取留諮頁URL標題',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '課程編號',
  course_name STRING COMMENT '課程名稱',
  zhuge_session_id STRING COMMENT 'zhuge會話id',
  is_repeat int COMMENT '是否重複線索(手機號維度) 0:正常 1：重複',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活動id',
  activity_name STRING COMMENT '活動名稱',
  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  shunt_mode_id int COMMENT '匹配到的技能組id',
  shunt_employee_group_id int COMMENT '所屬分流員工組',
  ends_time STRING COMMENT '有效時間')
comment '客户關係表'
PARTITIONED BY(starts_time STRING)
clustered by(customer_relationship_id) sorted by(customer_relationship_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

CREATE DATABASE IF NOT EXISTS itcast_dimen;

CREATE TABLE IF NOT EXISTS itcast_dimen.`customer` (
  `id` int COMMENT 'key id',
  `customer_relationship_id` int COMMENT '當前意向id',
  `create_date_time` STRING COMMENT '創建時間',
  `update_date_time` STRING COMMENT '最後更新時間',
  `deleted` int  COMMENT '是否被刪除（禁用）',
  `name` STRING COMMENT '姓名',
  `idcard` STRING  COMMENT '身份證號',
  `birth_year` int COMMENT '出生年份',
  `gender` STRING COMMENT '性別',
  `phone` STRING COMMENT '手機號',
  `wechat` STRING COMMENT '微信',
  `qq` STRING COMMENT 'qq號',
  `email` STRING COMMENT '郵箱',
  `area` STRING COMMENT '所在區域',
  `leave_school_date` date COMMENT '離校時間',
  `graduation_date` date COMMENT '畢業時間',
  `bxg_student_id` STRING COMMENT '博學谷學員ID，可能未關聯到，不存在',
  `creator` int COMMENT '創建人ID',
  `origin_type` STRING COMMENT '數據來源',
  `origin_channel` STRING COMMENT '來源渠道',
  `tenant` int,
  `md_id` int COMMENT '中台id')
comment '客户表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

CREATE TABLE IF NOT EXISTS itcast_dimen.employee (
  id int COMMENT '員工id',
  email STRING COMMENT '公司郵箱，OA登錄賬號',
  real_name STRING COMMENT '員工的真實姓名',
  phone STRING COMMENT '手機號，目前還沒有使用；隱私問題OA接口沒有提供這個屬性，',
  department_id STRING COMMENT 'OA中的部門編號，有負值',
  department_name STRING COMMENT 'OA中的部門名',
  remote_login STRING COMMENT '員工是否可以遠程登錄',
  job_number STRING COMMENT '員工工號',
  cross_school STRING COMMENT '是否有跨校區權限',
  last_login_date STRING COMMENT '最後登錄日期',
  creator int COMMENT '創建人',
  create_date_time STRING COMMENT '創建時間',
  update_date_time STRING COMMENT '最後更新時間',
  deleted STRING COMMENT '是否被刪除（禁用）',
  scrm_department_id int COMMENT 'SCRM內部部門id',
  leave_office STRING COMMENT '離職狀態',
  leave_office_time STRING COMMENT '離職時間',
  reinstated_time STRING COMMENT '復職時間',
  superior_leaders_id int COMMENT '上級領導ID',
  tdepart_id int COMMENT '直屬部門',
  tenant int COMMENT '租户',
  ems_user_name STRING COMMENT 'ems用户名稱'
)
comment '員工表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

CREATE TABLE IF NOT EXISTS itcast_dimen.`scrm_department` (
  `id` int COMMENT '部門id',
  `name` STRING COMMENT '部門名稱',
  `parent_id` int COMMENT '父部門id',
  `create_date_time` STRING COMMENT '創建時間',
  `update_date_time` STRING COMMENT '更新時間',
  `deleted` STRING COMMENT '刪除標誌',
  `id_path` STRING COMMENT '編碼全路徑',
  `tdepart_code` int COMMENT '直屬部門',
  `creator` STRING COMMENT '創建者',
  `depart_level` int COMMENT '部門層級',
  `depart_sign` int COMMENT '部門標誌，暫時默認1',
  `depart_line` int COMMENT '業務線，存儲業務線編碼',
  `depart_sort` int COMMENT '排序字段',
  `disable_flag` int COMMENT '禁用標誌',
  `tenant` int COMMENT '租户')
comment 'scrm部門表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

CREATE TABLE IF NOT EXISTS itcast_dimen.`itcast_school` (
  `id` int COMMENT '自增主鍵',
  `create_date_time` timestamp COMMENT '創建時間',
  `update_date_time` timestamp  COMMENT '最後更新時間',
  `deleted` STRING COMMENT '是否被刪除（禁用）',
  `name` STRING COMMENT '校區名稱',
  `code` STRING COMMENT '校區標識',
  `tenant` int COMMENT '租户')
comment '校區字典表'

PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

CREATE TABLE IF NOT EXISTS itcast_dimen.`itcast_subject` (
  `id` int COMMENT '自增主鍵',
  `create_date_time` timestamp COMMENT '創建時間',
  `update_date_time` timestamp COMMENT '最後更新時間',
  `deleted` STRING COMMENT '是否被刪除（禁用）',
  `name` STRING COMMENT '學科名稱',
  `code` STRING COMMENT '學科編碼',
  `tenant` int COMMENT '租户')
comment '學科字典表'

PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

drop table itcast_dwd.`itcast_intention_dwd`;
CREATE TABLE IF NOT EXISTS itcast_dwd.`itcast_intention_dwd` (
  `rid` int COMMENT 'id',
  `customer_id` STRING COMMENT '客户id',
  `create_date_time` STRING COMMENT '創建時間',
  `itcast_school_id` STRING COMMENT '校區id',
  `deleted` STRING COMMENT '是否被刪除',
  `origin_type` STRING COMMENT '來源渠道',
  `itcast_subject_id` STRING COMMENT '學科id',
  `creator` int COMMENT '創建人',
  `hourinfo` STRING COMMENT '小時信息',
  `origin_type_stat` STRING COMMENT '數據來源:0.線下；1.線上'
)
comment '客户意向dwd表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
clustered by(rid) sorted by(rid) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

create database itcast_dwm;

drop table itcast_dwm.`itcast_intention_dwm`;
CREATE TABLE IF NOT EXISTS itcast_dwm.`itcast_intention_dwm` (
  `customer_id` STRING COMMENT 'id信息',
  `create_date_time` STRING COMMENT '創建時間',
  `area` STRING COMMENT '區域信息',
  `itcast_school_id` STRING COMMENT '校區id',
  `itcast_school_name` STRING COMMENT '校區名稱',
  `deleted` STRING COMMENT '是否被刪除',
  `origin_type` STRING COMMENT '來源渠道',
  `itcast_subject_id` STRING COMMENT '學科id',
  `itcast_subject_name` STRING COMMENT '學科名稱',
  `hourinfo` STRING COMMENT '小時信息',
  `origin_type_stat` STRING COMMENT '數據來源:0.線下；1.線上',
  `clue_state_stat` STRING COMMENT '新老客户：0.老客户；1.新客户',
  `tdepart_id` STRING COMMENT '創建者部門id',
  `tdepart_name` STRING COMMENT '諮詢中心名稱'
)
comment '客户意向dwm表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
clustered by(customer_id) sorted by(customer_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

drop Table itcast_dws.itcast_intention_dws;
CREATE TABLE IF NOT EXISTS itcast_dws.itcast_intention_dws (
   `customer_total` INT COMMENT '聚合意向客户數',
   `area` STRING COMMENT '區域信息',
   `itcast_school_id` STRING COMMENT '校區id',
   `itcast_school_name` STRING COMMENT '校區名稱',
   `origin_type` STRING COMMENT '來源渠道',
   `itcast_subject_id` STRING COMMENT '學科id',
   `itcast_subject_name` STRING COMMENT '學科名稱',
   `hourinfo` STRING COMMENT '小時信息',
   `origin_type_stat` STRING COMMENT '數據來源:0.線下；1.線上',
   `clue_state_stat` STRING COMMENT '客户屬性：0.老客户；1.新客户',
   `tdepart_id` STRING COMMENT '創建者部門id',
   `tdepart_name` STRING COMMENT '諮詢中心名稱',
   `time_str` STRING COMMENT '時間明細',
   `groupType` STRING COMMENT '產品屬性類別：1.總意向量；2.區域信息；3.校區、學科組合分組；4.來源渠道；5.貢獻中心;',
   `time_type` STRING COMMENT '時間維度：1、按小時聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合；'
)
comment '客户意向dws表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

select id,
       customer_relationship_id,
       create_date_time,
       update_date_time,
       deleted,
       name,
       idcard,
       birth_year,
       gender,
       phone,
       wechat,
       qq,
       email,
       area,
       leave_school_date,
       graduation_date,
       bxg_student_id,
       creator,
       origin_type,
       origin_channel,
       tenant,
       md_id,
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time
from customer;

sqoop import \

--connect jdbc:mysql://192.168.52.150:3306/scrm \

--username root \

--password 123456 \

--query 'select id, customer_relationship_id, create_date_time, update_date_time, deleted, name, idcard, birth_year, gender, phone,  wechat, qq, email, area, leave_school_date, graduation_date, bxg_student_id, creator, origin_type, origin_channel, tenant, md_id, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as start_time from customer where $CONDITIONS' \

--hcatalog-database itcast_dimen \

--hcatalog-table customer \

-m 100 \

--split-by id

select id,
   email,
   real_name,
   -1                                    as phone,
   department_id,
   department_name,
   remote_login,
   job_number,
   cross_school,
   last_login_date,
   creator,
   create_date_time,
   update_date_time,
   deleted,
   scrm_department_id,
   leave_office,
   leave_office_time,
   reinstated_time,
   superior_leaders_id,
   tdepart_id,
   tenant,
   ems_user_name,
   FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as start_time
from employee;

sqoop import \

--connect jdbc:mysql://192.168.52.150:3306/scrm \

--username root \

--password 123456 \

--query 'select id,email,real_name,-1 as phone,department_id,department_name,remote_login,job_number,cross_school,last_login_date,creator,create_date_time,update_date_time,deleted,scrm_department_id,leave_office,leave_office_time,reinstated_time,superior_leaders_id,tdepart_id,tenant,ems_user_name,FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as start_time from employee where $CONDITIONS' \

--hcatalog-database itcast_dimen \

--hcatalog-table employee \

-m 100 \

--split-by id

select *,
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time
from scrm_department;

sqoop import \

--connect jdbc:mysql://192.168.52.150:3306/scrm \

--username root \

--password 123456 \

--query 'select *, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as start_time from scrm_department where $CONDITIONS' \

--hcatalog-database itcast_dimen \

--hcatalog-table scrm_department \

-m 100 \

--split-by id

select *,
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time
from itcast_school;

sqoop import \

--connect jdbc:mysql://192.168.52.150:3306/scrm \

--username root \

--password 123456 \

--query 'select *, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as start_time from itcast_school where $CONDITIONS' \

--hcatalog-database itcast_dimen \

--hcatalog-table itcast_school \

-m 100 \

--split-by id

select *,
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time
from itcast_subject;

sqoop import \

--connect jdbc:mysql://192.168.52.150:3306/scrm \

--username root \

--password 123456 \

--query 'select *, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as start_time from itcast_subject where $CONDITIONS' \

--hcatalog-database itcast_dimen \

--hcatalog-table itcast_subject \

-m 100 \

--split-by id

select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time,
       date_format("9999-12-31", "%Y-%m-%d")       as end_time
from customer_relationship;

sqoop import \

--connect jdbc:mysql://192.168.52.150:3306/scrm \

--username root \

--password 123456 \

--query 'select id, create_date_time, update_date_time, deleted, customer_id, first_id, belonger, belonger_name, initial_belonger, distribution_handler, business_scrm_department_id, last_visit_time, next_visit_time, origin_type, itcast_school_id, itcast_subject_id, intention_study_type, anticipat_signup_date, level, creator, current_creator, creator_name, origin_channel, comment, first_customer_clue_id, last_customer_clue_id, process_state, process_time, payment_state, payment_time, signup_state, signup_time, notice_state, notice_time, lock_state, lock_time, itcast_clazz_id, itcast_clazz_time, payment_url, payment_url_time, ems_student_id, delete_reason, deleter, deleter_name, delete_time, course_id, course_name, delete_comment, close_state, close_time, appeal_id, tenant, total_fee, belonged, belonged_time, belonger_time, transfer, transfer_time, follow_type, transfer_bxg_oa_account, transfer_bxg_belonger_name, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as start_time,date_format("9999-12-31","%Y-%m-%d") as end_time from customer_relationship where $CONDITIONS' \

--hcatalog-database itcast_ods \

--hcatalog-table customer_relationship \

-m 10 \

--split-by id

DROP TABLE itcast_ods.`customer_relationship_tmp`;
CREATE TABLE IF NOT EXISTS itcast_ods.`customer_relationship_tmp` (
  `id` int COMMENT '客户關係id',
  `create_date_time` STRING COMMENT '創建時間',
  `update_date_time` STRING COMMENT '最後更新時間',
  `deleted` int COMMENT '是否被刪除（禁用）',
  `customer_id` int COMMENT '所屬客户id',
  `first_id` int COMMENT '第一條客户關係id',
  `belonger` int COMMENT '歸屬人',
  `belonger_name` STRING COMMENT '歸屬人姓名',
  `initial_belonger` int COMMENT '初始歸屬人',
  `distribution_handler` int COMMENT '分配處理人',
  `business_scrm_department_id` int COMMENT '歸屬部門',
  `last_visit_time` STRING COMMENT '最後回訪時間',
  `next_visit_time` STRING COMMENT '下次回訪時間',
  `origin_type` STRING COMMENT '數據來源',
  `itcast_school_id` int COMMENT '校區Id',
  `itcast_subject_id` int COMMENT '學科Id',
  `intention_study_type` STRING COMMENT '意向學習方式',
  `anticipat_signup_date` STRING COMMENT '預計報名時間',
  `level` STRING COMMENT '客户級別',
  `creator` int COMMENT '創建人',
  `current_creator` int COMMENT '當前創建人：初始==創建人，當在公海拉回時為 拉回人',
  `creator_name` STRING COMMENT '創建者姓名',
  `origin_channel` STRING COMMENT '來源渠道',
  `comment` STRING COMMENT '備註',
  `first_customer_clue_id` int COMMENT '第一條線索id',
  `last_customer_clue_id` int COMMENT '最後一條線索id',
  `process_state` STRING COMMENT '處理狀態',
  `process_time` STRING COMMENT '處理狀態變動時間',
  `payment_state` STRING COMMENT '支付狀態',
  `payment_time` STRING COMMENT '支付狀態變動時間',
  `signup_state` STRING COMMENT '報名狀態',
  `signup_time` STRING COMMENT '報名時間',
  `notice_state` STRING COMMENT '通知狀態',
  `notice_time` STRING COMMENT '通知狀態變動時間',
  `lock_state` STRING COMMENT '鎖定狀態',
  `lock_time` STRING COMMENT '鎖定狀態修改時間',
  `itcast_clazz_id` int COMMENT '所屬ems班級id',
  `itcast_clazz_time` STRING COMMENT '報班時間',
  `payment_url` STRING COMMENT '付款鏈接',
  `payment_url_time` STRING COMMENT '支付鏈接生成時間',
  `ems_student_id` int COMMENT 'ems的學生id',
  `delete_reason` STRING COMMENT '刪除原因',
  `deleter` int COMMENT '刪除人',
  `deleter_name` STRING COMMENT '刪除人姓名',
  `delete_time` STRING COMMENT '刪除時間',
  `course_id` int COMMENT '課程ID',
  `course_name` STRING COMMENT '課程名稱',
  `delete_comment` STRING COMMENT '刪除原因説明',
  `close_state` STRING COMMENT '關閉裝填',
  `close_time` STRING COMMENT '關閉狀態變動時間',
  `appeal_id` int COMMENT '申訴id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '報名費總金額',
  `belonged` int COMMENT '小週期歸屬人',
  `belonged_time` STRING COMMENT '歸屬時間',
  `belonger_time` STRING COMMENT '歸屬時間',
  `transfer` int COMMENT '轉移人',
  `transfer_time` STRING COMMENT '轉移時間',
  `follow_type` int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  `transfer_bxg_oa_account` STRING COMMENT '轉移到博學谷歸屬人OA賬號',
  `transfer_bxg_belonger_name` STRING COMMENT '轉移到博學谷歸屬人OA姓名',
  `end_time` STRING COMMENT '有效截止時間')
comment '客户關係表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time,
       date_format("9999-12-31", "%Y-%m-%d")       as end_time
from customer_relationship;

sqoop import \

--connect jdbc:mysql://192.168.52.150:3306/scrm \

--username root \

--password 123456 \

--query 'select id, create_date_time, update_date_time, deleted, customer_id, first_id, belonger, belonger_name, initial_belonger, distribution_handler, business_scrm_department_id, last_visit_time, next_visit_time, origin_type, itcast_school_id, itcast_subject_id, intention_study_type, anticipat_signup_date, level, creator, current_creator, creator_name, origin_channel, comment, first_customer_clue_id, last_customer_clue_id, process_state, process_time, payment_state, payment_time, signup_state, signup_time, notice_state, notice_time, lock_state, lock_time, itcast_clazz_id, itcast_clazz_time, payment_url, payment_url_time, ems_student_id, delete_reason, deleter, deleter_name, delete_time, course_id, course_name, delete_comment, close_state, close_time, appeal_id, tenant, total_fee, belonged, belonged_time, belonger_time, transfer, transfer_time, follow_type, transfer_bxg_oa_account, transfer_bxg_belonger_name, FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as start_time,date_format("9999-12-31","%Y-%m-%d") as end_time from customer_relationship where $CONDITIONS' \

--hcatalog-database itcast_ods \

--hcatalog-table customer_relationship_tmp \

-m 10 \

--split-by id

--分區
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive壓縮
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--寫入時壓縮生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

insert overwrite table itcast_ods.customer_relationship partition(start_time)
select * from itcast_ods.customer_relationship_tmp;

DROP TABLE itcast_ods.customer_clue_tmp;
CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue_tmp (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '創建時間',
  update_date_time STRING COMMENT '最後更新時間',
  deleted STRING COMMENT '是否被刪除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户關係id',
  session_id STRING COMMENT '七陌會話id',
  sid STRING COMMENT '訪客id',
  status STRING COMMENT '狀態（undeal待領取 deal 已領取 finish 已關閉 changePeer 已流轉）',
  users STRING COMMENT '所屬坐席',
  create_time STRING COMMENT '七陌創建時間',
  platform STRING COMMENT '平台來源 （pc-網站諮詢|wap-wap諮詢|sdk-app諮詢|weixin-微信諮詢）',
  s_name STRING COMMENT '用户名稱',
  seo_source STRING COMMENT '搜索來源',
  seo_keywords STRING COMMENT '關鍵字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上級來源頁面',
  from_url STRING COMMENT '會話來源頁面',
  landing_page_url STRING COMMENT '訪客着陸頁面',
  url_title STRING COMMENT '諮詢頁面title',
  to_peer STRING COMMENT '所屬技能組',
  manual_time STRING COMMENT '人工開始時間',
  begin_time STRING COMMENT '坐席領取時間 ',
  reply_msg_count int COMMENT '客服回覆消息數',
  total_msg_count int COMMENT '消息總數',
  msg_count int COMMENT '客户發送消息數',
  comment STRING COMMENT '備註',
  finish_reason STRING COMMENT '結束類型',
  finish_user STRING COMMENT '結束坐席',
  end_time STRING COMMENT '會話結束時間',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '瀏覽器名稱',
  os_info STRING COMMENT '系統名稱',
  area STRING COMMENT '區域',
  country STRING COMMENT '所在國家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '創建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份證號',
  phone STRING COMMENT '手機號',
  itcast_school_id int COMMENT '校區Id',
  itcast_school STRING COMMENT '校區',
  itcast_subject_id int COMMENT '學科Id',
  itcast_subject STRING COMMENT '學科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq號',
  email STRING COMMENT '郵箱',
  gender STRING COMMENT '性別',
  level STRING COMMENT '客户級別',
  origin_type STRING COMMENT '數據來源渠道',
  information_way STRING COMMENT '資訊方式',
  working_years STRING COMMENT '開始工作時間',
  technical_directions STRING COMMENT '技術方向',
  customer_state STRING COMMENT '當前客户狀態',
  valid STRING COMMENT '該線索是否是網資有效線索',
  anticipat_signup_date STRING COMMENT '預計報名時間',
  clue_state STRING COMMENT '線索狀態',
  scrm_department_id int COMMENT 'SCRM內部部門id',
  superior_url STRING COMMENT '諸葛獲取上級頁面URL',
  superior_source STRING COMMENT '諸葛獲取上級頁面URL標題',
  landing_url STRING COMMENT '諸葛獲取着陸頁面URL',
  landing_source STRING COMMENT '諸葛獲取着陸頁面URL來源',
  info_url STRING COMMENT '諸葛獲取留諮頁URL',
  info_source STRING COMMENT '諸葛獲取留諮頁URL標題',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '課程編號',
  course_name STRING COMMENT '課程名稱',
  zhuge_session_id STRING COMMENT 'zhuge會話id',
  is_repeat int COMMENT '是否重複線索(手機號維度) 0:正常 1：重複',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活動id',
  activity_name STRING COMMENT '活動名稱',
  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  shunt_mode_id int COMMENT '匹配到的技能組id',
  shunt_employee_group_id int COMMENT '所屬分流員工組',
  ends_time STRING COMMENT '有效時間')
comment '客户關係表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       customer_relationship_id,
       session_id,
       sid,
       status, user, create_time, platform, s_name, seo_source, seo_keywords, ip, referrer, from_url, landing_page_url, url_title, to_peer, manual_time, begin_time, reply_msg_count, total_msg_count, msg_count, comment, finish_reason, finish_user, end_time, platform_description, browser_name, os_info, area, country, province, city, creator, name, idcard, phone, itcast_school_id, itcast_school, itcast_subject_id, itcast_subject, wechat, qq, email, gender, level, origin_type, information_way, working_years, technical_directions, customer_state, valid, anticipat_signup_date, clue_state, scrm_department_id, superior_url, superior_source, landing_url, landing_source, info_url, info_source, origin_channel, course_id, course_name, zhuge_session_id, is_repeat, tenant, activity_id, activity_name, follow_type, shunt_mode_id, shunt_employee_group_id, 
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as starts_time, date_format("9999-12-31", "%Y-%m-%d") as ends_time
from customer_clue;

sqoop import \

--connect jdbc:mysql://192.168.52.150:3306/scrm \

--username root \

--password 123456 \

--query 'select id,create_date_time,update_date_time,deleted,customer_id,customer_relationship_id,session_id,sid,status,user as users,create_time,platform,s_name,seo_source,seo_keywords,ip,referrer,from_url,landing_page_url,url_title,to_peer,manual_time,begin_time,reply_msg_count,total_msg_count,msg_count,comment,finish_reason,finish_user,end_time,platform_description,browser_name,os_info,area,country,province,city,creator,name,"-1" as idcard,"-1" as phone,itcast_school_id,itcast_school,itcast_subject_id,itcast_subject,"-1" as wechat,"-1" as qq,"-1" as email,gender,level,origin_type,information_way,working_years,technical_directions,customer_state,valid,anticipat_signup_date,clue_state,scrm_department_id,superior_url,superior_source,landing_url,landing_source,info_url,info_source,origin_channel,course_id,course_name,zhuge_session_id,is_repeat,tenant,activity_id,activity_name,follow_type,shunt_mode_id,shunt_employee_group_id,FROM_UNIXTIME(unix_timestamp(),"%Y-%m-%d")as starts_time,date_format("9999-12-31","%Y-%m-%d") as ends_time from customer_clue where $CONDITIONS' \

--hcatalog-database itcast_ods \

--hcatalog-table customer_clue_tmp \

-m 10 \

--split-by id

insert overwrite table itcast_ods.customer_clue partition(starts_time)
select * from itcast_ods.customer_clue_tmp;

EXPLAIN [EXTENDED] query

1   STAGE DEPENDENCIES:
2    Stage-1 is a root stage
3    Stage-0 depends on stages: Stage-1

1   STAGE DEPENDENCIES:
2    Stage-1 is a root stage
3    Stage-0 depends on stages: Stage-1
4
5  STAGE PLANS:
6    Stage: Stage-1
7      Map Reduce
8        Map Operator Tree:
9            TableScan
10             alias: rs
11             Statistics: Num rows: 1109147 Data size: 236547154 Basic stats: COMPLETE Column stats: COMPLETE
12             Filter Operator
13               predicate: (((hash(id) & 2147483647) % 10) = 0) (type: boolean)
14               Statistics: Num rows: 554573 Data size: 118273474 Basic stats: COMPLETE Column stats: COMPLETE
15               Select Operator
16                 expressions: id (type: int), customer_id (type: int), create_date_time (type: string), if((itcast_school_id is null or (itcast_school_id = 0)), -1, itcast_school_id) (type: int), deleted (type: int), origin_type (type: string), if((itcast_subject_id is null or (itcast_subject_id = 0)), -1, itcast_subject_id) (type: int), substr(create_date_time, 12, 2) (type: string), if((origin_type = 'NETSERVICE'), '1', if((origin_type = 'PRESIGNUP'), '1', '0')) (type: string), substr(create_date_time, 1, 4) (type: string), substr(create_date_time, 6, 2) (type: string), substr(create_date_time, 9, 2) (type: string)
17                 outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11
18                 Statistics: Num rows: 554573 Data size: 631104074 Basic stats: COMPLETE Column stats: COMPLETE
19                 File Output Operator
20                   compressed: false
21                   Statistics: Num rows: 554573 Data size: 631104074 Basic stats: COMPLETE Column stats: COMPLETE
22                   table:
23                       input format: org.apache.hadoop.mapred.SequenceFileInputFormat
24                       output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
25                       serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
26
27   Stage: Stage-0
28     Fetch Operator
29       limit: -1
30       Processor Tree:
31         ListSink

--分區
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive壓縮
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--寫入時壓縮生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

insert into table itcast_dwd.itcast_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    rs.id as rid,
    rs.customer_id,
    rs.create_date_time,
    if((rs.itcast_school_id is null) or (rs.itcast_school_id = 0), -1, rs.itcast_school_id) as itcast_school_id,
    rs.deleted,
    rs.origin_type,
    if((rs.itcast_subject_id is null) or (rs.itcast_subject_id = 0), -1, rs.itcast_subject_id) as itcast_subject_id,
    substr(rs.create_date_time, 12, 2) hourinfo,
    if(rs.origin_type='NETSERVICE', '1', if(rs.origin_type='PRESIGNUP', '1', '0')) as origin_type_stat,
    substr(rs.create_date_time, 1, 4) yearinfo,
    substr(rs.create_date_time, 6, 2) monthinfo,
    substr(rs.create_date_time, 9, 2) dayinfo
from itcast_ods.customer_relationship rs
where rs.deleted = 0;

insert into table itcast_dwd.itcast_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    rs.id as rid,
    rs.customer_id,
    rs.create_date_time,
    if((rs.itcast_school_id is null) or (rs.itcast_school_id = 0), -1, rs.itcast_school_id) as itcast_school_id,
    rs.deleted,
    rs.origin_type,
    if((rs.itcast_subject_id is null) or (rs.itcast_subject_id = 0), -1, rs.itcast_subject_id) as itcast_subject_id,
    substr(rs.create_date_time, 12, 2) hourinfo,
    if(rs.origin_type='NETSERVICE', '1', if(rs.origin_type='PRESIGNUP', '1', '0')) as origin_type_stat,
    substr(rs.create_date_time, 1, 4) yearinfo,
    substr(rs.create_date_time, 6, 2) monthinfo,
    substr(rs.create_date_time, 9, 2) dayinfo
from itcast_ods.customer_relationship tablesample(bucket 1 out of 10 on id) rs
where rs.deleted = 0;

set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;

select count(1), substr(create_date_time, 1, 4) from itcast_ods.customer_relationship group by substr(create_date_time, 1, 4);

insert into table itcast_dwd.itcast_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    rs.id as rid,
    rs.customer_id,
    rs.create_date_time,
    if((rs.itcast_school_id is null) or (rs.itcast_school_id = 0), -1, rs.itcast_school_id) as itcast_school_id,
    rs.deleted,
    rs.origin_type,
    if((rs.itcast_subject_id is null) or (rs.itcast_subject_id = 0), -1, rs.itcast_subject_id) as itcast_subject_id,
    substr(rs.create_date_time, 12, 2) hourinfo,
    if(rs.origin_type='NETSERVICE', '1', if(rs.origin_type='PRESIGNUP', '1', '0')) as origin_type_stat,
    substr(rs.create_date_time, 1, 4) yearinfo,
    substr(rs.create_date_time, 6, 2) monthinfo,
    substr(rs.create_date_time, 9, 2) dayinfo
from itcast_ods.customer_relationship tablesample(bucket 1 out of 10 on id) rs
where rs.create_date_time between '2011-01-01 00:00:00' and '2012-01-01 00:00:00';

set hive.exec.mode.local.auto=true;

--分區
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive壓縮
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--寫入時壓縮生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

insert into table itcast_dwm.itcast_intention_dwm partition (yearinfo,monthinfo,dayinfo)
select
    dwd.customer_id,
    dwd.create_date_time,
    cus.area,
    dwd.itcast_school_id,
    sch.name as itcast_school_name,
    dwd.deleted,
    dwd.origin_type,
    dwd.itcast_subject_id,
    sub.name as itcast_subject_name,
    dwd.hourinfo,
    dwd.origin_type_stat,
    if(clue.clue_state='VALID_NEW_CLUES', '1', if(clue.clue_state='VALID_PUBLIC_NEW_CLUE', '0', '-1')) as clue_state_stat,
    e.department_id as tdepart_id,
    dept.name as tdepart_name,
    dwd.yearinfo,
    dwd.monthinfo,
    dwd.dayinfo
from itcast_dwd.itcast_intention_dwd dwd
left join itcast_ods.customer_clue clue on clue.customer_relationship_id=dwd.rid
left join itcast_dimen.customer cus on dwd.customer_id = cus.id
left join itcast_dimen.employee e on dwd.creator = e.id
left join itcast_dimen.scrm_department dept on e.department_id = dept.id
left join itcast_dimen.itcast_subject sub on dwd.itcast_subject_id = sub.id and sub.name is not null
left join itcast_dimen.itcast_school sch on dwd.itcast_school_id = sch.id;

explain
select
    dwd.customer_id,
    dwd.create_date_time,
    cus.area,
    dwd.itcast_school_id,
    sch.name as itcast_school_name,
    dwd.deleted,
    dwd.origin_type,
    dwd.itcast_subject_id,
    sub.name as itcast_subject_name,
    dwd.hourinfo,
    dwd.origin_type_stat,
    if(clue.clue_state='VALID_NEW_CLUES', '1', if(clue.clue_state='VALID_PUBLIC_NEW_CLUE', '0', '-1')) as clue_state_stat,
    e.department_id as tdepart_id,
    dept.name as tdepart_name,
    dwd.yearinfo,
    dwd.monthinfo,
    dwd.dayinfo
from itcast_dwd.itcast_intention_dwd tablesample(bucket 1 out of 10 on rid) dwd
left join itcast_ods.customer_clue clue on clue.customer_relationship_id=dwd.rid
left join itcast_dimen.customer cus on dwd.customer_id = cus.id
left join itcast_dimen.employee e on dwd.creator = e.id
left join itcast_dimen.scrm_department dept on e.department_id = dept.id
left join itcast_dimen.itcast_subject sub on dwd.itcast_subject_id = sub.id
left join itcast_dimen.itcast_school sch on dwd.itcast_school_id = sch.id;

insert into table itcast_dwm.itcast_intention_dwm partition (yearinfo,monthinfo,dayinfo)
select
    dwd.customer_id,
    dwd.create_date_time,
    cus.area,
    dwd.itcast_school_id,
    sch.name as itcast_school_name,
    dwd.deleted,
    dwd.origin_type,
    dwd.itcast_subject_id,
    sub.name as itcast_subject_name,
    dwd.hourinfo,
    dwd.origin_type_stat,
    if(clue.clue_state='VALID_NEW_CLUES', '1', if(clue.clue_state='VALID_PUBLIC_NEW_CLUE', '0', '-1')) as clue_state_stat,
    e.department_id as tdepart_id,
    dept.name as tdepart_name,
    dwd.yearinfo,
    dwd.monthinfo,
    dwd.dayinfo
from itcast_dwd.itcast_intention_dwd dwd
left join itcast_ods.customer_clue clue on clue.customer_relationship_id=dwd.rid
left join itcast_dimen.customer cus on dwd.customer_id = cus.id
left join itcast_dimen.employee e on dwd.creator = e.id
left join itcast_dimen.scrm_department dept on e.department_id = dept.id
left join itcast_dimen.itcast_subject sub on dwd.itcast_subject_id = sub.id
left join itcast_dimen.itcast_school sch on dwd.itcast_school_id = sch.id;

--分區
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive壓縮
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--寫入時壓縮生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

--總意向量分組（按照時間和常駐類型統計）
--小時
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
    '1' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by yearinfo, monthinfo, dayinfo, hourinfo, origin_type_stat, clue_state_stat;
--天
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
    '1' as grouptype,
    '2' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by yearinfo, monthinfo, dayinfo, origin_type_stat, clue_state_stat;
--月
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo) as time_str,
    '1' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by yearinfo, monthinfo, origin_type_stat, clue_state_stat;
--年
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo) as time_str,
    '1' as grouptype,
    '1' as time_type,
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by yearinfo, origin_type_stat, clue_state_stat;

--地區分組（按照地區、時間和常駐類型統計）
--小時
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
    '2' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by area, yearinfo, monthinfo, dayinfo, hourinfo, origin_type_stat, clue_state_stat;
--天
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
    '2' as grouptype,
    '2' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by area, yearinfo, monthinfo, dayinfo, origin_type_stat, clue_state_stat;
--月
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo) as time_str,
    '1' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by area, yearinfo, monthinfo, origin_type_stat, clue_state_stat;
--年
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo) as time_str,
    '2' as grouptype,
    '1' as time_type,
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by area, yearinfo, origin_type_stat, clue_state_stat;

--學科、校區分組（按照學科、校區、時間和常駐類型統計）
--小時
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    itcast_school_id,
    itcast_school_name,
    '-1' as origin_type,
    itcast_subject_id,
    itcast_subject_name,
    hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
    '3' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name, yearinfo, monthinfo, dayinfo, hourinfo, origin_type_stat, clue_state_stat;
--天
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    itcast_school_id,
    itcast_school_name,
    '-1' as origin_type,
    itcast_subject_id,
    itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
    '3' as grouptype,
    '2' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name, yearinfo, monthinfo, dayinfo, origin_type_stat, clue_state_stat;
--月
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    itcast_school_id,
    itcast_school_name,
    '-1' as origin_type,
    itcast_subject_id,
    itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo) as time_str,
    '3' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name, yearinfo, monthinfo, origin_type_stat, clue_state_stat;
--年
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    itcast_school_id,
    itcast_school_name,
    '-1' as origin_type,
    itcast_subject_id,
    itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo) as time_str,
    '3' as grouptype,
    '1' as time_type,
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by itcast_school_id, itcast_school_name, itcast_subject_id, itcast_subject_name, yearinfo, origin_type_stat, clue_state_stat;

--來源渠道分組（按照來源渠道、時間和常駐類型統計）
--小時
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
    '4' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by origin_type, yearinfo, monthinfo, dayinfo, hourinfo, origin_type_stat, clue_state_stat;
--天
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
    '4' as grouptype,
    '2' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by origin_type, yearinfo, monthinfo, dayinfo, origin_type_stat, clue_state_stat;
--月
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo) as time_str,
    '4' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by origin_type, yearinfo, monthinfo, origin_type_stat, clue_state_stat;
--年
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo) as time_str,
    '4' as grouptype,
    '1' as time_type,
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by origin_type, yearinfo, origin_type_stat, clue_state_stat;

--諮詢中心分組（按照諮詢中心、時間和常駐類型統計）
--小時
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    hourinfo,
    origin_type_stat,
    clue_state_stat,
    tdepart_id,
    tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
    '5' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by tdepart_id, tdepart_name, yearinfo, monthinfo, dayinfo, hourinfo, origin_type_stat, clue_state_stat;
--天
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    tdepart_id,
    tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
    '5' as grouptype,
    '2' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by tdepart_id, tdepart_name, yearinfo, monthinfo, dayinfo, origin_type_stat, clue_state_stat;
--月
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    tdepart_id,
    tdepart_name,
    concat(yearinfo,'-',monthinfo) as time_str,
    '5' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by tdepart_id, tdepart_name, yearinfo, monthinfo, origin_type_stat, clue_state_stat;
--年
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' as itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    tdepart_id,
    tdepart_name,
    concat(yearinfo) as time_str,
    '5' as grouptype,
    '1' as time_type,
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
group by tdepart_id, tdepart_name, yearinfo, origin_type_stat, clue_state_stat;

CREATE TABLE itcast_intention_app (
  `customer_total` int(11) COMMENT '聚合意向客户數',
  `area` varchar(32) COMMENT '區域信息',
  `itcast_school_id` varchar(32) COMMENT '校區id',
  `itcast_school_name` varchar(32) COMMENT '校區名稱',
  `origin_type` varchar(32) COMMENT '來源渠道',
  `itcast_subject_id` varchar(32) COMMENT '學科id',
   `itcast_subject_name` varchar(32) COMMENT '學科名稱',
   `hourinfo` varchar(32) COMMENT '小時信息',
   `origin_type_stat` varchar(32) COMMENT '數據來源:0.線下；1.線上',
   `clue_state_stat` varchar(32) COMMENT '客户屬性：0.老客户；1.新客户',
  `tdepart_id` varchar(32) COMMENT '創建者',
   `tdepart_name` varchar(32) COMMENT '諮詢中心名稱',
   `time_str` varchar(32) COMMENT '時間明細',
  `groupType` varchar(32) COMMENT '產品屬性類別：1.總意向量；2.區域信息；3.校區、學科組合分組；4.來源渠道；5.貢獻中心;',
  `time_type` varchar(32) COMMENT '聚合時間類型：1、按小時聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合；',
  `dayinfo` varchar(32) COMMENT '日信息',
  `monthinfo` varchar(32) COMMENT '月信息',
  `yearinfo` varchar(32) COMMENT '年信息'
);

sqoop export \
--connect "jdbc:mysql://192.168.52.150:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password '123456' \
--table itcast_intention_app \
--hcatalog-database itcast_dws \
--hcatalog-table itcast_intention_dws \
-m 100

DROP TABLE IF EXISTS itcast_ods.customer_relationship_update;
CREATE TABLE IF NOT EXISTS itcast_ods.customer_relationship_update (
  id int COMMENT '客户關係id',
  create_date_time STRING COMMENT '創建時間',
  update_date_time STRING COMMENT '最後更新時間',
  deleted int COMMENT '是否被刪除（禁用）',
  customer_id int COMMENT '所屬客户id',
  first_id int COMMENT '第一條客户關係id',
  belonger int COMMENT '歸屬人',
  belonger_name STRING COMMENT '歸屬人姓名',
  initial_belonger int COMMENT '初始歸屬人',
  distribution_handler int COMMENT '分配處理人',
  business_scrm_department_id int COMMENT '歸屬部門',
  last_visit_time STRING COMMENT '最後回訪時間',
  next_visit_time STRING COMMENT '下次回訪時間',
  origin_type STRING COMMENT '數據來源',
  itcast_school_id int COMMENT '校區Id',
  itcast_subject_id int COMMENT '學科Id',
  intention_study_type STRING COMMENT '意向學習方式',
  anticipat_signup_date STRING COMMENT '預計報名時間',
  level STRING COMMENT '客户級別',
  creator int COMMENT '創建人',
  current_creator int COMMENT '當前創建人：初始==創建人，當在公海拉回時為 拉回人',
  creator_name STRING COMMENT '創建者姓名',
  origin_channel STRING COMMENT '來源渠道',
  comment STRING COMMENT '備註',
  first_customer_clue_id int COMMENT '第一條線索id',
  last_customer_clue_id int COMMENT '最後一條線索id',
  process_state STRING COMMENT '處理狀態',
  process_time STRING COMMENT '處理狀態變動時間',
  payment_state STRING COMMENT '支付狀態',
  payment_time STRING COMMENT '支付狀態變動時間',
  signup_state STRING COMMENT '報名狀態',
  signup_time STRING COMMENT '報名時間',
  notice_state STRING COMMENT '通知狀態',
  notice_time STRING COMMENT '通知狀態變動時間',
  lock_state STRING COMMENT '鎖定狀態',
  lock_time STRING COMMENT '鎖定狀態修改時間',
  itcast_clazz_id int COMMENT '所屬ems班級id',
  itcast_clazz_time STRING COMMENT '報班時間',
  payment_url STRING COMMENT '付款鏈接',
  payment_url_time STRING COMMENT '支付鏈接生成時間',
  ems_student_id int COMMENT 'ems的學生id',
  delete_reason STRING COMMENT '刪除原因',
  deleter int COMMENT '刪除人',
  deleter_name STRING COMMENT '刪除人姓名',
  delete_time STRING COMMENT '刪除時間',
  course_id int COMMENT '課程ID',
  course_name STRING COMMENT '課程名稱',
  delete_comment STRING COMMENT '刪除原因説明',
  close_state STRING COMMENT '關閉裝填',
  close_time STRING COMMENT '關閉狀態變動時間',
  appeal_id int COMMENT '申訴id',
  tenant int COMMENT '租户',
  total_fee DECIMAL COMMENT '報名費總金額',
  belonged int COMMENT '小週期歸屬人',
  belonged_time STRING COMMENT '歸屬時間',
  belonger_time STRING COMMENT '歸屬時間',
  transfer int COMMENT '轉移人',
  transfer_time STRING COMMENT '轉移時間',
  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  transfer_bxg_oa_account STRING COMMENT '轉移到博學谷歸屬人OA賬號',
  transfer_bxg_belonger_name STRING COMMENT '轉移到博學谷歸屬人OA姓名',
  end_time STRING COMMENT '有效時間')
comment '客户關係表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time,
       "9999-12-31"       as end_time
from customer_relationship
where
 (
    create_date_time >= "2011-12-04 00:00:00"
     and
    create_date_time < "2011-12-05 00:00:00"
  )
  or
  (
    update_date_time >= "2011-12-04 00:00:00"
    and
    update_date_time < "2011-12-05 00:00:00"
  );

sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query '

select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time,
       date_format("9999-12-31", "%Y-%m-%d")       as end_time
from customer_relationship
where
  (
    create_date_time >= "2011-12-04 00:00:00"
     and
    create_date_time < "2011-12-05 00:00:00"
  )
  or
  (
    update_date_time >= "2011-12-04 00:00:00"
    and
    update_date_time < "2011-12-05 00:00:00"
  )

 and $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_relationship_update
--hive-partition-key start_time \
--hive-partition-value  2020-07-15 \
-m 100 \
--split-by id

DROP TABLE itcast_ods.`customer_relationship_tmp`;
CREATE TABLE IF NOT EXISTS itcast_ods.`customer_relationship_tmp` (
  `id` int COMMENT '客户關係id',
  `create_date_time` STRING COMMENT '創建時間',
  `update_date_time` STRING COMMENT '最後更新時間',
  `deleted` int COMMENT '是否被刪除（禁用）',
  `customer_id` int COMMENT '所屬客户id',
  `first_id` int COMMENT '第一條客户關係id',
  `belonger` int COMMENT '歸屬人',
  `belonger_name` STRING COMMENT '歸屬人姓名',
  `initial_belonger` int COMMENT '初始歸屬人',
  `distribution_handler` int COMMENT '分配處理人',
  `business_scrm_department_id` int COMMENT '歸屬部門',
  `last_visit_time` STRING COMMENT '最後回訪時間',
  `next_visit_time` STRING COMMENT '下次回訪時間',
  `origin_type` STRING COMMENT '數據來源',
  `itcast_school_id` int COMMENT '校區Id',
  `itcast_subject_id` int COMMENT '學科Id',
  `intention_study_type` STRING COMMENT '意向學習方式',
  `anticipat_signup_date` STRING COMMENT '預計報名時間',
  `level` STRING COMMENT '客户級別',
  `creator` int COMMENT '創建人',
  `current_creator` int COMMENT '當前創建人：初始==創建人，當在公海拉回時為 拉回人',
  `creator_name` STRING COMMENT '創建者姓名',
  `origin_channel` STRING COMMENT '來源渠道',
  `comment` STRING COMMENT '備註',
  `first_customer_clue_id` int COMMENT '第一條線索id',
  `last_customer_clue_id` int COMMENT '最後一條線索id',
  `process_state` STRING COMMENT '處理狀態',
  `process_time` STRING COMMENT '處理狀態變動時間',
  `payment_state` STRING COMMENT '支付狀態',
  `payment_time` STRING COMMENT '支付狀態變動時間',
  `signup_state` STRING COMMENT '報名狀態',
  `signup_time` STRING COMMENT '報名時間',
  `notice_state` STRING COMMENT '通知狀態',
  `notice_time` STRING COMMENT '通知狀態變動時間',
  `lock_state` STRING COMMENT '鎖定狀態',
  `lock_time` STRING COMMENT '鎖定狀態修改時間',
  `itcast_clazz_id` int COMMENT '所屬ems班級id',
  `itcast_clazz_time` STRING COMMENT '報班時間',
  `payment_url` STRING COMMENT '付款鏈接',
  `payment_url_time` STRING COMMENT '支付鏈接生成時間',
  `ems_student_id` int COMMENT 'ems的學生id',
  `delete_reason` STRING COMMENT '刪除原因',
  `deleter` int COMMENT '刪除人',
  `deleter_name` STRING COMMENT '刪除人姓名',
  `delete_time` STRING COMMENT '刪除時間',
  `course_id` int COMMENT '課程ID',
  `course_name` STRING COMMENT '課程名稱',
  `delete_comment` STRING COMMENT '刪除原因説明',
  `close_state` STRING COMMENT '關閉裝填',
  `close_time` STRING COMMENT '關閉狀態變動時間',
  `appeal_id` int COMMENT '申訴id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '報名費總金額',
  `belonged` int COMMENT '小週期歸屬人',
  `belonged_time` STRING COMMENT '歸屬時間',
  `belonger_time` STRING COMMENT '歸屬時間',
  `transfer` int COMMENT '轉移人',
  `transfer_time` STRING COMMENT '轉移時間',
  `follow_type` int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  `transfer_bxg_oa_account` STRING COMMENT '轉移到博學谷歸屬人OA賬號',
  `transfer_bxg_belonger_name` STRING COMMENT '轉移到博學谷歸屬人OA姓名',
  `end_time` STRING COMMENT '有效截止時間')
comment '客户關係表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

insert overwrite table itcast_ods.customer_relationship_tmppartition (start_time)
select * from
    (
--      一、update表更新的數據
        select
          id,
          create_date_time,
          update_date_time,
          deleted,
          customer_id,
          first_id,
          belonger,
          belonger_name,
          initial_belonger,
          distribution_handler,
          business_scrm_department_id,
          last_visit_time,
          next_visit_time,
          origin_type,
          itcast_school_id,
          itcast_subject_id,
          intention_study_type,
          anticipat_signup_date,
          level,
          creator,
          current_creator,
          creator_name,
          origin_channel,
          comment,
          first_customer_clue_id,
          last_customer_clue_id,
          process_state,
          process_time,
          payment_state,
          payment_time,
          signup_state,
          signup_time,
          notice_state,
          notice_time,
          lock_state,
          lock_time,
          itcast_clazz_id,
          itcast_clazz_time,
          payment_url,
          payment_url_time,
          ems_student_id,
          delete_reason,
          deleter,
          deleter_name,
          delete_time,
          course_id,
          course_name,
          delete_comment,
          close_state,
          close_time,
          appeal_id,
          tenant,
          total_fee,
          belonged,
          belonged_time,
          belonger_time,
          transfer,
          transfer_time,
          follow_type,
          transfer_bxg_oa_account,
          transfer_bxg_belonger_name,
          '9999-12-31' end_time,
          '2020-07-15' as start_time
        from itcast_ods.customer_relationship_updatewhere start_time='2020-07-15'

        union all

--      二、歷史拉鍊表數據，並根據update判斷更新end_time有效期
        select
          rs.id,
          rs.create_date_time,
          rs.update_date_time,
          rs.deleted,
          rs.customer_id,
          rs.first_id,
          rs.belonger,
          rs.belonger_name,
          rs.initial_belonger,
          rs.distribution_handler,
          rs.business_scrm_department_id,
          rs.last_visit_time,
          rs.next_visit_time,
          rs.origin_type,
          rs.itcast_school_id,
          rs.itcast_subject_id,
          rs.intention_study_type,
          rs.anticipat_signup_date,
          rs.level,
          rs.creator,
          rs.current_creator,
          rs.creator_name,
          rs.origin_channel,
          rs.comment,
          rs.first_customer_clue_id,
          rs.last_customer_clue_id,
          rs.process_state,
          rs.process_time,
          rs.payment_state,
          rs.payment_time,
          rs.signup_state,
          rs.signup_time,
          rs.notice_state,
          rs.notice_time,
          rs.lock_state,
          rs.lock_time,
          rs.itcast_clazz_id,
          rs.itcast_clazz_time,
          rs.payment_url,
          rs.payment_url_time,
          rs.ems_student_id,
          rs.delete_reason,
          rs.deleter,
          rs.deleter_name,
          rs.delete_time,
          rs.course_id,
          rs.course_name,
          rs.delete_comment,
          rs.close_state,
          rs.close_time,
          rs.appeal_id,
          rs.tenant,
          rs.total_fee,
          rs.belonged,
          rs.belonged_time,
          rs.belonger_time,
          rs.transfer,
          rs.transfer_time,
          rs.follow_type,
          rs.transfer_bxg_oa_account,
          rs.transfer_bxg_belonger_name,
更新end_time：如果沒有匹配到變更數據，或者當前已經是無效的歷史數據，則保留原始end_time過期時間；否則變更end_time時間為前天（昨天之前有效）
          if(up.id is null or rs.end_time<'9999-12-31', rs.end_time, date_add(up.start_time,-1)) end_time,
          rs.start_time
        from itcast_ods.customer_relationship rs left join
            (
                select
                *
                from itcast_ods.customer_relationship_update
                where start_time='2020-07-15'
            ) up
        on rs.id=up.id
        --4、時間限制：歷史表中30天之內的數據才有可能變更，結果會按照所屬分區進行覆蓋插入
        where rs.start_time >= date_add(up.start_time,-30)
    )his
order by his.id, start_time;

INSERT OVERWRITE TABLE itcast_ods.customer_relationship partition (start_time) 
SELECT * from itcast_ods.customer_relationship_tmp;

SELECT * from itcast_ods.customer_relationship
WHERE create_date_time BETWEEN "2011-12-04 00:00:00" and "2011-12-05 00:00:00";

#! /bin/bash

HIVE_HOME=/usr/bin/hive

if [[ $1 == "" ]];

then

    TD_DATE=`date -d ''1 days ago'' "+%Y-%m-%d"`

else

    TD_DATE=$1

fi

output=$(${HIVE_HOME} -S -e "

SET hive.exec.dynamic.partition=true;

SET hive.exec.dynamic.partition.mode=nonstrict;

DROP TABLE IF EXISTS itcast_ods.customer_relationship_update;

CREATE TABLE IF NOT EXISTS itcast_ods.customer_relationship_update (

  id int COMMENT '客户關係id',

  create_date_time STRING COMMENT '創建時間',

  update_date_time STRING COMMENT '最後更新時間',

  deleted int COMMENT '是否被刪除（禁用）',

  customer_id int COMMENT '所屬客户id',

  first_id int COMMENT '第一條客户關係id',

  belonger int COMMENT '歸屬人',

  belonger_name STRING COMMENT '歸屬人姓名',

  initial_belonger int COMMENT '初始歸屬人',

  distribution_handler int COMMENT '分配處理人',

  business_scrm_department_id int COMMENT '歸屬部門',

  last_visit_time STRING COMMENT '最後回訪時間',

  next_visit_time STRING COMMENT '下次回訪時間',

  origin_type STRING COMMENT '數據來源',

  itcast_school_id int COMMENT '校區Id',

  itcast_subject_id int COMMENT '學科Id',

  intention_study_type STRING COMMENT '意向學習方式',

  anticipat_signup_date STRING COMMENT '預計報名時間',

  level STRING COMMENT '客户級別',

  creator int COMMENT '創建人',

  current_creator int COMMENT '當前創建人：初始==創建人，當在公海拉回時為 拉回人',

  creator_name STRING COMMENT '創建者姓名',

  origin_channel STRING COMMENT '來源渠道',

  comment STRING COMMENT '備註',

  first_customer_clue_id int COMMENT '第一條線索id',

  last_customer_clue_id int COMMENT '最後一條線索id',

  process_state STRING COMMENT '處理狀態',

  process_time STRING COMMENT '處理狀態變動時間',

  payment_state STRING COMMENT '支付狀態',

  payment_time STRING COMMENT '支付狀態變動時間',

  signup_state STRING COMMENT '報名狀態',

  signup_time STRING COMMENT '報名時間',

  notice_state STRING COMMENT '通知狀態',

  notice_time STRING COMMENT '通知狀態變動時間',

  lock_state STRING COMMENT '鎖定狀態',

  lock_time STRING COMMENT '鎖定狀態修改時間',

  itcast_clazz_id int COMMENT '所屬ems班級id',

  itcast_clazz_time STRING COMMENT '報班時間',

  payment_url STRING COMMENT '付款鏈接',

  payment_url_time STRING COMMENT '支付鏈接生成時間',

  ems_student_id int COMMENT 'ems的學生id',

  delete_reason STRING COMMENT '刪除原因',

  deleter int COMMENT '刪除人',

  deleter_name STRING COMMENT '刪除人姓名',

  delete_time STRING COMMENT '刪除時間',

  course_id int COMMENT '課程ID',

  course_name STRING COMMENT '課程名稱',

  delete_comment STRING COMMENT '刪除原因説明',

  close_state STRING COMMENT '關閉裝填',

  close_time STRING COMMENT '關閉狀態變動時間',

  appeal_id int COMMENT '申訴id',

  tenant int COMMENT '租户',

  total_fee DECIMAL COMMENT '報名費總金額',

  belonged int COMMENT '小週期歸屬人',

  belonged_time STRING COMMENT '歸屬時間',

  belonger_time STRING COMMENT '歸屬時間',

  transfer int COMMENT '轉移人',

  transfer_time STRING COMMENT '轉移時間',

  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',

  transfer_bxg_oa_account STRING COMMENT '轉移到博學谷歸屬人OA賬號',

  transfer_bxg_belonger_name STRING COMMENT '轉移到博學谷歸屬人OA姓名',

  end_time STRING COMMENT '有效時間')

comment '客户關係表'

PARTITIONED BY(start_time STRING)

ROW FORMAT DELIMITED 

FIELDS TERMINATED BY '\t'

stored as orc

TBLPROPERTIES ('orc.compress'='ZLIB');

")

SQOOP_HOME=/usr/bin/sqoop

output=$(${SQOOP_HOME} import \

--connect jdbc:mysql://172.17.0.202:3306/scrm \

--username root \

--password 123456 \

--query 'select id,

       create_date_time,

       update_date_time,

       deleted,

       customer_id,

       first_id,

       belonger,

       belonger_name,

       initial_belonger,

       distribution_handler,

       business_scrm_department_id,

       last_visit_time,

       next_visit_time,

       origin_type,

       itcast_school_id,

       itcast_subject_id,

       intention_study_type,

       anticipat_signup_date,

       level,

       creator,

       current_creator,

       creator_name,

       origin_channel,

       comment,

       first_customer_clue_id,

       last_customer_clue_id,

       process_state,

       process_time,

       payment_state,

       payment_time,

       signup_state,

       signup_time,

       notice_state,

       notice_time,

       lock_state,

       lock_time,

       itcast_clazz_id,

       itcast_clazz_time,

       payment_url,

       payment_url_time,

       ems_student_id,

       delete_reason,

       deleter,

       deleter_name,

       delete_time,

       course_id,

       course_name,

       delete_comment,

       close_state,

       close_time,

       appeal_id,

       tenant,

       total_fee,

       belonged,

       belonged_time,

       belonger_time,

       transfer,

       transfer_time,

       follow_type,

       transfer_bxg_oa_account,

       transfer_bxg_belonger_name,

       FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time,

       date_format("9999-12-31", "%Y-%m-%d")       as end_time

from customer_relationship

where

  (

    create_date_time >= FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY),"%Y-%m-%d %H:%i:%s")

     and

    create_date_time < FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)),"%Y-%m-%d %H:%i:%s")

  )

  or

  (

    update_date_time >= FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY),"%Y-%m-%d %H:%i:%s")

    and

    update_date_time < FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)),"%Y-%m-%d %H:%i:%s")

  ) and $CONDITIONS' \

--hcatalog-database itcast_ods \

--hcatalog-table customer_relationship_update \

--hive-partition-key start_time \

--hive-partition-value ${TD_DATE} \

-m 100 \

--split-by id)

output=$(${HIVE_HOME} -S -e "

SET hive.exec.dynamic.partition=true;

SET hive.exec.dynamic.partition.mode=nonstrict;

DROP TABLE itcast_ods.customer_clue_tmp;

CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue_tmp (

  id int COMMENT 'customer_clue_id',

  create_date_time STRING COMMENT '創建時間',

  update_date_time STRING COMMENT '最後更新時間',

  deleted STRING COMMENT '是否被刪除（禁用）',

  customer_id int COMMENT '客户id',

  customer_relationship_id int COMMENT '客户關係id',

  session_id STRING COMMENT '七陌會話id',

  sid STRING COMMENT '訪客id',

  status STRING COMMENT '狀態（undeal待領取 deal 已領取 finish 已關閉 changePeer 已流轉）',

  users STRING COMMENT '所屬坐席',

  create_time STRING COMMENT '七陌創建時間',

  platform STRING COMMENT '平台來源 （pc-網站諮詢|wap-wap諮詢|sdk-app諮詢|weixin-微信諮詢）',

  s_name STRING COMMENT '用户名稱',

  seo_source STRING COMMENT '搜索來源',

  seo_keywords STRING COMMENT '關鍵字',

  ip STRING COMMENT 'IP地址',

  referrer STRING COMMENT '上級來源頁面',

  from_url STRING COMMENT '會話來源頁面',

  landing_page_url STRING COMMENT '訪客着陸頁面',

  url_title STRING COMMENT '諮詢頁面title',

  to_peer STRING COMMENT '所屬技能組',

  manual_time STRING COMMENT '人工開始時間',

  begin_time STRING COMMENT '坐席領取時間 ',

  reply_msg_count int COMMENT '客服回覆消息數',

  total_msg_count int COMMENT '消息總數',

  msg_count int COMMENT '客户發送消息數',

  comment STRING COMMENT '備註',

  finish_reason STRING COMMENT '結束類型',

  finish_user STRING COMMENT '結束坐席',

  end_time STRING COMMENT '會話結束時間',

  platform_description STRING COMMENT '客户平台信息',

  browser_name STRING COMMENT '瀏覽器名稱',

  os_info STRING COMMENT '系統名稱',

  area STRING COMMENT '區域',

  country STRING COMMENT '所在國家',

  province STRING COMMENT '省',

  city STRING COMMENT '城市',

  creator int COMMENT '創建人',

  name STRING COMMENT '客户姓名',

  idcard STRING COMMENT '身份證號',

  phone STRING COMMENT '手機號',

  itcast_school_id int COMMENT '校區Id',

  itcast_school STRING COMMENT '校區',

  itcast_subject_id int COMMENT '學科Id',

  itcast_subject STRING COMMENT '學科',

  wechat STRING COMMENT '微信',

  qq STRING COMMENT 'qq號',

  email STRING COMMENT '郵箱',

  gender STRING COMMENT '性別',

  level STRING COMMENT '客户級別',

  origin_type STRING COMMENT '數據來源渠道',

  information_way STRING COMMENT '資訊方式',

  working_years STRING COMMENT '開始工作時間',

  technical_directions STRING COMMENT '技術方向',

  customer_state STRING COMMENT '當前客户狀態',

  valid STRING COMMENT '該線索是否是網資有效線索',

  anticipat_signup_date STRING COMMENT '預計報名時間',

  clue_state STRING COMMENT '線索狀態',

  scrm_department_id int COMMENT 'SCRM內部部門id',

  superior_url STRING COMMENT '諸葛獲取上級頁面URL',

  superior_source STRING COMMENT '諸葛獲取上級頁面URL標題',

  landing_url STRING COMMENT '諸葛獲取着陸頁面URL',

  landing_source STRING COMMENT '諸葛獲取着陸頁面URL來源',

  info_url STRING COMMENT '諸葛獲取留諮頁URL',

  info_source STRING COMMENT '諸葛獲取留諮頁URL標題',

  origin_channel STRING COMMENT '投放渠道',

  course_id int COMMENT '課程編號',

  course_name STRING COMMENT '課程名稱',

  zhuge_session_id STRING COMMENT 'zhuge會話id',

  is_repeat int COMMENT '是否重複線索(手機號維度) 0:正常 1：重複',

  tenant int COMMENT '租户id',

  activity_id STRING COMMENT '活動id',

  activity_name STRING COMMENT '活動名稱',

  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',

  shunt_mode_id int COMMENT '匹配到的技能組id',

  shunt_employee_group_id int COMMENT '所屬分流員工組',

  ends_time STRING COMMENT '有效時間')

comment '客户關係表'

PARTITIONED BY(starts_time STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

stored as orc

TBLPROPERTIES ('orc.compress'='ZLIB');

insert overwrite table itcast_ods.`customer_relationship_tmp` partition (start_time)

select * from

    (

        select

          id,

          create_date_time,

          update_date_time,

          deleted,

          customer_id,

          first_id,

          belonger,

          belonger_name,

          initial_belonger,

          distribution_handler,

          business_scrm_department_id,

          last_visit_time,

          next_visit_time,

          origin_type,

          itcast_school_id,

          itcast_subject_id,

          intention_study_type,

          anticipat_signup_date,

          level,

          creator,

          current_creator,

          creator_name,

          origin_channel,

          comment,

          first_customer_clue_id,

          last_customer_clue_id,

          process_state,

          process_time,

          payment_state,

          payment_time,

          signup_state,

          signup_time,

          notice_state,

          notice_time,

          lock_state,

          lock_time,

          itcast_clazz_id,

          itcast_clazz_time,

          payment_url,

          payment_url_time,

          ems_student_id,

          delete_reason,

          deleter,

          deleter_name,

          delete_time,

          course_id,

          course_name,

          delete_comment,

          close_state,

          close_time,

          appeal_id,

          tenant,

          total_fee,

          belonged,

          belonged_time,

          belonger_time,

          transfer,

          transfer_time,

          follow_type,

          transfer_bxg_oa_account,

          transfer_bxg_belonger_name,

          '9999-12-31' end_time,

          FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as start_time

        from itcast_ods.customer_relationship_update where start_time=FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d")

        union all

        select

          rs.id,

          rs.create_date_time,

          rs.update_date_time,

          rs.deleted,

          rs.customer_id,

          rs.first_id,

          rs.belonger,

          rs.belonger_name,

          rs.initial_belonger,

          rs.distribution_handler,

          rs.business_scrm_department_id,

          rs.last_visit_time,

          rs.next_visit_time,

          rs.origin_type,

          rs.itcast_school_id,

          rs.itcast_subject_id,

          rs.intention_study_type,

          rs.anticipat_signup_date,

          rs.level,

          rs.creator,

          rs.current_creator,

          rs.creator_name,

          rs.origin_channel,

          rs.comment,

          rs.first_customer_clue_id,

          rs.last_customer_clue_id,

          rs.process_state,

          rs.process_time,

          rs.payment_state,

          rs.payment_time,

          rs.signup_state,

          rs.signup_time,

          rs.notice_state,

          rs.notice_time,

          rs.lock_state,

          rs.lock_time,

          rs.itcast_clazz_id,

          rs.itcast_clazz_time,

          rs.payment_url,

          rs.payment_url_time,

          rs.ems_student_id,

          rs.delete_reason,

          rs.deleter,

          rs.deleter_name,

          rs.delete_time,

          rs.course_id,

          rs.course_name,

          rs.delete_comment,

          rs.close_state,

          rs.close_time,

          rs.appeal_id,

          rs.tenant,

          rs.total_fee,

          rs.belonged,

          rs.belonged_time,

          rs.belonger_time,

          rs.transfer,

          rs.transfer_time,

          rs.follow_type,

          rs.transfer_bxg_oa_account,

          rs.transfer_bxg_belonger_name,

          if(up.id is null, rs.end_time, date_add(up.start_time,-1)) end_time,

          rs.start_time

        from itcast_ods.customer_relationship rs left join

            (

                select

                *

                from itcast_ods.customer_relationship_update

                where start_time=FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d")

            ) up

        on rs.id=up.id where rs.start_time >= date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP()),30) and rs.end_time='9999-12-31'

    )his

order by his.id, start_time;

INSERT OVERWRITE TABLE itcast_ods.customer_relationship partition (start_time) 

SELECT * from itcast_ods.customer_relationship_tmp;

")

DROP TABLE itcast_ods.customer_clue_update;
CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue_update (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '創建時間',
  update_date_time STRING COMMENT '最後更新時間',
  deleted STRING COMMENT '是否被刪除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户關係id',
  session_id STRING COMMENT '七陌會話id',
  sid STRING COMMENT '訪客id',
  status STRING COMMENT '狀態（undeal待領取 deal 已領取 finish 已關閉 changePeer 已流轉）',
  users STRING COMMENT '所屬坐席',
  create_time STRING COMMENT '七陌創建時間',
  platform STRING COMMENT '平台來源 （pc-網站諮詢|wap-wap諮詢|sdk-app諮詢|weixin-微信諮詢）',
  s_name STRING COMMENT '用户名稱',
  seo_source STRING COMMENT '搜索來源',
  seo_keywords STRING COMMENT '關鍵字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上級來源頁面',
  from_url STRING COMMENT '會話來源頁面',
  landing_page_url STRING COMMENT '訪客着陸頁面',
  url_title STRING COMMENT '諮詢頁面title',
  to_peer STRING COMMENT '所屬技能組',
  manual_time STRING COMMENT '人工開始時間',
  begin_time STRING COMMENT '坐席領取時間 ',
  reply_msg_count int COMMENT '客服回覆消息數',
  total_msg_count int COMMENT '消息總數',
  msg_count int COMMENT '客户發送消息數',
  comment STRING COMMENT '備註',
  finish_reason STRING COMMENT '結束類型',
  finish_user STRING COMMENT '結束坐席',
  end_time STRING COMMENT '會話結束時間',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '瀏覽器名稱',
  os_info STRING COMMENT '系統名稱',
  area STRING COMMENT '區域',
  country STRING COMMENT '所在國家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '創建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份證號',
  phone STRING COMMENT '手機號',
  itcast_school_id int COMMENT '校區Id',
  itcast_school STRING COMMENT '校區',
  itcast_subject_id int COMMENT '學科Id',
  itcast_subject STRING COMMENT '學科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq號',
  email STRING COMMENT '郵箱',
  gender STRING COMMENT '性別',
  level STRING COMMENT '客户級別',
  origin_type STRING COMMENT '數據來源渠道',
  information_way STRING COMMENT '資訊方式',
  working_years STRING COMMENT '開始工作時間',
  technical_directions STRING COMMENT '技術方向',
  customer_state STRING COMMENT '當前客户狀態',
  valid STRING COMMENT '該線索是否是網資有效線索',
  anticipat_signup_date STRING COMMENT '預計報名時間',
  clue_state STRING COMMENT '線索狀態',
  scrm_department_id int COMMENT 'SCRM內部部門id',
  superior_url STRING COMMENT '諸葛獲取上級頁面URL',
  superior_source STRING COMMENT '諸葛獲取上級頁面URL標題',
  landing_url STRING COMMENT '諸葛獲取着陸頁面URL',
  landing_source STRING COMMENT '諸葛獲取着陸頁面URL來源',
  info_url STRING COMMENT '諸葛獲取留諮頁URL',
  info_source STRING COMMENT '諸葛獲取留諮頁URL標題',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '課程編號',
  course_name STRING COMMENT '課程名稱',
  zhuge_session_id STRING COMMENT 'zhuge會話id',
  is_repeat int COMMENT '是否重複線索(手機號維度) 0:正常 1：重複',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活動id',
  activity_name STRING COMMENT '活動名稱',
  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  shunt_mode_id int COMMENT '匹配到的技能組id',
  shunt_employee_group_id int COMMENT '所屬分流員工組',
  ends_time STRING COMMENT '有效時間')
comment '客户關係表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

select id,
    create_date_time,
    update_date_time,
    deleted,
    customer_id,
    customer_relationship_id,
    session_id,
    sid,
    status, user as users, create_time, platform, s_name, seo_source, seo_keywords, ip, referrer, from_url, landing_page_url, url_title, to_peer, manual_time, begin_time, reply_msg_count, total_msg_count, msg_count, comment, finish_reason, finish_user, end_time, platform_description, browser_name, os_info, area, country, province, city, creator, name, idcard, phone, itcast_school_id, itcast_school, itcast_subject_id, itcast_subject, wechat, qq, email, gender, level, origin_type, information_way, working_years, technical_directions, customer_state, valid, anticipat_signup_date, clue_state, scrm_department_id, superior_url, superior_source, landing_url, landing_source, info_url, info_source, origin_channel, course_id, course_name, zhuge_session_id, is_repeat, tenant, activity_id, activity_name, follow_type, shunt_mode_id, shunt_employee_group_id,
    FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as starts_time, date_format("9999-12-31", "%Y-%m-%d") as ends_time
from customer_clue
where
  (
    create_date_time >= FROM_UNIXTIME(UNIX_TIMESTAMP("2019-12-04 00:00:00"),"%Y-%m-%d %H:%i:%s")
     and
    create_date_time < FROM_UNIXTIME(UNIX_TIMESTAMP("2019-12-04 23:59:59"),"%Y-%m-%d %H:%i:%s")
  )
  or
  (
    update_date_time >= FROM_UNIXTIME(UNIX_TIMESTAMP("2019-12-04 00:00:00"),"%Y-%m-%d %H:%i:%s")
    and
    update_date_time < FROM_UNIXTIME(UNIX_TIMESTAMP("2019-12-04 23:59:59"),"%Y-%m-%d %H:%i:%s")
  );

sqoop import \
--connect jdbc:mysql://172.17.0.202:3306/scrm \
--username root \
--password 123456 \
--query '
select id,
    create_date_time,
    update_date_time,
    deleted,
    customer_id,
    customer_relationship_id,
    session_id,
    sid,
    status, user as users, create_time, platform, s_name, seo_source, seo_keywords, ip, referrer, from_url, landing_page_url, url_title, to_peer, manual_time, begin_time, reply_msg_count, total_msg_count, msg_count, comment, finish_reason, finish_user, end_time, platform_description, browser_name, os_info, area, country, province, city, creator, name, idcard, phone, itcast_school_id, itcast_school, itcast_subject_id, itcast_subject, wechat, qq, email, gender, level, origin_type, information_way, working_years, technical_directions, customer_state, valid, anticipat_signup_date, clue_state, scrm_department_id, superior_url, superior_source, landing_url, landing_source, info_url, info_source, origin_channel, course_id, course_name, zhuge_session_id, is_repeat, tenant, activity_id, activity_name, follow_type, shunt_mode_id, shunt_employee_group_id,
    FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as starts_time, date_format("9999-12-31", "%Y-%m-%d") as ends_time
from customer_clue
where
  (
    create_date_time >= FROM_UNIXTIME(UNIX_TIMESTAMP("2019-12-04 00:00:00"),"%Y-%m-%d %H:%i:%s")
     and
    create_date_time < FROM_UNIXTIME(UNIX_TIMESTAMP("2019-12-04 23:59:59"),"%Y-%m-%d %H:%i:%s")
  )
  or
  (
    update_date_time >= FROM_UNIXTIME(UNIX_TIMESTAMP("2019-12-04 00:00:00"),"%Y-%m-%d %H:%i:%s")
    and
    update_date_time < FROM_UNIXTIME(UNIX_TIMESTAMP("2019-12-04 23:59:59"),"%Y-%m-%d %H:%i:%s")
  )
and $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_clue_update \
--hive-partition-key starts_time \
--hive-partition-value 2019-12-04 \
-m 100 \
--split-by id

DROP TABLE itcast_ods.customer_clue_tmp;
CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue_tmp (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '創建時間',
  update_date_time STRING COMMENT '最後更新時間',
  deleted STRING COMMENT '是否被刪除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户關係id',
  session_id STRING COMMENT '七陌會話id',
  sid STRING COMMENT '訪客id',
  status STRING COMMENT '狀態（undeal待領取 deal 已領取 finish 已關閉 changePeer 已流轉）',
  users STRING COMMENT '所屬坐席',
  create_time STRING COMMENT '七陌創建時間',
  platform STRING COMMENT '平台來源 （pc-網站諮詢|wap-wap諮詢|sdk-app諮詢|weixin-微信諮詢）',
  s_name STRING COMMENT '用户名稱',
  seo_source STRING COMMENT '搜索來源',
  seo_keywords STRING COMMENT '關鍵字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上級來源頁面',
  from_url STRING COMMENT '會話來源頁面',
  landing_page_url STRING COMMENT '訪客着陸頁面',
  url_title STRING COMMENT '諮詢頁面title',
  to_peer STRING COMMENT '所屬技能組',
  manual_time STRING COMMENT '人工開始時間',
  begin_time STRING COMMENT '坐席領取時間 ',
  reply_msg_count int COMMENT '客服回覆消息數',
  total_msg_count int COMMENT '消息總數',
  msg_count int COMMENT '客户發送消息數',
  comment STRING COMMENT '備註',
  finish_reason STRING COMMENT '結束類型',
  finish_user STRING COMMENT '結束坐席',
  end_time STRING COMMENT '會話結束時間',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '瀏覽器名稱',
  os_info STRING COMMENT '系統名稱',
  area STRING COMMENT '區域',
  country STRING COMMENT '所在國家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '創建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份證號',
  phone STRING COMMENT '手機號',
  itcast_school_id int COMMENT '校區Id',
  itcast_school STRING COMMENT '校區',
  itcast_subject_id int COMMENT '學科Id',
  itcast_subject STRING COMMENT '學科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq號',
  email STRING COMMENT '郵箱',
  gender STRING COMMENT '性別',
  level STRING COMMENT '客户級別',
  origin_type STRING COMMENT '數據來源渠道',
  information_way STRING COMMENT '資訊方式',
  working_years STRING COMMENT '開始工作時間',
  technical_directions STRING COMMENT '技術方向',
  customer_state STRING COMMENT '當前客户狀態',
  valid STRING COMMENT '該線索是否是網資有效線索',
  anticipat_signup_date STRING COMMENT '預計報名時間',
  clue_state STRING COMMENT '線索狀態',
  scrm_department_id int COMMENT 'SCRM內部部門id',
  superior_url STRING COMMENT '諸葛獲取上級頁面URL',
  superior_source STRING COMMENT '諸葛獲取上級頁面URL標題',
  landing_url STRING COMMENT '諸葛獲取着陸頁面URL',
  landing_source STRING COMMENT '諸葛獲取着陸頁面URL來源',
  info_url STRING COMMENT '諸葛獲取留諮頁URL',
  info_source STRING COMMENT '諸葛獲取留諮頁URL標題',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '課程編號',
  course_name STRING COMMENT '課程名稱',
  zhuge_session_id STRING COMMENT 'zhuge會話id',
  is_repeat int COMMENT '是否重複線索(手機號維度) 0:正常 1：重複',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活動id',
  activity_name STRING COMMENT '活動名稱',
  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  shunt_mode_id int COMMENT '匹配到的技能組id',
  shunt_employee_group_id int COMMENT '所屬分流員工組',
  ends_time STRING COMMENT '有效時間')
comment '客户關係表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

insert overwrite table itcast_ods.customer_clue_tmp partition (starts_time)
select * from 
(
    select 
      id,
      create_date_time,
      update_date_time,
      deleted,
      customer_id,
      customer_relationship_id,
      session_id,
      sid,
      status,
      users,
      create_time,
      platform,
      s_name,
      seo_source,
      seo_keywords,
      ip,
      referrer,
      from_url,
      landing_page_url,
      url_title,
      to_peer,
      manual_time,
      begin_time,
      reply_msg_count,
      total_msg_count,
      msg_count,
      comment,
      finish_reason,
      finish_user,
      end_time,
      platform_description,
      browser_name,
      os_info,
      area,
      country,
      province,
      city,
      creator,
      name,
      idcard,
      phone,
      itcast_school_id,
      itcast_school,
      itcast_subject_id,
      itcast_subject,
      wechat,
      qq,
      email,
      gender,
      level,
      origin_type,
      information_way,
      working_years,
      technical_directions,
      customer_state,
      valid,
      anticipat_signup_date,
      clue_state,
      scrm_department_id,
      superior_url,
      superior_source,
      landing_url,
      landing_source,
      info_url,
      info_source,
      origin_channel,
      course_id,
      course_name,
      zhuge_session_id,
      is_repeat,
      tenant,
      activity_id,
      activity_name,
      follow_type,
      shunt_mode_id,
      shunt_employee_group_id,
      '9999-12-31' ends_time,
      FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as starts_time
    from itcast_ods.customer_clue_update where starts_time=FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d")

    union all 
    select
      rs.id,
      rs.create_date_time,
      rs.update_date_time,
      rs.deleted,
      rs.customer_id,
      rs.customer_relationship_id,
      rs.session_id,
      rs.sid,
      rs.status,
      rs.users,
      rs.create_time,
      rs.platform,
      rs.s_name,
      rs.seo_source,
      rs.seo_keywords,
      rs.ip,
      rs.referrer,
      rs.from_url,
      rs.landing_page_url,
      rs.url_title,
      rs.to_peer,
      rs.manual_time,
      rs.begin_time,
      rs.reply_msg_count,
      rs.total_msg_count,
      rs.msg_count,
      rs.comment,
      rs.finish_reason,
      rs.finish_user,
      rs.end_time,
      rs.platform_description,
      rs.browser_name,
      rs.os_info,
      rs.area,
      rs.country,
      rs.province,
      rs.city,
      rs.creator,
      rs.name,
      rs.idcard,
      rs.phone,
      rs.itcast_school_id,
      rs.itcast_school,
      rs.itcast_subject_id,
      rs.itcast_subject,
      rs.wechat,
      rs.qq,
      rs.email,
      rs.gender,
      rs.level,
      rs.origin_type,
      rs.information_way,
      rs.working_years,
      rs.technical_directions,
      rs.customer_state,
      rs.valid,
      rs.anticipat_signup_date,
      rs.clue_state,
      rs.scrm_department_id,
      rs.superior_url,
      rs.superior_source,
      rs.landing_url,
      rs.landing_source,
      rs.info_url,
      rs.info_source,
      rs.origin_channel,
      rs.course_id,
      rs.course_name,
      rs.zhuge_session_id,
      rs.is_repeat,
      rs.tenant,
      rs.activity_id,
      rs.activity_name,
      rs.follow_type,
      rs.shunt_mode_id,
      rs.shunt_employee_group_id,
      if(up.id is null or rs.end_time<'9999-12-31', rs.ends_time, date_add(up.starts_time,-1)) ends_time,
      rs.starts_time
    from itcast_ods.customer_clue rs left join 
        (
            select
            *
            from itcast_ods.customer_clue_update
            where starts_time=FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d")
        ) up
    on rs.id=up.id  where rs.starts_time >= date_add(FROM_UNIXTIME(UNIX_TIMESTAMP()),-30)
)his 
order by his.id, starts_time;

INSERT OVERWRITE TABLE itcast_ods.customer_clue partition (starts_time) 

SELECT * from itcast_ods.customer_clue_tmp;

#! /bin/bash
HIVE_HOME=/usr/bin/hive
if [[ $1 == "" ]];
then
    TD_DATE=`date -d ''1 days ago'' "+%Y-%m-%d"`
else
    TD_DATE=$1
fi
output=$(${HIVE_HOME} -S -e "
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
DROP TABLE itcast_ods.customer_clue_update;
CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue_update (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '創建時間',
  update_date_time STRING COMMENT '最後更新時間',
  deleted STRING COMMENT '是否被刪除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户關係id',
  session_id STRING COMMENT '七陌會話id',
  sid STRING COMMENT '訪客id',
  status STRING COMMENT '狀態（undeal待領取 deal 已領取 finish 已關閉 changePeer 已流轉）',
  users STRING COMMENT '所屬坐席',
  create_time STRING COMMENT '七陌創建時間',
  platform STRING COMMENT '平台來源 （pc-網站諮詢|wap-wap諮詢|sdk-app諮詢|weixin-微信諮詢）',
  s_name STRING COMMENT '用户名稱',
  seo_source STRING COMMENT '搜索來源',
  seo_keywords STRING COMMENT '關鍵字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上級來源頁面',
  from_url STRING COMMENT '會話來源頁面',
  landing_page_url STRING COMMENT '訪客着陸頁面',
  url_title STRING COMMENT '諮詢頁面title',
  to_peer STRING COMMENT '所屬技能組',
  manual_time STRING COMMENT '人工開始時間',
  begin_time STRING COMMENT '坐席領取時間 ',
  reply_msg_count int COMMENT '客服回覆消息數',
  total_msg_count int COMMENT '消息總數',
  msg_count int COMMENT '客户發送消息數',
  comment STRING COMMENT '備註',
  finish_reason STRING COMMENT '結束類型',
  finish_user STRING COMMENT '結束坐席',
  end_time STRING COMMENT '會話結束時間',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '瀏覽器名稱',
  os_info STRING COMMENT '系統名稱',
  area STRING COMMENT '區域',
  country STRING COMMENT '所在國家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '創建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份證號',
  phone STRING COMMENT '手機號',
  itcast_school_id int COMMENT '校區Id',
  itcast_school STRING COMMENT '校區',
  itcast_subject_id int COMMENT '學科Id',
  itcast_subject STRING COMMENT '學科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq號',
  email STRING COMMENT '郵箱',
  gender STRING COMMENT '性別',
  level STRING COMMENT '客户級別',
  origin_type STRING COMMENT '數據來源渠道',
  information_way STRING COMMENT '資訊方式',
  working_years STRING COMMENT '開始工作時間',
  technical_directions STRING COMMENT '技術方向',
  customer_state STRING COMMENT '當前客户狀態',
  valid STRING COMMENT '該線索是否是網資有效線索',
  anticipat_signup_date STRING COMMENT '預計報名時間',
  clue_state STRING COMMENT '線索狀態',
  scrm_department_id int COMMENT 'SCRM內部部門id',
  superior_url STRING COMMENT '諸葛獲取上級頁面URL',
  superior_source STRING COMMENT '諸葛獲取上級頁面URL標題',
  landing_url STRING COMMENT '諸葛獲取着陸頁面URL',
  landing_source STRING COMMENT '諸葛獲取着陸頁面URL來源',
  info_url STRING COMMENT '諸葛獲取留諮頁URL',
  info_source STRING COMMENT '諸葛獲取留諮頁URL標題',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '課程編號',
  course_name STRING COMMENT '課程名稱',
  zhuge_session_id STRING COMMENT 'zhuge會話id',
  is_repeat int COMMENT '是否重複線索(手機號維度) 0:正常 1：重複',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活動id',
  activity_name STRING COMMENT '活動名稱',
  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  shunt_mode_id int COMMENT '匹配到的技能組id',
  shunt_employee_group_id int COMMENT '所屬分流員工組',
  ends_time STRING COMMENT '有效時間')
comment '客户關係表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');
")

SQOOP_HOME=/usr/bin/sqoop
output=$(${SQOOP_HOME} import \
--connect jdbc:mysql://172.17.0.202:3306/scrm \
--username root \
--password 123456 \
--query '
select id,
    create_date_time,
    update_date_time,
    deleted,
    customer_id,
    customer_relationship_id,
    session_id,
    sid,
    status, user, create_time, platform, s_name, seo_source, seo_keywords, ip, referrer, from_url, landing_page_url, url_title, to_peer, manual_time, begin_time, reply_msg_count, total_msg_count, msg_count, comment, finish_reason, finish_user, end_time, platform_description, browser_name, os_info, area, country, province, city, creator, name, idcard, phone, itcast_school_id, itcast_school, itcast_subject_id, itcast_subject, wechat, qq, email, gender, level, origin_type, information_way, working_years, technical_directions, customer_state, valid, anticipat_signup_date, clue_state, scrm_department_id, superior_url, superior_source, landing_url, landing_source, info_url, info_source, origin_channel, course_id, course_name, zhuge_session_id, is_repeat, tenant, activity_id, activity_name, follow_type, shunt_mode_id, shunt_employee_group_id,
    FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as starts_time, date_format("9999-12-31", "%Y-%m-%d") as ends_time
from customer_clue
where
  (
    create_date_time >= FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY),"%Y-%m-%d %H:%i:%s")
     and
    create_date_time < FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)),"%Y-%m-%d %H:%i:%s")
  )
  or
  (
    update_date_time >= FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY),"%Y-%m-%d %H:%i:%s")
    and
    update_date_time < FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)),"%Y-%m-%d %H:%i:%s")
  ) and $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_clue_update \
--hive-partition-key starts_time \
--hive-partition-value ${TD_DATE} \
-m 100 \
--split-by id)

output=$(${HIVE_HOME} -S -e "
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

DROP TABLE itcast_ods.customer_clue_tmp;
CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue_tmp (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '創建時間',
  update_date_time STRING COMMENT '最後更新時間',
  deleted STRING COMMENT '是否被刪除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户關係id',
  session_id STRING COMMENT '七陌會話id',
  sid STRING COMMENT '訪客id',
  status STRING COMMENT '狀態（undeal待領取 deal 已領取 finish 已關閉 changePeer 已流轉）',
  users STRING COMMENT '所屬坐席',
  create_time STRING COMMENT '七陌創建時間',
  platform STRING COMMENT '平台來源 （pc-網站諮詢|wap-wap諮詢|sdk-app諮詢|weixin-微信諮詢）',
  s_name STRING COMMENT '用户名稱',
  seo_source STRING COMMENT '搜索來源',
  seo_keywords STRING COMMENT '關鍵字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上級來源頁面',
  from_url STRING COMMENT '會話來源頁面',
  landing_page_url STRING COMMENT '訪客着陸頁面',
  url_title STRING COMMENT '諮詢頁面title',
  to_peer STRING COMMENT '所屬技能組',
  manual_time STRING COMMENT '人工開始時間',
  begin_time STRING COMMENT '坐席領取時間 ',
  reply_msg_count int COMMENT '客服回覆消息數',
  total_msg_count int COMMENT '消息總數',
  msg_count int COMMENT '客户發送消息數',
  comment STRING COMMENT '備註',
  finish_reason STRING COMMENT '結束類型',
  finish_user STRING COMMENT '結束坐席',
  end_time STRING COMMENT '會話結束時間',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '瀏覽器名稱',
  os_info STRING COMMENT '系統名稱',
  area STRING COMMENT '區域',
  country STRING COMMENT '所在國家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '創建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份證號',
  phone STRING COMMENT '手機號',
  itcast_school_id int COMMENT '校區Id',
  itcast_school STRING COMMENT '校區',
  itcast_subject_id int COMMENT '學科Id',
  itcast_subject STRING COMMENT '學科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq號',
  email STRING COMMENT '郵箱',
  gender STRING COMMENT '性別',
  level STRING COMMENT '客户級別',
  origin_type STRING COMMENT '數據來源渠道',
  information_way STRING COMMENT '資訊方式',
  working_years STRING COMMENT '開始工作時間',
  technical_directions STRING COMMENT '技術方向',
  customer_state STRING COMMENT '當前客户狀態',
  valid STRING COMMENT '該線索是否是網資有效線索',
  anticipat_signup_date STRING COMMENT '預計報名時間',
  clue_state STRING COMMENT '線索狀態',
  scrm_department_id int COMMENT 'SCRM內部部門id',
  superior_url STRING COMMENT '諸葛獲取上級頁面URL',
  superior_source STRING COMMENT '諸葛獲取上級頁面URL標題',
  landing_url STRING COMMENT '諸葛獲取着陸頁面URL',
  landing_source STRING COMMENT '諸葛獲取着陸頁面URL來源',
  info_url STRING COMMENT '諸葛獲取留諮頁URL',
  info_source STRING COMMENT '諸葛獲取留諮頁URL標題',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '課程編號',
  course_name STRING COMMENT '課程名稱',
  zhuge_session_id STRING COMMENT 'zhuge會話id',
  is_repeat int COMMENT '是否重複線索(手機號維度) 0:正常 1：重複',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活動id',
  activity_name STRING COMMENT '活動名稱',
  follow_type int COMMENT '分配類型，0-自動分配，1-手動分配，2-自動轉移，3-手動單個轉移，4-手動批量轉移，5-公海領取',
  shunt_mode_id int COMMENT '匹配到的技能組id',
  shunt_employee_group_id int COMMENT '所屬分流員工組',
  ends_time STRING COMMENT '有效時間')
comment '客户關係表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

insert overwrite table itcast_ods.customer_clue_tmp partition (starts_time)
select * from 
(
    select 
      id,
      create_date_time,
      update_date_time,
      deleted,
      customer_id,
      customer_relationship_id,
      session_id,
      sid,
      status,
      user,
      create_time,
      platform,
      s_name,
      seo_source,
      seo_keywords,
      ip,
      referrer,
      from_url,
      landing_page_url,
      url_title,
      to_peer,
      manual_time,
      begin_time,
      reply_msg_count,
      total_msg_count,
      msg_count,
      comment,
      finish_reason,
      finish_user,
      ends_time,
      platform_description,
      browser_name,
      os_info,
      area,
      country,
      province,
      city,
      creator,
      name,
      idcard,
      phone,
      itcast_school_id,
      itcast_school,
      itcast_subject_id,
      itcast_subject,
      wechat,
      qq,
      email,
      gender,
      level,
      origin_type,
      information_way,
      working_years,
      technical_directions,
      customer_state,
      valid,
      anticipat_signup_date,
      clue_state,
      scrm_department_id,
      superior_url,
      superior_source,
      landing_url,
      landing_source,
      info_url,
      info_source,
      origin_channel,
      course_id,
      course_name,
      zhuge_session_id,
      is_repeat,
      tenant,
      activity_id,
      activity_name,
      follow_type,
      shunt_mode_id,
      shunt_employee_group_id,
      '9999-12-31' ends_time,
      FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d") as starts_time
    from itcast_ods.customer_clue_update where starts_time=FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d")

    union all 
    select
      rs.id,
      rs.create_date_time,
      rs.update_date_time,
      rs.deleted,
      rs.customer_id,
      rs.customer_relationship_id,
      rs.session_id,
      rs.sid,
      rs.status,
      rs.user,
      rs.create_time,
      rs.platform,
      rs.s_name,
      rs.seo_source,
      rs.seo_keywords,
      rs.ip,
      rs.referrer,
      rs.from_url,
      rs.landing_page_url,
      rs.url_title,
      rs.to_peer,
      rs.manual_time,
      rs.begin_time,
      rs.reply_msg_count,
      rs.total_msg_count,
      rs.msg_count,
      rs.comment,
      rs.finish_reason,
      rs.finish_user,
      rs.ends_time,
      rs.platform_description,
      rs.browser_name,
      rs.os_info,
      rs.area,
      rs.country,
      rs.province,
      rs.city,
      rs.creator,
      rs.name,
      rs.idcard,
      rs.phone,
      rs.itcast_school_id,
      rs.itcast_school,
      rs.itcast_subject_id,
      rs.itcast_subject,
      rs.wechat,
      rs.qq,
      rs.email,
      rs.gender,
      rs.level,
      rs.origin_type,
      rs.information_way,
      rs.working_years,
      rs.technical_directions,
      rs.customer_state,
      rs.valid,
      rs.anticipat_signup_date,
      rs.clue_state,
      rs.scrm_department_id,
      rs.superior_url,
      rs.superior_source,
      rs.landing_url,
      rs.landing_source,
      rs.info_url,
      rs.info_source,
      rs.origin_channel,
      rs.course_id,
      rs.course_name,
      rs.zhuge_session_id,
      rs.is_repeat,
      rs.tenant,
      rs.activity_id,
      rs.activity_name,
      rs.follow_type,
      rs.shunt_mode_id,
      rs.shunt_employee_group_id,
      if(up.id is null, rs.ends_time, date_add(up.starts_time,-1)) ends_time,
      rs.starts_time
    from itcast_ods.customer_clue rs left join 
        (
            select
            *
            from itcast_ods.customer_clue_update
            where starts_time=FROM_UNIXTIME(unix_timestamp(), "%Y-%m-%d")
        ) up
    on rs.id=up.id  where rs.starts_time >= date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP()),30) and rs.ends_time='9999-12-31'
)his 
order by his.id, starts_time;

INSERT OVERWRITE TABLE itcast_ods.customer_clue partition (starts_time) 
SELECT * from itcast_ods.customer_clue_tmp;
")

--分區
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive壓縮
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--寫入時壓縮生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

insert into table itcast_dwd.itcast_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    rs.id as rid,
    rs.customer_id,
    rs.create_date_time,
    if((rs.itcast_school_id is null) or (rs.itcast_school_id = 0), -1, rs.itcast_school_id) as itcast_school_id,
    rs.deleted,
    rs.origin_type,
    if((rs.itcast_subject_id is null) or (rs.itcast_subject_id = 0), -1, rs.itcast_subject_id) as itcast_subject_id,
    substr(rs.create_date_time, 12, 2) hourinfo,
    if(rs.origin_type='NETSERVICE', '1', if(rs.origin_type='PRESIGNUP', '1', '0')) as origin_type_stat,
    substr(rs.create_date_time, 1, 4) yearinfo,
    substr(rs.create_date_time, 6, 2) monthinfo,
    substr(rs.create_date_time, 9, 2) dayinfo
from itcast_ods.customer_relationship rs
where rs.deleted = 0
  and start_time = '${Last_DATE}'--2019-11-01
  and rs.end_time = '9999-12-31';

#! /bin/bash
SQOOP_HOME=/usr/bin/sqoop

#昨天
Last_DATE=$(date -d "-1 day" +%Y-%m-%d)


${HIVE_HOME} -S -e "
--分區
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive壓縮
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--寫入時壓縮生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

insert into table itcast_dwd.itcast_intention_dwd partition (yearinfo,monthinfo,dayinfo)
select
    rs.id as rid,
    rs.customer_id,
    rs.create_date_time,
    if((rs.itcast_school_id is null) or (rs.itcast_school_id = 0), -1, rs.itcast_school_id) as itcast_school_id,
    rs.deleted,
    rs.origin_type,
    if((rs.itcast_subject_id is null) or (rs.itcast_subject_id = 0), -1, rs.itcast_subject_id) as itcast_subject_id,
    substr(rs.create_date_time, 12, 2) hourinfo,
    if(rs.origin_type='NETSERVICE', '1', if(rs.origin_type='PRESIGNUP', '1', '0')) as origin_type_stat,
    substr(rs.create_date_time, 1, 4) yearinfo,
    substr(rs.create_date_time, 6, 2) monthinfo,
    substr(rs.create_date_time, 9, 2) dayinfo
from itcast_ods.customer_relationship rs
where rs.deleted = 0
  and substr(rs.start_time, 1, 10) = '${Last_DATE}'--2019-11-01
  and rs.end_time = '9999-12-31';
"

insert overwrite table itcast_dwm.itcast_intention_dwm partition (yearinfo,monthinfo,dayinfo)
select
    dwd.customer_id,
    dwd.create_date_time,
    cus.area,
    dwd.itcast_school_id,
    sch.name as itcast_school_name,
    dwd.deleted,
    dwd.origin_type,
    dwd.itcast_subject_id,
    sub.name as itcast_subject_name,
    dwd.hourinfo,
    dwd.origin_type_stat,
    if(clue.clue_state='VALID_NEW_CLUES', '1', if(clue.clue_state='VALID_PUBLIC_NEW_CLUE', '0', '-1')) as clue_state_stat,
    e.department_id as tdepart_id,
    dept.name as tdepart_name,
    dwd.yearinfo,
    dwd.monthinfo,
    dwd.dayinfo
from itcast_dwd.itcast_intention_dwd dwd
left join itcast_ods.customer_clue clue on clue.customer_relationship_id=dwd.rid
left join itcast_dimen.customer cus on dwd.customer_id = cus.id
left join itcast_dimen.employee e on dwd.creator = e.id
left join itcast_dimen.scrm_department dept on e.department_id = dept.id
left join itcast_dimen.itcast_subject sub on dwd.itcast_subject_id = sub.id
left join itcast_dimen.itcast_school sch on dwd.itcast_school_id = sch.id
where concat_ws('-',dwd.yearinfo,dwd.monthinfo,dwd.dayinfo) >= '${Last_Month_DATE}'--2019-11-01;

#! /bin/bash
SQOOP_HOME=/usr/bin/sqoop

#上個月1日
Last_Month_DATE=$(date -d "-1 month" +%Y-%m-01)


${HIVE_HOME} -S -e "
--分區
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive壓縮
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--寫入時壓縮生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

insert into table itcast_dwm.itcast_intention_dwm partition (yearinfo,monthinfo,dayinfo)
select
    dwd.customer_id,
    dwd.create_date_time,
    cus.area,
    dwd.itcast_school_id,
    sch.name as itcast_school_name,
    dwd.deleted,
    dwd.origin_type,
    dwd.itcast_subject_id,
    sub.name as itcast_subject_name,
    dwd.hourinfo,
    dwd.origin_type_stat,
    if(clue.clue_state='VALID_NEW_CLUES', '1', if(clue.clue_state='VALID_PUBLIC_NEW_CLUE', '0', '-1')) as clue_state_stat,
    e.department_id as tdepart_id,
    dept.name as tdepart_name,
    dwd.yearinfo,
    dwd.monthinfo,
    dwd.dayinfo
from itcast_dwd.itcast_intention_dwd dwd
left join itcast_ods.customer_clue clue on clue.customer_relationship_id=dwd.rid
left join itcast_dimen.customer cus on dwd.customer_id = cus.id
left join itcast_dimen.employee e on dwd.creator = e.id
left join itcast_dimen.scrm_department dept on e.department_id = dept.id
left join itcast_dimen.itcast_subject sub on dwd.itcast_subject_id = sub.id
left join itcast_dimen.itcast_school sch on dwd.itcast_school_id = sch.id
where concat_ws('-',dwd.yearinfo,dwd.monthinfo,dwd.dayinfo) >= '${Last_Month_DATE}'--2019-11-01;
"

--總意向量分組（按照時間和常駐類型統計）
--小時
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
    '1' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
where concat_ws('-',dwm.yearinfo,dwm.monthinfo,dwm.dayinfo) >= '${Last_Month_DATE}'--2011-08-01
group by yearinfo, monthinfo, dayinfo, hourinfo, origin_type_stat, clue_state_stat;
--天
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
    '1' as grouptype,
    '2' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
where concat_ws('-',dwm.yearinfo,dwm.monthinfo,dwm.dayinfo) >= '${Last_Month_DATE}'--2011-08-19
group by yearinfo, monthinfo, dayinfo, origin_type_stat, clue_state_stat;
--月
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo) as time_str,
    '1' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
where concat_ws('-',dwm.yearinfo,dwm.monthinfo) >= '${V_Month}'--2011-08
group by yearinfo, monthinfo, origin_type_stat, clue_state_stat;
--年
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    '-1' as area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo) as time_str,
    '1' as grouptype,
    '1' as time_type,
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
where dwm.yearinfo >= '${V_Year}'--2011
group by yearinfo, origin_type_stat, clue_state_stat;

--地區分組（按照地區、時間和常駐類型統計）
--小時
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
    '2' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
where concat_ws('-',dwm.yearinfo,dwm.monthinfo,dwm.dayinfo) >= '${Last_Month_DATE}'--2011-08-19
group by area, yearinfo, monthinfo, dayinfo, hourinfo, origin_type_stat, clue_state_stat;
--天
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
    '2' as grouptype,
    '2' as time_type,
    yearinfo,
    monthinfo,
    dayinfo
from itcast_dwm.itcast_intention_dwm dwm
where concat_ws('-',dwm.yearinfo,dwm.monthinfo,dwm.dayinfo) >= '${Last_Month_DATE}'--2011-08-19
group by area, yearinfo, monthinfo, dayinfo, origin_type_stat, clue_state_stat;
--月
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo,'-',monthinfo) as time_str,
    '1' as grouptype,
    '1' as time_type,
    yearinfo,
    monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
where concat_ws('-',dwm.yearinfo,dwm.monthinfo) >= '${V_Month}'--2011-08
group by area, yearinfo, monthinfo, origin_type_stat, clue_state_stat;
--年
insert into itcast_dws.itcast_intention_dws partition (yearinfo, monthinfo, dayinfo)
select
    count(distinct customer_id) as customer_total,
    area,
    '-1' itcast_school_id,
    '-1' as itcast_school_name,
    '-1' as origin_type,
    '-1' as itcast_subject_id,
    '-1' as itcast_subject_name,
    '-1' as hourinfo,
    origin_type_stat,
    clue_state_stat,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    concat(yearinfo) as time_str,
    '2' as grouptype,
    '1' as time_type,
    yearinfo,
    '-1' as monthinfo,
    '-1' as dayinfo
from itcast_dwm.itcast_intention_dwm dwm
where dwm.yearinfo >= '${V_Year}'--2011
group by area, yearinfo, origin_type_stat, clue_state_stat;

#! /bin/bash

#上個月1日

Last_Month_DATE=$(date -d "$(date +%Y%m)01 last month" +%Y-%m-01)

#根據TD_DATE計算年季度月日

V_PARYEAR=`date --date="$Last_Month_DATE" +%Y`

V_PARMONTH=`date --date="$Last_Month_DATE" +%m`

V_PARDAY=`date --date="$Last_Month_DATE" +%d`

#獲取季度，-m為不帶0，比如7，而不是07

V_month_for_quarter=`date --date="$Last_Month_DATE" +%-m`