当前位置： 首页 > news >正文

windows系统做网站,经典网站欣赏、,电子商务网站基础建设,网站服务器多少钱Hadoop生态圈中的Hive数据仓库技术 一、Hive数据仓库的基本概念二、Hive的架构组成三、Hive和数据库的区别四、Hive的安装部署五、Hive的基本使用六、Hive的元数据库的配置问题七、Hive的相关配置项八、Hive的基本使用方式1、Hive的命令行客户端的使用2、使用hiveserver2方法操… Hadoop生态圈中的Hive数据仓库技术 一、Hive数据仓库的基本概念二、Hive的架构组成三、Hive和数据库的区别四、Hive的安装部署五、Hive的基本使用六、Hive的元数据库的配置问题七、Hive的相关配置项八、Hive的基本使用方式1、Hive的命令行客户端的使用2、使用hiveserver2方法操作Hive3、Hive的JDBC客户端的使用 九、Hive中HQL语法1、DDL语法2、DML语法3、DQL语法 —— Hive中最核心最重要的语法 十、Hive压缩机制 一、Hive数据仓库的基本概念 Hive也是Apache网站开源的一个顶尖项目官网网址hive.apache.org Hive技术通过类SQL语言HiveQL–HQL分布式数据的管理、计算、存储的操作。 Hive是基于Hadoop的数据仓库软件采用了类似于MySQL中数据表的形式进行海量数据的管理和计算。 Hive存储的数据是类似于数据表Table的形式但是Hive本身不存储任何数据只是存储了表格的形式数据表格的数据最终还是在HDFS上进行存放只不过Hive通过一种叫做元数据库的操作手段可以实现将HDFS存储的结构化文件转换成为表格形式进行展示。Hive的表格中数据可以通过类SQL语句DQL语言、DDL语言对数据进行计算只不过Hive表面上使用的是SQL语言进行计算但是底层会把SQL语言转换成为MapReduce程序在YARN上运行。目前Hive1.x版本以后Hive也支持了可以将类SQL语言转换成为Spark、Tez分布式计算程序运行 Hive是基于Hadoop的因此hive对hadoop的版本是有要求的 hive.2.x.x—-hadoop2.x.x hive3.x.x—-hadoop3.x.x Hive本质上就相当于是Hadoop的一个客户端而且是一种类似于可以通过SQL操作Hadoop的客户端。 二、Hive的架构组成 Hive相当于Hadoop的一个客户端可以实现将存储到HDFS上的数据转换成为数据表的形式同时还可以借助类SQL语言对Hive数据表的数据进行计算而计算使用的类SQL语句底层又会转换成为MapReduce程序在YARN上运行。 Hive的之所以可以实现以上功能主要是因为Hive的设计架构Hive整体主要由以下几部分组成 1、Hive的Clienthive的客户端就是编写类SQL语言进行数据库、数据表创建、查询语句的编写的客户端Hive的客户端有很多种hive的命令行客户端、hive的Java APIJDBC的客户端、Hive的Web客户端等等。2、Hive的Driver驱动程序hive的核心hive之所以可以实现将类SQL语句转换成为MR程序主要就是由Driver来负责进行转换的其中hive的Driver又由以下几部分组成 1、解析器将编写的类SQL语言抽象成为一个语法树检查语法有没有问题。2、编译器将抽象成为的语法树生成逻辑执行计划。3、优化器对执行计划进行优化。4、执行器将优化之后的执行计划转换成为真正的物理执行代码比如Hive支持的计算程序MR、TEZ、Spark程序。 3、Hive的元数据metaStoreHive不负责存储任何的数据包括hive创建的数据库、数据表、表结构等等内容都不是在hive中的存放的还有表数据HDFS上这些信息我们都是在Hive的元数据中进行存放元数据存放到一个关系型数据库中比如MySQL、oracle、SQL Server 、Derby数据库。4、Hive的元数据库hive的元数据库中存储了Hive中创建的数据库、数据表、表字段和字段类型以及表数据和数据表之间的映射关系。 Hive的元数据库不是在Hive中存放的而是在一个关系型数据库derby、MySQL、SQL Server、Oracle等等中存放。默认情况下如果没有进行任何的配置hive默认会在derby数据库存放元数据hive-default.xml.templete默认文件配置的。 三、Hive和数据库的区别 Hive采用了类SQL语言进行海量数据的计算看上去操作和数据库还挺像的。但是一定要知道Hive和数据库完全不是一回事只不过就是Hive借助数据库中的数据表和SQL的思想简化了处理海量数据的操作除此以外hive的存储机制、执行机制、执行延迟、存储数据量等等和数据库有本质性的区别。 查询语言的区别 由于SQL被广泛的应用在数据仓库中因此专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。数据存储位置的区别 Hive是建立在Hadoop之上的所有Hive的数据都是存储在HDFS中的。而数据库则可以将数据保存在块设备或者本地文件系统中。数据更新的区别 由于Hive是针对数据仓库应用设计的而数据仓库的内容是读多写少的。因此Hive中不支持对数据的改写和添加所有的数据都是在加载的时候中确定好的。而数据库中的数据通常是需要经常进行修改的因此可以使用 INSERT INTO … VALUES 添加数据使用 UPDATE … SET修改数据。索引的区别 Hive在加载数据的过程中不会对数据进行任何处理甚至不会对数据进行扫描因此也没有对数据中的某些Key建立索引。Hive要访问数据中满足条件的特定值时需要暴力扫描整个数据因此访问延迟较高。由于 MapReduce 的引入 Hive 可以并行访问数据因此即使没有索引对于大数据量的访问Hive 仍然可以体现出优势。数据库中通常会针对一个或者几个列建立索引因此对于少量的特定条件的数据的访问数据库可以有很高的效率较低的延迟。由于数据的访问延迟较高决定了 Hive 不适合在线数据查询。执行方式的区别 Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而数据库通常有自己的执行引擎。执行延迟的区别 Hive 在查询数据的时候由于没有索引需要扫描整个表因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce框架。由于MapReduce 本身具有较高的延迟因此在利用MapReduce 执行Hive查询时也会有较高的延迟。相对的数据库的执行延迟较低。当然这个低是有条件的即数据规模较小当数据规模大到超过数据库的处理能力的时候Hive的并行计算显然能体现出优势。可扩展性的区别 由于Hive是建立在Hadoop之上的因此Hive的可扩展性是和Hadoop的可扩展性是一致的世界上最大的Hadoop 集群在 Yahoo!2009年的规模在4000 台节点左右。而数据库由于 ACID 语义的严格限制扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。数据规模的区别 由于Hive建立在集群上并可以利用MapReduce进行并行计算因此可以支持很大规模的数据对应的数据库可以支持的数据规模较小。四、Hive的安装部署 Hive相当于是Hadoop的一个类SQL的客户端底层的存储和计算都是基于Hadoop运行的因此Hive安装之前必须先部署安装Hadoop软件伪分布式、完全分布式、HA高可用。 Hive本身不是一个分布式软件依赖于Hadoop的分布式存储和分布式计算Hive就相当于是一个客户端软件因此不管Hadoop软件安装的是哪种模式Hive只需要安装到Hadoop集群的任意的一台节点上即可。 Hive的安装分为如下几步 0、安装hive之前必须先把JDK、Hadoop安装配置成功 1、上传、解压、配置环境变量 tar -zxvf apache-hive-3.1.2-bin.tar.gz -C /opt/app/vim /ect/profileexport HIVE_HOME/opt/app/hive-3.1.2 export PATH\(PATH:\)HIVE_HOME/binsource /etc/profile安装的大数据软件目录目录名最好只包含软件名版本号即可。 2、修改hive的配置文件 1、修改hive和hive配置文件的关联2、修改hive和Hadoop的关联以上两项在hive-env.sh中修改配置 先将conf目录下的hive-env.sh.template复制一份重命名为hive-env.sh vim hive-env.shexport HADOOP_HOME 2export HIVE_CONF_DIR 之所以指定hive的配置文件目录是因为hive默认提供的配置文件都是一个临时后缀名的文件更像是一个hive的配置模块Hive在默认情况下找不到配置文件目录3、配置hive的日志输出文件hive-log4j2.properties cp hive-log4j2.properties.template hive-log4j2.propertiesvim hive-log4j2.properties3、初始化hive的元数据库metastore默认情况下使用derby数据库hive自带的: schematool -dbType derby -initSchema 初始化会报错一个NoSuchMethod异常hive的guava的依赖jar包和hadoop的guava的依赖jar包的版本冲突了 解决方案hive的lib目录把guava.jar包删除了或者重命名了然后把这个\({HADOOP_HOME}/share/hadoop/common/lib/guava.xxx.jar 给hive的lib目录复制一份 4、hive和hadoop还有一个依赖是冲突的但是这个冲突不解决不会影响hive的正常使用给我们报警告hive和Hadoop的日志输出的依赖。 hive中的日志依赖版本低于Hadoop的日志依赖把hive的日志依赖删除了log4j-slf4jxxxxx 5、在Hive中引入一个依赖文件–JDBC连接MySQL的驱动依赖—配置hive的元数据库到MySQL中 【注意事项】 1、一定要注意第一次安装hive一定要初始化hive的元数据库以后再启动hive的命令行客户端。 初始化元数据库之后在初始化命令执行的工作目录下创建一个metastore_db文件夹文件夹就是derby记录的元数据库的文件位置。 如果初始化失败了一定一定要先把这个创建的目录给删除了再重新初始化。2、在env.sh文件配置关联路径时路径和之间不要加空格。3、hive和Hadoop有两个依赖包的冲突分别是guava、log4j的guava的依赖需要把hive的删除然后把Hadoop的复制一份给hivelog4j的需要把hive的删除了就行了。4、配置Hive的时候Hive底层需要MapReduce运行但是hive的HQL语句转换的MR程序启动多少个map任务多少个reduce任务都是不一定因此如果转换的MR的任务书过多而你的Hadoop集群的计算资源CPU、内存不足语法没问题但是HQL语句执行会报错—报错资源不足xxxG of xxxG 使用Hive之前最好在Hadoop的mapred-site.xml和yarn-site.xml文件中把资源调整一下 五、Hive的基本使用 Hive其实就是一个Hadoop的类SQL客户端Hive提供了多种方式可以进行类SQL编程Hive的命令行方式Java的JDBC操作方式等等。 如果我们要使用hive的命令行方式操作hive那么我们不需要启动任何的hive相关服务只需要把HDFS和YARN启动即可。 如果我们要使用Java的JDBC方式操作hive,那么必须配置Hive的远程连接服务并且启动hive的远程连接服务同时还得需要启动HDFS和YARN才能进行操作。 Hive的命令行操作方式 启动hive的命令行必须在hive的安装节点上使用 hive hive的HQL查询表数据时设计到聚合函数或者筛选等等操作才会转换成为MR程序运行。 支持增加和删除表中的所有数据不支持修改或者删除表中的部分数据。 六、Hive的元数据库的配置问题 hive中有一个很重要的概念就是元数据metastore元数据中记录了hive中创建了哪些数据库和数据表以及数据表的字段和字段类型、数据表的数据在HDFS上的存储目录等等信息。 而且hive要求元数据hive本身不负责存储它要求必须使用一个关系型数据库进行hive元数据的存储因为hive的元数据其实也是一堆表。hive默认使用的derby数据库进行元数据的存储但是derby存储元数据有一个非常严重的问题无法多客户端使用hive命令行。 derby数据库同一时刻只允许有一个客户端连接访问元数据库因此如果hive的元数据初始化到了derby数据库无法实现多客户端操作hive数据仓库。 因此我们建议大家包括hive官方也建议大家把hive的元数据库初始化到MySQL或者oracle或者SQL Server等关系型数据库当中。 Hive实现初始化元数据库到MySQL等关系型数据库底层借助了Java的JDBC操作实现如果想要实现把元数据库在MySQL中初始化必须先做三件事情 1、把以前在derby元数据库上创建、添加的表文件在HDFS上先删除了同时把以前derby的元数据库在Linux的目录移除了。 2、在Linux安装MySQL linux安装MySQL需要借助yum仓库进行yum仓库必须先配置阿里云的yum源 3、因为hive初始化元数据到MySQL使用的是Java的JDBC技术但是hive中并没有MySQL的JDBC驱动因此我们需要把MySQL的jdbc驱动给hive的lib目录下上传一份 Hive连接MySQL的时候还有一个问题MySQL连接需要用户名和密码但是hive默认情况下不知道需要修改hive的一个配置文件指定连接的MySQL的用户名和密码、驱动程序等等。 需要在hive的安装目录的conf路径创建一个文件hive-site.xml文件 这个文件中主要配置hive的相关配置项配置项默认在hive-default.xml.template文件中都存在但是如果我们在hive-site.xml文件中配置了相同的配置项会覆盖hive-default.xml同样的配置。hive-site.xml文件中第一行和第二行代码是xml文件的核心如果第一行和第二行出现多余的空格或者少了字符xml文件就不会解析或者解析失败。主要先配置了和元数据库有关的四项配置URL(在xml中 符合必须使用标识)、Driver、Username、Password。 ?xml version1.0? ?xml-stylesheet typetext/xsl hrefconfiguration.xsl?configuration!-- 指定MySQL数据库地址以及hive元数据在MySQL中存储的数据库hive_metastore --propertynamejavax.jdo.option.ConnectionURL/namevaluejdbc:mysql://single:3306/hive_metastore?createDatabaseIfNotExisttrueamp;serverTimezoneUTCamp;useSSLfalseamp;useUnicodetrueamp;characterEncodingUTF-8/valuedescriptionJDBC connect string for a JDBC metastore/description/property!-- MySQL连接驱动 --propertynamejavax.jdo.option.ConnectionDriverName/namevaluecom.mysql.cj.jdbc.Driver/valuedescriptionDriver class name for a JDBC metastore/description/property!-- MySQL用户名 --propertynamejavax.jdo.option.ConnectionUserName/namevalueroot/valuedescriptionusername to use against metastore database/description/property!-- MySQL密码 --propertynamejavax.jdo.option.ConnectionPassword/namevalueRoot123456../valuedescriptionpassword to use against metastore database/description/property/configuration初始化hive的元数据到MySQL中schematool -initSchema -dbType mysql -verbose。 七、Hive的相关配置项 1、Hive的表数据在HDFS上存储目录的配置 Hive存储的表数据默认是在HDFS上存放的默认存储到了HDFS的/user/hive/warehouse目录下Hive安装好以后默认会给我们提供一个default数据库。如果在hive中我们没有指定使用哪个数据库那么hive会默认使用default数据库。default数据库中相关表数据都在/user/hive/warehouse路径下存放的。 其他数据库的表数据会先在/user/hive/warehouse路径下创建一个xxx.db目录然后再在这个目录下放对应数据库的表数据。Hive的配置文件有一个配置项可以更改Hive表数据的存储目录hive.metastore.warehouse.dir 默认配置的路径就是/user/hive/warehouse路径 2、Hive的HQL语句执行的时候可以转换成为MR程序、Spark程序、TEZ程序默认情况下转换成为MR程序。 hive-default.xml.template中有一个配置项可以指定MR底层的转换规则 hive.execution.engine mr 3、配置hive的数据库名和表头的显示 默认情况下HiveCli-hive的命令行的客户端使用了某个数据库以后无法直观看到我们正在使用哪个数据库包括查询表数据的时候只会展示表数据表字段不会展示。 propertynamehive.cli.print.header/namevaluetrue/value/propertypropertynamehive.cli.print.current.db/namevaluetrue/value/property4、hive常用的客户端有两种 Hive的命令行客户端hive命令 命令行客户端只能在hive安装的节点上使用 Hive的JDBC客户端操作 如果我们想在其他机器、节点上操作hivehive命令行客户端就无法使用了但是我们可以使用JDBC远程连接hive操作hive。但是如果使用jdbc连接hive那么hive必须启动一个对应的服务hiveserver2只有启动了这个服务我们才能使用jdbc远程连接hive。 hivesever2是hive的远程连接服务远程连接服务器会启动一个端口通过端口可以进行通信远程操作Hive在默认情况下Hiveserver2没有配置的我们需要自己配置并且启动。 1、配置hive-site.xml文件 # HiveServer2需要在hive-site.xm1中增加如下配置项 !-- 设置权限校验-- property /propertyvalueNONE/value /property !--设置hiveserver2主机地址 必须是hive的安装节点-- propertynamehive.server2.thrift.bind.host/namevaluesingle/value /property !-- 设置hiveserver2绑定端口 -- propertynamehive.server2.thrift.port/namevalue10000/valuedescriptionTCP port number to listen on, default 10000/description /property !--设置hiveserver2的http请求端中(不常用) -- propertynamehive.server2.thrift.http .port/namevalue10001/value /property !--设置hiveserver2的连接用户-- propertynamehive . server2.thrift.client.user/namevalueroot/valuedescriptionUsername to use against thrift client/description /property !--设置hiveserver2的连接密码. -- propertynamehive.server2.thrift.client.password/namevalueroot/valuedescriptionPassword to use against thrift client/description /property2、配置Hadoop的core-site.xml文件 是因为hiveserver2启动之后远程连接是需要用户名和密码的远程连接的使用的用户名和密码默认是不能访问HDFS的。 propertynamehadoop.proxyuser.root.hosts/namevalue*/value /property propertynamehadoop.proxyuser.root.groups/namevalue*/value /property启动hiveserver2 nohup hiveserver2 1/opt/app/hive-3.1.2/hive.log 21 【注意】hiveserver2后端启动的时候需要将日志输出到一个设置的指定文件中日志文件赋予777的权限chmod 777 hive.log JDBC连接操作Hive有三种方式 1、使用一种叫做beeline客户端连接hiveserver2服务进行操作beeline是一种命令行工具只不过命令行工具底层需要通过JDBC的方式去操作hive。beelinehive有集成的。我们可以单独安装。 2、使用DBeaver连接hiveserver2进行操作DBeaver连接相关数据库都是使用JDBC的方式进行连接。 将原有的驱动删除掉将我们自己的驱动jar包添加进去hive-jdbc-3.1.2-standalone.jar 再次点击编辑驱动设置 最后点击测试连接前提是大数据环境下已启动了10000的端口号 3、使用原生的Java代码去连接hiveserver2进行操作七步曲 八、Hive的基本使用方式 1、Hive的命令行客户端的使用 好处只需要启动了HDFS和YARN就可以直接使用了不需要启动任何的hive服务。缺点只能在hive的安装节点上使用无法远程操作。hive命令行的使用有三种方式 1、hive 直接执行hive会进入hive的交互式命令行窗口REPL在窗口中写一行HQL语句只要一敲回车执行一行HQL语句。 2、hive -e “HQL语句”;“HQL语句” 不需要进入hive的交互式命令行REPL也可以快速的执行hive的HQL语句缺点是只能执行一条。HQL语句也可以有多条只要保证语句之间以分号分割即可但是不建议这种方式执行多条HQL语句 3、hive -f xxx.sql --hiveconf keyvalue --hivevar keyvalue 可以将需要执行的HQL语句封装到一个SQL文件当中文件当中可以编写多条HQL语句只需要每一条HQL语句以分号分割即可。–hiveconf选项可以增加可以不增加如果添加了代表向sql文件传递一个参数参数在SQL文件可以使用\){hiveconf:key}获取参数的值。–hivevar选项传递的参数需要通过\({hivevar:key}如果传递多个参数使用hiveconf传递 hive -f xxx.sql --hiveconf keyvalue --hiveconf keyvalue如果传递多个参数使用hivevar传递 hive -f xxx.sql --hivevar keyvalue --hivevar keyvalue 2、使用hiveserver2方法操作Hive 好处可以在任何一台节点通过JDBC的远程连接方式去远程操作Hive。缺点因为远程连接需要通过网络传输数据速度没有直接使用hive客户端快。 在Hive客户端中也可以直接操作HDFS和Linux的文件系统。 dfs 选项 HDFS路径!命令 相关操作—Linux的相关操作 【注意】命令行客户端支持使用sql文件执行多个HQL命令其中sql文件中可以添加注释在Hive的SQL文件中 注释语言–空格 注释。 DBeaver中SQL编辑器中也是同样的道理-- 注释。 3、Hive的JDBC客户端的使用 可以通过Java代码借助JDBC工具远程连接Hive数据仓库然后通过网络传递HQL语句以及执行结果。 使用前提必须启动hiveserver2,hiveserver2相等于是hive的远程连接服务专门用来让我们通过JDBC远程连接的。hiveserver2启动之后会给我们提供一个网络端口10000必须在hive-site.xml文件中配置hiveserver2的相关参数、core-site.xml中允许hiveserver2的用户操作Hadoop集群、hdfs-site.xml文件中放行hive服务用户的权限。 Java URL:jdbc:hive2://ip:port 启动hiveserver2 nohup hiveserver2 1xxxx.log 21 Hive服务的启动和关闭代码比较多的因此我们可以启动和关闭的命令封装成为一个shell脚本便于我们后期的操作 hs2.sh 【注意】我们每次开启虚拟机都需要开启hdfs、yarn、jobhistory、hiveserver2扩展作业把HDFS、YARN、Jobhistory、hiveserver2的开启封装到一个通用的脚本文件中。 #!/bin/sh if [[ \)1 start ]];thenecho starting hiveserver2……nohup hiveserver2 1/opt/app/hive-3.1.2/hive.log 21 echo start hiveserver2 complete! elif [[ \(1 stop ]];thenecho stopping hiveserver2......pidnetstat -untlp | grep 10000 | awk {print \)7} | awk -F / {print \(1}if [[ \)pid ]];thenecho hiveserver2未开启;elsekill -9 $pidecho stop hiveserver2 complete!fi elseecho 传递参数有误 fichmod 755 hadoop.sh #!/bin/sh

调用这个脚本需要传递一个参数 start/stop start开启所有hadoop相关服务 stop关闭所有的hadoop服务

if [[ \(1 start ]];thenecho 开启HDFS.....start-dfs.shecho HDFS开启成功echo 开启YARN.... start-yarn.shecho 开启YARN成功echo 开启历史日志服务器JobHistory..mapred --daemon start historyserverecho 开启历史日志服务器成功echo 开启hiveserver2服务.....hs2.sh startecho 开启hivsserver2服务成功 elif [[ \)1 stop ]];thenecho stop…stop-dfs.shstop-yarn.shmapred –daemon stop historyserverhs2.sh stopecho ok elseecho 参数有误 fi使用方式 1、使用Java代码中的原始的JDBC去操作Hiveserver22、使用一些基于JDBC的工具 beeline – hive自带的jdbc客户端dbeaver – 基于jdbc的数据库可视化工具 3、使用阿里云开发发Chat2DB工具来进行操作 九、Hive中HQL语法 Hive中提供了类SQL语法进行数据的存储和计算操作存储的数据也都是以表格和库的形式存在的。因此HQL语言和SQL语言有很多相似之处但是也有很多的操作不一样的。 1、DDL语法 DDL语法就是hive用来管理数据库和数据表的语言。虽然Hive使用数据库和数据表来管理结构化数据但是库和表的底层实现和正宗的数据库是没有任何的关系的。 数据库和数据表的管理语法创建、删除、修改、查询数据库和数据表的语法。 数据库的管理语法 创建语法 create database [if not exists] database_name [comment 备注] 给数据库加个介绍和备注 [location hdfs路径] 指定数据库的数据在HDFS上的存储位置如果没有指定那么默认存储到hdfs的/user/hive/warehouse/xxx.db [with dbproperties(keyvalue,keyvalue)]– 创建数据库的语法 create database if not exists demo01;– 默认数据库数据存放到HDFS的/usr/hive/warehouse路径下 create database if not exists demo02 comment 这是一个测试使用的专属数据库 location hdfs://192.168.31.104:9000/demo02– 一般不会指定路径默认路径就挺好 with dbproperties(createtime2023.08.02,createuserkanglei,namedemo02);修改语法 数据库不能改名字以及数据库在HDFS上的位置但是可以修改数据库的dbproperties属性值。 修改数据库的存储位置hive2.2.1版本之后才支持alter database database_name set location hdfs路径 alter database database_name set dbproperties(createtime20180830);– 修改数据库语法 alter database demo02 set dbproperties(createtime2023.08.01,tablenum3);查看语法 show databases: 查看hive中有哪些数据库 show databases like ‘名字’ 查询带有这个名字的数据库有哪些 desc database 数据库名; 查看数据库的简略信息 desc database extended 数据库名; 查看数据库的详细信息 desc formatted table_name查看数据表的详细信息 – 查看demo02的数据库信息 desc database demo02; – 查看demo02的数据库详细信息 desc database extended demo02;使用语法 use 数据库名; 删除数据库语法 drop database if exists database_name [cascade]; – 使用数据库 use demo02; create table student(student_name string); – 删除数据库 drop database demo02;– 这个命令只能删除空数据库 非空数据库无法使用该命令删除 —— 报错 drop database demo02 cascade;– 删除非空数据库 把库下的数据表以及表数据一并删除了 —— 慎用数据表的管理语法 创建数据表的语法 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name #external 外部的 [(col_name data_type [COMMENT col_comment], …)] #表字段 [COMMENT table_comment] #表的备注 [PARTITIONED BY (col_name data_type [COMMENT col_comment], …)] #hive中特有的数据表 分区表 [CLUSTERED BY (col_name, col_name, …) #hive中特有的数据表 分桶表 [SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS] #分桶表的信息 [ROW FORMAT row_format] #表字段之间的分隔符 [STORED AS file_format] #hdfs存储的文件的类型格式 默认是文本格式 [LOCATION hdfs_path] #单独指定数据表在hdfs上存储的目录如果没有指定 那么就在表对应的数据库的路径下hive数据表底层存储文件的分隔符问题 row_format DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] 列和列之间的分隔符 [LINES TERMINATED BY char] 行和行之间分隔符 \n [COLLECTION ITEMS TERMINATED BY char] 集合、struct、数组等等结构元素之间的分隔符 [MAP KEYS TERMINATED BY char] map集合key value之间的分隔符 [NULL DEFINED AS char] null值用什么字符表示Hive中还有两种比较特殊的创建数据表的语法 1、根据查询语法创建数据表 create table table_name as select查询语句– 1、根据查询语句创建数据表创建的数据表字段会根据查询语句的字段自动确定类型自动推断 use demo; create table teacher as select teacher_id as td,teacher_name from teacher1; select * from teacher;2、根据另外一个数据表创建一个新的数据表 create table table_name like other_table_name;创建的新表只有旧表的结构没有旧表的数据 分区信息和分桶信息也会一并复制 Hive中有四种类型的表 —— 一张数据表可能同时满足多个类型 —— Hive中一张表一定是内部表或者是外部表的一种分区表和分桶表是在内外表的基础之上的衍生表格 管理表/内部表 create table table_name(…….)管理表是Hive具备所有权限的一张表如果把表删除了那么表数据文件在HDFS上也会同步删除。 – 表的创建语法 use demo; – 1、管理表/内部表 create table if not exists demo(username string comment name,password string comment password )comment this is manager table row format delimited fields terminated by , lines terminated by \n stored as textfile;INSERT INTO demo values(kl,123456);dfs -cat /user/hive/warehouse/demo.db/demo/000000_0;– 删除表 drop table demo;外部表 create external table table_name(……)hive对于外部表只具备查询和添加数据的操作如果外部表删除只是在hive中把表元数据信息删除了表数据在hdfs还依然存在。 在有些情况下我们使用的数据不止只有hive在使用spark flink可能都在使用这个数据因此hive如果不使用这个数据了把表删除了但是数据是不能删除的此时这样的表设置成为外部表。 – 2、外部表 create external table if not exists demo(username string comment name,password string comment password )comment this is external table row format delimited fields terminated by - lines terminated by \n stored as sequencefile;insert into demo values(kl,123456);drop table demo;– hdfs中仍然存在此数据分区表 create [external] table table_name(…….) comment partitioned by(分区字段 字段类型,第二个分区字段 字段类型) row format …….分区表可以是管理表也可以是外部表分区表和普通数据表不一样的地方在于在HDFS存储数据的时候非分区表是将数据以文件的形式直接存储到数据表所在目录下而分区表会先在数据表所在目录下创建一个一个文件夹然后再在文件夹里面放对应分区的数据文件夹都是按照指定的数值进行操作的。 如果创建一个分区表指定一个或者多个分区字段分区字段的值有多个那么在数据库表的目录下创建多个不同的文件夹存放不同分区的数据。 分区表的目的是将表数据按照指定的规则分为不同的区域这样的话以后我们在处理数据的时候可以按照指定的区域获得我们想要的数据。 分区表需要指定分区字段分区字段一定不能是表字段。 分区表增加数据的语法 insert into table_name partition(分区字段值) values(表字段值)– 3、管理分区表 create table if not exists student(student_name string,student_age int,student_phone string )partitioned by(student_sex string) row format delimited fields terminated by - ;insert into student partition(student_sexman) values(kl,21,123456789); insert into student partition(student_sexman) values(gb,20,123789456); insert into student partition(student_sexwoman) values(hyf,18,123159753);dfs -cat /user/hive/warehouse/demo.db/student/student_sexman/000000_0; dfs -cat /user/hive/warehouse/demo.db/student/student_sexman/000000_0_copy_1;select * from student; select * from student where student_sexman;– 4、多级分区表 create table if not exists student1(student_name string,student_age int,student_phone string )partitioned by(student_sex string,student_birthday string) row format delimited fields terminated by ,;insert into student1 partition(student_sexman,student_birthday2023-07) values(kl,21,123456789);select * from student1;分桶表 create [external] table table_name(…….) comment partitioned by(分区字段 字段类型,第二个分区字段 字段类型) clustered by(分桶字段–一定表字段) [sorted by (排序字段 asc|desc)] into num buckets; row format …….分桶表既可以是分区表也可以是外部表还可以是内部表。分桶表指的是将最终存储的结果文件存储为指定个数个文件就相当于是MR程序在执行时启动了多个reduceTask每个reudceTask输出一个结果文件。 分桶表的字段一定是表字段 – 5、创建一个普通的分桶教师表要求按照教室的编号分为4个文件存储教师信息 每个文件需要按照教室的年龄的进行降序排序 create table if not exists teacher(teacher_num string,teacher_name string,teacher_age int )clustered by (teacher_num) sorted by(teacher_age desc) into 4 buckets;insert into teacher values(t001,zs,30),(t002,zs1,28),(t003,zs2,27),(t004,zs3,45),(t005,zs4,50),(t006,zs5,55),(t007,zs6,46),(t008,zs7,39),(t009,zs8,52),(t0010,zs9,43);dfs -cat /user/hive/warehouse/demo.db/teacher/000003_0;– 分桶表的抽样查询 总共设置了4个桶 1 out of 2的意思 从第1个桶开始抽取4/2个桶的数据 select * from teacher tablesample(bucket 1 out of 2);– 按照比例抽取 如果抽取某一个数据块大于小于128M 返回数据块的所有数据 select * from teacher tablesample(0.9 percent);修改数据表的语法 1、修改表名 alter table table_name rename to new_table_name2、修改/增加/替换列 alter table table_name change old_column new_column type3、增加分区信息–不是增加分区字段而是增加基于现有的分区字段增加一个新的目录 alter table table_name add partition(分区字段分区值)4、删除分区信息 alter table table_name drop partition(分区字段分区值)– 修改表名 alter table teacher rename to teacher1; – 修改增加列 alter table teacher1 change teacher_num teacher_id string; alter table teacher1 add columns(teacher_phone string); – 增加分区信息 alter table student1 add partition(student_sexwomen,student_birthday2023-08) partition(student_sexno,student_birthday2023-09); – 删除分区 分区下数据丢失 alter table student1 drop partition(student_sexman,student_birthday2023-07);查看表的相关信息语法 show tables; 查看某一个数据库下的所有数据表desc 表名 查看某个表的字段、分区字段desc formatted 表名 查看某个表的详细信息show partitions 表名 查看某个表有多少个分区删除数据表的语法 drop table if not exists table_name;数据表字段类型 整数类型 tinyintsmallintint/integerbigint 布尔类型 boolean 小数类型 floatdouble 字符串类型 string 时间日期有关的类型 timestamp 字节类型 binary 复杂的数据类型 array - 数组类型map - Java中map集合struct—Java对象可以存放多个数据每个数据的类型都可以不一样 – 3、创建一个具有复杂数据类型的数据表 必须指定复杂数据类型的元素的分隔符 – array map struct 三个类型都是有多条数据组成的需要指定数据之间的分隔符 create table demo(hobby arraystring,menu mapstring,double,students struct name:string,age:int,sex:string )row format delimited fields terminated by , collection items terminated by _ map keys terminated by : lines terminated by \n;– 向数据表增加特殊数据此方法不建议使用 insert into demo values(game_study_sleep,apple:20.0_pear:30.0_orange:40.0,zs_20_man); – insert增加问题比较多不用insert增加了而是使用文件增加 select * from demo; select hobby,menu,students.age from demo; select hobby[0],menu[apple],students.age from demo;2、DML语法 Hive中存储的数据是以数据库和数据表的形式进行存储的因此我们就可以使用DML操作对表数据进行相关的增加、删除、修改等操作。但是因为hive的特殊性Hive对数据的修改和删除不是特别的支持。 Hive的DML操作分为两部分 1、正常的DML操作对数据增加、删除、修改操作 增加数据的语法 普通的insert命令底层会翻译成为MR程序执行 insert into table_name(表字段) 【partition(分区字段分区值)】 values(字段对应的值列表),(值列表)……. Hive中基本不用insert into table_name(表字段) 【partition(分区字段分区值)】 select 查询语句 insert overwrite table table_name(表字段) 【partition(分区字段分区值)】 select 查询语句 Hive比较常用 根据一个查询语句添加数据 要求 table_name后面跟的表字段的个数、类型、顺序 必须和查询语句的得到结果一致3、多插入语法从同一个表A查询回来不同范围的数据插入到另外一个表B form A insert into/overwrite [table] table_name [partitio(分区字段分区值)] select 查询字段 where筛选条件 insert into/overwrite [table] table_name [partitio(分区字段分区值)] select 查询字段 where另外一个筛选条件;– 1、insert增加单条或者多条数据 create table test(name string,age int )row format delimited fields terminated by ,; insert into test values(zs,20),(ls,30); insert into test select name,age from test01;create table test01(name string,age int )partitioned by (timestr string) row format delimited fields terminated by ,; insert into test01 partition(timestr2023) values(zs,20),(ls,30); insert overwrite table test01 partition(timestr2023) select name,age from test;– 多插入语法根据多条增加语句增加数据,要求多条增加语句的查询是从同一张表查询过来的 from testinsert overwrite table test01 partition(timestr2022) select name,age insert overwrite table test01 partition(timestr2023) select name,age;如果向表中增加数据除了insert语法以外我们还可以通过一些手法来添加数据。 1、按照表格的格式要求将一个符合格式要求的数据文件上传到数据表的所在HDFS目录下 不建议使用 【注意事项】 如果不是分区表数据上传成功表会自动识别。 如果是分区表可能会出现数据上传成功但是表不识别分区目录是我们手动创建的我们修复分区表 msck repair table table_name。2、创建表的时候指定location,location位置可以存在。 load装载命令 也是将文件装载到数据表当中底层表现就是会把文件移动到数据表所在的目录下load装载命令相比于手动上传文件而言load不会出现数据上传无法识别的情况因此load装载数据会走hive的元数据。 同时手动上传文件到数据表目录下因为不走元数据因此我们执行count( * ) 命令统计表中的数据行结果不准确的因为count( * )直接从元数据中获取结果。但是如果使用load装载同样是将文件上传到hive数据表的存储目录但是load走元数据。 load data [local] inpath 路径 [overwrite] into table table_name [partition(分区字段分区值)]local 如果加了local 那么后面路径是linux的路径 如果没有加local 那么路径是HDFS的路径如果是HDFS上的文件装载把文件移动到数据表的目录下原始文件不见【注意事项】load装载的文件的格式必须和数据表的分割符一致列也是对应。否则会出现装载失败或者数据异常。 – 装载Linux数据到hive的某个非分区表中 load data local inpath /root/test.txt into table test; load data inpath /t.txt into table test;– 装载Linux数据到hive的某个分区表中 load data local inpath /root/test.txt into table test01 partition(timestr2023);更新操作 Hive中创建的分区表、管理表、外部表、分桶表默认不支持更新操作。更新操作需要hive的一些特殊手段hive的事务操作。 删除操作 Hive中创建的这些表默认不支持删除部分数据操作但是支持清空表数据或者删除某一个分区的数据或者删除所有数据的操作。如果要删除表中所有数据必须使用truncate [table] table_name [partition partition_spec] 命令是DDL命令。 2、import和export操作 导出操作 将hive数据表中数据导出到指定的目录下存储。export table table_name [partition(分区值)] to “路径” 导入操作 将hive导出的数据导入到hive中。import [external] table table_name [partition(分区值)] from “hdfs路径-必须是通过export导出的数据” 如果导入指定分区分区必须导出目录也存在。 – 把test1上的数据导出到hdfs的/export目录 export table test01 to /export;– 导入数据 import table test02 from /export;3、DQL语法 —— Hive中最核心最重要的语法 DQL语言是一个数据操作语言Hive提供的DQL语言是Hive实现统计分析的核心。Hive可以通过DQL语言的基本连接查询、条件查询、分组查询等等操作可以实现我们以前MR程序复杂的计算逻辑。 Hive的DQL语法和MySQL的DQL查询语法是非常像的 SELECT [ALL | DISTINCT] 查询列表表达式、常量、函数、表字段FROM table_reference as 别名[inner join | left join | right join | full join other_table as 别名 on 连接条件][WHERE where_condition][GROUP BY col_list][ORDER BY col_list][CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ][LIMIT [offset,] rows][union | union all other_select]单表查询 SELECT [ALL | DISTINCT] 查询列表表达式、常量、函数、表字段FROM table_reference[WHERE where_condition][GROUP BY col_list][ORDER BY col_list][CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]][LIMIT [offset,] rows]基本查询 select 查询列表 from table_name 如果查询的是表中的所有列的数据查询列表可以使用*来代替 【注意】 * 虽然简单但是能不用就不用 1HQL 语言大小写不敏感。 2) HQL 可以写在一行或者多行 3关键字不能被缩写也不能分行 4各子句一般要分行写。 5使用缩进提高语句的可读性。 select student_name from student;select * from student;条件查询 SELECT 查询列表 from table_name where 查询条件查询条件可以是条件表达式、逻辑表达式、模糊查询 条件表达式 !逻辑表达式 is null | is not null | and | or | in | not in | ! | between a and b模糊查询like 两个特殊的符号 % _ rlike 正则表达式 分组查询 select 查询列表只能是常量、表达式、聚合函数、每一组分组的字段 from table_name [where 查询条件] group by 分组字段,…, having 分组后的筛选 排序查询 全局排序order by 排序字段 asc | desc Hive的HQL的复杂查询语句底层会转换成为MR程序进行运行查询的过程中如果我们需要对查询的结果进行排序那么我们可以使用order by进行排序order by是全局排序一旦使用order by 那么HQL语句转换的MR程序底层的reduce任务只有一个这样的话会把所有的map任务的数据拉取过来输出一个结果文件结果文件全局有序。全局排序因为只有一个reduce任务如果处理数据量过多那么就会导致reduce计算缓慢甚至崩溃 局部排序sort by 排序字段 asc|desc 对HQL语句转换的MR程序可以指定多个reduceTask,然后map输出的数据会按照hash分区机制随机分区sort by无法控制的但是每一个分区的数据最终会根据sort by排序【注意】sort by使用的时候必须指定设置reduceTask的任务数大于1如果1那么sort by和order by就没有任何的区别。局部排序指定分区字段MR中自定义分区机制 sort by是对每一个分区的数据进行局部排序但是sort by排序的时候不负责分区的数据到底如何划分每一个分区的数据我们无法控制。Distribute By 分区字段 sort by 排序字段 asc |desc 先按照Distribute By的分区字段对数据进行分区分区字段的hash码和reduce任务个数取余数在按照排序字段对每个分区的数据进行排序设置reduceTask的任务数 —— set mapreduce.job.reduce 2;默认值为-1。 CLUSTER BY 当Distribute By 和sort by的字段一致的时候可以使用cluster by 分区排序字段select * from a cluster by age 等同于 select * from a distribute by age sort by agecluster by 无法指定排序规则是升序和降序只能是升序 分页查询 limit num查询回来num条数据limit offset,num从第offset条数据开始查询回来num条数据 offset从0开始 【特殊的两个语法】 起别名去重 【注意】分区表特殊在存储上以分区字段值为文件夹形式进行存放使用的时候可以当作表字段来使用 连接查询 使用场景查询的数据来自于多张数据表并且多张数据表存在“外键”关系。 Hive和MySQL一样 只支持等值连接。 连接查询分类Hive四类连接查询全部支持 内连接查询 inner join左外连接查询 left join右外连接查询 right join全外连接查询 full join 多表连接问题 select * from a xxx join b on a.xxb.xx
xxx join c on xxxxxx笛卡尔乘积问题一定要避免这个问题 a join b a m条数据 b n条数据 最后join完成得到m*n条数据 出现效果就是a表的每一条数据和b表的每一条数据都匹配上了产生原因没有写连接条件或者连接条件写错了 联合查询 使用场景将多条HQL查询语句的结果通过union|union all连接起来union 、union all union去重数据 union all不会去重数据限制多条查询语句的查询列表查询列表的个数、类型和顺序必须保持一致 子查询 和MySQL的子查询一模一样的查询里面嵌套了一个查询子查询可以出现from子语句、where子语句…
DQL查询语句中常用函数–Hive最核心的知识点 内置函数 – 如何查看系统自带的内置函数 show functions; desc function abs; desc function extended abs;Hive中常见的一些内置函数的用法 数学函数:UDF UDF函数一对一函数输入一个数据输出一个数据 abs(x) 返回x的绝对值 ceil(x)向上取整返回比x大的正整数中最小的那一个 floor(x)向下取整 mod(a,b)a%b pow(a,b)a^b round(x,[n])四舍五入 如果不传递n 代表小数点不保留如果n1 代表小数点后保留n位 sqrt(x)根号x select abs(-12); select ceil(12.4); select ceil(11.4); select floor(12.4); select floor(11.4); select mod(11,3); select pow(2,5); select round(3.1415926,4); select round(3.1415926,0); select round(3.1415926); select round(3.1415926,-1); select sqrt(99);字符串函数 concat直接拼接拼接需要传递多个参数会把多个参数拼接起来如果有一个参数为null值那么结果直接为null concat_ws可以拼接的分隔符传递的第一个参数是一个分隔符如果拼接了null值null值不计算。 lpad|rpad(str,x,pad) 在str左/右边以指定的pad字符填充字符串到指定的x长度 ltrim|rtrim|trim(str) 去除空格 length(x)返回字符串的长度 replace(str,str1,replacestr)将字符串中指定字符串替换成为另外一个字符串 reverse(str)字符串反转 split(str,x):array 字符串切割 substr|substring(str,n,[num])截取字符串 create table demo(name string ); insert into demo values(zs),(ls),(ww); select * from demo; select concat(name,null) from demo; select concat_ws(-,zs,ls,null);select lpad(zs,10,-); select rpad(zs,10,-); select ltrim( z s ); select rtrim( z s ); select trim( z s ); select length(sdadadadsasd);select replace(2022-10-11,-,/); select reverse(asdfghjkl);select split(as-df-gh-jkl,-); select substring(as df gh jkl,1,3);日期 current_date()返回当前的日期 年月日 current_timestamp():返回当前时间 date_format(date,“格式”) 格式化时间的 datediff(date,date1)返回这两个时间的差值天 date_add(date,day) date_sub(date,day) select current_date(); select current_timestamp(); select date_format(current_date(),yy-MM-dd); select datediff(current_date(),2002-08-03);条件判断函数 if case when then [when then] … else end select if(12,zs,ls);select case 10when 10 then zswhen 20 then lselse wwendselect case when 12 then zswhen 12 then lselse wwendend特殊函数 和数组、集合操作有关的函数 函数内部需要传递一个数组或者返回值是一个数组类型的函数 split(str,spea):array collect_set(列名)array 将一列中的所有行的数据封装为一个数组 列转行 不允许重复 collect_list(列名)array 将一列中的所有行的数据封装为一个数组 列转行 允许重复 array(ele…):array map(key,value,key,value,key,value…):map 传入偶数个参数 concat_ws(spe,array(string))String 将一个数组中的所有字符串以指定的分隔符拼接得到一个全新的字符串 explodearray,map集合多行多列的数据 炸裂函数 行转列的函数 如果传递的是array,那么结果是一列多行 如果传递的是map集合那么结果就是两列多行 insert into demo values(zs),(ls),(ww); select collect_set(name) from demo; select collect_list(name) from demo; select concat_ws(-,collect_set(name)) from demo;select array(1,2,3,4,5); select explode(array(1,2,3,4,5)); select map(name,zs,age,20,sex,man); select explode(map(name,zs,age,20,sex,man));和字符串有关的特殊函数字符串必须得是URL URL的概念 URL是叫做统一资源定位符是用来表示互联网或者主机上的唯一的一个资源的 URL整体上主要有如下几部分组成的 协议http/https、ftp、file、ssh host主机名、域名、ip地址 port端口号 path资源路径 queryParam参数 ?keyvaluekeyvalue….例子http://192.168.35.101:9870/index.html?namezsage20 https://www.baidu.com/search/a?keyvalueURL中如果没有写端口号那么都是有默认端口http:80 https:443 ssh:22parse_urlurlstr,“特殊字符”string 一次只能提取URL的一个成分 parse_url_tuple(urlstr,“特殊字符”…)每一个成分当作一列单独展示函数可以将一个数据转换成为一行多列的数据 函数多了一个特殊字符QUERY:key parse_url_tuple(urlstr,“特殊字符”…) as (列名…) hive提供用来专门用来解析URL的函数从URL中提取URL组成成分 特殊字符代表的是URL的组成成分特殊字符有如下几种 HOST提取URL中的主机名、IP地址 PATH,提取URL中资源路径 QUERY, 提取URL中的所有请求参数 PROTOCOL, 提起URL中的请求协议 AUTHORITY, FILE, USERINFO, REF,select parse_url(http://www.baidu.com:80/search/a?namezsage30,HOST); select parse_url(http://www.baidu.com:80/search/a?namezsage30,PATH); select parse_url(http://www.baidu.com:80/search/a?namezsage30,QUERY); select parse_url(http://www.baidu.com:80/search/a?namezsage30,PROTOCOL);select parse_url_tuple(http://www.baidu.com:80/search/a?namezsage30,QUERY,HOST,PATH,PROTOCOL,QUERY:name) as (query,host,path,protocol,name);侧视图 侧视图Lateral View专门用来和UDTF函数结合使用用来生成一个虚拟表格然后这个虚拟表格一行数据会生成一个虚拟表格是动态的一行数据会生成一个虚拟表格生成的虚拟表格和当前行做一个笛卡尔乘积得到一些我们普通SQL无法实现的功能 侧视图使用场景一个表格中某一行的某一列是一个多字段组成的数据我们想把多字段的列拆分开和当前行结合得到一个多行的结果。 – 侧视图的使用 create table test(name string,age int,hobby arraystring );insert into test values(zs,20,array(play,study)),(ls,30,array(sleep,study)); select * from test;select name,age,hobby,temp.hb from test lateral view explode(hobby) temp as hb;开窗函数 over select子语句中 开窗函数指的是在查询表数据时将表按照指定的规则拆分成为多个虚拟窗口并没有真实的拆分、类似于分组然后可以在窗口中得到一些只有分组之后才能得到一些信息然后将信息和原始数据结合起来实现在同一个查询中既可以得到基础字段还可以得到聚合字段。 既需要普通字段还需要一些聚合信息的时候开窗函数就是最完美的选择。 语法函数(参数) over(partition by 列名 order by 字段 rows between 上边界 and 下边界) as 别名-列名 partition by 的作用就是用来规定以哪个字段进行分组开窗 order by的作用就是对划分的窗口的以指定的字段进行排序 rows between的作用是为了划分窗口的边界的每一个窗口默认的边界是 分组中的所有数据但是窗口也可以是分组的部分行数据。 默认情况下 我们不写边界默认边界默认无上边界也无下边界就是一个组中的所有数据 可以和窗口函数结合使用的主要有三种类型的函数 first_value(col)|last_value(col) over(partition by 列名 order by 字段) as 别名-列名 聚合函数 sum/avg/count/max/min over(partition by 列名) as 列别名 排名函数 row_number()/rank()/dense_rank() over(partition by 列名 order by 字段) as 列别名 排名函数的作用就是对数据开窗之后查询到某一行数据之后看一下这行数据在所属窗口的排名-位置然后根据位置打上一个序号序号从1开始 row_number() 序号是从1开始依次递增如果两行数据排名一致也会依次编号 rank() 序号是从1开始依次递增如果两行数据排名一致两行编号一样的 跳排名 dense_rank() 序号是从1开始依次递增如果两行数据排名一致也会依次编号,不会跳排名 使用场景求不同组中排名topN的数据信息 注意开窗函数要和 一些其他函数结合使用而其他函数在使用的时候大部分函数默认的边界都是无上边界和无下边界而有少部分函数如果没有写窗口边界默认不是无边界而是有边界的。所以以后使用窗口函数的时候建议把窗口边界也声明上。 – 开窗函数 create table student(student_name string,student_age int,student_sex string );insert into student values(zs,20,man),(ls,20,woman),(ww,20,man),(ml,20,woman),(zsf,20,man);select * from student; – 查询不同性别的总人数 select student_sex,count(1) from student group by student_sex; – 查询表中所有的学生信息并且每个学生信息后面需要跟上这个学生所属性别组的总人数 select student_name,student_age,student_sex,count(1) over(partition by student_sex) as sex_count from student;select student_name,student_age,student_sex,first_value(student_name) over(partition by student_sex) as sex_count from student;select student_name,student_age,student_sex,row_number() over(partition by student_sex order by student_name desc) as sex_count from student;– 排名函数的使用场景 create table employees(employees_id int,employees_name string,employees_dept int,employees_salary double ); insert into employees values(1,zs,1,2000.0),(2,ls,1,1800.0),(3,ww,1,1700.0),(4,ml,1,2000.0),(5,zsf,1,1900.0),(6,zwj,2,3000.0),(7,qf,2,2500.0),(8,cl,2,2500.0),(9,jmsw,2,2000.0);select * from employees; – 获取每个部门薪资排名前二的员工信息 – 部门分组 薪资降序排序 排名窗口函数 给每一行数据打上一个序号 select *,dense_rank() over(partition by employees_dept order by employees_salary desc) as salary_rank from employees;select * from (select *,dense_rank() over(partition by employees_dept order by employees_salary desc) as salary_rankfrom employees )as b where salary_rank 2;开窗函数的使用补充代码 create table student_score(student_id int,student_name string,student_class int,student_score double );insert into student_score values(1,zs,1,80.0),(2,ls,1,90.0),(3,ww,1,100.0),(4,ml,1,85.0),(5,zsf,2,80.0),(6,zwj,2,70.0),(7,qf,2,60.0); select * from student_score; – 查询每一个学生的成绩并且还要获取同一个班级的学生和前一个学生的成绩的差值 select a.*,abs(a.student_score-a.front_score) as score_diff from(select , first_value(student_score) over(partition by student_class order by student_id asc rows between 1 preceding and current row) as front_scorefrom student_score ) as a– 查询每一个学生的成绩同时还要获取同一个班级每一个学生和班级最高分的差值。 select a.,abs(a.max_score - a.student_score) as score_diff from (select * ,first_value(student_score) over(partition by student_class order by student_score desc) as max_scorefrom student_score ) as aselect ,abs(student_score - (max(student_score) over(partition by student_class rows between unbounded preceding and unbounded following))) as score_diff from student_score;用户自定义函数 用户自定义函数就是我们觉得hive内置函数不满足我们的需求我们可以自定义函数实现我们想要的功能 Hive自定义函数的步骤 Hive底层也都是Java自定义函数也是编写Java代码的 1、创建一个Java项目 2、引入编程依赖 1、创建lib目录自己找jar包放到lib目录下然后lib目录add as library hive的安装目录的lib目录下2、使用maven然后根据gav坐标引入依赖 3、编写对应的函数类UDF、UDTF、UDAF 大部分自定义都是UDF和UDTF函数 4、将编写好的Java代码打成jar包 5、将jar包上传到HDFS上 6、通过create function …从jar包以全限定类名的方式创建函数 注意自定义的函数创建的时候有两种创建方式 create [temporary] function function_name as “全限定类名” using jar “jar包在hdfs上的路径” 临时函数只对本次会话有效 只可以通过show functions查看元数据库不会记录永久函数永久生效 无法通过show functions查看,但是可以通过Hive的元数据库的FUNS表中查看 引入依赖 project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersiongroupIdcom.kang/groupIdartifactIdhive-function/artifactIdversion1.0/versionpackagingjar/packagingnamehive-function/nameurlhttp://maven.apache.org/urlpropertiesproject.build.sourceEncodingUTF-8/project.build.sourceEncoding/propertiesdependenciesdependencygroupIdorg.apache.hive/groupIdartifactIdhive-exec/artifactIdversion3.1.2/version/dependency/dependenciesbuildfinalNamehf/finalName/build /project编写Java代码 package com.kang.udf;import org.apache.hadoop.hive.ql.exec.UDFArgumentException; import org.apache.hadoop.hive.ql.metadata.HiveException; import org.apache.hadoop.hive.ql.udf.generic.GenericUDF; import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;/** 类就是从一个字符串中找大写字符个数的一个函数* UDF函数需要继承一个类GenericUDF并且重写三个方法/ public class FindUpperCount extends GenericUDF {/** 初始化方法方法是用来判断函数参数的* 指定函数参数的个数以及函数参数的类型* param objectInspectors 函数参数的类型和个数的一个数组* return 方法的返回值代表的是函数执行完成之后的返回值类型* throws UDFArgumentException/Overridepublic ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {/** 1、判断参数的类型和个数是否满足需求///数组的长度就是函数参数的个数int length objectInspectors.length;if(length ! 1){throw new UDFArgumentException(function only need one param);}else {//ObjectInspector是一个Hive数据类型的顶尖父类 参数的类型ObjectInspector objectInspector objectInspectors[0];//PrimitiveObjectInspectorFactory是Hive中所有基础数据类型的工厂类//返回函数的执行完成之后输出的结果类型 整数类型return PrimitiveObjectInspectorFactory.javaIntObjectInspector;}}/** 方法就是函数实现的核心逻辑和方法* param deferredObjects 函数传递的参数* return 返回值就是函数执行之后的返回结果返回结果必须和initialize的返回值类型保持一致* throws HiveException/Overridepublic Object evaluate(DeferredObject[] deferredObjects) throws HiveException {//获取函数传递的那一个参数DeferredObject deferredObject deferredObjects[0];//get方法是获取封装的参数值Object o deferredObject.get();String str o.toString();int num 0;for (char c : str.toCharArray()) {if (c 65 c 90){num;}}return num;}/** HQL的解析SQL的输出—没有用处* param strings* return/Overridepublic String getDisplayString(String[] strings) {return ;} }将Windows上书写的Java代码打成Jar包先上传到Linux系统中然后在通过Linux上传到HDFS的路径下,最后在Hive环境下进行运行 – 想通过HQL语句获取一个字符串中大写字符的数量 UDF – 根据HDFS上的jar包创建函数 – 临时函数可以通过show functions;查看到 create temporary function find_upper as com.kang.udf.FindUpperCount using jar hdfs://192.168.31.104:9000/hf.jar; show functions; select find_upper(AsdsdSsdsDdsdF); – 永久函数不可以通过show functions;查看到但是可以通过Hive的元数据库的FUNS表中查看 create function find_upper as com.kang.udf.FindUpperCount using jar hdfs://192.168.31.104:9000/hf.jar; select find_upper(AsdsdSsdsDdsdF);自定义UDF函数 package com.kang.udtf;import org.apache.hadoop.hive.ql.exec.UDFArgumentException; import org.apache.hadoop.hive.ql.metadata.HiveException; import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF; import org.apache.hadoop.hive.serde2.objectinspector.; import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;import java.util.ArrayList; import java.util.List;/*** 输入参数有两个* 字符串* 分隔符* 输出结果一列多行的结果* word* zs* ls*/ public class SplitPlus extends GenericUDTF {/*作用* 1、校验输入的参数* 2、返回UDTF函数返回值的类型和函数返回的列的个数、名字、类型* param argOIs 当作一个数组来看里面多个参数组成的* return* throws UDFArgumentException*/Overridepublic StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {List? extends StructField allStructFieldRefs argOIs.getAllStructFieldRefs();if (allStructFieldRefs.size() ! 2){throw new UDFArgumentException(function need two params);}else {/* 返回一列多行 UDTF函数可以返回多行多列///返回的列的名字 是一个集合 集合有几项 代表UDTF函数返回几列ListString columnNames new ArrayList();columnNames.add(word);//返回的列的类型 集合的个数必须和columnNames集合的个数保持一致ListObjectInspector columnTypes new ArrayList();columnTypes.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);//构建StandardListObjectInspector需要两个List集合 ListString ListObjectInspectorStandardStructObjectInspector standardStructObjectInspector ObjectInspectorFactory.getStandardStructObjectInspector(columnNames, columnTypes);return standardStructObjectInspector;}}/** UDTF函数执行的核心逻辑* 结果的输出需要借助forward方法* param objects 函数的输入参数* throws HiveException/Overridepublic void process(Object[] objects) throws HiveException {String str objects[0].toString();String split objects[1].toString();String[] array str.split(split);for (String s : array) {//一行数据需要输出一次 如果输出一行数据 那么只需要 调用一次forward方法即可/** 如果一行数据有多列可以先创建一个List集合ListObject集合中把一行的多列值全部加进来/forward(s);}}/** close用于关闭一些外部资源* throws HiveException*/Overridepublic void close() throws HiveException {} }– 想实现一个类似于split的函数功能split函数切割之后返回多行数据而非一个数组 – split(zs-ls,-):array(zs,ls) split_plus(zs-ls,-):zs ls create function split_plus as com.kang.udtf.SplitPlus using jar hdfs://192.168.31.104:9000/hf.jar; list jars; DELETE jar;select split(zs-ls-ww,-); select split_plus(zs-ls-ww,-);自定义UDTF函数 删除自定义函数drop function 函数名 【注意】用户自定义函数有一个特别重要的问题自定义函数和数据库绑定的。只能在创建函数的数据库使用函数。如果要在其他数据库下使用需要在其他数据库下把函数重新创建一遍即可。 Hive函数的分类 UDF函数一对一函数输入一个数据输出一个数据UDTF函数一对多函数输入一个数据输出多个数据UDAF函数多对一函数输入多个数据输出一个数据自定义函数侧视图函数专门用来搞笛卡尔乘积的
十、Hive压缩机制 Hive底层会转换成为MapReduce运行MapReduce阶段中间都是可以进行压缩的。因此Hive也支持设置压缩机制也是设置转换的MR程序底层是Map阶段压缩 还是reduce阶段压缩 Hive底层也可以转换成为Spark或者TEZ程序运行Spark和TEZ的压缩和Mapreduce的压缩是不一样的。