第13章-基于Hive的交互式数据处理

第13章

基于Hive的交互式数据处理

Hive系统架构与安装数据预处理

创建数据仓库

数据分析12《大数据技术》1.1Hive系统架构Hive是基于HDFS和MapReduce架构的数据仓库，提供类SQL的HQL语言操作结构化数据。Hive的基本原理是将HQL语句转换成MapReduce任务，从而完成对Hadoop集群海量数据的查询和分析。1.Hive系统架构与安装《大数据技术》34《大数据技术》Hive的特点Hive构建在批处理的Hadoop之上，有较高的延迟，因此Hive不能在大数据集上实现低延迟的快速查询，如联机事务处理(OLTP)。Hive不提供实时查询和基于行级的数据更新操作。Hive的最佳使用场合是大数据离线分析。1.Hive系统架构与安装Hive架构Hive是运行在Hadoop基础之上的数据仓库工具，主要由用户接口、驱动程序和元数据存储系统3部分组成。《大数据技术》51.Hive系统架构与安装6《大数据技术》1.2安装与配置从“/”下载各种版本的Hive安装包，本书使用的是apache-hive-4.0.0-alpha-2-bin.tar.gz文件。执行解压Hive安装包，然后重命名，命令如下。[root@masterswxy]#tar-zxvfapache-hive-4.0.0-alpha-2-bin.tar.gz[root@masterswxy]#mvapache-hive-4.0.0-alpha-2-binhive-4.0.01.Hive系统架构与安装7《大数据技术》配置Hive（1）在目录“conf”中创建hive-site.xml文件，代码如下：<property><name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:mysql://master:3306/metastore?createDatabaseIfNotExist=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true

</value></property><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value></property>1.Hive系统架构与安装8《大数据技术》<property><name>javax.jdo.option.ConnectionUserName</name><value>root</value></property><property><name>javax.jdo.option.ConnectionPassword</name><value>123456</value></property><!--指定存储元数据要连接的地址--><property><name>hive.metastore.uris</name><value>thrift://master:9083</value><description>URIforclienttoconnecttometastoreserver</description></property>1.Hive系统架构与安装9《大数据技术》<!--指定hiveserver2连接的host--><property><name>hive.server2.thrift.bind.host</name><value>master</value></property><!--指定hiveserver2连接的端口号--><property><name>hive.server2.thrift.port</name><value>10000</value></property>1.Hive系统架构与安装10《大数据技术》<!--hiveserver2的高可用参数，如果不开会导致了开启tezsession导致hiveserver2无法启动--><property><name>hive.server2.active.passive.ha.enable</name><value>true</value></property><!--解决Errorinitializingnotificationeventpoll问题--><property><name>hive.metastore.event.db.notification.api.auth</name><value>false</value></property></configuration>1.Hive系统架构与安装11《大数据技术》（2）复制javaconnector到依赖库：执行下面命令。cpmysql-connector-java-8.0.18.jar/home/swxy/hive-4.0.0/lib/（3）配置profile文件在/etc/profile文件中，将下列环境变量设置代码放在该文件的尾部。exportHIVE_HOME=/home/swxy/hive-4.0.0exportPATH=$PATH:$HIVE_HOME/bin使上述配置文件生效。source/etc/profile1.Hive系统架构与安装12《大数据技术》（4）初始化元数据以下命令只要执行一次[root@master~]#schematool-dbTypemysql-initSchema启动并验证Hive要启动Hive，必须保证Hadoop和MySQL已经启动，可以用“servicemysqldstatus”命令查看MySQL的状态。在Hadoop和MySQL已经启动的条件下，在master启动Hive，命令如下，以下二条命令需要打开二个窗口。[root@master~]#hive--servicemetastore[root@master~]#hiveserver21.Hive系统架构与安装13《大数据技术》Hive可视化客户端的使用（IDEA）（1）在IDEA中创建任意工程，以Java项目为例，选择菜单File→NewProject→Java，创建项目后选择Database标签配置HiveDriver驱动。具体步骤是：Database→+→Driver→ApacheHive→右边DriverFiles选中hive-jdbc-3.1.2-standalone.jar。1.Hive系统架构与安装14《大数据技术》（2）配置Hive数据源，具体步骤是：DataSource→ApacheHive→设置Host（Master）、Port（10000）、User（root）、URL(默认为jdbc:hive2://master:10000）→点击下面的TestConnection，如果打勾表示连接成功。1.Hive系统架构与安装15《大数据技术》配置Hive数据源1.Hive系统架构与安装16《大数据技术》编写HQL在项目的src下新建File,命名为hive.sql，编写语句,例如（showdatabases;），选中语句后右击execute,第一次运行时创建一个新的Session，Result面板会显示结果，也可以点击代码区上方的工具栏In-EditorResults,这样结果就会显示在SQL窗口文件中显示，更方便查看。显示以下结果表示配置成功。1.Hive系统架构与安装《大数据技术》172.1数据扩展查看数据：执行“less”命令查看“movies_metadata.csv”文件内容。release_date,title,popularity,runtime,vote_average,vote_count2017-09-14,Leatherface,9.742082,90,5.7,622017-09-14,Porto,2.152189,75,7.8,52017-09-01,God'sOwnCountry,2.595488,105,5.4,82.数据预处理《大数据技术》18数据扩展：扩展movies_metadata.csv文件，增加年、月、日3个新字段。#!/bin/bash#infile=/root/hivedata/movies_metadata.csvinfile=$1#outfile=/root/hivedata/movies_metadata_ext.csvoutfile=$2awk-F","'{print$0","substr($1,1,4)","substr($1,6,2)","substr($1,9,2)}'$infile>$outfile2.数据预处理《大数据技术》19把movies_metadata_extend.sh文件复制到位后，请在/home/swxy目录下执行数据扩展命令。bashmovies_metadata_extend.shmovies_metadata.csvmovies_metadata_ext.csv提示”'forreading(Nosuchfileordirectory)”表示找不到目录，需要设置movies_metadata_extend.sh文件的格式，具体设置如下。[root@masterhivedata]#vimmovies_metadata_extend.sh查看格式，如下。:setff设置格式为unix。:setff=unix使用命令“lessmovies_metadata_ext.csv”查看结果，出现字符”^M”，使用sed命令可以去掉^M符号。[root@masterhivedata]#sed-i"s/\r//g"movies_metadata_ext.csv2.数据预处理《大数据技术》20用“less”命令查看一下其内容，部分结果如下所示。release_date,title,popularity,runtime,vote_average,vote_count,rele,se,da2017-09-14,Leatherface,9.742082,90,5.7,62,2017,09,142017-09-14,Porto,2.152189,75,7.8,5,2017,09,142017-09-01,God'sOwnCountry,2.595488,105,5.4,8,2017,09,012.数据预处理《大数据技术》212.2数据过滤有些数据（例如某些字段为空）需要进行过滤，需要编写过滤处理的BashShell文件movies_metadata_filter.sh，对movies_metadata_ext.csv文件进行处理，文件内容如下。#!/bin/bash#infile=/root/hivedata/movies_metadata_ext.csvinfile=$1#outfile=/root/hivedata/movies_metadata_ext_flt.csvoutfile=$2awk-F","'{if($1!=""&&$2!="")print$0}'$infile>$outfile2.数据预处理《大数据技术》22设置movies_metadata_filter.sh文件格式为unix。:setff=unix把movies_metadata_filter.sh文件复制到位后，执行如下命令。[root@masterhivedata]#bashmovies_metadata_filter.shmovies_metadata_ext.csvmovies_metadata_ext_flt.csv2.数据预处理《大数据技术》232.3数据上传将movies_metadata.csv上传到HDFS中了，可以在Web界面上传文件（打开浏览器并输入master:9870）hadoopfs-mkdir/movieshadoopfs-mkdir/movies/20231015hadoopfs-put/root/hivedata/movies_metadata_ext_flt.csv/movies/202310152.数据预处理24《大数据技术》3.创建数据仓库3.1基本命令执行“createdatabasejyphive”命令来创建一个数据仓库。执行“usejyphive”命令打开创建的数据仓库。1.内部表createtableifnotexistsmovie( idint, yearstring, namestring)rowformatdelimitedfieldsterminatedby',';25《大数据技术》3.创建数据仓库2.外部表createexternaltableifnotexistsmovie_ext( idint, yearstring, namestring)rowformatdelimitedfieldsterminatedby','location'/movies/20231015';26《大数据技术》3.创建数据仓库3.2创建Hive分区表1.分区表数据加载--静态分区静态分区是指分区的属性值是由用户在加载数据的时候手动指定的，语法如下。loaddata[local]inpath'filepath'intotabletablenamepartition(分区字段='分区值'...)27《大数据技术》3.创建数据仓库1）通过partitionedby创建分区表。createtableifnotexistsmovie_part( idint, yearstring, namestring)partitionedby(ystring)--这里是分区字段，不能与表中字段相同rowformatdelimitedfieldsterminatedby',';28《大数据技术》3.创建数据仓库2）加载本地文件到分区目录下loaddatalocalinpath'/root/hivedata/movie_titles_2020.csv'intotablemovie_partpartition(y=2020);loaddatalocalinpath'/root/hivedata/movie_titles_2021.csv'intotablemovie_partpartition(y=2021);loaddatalocalinpath'/root/hivedata/movie_titles_2022.csv'intotablemovie_partpartition(y=2022);3）通过分区表查询数据效果明显增加。（注意这里的条件是y=2022,如果写成year=2022表示还是使用非分区表查询数据）select*frommovie_partwherey=2022;29《大数据技术》3.创建数据仓库4）通过WebUI查看分区表movie_part的数据30《大数据技术》3.创建数据仓库2.多重分区表（1）上传company_gd_gz.txt、company_gd_zs.txt、company_hn_cs.txt、company_hn_yy.txt文件到虚拟机/root/hivedata目录下（2）创建表并加载上述4个文件到HDFS中。

createtablecompany_part( idint, namestring, addressstring

)

partitionedby(provincestring,citystring)

rowformatdelimitedfieldsterminatedby',';31《大数据技术》3.创建数据仓库loaddatalocalinpath'/root/hivedata/company_gd_gz.txt'intotablecompany_part partition(province='guangdong',city='guangzhou');loaddatalocalinpath'/root/hivedata/company_gd_zs.txt'intotablecompany_part partition(province='guangdong',city='zhongshan');loaddatalocalinpath'/root/hivedata/company_hn_cs.txt'intotablecompany_part partition(province='hunan',city='changsha');loaddatalocalinpath'/root/hivedata/company_hn_yy.txt'intotablecompany_part partition(province='hunan',city='yueyang');（3）测试select*fromcompany_partwhereprovince='guangdong'andcity='zhongshan';（4）通过WebUI查看桶表company_part的数据。32《大数据技术》3.创建数据仓库

按省份进行分区点击province=guangdong，进入广东省，按城市分区情况如下。

按城市进行分区33《大数据技术》3.创建数据仓库3.分区表数据加载--动态分区（1）动态分区是指分区字段值是基于查询结果自动推断出来的。使用动态分区，需要设置二个参数：

sethive.exec.dynamic.partition=true;--是否开启动态分区功能

sethive.exec.dynamic.partition.mode=nonstrict;--非严格模式34《大数据技术》3.创建数据仓库（2）创建表company_all

createtablecompany_all( idint, namestring, addressstring, provincestring, citystring

)rowformatdelimitedfieldsterminatedby',';35《大数据技术》3.创建数据仓库（3）创建表company_part_dynamic，并使用insertselect语句添加数据。

createtablecompany_part_dynamic( idint, namestring, addressstring, provincestring, citystring

)partitionedby(prostring)

rowformatdelimitedfieldsterminatedby',';

insertintotablecompany_part_dynamicpartition(pro)

selecttmp.*,vincefromcompany_alltmp;36《大数据技术》3.创建数据仓库3.3创建Hive分桶表分桶表是一种用于优化查询而设计的表类型，对应的数据文件在底层被分解为若干个部分，通俗来说就是被拆分成若干个独立的小文件。在分桶时，要指定根据哪个字段将数据分为几桶。37《大数据技术》3.创建数据仓库（1）开启分桶的功能，从Hive2.0开始不再需要设置。sethive_enforce.bucketing=true;（2）创建电影表。createtablemovie( idint, yearint, namestring)rowformatdelimited fieldsterminatedby',';38《大数据技术》3.创建数据仓库（3）上传源数据文件movie_titles.csv到HDFS的/hive/warehouse/jypdb.db/movie目录下。（4）创建分桶表，根据年份year把数据分为5桶，并按name排序。createtablemovie_bucket( idint, yearint, namestring)clusteredby(year)--分桶字段year一定要是表中的字段sortedby(name)into5buckets;--指定分桶内的数据排序规则39《大数据技术》3.创建数据仓库（5）使用insert+select语法将数据加载到分桶表中。

insertintomovie_bucketselect*frommovie;（6）测试。

select*frommovie_bucketwhereyear=2022;（7）通过WebUI查看桶表movie_bucket的数据。40《大数据技术》3.创建数据仓库分桶表有以下三个好处。（1）基于分桶字段查询时，减少全表扫描。（2）JOIN时可以提高MR程序效率，减少笛卡尔积数量（二张表按照关联字段分桶）。（3）分桶表数据进行高效抽样。41《大数据技术》3.创建数据仓库3.4Hive内置函数1.查看函数（1）查看系统自带函数showfunctions;（2）显示自带的函数的用法descfunctionceiling;（3）详细显示自带的函数的用法descfunctionextendedceiling;42《大数据技术》3.创建数据仓库2.Hive的窗口函数普通的聚合函数每组只返回一个值，而开窗函数可以为窗口中的每行都返回一个值。开窗函数一般就是指over()函数，其窗口是由一个over子句定义的多行记录。开窗函数一般分为聚合开窗函数和排序开窗函数。43《大数据技术》3.创建数据仓库（1）聚合开窗函数聚合开窗函数主要包括sum(求和)、min(最小)、max(最大)、avg(平均值)和count(计数)学生表中相同科目作为一个分区，按照分区求平均分，同一分区的分数降序进行显示。语句如下。selectname,subject,score,avg(score)over(partitionbysubjectorderbyscoredesc)asavgfromstudent;44《大数据技术》3.创建数据仓库45《大数据技术》3.创建数据仓库（2）排序开窗函数RANK()排序相同时会重复，总数不会变DENSE_RANK()排序相同时会重复，总数会减少ROW_NUMBER()会根据顺序计算selectname,subject,score,rank()over(partitionbysubjectorder