Hadoop大数据开发基础（第2版）第6章 Hive数据仓库

Hive数据仓库随着信息技术的普及和企业信息化建设步伐的加快，企业逐步认识到建立企业范围内的统一数据存储的重要性，越来越多的企业已经建立或正着手建立企业数据仓库。企业数据仓库（DataWarehouse）有效集成了来自不同部门、不同地理位置、具有不同格式的数据，为企业管理决策提供了企业范围内的单一数据视图，从而为综合分析和科学决策奠定了坚实的基础。电信运营商为了优化维护工作，每天都会派出维护人员对分布在全国各的基站进行检测，分析基站的工作情况。其中掉话率在移动通信网中是一项非常重要的指标，掉话率的高低在一定程度上体现了移动网通信质量的优劣。不同厂家的设备对该指标的计算方法与标准不尽相同。因此，对基站掉话率的分析显得尤为重要。任务背景运营商有一个系统，收集了分布在各地基站的各种数据，数据字段说明如下表所示，共有5个数据字段。为了提升网络质量，节约时间和人力成本，优化维护工作，分析基站的掉话率就显得尤为重要，因此，针对系统收集的各基站运行数据，运营商为更精准、有针对性的完成基站的维护工作，提出了基站掉话率统计需求。任务背景属性名称属性说明record_time通话时间（单位：s）imei基站编号cell手机编号drop_num掉话秒数（单位：s）duration通话总秒数（单位：s）通常，运营商基站运行期间产生的文件数量都比较大，而且文件中的记录数也很多，文件大多数为文本格式。为了保证较高的处理效率与灵活性，在此选用Hive数据仓库对基站数据进行存储与处理分析。本章将详细讲解怎样通过Hive编程解决实际问题，首先介绍Hive数据仓库的系统架构、数据模式和工作原理，其次讲解访问Hive的3种方式及其搭建过程，再介绍Hive数据仓库中表的创建与修改的基本语法格式，并结合官方的示例介绍表数据的增删改查，最后通过编写Hive语句实现基站掉话率的统计分析。任务背景1访问Hive的3种方式目录认识Hive数据仓库2实现Hive表的创建与修改3实现Hive表中数据的增删查改4掉线率top10基站统计5Hive数据仓库基于Hadoop开发，是Hadoop生态圈组件之一，是海量数据存储和离线批量处理的常用工具。本小节的任务如下，这是学习与使用Hive进行数据存储与处理的第一步。了解Hive的概念。了解Hive与传统数据库的异同点。了解系统架构及数据模型。任务描述随着大数据时代的全面到来，传统数据仓库面临的挑战主要包括以下几个方面。无法满足快速增长的海量数据存储需求。无法有效处理不同类型的数据。计算和处理能力不足。大多数数据仓库应用程序均是使用关系型数据库进行实现的，并使用SQL作为查询语言。为了提高数据仓库的性能，选择将基于传统关系型数据库的数据仓库构建在Hadoop上，是一个很好的解决思路。对于大量的SQL用户，包括专业数据库设计师和管理员，也包括使用SQL语句从数据仓库中抽取信息的临时用户，急需一个可以提供有效地、合理地组织和使用海量数据的模型，Hive应运而生。什么是Hive大多数数据仓库应用程序均是使用关系型数据库进行实现的，并使用SQL作为查询语言。为了提高数据仓库的性能，选择将基于传统关系型数据库的数据仓库构建在Hadoop上，是一个很好的解决思路。对于大量的SQL用户，包括专业数据库设计师和管理员，也包括使用SQL语句从数据仓库中抽取信息的临时用户，急需一个可以提供有效地、合理地组织和使用海量数据的模型，Hive应运而生。Hive是Hadoop生态系统中必不可少的一个工具，它提供了一种类SQL语言，可以查询存储在Hadoop分布式文件系统（HadoopDistributedFileSystem，HDFS）中的数据，也可以查询其他与Hadoop集成的文件系统的数据，如MapReduce-FS、AmazonS3，也可以查询数据库中的数据，如HBase和Cassandra。什么是HiveHive在很多方面和传统数据库类似，但是它底层依赖的是HDFS和MapReduce（或Tez、Spark），因此，Hive在很多方面又有别于传统数据库。从数据存储、索引、分区、执行引擎、执行延迟、扩展性、数据规模等方面，对Hive和传统数据库进行了对比分析，如下表所示。了解Hive与传统数据库的对比对比内容Hive传统数据库查询语言HQLSQL数据存储HDFS本地文件系统索引0.8版以后支持位图索引支持复杂索引执行引擎MapReduce、Tez、Spark自身的执行引擎执行延迟高低可扩展性好有限处理数据规模大小Hive与传统数据库的对比具体说明如下。查询语言。由于SQL被广泛地应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便地使用Hive进行开发。了解Hive与传统数据库的对比数据存储。Hive是构建在Hadoop上的，所有Hive的数据都是存储在HDFS中。而数据库是将数据保存在块设备或本地文件系统中。在数据格式方面，Hive中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定3个属性，即列分隔符（通常为空格、“\t”、“\x001”）、行分隔符（“\n”）和读取文件数据的方法（Hive中默认有3种文件格式，分别为TextFile、SequenceFile和RCFile）。由于在加载数据的过程中，不需要从用户数据格式到Hive定义的数据格式的转换，因此，Hive在加载的过程中不会对数据本身进行任何修改，而只是将数据内容复制或移动至相应的HDFS目录中，而在数据库中，不同的数据库有不同的存储引擎，需要定义不同的数据格式。所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不支持对数据的改写和添加，所有的数据都是在加载时中确定好的，而数据库中的数据通常是需要经常进行修改的，因此可以使用INSERTINTOVALUES添加数据，使用UPDATESET修改数据。了解Hive与传统数据库的对比索引。Hive在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些Key建立索引。Hive要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于数据的访问延迟较高，所以决定了Hive不适合在线数据查询。数据库中，通常会针对一个或几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。执行引擎。在Hive中，大多数查询的执行是通过Hadoop提供的MapReduce实现的，而数据库通常有自己的执行引擎。了解Hive与传统数据库的对比执行延迟。Hive在查询数据时，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致Hive执行延迟高的因素是MapReduce框架。由于MapReduce本身具有较高的延迟，因此在利用MapReduce执行Hive查询时，也会有较高的延迟。当数据规模较小时，传统数据库的执行延迟较低，但是当数据规模大到超过传统数据库的处理能力时，Hive的并行计算显然能体现出优势。可扩展性。由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是和Hadoop的可扩展性是一致的，而数据库由于语义的严格限制，扩展能力非常有限。处理数据规模。由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据。了解Hive与传统数据库的对比Hive是典型的客户端/服务器（Client/Server，C/S）模式，底层执行引擎使用的是Hadoop的MapReduce框架，因此Hive运行在Hadoop基础上。Hive的系统架构组成如下图所示，主要包括5个部分：用户接口层、跨语言服务、元数据存储系统、底层的驱动引擎、底层存储。了解Hive系统架构用户接口层。用户接口用于连接访问Hive，包括CLI、JDBC/ODBC和HWI（HiveWebInterface）3种方式。CLI指的是shell终端命令行，采用交互式的方式访问Hive。JDBC/ODBC是通过客户端的方式连接并访问Hive。HWI则是通过浏览器的方式访问Hive。跨语言服务。Thrift是用于进行可扩展且跨语言的服务的开发，Hive集成了Thrift服务，能让不同的编程语言调用Hive的接口。了解Hive系统架构元数据库（MetaStore）存储系统。Hive将元数据存储在数据库中，如MySQL、Derby（Hive自带的内存数据库）。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。MetaStore默认存在自带的Derby数据库中。由于Derby数据库不适合多用户操作，并且数据存储目录不固定，不方便管理，因此，通常都将元数据存储在MySQL数据库中。了解Hive系统架构底层的驱动引擎（Driver）。底层的驱动引擎实现了将HiveQL语句转化为MapReduce任务的过程。Hive的底层的驱动引擎主要包括解释器、编译器、优化器和执行器，一同完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后由执行器（Executor）调用MapReduce执行。底层存储。Hive数据仓库的数据是存储在HDFS中的。针对大部分的HQL查询请求，Hive内部会将HQL语句自动转换为MapReduce任务执行。了解Hive系统架构对于数据存储，Hive没有专门的数据存储格式，也没有为数据建立索引，用户可以非常自由地组织Hive中的表，只需要在创建表时设置数据的列分隔符和行分隔符，即可解析数据。Hive中所有的数据都存储在HDFS中，存储结构主要包括数据库、表（内部表、外部表、带分区的表、桶表）和视图。了解Hive数据模型1.数据库Hive数据库类似于传统数据库的Database。内部表与数据库中的table在概念上类似。每一个table在Hive中都有一个相应的目录存储数据，所有的table数据均保存在这个目录中。2.表Hive的表在逻辑上由存储的数据和描述表格数据形式的相关元数据组成。表存储的数据存放在分布式文件系统里，如HDFS。Hive中的表分为两种类型，一种为内部表，这种表的数据存储在Hive数据仓库中；另一种为外部表，表数据可以存放在Hive数据仓库外的分布式文件系统中，也可以存储在Hive数据仓库中。值得一提的是，Hive数据仓库其实也是HDFS中的一个目录，该目录是Hive数据存储的默认路径，也可以在Hive的配置文件中重新配置。了解Hive数据模型Hive将表组织成分区（partition），这是一种根据分区列（partitioncolumn）的值对表进行粗略划分的机制。使用分区可以加快数据分片的查询速度。表或分区可以进一步分为桶（bucket）。将表或分区组织成桶有以下两个原因。第一个原因是可以获得更高的查询处理效率。桶其实是为表加上了额外的结构，Hive在处理有些查询时能够利用这个结构。如连接两个在相同列上划分了桶的表，可以使用Map端的join高效实现。第二个原因是使取样（sampling）更高效。在大规模数据集上进行开发和查询时，如果可以先在一部分小数据集上运行查询，那么会更加地方便。Hive使用CLUSTEREDBY子句来指定划分桶所用的列和要划分的桶的个数。了解Hive数据模型3.分区和桶Hive的视图与传统数据库的视图类似。视图是只读的，视图依赖的基本表如果改变，数据增加不会影响视图的呈现；如果删除基本表，那么会出现问题。了解Hive数据模型4.视图Hive的执行流程如下图所示，Hive中的任务执行流程是将客户端（Client）提交的任务（如HiveQL语句）通过驱动（Driver）找到HiveQL中的相关元数据（MetaStore）确定表及存储的情况，再编译（Compiler）相关信息，生成执行计划，提交给Hadoop完成Hive的执行过程。了解Hive工作原理1访问Hive的3种方式目录认识Hive数据仓库2实现Hive表的创建与修改3实现Hive表中数据的增删查改4掉线率top10基站统计5Hive的安装模式分为3种，分别是内嵌模式、直连数据库模式（或本地模式）和远程模式。内嵌模式使用内嵌的Derby数据库存储元数据。直连接数据库模式采用外部数据库存储元数据。远程模式也采用外部数据库存储元数据，不同的是，远程模式需要单独开启MetaStore服务。为了顺利地访问Hive，本小节的任务是介绍访问Hive的3种方式，并根据不同的安装模式进行配置。任务描述内嵌模式使用内嵌的Derby数据库存储元数据。在内嵌模式下，只允许一个会话连接，若尝试多个会话则连接时将报错。设置内嵌Derby模式内嵌模式下Hive的安装配置步骤如下。在Apache镜像网下载Linux下的Hive3.1.2版本的安装包，并上传至Linux系统的/opt/apps目录下，没有就创建。切换/opt/apps目录下，执行代码“tar-zxvfapache-hive-3.1.2-bin.tar.gz-C/opt/”解压压缩包至/opt目录下。将Hive3.1.2版本内存在的guava-19.0.jar删除，再将Hadoop3.1.4版本的guava-27.0-jre.jar复制至/opt/apache-hive-3.1.2-bin/lib/目录下。执行命令“mvlog4j-slf4j-impl-2.10.0.jarlog4j-slf4j-impl-2.10.0.jar.bak”，解决日志jar包冲突。切换至Hive安装目录下的bin目录，执行命令“./schematool-dbTypederby-initSchema”，初始化元数据库。配置Hive的全局变量值/etc/profile，执行命令“source/etc/profile”，使环境变量生效。设置内嵌Derby模式内嵌Derby模式下，无须对Hive配置文件进行修改，通过“hive”命令直接启动并进入Hive即可，如下图所示。在Hive交互式界面执行命令“showdatabases;”，可以查看当前所有数据库列表，如下图所示。设置内嵌Derby模式当退出Hive客户端时，在当前路径下将默认生成derby.log文件，该文件是记录用户操作Hive的日志文件，由于内嵌模式元数据不会共享，在其他路径下打开Hive客户端会创建新的derby.log文件，因此上一客户端进行的任何操作当前用户均无法访问。设置内嵌Derby模式直连数据库模式也叫本地模式，它和远程模式安装配置方式大致相同，本质上是将Hive默认的元数据存储介质由自带的Derby数据库替换为MySQL数据库。无论在什么目录下以任何方式启动Hive，只要连接的是同一台Hive服务，所有节点访问的元数据信息均是一致的，从而实现元数据的共享。以直连数据库模式为例，讲解Hive的安装过程。直连数据库模式的Hive安装主要包括安装MySQL服务和安装配置Hive两个步骤。设置直连数据库模式MySQL安装方式有许多种，可以直接解压安装包进行相关配置，也可以选择在线安装。本小节使用直接解压MySQL安装包方式安装MySQL，安装过程和说明如下（MySQL的安装过程均在master节点上进行）。MySQL安装需要下载4个rpm软件包，分别为mysql-community-client-5.7.18-1.el6.x86_64.rpm、mysql-community-common-5.7.18-1.el6.x86_64.rpm、mysql-community-libs-5.7.18-1.el6.x86_64.rpm、mysql-community-server-5.7.18-1.el6.x86_64.rpm。设置直连数据库模式1.安装MySQL服务RedHat软件包管理工具（RedhatPackageManager，RPM）原本是RedHatLinux发行版专门用于管理Linux各项套件的程序，由于它遵循GPL规则且功能强大方便，所以广受欢迎，Linux其他发行版也开始使用。RPM套件管理方式的出现，让Linux易于安装、升级，间接提升了Linux的适用度。为保证安装过程中不出现问题，在安装之前，需要检查并删除系统中与旧版本MySQL相关的配置文件和库等残留信息，删除目录/usr/lib/mysql、/usr/include/mysql、/etc/f、/var/lib/mysql、/usr/share/mysql。设置直连数据库模式由于MySQL的4个rpm包之间存在依赖关系，所以在安装过程中有顺序要求，具体的顺序为rpm-ivhmysql-community-common-5.7.18-1.el6.x86_64.rpm—>rpm-ivhmysql-community-libs-5.7.18-1.el6.x86_64.rpm—>rpm-ivhmysql-community-client-5.7.18-1.el6.x86_64.rpm—>rpm-ivhmysql-community-server-5.7.18-1.el6.x86_64.rpm。执行命令“rpm-ivhmysql-community-*”，可自动识别顺序并完成MySQL的安装。编辑/etc/f文件，添加配置，如下图所示，对MySQL数据库编码方式进行设置。设置直连数据库模式使用使用

service

启动MySQL服务器。在/var/log/mysqld.log文件中找到随机密码“zts.!yk)8ppL”，如下图所示。启动了MySQL服务器并拥有了初始随机密码，便可通过密码登录MySQL如下图所示。设置直连数据库模式修改默认密码。设置新密码的验证策略为0，表示最低。

设置新密码的长度最小值为6位。设置新密码为123456。设置允许root用户远程登录的权限，在centos中设置MySQL开机启动。设置直连数据库模式首先将apache-hive-3.1.2-bin.tar.gz文件上传至slave1节点的/opt/apps目录下；其次，元数据库存储是MySQL数据库，因此需要上传MySQL连接的jar包mysql-connector-java-5.1.32-bin.jar到slave1节点的/opt/apps目录下。Hive安装包的解压、解决guava-19.0.jar版本不一致、解决日志jar包冲突的操作过程与上一小节的Hive内嵌Derby模式基本一致，因此将不再叙述，重点介绍Hive直连数据库模式的配置。设置直连数据库模式2.Hive直连数据库模式配置修改/etc/profile配置文件，配置环境变量，如下图所示。配置完Hive的环境变量，通过source命令使环境变量配置信息生效。可以测试在任何目录下输入“hiv”并按tab键，会自动补齐命令为hive，如果可以自动补齐，那么表示hive环境变量配置成功，否则配置有问题，需检查hive的安装路径是否有误。设置直连数据库模式添加hive-site.xml配置文件，配置MySQL相关信息。由于Hive安装包/opt/apache-hive-3.1.2-bin/conf下并未提供hive-site.xml文件，需要创建一个hive-site.xml配置文件并编辑，如右图所示。设置直连数据库模式完成配置后，Hive会把默认使用Derby数据库方式覆盖。执行命令“cp/opt/apps/mysql-connector-java-5.1.32-bin.jar/opt/apache-hive-3.1.2-bin/lib/”，复制MySQL连接驱动的jar包至Hive安装包的lib目录下。在启动Hive之前，还需使用命令对元数据库进行初始化，如下图所示。若出现下图所示的结果，表示初始化成功。设置直连数据库模式通过hive命令启动，便可进入hive，如下图所示。设置直连数据库模式将slave1节点上已经配置好的Hive安装包分别发送到slave2、slave3节点，并给slave2、slave3配置相同的Hive环境变量。由于slave1的环境变量与slave2、slave3基本一致，因此，为方便起见，可直接将slave1上的/etc/profile配置文件分别发送到slave2和slave3节点上。同时为了使得slave2和slave3节点上的环境变量生效，需要分别在两个节点上执行“source/etc/profile”，为检测是否生效，可输入“hiv”后按快捷键tab，如果可以补齐hive，那么证明环境变量已经生效，否则配置有问题。如果使用远程模式的安装方式，其原理就是将元数据服务作为一个单独的服务进行启动，其他节点通过访问元数据服务器实现对数据的操作。设置远程服务器模式以slave2节点作为服务器，slave3节点作为客户端，分别配置slave2节点和slave3节点Hive安装包下的hive-site.xml文件。配置完成后，在slave2节点上执行“hive--servicemetastore”命令，启动服务器，如下图所示，服务器为启动状态。设置远程服务器模式此时通过slave3节点可通过“hive”命令访问服务器，同时会在数据库中创建一个新的数据库hive_metastore。设置远程服务器模式本次配置仍以master节点上的MySQL数据库作为存储元数据的场所，因此master节点的MySQL数据库中将多一个hive_metastore数据库，如下图所示。设置远程服务器模式通过上述配置，在slave3节点上启动Hive客户端，可以访问存储在master节点中MySQL数据库上的元数据信息（由于slave2节点作为服务器，配置中仍是以master节点上的MySQL作为存储元数据的场所）。至此完成了Hive远程模式的配置。设置远程服务器模式1访问Hive的3种方式目录认识Hive数据仓库2实现Hive表的创建与修改3实现Hive表中数据的增删查改4掉线率top10基站统计5尽管HiveQL的语法规则与SQL语法非常相似，但是，Hive在模式设计上与传统关系型数据库非常不同。Hive是反模式的，且在反模式的应用中，与传统数据库也是有区别的。本小节的任务如下。了解Hive的基本语法。掌握Hive表的创建与修改。任务描述Hive数据定义语言（DDL）可以实现Hive数据存储所依赖的数据库及表结构的定义、表描述查看和表结构更改的操作。与传统数据库（如MySQL）用法类似，Hive需要先创建数据库，再创建表，表中有表结构，数据按表结构进行存储。与传统数据库相比，Hive对表格式的定义更加宽松、随性。实现Hive表的创建与修改（1）创建数据库语法格式使用CREATE命令可以创建数据库，创建数据库语法格式如下。CREATE(DATABASE|SCHEMA)[IFNOTEXISTS]database_name[COMNENTdatabase_comment][LOCATIONhdfs_path][WITHDBPROPERTIES(property_name=property_value,...)];实现Hive表的创建与修改1.数据库（DDL）基本语法（1）创建数据库语法格式创建数据库语法中，CREATEDATABASE是固定的HQL语句，用于创建数据库，database_name表示创建数据库的名称，这个名称是唯一的，唯一性可以通过IfNotExists进行判断。DATABASE|SCHEMA是用于限定创建数据库或数据库模式。默认情况下，创建的数据库存储在/user/hive/warehouse/db_name.db/table_name/partition_name/路径下。下图展示了通过直连模式（本地模式）在slave1节点上创建一个名为student的数据库，并且通过使用“showdatabases;”命令，显示数据仓库列表信息，将看到新建的数据库student，如下图所示。实现Hive表的创建与修改（1）创建数据库语法格式创建成功后，通过WebUI打开Hive数据库所在HDFS路径（由于在配置hive-site.xml中指定的是jdbc:mysql://41:3306/hive，由此可在master节点的usr/hive/warehouse中进行查看），如下图所示。实现Hive表的创建与修改（2）删除数据库语法格式使用DROP命令可以删除数据库，删除数据库语法格式如下。DROP(DATABASE|SCHEMA)[IFEXISTS]database_name[RESTRICTI|CASCADE];DROPDATABASE命令用于删除数据库，默认删除的行为是RESTRICT，表示若数据库不为空，则DROPDATABASE将失败。在删除数据库时，若数据库中有数据表，则必须先删除数据表，才能删除数据库。也可以直接使用DROPDATABASEdatabase_nameCASCADE命令强制性删除，一般要慎用。实现Hive表的创建与修改1.数据库（DDL）基本语法（3）更改数据库语法格式使用ALTER命令可以更改数据库当前目录，更改数据库语法格式如下。ALTER(DATABASE|SCHEMA)database_nameSETLOCATIONhdfs_path;--(Note:Hive2.2.1,2.4.andlater)ALTERDATABASE...SETLOCATION语句不会将数据库当前目录的内容移动到新指定的位置。它不会更改与指定数据库下的任何表/分区关联的位置。它仅更改将为此数据库添加新表的默认父目录。此行为类似于更改表目录不会将现有分区移动到其他位置的方式。不能更改有关数据库的其他元数据。实现Hive表的创建与修改1.数据库（DDL）基本语法（4）使用数据库语法格式使用数据库语法格式如下。USEdatabase_name;USEDEPAULT;在Hive0.6（HIVE-675）中添加了USEdatabasename。通过USE为所有后续HiveQL语句设置当前数据库。将当前工作表所在数据库还原为默认数据库，需要使用关键字default而不是数据库名称。实现Hive表的创建与修改1.数据库（DDL）基本语法（1）创建表语法格式完成创建数据仓库后，使用use命令切换到对应的数据库，即可在指定的数据库中进行数据表的创建、修改等操作。实现Hive表的创建与修改2.表（DDL）基本语法（1）创建表语法格式其中Hive中创建表的基本语法格式如下。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]实现Hive表的创建与修改（1）创建表语法格式在创建表的同时可以声明很多约束信息，其中重要参数的说明如下。CREATETABLE用于创建一个指定名字的表。若相同名字的表已经存在，则抛出异常；用户可以用IFNOTEXISTS选项忽略这个异常。EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，则仅记录数据所在的路径，不对数据的位置做任何改变。在删除表时，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。PARTITIONEDBY用于创建带有分区的表，一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在于表文件的文件夹下，表和列名不区分大小写，分区是以字段的形式在表结构中存在，通过Describe命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。实现Hive表的创建与修改（1）创建表语法格式CLUSTEREDBY：对于每一个表（table）或分区，Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive是针对某一列进行桶的组织，对列值进行哈希计算后，除以桶的个数求余，最终决定该条记录存放在哪个桶当中。把表（或者分区）组织成桶（Bucket）有如下两个理由。获得更高的查询处理效率。桶为表加上了额外的结构，Hive在处理有些查询时能利用这个结构。连接两个在（包含连接列的）相同列上划分了桶的表，可以使用Map端连接（Map-sidejoin）高效的实现，如JOIN连接操作当两个表有一个相同的列时，如果对这两个表进行桶操作，那么将保存相同列值的桶进行JOIN操作即可大大减少JOIN连接的数据量。使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的小部分数据上试运行查询那么会更加的方便。实现Hive表的创建与修改（1）创建表语法格式SORTEDBY：对列排序的选项，可以提高查询的性能。ROWFORMAT：行格式是指一行中的字段存储格式，在加载数据时，需要选用合适的字符作为分隔符来映射字段，否则表中数据为NULL。实现Hive表的创建与修改（1）创建表语法格式在编写ROWFORMAT选项参数时，可以选用以下指定规则。row_BYchar]|SERDEserde_namformat：:DELIMITED[FIELDSTERMINATEDBYchar][COLLECTIONITEMSTERMINATEDBYchar][MAPKEYSTERMINATEDBYchar][LINESTERMINATEDe[WITHSERDEPROPERTIES(property_name=property_value,property_name=property_value,...)]实现Hive表的创建与修改（1）创建表语法格式SORTEDBY：对列排序的选项，可以提高查询的性能。ROWFORMAT：行格式是指一行中的字段存储格式，在加载数据时，需要选用合适的字符作为分隔符来映射字段，否则表中数据为NULL。在编写ROWFORMAT选项参数时，可以选用如下指定规则。用户在建表时可以自定义SERDE或使用自带的SERDE。如果没有指定ROWFORMAT或者ROWFORMATDELIMITED，那么将会使用自带的SERDE。在建表时，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SERDE，Hive通过SerDe确定表的具体的列的数据。实现Hive表的创建与修改（1）创建表语法格式STOREDAS：指文件存储格式，默认指定TextFile格式，导入数据时会直接将数据文件复制到HDFS上不进行处理，数据不压缩，解析开销较大。LOCATION：指需要将对应数据库表的映射在HDFS上的实际路径。实现Hive表的创建与修改（1）创建表语法格式在编写STOREDAS选项参数时，可以选用如下指定规则。File_format::SEQUENCEFILE|TEXTFILE|RCFILE|ORC|PARQUET|AVRO|JSONFILE|INPUTFORMATinput_namr_classnameQUTPUFORMATOutput_format_classname实现Hive表的创建与修改（2）复制表语法格式Hive中复制表的基本语法格式如下。CREATE[TEMPRORARY][EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_nameLIKEexisting_table_or_view_name[LOCATIONhdfs_path]在复制表语法格式中其他关键参数与创建表语法格式的要求一致，仅多了一个LIKE关键字。LIKE是用以声明用户复制现有的表结构，但是不复制数据。实现Hive表的创建与修改2.表（DDL）基本语法（3）删除表语法格式Hive中删除表的基本语法格式如下。DROPTABLE[IFEXISTS]table_name[PURGE]--(Note:PURGEavailableinHive0.14.0andlater)DROPTABLE删除此表的元数据和数据。如果配置了Trash（并且没有指定PURGE），数据实际上会被移动到Trash/Current目录。元数据将完全丢失。实现Hive表的创建与修改2.表（DDL）基本语法（4）修改表语法格式通过RENAME语句可以将表的名称更改为其他名称，语法格式如下。ALTERTABLEtable_nameRENAMETOnew_table_name;实现Hive表的创建与修改2.表（DDL）基本语法（4）修改表语法格式通过ALTERTABLE语句可以添加或删除表约束，语法格式如下。ALTERTABLEtable_nameADDCONSTRAINTconstraint_namePRIMARYKEY(column,...)DISABLENOVALIDATE;ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameFOREIGNKEY(column,...)REFERENCEStable_name(column,...)DISABLENOVALIDATERELY;ALTERTABLEtable_nameCHANGECOLUMNcolumn_namecolumn_namedata_typeCONSTRAINTconstraint_nameNOTNULLENABLE;ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name;使用ALTERTABLEADDCONSTRAINT向表中添加约束信息；PRIMARYKEY关键字用主要用于说明添加主约束，FOREIGNKEY关键字主要是用于添加列级外键约束，CHANGECOLUMN关键字用于修改表列名；DROPCONSTRAINT用于删除约束信息。实现Hive表的创建与修改（4）修改表语法格式通过ALTERTABLE语句也可以添加或删除表的分区，语法格式如下。ALTERTABLEtable_nameADD[IFNOTEXISTS]PARTITIONpartition_spec[LOCATION'location'][,PARTITIONpartition_spec[LOCATION'location'],...];partition_spec:(partition_column=partition_col_value,partition_column=partition_col_value,...)ALTERTABLEtable_nameDROP[IFEXISTS]PARTITIONpartition_spec,partition_spec,...][IGNOREPROTECTION][PURGE];实现Hive表的创建与修改（4）修改表语法格式可以使用ALTERTABLEADDPARTITION向表中添加分区。仅当分区值是字符串时才可以使用。位置必须是表所在的HDFS目录（ADDPARTITION更改表元数据，但不加载数据。如果分区位置中不存在数据，那么查询将不会返回任何结果）。如果表的分区规范已存在，那么会引发错误。可以使用IFNOTEXISTS跳过错误。可以使用ALTERTABLEDROPPARTITION删除表的分区，这将会删除此分区的数据和元数据。可以使用ALTERTABLEADDPARTITION向表中添加分区。仅当分区值是字符串时才可以使用。位置必须是表所在的HDFS目录（ADDPARTITION更改表元数据，但不加载数据。如果分区位置中不存在数据，那么查询将不会返回任何结果）。如果表的分区规范已存在，那么会引发错误。可以使用IFNOTEXISTS跳过错误。可以使用ALTERTABLEDROPPARTITION删除表的分区，这将会删除此分区的数据和元数据。实现Hive表的创建与修改Hive数据创建与传统数据库一样，表都是存储在数据库上的，因此，创建表之前必须指定存储的数据库。在myhive数据库上创建一个名为user_info的内部表，包含id和name两个字段。通过master节点的WebUI中查看创建的user_info表的位置，如下图所示。创建表基本操作1.创建内部表当文件较大时，可以选择创建外部表，因为外部表不需要移动结构化数据文件。现有结构化文件student_info.txt，该数据共有5个字段，分别为stu_no（学号）、stu_name（姓名）、stu_sex（性别）、telphone（联系电话）、stu_class（班级），部分数据内容如下图所示。创建表基本操作2.创建外部表首先，需要将student_info.txt文件上传至HDFS上的/stu路径下（在上传之前先在HDFS上创建/stu目录），用于模拟生产环境下的数据文件。其次，在myhive数据库下创建一张外部表。再次查看myhive数据库中的数据表，如下图所示，可以看到student_info外部表已经创建成功。通过“select*fromstudent_info;”可以查看student_info表数据内容。创建表基本操作分区表是按照属性在目录层面给文件更好的管理，分区表实际上是对应一个在HDFS上的独立文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区也可以理解为分目录，当查询分区表指定分区的数据时可通过where子句中的表达式选择查询指定的分区数据，这样的查询效率会提高很多。创建分区表分两种，一种是单分区，即表示在表目录下只有一级目录。另外一种是多分区，表目录下出现多个目录嵌套。本小节仅针对单分区进行详解。以创建一个单分区的分区表为例，创建一个以depart为分区的teacher_info表，仅需在创表语句后加入“partitioned(departstring)”即可。创建表基本操作3.创建分区表Hive提供了丰富的有关修改表的操作，如表的重命名、增加修改表的列信息、删除表、添加分区等操作。创建一个表score。通过“describeextendedscore;”命令查看score表结构，如下图所示。使用RENAME将表score重命名成stu_score。修改表基本操作使用ALTERTABLE语句向表stu_score中添加credit和gpa两个列。通过“describeextendedstu_score;”命令查看表结构，如下图所示。使用ALTERTABLE语句将表stu_score中的列credit重命名为Credits，并将数据类型修改为Float。修改表基本操作使用ALTERTABLE语句修改表stu_score中的分区，新增class_name为07111301的分区。通过HDFS上查看stu_score表多了一个“class_name=07111301”的分区，如下图所示。使用ALTERTABLE语句与DROP命令删除分区，将新增的class_name为07111301的分区删除。删除表stu_score，可直接使用droptable命令删除表。修改表基本操作1访问Hive的3种方式目录认识Hive数据仓库2实现Hive表的创建与修改3实现Hive表中数据的增删查改4掉线率top10基站统计5Hive数据库通过数据定义语言（DDL）可实现数据库、表的创建与修改。Hive数据库的本质是用于存储管理数据的，如何将外部数据写入Hive中并对表数据进行增删查改的操作是非常重要的一个环节。本小节的任务如下。了解Hive数据库提供的数据操作语言（DML）的语法。实现表数据的增删查改。任务描述Hive没有行级别的数据插入、数据更新和删除操作，向表中输入数据一般使用数据装载操作LOADDATA，或将文件直接上传至HDFS目录下，也可以使用查询语句向表插入数据。Hive数据操作包括数据表中数据的加载、查询、插入及删除等操作。在所有数据库系统中，查询语句是使用较为频繁的，也是比较复杂的，Hive中的select语句与MySQL语法基本一致，且支持where、distinct、groupby、orderby、having、limit以及子查询等查询操作。了解Hive数据操作语言（DML）的基本语法格式如下。LOADDATA[LOCAL]INPATH'filepath'[OVERWRITE]INTOTABLEtablename[PARTITION(partcol1=val1,partcol2=val2...)]LOADDATA[LOCAL]INPATH'filepath'[OVERWRITE]INTOTABLEtablename[PARTITION(partcol1=val1,partcol2=val2...)][INPUTFORMAT'inputformat'SERDE'serde']了解Hive数据操作语言（DML）的基本语法1.数据装载语法格式LOAD关键字表示进行数据的装载操作，装载操作目前是纯复制或移动操作，在将数据文件移动到与配置单元表对应的位置的过程不对表中的数据进行任何转换。filepath的值表示将要进行装载的数据文件的路径，可以是相对路径或绝对路径，也可以是完整的URI。如果有LOCAL关键字，那么filepath为本地文件系统的路径。如果是相对路径，那么路径会被解释为相对于当前用户的当前路径，并将filepath中的文件复制至目标文件系统中。目标文件系统由表的位置属性决定。如果没有指定LOCAL关键字，那么filepath指向的需要是一个完整的URI，Hive会直接使用该URI。Hive会将filepath路径下对应的文件内容移动至Hive表所指定的路径中。了解Hive数据操作语言（DML）的基本语法格式如下。SELECT[ALL|DISTINCT]select_expr,select_expr,...FROMtable_reference[WHEREwhere_condition][GROUPBYcol_list][ORDERBYcol_list][CLUSTERBYcol_list|[DISTRIBUTEBYcol_list][SORTBYcol_list]][LIMIT[offset,]rows]了解Hive数据操作语言（DML）的基本语法2.数据查询语法格式格式如下。INSERTOVERWRITETABLEtablename1[PARTITION(partcol1=val1,partcol2=val2...)[IFNOTEXISTS]]select_statement1FROMfrom_statement;INSERTINTOTABLEtablename1[PARTITION(partcol1=val1,partcol2=val2...)]select_statement1FROMfrom_statement;Hive提供了两种标准插入语法，一种是直接插入insertintotable，另一种是insertoverwritetable。两种的区别在于第一种是直接向表的最后一行插入，不会覆盖原有的表中数据，第二种是一种覆盖插入，将会覆盖表中原有的数据。了解Hive数据操作语言（DML）的基本语法3.插入数据语法格式格式如下。DELETEFROMtablename[WHEREexpression]DELETEFROM语句用于指定要删除的数据表。WHERE关键字作为可选参数，用于指定删除数据的条件。了解Hive数据操作语言（DML）的基本语法4.删除表中数据语法格式首先准备3个数据文件：student.txt，course.txt，sc.txt。其中，student.txt记录了学生的基本信息，主要包括学号、姓名、性别、年龄和专业共5个字段，部分数据如左图所示。course.txt记录了课程信息，主要包括课程编号和课程名称两个数据字段，数据如中图所示。sc.txt记录了学生每门课程的成绩信息，主要包括学号、课程编号、成绩共3数据个字段，部分数据如右图所示。向数据表中装载（Load）文件1.创建表结构使用CREATETABLE语句根据3个结构化文件创建对应的student、course、sc3个表结构。2.装载数据至Hive的3个数据表中使用LOAD命令将Linux本地的/root/hivedata目录下的3个文件的数据分别装载至Hive中的3个表中。装载完后，使用SELECT语句查询在Hive中的myhive数据库下的student、course和sc表的数据。以查询验证student表中的数据为例，如右图所示。向数据表中装载（Load）文件使用select语句查询student表中全体学生的学号与姓名，如左图所示。使用where条件查询student表中所有的男生的学号和姓名，如右图所示。查询数据使用join连接查询选修了课程的学生姓名，并结合where语句连接查询指定课程且课程成绩在90分以上的学生信息。首先，查询选修了课程的学生，如左图