数据仓库Hive应用实战- 课件 模块9 数据仓库应用实战

理解并熟记创建数据库的语法和应用方法理解并熟记修改和删除数据库的语法和应用方法理解并熟记查询和切换数据库的语法和应用方法理解并熟记修改数据库存储位置的语法和应用方法理解并初步建立大数据仓库安全与规范意识

联通运营商数据中的通话时长、出账费用、流量使用、产品到期等指标数据存放在四张数据表中，我们需要根据联通运营商数据文件，完成“联通运营商数据分析项目”数据仓库的构建、表的创建。表9-1展示了数据和表的对应关系。

本任务以“联通运营商大数据分析”项目为实操载体，完成创建数据仓库、创建用户基础信息表、创建用户产品属性表、创建用户使用信息表和创建宽表等学习目标。任务1设计数据仓库“联通运营商数据分析项目”数据表规划任务1创建数据仓库文件名文件内容表名备注list1_test.txt用户产品信息表user_product_info

list2_test1.txt用户基础信息表user_info_basic

list2_test2.txt用户产品属性表user_product_attr

list3_test.txt用户使用信息表user_info

//user_churn_total宽表，经过各种加工汇总，得到信息汇总表数据示例list1_test.txt任务1创建数据仓库100100900531023713G032017-12-0114:59:552050-12-3100:00:00201801100101000531023713G032017-03-1110:55:172050-12-3100:00:00201801100101200531023713G032017-08-0100:00:002050-12-3100:00:00201801......用户基础信息表任务1创建数据仓库字段字段类型字段描述口径概要说明d_useridString用户唯一标识电信公司内部用于区分用户的唯一编码13service_typeString业务类型2G/3G/4G，指用户使用的最高的网络类型。pay_modeString付费模式分为后付费/预付费两种，cert_ageInt用户年龄用户年龄chnl_typeString发展渠道联通在发展新用户时的发展渠道month_idString账期-月用户使用业务后会产生语音通话等数据，根据统计频率的不同，账期分为月/日等不同粒度，由于统计的是用户已发生的数据，通常滞后一个账期数据示例list2_test1.txt任务1创建数据仓库100092540AAAAAA143社会实体渠道201801100092720AAAAAA153社会实体渠道201801100092940AAAAAA146社会实体渠道201801.......用户产品信息表任务1创建数据仓库字段字段类型字段描述口径概要说明d_useridString用户唯一标识电信公司内部用于区分用户的唯一编码product_modeString产品模式分为主产品（例如106元套餐）、附属产品（例如10元300M流量包）product_idString产品编码产品是指用户选择的资费标准，如106套餐brand_codeString品牌编码品牌例如：如意通、新势力、世界风start_dateString产品生效时间产品生效时间end_dateString产品到期时间产品到期时间month_idString账期-月用户使用电信业务后会产生语音通话等数据，根据统计频率的不同，账期分为月/日等不同粒度，由于统计的是用户已发生的数据，通常滞后一个账期数据示例list2_test2.txt任务1创建数据仓库1000925NULL无单卡2018011000927NULL无单卡201801......1002754NULL智慧沃家组合优化版单卡2018011002756NULL主副卡单卡201801.......用户产品属性表任务1创建数据仓库字段字段类型字段描述口径概要说明d_useridString用户唯一标识

product_class_descString产品类别产品分类，如标准化套餐、省份制定的个性化套餐、互联网类套餐等comp_typeString融合产品类型融合产品是指用户既有移动号码又有宽带activity_typeString活动类型用户主产品办理时参加的活动，如存费送机/购机送费等month_idString账期-月用户使用业务后会产生语音通话等数据，根据统计频率的不同，账期分为月/日等不同粒度，由于统计的是用户已发生的数据，通常滞后一个账期数据示例list3_test.txt任务1创建数据仓库10009251653493022.34-182.69-5.95112480.5764000002018011000927141018-4.600001863.751400000201801100092916321840.14.134.12002351.72200000201801......用户使用信息表任务1创建数据仓库字段字段类型字段描述口径概要说明d_useridString用户唯一标识

moudecimal(30,2)户均通话时长用户每月平均通话时长doudecimal(30,2)户均上网流量用户每月平均上网流量arpudecimal(30,2)月均出账费-元用户每月平均消费金额mou_vardecimal(30,2)月通话时长-方差方差：月均值的期望值dou_vardecimal(30,2)月上网流量-方差方差：月均值的期望值arpu_vardecimal(30,2)月出账费-方差方差：月均值的期望值smsInt短信使用条数用户月度短信使用条数flux_4gdecimal(30,2)4G流量使用量使用4G网络产生的流量使用量call_ringInt呼叫圈个数与该用户产生通话行为的主要用户的个数unicom_scoredecimal(30,2)用户分值系统内部评定分值innet_monthsInt在网月份数用户从开始使用该号码，持续到现在的月份数is_cardString是否单卡该移网用户相同证件下是否有宽带业务，有宽带则为融合，无宽带则为单卡is_groupString是否集客指以集团组织或法人单位与电信公司办理业务协议等is_lvString是否低价值用户低价值：出账费用小于一定额度，语音/流量/短信使用量很少is_transString是否转网指在2G或3G用户转到4G网络is_acct_lostString是否流失本例的目标字段。0-未流失，1-流失15month_idString账期-月用户使用业务后会产生语音通话等数据，根据统计频率的不同，账期分为月/日等不同粒度，由于统计的是用户已发生的数据，通常滞后一个账期联通运营商大数据分析本任务实施以“联通运营商大数据分析”项目为实操载体，依次完成创建数据仓库、创建用户基础信息表、创建用户产品属性表、创建用户使用信息表和创建宽表等操作，最终完成数据仓库的设计和表的创建。任务1创建数据仓库9.1.1创建数据仓库我们使用Hive构建原始数据的数据仓库，进入Hive客户端创建数据建库user_churn，操作命令如下所示：CREATEdatabaseifnotexistsuser_churn;查看hive中所有数据库，使用user_churn数据库。任务1设计数据仓库9.1.2创建用户产品信息表createtableuser_product_info(

d_useridstringCOMMENT'用户唯一标识',

product_modestringCOMMENT'产品模式',

product_idstringCOMMENT'产品编码',

brand_codestringCOMMENT'品牌编码',

start_datestringCOMMENT'产品生效时间',

end_datestringCOMMENT'产品到期时间',

month_idstringCOMMENT'账期-月')rowformatdelimitedfieldsterminatedby'\u0001'nulldefinedas''storedastextfile;任务1设计数据仓库9.1.3创建用户基础信息表createtableuser_info_basic(

d_useridstringCOMMENT'用户唯一标识',

service_typestringCOMMENT'业务类型',

pay_modestringCOMMENT'付费模式',

cert_ageintCOMMENT'用户年龄',

chnl_typestringCOMMENT'发展渠道',

month_idstringCOMMENT'账期-月')rowformatdelimitedfieldsterminatedby'\u0001'nulldefinedas''storedastextfile;任务1设计数据仓库9.1.4创建用户产品属性表createtableuser_product_attr(

d_useridstringCOMMENT'用户唯一标识',

product_class_descstringCOMMENT'产品类别',

comp_typestringCOMMENT'融合产品类型',

activity_typestringCOMMENT'活动类型',

month_idstringCOMMENT'账期-月')rowformatdelimitedfieldsterminatedby'\u0001'nulldefinedas''storedastextfile;任务1设计数据仓库9.1.5创建用户使用信息表任务1设计数据仓库9.1.6创建宽表createtableuser_churn_total(d_useridstringCOMMENT'用户唯一标识',product_idstringCOMMENT'产品编码',brand_codestringCOMMENT'品牌编码',service_typestringCOMMENT'业务类型',product_class_descstringCOMMENT'产品类别',pay_modestringCOMMENT'付费模式',cert_ageintCOMMENT'用户年龄',chnl_typestringCOMMENT'发展渠道',comp_typestringCOMMENT'融合产品类型',activity_typestringCOMMENT'活动类型',moudecimal(30,2)COMMENT'户均通话时长',任务1设计数据仓库9.1.6创建宽表doudecimal(30,2)COMMENT'户均上网流量',arpudecimal(30,2)COMMENT'月均出账费-元',mou_vardecimal(30,2)COMMENT'月通话时长-方差',dou_vardecimal(30,2)COMMENT'月上网流量-方差',arpu_vardecimal(30,2)COMMENT'月出账费-方差',smsintCOMMENT'短信使用条数',flux_4gdecimal(30,2)COMMENT'4G流量使用量',call_ringintCOMMENT'呼叫圈个数',unicom_scoredecimal(30,2)COMMENT'用户分值',innet_monthsintCOMMENT'在网月份数',is_cardstringCOMMENT'是否单卡',任务1设计数据仓库9.1.6创建宽表is_groupstringCOMMENT'是否集客',is_lvstringCOMMENT'是否低价值用户',is_transstringCOMMENT'是否转网',is_acct_loststringCOMMENT'是否流失',rest_monthsstringCOMMENT'到期剩余月份数')PARTITIONEDBY(month_idstringCOMMENT'账期-月')rowformatdelimitedfieldsterminatedby'\u0001'nulldefinedas''storedastextfile;任务1设计数据仓库

本任务以“联通运营商大数据分析”项目为实操载体，完成导入数据、验证导入结果等学习目标。

完成数据导入操作，需将现有数据list1_test.txt、list2_test1.txt、list2_test2.txt和list3_test.txt存放到本地目录下，使用secureFX工具上传数据到本地目录/hivepro。任务2导入联通运营商数据到数据仓库9.2.1导入数据向表中加载对应数据。loaddatalocalinpath'/hivepro/list1_test.txt'overwriteintotableuser_product_info;loaddatalocalinpath'/hivepro/list2_test1.txt'overwriteintotableuser_info_basic;loaddatalocalinpath'/hivepro/list2_test2.txt'overwriteintotableuser_product_attr;loaddatalocalinpath'/hivepro/list3_test.txt'overwriteintotableuser_info;任务2导入联通运营商数据到数据仓库9.2.2验证导入结果1、验证user_product_info数据。查看user_product_info表的前3条数据和数据总条数。任务2导入联通运营商数据到数据仓库9.2.2验证导入结果2、验证user_info_basic数据。查看user_info_basic表中前3条数据和数据总条数。任务2导入联通运营商数据到数据仓库9.2.2验证导入结果3、验证user_product_attr数据。查看user_product_attr表中前3条数据和数据总条数。任务2导入联通运营商数据到数据仓库9.2.2验证导入结果4、验证user_info数据。查看user_info表中前3条数据和数据总条数。任务2导入联通运营商数据到数据仓库

在对数据进行统计分析之前，需要对原始数据进行必要的清洗、集成和转换等操作，目的在于删除重复信息，纠正存在的错误，处理无效值和缺失值。

本任务以“联通运营商大数据分析”项目为实操载体，完成联通运营商数据的预处理，包括删除重复数据、处理缺失值、衍生新指标、删除无效字段和数据归集数据等学习目标。任务3处理联通运营商数据9.3.1删除重复数据1、数据核查核查是否有重复数据，将user_product_info表按账期与号码分组，查询数据中分组个数大于等于2个的数据。任务3处理联通运营商数据9.3.1删除重复数据2、功能分析查看和分析重复数据情况，利用上面查询到的重复数据d_userid（用户唯一标识）结果，查询所有重复数据的每个字段，按照d_userid正序排列。任务3处理联通运营商数据9.3.1删除重复数据3、程序开发将user_product_info表中数据按end_date倒序排列，保留顺序号为1的记录重新插入到user_product_info表中。任务3处理联通运营商数据insertoverwritetableuser_product_infoselecta.d_userid,product_mode,product_id,brand_code,start_date,end_date,month_idfrom(selectt.*,row_number()over(partitionbyt.d_userid,t.month_idorderbyt.end_datedesc)rnfromuser_product_infot)awherea.rn=1;9.3.1删除重复数据3、程序开发去除重复数据之后，我们可以验证去重结果：任务3处理联通运营商数据9.3.2处理缺失值1、数据核查HQL命令统计缺失值，查询user_product_info表中所有字段等于‘NULL’或者为空的个数，并展示出来。任务3处理联通运营商数据9.3.2处理缺失值1、数据核查同理可以查询其他三个表中的缺失值。user_info_basic表空值情况：任务3处理联通运营商数据9.3.2处理缺失值1、数据核查user_product_attr表空值情况：任务3处理联通运营商数据9.3.2处理缺失值1、数据核查user_info表空值情况：任务3处理联通运营商数据9.3.2处理缺失值2、功能分析user_product_info表的product_mode、product_id、brand_code、start_date、end_date这5个字段有30个空值，记录比较少，删除几乎不会影响到数据信息，可做删除处理user_product_attr表的product_class_desc字段有134461个空值，空值较多，删除会导致信息损失，由于是字符型字段，可用某个不常用字符填充，如‘missing’；user_info_basic表的cert_age字段有8914个空值，是年龄字段，根据业务经验，可用均值填充。任务3处理联通运营商数据9.3.2处理缺失值3、程序开发（1）空值记录删除：将user_product_info表中字段不为空的数据重新插入user_product_info表中：任务3处理联通运营商数据insertoverwritetableuser_product_infoselecta.*fromuser_product_infoawhere(duct_modeisnotnull)OR(duct_idisnotnull)OR(a.brand_codeisnotnull)OR(a.start_dateisnotnull)OR(a.end_dateisnotnull);9.3.2处理缺失值3、程序开发删除空值后的user_product_info表数据总条数如下：任务3处理联通运营商数据9.3.2处理缺失值3、程序开发（2）空值记录填充：user_product_attr表和user_info_basic表都要进行空值填充操作。product_class_desc字段填充，将字段等于'NULL'或者为空的记录替换为'missing'重新插入到表中。任务3处理联通运营商数据insertoverwritetableuser_product_attrselectd_userid,casewhenproduct_class_desc='NULL'ORproduct_class_descisnullthen'missing'elseproduct_class_descend,comp_type,activity_type,month_idfromuser_product_attra;9.3.2处理缺失值3、程序开发（2）空值记录填充：user_product_attr表和user_info_basic表都要进行空值填充操作。②cert_age字段填充，先对年龄取均值，然后关联表user_info_basic，将年龄等于'NULL'或者为空的记录替换为均值重新插入到表中。任务3处理联通运营商数据insertoverwritetableuser_info_basicselectd_userid,service_type,pay_mode,casewhencert_age='NULL'ORcert_ageisnullthenb.avg_ageelsecert_ageend,chnl_type,month_idfromuser_info_basicajoin(selectround(avg(cert_age),0)avg_agefromuser_info_basic)b;9.3.3衍生新指标1、功能分析产品的开始时间（start_date）和结束时间（end_date）都是时点数据，分析时很少直接使用，通常都用于衍生其他字段，比如用end_date与账期做差值，衍生出“到期剩余月份数”指标，去掉start_date和end_date，并生成新表：user_product_info_new。任务3处理联通运营商数据9.3.3衍生新指标2、程序开发任务3处理联通运营商数据createtableuser_product_info_newasselectd_userid,product_mode,product_id,brand_code,floOR(months_between(end_date_re,month_id_re))rest_months,month_idfrom(selectt1.*,substr(end_date,1,10)end_date_re,from_unixtime(unix_timestamp(concat(month_id,'01'),'yyyymmdd'),'yyyy-mm-dd')month_id_refromuser_product_infot1)a;9.3.3衍生新指标2、程序开发生成的新表user_product_info_new数据展示：任务3处理联通运营商数据9.3.4删除无效字段2、程序开发对于字段的删除，我们通常很少在原表上进行操作，而是在使用的时候直接放弃选择这个字段，所以product_mode等字段在数据归集成宽表时直接弃用。任务3处理联通运营商数据9.3.5归集数据1、功能分析先将user_info_basic表与user_product_attr表合并为一张中间表user_churn_temp，再将中间表user_churn_temp、user_product_info表和user_info表合并到同一张宽表中，以便于后续分析。任务3处理联通运营商数据9.3.5归集数据2、程序开发（1）user_info_basic表与user_product_attr表按照用户唯一标识关联合并中间表，限定账期为“201801”。任务3处理联通运营商数据9.3.5归集数据查看中间表的数据：任务3处理联通运营商数据9.3.5归集数据2、程序开发（2）中间表user_churn_temp、user_product_info表和user_info表按照用户唯一标识关联合并成宽表user_churn_total，将账期为“201801”的数据插入到“201801”分区中。任务3处理联通运营商数据9.3.5归集数据查看宽表user_churn_total的前3条数据和数据总条数：任务3处理联通运营商数据9.3.5归集数据2、程序开发（3）生成中间表，对账期为“201802”的数据则重复上面的步骤，将month_id='201801'改为month_id='2018