大数据技术基础及应用（微课版） 实验手册6.Hive数据仓库实战

Hive数据仓库实战实验背景Hive是重要的数据仓库工具，在数据挖掘、数据汇总、统计分析等领域有重要作用。特别的在电信业务中，Hive扮演相当重要的角色，可以利用Hive统计用户的流量、话费、资费等信息，也可挖掘出用户的消费模型以帮助运营商更好的规划套餐内容。实验前提实验环境创建成功后，请等待1-2分钟，后台在执行start-oms.sh脚本：执行sh${BIGDATA_HOME}/om-server/om/sbin/status-oms.sh命令查看OMS的状态，显示信息表示主备OMS服务启动正常再执行下一步。在windows-client打开GoogleChrome浏览器登录FusionInsightManager地址（00:8080/web）。登录FusionInsightManager后，单击“服务管理”，选择“更多操作>启动集群”。在弹出的提示框中单击“确定”，开始启动集群。界面提示“操作成功。”，单击“完成”，集群成功启动。使用SecureCRT工具登录到fihsots-1节点上，接下来进行环境变量设置与用户认证。cd/opt/hadoopclient/切换到客户端安装目录。sourcebigdata_env配置环境变量。kinituser01进行用户认证，输入用户密码客户端文件安装于fihosts-1服务器的/opt/hadoopclient目录中，认证用户user01，密码Huawei!@34实验目的掌握Hive的常用操作。学会在Hue上使用和运行HQL。实验任务Hive常用函数（可选）进入Hive客户端beeline。>/opt/hadoopClient/Hive/Beeline/bin/beeline...Connectedto:ApacheHive(version1.3.0)Driver:HiveJDBC(version1.3.0)Transactionisolation:TRANSACTION_REPEATABLE_READBeelineversion1.3.0byApacheHive0:jdbc:hive2://1:21066/>注意：hive中的所有语句都需要以分号结尾，否则不能执行。参考：适用如下命令，可以过滤INFO日志的输出：beeline--hiveconfhive.server2.logging.operation.level=NONE字符串函数length(stringA)。字符串长度函数：length语法:length(stringA)返回值:int说明：返回字符串A的长度hive>selectlength(‘abcedfg’);7字符串反转函数reverse。语法:reverse(stringA)返回值:string说明：返回字符串A的反转结果hive>selectreverse(‘abcedfg’);gfdecba字符串连接函数concat。语法:concat(stringA,stringB…)返回值:string说明：返回输入字符串连接后的结果，支持任意个输入字符串hive>selectconcat(‘abc’,'def’,'gh’);abcdefgh带分隔符字符串连接函数concat_ws。语法:concat_ws(stringSEP,stringA,stringB…)返回值:string说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符hive>selectconcat_ws(‘-’,'abc’,'def’,'gh’);abc-def-gh字符串截取函数substr,substring。语法:substr(stringA,intstart,intlen),substring(stringA,intstart,intlen)返回值:string说明：返回字符串A从start位置开始，长度为len的字符串hive>selectsubstr('abcde',3,2);cdhive>selectsubstr('abcde',-2,2);de字符串转大写函数upper,ucase。语法:upper(stringA)ucase(stringA)返回值:string说明：返回字符串A的大写格式hive>selectupper(‘abC’);ABChive>selectucase(‘abC’);ABC字符串转小写函数lower,lcase。语法:lower(stringA)lcase(stringA)返回值:string说明：返回字符串A的小写格式hive>selectlower(‘abC’);abchive>selectlcase(‘abC’);abc去空格函数trim。语法:trim(stringA)返回值:string说明：去除字符串两边的空格hive>selecttrim(‘abc‘);abc分割字符串函数split。语法:

split(stringstr,stringpat)返回值:array说明:按照pat字符串分割str，会返回分割后的字符串数组hive>selectsplit(‘abtcdtef’,'t’);["ab","cd","ef"]日期函数。获取当前UNIX时间戳函数:

unix_timestamp语法:

unix_timestamp()

返回值:

bigint说明:

获得当前时区的UNIX时间戳。hive>select

unix_timestamp()

from

dual;1521511607UNIX时间戳转日期函数:

from_unixtime语法:from_unixtime(bigint

unixtime[,

string

format])

返回值:

string说明:

转化UNIX时间戳（从1970-01-01

00:00:00

UTC到指定时间的秒数）到当前时区的时间格式。hive>

select

from_unixtime(1521511607,'yyyyMMdd');20180320Hive创建表建表语句CREATE[EXTERNAL]TABLE[IFNOTEXISTS]table_name[(col_namedata_type[COMMENTcol_comment],...)][COMMENTtable_comment][PARTITIONEDBY(col_namedata_type[COMMENTcol_comment],...)][CLUSTEREDBY(col_name,col_name,...)[SORTEDBY(col_name[ASC|DESC],...)]INTOnum_bucketsBUCKETS][ROWFORMATrow_format][STOREDASfile_format][LOCATIONhdfs_path]创建内部表创建内部表cga_info1，包含name，gender和time三列。>createtablecga_info1(namestring,genderstring,timeint)rowformatdelimitedfieldsterminatedby','storedastextfile;Norowsaffected(0.293seconds)其中，rowformatdelimitedfieldsterminatedby','表示行分隔符设置为’,’，如果不设置则使用默认分隔符。Hive的HQL语句最后是以’;’结束的。查看表“cga_info1”：>showtableslike'cga_info1';+------------+--+|tab_name|+------------+--+|cga_info1|+------------+--+1rowselected(0.07seconds)创建外部表创建外部表时要注明externaltable。>createexternaltablecga_info2(namestring,genderstring,timeint)rowformatdelimitedfieldsterminatedby','storedastextfile;Norowsaffected(0.343seconds)查看创建表“cga_info2”。>showtableslike'cga_info2';+------------+--+|tab_name|+------------+--+|cga_info2|+------------+--+1rowselected(0.078seconds)载入本地数据预先在本地/tmp下建立一个文件。>touch/tmp/cga111.txt使用vim命令编辑文件cga111.txt，按照name,gender,time的先后顺序输入几行数据，字段分隔符为’,’，换行按enter，输入完毕后按ESC，:wq，保存并退出到linux界面。>vim/tmp/cga111.txt具体输入内容，参照步骤6查询结果重新进入Hive。>beeline建立一个名为“cga_info3”的表。>createtablecga_info3(namestring,genderstring,timeint)rowformatdelimitedfieldsterminatedby','storedastextfile;Norowsaffected(0.408seconds)将本地数据“cga111.txt”载入表“cga_info3”中。>loaddatalocalinpath'/tmp/cga111.txt'intotablecga_info3;INFO:Loadingdatatotabledefault.cga_info3fromfile:/tmp/cga111.txtNorowsaffected(0.516seconds)查询表“cga_info3”中的内容。>select*fromcga_info3;+-----------------+-------------------+-----------------+--+|cga_|cga_info3.gender|cga_info3.time|+-----------------+-------------------+-----------------+--+|xiaozhao|female|20||xiaoqian|male|21||xiaosun|male|25||xiaoli|female|40||xiaozhou|male|33|+-----------------+-------------------+-----------------+--+5rowsselected(0.287seconds)由此可见，已经成功将本地文件“cga111.txt”中的内容加载到了Hive表“cga_info3”中。载入HDFS数据首先在HDFS上创建文件夹“/cga/cg”。>hdfsdfs-mkdir/cga18/04/1219:43:54INFOhdfs.PeerCache:SocketCachedisabled.>hdfsdfs-mkdir/cga/cg18/04/1219:44:24INFOhdfs.PeerCache:SocketCachedisabled.将tmp文件夹中的本地文件“cga111.txt”上传到HDFS的文件夹“/cga/cg”。>hdfsdfs-putcga111.txt/cga/cg18/04/1214:19:39INFOhdfs.PeerCache:SocketCachedisabled.重新进入Hive。>beeline建立一个名为“cga_info4”的表。>createtablecga_info4(namestring,genderstring,timeint)rowformatdelimitedfieldsterminatedby','storedastextfile;Norowsaffected(0.404seconds)将HDFS文件“cga111.txt”载入表“cga_info4”中。>loaddatainpath'/cga/cg/cga111.txt'intotablecga_info4;INFO:Loadingdatatotabledefault.cga_info4fromhdfs://hacluster/app_stu01/cga111.txtNorowsaffected(0.341seconds)加载成功后，hdfs的数据文件会被移动到/user/hive/warehouse目录下注：加载本地数据和HDFS数据使用的命令略有不同：加载本地文件：loaddatalocalinpath'local_inpath'intotablehive_table;加载HDFS文件：loaddatainpath'HDFS_inpath'intotablehive_table。查询表“cga_info4”中的内容。>select*fromcga_info4;+-----------------+-------------------+-----------------+--+|cga_|cga_info4.gender|cga_info4.time|+-----------------+-------------------+-----------------+--+|xiaozhao|female|20||xiaoqian|male|21||xiaosun|male|25||xiaoli|female|40||xiaozhou|male|33|+-----------------+-------------------+-----------------+--+5rowsselected(0.303seconds)由此可见，已经将HDFS文件“cga111.txt”的内容加载到了Hive表“cga_info3”中。创建表时载入数据创建表“cga_info5”同时载入HDFS上cga111.txt的数据。>createexternaltablecga_info5(namestring,genderstring,timeint)rowformatdelimitedfieldsterminatedby','storedastextfilelocation'/cga/cg';Norowsaffected(0.317seconds)查询表“cga_info5”中的内容。>select*fromcga_info5;+-----------------+-------------------+-----------------+--+|cga_|cga_info5.gender|cga_info5.time|+-----------------+-------------------+-----------------+--+|xiaozhao|female|20||xiaoqian|male|21||xiaosun|male|25||xiaoli|female|40||xiaozhou|male|33|+-----------------+-------------------+-----------------+--+5rowsselected(0.268seconds)由此可见，我们成功的在创建表“cga_info5”的同时载入了HDFS上cga111.txt的数据。复制一个空表建立一个新表“cga_info6”,将表“cga_info1”复制到表“cga_info6”中。>createtablecga_info6likecga_info1;Norowsaffected(0.244seconds)查询表“cga_info6”中的内容。>select*fromcga_info6;+-----------------+-------------------+-----------------+--+|cga_|cga_info6.gender|cga_info6.time|+-----------------+-------------------+-----------------+--++-----------------+-------------------+-----------------+--+Norowsselected(0.243seconds)由结果可知，复制空表成功。查询模糊查询表查询以“cga开头的表”。>showtableslike'*cga*';+-----------------+--+|tab_name|+-----------------+--+|cga_hive_hbase||cga_info1||cga_info2||cga_info3||cga_info4|+-----------------+--+5rowsselected(0.072seconds)条件查询示例1：使用limit查询表“cga_info3”中前两行的数据。>select*fromcga_info3limit2;+-----------------+-------------------+-----------------+--+|cga_|cga_info3.gender|cga_info3.time|+-----------------+-------------------+-----------------+--+|xiaozhao|female|20||xiaoqian|male|21|+-----------------+-------------------+-----------------+--+2rowsselected(0.295seconds)示例2：使用where查询表“cga_info3”中所有女性的信息。>select*fromcga_info3wheregender='female';+-----------------+-------------------+-----------------+--+|cga_|cga_info3.gender|cga_info3.time|+-----------------+-------------------+-----------------+--+|xiaozhao|female|20||xiaoli|female|40|+-----------------+-------------------+-----------------+--+2rowsselected(0.286seconds)示例3：使用order按时间递减顺序查询“cga_info3”中所有女性的信息。>select*fromcga_info3wheregender='female'orderbytimedesc;+-----------------+-------------------+-----------------+--+|cga_|cga_info3.gender|cga_info3.time|+-----------------+-------------------+-----------------+--+|xiaoli|female|40||xiaozhao|female|20|+-----------------+-------------------+-----------------+--+2rowsselected(24.129seconds)由结果可以看出本来xiaozhao的信息是先输入的，查询结果按照time的递减排序，所以xiaozhao的信息排在了第二个。多条件查询示例1：对表“cga_info3”进行查询，按姓名进行分组，找出time值大于30的人。>selectname,sum(time)all_timefromcga_info3groupbynamehavingall_time>=30;+-----------+-----------+--+|name|all_time|+-----------+-----------+--+|xiaoli|40||xiaozhou|33|+-----------+-----------+--+2rowsselected(24.683seconds)示例2：对表“cga_info3”进行查询，按性别分组，找出time值最大的人。>selectgender,max(time)fromcga_info3groupbygender;+---------+------+--+|gender|_c1|+---------+------+--+|female|40||male|33|+---------+------+--+2rowsselected(24.35seconds)示例3：统计表“cga_info3”中，女性和男性的总数各是多少。>selectgender,count(1)numfromcga_info3groupbygender;+---------+------+--+|gender|num|+---------+------+--+|female|2||male|3|+---------+------+--+2rowsselected(23.828seconds)示例4：将表“cga_info7”中女性的信息插入表”cga_info3”。首先建立内部表“cga_info7”。>createtablecga_info7(namestring,genderstring,timeint)rowformatdelimitedfieldsterminatedby','storedastextfile;Norowsaffected(0.282seconds)创建/tmp中创建本地文件“cga222.dat”并输入内容。>touchcga222.dat>vimcga222.datxiaozhao,female,20xiaochen,female,28将本地数据载入表“cga_info7”中。>loaddatalocalinpath'/tmp/cga222.dat'intotablecga_info7;INFO:Loadingdatatotabledefault.cga_info7fromfile:/tmp/cga222.datNorowsaffected(0.423seconds)将表“cga_info7”中女性的信息加载到表“cga_info3”中。>insertintocga_info3select*fromcga_info7wheregender='female';Norowsaffected(20.232seconds)查询表“cga_info3”中的内容。>select*fromcga_info3;+-----------------+-------------------+-----------------+--+|cga_|cga_info3.gender|cga_info3.time|+-----------------+-------------------+-----------------+--+|xiaozhao|female|20||xiaochen|female|28||xiaozhao|female|20||xiaoqian|male|21||xiaosun|male|25||xiaoli|female|40||xiaozhou|male|33|+-----------------+-------------------+-----------------+--+7rowsselected(0.224seconds)结果表明表“cga_info3”中增加了两条表“cga_info7‘’中女性信息。示例5：按姓名和性别分组，查询表“cga_info3”中每个人time值的总和。>selectname,gender,sum(time)timefromcga_info3groupbyname,gender;+-----------+---------+-------+--+|name|gender|time|+-----------+---------+-------+--+|xiaochen|female|28||xiaoli|female|40||xiaoqian|male|21||xiaosun|male|25||xiaozhao|female|40||xiaozhou|male|33|+-----------+---------+-------+--+6rowsselected(23.554seconds)从结果中可以看出，原本在表“cga_info3”中有两条xiaozhao的信息，现在已经被合并。示例6:首先统计表“cga_info3”中每个人的time总和信息，然后按照不同的性别，以time值降序排序。>select*,row_number()over(partitionbygenderorderbytimedesc)rankfrom(selectname,gender,sum(time)timefromcga_info3groupbyname,gender)b;+-----------+-----------+---------+-------+--+||b.gender|b.time|rank|+-----------+-----------+---------+-------+--+|xiaozhao|female|40|1||xiaoli|female|40|2||xiaochen|female|28|3||xiaozhou|male|33|1||xiaosun|male|25|2||xiaoqian|male|21|3|+-----------+-----------+---------+-------+--+6rowsselected(52.762seconds)Hiveonspark操作在beeline客户端下，将计算引擎设置成Spark。>sethive.execution.engine=spark;Norowsaffected(0.004seconds)按姓名和性别分组，查询表“cga_info3”中每个人time值的总和。>selectname,gender,sum(time)timefromcga_info3groupbyname,gender;+-----------+---------+-------+--+|name|gender|time|+-----------+---------+-------+--+|xiaochen|female|28||xiaoli|female|40||xiaoqian|male|21||xiaosun|male|25||xiaozhao|female|40||xiaozhou|male|33|+-----------+---------+-------+--+6rowsselected(1.213seconds)和实验任务《查询》示例5的结果相比，HiveonSpark的查询速度只要1秒钟，远远快于HiveonMapReduce的查询速率。注：具体执行速度与服务器配置有关。HiveJoin操作按照实验任务《Hive创建表》中《载入本地数据》的方法创建两张表“cga_info8”和“cga_info9”。注意列的数量查询表“cga_info8”的内容。>select*fromcga_info8;+-----------------+----------------+--+|cga_|cga_info8.age|+-----------------+----------------+--+|GuoYijun|5||YuanJing|10||Liyuan|20|+-----------------+----------------+--+3rowsselected(0.212seconds)查询表“cga_info9”的内容：>select*fromcga_info9;+-----------------+-------------------+--+|cga_|cga_info9.gender|+-----------------+-------------------+--+|YuanJing|male||Liyuan|male||LiuYang|female||Lilei|male|+-----------------+-------------------+--+4rowsselected(0.227seconds)join/innerjoinjoin和innerjoin操作是内关联，它将两个表的信息相关联，然后只返回两个表共有的结果。下面的语句使用join将表“cga_info8”和“cga_info9”中同一个人的信息相关联。>select*fromcga_info9ajoincga_info8bon=;+-----------+--------+-----------+-----------+--+||a.age||b.gender|+-----------+--------+-----------+-----------+--+|YuanJing|10|YuanJing|male||Liyuan|20|Liyuan|male|+-----------+--------+-----------+-----------+--+2rowsselected(24.954seconds)使用innerjoin将表“cga_info8”和“cga_info9”中同一个人的信息相关联。>select*fromcga_info9ainnerjoincga_info8bon=;+-----------+--------+-----------+-----------+--+||a.age||b.gender|+-----------+--------+-----------+-----------+--+|YuanJing|10|YuanJing|male||Liyuan|20|Liyuan|male|+-----------+--------+-----------+-----------+--+2rowsselected(25.07seconds)leftjoinleftjoin：左外关联，以leftjoin关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。使用leftjoin将表“cga_info8”和“cga_info9”中同一个人的信息相关联select*fromcga_info9aleftjoincga_info8bon=;+-----------+-----------+-----------+--------+--+||a.gender||b.age|+-----------+-----------+-----------+--------+--+|YuanJing|male|YuanJing|10||Liyuan|male|Liyuan|20||LiuYang|female|NULL|NULL||Lilei|male|NULL|NULL|+-----------+-----------+-----------+--------+--+4rowsselected(24.324seconds)rightjoinrightjoin：右外关联，以rightjoin关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。使用rightjoin将表“cga_info8”和“cga_info9”中同一个人的信息相关联。>select*fromcga_info9arightjoincga_info8bon=;+-----------+-----------+-----------+--------+--+||a.gender||b.age|+-----------+-----------+-----------+--------+--+|NULL|NULL|GuoYijun|5||YuanJing|male|YuanJing|10||Liyuan|male|Liyuan|20|+-----------+-----------+-----------+--------+--+3rowsselected(23.225seconds)fulljoinfulljoin：全外关联，是以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。使用fulljoin将表“cga_info8”和“cga_info9”中同一个人的信息相关联。>select*fromcga_info9afulljoincga_info8bon=;+-----------+-----------+-----------+--------+--+||a.gender||b.age|+-----------+-----------+-----------+--------+--+|NULL|NULL|GuoYijun|5||Lilei|male|NULL|NULL||LiuYang|female|NULL|NULL||Liyuan|male|Liyuan|20||YuanJing|male|YuanJing|10|+-----------+-----------+-----------+--------+--+5rowsselected(26.763seconds)leftsemijoinleftsemijoin：以leftsemijoin关键字前面的表为主表，返回主表的KEY也在副表中的记录。使用leftsemijoin将表“cga_info8”和“cga_info9”中同一个人的信息相关联。>select*fromcga_info9aleftsemijoincga_info8bon=;+-----------+-----------+--+||a.gender|+-----------+-----------+--+|YuanJing|male||Liyuan|male|+-----------+-----------+--+2rowsselected(24.96seconds)mapjoinmapjoin是Hive的一种优化操作，其适用于小表join大表的场景，由于表的join操作是在Map端且在内存进行的，所以其并不需要启动Reduce任务也就不需要经过shuffle阶段，从而能在一定程度上节省资源提高join效率。使用mapjoin将表“cga_info8”和“cga_info9”中同一个人的信息相关联。>select/*+mapjoin(age)*/*fromcga_info9ajoincga_info8bon=;+-----------+-----------+-----------+--------+--+||a.gender||b.age|+-----------+-----------+-----------+--------+--+|YuanJing|male|YuanJing|10||Liyuan|male|Liyuan|20|+-----------+-----------+-----------+--------+--+2rowsselected(25.129seconds)Hive操作HBase进入HBaseshell。>hbaseshell建立HBase表“student”。>create'student','info'0row(s)in0.4750seconds=>Hbase::Table–student向HBase表‘student’中输入信息。>put'student','001','info:name','lilei'0row(s)in0.1310seconds>put'student','002','info:name','tom'0row(s)in0.0210seconds查看表“student”中的信息。>scan'student'ROWCOLUMN+CELL001column=info:name,timestamp=1523544015712,value=lilei002column=info:name,timestamp=1523544040443,value=tom2row(s)in0.0370seconds下一步操作需要Hive作为客户端访问HBase获取数据，所以需要允许Hive模拟客户端用户。在Hive的“服务配置”中，选择参数类别为“全部配置”，设置“hive.server2.enable.doAs”的参数为“true”。修改完成后点击“保存配置”勾选“重新启动受影响的服务或实例”后，点击“确定”，更新配置和重启角色实例。创建Hive外部表“cga_hbase_hive”关联该“student”表。>createexternaltablecga_hbase_hive(keyint,gidmap<string,string>)storedby'org.apache.hadoop.hive.hbase.HBaseStorageHandler'withSERDEPROPERTIES("hbase.columns.mapping"="info:")TBLPROPERTIES(""="student");Norowsaffected(0.433seconds)查询表“cga_hbase_hive”中的内容。>select*fromcga_hbase_hive;+---------------------+---------------------+--+|cga_hbase_hive.key|cga_hbase_hive.gid|+---------------------+---------------------+--+|1|{"name":"lilei"}||2|{"name":"tom"}|+---------------------+---------------------+--+2rowsselected(0.477seconds)查询表“cga_hbase_hive”中和姓名相关的内容。>selectgid['name']fromcga_hbase_hive;+--------+--+|_c0|+--------+--+|lilei||tom|+--------+--+2rowsselected(0.733seconds)由实验结果可以看出完成了Hive表与HBase表的关联操作。Hive小文件合并查看HDFS的文件夹/user/hive/warehouse/cga_info1中的内容。>hdfsdfs-ls-h/user/hive/warehouse/cga_info1Found2items……stu01hive172018-04-1315:32/user/hive/warehouse/cga_info1/hive1.log……stu01hive152018-04-1315:32/user/hive/warehouse/cga_info1/hive2.log文件夹/cga/cg中有2个文件。在Hive客户端修改参数，将是否合并

Reduce

输出文件设定为“true”。>sethive.merge.mapredfiles=true;Norowsaffected(0.037seconds)建立新表“cga_info10”将表“cga_info1”的内容载入其中。>createtablecga_info10asselect*fromcga_info1;Norowsaffected(20.93seconds)查看表cga_info10的数据。>hdfsdfs-ls-h/user/hive/warehouse/cga_info1018/04/1315:38:23INFOhdfs.PeerCache:SocketCachedisabled.Found1items-rw-------+3stu01hive1102018-04-1315:34/user/hive/warehouse/cga_info10/000000_0结果显示，由于步骤2的修改参数设定，本来Reduce阶段应该输出的2个小文件已经被合并成了一个。Hive列加密Hive列加密目前常用AES算法进行加密。需在建表时指定，AE