网站服务器宽带,艺术设计网,新手做网站起步教程,标题制作网站ods层
主要在ods干的事#xff0c;就是根据需求将所需要的表的数据从mysql中导入到ods层中#xff0c;所以需要在hive中建立ods层的表#xff0c;并且在导入的时候需要根据需求过滤一下空数据#xff0c;并在建表时加入一个dt字段记录导入的时间
ods层建表语句
1#x…ods层
主要在ods干的事就是根据需求将所需要的表的数据从mysql中导入到ods层中所以需要在hive中建立ods层的表并且在导入的时候需要根据需求过滤一下空数据并在建表时加入一个dt字段记录导入的时间
ods层建表语句
1edu_ods层建立ods_customer_relationship
drop table edu_ods.ods_customer_relationship;
CREATE TABLE edu_ods.ods_customer_relationship (id bigint ,create_date_time string ,update_date_time string COMMENT 最后更新时间,deleted bigint COMMENT 是否被删除禁用,customer_id bigint COMMENT 所属客户id,first_id bigint COMMENT 第一条客户关系id,belonger bigint COMMENT 归属人,belonger_name string COMMENT 归属人姓名,initial_belonger bigint COMMENT 初始归属人,distribution_handler bigint COMMENT 分配处理人,business_scrm_department_id bigint COMMENT 归属部门,last_visit_time string COMMENT 最后回访时间,next_visit_time string COMMENT 下次回访时间,origin_type string COMMENT 数据来源,itcast_school_id bigint COMMENT 校区Id,itcast_subject_id bigint COMMENT 学科Id,intention_study_type string COMMENT 意向学习方式,anticipat_signup_date string COMMENT 预计报名时间,level string COMMENT 客户级别,creator bigint COMMENT 创建人,current_creator bigint COMMENT 当前创建人初始创建人当在公海拉回时为 拉回人,creator_name string COMMENT 创建者姓名,origin_channel string COMMENT 来源渠道,comment string COMMENT 备注,first_customer_clue_id bigint COMMENT 第一条线索id,last_customer_clue_id bigint 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 bigint COMMENT 锁定状态,lock_time string COMMENT 锁定状态修改时间,itcast_clazz_id bigint COMMENT 所属ems班级id,itcast_clazz_time string COMMENT 报班时间,payment_url string COMMENT 付款链接,payment_url_time string COMMENT 支付链接生成时间,ems_student_id bigint COMMENT ems的学生id,delete_reason string COMMENT 删除原因,deleter bigint COMMENT 删除人,deleter_name string COMMENT 删除人姓名,delete_time string COMMENT 删除时间,course_id bigint COMMENT 课程ID,course_name string COMMENT 课程名称,delete_comment string COMMENT 删除原因说明,close_state string COMMENT 关闭装填,close_time string COMMENT 关闭状态变动时间,appeal_id bigint COMMENT 申诉id,tenant bigint COMMENT 租户,total_fee decimal(19,0) COMMENT 报名费总金额,belonged bigint COMMENT 小周期归属人,belonged_time string COMMENT 归属时间,belonger_time string COMMENT 归属时间,transfer bigint COMMENT 转移人,transfer_time string COMMENT 转移时间,follow_type bigint COMMENT 分配类型0-自动分配1-手动分配2-自动转移3-手动单个转移4-手动批量转移5-公海领取,transfer_bxg_oa_account string COMMENT 转移到博学谷归属人OA账号,transfer_bxg_belonger_name string COMMENT 转移到博学谷归属人OA姓名
) COMMENT 客户意向表
partitioned by (dt string)
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressZLIB);2edu_ods层建立ods_customer_clue
drop table edu_ods.ods_customer_clue;
CREATE TABLE edu_ods.ods_customer_clue (id bigint ,create_date_time string COMMENT 创建时间,update_date_time string COMMENT 最后更新时间,deleted bigint COMMENT 是否被删除禁用,customer_id bigint COMMENT 客户id,customer_relationship_id bigint COMMENT 客户关系id,session_id string COMMENT 七陌会话id,sid string COMMENT 访客id,status string COMMENT 状态undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转,user 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 bigint COMMENT 客服回复消息数,total_msg_count bigint COMMENT 消息总数,msg_count bigint 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 bigint COMMENT 创建人,name string COMMENT 客户姓名,idcard string COMMENT 身份证号,phone string COMMENT 手机号,itcast_school_id bigint COMMENT 校区Id,itcast_school string COMMENT 校区,itcast_subject_id bigint 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 bigint COMMENT 该线索是否是网资有效线索,anticipat_signup_date string COMMENT 预计报名时间,clue_state string COMMENT 线索状态,scrm_department_id bigint 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 bigint,course_name string ,zhuge_session_id string,is_repeat bigint COMMENT 是否重复线索(手机号维度) 0:正常 1重复,tenant bigint COMMENT 租户id,activity_id string COMMENT 活动id,activity_name string COMMENT 活动名称,follow_type bigint COMMENT 分配类型0-自动分配1-手动分配2-自动转移3-手动单个转移4-手动批量转移5-公海领取,shunt_mode_id bigint COMMENT 匹配到的技能组id,shunt_employee_group_id bigint COMMENT 所属分流员工组
) COMMENT 客户线索表
partitioned by (dt string)
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressZLIB);3,edu_ods层建立ods_employee
drop table edu_ods.ods_employee;
create table ods_employee
(id bigint ,email string comment 公司邮箱OA登录账号,real_name string comment 员工的真实姓名,phone string comment 手机号目前还没有使用隐私问题OA接口没有提供这个属性,department_id string comment OA中的部门编号有负值,department_name string comment OA中的部门名,remote_login bigint comment 员工是否可以远程登录,job_number string comment 员工工号,cross_school bigint comment 是否有跨校区权限,last_login_date string comment 最后登录日期,creator bigint comment 创建人,create_date_time string comment 创建时间,update_date_time string comment 最后更新时间,deleted bigint comment 是否被删除禁用,scrm_department_id bigint comment SCRM内部部门id,leave_office bigint comment 离职状态,leave_office_time string comment 离职时间,reinstated_time string comment 复职时间,superior_leaders_id bigint comment 上级领导ID,tdepart_id bigint comment 直属部门,tenant bigint,ems_user_name string
)comment 员工信息表
partitioned by (dt string)
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressZLIB);4,edu_ods层建立ods_scrm_department
drop table edu_ods.ods_scrm_department;
CREATE TABLE edu_ods.ods_scrm_department (id bigint COMMENT 部门id,name string COMMENT 部门名称,parent_id bigint COMMENT 父部门id,create_date_time string COMMENT 创建时间,update_date_time string COMMENT 更新时间,deleted bigint COMMENT 删除标志,id_path string COMMENT 编码全路径,tdepart_code bigint COMMENT 直属部门,creator string COMMENT 创建者,depart_level bigint COMMENT 部门层级,depart_sign bigint COMMENT 部门标志暂时默认1,depart_line bigint COMMENT 业务线存储业务线编码,depart_sort bigint COMMENT 排序字段,disable_flag bigint COMMENT 禁用标志,tenant bigint
) comment 部门表
partitioned by (dt string)
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressZLIB);5edu_ods层建立ods_customer_appeal
drop table edu_ods.ods_customer_appeal;
CREATE TABLE edu_ods.ods_customer_appeal (id bigint COMMENT 主键,customer_relationship_first_id bigint COMMENT 第一条客户关系id,employee_id bigint COMMENT 申诉人,employee_name string COMMENT 申诉人姓名,employee_department_id bigint COMMENT 申诉人部门,employee_tdepart_id bigint COMMENT 申诉人所属部门,appeal_status bigint COMMENT 申诉状态0:待稽核 1:无效 2有效,audit_id bigint COMMENT 稽核人id,audit_name string COMMENT 稽核人姓名,audit_department_id bigint COMMENT 稽核人所在部门,audit_department_name string COMMENT 稽核人部门名称,audit_date_time string COMMENT 稽核时间,create_date_time string COMMENT 创建时间申诉时间,update_date_time string COMMENT 更新时间,deleted bigint COMMENT 删除标志位,tenant bigint
) comment 线索申诉表
partitioned by (dt string)
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressZLIB);6edu_ods层建立ods_itcast_clazz
CREATE TABLE edu_ods.ods_itcast_clazz (id string COMMENT ems课程id(非自增),create_date_time string COMMENT 创建时间,update_date_time string COMMENT 最后更新时间,deleted string COMMENT 是否被删除禁用,itcast_school_id string COMMENT ems校区ID,itcast_school_name string COMMENT ems校区名称,itcast_subject_id string COMMENT ems学科ID,itcast_subject_name string COMMENT ems学科名称,itcast_brand string COMMENT ems品牌,clazz_type_state string COMMENT 班级类型状态,clazz_type_name string COMMENT 班级类型名称,teaching_mode string COMMENT 授课模式,start_time string COMMENT 开班时间,end_time string COMMENT 毕业时间,comment string COMMENT 备注,detail string COMMENT 详情(比如27期),uncertain string COMMENT 待定班(0:否,1:是),tenant string )comment 报名课程表
partitioned by (dt string)row format delimited fields terminated by \t
stored as orc tblproperties (orc.compressZLIB);
数据从mysql导入ods层语句 将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的customer
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query SELECT * ,${dateStr} as dt FROM edu_scrm.customer_${tableMonth}
WHERE wce.create_time BETWEEN ${dateStr} 00:00:00 AND ${dateStr} 23:59:59 and \$CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_customer \
-m 1将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的customer_appeal
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query select *, 2023-12-07 as dt from customer_appeal where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_customer_appeal \
--m 1;将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的customer_clue
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query select *, 2023-12-07 as dt from customer_clue where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_customer_clue \
--m 1;将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的customer_relationship
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query select *, 2023-12-07 as dt from customer_relationship where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_customer_relationship \
--m 1;将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的employee
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query select *, 2023-12-07 as dt from employee where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_employee \
--m 1;将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的itcast_clazz
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query select *, 2023-12-07 as dt from itcast_clazz where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_itcast_clazz \
--m 1;将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的itcast_school
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query select *, 2023-12-07 as dt from itcast_school where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_itcast_school \
--m 1;将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的itcast_subject
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query select *, 2023-12-07 as dt from itcast_subject where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_itcast_subject\
--m 1;将数据从mysql中导入到hive中的edu_ods 数据库下的scrm_department
/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/edu_scrm \
--username root \
--password 123456 \
--query select *, 2023-12-07 as dt from scrm_department where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_scrm_department \
--m 1;
全量脚本将数据从mysql导入hive的ods层的全量脚本 export SQOOP_HOME/usr/bin/sqoopdateStrdate -d -1 day %Y-%m-%d ###获取昨天的日期用于增量导入
tableMonthdate -d -1 day %Y_%m ###获取当年的年月用于导入数据的业务数据库的表名称jdbcUrljdbc:mysql://106.75.33.59:3306/edu_scrm?useUnicodetruecharacterEncodingUTF-8autoReconnecttrue
usernameitcast_edu_stu
passworditcast_edu_stu### 将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的customer_appeal
/usr/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splittertrue \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query select *, 2023-12-07 as dt from customer_appeal where 11 and $CONDITIONS and dtdateStr \
--hcatalog-database edu_ods \
--hcatalog-table ods_customer_appeal \
--m 1;### 将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的customer_clue
/usr/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splittertrue \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query select *, 2023-12-07 as dt from customer_clue where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_customer_clue \
--m 1;### 将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的customer_relationship
/usr/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splittertrue \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query select *, 2023-12-07 as dt from customer_relationship where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_customer_relationship \
--m 1;### 将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的employee
/usr/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splittertrue \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query select *, 2023-12-07 as dt from employee where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_employee \
--m 1;### 将数据从mysql中导入到hive中的edu_scrm_ods 数据库下的itcast_clazz
/usr/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splittertrue \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query select *, 2023-12-07 as dt from itcast_clazz where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_itcast_clazz \
--m 1;### 将数据从mysql中导入到hive中的edu_ods 数据库下的scrm_department
/usr/bin/sqoop import -Dorg.apache.sqoop.splitter.allow_text_splittertrue \
--connect ${jdbcUrl} \
--username ${username} \
--password ${password} \
--query select *, 2023-12-07 as dt from scrm_department where 11 and $CONDITIONS \
--hcatalog-database edu_ods \
--hcatalog-table ods_scrm_department \
--m 1;dwd层维度表事实表拉链表建表语句 1创建DWD层的表dim_customer_appealdrop table edu_dwd.dim_customer_appeal;
CREATE TABLE edu_dwd.dim_customer_appeal (id int,customer_relationship_first_id int COMMENT 第一条客户关系id,employee_id int COMMENT 申诉人,employee_name string COMMENT 申诉人姓名,employee_department_id int COMMENT 申诉人部门,employee_tdepart_id int COMMENT 申诉人所属部门,appeal_status int COMMENT 申诉状态0:待稽核 1:无效 2有效,audit_id int COMMENT 稽核人id,audit_name string COMMENT 稽核人姓名,audit_department_id int COMMENT 稽核人所在部门,audit_department_name string COMMENT 稽核人部门名称,audit_date_time string COMMENT 稽核时间,create_date_time string COMMENT 创建时间申诉时间,update_date_time string COMMENT 更新时间,deleted int COMMENT 删除标志位,tenant int ,
end_date string COMMENT 失效日期,start_date string COMMENT 开始日期) comment 线索申诉维度拉链表partitioned by (dt string)
clustered by (customer_relationship_first_id) into 6 bucketsrow format delimited fields terminated by \t
stored as orc tblproperties (orc.compressSNAPPY)relationship,clue,appeal,itcast_clazz,employee,department2创建DWD层的表dim_itcast_clazz
drop table edu_dwd.dim_itcast_clazz;
CREATE TABLE edu_dwd.dim_itcast_clazz (id int COMMENT ems课程id(非自增),create_date_time string COMMENT 创建时间,update_date_time string COMMENT 最后更新时间,deleted int COMMENT 是否被删除禁用,itcast_school_id string COMMENT ems校区ID,itcast_school_name string COMMENT ems校区名称,itcast_subject_id string COMMENT ems学科ID,itcast_subject_name string COMMENT ems学科名称,itcast_brand string COMMENT ems品牌,clazz_type_state string COMMENT 班级类型状态,clazz_type_name string COMMENT 班级类型名称,teaching_mode string COMMENT 授课模式,start_time string COMMENT 开班时间,end_time string COMMENT 毕业时间,comment string COMMENT 备注,detail string COMMENT 详情(比如27期),uncertain int COMMENT 待定班(0:否,1:是),tenant int ,end_date string COMMENT 失效日期,start_date string COMMENT 开始日期,dt string )comment 报名课程表
partitioned by (year string,month string,day string)
clustered by (id) into 3 bucketsrow format delimited fields terminated by \t
stored as orc tblproperties (orc.compressSNAPPY);3创建DWD层的表dim_scrm_departmentCREATE TABLE edu_dwd.dim_scrm_department (id bigint COMMENT 部门id,name string COMMENT 部门名称,parent_id bigint COMMENT 父部门id,create_date_time string COMMENT 创建时间,update_date_time string COMMENT 更新时间,deleted bigint COMMENT 删除标志,id_path string COMMENT 编码全路径,tdepart_code bigint COMMENT 直属部门,creator string COMMENT 创建者,depart_level bigint COMMENT 部门层级,depart_sign bigint COMMENT 部门标志暂时默认1,depart_line bigint COMMENT 业务线存储业务线编码,depart_sort bigint COMMENT 排序字段,disable_flag bigint COMMENT 禁用标志,tenant bigint,dt string
) comment 部门表
partitioned by (yeat string,month string ,day string)
clustered by (id) into 3 buckets
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressSNAPPY);4创建了DWD层的表edu_dwd.dim_employee
create table edu_dwd.dim_employee
(id bigint ,email string comment 公司邮箱OA登录账号,real_name string comment 员工的真实姓名,phone string comment 手机号目前还没有使用隐私问题OA接口没有提供这个属性,department_id string comment OA中的部门编号有负值,department_name string comment OA中的部门名,remote_login bigint comment 员工是否可以远程登录,job_number string comment 员工工号,cross_school bigint comment 是否有跨校区权限,last_login_date string comment 最后登录日期,creator bigint comment 创建人,create_date_time string comment 创建时间,update_date_time string comment 最后更新时间,deleted bigint comment 是否被删除禁用,scrm_department_id bigint comment SCRM内部部门id,leave_office bigint comment 离职状态,leave_office_time string comment 离职时间,reinstated_time string comment 复职时间,superior_leaders_id bigint comment 上级领导ID,tdepart_id bigint comment 直属部门,tenant bigint,ems_user_name string
)comment 员工信息表
partitioned by (dt string)
clustered by (id) into 3 buckets
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressSNAPPY);5创建DWD层的表ods_customer_clue
drop table edu_dwd.ods_customer_clue;
CREATE TABLE edu_dwd.ods_customer_clue (id bigint ,create_date_time string COMMENT 创建时间,update_date_time string COMMENT 最后更新时间,deleted bigint COMMENT 是否被删除禁用,customer_id bigint COMMENT 客户id,customer_relationship_id bigint COMMENT 客户关系id,session_id string COMMENT 七陌会话id,sid string COMMENT 访客id,status string COMMENT 状态undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转,user 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 bigint COMMENT 客服回复消息数,total_msg_count bigint COMMENT 消息总数,msg_count bigint 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 bigint COMMENT 创建人,name string COMMENT 客户姓名,idcard string COMMENT 身份证号,phone string COMMENT 手机号,itcast_school_id bigint COMMENT 校区Id,itcast_school string COMMENT 校区,itcast_subject_id bigint 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 bigint COMMENT 该线索是否是网资有效线索,anticipat_signup_date string COMMENT 预计报名时间,clue_state string COMMENT 线索状态,scrm_department_id bigint 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 bigint,course_name string ,zhuge_session_id string,is_repeat bigint COMMENT 是否重复线索(手机号维度) 0:正常 1重复,tenant bigint COMMENT 租户id,activity_id string COMMENT 活动id,activity_name string COMMENT 活动名称,follow_type bigint COMMENT 分配类型0-自动分配1-手动分配2-自动转移3-手动单个转移4-手动批量转移5-公海领取,shunt_mode_id bigint COMMENT 匹配到的技能组id,shunt_employee_group_id bigint COMMENT 所属分流员工组,end_date string COMMENT 失效日期,start_date string COMMENT 开始日期dt string
) COMMENT 客户线索表
partitioned by (year string,month string ,day string)
clustered by (customer_relationship_id) into 6 buckets
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressSNAPPY);6创建DWD层的表fact_customer_relationship
drop table edu_ods.ods_customer_relationship;
CREATE TABLE edu_dwd.fact_customer_relationship(id bigint ,create_date_time string ,update_date_time string COMMENT 最后更新时间,deleted bigint COMMENT 是否被删除禁用,customer_id bigint COMMENT 所属客户id,first_id bigint COMMENT 第一条客户关系id,belonger bigint COMMENT 归属人,belonger_name string COMMENT 归属人姓名,initial_belonger bigint COMMENT 初始归属人,distribution_handler bigint COMMENT 分配处理人,business_scrm_department_id bigint COMMENT 归属部门,last_visit_time string COMMENT 最后回访时间,next_visit_time string COMMENT 下次回访时间,origin_type string COMMENT 数据来源,itcast_school_id bigint COMMENT 校区Id,itcast_subject_id bigint COMMENT 学科Id,intention_study_type string COMMENT 意向学习方式,anticipat_signup_date string COMMENT 预计报名时间,level string COMMENT 客户级别,creator bigint COMMENT 创建人,current_creator bigint COMMENT 当前创建人初始创建人当在公海拉回时为 拉回人,creator_name string COMMENT 创建者姓名,origin_channel string COMMENT 来源渠道,comment string COMMENT 备注,first_customer_clue_id bigint COMMENT 第一条线索id,last_customer_clue_id bigint 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 bigint COMMENT 锁定状态,lock_time string COMMENT 锁定状态修改时间,itcast_clazz_id bigint COMMENT 所属ems班级id,itcast_clazz_time string COMMENT 报班时间,payment_url string COMMENT 付款链接,payment_url_time string COMMENT 支付链接生成时间,ems_student_id bigint COMMENT ems的学生id,delete_reason string COMMENT 删除原因,deleter bigint COMMENT 删除人,deleter_name string COMMENT 删除人姓名,delete_time string COMMENT 删除时间,course_id bigint COMMENT 课程ID,course_name string COMMENT 课程名称,delete_comment string COMMENT 删除原因说明,close_state string COMMENT 关闭装填,close_time string COMMENT 关闭状态变动时间,appeal_id bigint COMMENT 申诉id,tenant bigint COMMENT 租户,total_fee decimal(19,0) COMMENT 报名费总金额,belonged bigint COMMENT 小周期归属人,belonged_time string COMMENT 归属时间,belonger_time string COMMENT 归属时间,transfer bigint COMMENT 转移人,transfer_time string COMMENT 转移时间,follow_type bigint COMMENT 分配类型0-自动分配1-手动分配2-自动转移3-手动单个转移4-手动批量转移5-公海领取,transfer_bxg_oa_account string COMMENT 转移到博学谷归属人OA账号,transfer_bxg_belonger_name string COMMENT 转移到博学谷归属人OA姓名
) COMMENT 客户意向表
partitioned by (year string,month string ,day string)
clustered by (id) into 3 buckets
row format delimited fields terminated by \t stored as orc tblproperties (orc.compressSNAPPY);数据从ods层导入dwd层语句
1,将ods层的数据插入dwd层的表dim_customer_appeal拉链表
insert overwrite table edu_dwd.dim_customer_appeal PARTITION (dt)
select id ,customer_relationship_first_id ,employee_id ,employee_name ,employee_department_id,employee_tdepart_id,appeal_status,audit_id,audit_name,audit_department_id ,audit_department_name ,audit_date_time ,create_date_time,update_date_time,deleted,tenant ,
9999-99-99 end_date,
update_date_time as start_date,
dt
from edu_ods.ods_customer_appeal2,,将ods层的数据插入dwd层的表edu_dwd.dim_itcast_clazz(拉链表)
insert overwrite table edu_dwd.dim_itcast_clazz partition(year,month ,day)
select
id
,create_date_time
,update_date_time
,deleted
,itcast_school_id
,itcast_school_name
,itcast_subject_id
,itcast_subject_name
,itcast_brand
,clazz_type_state
,clazz_type_name
,teaching_mode
,start_time
,end_time
,comment
,detail
,uncertain
,tenant
,9999-99-99 end_date
,update_date_time as start_date
,dt string
,year(create_date_time)
,month(create_date_time)
,day(create_date_time)
from
edu_ods.ods_itcast_clazz3,将ods层的数据插入dwd层的表dim_scrm_departmentinsert overwrite table edu_dwd.dim_scrm_department partition(year,month,day)
select
id
,name
,parent_id
,create_date_time
,update_date_time
,deleted
,id_path
,tdepart_code
,creator
,depart_level
,depart_sign
,depart_line
,depart_sort
,disable_flag
,tenant
,dt
,year(create_date_time) as year
,month(create_date_time) as month
,day(create_date_time) as day
from edu_ods.ods_scrm_department4,将ods层的数据插入dwd层的表edu_dwd.dim_employee
insert overwrite table edu_dwd.dim_employee partition(dt)
select
,id
,email
,real_name
,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
,dt
from deu_ods.ods_employee5,将ods层的数据插入dwd层的表clue拉链表
insert overwrite table edu_dwd.fact_customer_clue partition(year,month,day)
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
,9999-99-99 end_date
,update_date_time as start_date,
dt,
year(create_date_time) as year,
month(create_date_time) as month,
day(create_date_time) as day
from edu_ods.ods_customer_clue6,将ods层的数据插入dwd层的表realtionship拉链表insert overwrite table edu_dwd.fact_customer_relationship partition(year,month,day)
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-99-99 end_date
,update_date_time as start_date,year(create_date_time) as year,month(create_date_time) as month,day(create_date_time) as dayfrom edu_ods.ods_customer_relationship
