大数据技术原理与操作应用 第7章 Hive 数据仓库

第7章Hive数据仓库1Hive的相关功能和特点Hive的安装和配置HiveQL的相关操作2✎学习目标了解熟悉了解掌握了解Hive的相关功能12了解Hive的特点4Hive的安装和配置3掌握HiveQL的相关操作章节概要ApacheHive是架构在Hadoop之上的一个数据仓库工具，是一款为Hadoop解决大数据管理和处理难题的框架，可以方便地进行大数据存储、查询和分析。Hive能够将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能，能够将SQL语句转变成MapReduce任务来执行，十分适合数据仓库的统计分析。

7.1Hive基础7.1.1数据仓库概述1)数据仓库面向主题2)数据仓库数据集成3)数据仓库不可更新4)数据仓库随时间变化(1)数据仓库特征

7.1Hive基础7.1.1数据仓库概述数据仓库系统是一个信息提供平台,它从业务处理系统获得数据,主要以星型模型和雪花型模型进行数据组合,并为用户提供各种手段,从数据中获取信息和知识。1)业务建模2)领域概念建模3)逻辑建模4)物理建模(3)数据仓库的数据建模

7.1Hive基础7.1.1数据仓库概述数据仓库并不是所谓的“大型数据库”,它的建设目的是为了进一步从海量数据集中挖掘数据资源,从而进行决策。数据库一般称为联机事务处理OLTP(On-LineTransactionProcessing),是面向事务设计的,数据量较少,在设计上应尽量避免冗余,可实现更新操作。数据仓库一般称为联机分析处理OLAP(On-LineAnalyticalProcessing),是面向主题设计的,存储的一般是历史数据,在设计上期望冗余,不支持更新操作,只能读取和追加数据。(2)数据仓库和数据库的区别

7.1Hive基础7.1.2 Hive简介Hive最初由Facebook开发,用于Facebook的日志分析,后来成为Apahce软件基金会的一个开源的顶级项目,是一个通用的、可伸缩的数据处理平台,很多公司和组织将Hive作为大数据平台中用于数据仓库分析的核心组件。Hive是基于Hadoop的一套数据仓库分析系统,用来对海量结构化数据进行提取、转化、加载、存储、查询和分析,可以将存储在Hadoop分布式文件系统(如HDFS)中的海量结构化数据映射为数据库表,并提供丰富的类SQL查询方式来进行分析。这套SQL又称为“HiveSQL”,简称“HQL”。HQL可以使得不熟悉MapReduce的用户能很方便地利用SQL语言查询、汇总和分析数据,Hive最终将这些HQL语句转换为MapReduce任务进行处理。Hive构建在基于静态批处理的Hadoop之上,并不能够在大规模数据集上实现低延迟快速的查询,也不适合像联机事务处理这类的高实性的应用。Hive最适合像网络日志分析这类的基于大量不可变的批处理作业。

7.1Hive基础7.1.3 Hive组成架构

7.1Hive基础7.1.3 Hive组成架构(1)用户接口用户接口也就是Hive的客户端,主要有三个:Web接口、CLI接口和JDBC/ODBC客户端。用户可以通过Hive的用户接口与底层数据存储系统进行互动。Web接口就是通过Web浏览器访问、操作和管理Hive,非常方便直观。CLI接口即Hive的命令行接口,这是最常用的客户端。JDBC/ODBC客户端指应用程序通过JDBC或ODBC协议连接至Hive,从而发送执行命令。

7.1Hive基础7.1.3 Hive组成架构(2)Hive驱动引擎Hive驱动引擎是Hive的核心,包括四个部分:SQLParser解析器、PhysicalPlan编译器、QueryOptimizer优化器和Execution执行器。用户通过Hive接口发送HQL,Hive通过驱动引擎结合MetaStore元数据,将HQL翻译成MapReduce去执行。首先,使用SQLParser解析器将HQL语句转换成语法树,接着使用PhysicalPlan编译器将语法树编译为逻辑执行计划,然后通过QueryOptimizer优化器对逻辑执行计划进行优化,最后将优化的结果使用Execution执行器调用底层MapReduce框架执行逻辑执行计划。

7.1Hive基础7.1.3 Hive组成架构（3）元数据库Hive选择一些关系型数据库(如MySQL、Derby等),用来存放Hive的基础信息。默认使用的是Derby。(4)HadoopHive是构建在Hadoop之上的,其数据文件存储在底层的HDFS或HBASE中,通过Ma-pReduce框架完成Hive的查询工作,并将结果返回给客户端。

7.1Hive基础7.1.4Hive工作原理

7.1Hive基础7.1.4Hive工作原理(1)ExecuteQuery用户通过Hive接口(如命令行或Web接口)发送查询任务给驱动程序。(2)GetPlan编译器获得该用户的任务Plan,对查询进行分析。(3)GetMetaData编译器根据用户任务发送元数据请求到元数据库,去获取需要的Hive的元数据信息。(4)SendMetaData元数据库发送元数据给编译器。

编译器得到元数据信息后,对任务进行编译。

先将HiveQL转换为抽象语法树,进而转换成查询块,然后将查询块转化为逻辑查询计划,并重写逻辑查询计划,将其转换为物理计划,最后进行调优,选择最佳的策略。(5)SendPlan编译器重新发送计划给驱动程序。

到此为止,查询分析和编译完成。(6)ExecutePlan驱动程序发送执行计划到执行引擎。

7.1Hive基础7.1.4Hive工作原理(7)ExecuteJob在Hive内部,执行作业的过程实际上是一个MapReduce工作。执行引擎发送作业给JobTracker,NameNode

将作业分配给数据节点的TaskTracker,执行Map和Reduce工作。任务会直接读取HDFS中的文件进行相应的操作。(8)MetaDataOps执行引擎可以通过MetaStore执行元数据操作,这里的操作与第(7)步骤同时执行。(9)FetchResults执行引擎接收来自数据节点的结果。(10)SendResults执行引擎发送结果给驱动程序。(11)SendResults驱动程序将结果返回给用户。

7.1Hive基础7.1.5 Hive内置数据类型表7.1Hive基础数据类型数据类型长度描述TINYINT1字节（8位）有符号整型SMALLINT2字节（16位）有符号整型INT4字节（32位）有符号整型BIGINT8字节（64位）有符号整型FLOAT4字节（32位）有符号单精度浮点数DOUBLE8字节（64位）有符号双精度浮点数DECIMAL-可带小数的精确数字字符串BOOLEAN-布尔类型TRUE/FALSEBINARY-字节序列STRING-字符串CHAR最大的字符数：255长度固定字符串VARCHAR字符数范围：1~65535长度不定字符串TIMESTAMP-时间戳，它支持传统的UNIX时间戳可选纳秒的精度DATE-日期，格式：YYYY-MM-DD

7.1Hive基础7.1.5 Hive内置数据类型表7.2Hive复杂数据类型数据类型描述ARRAY由一系列相同数据类型的元素组成，可通过下标访问这些元素，且下标从0开始MAP由一组无序的键值对组成，可通过键来访问元素。键的类型必须是原子的，值可以是任何类型STRUCT可以包含不同数据类型的元素，通过“点语法”的方式来访问元素UNION联合体，是异类的数据类型的集合，从Hive0.7.0开始支持。元素共享内存，同一时刻同一地点只有联合体中的一个元素生效

7.1Hive基础7.1.6 Hive数据模型Hive的数据存储是基于HDFS的,它没有专门的存储格式,在默认情况下,Hive使用制表符来分割列与列之间的间隔,用户也可以在创建表时自由指定数据中的列分隔符和行分隔符。Hive的数据模型主要有:数据库(Database)、表(Table)、分区(Partition)

和桶(Bucket)。

7.1Hive基础7.1.6 Hive数据模型(1)数据库(Database)Hive中的数据库相当于关系型数据库中的命名空间,作用是将数据库应用隔离到不同的数据库模式中,在HDFS中表现为“﹩{hive.metastore.warehouse.dir}”目录下的一个文件夹。(2)表(Table)Hive中的表本质上是Hadoop文件系统中的目录或文件,在默认情况下,表的存放路径位于数据库目录下,按表名进行文件夹区分。表可以过滤、投影、连接和联合。

7.1Hive基础7.1.6 Hive数据模型Hive常见的数据表的类型有内部表、外部表、分区表和桶表。1)内部表内部表(ManagerTable)也称为“管理表”,其数据由Hive自身管理,是Hive默认的表类型。内部表的数据会存放在HDFS的特定位置中,一般在Hive的数据库目录下。当删除内部表时,Hive将删除表中的数据和元数据。2)外部表外部表(ExternalTable)使用external修饰,其数据由HDFS管理,数据的存放位置可以由用户任意指定。外部表的数据除了Hive外,其他的工具(如Pig)也可以使用。创建外部表时,Hive仅记录数据所在的路径,不对数据的位置作任何改变。删除外部表时,Hive只删除表的元数据,表中的数据并未删除。因此,相对来说,外部表较内部表安全,数据组织更加灵活,方便数据共享。

7.1Hive基础7.1.6 Hive数据模型3)分区表分区表(PartitionTable)的存放路径在HDFS中表现为表目录下的子目录,该子目录以分区值命名。分区表就是将表进行水平切分,将表数据按照某种规则存储,以提高查询效率。4)桶表桶表(BucketTable)是同一个表目录下根据Hash散列之后的多个文件。在处理大规模数据集时,如果分区不能更细粒度地划分数据,可以采用分桶技术来更细粒度地划分和管理数据,一般用于高效率的数据采样。

7.1Hive基础7.1.6 Hive数据模型(3)分区(Partition)在大数据应用中进行全表扫描非常耗费资源,Hive可以为频繁使用的数据建立分区,分区是表的部分列的集合。这样查找分区中的数据时就不需要扫描全表,就能极大地提高查找效率。例如,一个表里存储了一年的数据,但很多时候只想查询其中一天的数据,而为了这一天的数据去扫描全表是相当不划算的操作。因此,建表时可以指定按天分区,这样在后续应用中可以按分区查找,以提高效率。在实际生产环境中,由于业务数据量较大,一般都会对表进行分区。

7.1Hive基础7.1.6 Hive数据模型(4)桶(Bucket)桶(Bucket)是通过对指定列进行Hash计算来实现的,通过哈希值来进行数据切分,每个桶对应于该列下的一个存储文件。分区和分桶最大的区别:分桶是随机分割数据,而分区并非随机分割;分桶存储在文件中,而分区存放在文件夹中。

7.2 Hive的安装部署Hive是一个客户端工具,没有集群的概念,因此,无须每台机器都安装,哪个节点需要就安装在哪个节点上。Hive会选择一些第三方数据库(如MySQL、Derby等),存放Hive的元数据信息。本章选用MySQL来存储Hive元数据,在安装Hive客户端前,需要先安装MySQL数据库。安装MySQL本章使用的MySQL版本是MySQL5.7。以下操作均使用root账户。①检查MySQL是否安装“yumlistinstall|grepmysql”。

如果已经安装了MySQL,但是版本并不是所需要的,可以先全部卸载:“yum-yremove+数据库名称”。②下载MySQL源安装包。将MySQL源进行安装:“yumlocalinstallmysql57-community-re-alease-el7-8.noarch.rpm”。mysql源安装完成后,需要检查是否安装成功:yumrepolistenabled|grep"mysql.∗-community.∗",如图7.3所示即为安装成功。图7.3mysql源安装结果

7.2 Hive的安装部署③安装MySQL服务:yuminstallmysql-community-server。④启动MySQL服务并设置开机自启动。systemctlstartmysqldsystemctlenablemysqldsystemctldaemon-reload查看MySQL服务状态：systemctlstatusmysqld，如图7.4所示，MySQL服务为运行状态。图7.4查看MySQL服务状态

7.2 Hive的安装部署⑤设置MySQLroot用户密码。在默认情况下,root用户没有密码。为了安全性考虑,还需要设置root用户密码。首先使用命令“grep'temporarypassword'/var/log/mysqld.log”查看MySQLroot用户的临时密码。如图7.5所示,MySQLroot用户的临时密码为“e<?n<GuD3opt”。7.5查看MySQL的临时密码

7.2 Hive的安装部署⑥创建MySQLHive账户。登录MySQL后输入命令“createuser‘hive’identifiedby‘Hive@123’;”来创建Hive账户。并将MySQL所有权限授予Hive账户,命令为“grantallon∗.∗to‘hive’@‘masteridentifiedby’Hive@123‘;”。最后使用命令“flushprivileges;”使前边的命令生效。可在MySQL命令行中输入命令“selecthost,userfrommysql.user;”,查看是否有Hive用户。⑦创建数据库。使用Hive账户登录MySQL,命令为“mysql-hmaster-uhive-pHive@123;”,其中master为主机名称。执行命令“createdatabasehivedb;”,创建名为“hivedb”的数据库。可通过“showdatabases;”命令查看“hivedb”数据库是否创建成功。⑧使用“quit;”命令退出MySQL。至此,MySQL安装配置完成。

7.2 Hive的安装部署1)下载并解压Hive在网上下载Hive安装包“apache-hive-1.2.2-bin.tar.gz”,并将其上传至master节点的“/home/apache/package”目录下,并在该目录下使用解压命令解压Hive至“/home/apache/soft”目录:“tar-zxvfapache-hive-1.2.2-bin.tar.gz-C/home/apache/soft”。

修改解压包名称“mvapache-hive-1.2.2-binhive-1.2.2”。2)修改配置文件hive-site.xml默认情况下hive-site.xml并不存在,需要先复制一个。

进入Hive安装目录“/home/a-pache/soft/hive-1.2.2/conf”,复制“hive-site.xml”文件“cphive-default.xml.templatehive-site.xml”。

编辑“hive-site.xml”文件:“vihive-site.xml”。(2)安装Hive

7.2 Hive的安装部署①在Linux命令输入模式下输入“/javax.jdo.option.ConnectionDriverName”搜索“javax.jdo.option.ConnectionDriverName”属性,将其修改为“com.mysql.jdbc.Driver”。②修改属性“javax.jdo.option.ConnectionURL”,将连接MySQL的url修改为“jdbc:mysql://master:3306/hivedb?characterEncoding=UTF-8”。③修改连接数据库的用户名和密码。连接用户名为“hive”，密码为“Hive@123”。(2)安装Hive

7.2 Hive的安装部署④修改Hive数据目录。新建目录“mkdir-p/home/apache/data/hive/io”,用于存放Hive的数据。修改“hive-site.xml”中的内容如下:(2)安装Hive<property><name>hive.querylog.location</name><value>/home/apache/data/hive/io</value><description>LocationofHiveruntimestructuredlogfile</description></property><property><name>hive.exec.local.scratchdir</name><value>/home/apache/data/hive/io</value><description>LocalscratchspaceforHivejobs</description></property><property><name>hive.downloaded.resources.dir</name><value>/home/apache/data/hive/io</value><description>Temporarylocaldirectoryforaddedresourcesintheremotefilesystem.</description></property>

7.2 Hive的安装部署下载MySQL驱动包“mysql-connector-java-5.1.38.jar”,将其上传到Hive的lib目录下。如果不添加驱动包,执行初始化时会抛出异常。(3)添加MySQL驱动包(4)配置Hive环境变量切换到root用户,打开“vi/etc/profile”文件,添加Hive相关的环境变量,如图7.6所示。保存并退出后,执行命令“source/etc/profile”,使配置文件生效。图7.6配置Hive环境变量(5)启动Hive首先启动Hadoop集群,然后直接在终端输入“hive”命令,即可启动Hive。

7.3 Hive基本操作7.3.1数据库相关操作Hive数据库是用来组织数据表的,本质上是数据仓库下的一个目录。创建数据库的语法为“createdatabase[ifnotexists]database_name;”。其中“[ifnotex-ists]”是可选的,如果使用了,表示只有当数据库不存在时才进行创建,如果有同名数据库存在,则不执行该创建数据库的命令。例如,创建一个名为“apachedb”的数据库。数据库创建完成后,可以在HDFS中查看到它的存储位置。默认存储在“/user/hive/warehouse”目录下,如图7.7所示。(1)创建数据库图7.7HDFS中对应数据库所在目录

7.3 Hive基本操作7.3.1数据库相关操作使用命令“showdatabases;”可查看创建的所有数据库。如果想要查看某一个数据库的详细信息,可使用关键字describe,具体的命令为“describedatabasedatabase_name;”,如图7.8所示。(2)查看数据库图7.8查看数据库信息

7.3 Hive基本操作7.3.1数据库相关操作Hive默认情况下当前的数据库是default,如果想使用其他的数据库,可使用use关键字进行切换。例如,要切换到apachedb

数据库,使用的命令为“useapachedb;”。为了方便查看目前正在使用的数据库是哪一个,可以在Hive的安装目录的bin目录下创建隐藏文件“.hiv-erc”,并在该文件中输入“sethive.cli.print.current.db=true;”。这样就可以在Hive的命令提示符中显示当前的数据库名称。(3)切换数据库

7.3 Hive基本操作7.3.1数据库相关操作可使用“drop”关键字删除数据库,语法为“dropdatabase[ifexists]database_name;”。例如,删除数据库apachedb,如图7.10所示。(4)删除数据库图7.8删除数据库apachedb

7.3 Hive基本操作7.3.2数据表相关操作数据表包括内部表、外部表、分区表和桶表,它们有不同的特征。创建表有两种不同的方式:按照数据目录,可以创建内部表和外部表;按照数据的管理方式,可以创建分区表和桶表。创建表的语法格式为:(1)创建表create[external]table[ifnotexists]table_name(column1data_type[comment'字段注释']，column2data_type[comment'字段注释'],...)[comment'表注释'][partitionedby(columndata_type,...)][clusteredby(column1,column2,...)][rowformatdelimitedfieldsterminatedby'\t'linesterminatedby'\n'storedastextfile][location'表的存储目录'][asselect_statement][likeexisting_table]

7.3 Hive基本操作7.3.2数据表相关操作①external关键字用于指定创建外部表,若未指定,默认是创建内部表。②可以使用comment关键字对表的字段和表添加注释。③partitionedby(columndata_type,…):用于创建分区表,指定分区的字段,且partitioned里的字段不能是表中声明的字段,必须是一个新字段。④clusteredby(column1,column2,…):用于创建桶表,指定按照哪些字段进行分桶。clustered里的字段必须要是表字段中出现的字段。⑤rowformatdelimited:用于指定行列的数据格式或分隔符,例如,可以指定字段间使用“\t”间隔,行间使用“\n”分隔。使用storedas指定文件的存储格式,默认为textfile。⑥location:创建外部表时需要使用location来指定表的存储位置,且location的位置必须是目录,不能是单个文件。⑦asselect_statement:查询建表法,通过复制另一张表的结构和表中所有数据来创建新表。⑧likeexisting_table:通过复制另一张表的结构来建表,不复制表中的数据。(1)创建表

7.3 Hive基本操作7.3.2数据表相关操作例如,分别用不同的方式创建一个员工表。1)创建内部表可按如下方式创建内部表“employee”,包括属性id(int类型)、name(string类型)、sex(string类型)、age(int类型)、department(string类型)。字段之间使用“,”号进行分隔,行之间使用“\n”分隔。(1)创建表createtableifnotexistsemployee(idintcomment'employeeid',namestringcomment'employeename',sexstring,age

int,departmentstring)rowformatdelimitedfieldsterminatedby','linesterminatedby'\n';

7.3 Hive基本操作7.3.2数据表相关操作创建外部表创建外部表需要指定关键字“external”,一般还需要指定一个外部路径,默认的路径是“/user/hive/warehouse/apachedb.db”下的以表名命名的目录。具体命令为:需要先创建好数据存储目录“/home/apache/data/hive/extbdata”。(1)创建表createexternaltableifnotexistsemployee_external(idintcomment'employeeid',namestringcomment'employeename',sexstring,age

int,departmentstring)rowformatdelimitedfieldsterminatedby','linesterminatedby'\n'location'/home/apache/data/hive/extbdata';

7.3 Hive基本操作7.3.2数据表相关操作3)创建分区表创建一个分区表“employee_partition”,以city进行分区。(1)创建表createtableifnotexistsemployee_partition(idintcomment'employeeid',namestringcomment'employeename',sexstring,age

int,departmentstring)partitionedby(citystring)rowformatdelimitedfieldsterminatedby','linesterminatedby'\n';

7.3 Hive基本操作7.3.2数据表相关操作4)创建桶表在创建桶表之前,需要先设置“sethive.enforce.bucketing=true”开启分桶功能,然后才能创建桶表。例如,将employee表按照id字段划分为4个桶。(1)创建表createtableifnotexistsemployee_bucket(idintcomment'employeeid',namestringcomment'employeename',sexstring,age

int,departmentstring)clusteredby(id)into4bucketsrowformatdelimitedfieldsterminatedby',';

7.3 Hive基本操作7.3.2数据表相关操作使用“showtables;”命令可查看数据库里面的所有表。如图7.11所示,可以看到数据库apachedb

中有刚刚创建的4个表。(2)查看表图7.11查看数据库中的表

7.3 Hive基本操作7.3.2数据表相关操作使用关键字“desc”可查看某个表的字段信息。例如,查看employee表的结构信息如图7.12所示。再加上关键字“formatted”,可以查看更详细的表信息。如图7.13显示的是查看“employee_external”的详细信息。(2)查看表图7.12查看employee表结构信息图7.13查看employee_external详情

7.3 Hive基本操作7.3.2数据表相关操作可以使用“altertable”命令来修改表的属性,该命令只是修改表的元数据,表中的数据不受影响。因此,需要保证表中的数据要与修改后的元数据模式匹配,否则原始表中的数据将变得不可用。(3)修改表1)修改表的名称语法为“altertabletable_namerenametonew_table_name;”。例如,将employee表的名称改为“employee2”。例如，将employee表的名称改为employee2。altertableemployeerenametoemployee2;

7.3 Hive基本操作7.3.2数据表相关操作(3)修改表2)修改表字段的定义①增加列使用addcolumns可以为表增加一个或多个列。例如,为employee2表增加string类型的province列和bigint类型的salary列,如图7.14所示。新添加的列位于该表的最后。图7.14增加新列

7.3 Hive基本操作7.3.2数据表相关操作(3)修改表2)修改表字段的定义②修改列定义使用change可以修改列名称、列注释、列类型和列的位置。例如,将employee2表的“id”字段重命名为“uid”,注释修改为“theuniqueid”,且将位置放在name之后。如图7.15所示。图7.15修改id字段信息

7.3 Hive基本操作7.3.2数据表相关操作(3)修改表2)修改表字段的定义③替换字段使用

replace将表中的所有字段替换成新的字段,相当于将整个表结构进行了重置。例如,将employee2表中的字段替换为id(int类型)、name(string类型)和department(string类型)。如图7.16所示。图7.16替换字段

7.3 Hive基本操作7.3.2数据表相关操作(3)修改表3)对分区表的分区进行操作为了方便进行测试,先使用like命令将“employee_partition”表复制一个新的分区表“employee_partition2”。createtableemployee_partition2likeemployee_partition;①增加分区在“employee_partiton2”表中增加city为beijing和tianjin两个分区。altertableemployee_partition2addpartition(city='beijing')partition(city='tianjin');

7.3 Hive基本操作7.3.2数据表相关操作(3)修改表可使用“showpartitions”命令查看分区情况,如图7.17所示。图7.17查看分区分区对应在HDFS上表现为数据存储目录,如图7.18所示。“employee_partition2”数据表目录下新增“city=beijing”和“city=tianjian”两个目录。图7.18新增分区

7.3 Hive基本操作7.3.2数据表相关操作(3)修改表②删除分区删除“employee_partition2”表中的分区“city='beijing'”和“city='tianjin'”。如图7.19所示。删除分区并没有删除HDFS上对应的目录,删除分区只是删除了元数据,对实际数据没有影响。图7.19删除分区

7.3 Hive基本操作7.3.2数据表相关操作(4)删除表使用drop命令可以删除表,例如,“droptableemployee;”命令可删除employee表。

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入Hive可以使用load、insert等操作命令来向表中装载数据,也可以使用insert子句将数据导出到HDFS。1)使用load方式导入数据load命令可以一次性向表中导入大量的数据,语法格式为:“loaddata[local]inpath'数据文件目录’[overwrite]intotabletable_name[partiton(分区值)];”。关键字local用于确定导入的文件是位于本地路径还是HDFS目录。加上local关键字,表示将本地文件复制并上传到HDFS指定目录。不加local,Hive

则是将HDFS上的数据移动到指定目录(而不是复制)。无论是本地的数据还是HDFS上的数据,都可以使用相对路径和绝对路径这两种方式进行导入。

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入①导入本地数据在本地“/home/apache/data/hive/data”目录下新建文件“employeeinfo1.txt”,内容如下:101,wanglan,male,23,marketing102,zhaojuan,femal,32,marketing103,yangran,femal,25,software104,lixiao,male,45,administration105,heyun,male,22,software106,zhouyang,male,30,software

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入将“/home/apache/data/hive/data”目录中的文件导入到内部表employee中:loaddatalocalinpath'/home/apache/data/hive/data/'intotableemployee;如果employee表中已有记录,但想将这些记录覆盖掉,可以加上overwrite关键字,命令为:loaddatalocalinpath'/home/apache/data/hive/data/'overwriteintotableemployee;如果是将数据导入到分区表,则需要在命令中指定分区值。例如,将“/home/apache/data/hive/data”目录下的文件导入到分区表“employee_partition”中,指定分区值为beijing。loaddatalocalinpath'/home/apache/data/hive/data/'intotableemployee_partitionpartition(city='beijing');

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入执行完上述命令语句后,“employeeinfo1.txt”文件中的数据全部导入“employee_partition”表的beijing分区中。在HDFS上可以看到,在“employee_partition”目录下有一个“city=beijing”的目录,里面存在文件“employeeinfo1.txt”,如图7.20所示。图7.20导入数据到分区表

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入②导入HDFS上的数据不加local关键字可以导入HDFS上的数据。这里先通过复制employee表来创建新表“employee_copy”,通过复制“employee_partition”来创建新的分区表“employee_partition3”。然后将“employeeinfo1.txt”文件上传到HDFS:“hdfsdfs-put/home/apache/data/hive/data/employeeinfo1.txt/hdfstest”。将HDFS中“/hdfstest”目录下的employeeinfo1.txt导入到employee_copy表中。loaddatainpath'/hdfstest/employeeinfo1.txt'intotableemployee_copy;

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入命令执行完后,“employeeinfo1.txt”由“/hdfstest”目录移动到“/user/hive/warehouse/apachedb.db/employee_copy”目录下。此时,进行“employee_partition3”数据表导入前,需要将本地的“employeeinfo1.txt”文件再次上传到HDFS的“/hdfstest”目录,然后才能执行导入命令。loaddatainpath'/hdfstest/employeeinfo1.txt'intotableemployee_partition3partition(city='beijing');执行完后,文件由“/hdfstest”目录移动到“/user/hive/warehouse/apachedb.db/employee_partition3/city=beijing”目录中。

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入2)使用insert方式导入数据Hive可利用insert关键字向表中插入数据,语法格式为:“insertintotabletable_name[partition(分区值)]values(值1,值2,…);”。也可以结合select子句构成查询插入方式,语法为:“insertintotabletable_name[partition(分区值)]select子句;”。这种方式实际上是将insert语句转换成MapReduce任务来执行。例如,往employee表中插入一条数据。insertintotableemployeevalues(107,'qianming','male',29,'software');

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入如果是分区表,则必须指定分区。例如,向分区表“employee_partition”中插入一条数据。insertintotableemployee_partitionpartition(city='beijing')values(107,'qianming','male',29,'software');结合select子句进行查询就是将select查询的结果插入到insert语句中的表中,select查询的所有字段都必须在insert后面的表中定义,且字段的格式需要一致。其示例如下:insertintotableemployee_copyselectid,name,sex,age,departmentfromemployee;

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入使用select子句可实现一次查询多次插入,这种方式可以减少查询操作扫描数据的次数,从而提高SQL语句的执行效率。先通过“employee_partition”表复制出三个结构相同的表,如图7.21所示。使用只查询一次的方式向这三个分区表插入数据,如图7.22所示。其中,employee_ptn2表在插入的过程中使用overwrite关键字实现数据覆盖。数据插入成功后,在HDFS中数据表目录下有一个“city=beijing”的目录,里面有一个名称为“000000_0”文件,这个就是数据表文件。可以使用dfs命令查看文件内容,如图7.23所示。

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入图7.21创建3个分区表图7.22使用insert实现查询一次多次插入图7.21使用dfs命令查看数据表文件内容

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入3)动态分区插入数据前面对分区表插入数据用的都是静态分区插入的方式,也就是手动指定分区。Hive也支持动态分区插入数据,即通过查询出来的分区字段的值来进行自动判断,从而创建出需要的分区,每一个不同分区值就会创建一个对应的分区。在默认情况下,Hive并没有开启动态分区功能,需要手动开启。在Hive命令行下输入以下两条命令:第一条是打开动态分区的开关,第二条是设置动态分区插入模式。sethive.exec.dynamic.partition=true;sethive.exec.dynamic.partition.mode=nonstrict;

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入设置好参数后,就可以利用动态分区往表中插入数据。创建表“employee_ptn4”,通过department字段分区。图7.24利用动态分区插入数据结果

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入需要注意的是,动态分区插入的分区字段必须位于select查询语句中出现的字段的末尾。上述命令中,select子句中的字段id、name、sex和age都是分区表“employee_ptn4”的普通字段,而最后一个department则是“employee_ptn4”的分区字段。

7.3 Hive基本操作7.3.3数据的导入导出(1)数据导入4)通过查询建表来加载数据Hive支持直接将查询出来的结果存储到新建的一张表里,新表的字段与查询语句出现的字段结构一样。例如,使用查询建表来创建一个表名为“employee_ctas”的表,并将employee表中的数据查询出来插入到“employee_ctas”中。createtableemployee_ctasasselectid,name,sex,departmentfromemployee;图7.25employee_ctas表结构及表中的数据

7.3 Hive基本操作7.3.3数据的导入导出(2)清空数据表中的数据使用truncate关键字可以清空数据表,语法为:“truncatetabletable_name;”。清空内部表、外部表和分桶表时,会将数据存储目录下的所有数据文件都删掉。而清空分区表时,只会清空分区下的数据文件,不会删掉分区。要将分区也删除,可使用dfs

命令完成。

7.3 Hive基本操作7.3.3数据的导入导出(3)数据导出使用insert命令可以将数据仓库中的数据导出到本地或者HDFS中,语法格式为:“insertoverwrite[local]directory导出目录数据分隔格式select子句;”。1)导出数据到本地将employee表中的字段为id、name、sex和department的数据导出到本地“/home/apache/data/hive/data”目录下。insertoverwritelocaldirectory'/home/apache/data/hive/data'rowformatdelimitedfieldsterminatedby','selectid,name,sex,departmentfromemployee;

7.3 Hive基本操作7.3.3数据的导入导出(3)数据导出命令执行完成后,本地“/home/apache/data/hive/data”目录下多了一个名为“000000_0”的文件,这个就是导出的文件,可以使用cat命令查看其内容,如图7.26所示。图7.26导出数据到本地

7.3 Hive基本操作7.3.3数据的导入导出(3)数据导出Hive也支持多模式导出,例如,将employee中的数据导出到本地“/home/apache/data/hive/data和/home/apache/data/hive/data2”目录中。fromemployeeinsertoverwritelocaldirectiory'/home/apache/data/hive/data'rowformatdelimitedfieldsterminatedby','selectid,nameinsertoverwritelocaldirectory'/home/apache/data/hive/data2'rowformatdelimitedfieldsterminatedby','selectid,name,department;

7.3 Hive基本操作7.3.3数据的导入导出(3)数据导出2)导出数据到HDFS在HDFS上新建目录“/user/hive/data/employee”,在终端中执行命令:“hdfs

dfs-mkdir–p/user/hive/data/employee”。选择employee表中的数据导出到HDFS的“/user/hive/data/employee”目录。insertoverwritedirectory'/user/hive/data/employee'rowformatdelimitedfieldsterminatedby','selectid,name,departmentfromemployee;使用dfs命令查看导出结果,如图7.27所示。图7.27数据导出到HDFS

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)select[all|distinct]select_expr,select_expr,...fromtable_name[wherewhereCondition][groupbycolList[havinghavingCondition]][clusterbycolList|[distributebycolList][sortby|orderbycolList]][limitnumber];

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(1)select语句“select…from”语句可实现从from指定的表中查询出select指定的数据。1)全表查询查询出employee表的所有数据,结果如图7.28所示。图7.28全表查询

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(1)select语句选择特定列查询查询出employee表的id和name两列信息,结果如图7.29所示。图7.29选择特定列查询

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(1)select语句3)设置别名可以为列和表加上别名,尤其在多表联合查询时,别名可以让查询语句的结构更加清晰。例如,给employee表添加别名为“em”,查询出id和name列。selectem.id,fromemployeeem;

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(1)select语句4)limit子句可以通过limit子句来限定返回的行数。例如,如果只想获得employee表的前三条数据,可使用的语句为:select*fromemployeelimit3;

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(2)where语句可以使用where语句来对查询条件进行限制。例如,查询employee表中年龄大于25岁的用户信息,如图7.30所示。图7.30查询employee表中年龄大于25岁的用户信息

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(2)where语句结合谓词表达式and,查询employee表中department为software的男性用户,如图7.31所示。结合谓词表达式or,查询employee表中年龄大于30或者部门为administration的用户信息,如图7.32所示。图7.31结合and进行查询图7.32结合or进行查询

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(2)where语句结合like子句进行查询,可在like子句中使用通配符“%”和占位符“_”。例如,查询employee表中姓名中含有“yang”这个拼音的用户信息,如图7.33所示。在employee表的name字段中使用like子句的占位符来查询用户信息,如图7.34所示。图7.33like子句中使用通配符图7.34like子句中使用占位符

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(3)分组语句groupby和havingHive支持对结果集进行分组。groupby语句的作用是按照某些字段分组,有相同字段值的放在一组,其后可以跟having语句用于对分组进行条件过滤。groupby语句通常与聚合函数联合使用,先按照列值对结果进行分组,再作用聚合函数。使用groupby语句时,select语句中的查询字段要么为聚合函数,要么为groupby语句中出现的用于分组的字段,不能是其他值。也就是说,select中的字段如果不是聚合列,则必须出现在groupby中。

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(3)分组语句groupby和having例如,查询employee表中的每个部门的人数,可以按department字段分组,并使用聚合函数count()对每组的记录数进行统计,如图7.35所示。图7.35查询employee表中的每个部门的人数

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(3)分组语句groupby和havinggroupby语句后面可以使用having子句来对分组结果进行条件过滤。例如,统计employee表中部门人数大于2的信息,如图7.36所示。可以使用as关键字对列或表重命名。having子句中的元素与groupby一样,要么为聚合函数,要么出现在select语句中。图7.36统计employee表中部门人数大于2的信息

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(4)orderby语句和sortby语句进行排序sortby是对输入数据进行局部排序,并不是全局排序。当设置的Reducer任务个数大于1时,sortby会在每个Reducer任务中分别进行排序,并不保证数据结果全局有序。当Reducer只有1个时,orderby与sortby的执行结果相同。

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(4)orderby语句和sortby语句进行排序例如,对employee表按age进行升序排序。当Reducer任务个数为1个时,图7.37所示为使用orderby排序的情况,图7.38所示为sortby排序的情况。从两张图中可以看出,当Reducer个数为1时,orderby与sortby的执行结果相同。图7.37Reducer任务个数为1时的orderby排序图7.38Reducer任务个数为1时的sortby排序

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(4)orderby语句和sortby语句进行排序当Reducer任务个数大于1时,比如执行命令“setmapred.reduce.tasks=2;”将Reducer任务个数设置为2个。使用orderby排序的情况如图7.39所示,使用sortby排序结果如图7.40所示。从两个图中可以看到,Reducer任务个数大于1个时,orderby的Reducer任务个数仍然为1个,仍然进行全局排序,而sortby只是局部排序。图7.39orderby全局排序图7.40Reducer任务个数为2时的sortby排序

7.3 Hive基本操作7.3.4 Hive数据查询语言(HQL)(5)distributeby和clusterby语句distributeby会根据它指定的规则将数据分到不同的Reducer任务,相当于MapReduce中的分区。distributeby一般结合sortby使用,但需写在sortby子句的前面,也就是先进行分区再进行排序,而且需要事先设置ReduceTask的个数,使得Reducer任务个数大于1。例如,使distributeby语句对employe