Hive
数据仓库
概念
数据仓库（Data Warehouse,简称数仓、DW），是一个用于存储，分许，报告的数据系统。
数据仓库的目的是构建面向分析的集成数据环境，分析结果为企业提供决策支持。
数据仓库本身并不是“生产”任何数据，其数据来源于不同外部系统；
同时数据仓库自身也不需要‘消费“任何的数据，其结果开放给各个外部应用使用；
这也是为什么叫“仓库”，而不叫“工厂”的原因
OLTP、OLAP
联机事物处理 OLTP
联机分析处理OLAP
OLTP系统
- 操作型处理，叫联机事物处理OLTP,主要目标是做数据处理，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改
- 用户较为关心操作的响应时间，数据的安全性，完整性和并发支持的用户数等问题。
- 传统的关系型数据库系统，作为数据管理的主要手段，主要用于操作型处理。
OLTP系统
- 分析型处理，叫联机分析处理OLAP,主要目标是做数据分析。
- 一般针对某些主题的历史数据进行复杂的多维分析，支持管理决策。
- 数据仓库是OLAP系统的一个典型实例，主要用于数据分析。
对比
OLTP OLAP 数据源 仅包含当前运行日常业务 整合来自多个来源的数据，包括OLTP和外部来源 目的 面向应用，面向业务，支撑事务 面向主题，面向分析，支撑分析决策 焦点 当下 主要面向过去，面向历史，实时数仓除外 任务 读写操作 大量读而很少写操作 响应时间 毫秒 秒，分钟，小时，或者天取决于数据和查询复杂性 数据量 小数据，MB,GB 大数据，TP,PB 数据仓库、数据库的区别
- 数据仓库不是大型的数据库，虽然数据仓库存储数据规模大。
- 数据仓库的出现，并不是要取代数据库
- 数据库是面向事务的设计，数据仓库是面向主题的设计的。
- 数据库一般存储业务数据，数据仓库存储的一般是历史数据
- 数据库是为了捕获数据而设计，数据仓库是为了分析数据而设计
数据仓库，数据集市
- 数据仓库是面向整个集团组织的数据，数据集市是面向单个部门使用的。
- 可以任务数据集市是数据仓库的子集，也有人吧数据集市叫做小型数据仓库。数据集市通常只涉及一个主题领域，例如市场营销或者销售。因为他们较小且更具体，所以他们通常更易于管理和维护，并具有更灵活的结构。
数据仓库分层架构的好处
分层的主要原因是在管理数据的时候，能对数据有一个更加清晰的掌控，详细来讲，主要有下面几个原因：
- 清晰数据结构
每一个数据分层都有它的作用域，在使用表的时候能更方便的定位和理解。
- 数据血缘最终
简单来说，我们最终给业务呈现的是一个能直接使用业务表，但是它的来源有很多，如果有一张来源表出问题了，我们希望能够快速准确地定位到问题，并清楚它的危害范围。
- 减少重复开发
规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算。
- 把复杂问题简单化
将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单的容易理解，而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。
- 屏蔽原始数据的异常
屏蔽业务的影响，不必该一次业务就需要重新接入数据
Hive概述
什么是Hive
- Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化，半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模块，称为Hive查询语言（HQL），用于访问和分析存储在Hadoop文件中的大型数据集。
- Hive核心是将HQL转换为MapReduce存续，然后将程序提交到Hadoop集群执行。
- Hive由Facebook实现并开源。
为什么使用Hive
使用Hadoop MapReduce直接处理数据所面临的问题
- 人员学习成本太高需要掌握java语言
- MapReduce实现复杂查询逻辑开发难度太大
使用Hive处理数据的好处
- 操作接口采用类SQL语法，提供快速开发的能力（简单，容易上手）
- 避免直接写MapReduce，减少开发人员的学习成本
- 支持自定义函数，功能扩展很方便
- 背靠Hadoop，擅长存储分析的海量数据集
HIve和Hadoop关系
从功能来说，数据仓库软件，至少需要具备下述两种能力：
- 存储数据的能力，分析数据的能力
Apache Hive作为一款大数据时代的数据仓库软件，当然也具备上述两种能力。只不过Hive并不是自己实现了上述两种能力，而是借助Hadoop.
- Hive利用HDFS存储数据，利用MapReduce查询分析数据
这样突然发现Hive没啥用，不过是套壳Hadoop罢了。其实不然，Hive的最大的魅力在于用户专注于编写HQL,Hive帮您转换为MapReduce程序对数据的分析。
场景案例：如何模拟实现Apache Hive的功能
Hive能将数据文件映射成一张表，这个映射是指什么？
- 文件和表之间的对应关系
Hive软件本身到底承担了什么功能职责？
- SQL语法解析编译成为MapReduce
Hive架构、组件
组件
- 用户接口
包括CLI、JDBC/ODBC、WebCUI。其中，CLI为shell命令行；Hive中的Thrift服务器语序外部客户端通过网络与Hive进行交互，类似于JDBC或ODBC协议。WebCUI是通过浏览器访问Hive.
Hive数据模型
Data Model概念
- 数据模型：用来描述数据，组织数据和对数据进行操作，是对现实世界数据特征的描述。
- Hive的数据模型类似于RDBMS库表结构，此外还有自己特有的模型。
- Hive中的数据可以在粒度级别上分为三类：
Table 表
Partition 分区
Bucket 桶
Databases 数据库
Hive作为一个数据仓库，在结构上积极向传统数据库看齐，也分数据仓库（Schema），每个数据库下面有各自的表组成。默认数据库default.
Hive的数据都是存储在HDFS上的，默认有一个根目录，在Hive-site.xml中，由参数hive.metastore.warehouse.dir指定。默认值为/user/hive/warehouse。
因此，Hive中的数据库在HDFS上的存储路径为：
${hive.metastore.warehouse.dir}/databasename.db
- 比如，名为itcast的数据库存储路径为：
/user/hive/warehouse/itcast.db
Tables 表
Hive表与关系数据库中的表相同。Hive中的表所对应的数据通常是存储在HDFS中，而表相关的元数据是存储在RDBMS中。
Hive中的表的数据在HDFS上的存储路径为：
${hive.metastore.warehouse.dir}/databasename.db/tablename
- 比如，itcast的数据库下t_user表存储路径为：
/user/hive/warehouse/itcast.db/t_user
Partitions分区
- Partition分区是hive的一种优化手段表。分区是指根据分区列（例如“日期day”）的值将表划分为不同分区。这样可以更快的对指定分区数据进行查询。
- 分区在存储层面上的表现是：table表目录下以子文件夹形式存在。
- 一个文件夹表示一个分区。子文件命名标准：分区列=分区值
- Hive还支持分区下继续创建分区，所谓的多重分区。关于分区表的使用和详细介绍，后面模块会单独展开
Buckets 分桶
- Bucket分桶表示hive的一种优化手段。分桶是指根据字段（例如“编号ID”）的值，经过hash计算规则将数据文件划分成指定的若干个小文件。
- 分桶规则：hashfunc(字段)% 桶个数，余数相同的分到同一个文件。
Hive和MySQL对比
Hive虽然具有RDBNS数据 库的外表，包括数据模型、SQL语法都十分相似，但应用场景却完全不同
Hive只适合用来做海量数据的离线分析。Hive的定位是数据仓库，面向分析的OLAP系统。
因此时刻告诉自己，HIve不是大型数据库，也不是要取代Mysql承担业务数据处理。
元数据
什么是元数据
- 元数据，又称中介数据、中继数据，为描述数据的数据，主要是描述数据属性的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。、
Hive Metadata
- Hive Metadata即Hive的元数据
- 包含用Hive创建的database、table、表的位置、类型等元信息。
- 元数据存储在关系型数据库中。如Hive内置的Derby、或者第三方如MySql等。
Hive Metastore
- Metastore即元数据服务。Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通过连接metastore服务，由metastore在去连接MySQL数据库来存储元数据。
- 有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore服务即可，某种程度上也保证了hive元数据的安全。
Metastore配置方式
内嵌模式
- 内嵌模式是metastore默认部署模式。
- 此种模式下，元数据存储在内置的Derby数据库，并且Derby和metastore都会启动。不需要额外起Metastore服务。
- 但是一次只能支持一个活动用户，适用于测试体验，不适用与生产环境。
本地模式
- 本地模式下，Metastore服务HiveServer进程在同一进程中运行，但是存储元数据的数据库在单独的进程中运行，并且可以在单独的主机上。metastore服务将通过JDBC与metastore数据库进行通信。
- 本地模式采用外部数据库来存储元数据，推荐使用MySQL
- hive根据hive.metastore.uris 参数值来判断，如果为空，则为本地模式。
- 缺点是：每启动一次hive服务，都内置启动了一个metastore。
远程模式
- 远程模式下，Metastore服务在其自己的单独JVM上运行，而不在HiveServer的JVM中运行。如果其他进程希望与Metastore服务器通信，则可以使用ThriftNetwork API进行通信。
- 远程模式下，需要配置hive.metastore.uris参数来指定metastore服务运行的机器ip和端口，并且需要单独手动启动metastore服务。元数据也采用外部数据库来存储元数据，推荐使用MySQL
Hive部署实战
安装前准备
- 由于Apache Hive是一款基于Hadoop的数据仓库软件，通常部署运行在Linux系统之上。因此不管使用何种方式配置Hive Metastore，必须要先保证服务的基础环境正常，Hadoop集群健康可用。
- 服务其基础环境
- 集群时间同步，防火墙关闭，主机Host映射，免密登录，JDK安装
- Hadoop集群健康可用
- 启动Hive之前必须先启动Hadoop集群，特别要注意，需要等待HDFS安全模式关闭之后在启动运行Hive.
- Hive不是分布式安装运行的软件，其分布式的特性主要借由Hadoop完成，包括分布式存储，分布式计算。
Hadoop与Hive整合
- 因此Hive需要把数据存储在HDFS上，并且通过MapReduce作为执行引擎处理数据；
- 以你需要在hadoop中添加相关配置属性，以满足Hive在Hadoop上运行
- 修改Hadoop中core-site.xml，并且Hadoop集群同步配置文件，重启生效（该配置是配置非root用户之外的用户登陆）。
<!--整合hive--> <property><name>hadoop.proxyuser.root.hosts</name><value>*</value> </property> <property><name>hadoop.proxyuser.root.groups</name><value>*</value> </property>
方式1：内嵌模式安装
- 内嵌模式特征就是：不需要安装数据库，不需要配置启动Metastore服务，解压安装包初始化即可测试体验Hive.
- 注意：Hive3版本需要用户手动进行元数据初始化动作。
- 内嵌模式下，判断是否初始化成功的依据是执行命令之后输出信息和命令的当前路径下是否有文件生成
内嵌模式安装
#上传解压安装包tar -zxvf apache-hive-2.1.1-bin.tar.gz -C /usr/local#修改hive安装路径名，方便以后使用mv apache-hive-2.1.1-bin/ hive#解决hadoop、hive之间guava版本差异(这里想不要操作，如果jar包有问题出现异常在试试) # cd /usr/local/src/hive # rm -rf lib/guava-19.0.jar # cp /usr/local/src/hadoop/share/hadoop/common/lib/guava-11.0.2.jar ./lib/#修改hive环境变量文件 添加Hadoop_HOMEcd /hive/conf/mv hive-env.sh.template hive-env.shvim hive -env.shexport HADOOP_HOME=/usr/local/src/hadoopexport HIVE_CONF_DIR=/usr/local/src/hive/confexport HIVE_AUX_JARS_PATH=/usr/local/src/hive/lib #初始化metadatacd /usr/local/hivebin/schematool -dbType derby -initSchema#进入环境变量文件vim /etc/profile #添加如下内容：export HIVE_HOME=/usr/local/src/hiveexport PATH=$HIVE_HOME/bin:$PATH #让profile生效source /etc/profile
方式2：本地模式安装
- 本地模式特征就是：需要安装数据库MySQL来存储元数据，但是不需要配置启动Metastore服务。
- 注意：Hive3版本需要用户手动进行元数据初始化动作。
MySQL模式安装
#卸载Centos7自带mariadb rpm -qa|grep mariadb rpm -qa|grep mysql rpm -e 查询到的包名 --nodeps#创建mysql安装包存放点 mkdir /usr/local/src/mysql #上传mysql-5.7.29安装包到上述文件夹下、解压 tar xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar#执行安装 yum -y install libaio rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm mysql-community-libs-5.7.29-1.el7.x86_64.rpm mysql-community-client-5.7.29-1.el7.x86_64.rpm mysql-community-server-5.7.29-1.el7.x86_64.rpm#初始化mysql mysqld --initialize#更改所属组 chown mysql:mysql /var/lib/mysql -R#启动mysql systemctl start mysqld.service#查看生成的临时root密码 grep 'temporary password' /var/log/mysqld.log #这行日志的最后就是随机生成的临时密码（这个不是以上命令的，忽略即可） #[Note] A temporary password is generated for root@localhost: o+TU+KDOm004#修改mysql root密码、授权远程访问 mysql -u root -p Enter password: #这里输入在日志中生成的临时密码#更改mysql的root用户的登陆密码设置为hadoop mysql> alter user user() identified by "hadoop"; #更改成功显示 Query OK, 0 rows affected (0.00 sec)#授权 mysql> use mysql; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'hadoop' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;#或者也可以使用下面指令授权（这里是尚硅谷的，上面是黑马的，这里容易出错，建议使用上面） mysql> use mysql; #使用mysql表 mysql> select User,Host from user; #查询user表 mysql> update user set host='%' where host='localhost'; #把host表的内容修改为% mysql> delete from user where Host='127.0.0.1'; #删除root用户其他的host#mysql的启动和关闭 状态查看 systemctl stop mysqld systemctl status mysqld systemctl start mysqld#建议设置为开机自启动服务 systemctl enable mysqld#查看是否已经设置自启动成功 systemctl list-unit-files | grep mysqldHive本地模式安装
#--------------------Hive安装配置---------------------- # 上传解压安装包 cd /usr/local/src/ tar zxvf apache-hive-3.1.2-bin.tar.gz mv apache-hive-3.1.2-bin hive#解决hadoop、hive之间guava版本差异(这里想不要操作，如果jar包有问题出现异常在试试) # cd /usr/local/src/hive # rm -rf lib/guava-19.0.jar # cp /usr/local/src/hadoop/share/hadoop/common/lib/guava-11.0.2.jar ./lib/#添加mysql jdbc驱动到hive安装包lib/文件下 mysql-connector-java-5.1.32.jar #如果没有这个包，可以在官网进行下载#修改hive环境变量文件 添加Hadoop_HOME cd /export/server/hive/conf/ mv hive-env.sh.template hive-env.sh vim hive-env.s export HADOOP_HOME=/usr/local/src/hadoop export HIVE_CONF_DIR=/usr/local/src/hive/conf export HIVE_AUX_JARS_PATH=/usr/local/src/hive/lib#新增hive-site.xml 配置mysql等相关信息 vim hive-site.xml #在文件中添加以下配置即可 #-----------------Hive-site.xml--------------------- <configuration> <!-- 存储元数据mysql相关配置 --> <property><name>javax.jdo.option.ConnectionURL</name><value> jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value> </property><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value> </property><property><name>javax.jdo.option.ConnectionUserName</name><value>root</value> </property><property><name>javax.jdo.option.ConnectionPassword</name><value>hadoop</value> </property><!-- 关闭元数据存储授权 --> <property><name>hive.metastore.event.db.notification.api.auth</name><value>false</value> </property><!-- 关闭元数据存储版本的验证 --> <property><name>hive.metastore.schema.verification</name><value>false</value> </property> </configuration> #---------------------切换到命令行继续操作--------------------------#初始化metadata cd /usr/local/src/hive bin/schematool -initSchema -dbType mysql -verbos #登陆mysql查看是否初始化成功，初始化成功会在mysql中创建74张表 mysql> show databases; mysql> use hive; msql> show tables;#启动hive服务 bin/hive#在启动hive服务之前首先要想启动metastore，如果没有启动使用HQL指令查询会报错，启动metastore使用 bin/hive --service metastore #启动hiveserver2服务，如果想在IDEA或beeline客户端进行使用的话则必须启动hiveserver2服务 bin/hive --service hiveserver2#启动hive前的注意事项，在启动hive时需要启动metastore服务，因为，hive时通过metastore进行对元数据进行交互的，内嵌模式和本地模式启动hive的同时会自动启动metastore服务，远程模式下，需要自己手动启动，在使用beeline客户端和IDEA进行连接的时候，要想启动hiveserver2服务，因为beeline是需要先连接到hiveserver2在有hiveserver2连接到metastore，在有metastore进行交互
方式3：远程模式安装（重要）
- 远程模式最大的特点有两个：
- 需要安装MySQL来存储Hive元数据；
- 需要手动单独配置启动Metastore服务
- 本系列课程中采用远程模式安装Hive。
远程模式安装
# 这里使用的hive版本是 # 安装远程模式之前吧MySQL安装好，上面有步骤 # 上传解压安装包 cd /export/server/ tar zxvf apache-hive-3.1.2-bin.tar.gz mv apache-hive-3.1.2-bin hive#解决hadoop、hive之间guava版本差异(这里不用操作，因为操作容易造成metastore 和hiveserver2启动异常，改了就会jar包冲突) cd /export/server/hive rm -rf lib/guava-19.0.jar cp /usr/local/src/hadoop/share/hadoop/common/lib/guava-27.0-jre.jar ./lib/#添加mysql jdbc驱动到hive安装包lib/文件下 mysql-connector-java-5.1.32.jar#修改hive环境变量文件 添加Hadoop_HOME cd /usr/local/src/hive/conf mv hive-env.sh.template hive-env.sh vim hive-env.sh export HADOOP_HOME=/usr/local/src/hadoop export HIVE_CONF_DIR=/usr/local/src/hive/conf export HIVE_AUX_JAcd RS_PATH=/usr/local/src/hive/lib#新增hive-site.xml 配置mysql等相关信息 vim hive-site.xml#初始化metadata cd /usr/local/src/hive bin/schematool -initSchema -dbType mysql -verbos #初始化成功会在mysql中创建74张表#-----------------hive-site.xml-------------- <configuration> <!-- 存储元数据mysql相关配置 --> <property><name>javax.jdo.option.ConnectionURL</name><value> jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value> </property> <!-- 使用指定的连接驱动进行连接 --> <property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value> </property><property><name>javax.jdo.option.ConnectionUserName</name><value>root</value> </property><property><name>javax.jdo.option.ConnectionPassword</name><value>hadoop</value> </property><!-- H2S运行绑定host --> <property><name>hive.server2.thrift.bind.host</name><value>master</value> </property><!-- 远程模式部署metastore 服务地址 --> <property><name>hive.metastore.uris</name><value>thrift://master:9083</value> </property><!-- 关闭元数据存储授权 --> <property><name>hive.metastore.event.db.notification.api.auth</name><value>false</value> </property><!-- 关闭元数据存储版本的验证 --> <property><name>hive.metastore.schema.verification</name><value>false</value> </property> </configuration>#-----------------core-site.xml--------------<property><name>hadoop.proxyuser.root.hosts</name><value>*</value></property><property><name>hadoop.proxyuser.root.groups</name><value>*</value></property>#-----------------Metastore Hiveserver2启动---- #前台启动 关闭ctrl+cha /usr/local/src/hive/bin/hive --service metastore#后台启动 进程挂起 关闭使用jps + kill #输入命令回车执行 再次回车 进程将挂起后台 nohup /export/server/hive/bin/hive --service metastore &#前台启动开启debug日志 /export/server/hive/bin/hive --service metastore --hiveconf hive.root.logger=DEBUG,console
Hive命令行客户端
Hive发展至今，总共经历了两代客户端工具。
- 第一代客户端（deprecated 不推荐使用）：$HIVE_HOME/bin/hive,是一个shellUtil.主要功能；一是可以用于以交互或批处理模式运行Hive查询；二是用于Hive相关服务的启动，比如metastore服务。
- 第二代客户端（recommended 推荐使用）：$HIVE_HOME/bin/beeline,是一个JDBC客户端，是官方强烈推荐使用的Hive行工具，和第一点客户端相比，性能加强安全性提高。
Beeline 客户端
- Beeline在嵌入式模式和远程模式下均可工作。
- 在嵌入模式下，它运行嵌入式Hive（类似Hive Client）；而远程模式下beeline通过Thrift连接到单独的HiveServer2服务上，这也是官方推荐在生产环境中使用的模式。
HiveServer、HiveServer2服务
- HiveServer、HiveServer2都是Hive自带的两种服务，允许客户端在不启动CLI（命令行）的情况下对Hive中的数据进行操作，且两个都允许远程客户端使用多种编程语言如Java,python等面向hive提交请求，取回结果。
- 但是，HiveServer不能处理多于一个客户端的并发请求，因此在Hive-0.11.0版本中重写了HiveServer代码得到了HiveServer2,进而解决了该问题。HiveServer已经被废弃。
- HiveServere2支持多客户端的并发和身份认证，只在为开放API客户端如JDBC、ODBC提供更好的支持。
Hive服务和客户端
关系梳理
HiveServer2通过Metastore服务读写元数据。所以在远程模式下，启动HiveServer2之前必须首先启动metastore服务。
特别注意：远程模式下，Beeline客户端只能通过HiveServer2服务访问Hive.而bin/hive是通过Metastore服务访问的。
bin/hive 客户端
在hive安装包的bin目录下，有hive提供的第一代客户端bin/hive。该客户端可以访问Hive的metastore服务，从而达到操作Hive的目的。
友情提示：如果您是远程模式部署，请手动启动运行metastore服务。如果是内嵌模式和本地模式，直接运行bin/hive,metastore服务会内嵌一起启动。
可以直接在启动Hive metastore 服务的机器上使用bin/hive客户端操作，此时不需要进行任何配置。
#远程模式 首先启动metastore服务 /usr/local/src/hive/bin/hive --service metastore #克隆CRT回话窗口 使用hive client连接 /usr/local/src/hive/bin/hive
bin/beeline 客户端
hive经过发展，推出了第二代客户端beeli但是beeline客户端不是直接访问metastore服务的，而是需要单独启动hiveserver2服务。
在hive安装的服务器上，首先启动metastore服务，然后启动hiveserver2服务
#先启动metastore服务 然后启动hiveserver2服务 nohup /usr/local/src/hive/bin/hive --service metastore & nohup /usr/local/src/hive/bin/hive --service hiveserver2 &
在node3上使用beeline客户端进行连接访问。需要注意hiveserver2服务启动之后需要稍等一会才可以对外提供服务。
Beeline是JDBC的客户端，通过JDBC协议和Hiveserver2服务进行通信，协议的地址是：jdbc:hive2://node1:10000
! connect jdbc:hive2://master:10000
Hive在命令行执行HQL
bin/hive -e "select * from aa;" #hiv后面家参数e，就可以直接在命令行执行hql语句了bin/hive -f ./hive.hql #在命令行执行一个写好的hql文件/usr/local/src/hive/bin/hive -f hive.hql >> ./history.txt #在命令行模式下执行一个编写好的hql文件，在吧执行的结果追加到history.txt文件中
Hive其他命令操作
#1、在hive cli命令窗口中如何查看hdfs文件系统 hive> dfs -ls /;#2、在hive cli命令窗口中如何查看本地文件系统 hive> !ls /opt/module/datas;#3、查看在hive中输入的所有历史命令 #（1）进入到当前用户的根目录/root或/home/atguigu #（2）查看.hivehistory文件cat .hivehistory #4、在hive cli命令窗口以键值对的形式查看相应配置的配置 hive> set mapred.reduce.tasks;#5、在命令行使用键值对的方式更改相应配置的信息 bin/hive -hiveconf mapred.reduce.tasks
Hive使用起来和MySQl差不多吗
结论：
- Hive SQL语法和标准SQL很类似，使得学习成本降低不少。
- Hive底层是通过MapReduce执行的数据插入动作，所以速度慢。
- 如果大数据集这么一条一条插入的话非常不现实，成本极高。
- Hive应该具有自己的数据插入表方式，结构化文件映射成为表。
Hive如何才能将结构化数据映射成为表
创建一张表，添加分割符语法
create table t_user(id int,name verchar(255),age int,city varchar(255)) row format delimited fields terminated by',';把user.txt文件从本地文件系统上传到hdfs
hdfs dfs -put user.txt /user/hive/warehouse/itcast.db/t_user/执行查询操作
select * from t_user;结论
要先在hive中创建表跟结构化文件映射成功，需要注意一下几个方面的问题：
创建表时，字段顺序，字段类型要和文件中保持一致。
如果类型不一致，hive会尝试转换，但是不保证转换成功，不成功为null.
Hive数据类型
基本数据类型
Hive数据类型 Java数据类型 长度 例子 TINYINT byte 1byte 有符号整数 20 SMALINT short 2byte有符号整数 20 INT int 4byte有符号整数 20 BIGINT long 8byte有符号整数 20 BOOLEAN boolean 布尔类型，true或者false TRUE ,FALSE FLOAT float 单精度浮点型 3.14 DOUDBLE double 双精度浮点型 3.14 STRING string 支付系列。可以指定字符集，可以使用单引号或者双引号 ‘now is the time’ TIMESTAMP 时间类型 BINARY 直接数组 对于hive的string类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明最多能存储多少个字符，理论上它可以存储2GB的字符数
集合数据类型
数据类型 描述 语法实例 STRUCT 和c预防struce类似，都可以通过“点"符合访问元素内容。例如如果某个列的数据类型是STRUCT{first STRING,last STRING},那么第一个元素可以通过字段.first来引用 struct() MAP MAP是一组件-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是Map，其中键->值对是‘first’->‘john’和’last‘->’Doe’,那么可以通过字段名[‘list’]获取最后一个元素 map() ARRAY 数组是一组具有相同类型和名称的变量集合，这些变量称为数据的元素，每个数据元素都有一个编号，编号从零开始。例如，数组值为[’john’,‘Doe’],那么第二个元素可以通过数据[1]进行引用 Array() Hive有三种复杂数据类型ARRAY、MAP和STRUCT。ARRAY和MAP与Java中的Array和Map类似，二STRUCT与c语言中的struct类似，它封装了一个命令字段集合，复杂数据类型允许任意层次的嵌套。
类型转换
Hive原子数据类型是可以进行隐式转换的，类似于java的类型转换，例如某表达式是哟INT类型，TINYINT会自动转换为INT类型，但是HIve不会进行反向转换，例如吗，某表达式使用TINYINT类型，它会返回错误，除非使用CAST操作。
- 隐式转换类型规则如下
- 任何整数类型都可以隐式转换的转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
- 所有整数类型，FLOAT和STRING类型都可以隐式转换成DOUBLE。
- TINYINT、SMALLTNT、INT都可以转换为FLOAT.
- BOOLEAN类型不可以转换为任何其他的类型。
- 可以使用CAST操作显示进行数据类型转换。
- 例如CAST(1,ASINT)将字符串’1’转换成整数1；如果强制类型转换失败，如执行CAST('X’ASINT)，表达式返回控制NULL.
数据定义语言（DDL）
SQL中DDL语法的作用
- 数据定义语言，是SQL语言中集中对数据库内部的对象结构进行创建，删除，修改等操作语言，这些数据对象包括database、table、view、index等。
- DDL核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。
- 在某些上下文中，该术语也称为数据描述语言，因为它描述了数据库中的字段和记录。
Hive数据类型详解
- Hive数据类型指的是表中列的字段类型
- 整体分为两类：原生数据类型，和复杂数据类型
- 原生数据类型包括：数值类型，时间日期类型，字符串类型，杂项数据类型；
- 复杂数据类型包括：array数组，map映射，struct结构，union联合体。
注意事项
- Hive SQL中，数据类型英文字母大小不敏感；
- 除SQL数据类型外，还支持java数据类型，比如字符串String；
- 复杂数据类型的使用通常需要和分隔符指定语法配合使用；
- 如果定义的数据类型和文件不一致，Hive会尝试隐式转换，但是不保证成功。
Hive读写文件机制
SerDe是什么
- SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。
- 序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。
- Hive使用SerDe（包括FileFormat）读取和吸入表行对象。需要注意的是，“key”部分在读取时会被忽略，而在吸入时key始终是参数，基本上行对象存储在“value”中。
Hive读文件流程
- Hive读取文件机制：首先调研InputFormat(默认TextInputFormat),返回一条一条kv键值对记录（默认是一行对应一条键值对）。然后调用SerDe（默认LazySimpleSerDe）的Deserializer，将一条记录中的value根据分隔切分为各个字段。
- Hive写文件机制：将Row吸入文件时，首先调用SerDe（默认LazySimleSerDe）的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。
LazySimpleSerDe分隔符指定
- LazySimpleSerDe是Hive默认的序列化类，包含4种语法，分别用于指定字段之间，集合元素之间，map映射 kv之间，换行的分隔符号。
- 在建表的时候可以根据数据的特点灵活搭配使用。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fNEHZ7M7-1666068736591)(C:\Users\云\AppData\Roaming\Typora\typora-user-images\image-20220930171805331.png)]
Hive数据存储路径
默认存储路径
- Hive表默认存储路径是由${HIVE_HOME} /conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定默认值是：/user/hive/warehouse。
指定存储路径
- 在Hive建表的时候，可以通过location语法来更改数据在HDFS上的存储路径，使得建表加载数据更加灵活方便。
- 语法：location ’<hdfs_location>‘。
- 对于已经生成好的数据文件，使用location指定路径会很方便。
基本语法
创建库
create database 库名;查看库
show databases;切换数据库
use 库命;创建库，并指定存储位置
create database 库名 location '路径';查询数据库结构
desc database 库名;模糊查询
show databases like 'hive*';修改数据库
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性名和数据库所在的目录位置。 alter database db_hive set dbproperties('createtime'='20170830');删除数据库
1、删除空数据库 drop database db_hive2;2、如果删除的数据库不存在，最好此采用if exists 判断数据库是否存在 drop database if exists db_hive2;3、如果数据库不为空，可以采用cascade命令，强制删除 drop database db_hive cascade;1、建表语法
create [external] tanle [if not exists] table_name [(col_name data_type [comment col_comment],...)] [comment table_comment] [Partitioned by (col_name data_type [comment col_comment],....)] [clustered by (col_name,col_name,...)] [sorted by (col_name [asc | desc],...)] into num_buckets buckets] [row format row_fomat] [stored as file_format] [location hdfs_path]2、字段解释说明
1、create table 创建一个指定名字的表，如果相同名字的表已经存在，则抛出异常；用户可以用 if not exists 选项来忽略这个异常。 2、external 关键字可以让用户创建一个外部表，在创建的同时指定一个指向实际数据的路径（location），Hive创建内部表时，会将数据移动到数据仓库指向的路径，若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变，在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，部删除数据。 3、comment,为表和列添加注释。 4、partitioned by 创建分区表 5、clustered by 创建分桶表 6、sorted by 不常用 7、row format 用户在创建表的时候可以自定义serDe或者使用自带的serDe。如果没有指定ROWFORMAT或者 row format delimited ，将会使用自带的serDe,在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的serDe，hive通过serDe切丁表的躯体列的数据。SerDe是serialize/Deserilize的简称，目的是用于序列化的反序列化。 8、stored as 指定存储文件类型，常用的存储文件类型：sequencefile（二进制序列文件）、textfile(文本)、rcfile(列式存储格式文件)如果文件数据式纯文本，可以使用stored as textfile.如果数据需要压缩，使用stored as sequencefile. 9、location: 指定表在hdfs上存储的位置 10、like语序用户复制现有的表结构，但是不复制数据。建表
create table 表名(name string, age int )查看表
show tables;查看指定表的元数据信息
describe formatted itheima.student_trans;创建表结构
create table t_archer( id int comment "ID", name string comment "英雄名称", hp_max int comment "最大生命", mp_max int comment "最大法力", attack_max int comment "最高物攻", defense_max int comment "最打物攻", datack_range string comment "攻击范围", role_main string comment "主要定位", role_assist string comment "次要定位" )comment "王者荣耀射手信息"向表中装载本地数据
load data local inpath '数据路径名' into table 表名;向表中转hdfs数据
load data inpath '数据路径名' into table 表名;删除表
drop table 表名; --删除表的元数据和数据 如果已经配置来几桶且未指定purge，则该表对应的数据实际上将移动到HDFS垃圾桶，而元数据完全丢失。删除external表时，该表中的数据不会从文件系统中删除，只删除元数据。 如果指定了purge，则表数据跳过hdfs垃圾桶直接被删除，因此如果drop失败，则无法挽回该表数据。清空表
truncate table 表名; --可以简单理解为清空表的所有数据但是保留表的元数据结构。如果hdfs启动了垃圾桶，数据将被丢进垃圾桶，否则将被删除。更改表名
alter table table_name Rename to new_table_name;更多熟悉
--查询指定表的元数据信息 describe formatted itheima.student_partition;--1、更改表名 ALTER TABLE table_name RENAME TO new_table_name; --2、更改表属性 ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... ); --更改表注释 ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table"); --3、更改SerDe属性 ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )]; ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties; ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ','); --移除SerDe属性 ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );--4、更改表的文件存储格式 该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进行。 ALTER TABLE table_name SET FILEFORMAT file_format; --5、更改表的存储位置路径 ALTER TABLE table_name SET LOCATION "new location";--6、更改列名称/类型/位置/注释 CREATE TABLE test_change (a int, b int, c int); // First change column a's name to a1. ALTER TABLE test_change CHANGE a a1 INT; // Next change column a1's name to a2, its data type to string, and put it after column b. ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b; // The new table's structure is: b int, a2 string, c int. // Then change column c's name to c1, and put it as the first column. ALTER TABLE test_change CHANGE c c1 INT FIRST; // The new table's structure is: c1 int, b int, a2 string. // Add a comment to column a1 ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';--7、添加/替换列 --使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。 --REPLACE COLUMNS 将删除所有现有列，并添加新的列集。ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);修改表，更改表的属性
alter table table_name set tblproperties(property_name = property_value,...);指定字段之间的分隔
row format delimited fields terminated by '\t';指定集合之间的分隔符
row format delimitedcollection items terminated by '-';指定map元素KV之间的分隔符
row format delimitedmap keys terminated by ':';不指定分隔符使用默认的\001进行分隔
--没有采用row format语句 此时采用的是默认的\001作为字段的分隔符 create table t_team_ace_player( id int, team_name string, ace_player_name string );使用location关键字指定本张表数据在hdfs上存储的路径
create table t_team_ace_player_location( id int, team_name string, ace_player_name string) location '/data' --使用location关键字指定本张表数据在hdfs上存储路径创建外部表
create external table student_ext( // 创建外部表 num int, name string, )row format delimited fields terminated by ',' // 指定字段之间的分隔符 location '/stu'; // 指定外部表存储的位置查询student2表的类型
desc formattd student2;修改内部表student2为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');修改外部表student2为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’）为固定写法，区分大小写
数据导入
向表中装载数据(重点)
语法
load data [local] inpath '数据的path' [overwrite] into table student [partition (partcoll=vall,...)]
- load data：表示加载数据
- local：表示从本地加载数据到hive表；否则从hdfs加载数据到hive表
- inpath：表示加载数据的路径
- overwrite：表示覆盖表中已有的数据，否则表示追加
- into table:表示加载到哪张表
- student：表示具体的表
- partition：表示上传到指定的分区
通过查询语句向表中插入数据（Insert)( 重点）
1、创建一张表
cretate table student_par(id int,name string)row format delimited fields terminated by '\t'2、基本插入数据
insert into table student_par value(1,'wangwu'),(2,'zhaoliu');3、基本模式插入（根据单张表查询结果）
insert overwrite table student_par select id,name from student where month='201709'; insert into:以追加数据的方式插入到表或分区，原有数据不会删除
insert overwrite :会覆盖表中已存在的数据
注意：insert不支持插入部分字段
4、多表（多分区）插入模式（根据多张表查询结果）
from student insert overwrite table student partition(month='201707') select id,name where month='201709' insert overwrite table student partition(month='201706') select id,name where month='201709';
查询语句中创建表并加载数据
根据查询结果创建表（查询的结果会添加到新的表中）
create table if not exists student3 as select id,name from student;
Import数据到指定Hive表中
注意：先用export导出后，在将数据导入。
import table student2 form '/user/hive/warehouse/export/student';
数据导出
注意：这里的导出要慎用，因为有覆盖的关键词overwrite所以导入的文件夹一般不要存放文件，否则会覆盖该文件。
将查询的结果导出到本地
insert overwrite local directory '/opt/hive/datas/export/student' select * from student;将查询的结果格式化导出到本地
insert overwrite local directory '/opt/hive/datas/export/student' row format delimited fields terminated by '\t'将查询到的结果以\t分隔导出到hdfs上存储为orc文件格式（没有local）
insert overwrite directory '/user/atguigu/student2' row format delimited fields terminated by '\t' stored as orc select * from student;
Hadoop命令导出到本地
dfs -get /user/hive/warehouse/student/student.txt /opt;
Hive shell命令导出
基本语法：（hive -f/-e 执行语句或者脚本 > file）
bin/hive -e 'select * from defaut.student;' >opt/
Export 导出到HDFS上
export table default.student to '/user/hive/warehouse/export/student';
Sqoop导出
它是直接将数据导入到MySQL当中的，后续在学习
清除表中数据（Truncate）
注意：Truncate只能删除管理表，不能删除外部中数据
truncate table student;
查询语句
基本查询
全表查询
select * from from emp; select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
选择特定列查询
select empno,ename from emp;注意：
- sql语言大小写不敏感
- SQL可以卸载一行或者多行
- 关键字不能被缩写也不能分行
- 各子句一般要分行写。
- 使用缩进提高语句的可读性。
列别名
重命名一个列
便于计算
紧跟列明，也可以在列名之间加入关键字 ‘as’
案例实操
select ename as name,deptno dn from emp;
常用函数
求总行数（count）
select count(*) cnt from emp;求工资的最大值（max）
select max(sal) as max_sal from emp;求工资的最小值（min）
select min(sal) as min_sal from emp;求工资的总和（sum）
select sum(sal) as sum_sal from emp;求工资的平均值（avg）
select avg(sal) as avg_sal from emp;
Limit语句
电信的查询会返回多行数据，Limit子句用于限制返回的行数。
select * from emp limit 5;select * from emp limit 2,3;
where语句
使用where子句，将不满足条件的行过来掉
where子句紧随from子句
实例实操
查询出薪水大于1000的所有员工
select * from emp where sal > 1000;注意：where之句不能使用字段别名
比较运算符
运算符 支持的数据类型 描述 a [not] between b and c 基本数据类型 如果a,b或者c任一为null，则结果为null,如果a的值大于等于b且小于或等于c，则结果为true，反之为false。如果使用not关键字则可以达到相反的 a is null 所有数据类型 如果a等于null，则返回true，反之返回false a is not null 所有数据类型 如果a不等于null，则返回true，反之返回false a [not] like b string b 是一个SQL下的简单正则表达式，也叫通配符模式，如果a与其匹配的话，则返回true，反之返回false.B的表达式说明如下：’x%'表示a必须以字母x开头，‘%x‘表示a必须以字母x结尾，而’%x%‘表示a包含字母x，可以位于开头，结尾或者字符串中间，如果是使用not关键字则可以达到相反的效果。 in(数值1，数值2) 所有数据类型 使用IN运算符显示列表中的值 案例实操
查询出薪水等于5000的所有员工
select * from emp where sal =5000查询工资在500到1000的员工信息
select * from emp where sal between 500 and 1000;查询comm为空的员工信息
select * from emp where comm is null;查询工资是1500或者5000的员工信息
select * from emp where sal in(1500,5000);
like 和rlike
使用like运算符选择类似的值
选择条件可以包含字符或数字：
%代表零个或多个字符（任意字符）
_代表一个字符
rlike子句
rlike子句是hive中这个功能的一个扩展，其可以通过java的正则表达式这个更强大的语言来指定匹配的条件。
案例实操
查找名字以A开头的员工信息
select * from emp where ename like 'A%';查找名字中有第二个字母为A的员工信息
select * from emp where ename like '_A%';查找名字中带A的员工信息
select * from emp where ename like '%A%'; select * from emp where ename rlike '[A]'; --跟上面意思是一样的
分组
group by语句
group by语句通常会和聚合函数一起使用，按照一个或者多个列队进行分组，然后队每个组执行聚合操作
案例实操
- 计算emp表每个部门的平均工资
selectt.deptno,avg(t.sal) as avg_sal from emp as t group by t.deptno;
- 计算emp每个部门后每个岗位的最高薪水
selectt.deptno,t.job,max(t.sal) as max_sal from emp as t group by t.deptno;
join 语句
等值join
hive支持通常的sql join语句。
案例实操
- 根据员工表和部门表中的编号相等，查询员工编号，员工名称和部门名称：
select e.empno,d.deptno,d.dname from emp as e join dept as d on e.deptno = d.deptno;
内连接
内连接:只有进行连接的两个表中都存在与连接条件相匹配的的数据才会被保留下来
select e.empno,d.ename,d.deptno from emp as e inner joindept as d on e.deptno = d.deptno;
左外连接
左外连接：JOIN操作符左边表中符合where子句的所有记录将会被返回。
select e.empno,e.ename,d.deptno from emp as e left joindept as d on e.deptno = d.deptno;
右外连接
右外连接：join操作字符右边表符合where子句的所有记录将会被返回。
selecte.empno,e.ename,d.deptno from emp as e right join dept d one.deptno = d.deptno;
满外连接
满外连接：将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值得话，那么就使用null值替代。
select e.empno,e.ename,d.deptno fromemp as e full join dept d on e.deptno = d.deptno;
笛卡尔积
笛卡尔积会在下面条件产生
- 省略连接条件
- 连接条件无效
- 所有表中的所有行互相连接
排序
全局排序（order by）
order by:全局培训，只有一个Reducer
使用order by子句排序
Asc ：升序（默认）
desc : 降序
order by 子句在select语句的结尾
案例实操
查询员工信息工资升序排列
select * from emp order by sal;查询员工信息按工资降序排列
select * from emp order by sal desc;
按照别名排序
按照员工薪水的2倍排序
select ename,sal*2 as twosal from emp order by twosal;
多个列排序
按照部门和工资升序排序
select ename,deptno,sal from emp order by deptno,sal;
每个Reduce内部排序（Sort by）
Sort By：对于大规模的数据集order by的效率非常低。在效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。
Sort by为每个reducer产生一个排序文件。每个reduce内部进行排序，对全局结果集来说不是排序。
设置reduce个数
set mapreduce.job.reduces=3;查看设置reduce个数
set mapreduce.job.reduces;根据部门编号降序查看员工的信息
select * from emp sort by deptno desc;将查询结果导入到文件中（按照部门变化降序排序）
insert overwrite local directory '/opt/module/hive/datas/sortby-result' --overwrite不能随便使用，一不小心就会覆盖原来的目录，容易丢失数据 select * from emp sort by deptno desc;将查询结果导入到文件中（按照部门编号降序排序）
insert overwrite local directory --overwrite不能随便使用，一不小心就会覆盖原来的目录，容易丢失数据 '/opt/module/hive/datas/sortby-result' select * from emp sort by deptno desc;
Hive 内、外部表
理论
默认创建的表都是所谓的管理表，有时也称为内部表。因为这种表，hive会（或多或少地）控制着数据的生命周期。hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir（例如，/user/hive/warehouse）所定义的目录的子目录下。当我们删除一个管理表时，Hive也会删除这个表中的数据。管理表不适合和其他共享数据。
什么是 内部表
- 内部表也称为被Hive拥有和管理的托管表
- 默认情况下创建的表就是内部表，Hive拥有该表的结构和文件，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。
- 当您删除内部表时，它会删除数据以及表的元数据。
什么是外部表
- 外部表中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。
- 要创建一个外部表，需要使用external 语法关键字。
- 删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。
- 实际场景中，外部表搭配location语法指定数据的路径，可以让数据更安全。
内、外部表差异
- 无论内部表还是外部表，Hive都在Hive Metastore中管理表的定义，字段类型等元数据信息。
- 删除内部表时，除了会从Metastore中删除表元数据，还会从HDFS中删除所有数据文件。
- 删除外部表时，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。
内部表、托管表 外部表 创建方式 默认情况下 使用EXTERNAL语法关键字 Hive管理范围 元数据、表数据 元数据 删除表结果 删除元数据，删除HDFS上文件数据 只会删除元数据 操作 支持ARCHIVE,UNARCHIVE，TRUNCATE,MERGE,CONCATENATE 不支持 事务 支持ACID/事务性 不支持 缓存 支持结果缓存 不支持 如何选择内、外部表
- 当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。
- 当数据来之不易，防止误删，请使用外部表，因为即使删除表，文件会被保留。
Hive表对应的数据在HDFS上的存储的位置
Location关键字的作用
- 在创建外部表的时候，可以使用location指定存储位置路径，如果不指定会如何
- 如果不指定location，外部表的默认路径也位于/user/hive/warehouse,由默认参数控制。
- 创建内部表的时候，是否可以使用location指定？
- 内部表可以使用location指定位置的。
- 是否意味着Hive表的数据在HDFS的位置不是一定要在/user/hive/warehouse下？
- 不一定，Hive中表数据存储位置，不管内部表还是外部表，默认都是在/user/hive、warehouse。当然可以在建表的时候通过location关键字指定存储位置在HDFS的任意位置上
外部表的使用
--创建一个外部表 create external table student_ext(num int,name string,sex string,age int,dept string )row format delimited fields terminated by ',' location '/stu'; --指定表的存储路径查看表是属于外部表还是内部表
describe formatted 库名.表名; --查看表的类型（库名可以字节省略）
Hive Partitioned Tables 分区表
概念
- 当Hive表对应的数据量大，文件个数多时，为了避免查询时全表扫描数据，Hive支持根据指定的字段对表进行分区，分区的字段可以是日期，地域，种类等具有标识意义的字段。
- 比如把一整年的数据根据月份划分12个月（12个分区）,后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。
背景
对于分区表的数据导入加载，最基础的是通过load命令加载数据。
在load过程中，分区值是手动指定写死的，叫做静态分区。
create table student_HDFS_p(Sno int,sname string,sex string,sage int,sdept string )partitioned by(country string) row format delimited fields terminated by ',' -- 注意 分区字段country的值是在导入数据的时候手动指定的China load data inpath '/student.txt' into table student_HDFS_p partition(country = 'China');分区表数据加载–静态分区
所谓静态分区指的是分区的属性值由用户在加载数据的时候手动指定的。
语法如下：
load data [local] inpath 'filepath' into table tablename partition partition(分区字段='分区值...');
- Local参数用于指定待加载的数据是位于本地文件系统还是HDFS文件系统。关于load语句后续详细展开讲解。
分区案例
--创建分区表 --注意分区表语法规则 create table t_all_hero_part( id int, name string, hp_max int, mp_max int, attack_max int, defense_max int, attack_range string, role_main string, role_assist string )partitioned by(role string) --指定分区表的分区字段，这里不能于表中的字段重复 row format delimitedfields terminated by '\t';select * from t_all_hero_part; --将数据从本地加载到分区表中，并且设置每个表的分区字段的值，通过分区字段的值得不同进行分区 --静态加载分区表数据 load data local inpath '/root/a/archer.txt' into table t_all_hero_part partition(role='sheshou'); load data local inpath '/root/a/assassin.txt' into table t_all_hero_part partition(role="cike"); load data loca l inpath '/root/a/mage.txt' into table t_all_hero_part partition(role="fashi"); load data local inpath '/root/a/support.txt' into table t_all_hero_part partition(role="fuzhu"); load data local inpath '/root/a/tank.txt' into table t_all_hero_part partition(role="tanke"); load data local inpath '/root/a/warrior.txt' into table t_all_hero_part partition(role="zhanshi");本质
- 分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。
- 不同分区对应着不同的文件夹，同一分区的数据在同一个文件夹下。
- 查询过滤的时候只需要根据分区值找对应的文件，扫描文件夹下本分区下得文件即可，避免全表数据扫描。
- 这种指定分区查询的方式叫做分区裁剪。
多重分区表
- 通过建表语句中关于分区的相关语法可以发现，Hive支持多个分区字段：PARTITIONRF BY
- 多重分区下，分区之间是一种地精关系，可以理解为在前一个分区的基础上继续分区。
- 从HDFS的角度来看就是文件夹下继续划分子文件夹。比如：把全国人口数据首先根据省进行区分，然后根据市进行划分，如果你需要甚至可以继续根据区在划分，此时就是3分区表。
双重分区表的使用
-- 分区字段之间是一种递进关系 因此要注意分区字段的顺序 谁在前在后 create table t_user_province_city(id int,name string,age int) partitioned by (province string,city string); -- 这里的参数谁在前色的分区就在前面-- 将数据加载到分区表中 load data locat inpath '/root/hivedata/user.txt' into table t_user_province_city partition(province='zhejiang',city='ningbo'); ....-- 双分区表的使用 使用分区进行过滤 减少全表扫描 提高查询效率 select * from t_user_province_city where province='zhejiang' and city=、'hangzhou';分区表数据加载——动态分区
所谓动态分区指的是分区的字段是基于查询结果（参数位置）自动推断出来的。核心语法就是insert + select。
启用hive动态分区，需要在hive 回话中设置两个参数：
#是否开启动态分区功能 set hive.exec.dynamic.partition=true;#指定动态分区模式，分为nanstick非严格模式和strict严格模式。 #strict严格模式要求至少有一个分区为静态分区 set hive.exec.dynamic.partition.mode=nonstrict;动态分区的使用
--使用动态分区之前需要设置两个配置 set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict;--创建一张新的分区表 t_all_he：ro_part_dunamic create table t_all_hero_part_dynamic(id int,name string,hp_max int,mp_max int,defense_max int,attack_range string,role_main string,role_assist string )partitioned by(role string) row format delimited fields termminated by '\t';-- 执行动态分区插入(将另外一张表中查询出来的结果插入到指定的分区表中) insert into table t_all_hero_part_dynamic partition(role) -- 注意这里我们的分区值并没有手动写死指定 select tmp.*,tmp.role_mian from t_all_hero as tmp;-- 最后查询分区表，反向已经分好区了 select * from t_all_hero_part_dynamic;分区表的注意事项
一、分区表不是建表的必要语法规则，是一种优化手段，可选；
二、分区字段不能是表中已有的字段，不 能重复；
三、分区字段是虚拟字段，其数据并不存在底层的文件中；
四、分区字段的切丁来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）
五、Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度
Hive Bucketed Tables分桶表
概念
- 分桶表也叫做桶表，叫法源自建表语法中bucket单词，是一种用于优化查询而设计的表类型。
- 分桶表对应的数据文件在底层会被分解为若干部分，通俗老说就是被拆分称为若干个独立的小文件。
- 在分桶时，要指定根据那个字段将数据分为几桶（几部分）。
规则
- 分桶规则如下：桶编号相同的数据会被分到同一个桶当中。
Bucket number = hash_function(bucketing_column) mod num_buckets 分桶编号 =哈希方法（分桶字段） 取模 分桶个数hash_function取决于分桶字段bucketing_column的类型；
1、如果是int类型，hash_function(int) == int;
2、如果是其他比如bigint，string或者复杂数据类型，hash_function比较棘手，将是从该类型派生的某个数字，比如hashcode值。
分桶表的创建
- 根据seate州把数据分为5桶，建表语句如下：
create table itheima.t_usa_covid19_bucket(count_date string,county string,state string,fips int,cases int,deaths int )clustered by(state)into 5 buckets; -- 分桶的字段一定要是表中已经存在的字段在创建分桶表时，还可以指定分桶内的数据排序规则：
--根据state州分为5桶 每个桶内根据cases病例数倒序排序 create table itheima.t_usa_covid19_bucket_sort(count_date string,county string,state string,fips int,cases int,deaths int )clustered by(state) sorted by(cases desc) into 5 buckets; --指定每个内部根据cases倒序排序分桶表的数据加载
--step1:开启分桶的功能呢 从hive2.0开始不在需要设置 set hive.enforce.bucketing=true;--step2:把元数据加载到普通hive表中 drop table if exists t_usa_covid19; create table itheima.t_usa_covid19(count_date string,county string,state string,fips int,cases int,deaths int )row format delimitedfields terminated by ',';--将数据源上传到hdfs，t_usa_covid19表对应的路径下 -- hdfs dfs -put us-covid-counties.dat /user/hive/warehouse/itheima.db/t_usa_covid19;--step3:使用insert+select语法将数据加载到分桶表中， insert into t_usa_covid19_bucket select * from t_usa_covid19;使用三个好处
- 基于分桶字段查询时，减少全表扫描
--基于分桶字段state查询来自于New York州的数据 --不在需要进行全表扫描过滤 --根据分桶的规则hash_function(New York)mod5计算出分桶编号 --查询指定分桶里面的数据，就可以找出结果 此时是分桶扫描而不是全表扫描 select * from t_usa_covid19_bucket where state="New York";
- JOIN时可以提高MR程序效率， 减少迪卡尔成绩数量（前提是join的等值是分桶的字段）
a join b on a.id =b.id
- 分桶表数据进行高效抽样
- 当数据量特别大时，对全体数据进行处理存在困难时，抽样就是显得尤为重要了，抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验，质量检验，社会调查普遍采用的一种经济有效的工作和研究方法。
Hive Transactional Tables 事务表
局限性
虽然hive支持具有ACID语义的事务，但是在使用起来，并没有限MySQL中使用的那样方便，有很多局限性。原因很简单，毕竟hive的设计目标不是为了支持事务操作，而是支持分析操作，且最终基于hdfs的底层存储机制使得文件的增加删除修改操作需要懂一些小心思。
一、尚不支持begin，commit 和rollback.所有语言操作都是自动提交的。
二、仅支持orc文件格式（stored as orc）
三、默认情况下事务配置为关闭。需要配置参数开启使用。
四、表必须是分桶表，才可以使用事务功能
五、表参数transaction必须为true;
六、外部表不能称为acid表，不允许从非acid回话读取/写入acid表
实现原理
Hive的文件是存储在HDFS上的，而HDFS上面不支持对文件的任意修改，只能是采取另外的手段来完成。
用HDFS文件作为元数据（基础数据），用delta保存事务操作的记录增量数据；
正在执行中的事务，是以一个staging开头的文件夹维护的，执行结束就是delta文件夹，每次执行一次事务操作都会有这样的一个delta增量文件夹；
当访问Hive 数据时，根据HDFS原始文件和delta增量文件做合并，查询最新的数据。
Insert语句会直接创建delta目录；
DELETE目录的前缀是delete_delta;
UPDATE语句采用了split-updata特性，即先删除、后插入；
实现原理之delta文件夹命名格式
- delta_minWID_maxWID_stmtID，即delta前缀，写事务的ID范围，以及语句ID；删除时前缀是delete_delta，里面包含了要删除的文件；
- Hive会写事务（insert、delete 等）创建一个写事务ID（WriteID），该ID在表范围内唯一；
- 语句ID（Statement ID）则是当一个事务中有多条写入语句时使用的，用作唯一标识。
- 每个事务delta文件夹下，都有两个文件：
- _orc_acid_version的内容是2，即当前ACID版本号是2,。和版本1主要的区别是update语句采用了split-update特性，即先删除、后插入。这个文件不是orc文件，可以下载下来直接查看。
合并器
- 随着表的修改操作，创建了越来越多的delta增量文件，就需要合并以保持足够的性能。
- 合并器Compactor是一套在Hive Metastore内运行，支持ACID系统的后台进程。所有合并都是在后台完成的，不会阻止数据的并发读，写。合并后，系统将等待所有旧文件的读操作完成后，删除旧文件。
- 黑冰操作分为两种，miner compaction(小合并)、ma jor compaction(小合并)：
- 小合并会将一组delta增量文件重写为单个增量文件，默认触发条件为10个delta问阿金；
- 大合并将一个或多个增量文件和基础文件重写为新的基础文件，默认触发条件为delta文件相应基础文件占比，10%
局限性
虽然Hive支持了具有ACID语句的事务，但是在使用起来，并没有像在MySQL中使用那样方便，有很多限制；
- 尚不支持BEGIN,COMMIT 和 ROLLBACK，所有语言操作都是自动提交的；
- 表文件存储格式仅支持ORC(STORED AS ORC)
- 需要配置参数开启事务使用；
- 外部表无法创建为事务表，因为Hive只能控制元数据，无法管理数据；
- 表属性参数transactional必须设置为true;
- 必须将Hive事务管理器设置为org.apache.hadoop.hive.ql.lockmgr.DbTxnManager才能使用ACID表；
- 事务表不支持LOAD DATA …语句。
事务的使用
使用事务时需要先配置一下内容
--Hive中事务表的创建使用 --1、开启事务配置（可以使用set设置当前session生效 也可以配置在hive-siste.xml中） set hive.support.concurrency = true; --Hive是否支持并发 set hive.enforce.bucketing = true; --从Hive2.0开始不在需要 是否开启分桶功能 set hive.exec.dynamic.partition.mode = nonstrict;--动态分区模式 非严格 set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程 set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。创建Hive事务表
--2、创建hive事务表 create table trans_student(id int,name String,age int )clustered by(id) into 2 buckets stored as orc TBLPROPERTIES ("transactional"='true'); -- 注意事务表的创建的几个要素，开启参数，分桶表，存储格式为orc ，表属性 -- clustered by(id) into 2 buckets 表示创建的是一个分桶表，分为2桶 --stored as orc 表示创建的表存储格式为orc -- TBLPROPERTIES ("transactional"='true') 表示表的属性transactional 设置为true 表示事务的开启针对事务表进行insert update delete操作
insert into trans_student values(1,"allen",18); -- 插入数据update trans_student setage = 20 where id = 1; --更新delete from trans_student where id = 1; -- 删除
Hive Views视图
概念
- Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据。
- 通常从真实的物理表中创建生成视图，也可以从已经存在的视图上创建新视图。
- 创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败。
- 视图时用来简化操作的，不缓冲记录，也没有提供查询性能。
基础语法
创建视图
create view v_usa_covid19 as select count_date,county,state,deaths from t_usa_covid19 limit 5;查看视图
show views;查看视图的定义（就是查询创建该视图的hql语句）
show create table v_usa_covid19;
使用视图的好处
- 将真实表中特定的列数据提供给用户，保护数据隐私
- 降低查询的复杂度，优化查询语句。
Hive3.0新特性：Materialized Views 物化视图
简要说明
物化视图的功能就是把需要物化出来的查询结果存储起来，等到下次有相同的查询要求，会直接返回此结果，这样可以减少查询的时间，大大增加查询的效率。
- 物化视图是一个包括查询结果的数据库对象，可以用于预先计算并保存表连接或聚集等耗时较多的操作结果。在执行结果查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。
- 使用物化视图的目的就是通过预计算，提高奥查询性能，当然西药占用一定的存储空间。
概念
- Hive3.0开始尝试引入物化视图，并提供对于物化视图的查询自动重写机制（基于Apache Calcite实现）。
- Hive的物化视图还提供了物化视图存储机制，可以本地存储在Hive，也可以通过用户自定义storage handlers 存储在其他系统 （如druid）.
- Hive引入物化视图的目的就是为了优化数据查询访问的效率，相当于预处理的角度优化数据访问。
- Hive从3.0丢弃了index索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度。
物化视图、视图的区别
- 视图时虚拟的，逻辑存在的，只有定义没有存储数据。
- 物化视图时真实的，物理存在的，里面存储着预计算的数据。
- 物化视图能够缓存数据，在创建物化视图的时候就把数据缓存起来了，Hive把物化视图当成一张表，将数据缓存。而视图只是创建一个虚拟表，只有表结构，没有数据，实际查询的时候在去改写SQL去访问实际的数据表。
- 视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能。
基于物化视图的查询重写
物化视图创建后即可用相关查询的加速，即：用户提交查询query，若该query经过重写后可以命中已经存在的物化视图，则直接通过物化视图查询数据返回结果，以实现查询加速
是否重写查询使用物化视图查询重写机制，默认为true：hive.materializedview.rewriting=true;
用户可以选择性的控制指定的物化视图查询重写机制，语法如下:
alter materialized view [db_name.]materialized_view_name enable|disable rewrite;物化视图的使用
--使用物化视图之前要先开启一下功能 --1、开启事务配置（可以使用set设置当前session生效 也可以配置在hive-siste.xml中） set hive.support.concurrency = true; --Hive是否支持并发 set hive.enforce.bucketing = true; --从Hive2.0开始不在需要 是否开启分桶功能 set hive.exec.dynamic.partition.mode = nonstrict;--动态分区模式 非严格 set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程 set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。--创建一个事务表 create table student_trans(sno int,sname string,sdept string )clustered by (sno) into 2 buckets stored as orc tblproperties('transactional'='true');-- 创建一个物化视图 --3、对student——trans建立聚合物化视图，这里把执行完的语句存储起来，下一就不用查询了直接使用这里的查询结果就可以了 create materialized view student_trans_agg as select sdept,count(*) as sdept_cnt from student_trans group by sdept;可以通过使用下面语句进行查看物化视图是否创建好了
show tables; show materialized views;由于会命中物化视图，重写query查询物化视图，查询速度会加快没有启动mr,只是普通的table scan）
--4对原始表student_trans查询 --由于会命中物化视图，重写query查询物化视图，查询速度会加快（没有启动mr,只是普通的table scan） select sdept,count(*) as sdept_cnt from student_trans group by sdept;禁用物化视图自动重写
alter materialized view student_trans_agg disable rewrite;删除物化视图
drop materialized view student_trans_agg;
Hive partition(分区)DDL操作
add partition 增加分区
- add partition会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询时将不会返回结果。
- 因此需要保证增加的分区位置路径下，数据已经存在，或者增加完分区之后导入分区数据。5
--1、增加分区 alter table table_name add partition(dt='20170101')location '/user/hadoop/warehouse/table_name/dt=20170101'; --一次添加一个分区alter table table_name add pattition(dt='2008-08-08',country='us')location '/path/to/us/part080808' partition(dt='2008-08-09',country='us')location'/path/to/us/part080809'; --一次添加多个分区rename partition 重命名分区
--2、重命名分区 alter table table_name partition partition_spec rename to Partition partition_spec;delete partition 删除分区
--3、删除分区 alter table table_name drop if exists partition (dt='2008-08-08',country='us') alter table table_name drop if exists partition (dt='2008-08-08',country='us')purge --直接删除数据不进垃圾桶msck parririon 背景
hive将每个表的分区列信息存储在其metastore中，但是，如果将新分区直接添加到hdfs（例如通过使用hadoop fs -put命令）或从hdfs中直接删除分区文件夹，则除非用户alter table table_name add/drop partition在每个新添加的分区上运行命令，否则metastore（也就是hive）将不会意识到分区信息的这些更改。
MSCK是metastore check的缩写，表示元数据检查操作，可用于元数据的修复。
--4.修复分区 msck [pepair] table table_name [add/drop/sync partitions];show 语法
整体概述
- show 相关的语句提供了一种查询hvie metastore的方法，可以帮助用户查询相关信息。
- 比如我们最常使用的查询当前数据库下有哪些表 show tables.
--1、显示所有数据库 schemas和databases 的用法，功能一样。 show databases; show schemas;--2、显示当前数据库所有表/视图/物化视图/分区/索引 show tables; show tables [in database_name]; -- 指定某个数据库--3、显示当前数据库下所有视图 show views; shwo views 'test_*'; show views from test1; --show views from database test1 shwo views [IN/from database_name];--4、显示当前数据库下所有物化视图 show materialized views [in/from database_name];-- 5、显示表的分区信息，分区按字母顺序列出，不是分区表执行该语句会报错。 show partitions table_name; show partitions itheima.student_partition;--6、显示表/分区的扩展信息 show table extended [in | from database_name] like table_name; show table extended like student; describe formatted itheima.student;--7、显示表的属性信息 show tblproperties table_name; show tblproperties student;--8、显示表、视图的创建语句 show create table([db_name]table_name|view_name) show create table student;--9、显示表中的所有列，包括分区列。 show colummns (from|in) table_name [(from|in) db_name]; shwo columns in student;--10、显示当前支持的所有自定义和内置的函数 show functions;--11、describe desc --查看表信息 --desc extended table_name; --查看表信息（格式化美观） desc fromatted table_name; --查看数据库相关信息 describe database database_name;
Load 功能与语法规则
Local本地在哪里
本地文件系统指定是Hiveserver2服务所在的机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。
load 从本地的文件加载是复制文件操作
load 从hdfs上加载是执行移动文件操作
语法
load data local inpath '/root/file/students.txt' into table student.txtLoad Hive 3.0的新特性
- hive3.0+，load加载数据时除了移动、复制操作之外，在某些场合下还会将加载重写为insert as select .
- Hive3.0+,还支持使用inputformat、serDe指定输入格式，例如Text,ORC等
- 比如，如果表具有分区，则load命令没有指定分区，则将load转换为Insert as select，并假定最后一组列为分区列，如果文件不符合预期，则报错。
Insert + select 使用方式
- insert+select 表示：将后面查询返回的结果作为内容插入到指定表中，注意overwrite将覆盖已有数据。
- 需要保证查询结果列的数目和需要插入数据表格的列数目一致。
- 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL
insert into table t_student select * from t_user;multiple inserts多重插入
翻译为多次插入，多重插入，其核心功能是:一次扫描，多次插入。
语法目的就是减少扫描的次数，在一次扫描中，完成多次insert操作。
--创建两个空表 create table t_student4(num int );create table t_student5(name string ); --使用多重插入模式给两张表分别插入对应的数据 from t_student insert into t_student5 select name insert into t_student4 select num;
Hive SQL select查询高阶语法
cluster by
根据指定字段将数据分组，每组内在根据字段正序排序（只能正序）。
概况起来就是：根据同一个字段，分且排序。
分组规则hash散列（分桶表规则一样）：Hash_Func（col_name） % reducetask个数
分为几组取决于reducetask的个数
--根据在学生表中，根据性别进行分组，在进行排序 select * from student cluster by sex ;distribute by + sort by
- distribute by + sort by 就相当于吧cluster by的功能一分为二：
- distribute by 负责根据指定字段分组;
- sort by负责分组内排序规则。
- 分组和排序的字段可以不同。
--案例：把学生表数据根据性别分为两部分，每个组内根据年龄的倒序排序。 select * from student distribute by sex sort by age desc;
- 如果distribute by + sort by 的字段一样，则：cluster by =distribute by + sort by
--下面两个语句执行结果一样 select * from student distribute by num sort by num; select * from student cluster by num;cluster、distribute、sort、order by
- order by 全局排序，因此只有一个reducer,结果输出在一个文件中，当输入规模大时，需要较长的计算时间。
- distribute by 根据指定字段将数据分组，算法是hash散列。sort by 是在分组之后，每个组内局部排序。
- cluster by 即有分组，又有排序，但是两个字段只能是同一个字段。
如果distribute 和 sort 的字段是同一个时，此时，cluster by = distribute by + sort by
Union联合查询
union用于将多个select 语句的结果合并为一个结果集。
- 使用distinct关键字与使用union默认值效果一样，都会删除重复行。1.2.0之前的hive版本仅支持union all ，在这种情况下不会消除重复的行。
- 使用all关键字，不会删除重复行，结果集包括所有select语句的匹配行（包括重复行）
- 每个select_statement返回的列的数量和名称必须相同。
--默认写法union会删除重复行 select num,name from student1union select num,name from student2--带参数distinct也会删除重复行 select num,name from student1union distinct select num,name from student2--如果使用union不想删除重复行的话可以加上all关键字 select num,name from student1union all select num,name from student2--如果要讲order by，sort by,cluster by,distribute by或limit引用于单个select --请将子句放在括住select的括号内，并给查询出来的表的结果起一个别名 select num,name from (select num,name from t_student2 limit 2) subq1 union select num,name from (select num,name from t_student3 limit 3)subq2;--如果要将order by,sort by,cluster by,distribute by 或limit子句应用于整个union结果 --请将order by,sort by, cluster by,distribute by 或limit放在最后一个之后。 select num,name from t_student2 union select num,name from t_student3 order by num desc;
from子句中子查询
在hive0.12版本，仅在from子句支持子查询。
必须要给子查询一个名称，因为from子句中的每个表都必须有一个名称。子句查询返回结果中的列必须具有唯一性的名称。子查询返回结果中的列在外部查询中可用，就像真实表的列一样。子查询也可以是带有union的查询表达式。
Hive支持任意级别的子查询，也就是所谓的嵌套子查询。
Hive 0.13.0和更高版本中的子查询名称之前可以包含可选关键字AS.
--from之句中子查询 --子查询 select num from(select num,namefrom student_local )tmp;--包含union all的子查询的实例 select t3.name from(select num,name from student_localunion distinctselect num,name from student_hdfs )t3; -- 这里from的子查询的表一定要起别名，否则会报错
where之句中子查询
从Hive 0.13开始，where子句支持下述类型的子查询：
- 不相关子查询：该子查询不引用父查询的列，可以将查询结果视为in和not in语句的常量；
- 相关子查询：子查询引用父查询中的列；
--where 之句中子查询 --不相关子查询，相当于IN,NOT IN,子查询只能选择一列。 --（1）执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。 --（2）执行外部查询，并显示整个结果 select * from student_hdfs where student_hdfs.num IN(select num from student_local limit 2);--相关子查询，指EXISTS和NOT EXISTS子查询 --子查询的where子句中支持对父查询的引用 select A from t1 where exists(select b from t2 where t1.x = t2.y);
Common Table Expressions (CTE)
CTE介绍
- 公用表达式（CTE）是一个临时结果集：该结果集是从with子句中指定的简单查询派生而来的，紧接在select或insert关键字之前。
- CTE仅在单个语句的执行范围内定义。
- CTE可以在select，insert，create table as select 或 create view as select 语句中使用
--临时结果集 --将查询的结果存储到一个q1的临时结果当中，需要子查询的时候可以直接使用q1进行查询。 with q1 as (select num,name,age from student where num = 95002) select * from q1;--from 风格 with q1 as (select num,name,age from student where num = 95002) from q1 select * ;--chaining CTEs链式 with q1 as (select * from student where num = 95002),q2 as (select num,name,age from q1) select * from (select num from q2) a;--union with q1 as (select * from student where num = 95002),q2 as (select * from student where num = 95004) select * from q1 union all select * from q2;
join 连接操作
inner join 内连接（掌握）
- 内连接是最常见的一种连接，它也被称为普通连接，其中inner可以省略：inner join ==join;
- 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。
select e.deptno,e.ename,e.job fromemp e joindept d on e.deptno = d.deptno;
left join左连接（掌握）
- left join中文叫做是左外连接或者左连接，其中outer可以省略，left outer join是早期的写法。
- left join 的核心就在于left左。左指的是join关键字左边的表，简称左表。
- 通俗解释：join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。
select e.deptno,e.ename,e.job fromemp e left joindept d one.deptno = d.deptno;
right join 右连接
- right join中文叫做是右外连接或者右连接，其中outer可以省略。
- right join的核心就在于Right右，右指的是join关键字右边的表，简称右表，
- 通俗解释：join时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联不上的显示null返回。
- 很明星啊，right join和left join之间很相似，重点在于以那边为准，也就是一个反向的问题。
full outer join 全外连接
- fill outer join等价 full join ,中文叫做全外连接或者外连接。
- 包括左、右两个表的全部行，不管另外一边的表中是否存在他们匹配的行；
- 在功能上：等价与对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的操作间上述两个结果集合合并为一个结果集。
- 简单来讲就是查询了两张表，但是需要吧俩个表之间重复的消除掉。
left semi join左半开连接
- 左半开连接（left semi join）会返回左边表的记录，前提是其记录对于右边的表满足on语句中的判断条件。
- 从效果上来看有点像inner join之后只返回左表的结果。
select * from employee e left semi join employee_address e_addr on e.id = e_addr.id;
cross join 交叉连接
- 交叉连接 cross join，将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘机。对于大表来说，cross join慎用
- 在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积，无需指定关联键。
- 在HiveSQL语法中，cross join 后面可以跟where子句进行过滤，或者on条件过滤。
join注意事项
- 允许使用复杂的连接表达式，支持非等值连接
select a.* from a join b on(a.id = b.id) select a.* from a join b on(a.id = b.id and a.department= b.department) select a.* from a left outer join b on (a.id <> b.id);
- 同一查询中可以连接2个以上的表
select a.val,b.val from a join b on (a.key = b.key1)join c on (c.key = b.key2)
如果每个表在连接子句中使用相同的列，则hive将多个表上的连接转换为单个MR作业
join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据说需要的内存
hive客户端与属性配置
Hive CLI
Hive第一代客户端bin/hive
$HIVE_HOME/bin/hive 是一个shell util ,通常称之为Hive的第一代客户端或者旧客户端，主要功能有两个：
交互式或批处理模式运行Hive查询，注意，此时作为客户端，需要并且能够访问的是hive metastore服务，而不是hiveserver2 服务。
hive相关服务的启动，比如metastore 服务。
可以通过运行“hive -H” 或者 “hive --help” 来查看命令行选项。
-e <quoted-query-string> --执行命令行-e参数后指定的sql语句 运行完退出 -f <filename> --执行命令行-f参数后指定的sql文件 运行完退出 -H,-help --打印帮助信息 --hiveconf<property=value> --设置参数 -S,--silent --静默模式 -v, -verbose --详细模式，将执行sql回显到console -service service_name --启动hive的相关服务Hive第二代客户端bin/beeline
- $HIME_HOME/bin/beeline被称之为第二代客户端或者新客户端，是一个JDBC客户端，是官方强烈推荐使用的hive命令行工具。和第一代客户端相比，性能加强安全性提高。Beeline在嵌入式模式和远程模式下均可工作。
- 在嵌入式模式下，它运行嵌入式HIve（类似于hive CLI）
- 远程模式下beeline通过thrift连接到单独的hiveserver2服务上，这也是官方推荐在生产环境中使用的模式。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-suZ4BELm-1666068736593)(C:\Users\26965\AppData\Roaming\Typora\typora-user-images\image-20221007214207475.png)]
总结
配置方式优先级
set设置 > hiveconf 参数 >hive-site.sml配置文件
set 参数声明会覆盖命令行参数hiveconf，命令行参数会覆盖配置文件hive-site.xml设定
日常开发使用中，如果不是核心的需要全局修改的参数属性，建议使用set命令进行设置
另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置
配置文件使用的官网
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
运算符
概述
整体上，Hive支持的运算符可以分为三大类：算数运算符、关系运算符、逻辑运算符
官方参考文档
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
- 也可以使用下述方式查看运算符的使用方式
显示所有的运算符和函数
show functions;查看运算符或者函数的使用说明
describe function +;关系运算符
空值判断 is null 非控制判断 is not null like 比较 like java的like 操作 rlike --1、创建表dual create table dual(id string); --2、加载一个文件dual.txt到dual表中 --dual.txt只有一行内容：内容为一个空格 load data local inpath '/root/file/dual.txt' into table dual; --3、在select查询语句中使用dual表完成运算符、函数功能测试 select 1+1 from dual;select 1+1;-- 新版本的hive是可以省略到from的-- 否定比较：not a like b select 1 from dual where 'itcast' like 'it_'; select 1 from dual where 'itcast' like 'it%'; select 1 from dual where 'itcast' not like 'hadoo_'; select 1 from dual where not 'itcast' like 'hadoo_';--rlike:确定字符串是否匹配正则表达式，是REGEXP_LIKE()的同义词。 select 1 from dual where 'itcast' rlike '^i.*t$'; --判断是否是以t结尾 select 1 from dual where '123456' rlike '^\\d+$'; --判断是否全为数字 select 1 from dual where '123456aa' rlike '^\\d+$';--regexp :功能与rlike相同用于判断字符串是否是否匹配正则表达式 select 1 from dual where 'itcast' regexp '^i.*t$';算数运算符
- 算数运算符操作数必须四数值类型。分为一元运算符和二元运算符：
- 一元运算符吗，只有一个操作数；二元运算符有两个操作数，运算符在两个操作数之间。
取证操作 div 取余操作 % 位与操作 & 位或操作 | 位异或操作 ^ --去整操作：div 给出将A除以B所得的整数部分。例如17 div 3得出5. select 17 div 3;--取余操作：%也叫做取模mod A除以B所得的余数部分 select 17 % 3;--位与操作：& A和B按位进行与操作的结果。与表示两个个都为1则结果为1 select 4 & 8 from dual; --4转换二进制：0100 8转换二进制：1000 select 6 & 4 from dual; --4转换二进制：0100 6转换二进制：0110--位或操作： | A和B按位进行或操作的结果 或表示有一个为1则结果为1 select 4 | 8 from dual; --4转换二进制：0100 8转换二进制：1000 select 6 | 4 from dual; --4转换二进制：0100 6转换二进制：0110--位或操作：^ A和B按位进行异或操作的结果 异或表示两者的值不同，则为1 select 4 ^ 8 from dual; --4转换二进制：0100 8转换二进制：1000 select 6 ^ 4 from dual; --4转换二进制：0100 6转换二进制：0110逻辑运算符
与操作 A and b 或操作 A or B 非操作 not A != A 在 A in(val1,val2,…) 不在 not in(val1,val2,…) 逻辑是否存在 [not] exiss (subquery) 案例
--3、Hive逻辑运算符 --与操作：A and B 如果A和B均为TRUE,则为TRUE,否则为FALSE.如果A或B为NULL,则为NULL. select 1 from dual where 3>1 and 2>1;--或操作：A or B 如果A或B两者均为TRUE,则为TRUE,否则为FALSE. select 1 from dual where 3>1 or 2!=2;--非操作：not a ！= A 如果a为false，则为true；如果a为Null,则为Null.否则为false. select 1 from dual where not 2 > 1; select 1 from dual where !2=1;--在：A in(val1,val2,...) 如果A任何值，则为True select 1 from dual where 11 not in(22,33,44);--逻辑是否存在：[NOT] EXISTS （subquery） --将主查询的数据，放到子查询中左条件验证，根据验证结果（true 或false）来决定主查询的数据结果是否得以保留。 select A.* from abs() where exists (select B.id from B where A.id =B.id);
Hive Functions函数入门
分类标准
Hive的函数分为两大累：内置函数、用户自定义函数：
- 内置函数可分为：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；
- 用户定义函数根据输入输出的行数可分为3类：UDF、UDAF、UDTF
- 根据函数输入输出的行数：
- UDF普通行数，一进一出
- UDAF聚合函数，多进一出
- UDTF表生成函数，一进多出
--查看当下可用的所有函数； show functions; --查看函数的使用方式。 describe function extended funcname;
Hive 内置函数
- 内置函数指的是Hive开发实现好，直接可以使用的函数，也叫做内建函数。
- 官方文档地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
- 内置函数根据应用归类整体可以分为8大种类型，我们将其中重要的，使用频率高的函数使用进行详细讲解。
字符串函数
（非常重要的函数）
函数作用 函数名称 字符串连接函数 concat 带风格符字符串连接函数 concat_ws 字符串截取函数 substr,substring 正则表达式替换函数 regexp _replace URL解析函数 parse url json 解析函数 get json object f分割字符串函数 split (比较常用的函数)
函数作用 函数名称 字符串长度函数 length 字符串反转函数 reverse 字符串转大写函数 upper,ucase 字符串转小写函数 lower,lcase 去空格函数 trim 左边去空格函数 ltrim 右边去空格函数 rtrim 正则表达式解析函数 regexp extract 空格字符串函数 space 重复字符串函数 repeat 首字母ascii函数 ascii 左补足函数 lpad 右补足函数 rpad 集合查找函数 find in set 字符串函数案例
------------String Functions 字符串函数------------ select concat("angela","baby"); --带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+) select concat_ws('.', 'www', array('itcast', 'cn')); --字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len]) select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数 select substr("angelababy",2,2); --正则表达式替换函数：regexp_replace(str, regexp, rep) select regexp_replace('100-200', '(\\d+)', 'num'); --正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容 select regexp_extract('100-200', '(\\d+)-(\\d+)', 2); --URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数 select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST'); --分割字符串函数: split(str, regex) select split('apache hive', '\\s+'); --json解析函数：get_json_object(json_txt, path) --$表示json对象 select get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"}, {"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '$.[1].website');--字符串长度函数：length(str | binary) select length("angelababy"); --字符串反转函数：reverse select reverse("angelababy"); --字符串连接函数：concat(str1, str2, ... strN) --字符串转大写函数：upper,ucase select upper("angelababy"); select ucase("angelababy"); --字符串转小写函数：lower,lcase select lower("ANGELABABY"); select lcase("ANGELABABY"); --去空格函数：trim 去除左右两边的空格 select trim(" angelababy "); --左边去空格函数：ltrim select ltrim(" angelababy "); --右边去空格函数：rtrim select rtrim(" angelababy "); --空格字符串函数：space(n) 返回指定个数空格 select space(4); --重复字符串函数：repeat(str, n) 重复str字符串n次 select repeat("angela",2); --首字符ascii函数：ascii select ascii("angela"); --a对应ASCII 97 --左补足函数：lpad select lpad('hi', 5, '??'); --???hi select lpad('hi', 1, '??'); --h --右补足函数：rpad select rpad('hi', 5, '??'); --集合查找函数: find_in_set(str,str_array) select find_in_set('a','abc,b,ab,c,def');日期函数案例
--获取当前日期: current_date select current_date(); --获取当前时间戳: current_timestamp --同一查询中对current_timestamp的所有调用均返回相同的值。 select current_timestamp(); --获取当前UNIX时间戳函数: unix_timestamp select unix_timestamp(); --日期转UNIX时间戳函数: unix_timestamp select unix_timestamp("2011-12-07 13:01:03"); --指定格式日期转UNIX时间戳函数: unix_timestamp select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss'); --UNIX时间戳转日期函数: from_unixtime select from_unixtime(1618238391); select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); --日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd' select datediff('2012-12-08','2012-05-09'); --日期增加函数: date_add select date_add('2012-02-28',10); --日期减少函数: date_sub select date_sub('2012-01-1',10);--抽取日期函数: to_date select to_date('2009-07-30 04:17:52'); --日期转年函数: year select year('2009-07-30 04:17:52'); --日期转月函数: month select month('2009-07-30 04:17:52'); --日期转天函数: day select day('2009-07-30 04:17:52'); --日期转小时函数: hour select hour('2009-07-30 04:17:52'); --日期转分钟函数: minute select minute('2009-07-30 04:17:52'); --日期转秒函数: second select second('2009-07-30 04:17:52'); --日期转周函数: weekofyear 返回指定日期所示年份第几周 select weekofyear('2009-07-30 04:17:52');数学函数
----Mathematical Functions 数学函数------------- --取整函数: round 返回double类型的整数值部分 （遵循四舍五入） select round(3.1415926); --指定精度取整函数: round(double a, int d) 返回指定精度d的double类型 select round(3.1415926,4); --向下取整函数: floor select floor(3.1415926); select floor(-3.1415926); --向上取整函数: ceil select ceil(3.1415926); select ceil(-3.1415926); --取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数 select rand(); --指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列 select rand(3);--二进制函数: bin(BIGINT a) select bin(18); --进制转换函数: conv(BIGINT num, int from_base, int to_base) select conv(17,10,16); --绝对值函数: abs select abs(-3.9);集合函数
-------Collection Functions 集合函数-------------- --集合元素size函数: size(Map<K.V>) size(Array<T>) select size(`array`(11,22,33)); select size(`map`("id",10086,"name","zhangsan","age",18));--取map集合keys函数: map_keys(Map<K.V>) select map_keys(`map`("id",10086,"name","zhangsan","age",18));--取map集合values函数: map_values(Map<K.V>) select map_values(`map`("id",10086,"name","zhangsan","age",18));--判断数组是否包含指定元素: array_contains(Array<T>, value) select array_contains(`array`(11,22,33),11); select array_contains(`array`(11,22,33),66);--数组排序函数:sort_array(Array<T>) select sort_array(`array`(12,2,32));条件函数
-----Conditional Functions 条件函数------------------ --使用之前课程创建好的student表数据 select * from student limit 3;describe function extended isnull;--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull) select if(1=2,100,200); select if(sex ='男','M','W') from student limit 3;--空判断函数: isnull( a ) select isnull("allen"); select isnull(null);--非空判断函数: isnotnull ( a ) select isnotnull("allen"); select isnotnull(null);--空值转换函数: nvl(T value, T default_value) select nvl("allen","itcast"); select nvl(null,"itcast");--非空查找函数: COALESCE(T v1, T v2, ...) --返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL select COALESCE(null,11,22,33); select COALESCE(null,null,null,33); select COALESCE(null,null,null);--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; select case sex when '男' then 'male' else 'female' end from student limit 3;--nullif( a, b ): -- 如果a = b，则返回NULL，否则返回一个 select nullif(11,11); select nullif(11,12);--assert_true(condition) --如果'condition'不为真，则引发异常，否则返回null SELECT assert_true(11 >= 0); SELECT assert_true(-1 >= 0);类型函数
----Type Conversion Functions 类型转换函数----------------- --任意数据类型之间转换:cast select cast(12.14 as bigint); select cast(12.14 as string); select cast("hello" as int);数据脱敏函数
----Data Masking Functions 数据脱敏函数------------ --mask --将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。 select mask("abc123DEF"); select mask("abc123DEF",'-','.','^'); --自定义替换的字母--mask_first_n(string str[, int n] --对前n个进行脱敏替换 select mask_first_n("abc123DEF",4);--mask_last_n(string str[, int n]) select mask_last_n("abc123DEF",4);--mask_show_first_n(string str[, int n]) --除了前n个字符，其余进行掩码处理 select mask_show_first_n("abc123DEF",4);--mask_show_last_n(string str[, int n]) select mask_show_last_n("abc123DEF",4);--mask_hash(string|char|varchar str) --返回字符串的hash编码。 select mask_hash("abc123DEF");其他杂项函数
----- Misc. Functions 其他杂项函数-----------------如果你要调用的java方法所在的jar包不是hive自带的 可以使用add jar添加进来 --hive调用java方法: java_method(class, method[, arg1[, arg2..]]) select java_method("java.lang.Math","max",11,22);--反射函数: reflect(class, method[, arg1[, arg2..]]) select reflect("java.lang.Math","max",11,22);--取哈希值函数:hash select hash("allen");--current_user()、logged_in_user()、current_database()、version()--SHA-1加密: sha1(string/binary) select sha1("allen");--SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512) select sha2("allen",224); select sha2("allen",512);--crc32加密: select crc32("allen");--MD5加密: md5(string/binary) select md5("allen");
用户自定义函数
- 用户自定义函数简称UDF，源自英文user-defined function.
- 根据函数输入输出的行数可以分为3类，分别是：
- UDF普通函数，一进一出
- UDAF聚合函数，多进一出
- UDTF表生成函数，一进多出
UDF普通函数
- UDF普通函数
- 比如round这样的取整函数，接收一行数据，输出的还是一行数据。
UDAF聚合函数
UDAF聚合函数，A所代表的单词就是Aggregation聚合的意思
多进一出，也就是输入多行输出一行。
比如count，sum这样的函数 。
count :统计检索到的总行数
sum :求和
avg :求平均值
min :最小值
max ：最大值
数据收集函数（去重）:collect_set(col)
数据收集函数（不去重）：collect_list(col)##### UDTF表生成函数- UDTF 表生成函数，T所代表的单词是Table-Generating表生成的意思 - 特点是一进多出，也就是输入一行输出多行。 - 这类型的函数作用返回的结果类似于表，同时，UDTF函数也是我们接触比较少得函数。 - 比如explode函数。##### 案例：开发Hive UDF实现手机号****加密在企业中处理数据的时候，对于敏感数据往往需要进行脱敏处理，比如手机号。我们常见的处理方式是将手机号中间4位进行****处理。Hive中没有这样的函数可以直接实现功能，虽然可以通过各种函数嵌套调用最终也能实现，但是效率不高，现要求自定义开发实现Hive函数，满足上述需求。1、能够对输入数据进行非空判断，手机号位数判断2、能够实现校验手机号格式，把满足规则的进行****处理3、对于不符合手机号规则的数据直接返回，不处理。UDF实训啊步骤- 写一个java类，基层UDF，并重载eveluate方法，方法中实现函数的业务逻辑； - 重载意味着可以在一个java类中实现多个函数功能； - 程序达成jar包，上传HS2服务器本地或者HDFS; - 客户端命令行中添加jar包到Hive的classpath: hive>add jar /xxxx/udf.jar; - 注册成为临时函数（给UDF命名）：create temporary function 函数名 as 'UDF类全路径'； - HQL中使用函数##### 开发环境准备- IDEA中创建Maven工程，添加下述pom依赖，用于开发Hive UDF;- 完整pom.xml请参考课程附件资料```xml<!-- 这里用到的包的版本必须和环境保持一致-> <dependencies><dependency><groupId>org.apache.hive</groupId><artifactId>hive-exec</artifactId> <varsion>3.1.2</varsion> </dependency> <dependency><groupId>org.apache.hadoop</groupId> <artifactId>hadoop-common</artifactId> <version>2.7.2</version> </dependency> </dependencies>java代码
```java
package cn.itcast.hive.udf;import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;import java.util.regex.Matcher;
import java.util.regex.Pattern;/**
- @description: hive自定义函数UDF 实现对手机号中间4位进行****加密
- @author: Itcast
/
public class EncryptPhoneNumber extends UDF {
/*- 重载evaluate方法 实现函数的业务逻辑
- @param phoNum 入参：未加密手机号
- @return 返回：加密后的手机号字符串
/
public String evaluate(String phoNum){
String encryptPhoNum = null;
//手机号不为空 并且为11位
if (StringUtils.isNotEmpty(phoNum) && phoNum.trim().length() == 11 ) {
//判断数据是否满足中国大陆手机号码规范
String regex = “^(1[3-9]\d{9}$)”;
Pattern p = Pattern.compile(regex);
Matcher m = p.matcher(phoNum);
if (m.matches()) {//进入这里都是符合手机号规则的
//使用正则替换 返回加密后数据
encryptPhoNum = phoNum.trim().replaceAll(“(\d{3})\d{4}(\d{4})”,"$1***$2");
}else{
//不符合手机号规则 数据直接原封不动返回
encryptPhoNum = phoNum;
}
}else{
//不符合11位 数据直接原封不动返回
encryptPhoNum = phoNum;
}
return encryptPhoNum;
}
}##### 把打包好的jar包上传到linux服务器 ，放在/root目录下，接着使用beeline客户端使用下面指令进行添加classpath中。```sql add jar /root/hive-udf-1.0-SNAPAHOT.jar --将jar包添加到classpath中注册临时函数
通俗来说就是给老用户的函数起个名字
```sql
create temporary function 函数名 as ‘UDF类全路径’;
UDTF之explode函数
功能介绍
- explode接收map、array类型的数据作为输入，然后把输入数据中的每个元素拆开变成一行数据，一个元素一行。
- explode执行效果正好满足于输入一行输出多行，所有叫做UDTF函数。
--该函数他会把集合或数组中的每个元素以表的形式显示出来 select explode(`array`(11,22,33,44,55)); select explode(`map`('id',10086),'name','allen','age',18)UDTF语法限制
- explode函数属于UDTF表生成函数，explode执行返回的结果可以理解位一张虚拟的表，其数据来源于源表；
- 在select中查询源表数据没有问题，只查询explode生成的虚拟表数据也没问题，但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段；通俗点讲，有两张表，不能只查询一张表但是又想返回分别属于两张表的字段。
执行错误
- 在select条件中，如果有explode函数表达式，程序执行是没有任何问题的；
- 但是如果在select条件中，包含explode和其他字段，就会报错
- 如何理解这个错误？为什么在select的时候，explode的旁边不支持其他字段同时出现？
UDTF语法限制解决
从SQL层面上来说上述问题的解决方案是：对两张表进行join关联查询；
Hive专门提供了语法lateral View 侧视图,专门用于搭配explode这样的UDTF函数，以满足上述需要。
--step5:lateral view +explode select a.team_name,b.year from the_nba_championship a lateral view explode(champion_year) b as year order by b.year desc;--根据年份倒序排序 select a.team_name ,b.year from the_nba_championship a lateral view explode(champion_year) b as year order by b.year desc;-- 统计每个球队获取总冠军的次数 并且根据倒叙排序 select a.team_name,count(*) as nums from the_nba_championship a lateral view explode(champion_year) b as year group by a.team_name order by nums desc;
Hive Lateral View 侧视图
概念
- Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。
- 一般只要使用UDTF，就会固定搭配lateral view使用。
原理
- 将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新表。这样就避免了UDTF的使用限制问题
- 使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by、limit等语句中，不需要在单独嵌套一层子查询。
Hive Aggregation 聚合函数
概述
- 聚合函数的功能是：度一组值执行计算并返回单一的值。
- 聚合函数是典型的输入多行输出一行，使用hive的分类标准，属于UDAF类型函数。
- 通常搭配Group By语法一起使用，分组或进行聚合操作。
聚合函数使用案例
-- 场景1：没有group by子句的聚合操作--count（*）：所有行进行统计，包括Null行--count(1):所有行进行统计，包括Null行--count(column):对column中非Null进行统计 select count(*) as cnt1,count(1) as cnt2 from student; select count(sex) from student;--场景2：带有group by子句的聚合操作 注意group by语法限制 select sex,count(*) as cont1,avg(age) as cont2 from student group by sex;--场景3:select时多个聚合函数一起使用 select count(*) as cot1,avg(age) as cot2 from student;--场景4：聚合函数和case where 条件转换函数，coalesce函数，if函数使用 select sum(case when sex ='男' then 1 else 0 end) from student select sum(if(sex='男',1,0)) from student;--场景5:聚合参数不支持嵌套函数 select avg(count(*)) from student --这是会报错的--场景6：聚合操作时针对null的处理 create table tmp_1(val1 int,val2 int); insert into table tmp_1 values (1,2),(null,2),(2,3); select * from tmp_1; select * from tmp_1; --第二行数据(Null,2)在进行sum(vall + val2)的时候会被忽略 select sum(val1 +val2) from tmp_1; select sum(val1 +val2) from tmp_1; --可以使用coalesce函数将空都替换成0进行解决 selectsum(coalesce(val1,0)),sum(coalesce(val1,0) + val2) from tmp_1;--场景7：配合distinct关键字去重聚合 --此场景下，会变异期间会自动设置只启动一个reduce task 处理数据 可能造成数据拥堵 select count(distinct sex)as cnt1 from student; --可以先去重，在聚合 通过子查询完成 --因为先执行distinct的时候 可以使用多个reducetask来跑数据 select count(*) as gender_uni_cnt from (select distinct sex from student) a;--案例需求：找出student中男女学生年龄最大的及其名字 --这里使用了struct来构造数据 然后针对struct应用max找出最大元素 然后取值 select sex,max(struct(age,name)).col1 as age,max(struct(age,name)).col2 as name from student group by sex;select struct(age,name) from student; select struct(age,name).col1 from student; select max(struct(age,name)) from student;增强聚合
- 增强聚合包括grouping_sets、cube、rollup这几个函数；主要用于OLAP多维数据分析模式中，多维分析中的维指的分析问题时看待问题的维度、角度
- 下面通过案例更好的理解函数的功能含义。数据中字段含义：月份、天、用户标识、cookieid。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3s19F0RE-1666068736594)(C:\Users\康登云\AppData\Roaming\Typora\typora-user-images\image-20221009160913650.png)]
增强聚合–grouping sets
- grouping sets 是一种将多个group by逻辑写在一个SQL语句中的便利写法。等价于将不同维度的group by 结果集进行union all。grouping__id表示结果属于哪一个分组集合。
-------------------增强聚合------------------- --表创建并且加载数据 create table cookie_info(month string,day string,cookieid string )row format delimited fields terminated by ','; load data local inpath '/root/file/cookie_info.txt' into table cookie_info;select * from cookie_info;-----group sets--------- selectmonth,day,count(distinct cookieid) as nums,grouping__ID from cookie_info group by month,day grouping sets (month,day) order by grouping__ID; --grouping_id标识这一组结果属于那个分组集合， --根据grouping sets 中的分组添加month, day,1是代表month， 2是代表day--上面sql是等价于这个的 select month,Null,count(distinct cookieid)as nums,1 as grouping__id from cookie_info group by month union all select null as month,day,count(distinct cookieid) as nums,2 as grouping__id from cookie_info group by day;--再比如 selectmonth,day,count(distinct cookieid) as nums,grouping__id from cookie_info group by month,day grouping sets (month,day,(month,day)) order by grouping__id; --等价于 select month,null,count(distinct cookieid) as nums,1 as grouping_id from cookie_info group by month union all select null,day,count(distinct cookieid) as nums,2 as grouping__id from cookie_info group by day union all select month,day,count(distinct cookieid) as nums,3 as grouping__id from cookie_info group by month,day;增强聚合–cube
- cube表示根据croup by的维度的所有组合进行聚合。
- 对于cube来说，如果有n个维度，则所有组合的总个数是：2n
比如cube有a,b,c 3个维度，则所有组合情况是：（a,b,c）,(a,b)，(b,c),(a,c),(a),(b),©,()
----cube------------ select month,day,count(distinct cookieid) as nums,grouping__id from cookie_info group by month,day with cube order by grouping__id; --等价于 select null,null,count(distinct cookieid) as nums,0 as grouping__id from cookie_info union all select month,null,count(distinct cookieid)as nums,1 as grouping__id from cookie_info group by month union all select null,day,count(distinct cookieid) as nums,2 as grouping__id from cookie_info group by day() union all select month,day ,count(distinct cookieid) as nums,3 as grouping__id from cookie_info group by month,day;增强聚合–rollup
- cube的语法功能指的是:根据group by的维度的所有组合进行聚合。
- rollup是cube的子集，以最左侧的维度为主，从该维度进行层级聚合。
- 比如rollup有a,b,c3个维度，则所有组合情况是：(a,b,c),(a,b),(a),()
Hive Windows Functions 窗口函数
概述
窗口函数也叫做开窗函数、OLAP函数，其最大特点是：输入值是从select语句的结果集中的一行或多行的“窗口“中获取的。
如果函数具有over子句，则它的窗口函数。
窗口函数可以简单的解释位类似于聚合函数的计算函数，但是通过group by子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。
--建表加载数据 create table employee(id int,name string,deg string,salary int,dept string )row format delimited fields terminated by ','; --上传数据 load data local inpath '/root/hivedata/employee.txt' into table employee; -----sum+group by 普通常规聚合操作---------- select sum(salary) as total from employee group by dept;----sum+窗口函数聚合操作--------------- select id,name,deg,salary,dept,sum(salary) over(partition by dept) as total from employee;
窗口聚合函数
- 所谓窗口聚合函数指的是sum,max,min,avg这样的聚合函数在窗口中使用；
- 这里以sum()函数为例，其他聚合函数使用类似。
---建表并且加载数据 create table website_pv_info(cookieid string,createtime string,pv int )row format delimited fields terminated by ',';create table website_url_info(cookieid string,createtime string,url string )row format delimited fields terminated by ',';load data local inpath '/root/file/website_pv_info.txt' into table website_pv_info; load data local inpath '/root/file/website_url_info.txt'into table website_url_info; --查看两个表知否创建成功 select * from website_pv_info; select * from website_url_info;------窗口聚合函数的使用------------- --1、求出每个用户总PV树 sum+group by普通常规聚合操作 select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid; --2、sum+窗口函数 总共有四种用法 注意是整体聚合 还是积累聚合 --sum(...) over() 对表所有行求和 --sum（...）over(ordr by ...) 连续累计求和 --sum(...) over(partition by ... order by ...) 在每个分组内，连续积累求和--需求：求出网站总的pv数，所有用户所有访问加起来 --sum(...) over()对表所有行求和 select cookieid,createtime,pv,sum(pv) over() as total_pv --注意这里窗口函数是没有partition by也就是没有分组 全表所有行 from website_pv_info;3--需求：求出每个用户总pv --sum(...) over(partition by ...) 同组内所有行求和 select cookieid,createtime,pv,sum(pv) over(partition by cookieid) as total_pv from website_pv_info;--需求：求出每个用户截止到当天，累计的总pv数 --sum(...) over(partition by... order by ...),在每个分组内，连续累计求和 select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as current_total_pv from website_pv_info;--需求：全表pv的平均值 select * ,avg(pv) over() from website_pv_info;--求出同组的平均值 select * ,avg(pv) over(partition by cookieid ) from website_pv_info;--求出全表依次进行累加的平均值 select * ,avg(pv) over(partition by cookieid order by pv) from website_pv_info;窗口表达式
在sum(…) over(partition by… order by …)语法完整的情况下，进行累积聚合行为是：从第一行聚合到当前行。
Window expression窗口表达式个给我们提供了一种控制范围的能力，比如向前2行，向后3行。
语法如下：
关键字是rows between，包括下面这个几个选项 -preceding :往前 -following ：往后 -current row :当前行 -unbounded: 边界 -unbounded preceding ：表示从前面的起点 -unbounded following ：表示到后面的终点案例使用
--窗口表达式 select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as pv1 --默认从第一行到当前行 from website_pv_info;--第一行到当前行 select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2 from website_pv_info;--向前3行至当前行 select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4 from website_pv_info;--向前3行 向后1行 select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between current row and 1 unbounded following) as pv5 from website_pv_info;--当前行至最后一行 select cookieid,createtime ,pv,sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following ) as pv6 from website_pv_info;
窗口排序函数 – row_number家族
row number:在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复
rank;在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；
dense rank;在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；
----窗口排序函数 selectcookieid,createtime,pv,rank() over (partition by cookieid order by pv desc) as rn1, --在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复dense_rank() over(partition by cookieid order by pv desc) as rn2, --在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；row_number() over(partition by cookieid order by pv desc) as rn3 --在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置 from website_pv_info where cookieid = 'cookie1';--需求：找出每个用户访问pv最多的Top3 重复并列的不考虑 select * from ( select cookieid,createtime,pv,row_number() over(partition by cookieid order by pv desc) as seqfrom website_pv_info) tmp where tmp.seq <4;
窗口排序函数–ntile
将 每个组内的数据分为指定的若干个桶里（分为若干个部分），并且为 每一个桶分配一个桶编号。
如果不能平均分配，则优先分配较小编号的桶，并且每个桶中能放的行数最多相差1.
有时会有这样的需求：如果数据排序后分为三个部分，业务人员只关心其中的一部分，如何将这中间的三分一数据拿出来呢？ntile 行数即可满足
--把每个分组内的数据分为3桶 selectcookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as m2 from website_pv_info order by cookieid,createtime;
窗口分析行数
LAG(col,n,default) 用于统计窗口内往上第n行值
第一个参数为列，第二个参数往上第n 行（可选，默认为1），第三个参数为默认值（当往上第n行为Null时候，取默认值，如不指定，则为null）；
LEAD(col,n,default) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认值为1），第三个参数为默认值（当往下第n行为Null时候，取默认值，如不指定，则为NULL）;
FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
LAST_VALUE 取分组内排序后，截止当前行，最后一个值
---------------窗口分析函数------------- --LAG --取指定字段前面第2条的数据，没有就返回给定的值，没有给定的值就返回null select cookieid,createtime,url,row_number() over (partition by cookieid order by createtime) as rn,lag(createtime,2 ,'1970-01-01 00:00:00') over(partition by cookieid order by createtime) as last_1_time,lag(createtime,2 ) over(partition by cookieid order by createtime) as last_2_time from website_url_info;--lead --取指定字段后面第2条的数据，没有就返回给定的值，没有给定的值就返回null select cookieid,createtime,url,row_number() over(partition by cookieid order by createtime) as rn,lead(createtime,1,"1970-01-01 00:00:00") over(partition by cookieid order by createtime) as next_1_time,lead(createtime,2) over(partition by cookieid order by createtime) as next_2_time from website_url_info;--first_value -- 获取指定当前的字段的第一行数据 select cookieid,createtime,url,row_number() over (partition by cookieid order by createtime) as rn,first_value(url) over(partition by cookieid order by createtime) as first1 from website_url_info;--last_value-- 获取指定当前的字段的最后一个行数据 select cookieid,createtime,url,row_number() over (partition by cookieid order by createtime) as rn,last_value(url) over(partition by cookieid order by createtime) as last1 from website_url_info;
sampling 抽样函数
- 当数据量过大时，我们可能需要查找数据子集以加快数据处理分析。
- 这就是抽样，采样，一种用于识别和分析数据中的子集的技术，以发现整个数据集中的模式和趋势。
- 在HQL中，可以通过三种方式采样数据：随机采样，存储桶表采样和块采样。
Random随机抽样
- 随机抽样使用rand()函数来确保随机获取数据，limit来限制抽取的数据个数。
- 优点是随机，缺点是速度不快，尤其表数据多的时候。
- 推荐distribute+sort，可以确保数据也随机分布在mapper和reducer之间，使得底层执行有效率。
- order by 语句也可以达到相同目的，但是表现不好，因为order by是全局排序，只会启动运行一个reduce。
--需求：随机抽取2个学生的情况进行查看(推荐使用) select * from student distribute by rand() sort by rand() limit 2; --使用随机函数进行分，使用随机函数进行排序，最后使用limit显示前面2条数据--使用order by + rand 也可以实现同样的效果，但是效率不高 select * from studentorder by rand() limit 2;Blocak 基于数据块抽样
- block快采用允许随机获取n行数据，百分比数据或指定大小的数据。
- 采样粒度是hdfs快大小。
- 有点事速度快，缺点是不随机。
--block抽样 --根据行数抽样 select * from student tablesample ( 1 rows ); -- 抽取1行的数据--根据数据大小百分比抽样 select * from student tablesample ( 50 percent ); -- 抽取百分之50的数据--根据数据大小抽样 --支持数据单位b/B，k/K m/M g/G select * from student tablesample (1k); -- 抽取1kb的数据Bucket table 基于分桶表抽样
这是一种特殊的采样方法，针对分桶表进行了优化。有点事既随机速度也很快。
语法如下：
tablesample (bucket x out of y [on colname])
tablesample (bucket x out of y [on colname])[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WiMifLnx-1666068736595)(C:\Users\康登云\AppData\Roaming\Typora\typora-user-images\image-20221010112348469.png)]
describe fromatted t_usa_covid19_bucket; select * from t_usa_covid19_bucket tablesample(bucket 1 out 5 on state);
Hive函数重要应用案例
hive 中的分隔符
hive默认序列化类似LazySimpleSerDe，其只支持使用**单字节分隔符****（char）来加载文本数据，例如逗号、制表符、空格等等，默认的分隔符为“\001”根据不同问阿金的不同分隔符，我们可以通过在创建表时使用 row fromat delimited来指定文件中的分割符，确保正确将表中的每一列与文件中的每一列一一对应的关系。4
特殊数据
情况一：每一行数据的分隔符是多字节分隔符，例如：“||”、“–”等
情况二：数据的字段中包含了分隔符
解决方案
解决方案一：替换分隔符
使用程序提前将数据中的多字节分隔符替换为单字节分隔符
程序开发
抵用mr程序提前将数据中的多字节分隔符替换为单字节分隔符
public static class ChangeSplitMapper extends Mapper<LongWritable,Text,Text,NullWritable>{// 定义输出的Keyprivate Text outputKey = new Text();// 定义输出的Valueprivate NullWritable outputValue = NullWritable.get();@Overrideprotected void map(longWritable key,Text value,Context context) throws IOException,InterruptedException{// 获取每条数据String line = value.toString();// 将里面的||转换为|String newLine = line.replaceAll("\\|\\|","|");// 替换后的内容作为keythis.outputKey.set(newLine);// 输出结果context.write(this.outputKey,this.outputValue);} }解决方案二：RegexSerDe正则加载
Hive内置的SerDe
- 除了使用最多的LazySimpleSerDe，Hive该内置了很多SerDe类；
- 官网地址：https://cwiki.apache.org/confluence/display/Hive/SerDe
- 多种SerDe用于解释和加载不同类型的数据文件，常用的有ORCSerDe、RegexSerDe、JsonSerDe等。
重新建表
基于正则表达式，使用RegexSerde建表
--如果该表存在就删除该表 drop table if exists singer; --创建表 create table singer (id string, --歌手idname string, --歌手名country string, --国家province string, --省份gender string, --性别works string --作品 )--指定使用RegexSerde加载数据 Row Format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties ("input.regex" = "([0-9]*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)");--加载数据 load data local inpath '/root/file/test01.txt'into table singer;--查看数据以及导入成功 select * from singer;--如果表存在，就删除表 drop table if exists apachelog;--创建表 create table apachelog(ip string, --ipstime string, --时间mothed string, --请求方式URL string, --请求地址policy string, --请求协议stat string, --请求状态body string --字节大小 ) --指定使用RegexSerde加载数据 row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' --指定正则表达式with serdeproperties("input.regex"= "([^ ]*) ([^}]*) ([^ ]*) ([^ ]*) ([^ ]*) ([0-9]*) ([^ ]*)")stored as textfile ;--将本地数据加载到表中 load data local inpath '/root/file/apache_web_access.log' into table apachelog;--查看加载后数据的表 select * from apachelog;解决方案三：自定义InputFormat
方案概述
- Hive中也允许使用自定义InputFormat来解决以上问题，通过在自定义InputFormat,来自定义解析逻辑实现读取每一行的数据。
自定义InputFromat
与MapReduce中自定义InputFormat一致，继承TextInputFormat
/* * @ClassName UserInputFormat * @Description TODO用于实现自定义InputFormat读取每行数据 * */ public class UserInputFormat extends TextInputFormat{@Overridepublic RecordReader<LongWritable,Text> getRecordReader(InputSplit genericSplit,JobConf job,Reporter reporter) throws IOException{reporter.setStatus(genericSplit.toString());UserRecordReader reader = new UserRecordReader(job,(FileSplit)genericSplit);return reader;} }自定义RecordReader
与MapReduce中自定义RecordReader一致，实现RecordReader接口，实现next方法
public synchronized boolean next(LongWritable key,Text value) throws IOException{while(getFilePosition() <= end){key.set(pos);int newSize = in.readLine(value,maxLineLength,Math.max(maxBytesToConsume(pos),maxLineLength));String str = value.toString().replaceAll("\\|\\|","\\|");value.set(str);pos += newSize;if(newSize < maxLineLength){return true;}LOG.info("Skipped line of size"+new Size +"at pos"+(pos - newSize)); } }添加自定义InputFormat到Hive中
打成jar包，添加到Hive的classpath中
添加到Hive的环境变量中：
add jar /export/server/hive-3.1.2-bin/lib/HiveUserInputFormat.jar;--指定以InputFormat add jar /root/file/HiveUserInputFormat.jar;-- 如果表以及存在就删除表 drop table if exists singer; --创建表 create table singer(id string, --歌手idname string, --歌手名称country string, --国家province string, --省份gender string, --性别works string ) --指定使用分隔符为| row format delimited fields terminated by "|" --指定使用自定义的类实现解析 stored as inputformat 'bigdata.itcast.cn.hive.mr.UserInputFormat' outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';--加载数据 load data local inpath '/root/file/test01.txt' into table singer;select * from singer;总结
问题与案例
当数据文件中出现多字节分隔或者数据中包含了分隔符时，会导致数据加载与实际表的字段不匹配的问题，基于这个问题我们提供了三种方案：
- 替换分隔符
- 正则加载RegexSerde
- 自定义InputFormat
其中替换分隔符无法解决数据字段中依然存在的分隔符的问题，自定义InputFormat的开发成本较高，所以整体推荐使用正则加载的方式来实现特殊数据的处理。
实际工作需求
分析需求
业务需求中，经常需要对用户的访问、用户的来源进行分析，用于支持运营和决策。
例如对用户访问的页面进行统计分析，分析热门受访问页面的Top10，观察大部分用户最喜欢的访问最多的页面等。
又或者需要分析不同搜索平台的用户来源分析，统计不同搜索平台中进入网站的用户个数；
根据数据进行精准的引导和精准的广告投放等
URL的基本组成
数据格式
要想实现上面的受访分析，来源分析等业务，必须在实际处理数据的过程中，对用户访问的URL和用户的来源URL进行解析处理，获取用户名的访问域名，访问页面，用户数据参数、来源域名、来源路径等信息。
URL的基本组成
在对URL进行解析时，我们要先了解URL的基本组成部分，在根据实际的需求从URL中获取对应的不符，例如一条URL由下几个部分组成
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lnmOPJLB-1666068736595)(C:\Users\康登云\AppData\Roaming\Typora\typora-user-images\image-20221010173600863.png)]
Hive中的URL解析函数
函数
Hive中为了实现对URL的解析，专门提供了解析URL的函数parse_url和parse_url_tuple；在show functions 中可以看到对应的函数。
parse_url 函数
功能
parse_url函数是Hive中提供的最基本的url解析函数，可以根据指定的参数，从url解析出对应的参数值进行返回，函数为普通的一对一函数类型。
语法
parse_url(url,pastToExtract[,key])-extracts a past from URL Parts:HOST,PATH,QUERY,REF,PRoTOCOL,AUTHORITY,FILE,USERINFO key实例
-------------------URL解析-------------------------- --注意这里面的参数'HOST',什么的都必须为大写 -- 返回主机名 select parse_url('http://facebook.com/path/p1.php?id=10086','HOST');--返回参数 select parse_url('http://facebook.com/path/p1.php?id=10086&name=allen','QUERY'); --返回参数当中的值 select parse_url('http://facebook.com/path/p1.php?id=10086&name=allen','query','name');parse_url的弊端
如果想一次解析多个参数，需要使用多次函数
select id,parse_url('http://facebook.com/path/p1.php?id=10086',"HOST") as host,parse_url('http://facebook.com/path/p1.php?id=10086&name=allen','PATH') as path,parse_url('http://facebook.com/path/p1.php?id=10086&name=allen','QUERY') as query fromparse_url_tuple函数
功能
parse_url_tuple函数是hive中提供的基于parse_url的url解析函数，可以通过一个指定多个参数，从URL解析出多个参数的值进行返回多列，函数为特殊的一对多函数类型，即通常所说的UDTF函数类型。
语法
parse_url_tuple(url,partname1,partname2,..,partnameN)-extracts N(N>=1) parts from a URL. lt tabkes a URL and one or multiple partnames,and returns a tuple.实例
drop table if exists tb_url; --建表 create table tb_url(id int,url string )row format delimited fields terminated by '\t';--加载数据‘ load data local inpath '/root/file/url.txt' into table tb_url;--查询表数据是否导入成功 select * from tb_url;--查询url的主机名和路径名 select parse_url_tuple(url,"HOST","PATH") as (host,path) from tb_url;--查询url的协议，主机名，路径名 select parse_url_tuple(url,"PROTOCOL","HOST","PATH") as (protocol,host,path) from tb_url;--查询url的主机名，路径名，请求参数 select parse_url_tuple(url,"HOST","PATH","QUERY") as (host,path,query) from tb_url;parse_url_tuple函数的问题
当执行一下SQL语句，将id与host、path、query等共同查询时报错
select id,parse_url_tuple(url,"HOST","PATH","QUERY") as (host,path,query) from tb_url;UDTF的问题
hive中的一对多的UDTF函数可以实现高效的数据转换，但是也存在或者一些使用中的问题，UDTF函数对于很多场景下使用限制，例如：select 时不能包含其他字段，不能嵌套调用，不能与group by等放在一起调用等等。
方式一：直接在select 后单独使用
方式二：与Lateral View 发在一起使用
Lateral View侧视图
功能
- Lateral View是一种特殊的语法，主要用于搭配UDTF类型功能的函数一起使用，用于解决UDTF函数的一些查询限制的问题。
- 侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by ，order by 、limit 等语句中，不需要在单独嵌套一层子查询。
- 一般要使用UDTF，就会固定搭配lateral view使用。
基础语法
select .... from tablelA lateral view UDTF(xxx) 别名 as coll,col2,col3 ....parse_url_tuple函数+Lateral View视图使用案例
--单个lateral view 使用 select a.id as id,b.host as host,b.path as path,b.query as query from tb_url a lateral view parse_url_tuple(url,"HOST","PATH","QUERY") b as host,path,query;--多个lateral view selecta.id as id,b.host as host,b.path as path,c.protocol as protocol,c.query as query from tb_url alateral view parse_url_tuple(url,"HOST","PATH") b as host,pathlateral view parse_url_tuple(url,"PROTOCOL","QUERY") c as protocol,query;--Outer Lateral View --如果UDTF不产生数据时，这时侧视图与原表关联的结果将为空 selectid,url,col1 from tb_urllateral view explode(array()) et as col1;--如果加上outer关键字以后，就会保留原数据，类似于outer join select id,url,col1 from tb_urllateral view outer explode(array()) et as col1;
行列转换应用与实现
--建表
create table row2coll(col1 string,col2 string,col3 int
)row format delimited fields terminated by '\t';--加载数据到表中
load data local inpath '/root/file/c2r/r2c1.txt' into table row2coll;select * from row2coll;
select * from row2coll;--sql最终实现
selectcol1 as col1,max(case col2 when 'c' then col3 else 0 end) as c,max(case col2 when 'd' then col3 else 0 end) as d,max(case col2 when 'e' then col3 else 0 end) as e
fromrow2coll
group bycol1;
多行转单列实现
实现字符串的拼接函数：
concat函数
-
功能
- 用于实现字符串拼接，不可指定分隔符
-
语法
-
concat(element1,element2,element3....)
-
-
测试
-
selecct concat("it","cast","And","heima"); -
itcastAndhima
-
-
特点：如果任意一个元素为null，结果就为null
-
select concat("it","cast","And","null") -
null
-
concat_ws函数
-
功能
- 用于实现字符串拼接，可以指定分隔符
-
语法
-
concat_ws(SplitChar,elementl,element2.....)
-
-
测试
-
select concat_ws("-","itcast","And","heima"); -
itcast-and-heima
-
-
特点：任意一个元素不为null，结果就不为null
-
select concat_ws("-","itcast","And",null) -
itcast-And
-
实现数据的收集函数
collect_list函数
-
功能
- 用于将一列中的多行合并为一行，不进行去从
-
语法
-
collect_list(colName)
-
-
测试
-
select collect_list(coll) from row2coll -
[“a”,“a”,“a”,“b",“b”,“b”]
-
collect_set函数
-
功能
- 用于将一列中的多行合并为一行，并进行去重
-
语法
-
coolect_set(colName)
-
-
测试
-
select collect_set(coll) from row2coll; -
[“b”,“a”]
-
代码实现
--建表
create table row2col2(col1 string,col2 string,col3 int
)row format delimited fields terminated by '\t';--加载数据列表中
load data local inpath '/root/file/r2c2.txt' into table row2coll;select * from row2coll;
--查看concat_ws函数拼接的类型必须是string类型的
describe function extended concat_ws;--最终sql实现
--因为concat_ws他只认字符串的拼接，所以在进行拼接之前需要使用cast将收集到的字段进行强制类型转换在用逗号进行拼接
selectcol1,col2,concat_ws(',',collect_list(cast(col3 as string))) as col3
from row2coll
group bycol1,col2;
多列传多行
union关键字
-
功能
- 将多个select语句结果合并为一个，且结果去重且排序
-
语法
-
select_statement UNION [DISTINCT] select_statement UNION [DISTINCT] select_statement....
-
-
测试
-
select 'b','a','c' union select 'a','b','c' union select 'a','b','c';
-
union all关键字
-
功能
- 将多个select语句结果合并为一个，且结果不去重不排序
-
语法
-
select_statement UNION ALL select_statement UNION ALL select_statement ...
-
-
测试
-
select 'b','a','c' union all select 'a','b','c' union all select 'a','b','c';
-
代码实现
--创建表
create table col2row1
(col1 string,col2 int,col3 int,col4 int
)row format delimited fields terminated by '\t';--加载数据
load data local inpath '/root/file/c2r1.txt' into table col2row1;select *
from col2row1;--最终实现
select col1,'c' as col2,col2 as col3 from col2row1
UNION all
select col1,'d' as col2,col3 as col3 from col2row1
union all
select col1,'e' as col2,col4 as col3 from col2row1;
单列转多行(重点)
--创建表
create table col2row2(col1 string,col2 string,col3 string
)row format delimited fields terminated by '\t';
select * from col2row1;-- 加载数据
load data local inpath '/root/file/c2r2.txt' into table col2row2;select * from col2row2;
-- 因为如果要使用col3炸开的话，就必须是array或map类型
--如何将col3转换为数组呢，使用split
select split(col3,',') from col2row2;--SQL最终实现
select col1,col2,lv.col3 as col3
from col2row1
lateral viewexplode(split(col3,',')) lv as col3;
JSON数据处理
json格式
json数据格式是数据存储及数据处理中最常见的结构化数据格式之一，很多场景下公司都会将数据以JSON格式存储在HDFS中，当构建数据仓库时，需要对JSON格式的数据进行处理和分析，那么就需要在Hive中对json格式的数据进行解析读取。
HIve中的json的两种处理方式
Hive中为了实现JSON格式的数据解析，提供了两种解析JSON数据的方式，在实际工作场景下，可以根据不同数据，不同的需求来选择合适的方式对json格式数据进行处理。
方式一：使用json处理函数处理
- get_json_object、json_tuple，这两种函数都可以实现将JSON数据中的每个字段独立解析出来，构建成表
方式二：JSON Serde加载数据
- 建表时指定Serde，加载JSON文件到表中，会自动解析为对应的表格式
JSON函数：get_json_object
get_json_object
-
功能
- 用于解析JSON字符串，可以从JSON字符串中返回指定的某个对象列的值
-
语法
-
get_json_object(json_txt,path) -Extract a json object from path
-
-
参数
- 第一个参数：指定要解析的JSON字符串
- 第二个参数：指定要返回的字段，通过$.columnName的方式来指定path
-
特点
- 每次只能返回JSON对象中一列的值
使用案例
--get_json_object
--创建表
create table tb_json_test1(json string
);--加载数据
load data local inpath '/root/file/device.json' into table tb_json_test1;select * from tb_json_test1;select--函数当中的$表示的是该对象，$.device表示调用该对象的建对应的值--获取设备名称get_json_object(json,"$.device") as device,--获取设备类型get_json_object(json,"$.deviceType") as deviceType,--获取设备型号强度get_json_object(json,"$.signal") as signal,--获取时间get_json_object(json,"$.time") as stime
from tb_json_test1;
JSON函数：json_tuple
json_tuple
-
功能
- 用于实现Json字符串的解析，可以通过指定多个参数来解析JSON返回多列的值
-
语法
-
json_tuple(jsonStr,p1,p2,...,pn) -- 他比较像 get_json_object,只不过他可以一次性取出多列值，并返回
-
-
参数
- 第一个参数；指定要解析的JSON字符串
- 第二个参数：指定要返回的第一个字段
- …
- 第N+1个参数：指定要返回的第N个字段
-
特点
- 功能类似于get_json_object,但是可以调用一次返回多列的值，属于UDTF类型的函数，一般搭配lateral view使用
- 返回的每一列都是字符串类型
使用案例
--get_json_object
--创建表
create table tb_json_test1(json string
);--加载数据
load data local inpath '/root/file/device.json' into table tb_json_test1;--查询是否加载成功
select * from tb_json_test1;--json_tuple
--单独使用
select--解析所有字段json_tuple(json,"device","deviceType","signal","time") as(device,deviceType,signal,stime)
from tb_json_test1;--搭配侧视图使用
selectjson,device,deviceType,signal,stime
from tb_json_test1lateral view json_tuple(json,"device","deviceType","signal","time") bas device,deviceType,signal,stime;
JSONSerde
功能
- 上述解析JSON的过程中是将数据作为一个JSON字符串加载到表中，在通过JSON解析函数对JSON字符串进行解析，灵活性比较高，但是对于如果整个文件就是一个JSON文件，在使用起来就相对比较麻烦。
- Hive中为了简化对于JSON文件的处理，内置了一种专门用于解析JSON文件的Serde解析器，在创建表时，只要指定使用JSONSerde解析表的文件，就会指定将JSON文件中的每一列进行解析。
JSON数据处理
不论是Hive中的JSON函数还是自带的JSONSerde，都可以实现对于JSON数据的解析，工作中一般根据数据格式以及对应的需求来实现解析。如果数据中每一行只有别字段是JSON格式字符串，就可以使用JSON函数来实现处理，但是如果数据加载的文件整体就是JSON文件，每一行数据就是一个JSON数据，那么建议字节使用JSONSerde来实现处理最为方便。
代码实例
--JsonSerDe
drop table if exists tb_json_test2;--创建表
create table tb_json_test2(device string,deviceType string,signal double,`time` string
)row format serde 'org.apache.hive.hcatalog.data.JsonSerDe' --设置JSON序列化
STORED AS TEXTFILE;--将数据加载到表中
load data local inpath '/root/file/device.json' into table tb_json_test2;--查询数据，可以直接解析出来了
select * from tb_json_test2;
窗口函数应用
案例1：连续登陆用户
实现方案分析
基于以上需求根据数据寻找规律，要想得到连续登陆用户，必须找到两个相同用户ID的行之间登陆日期之间的关系，例如：统计联系登陆两天的用户，只要用户ID相等，并且登陆日期之间相差1天即可。基于这个规律，我们有两种方案可以实现该需求。
-
方案一：表中的数据自连接，构建笛卡尔积(不建议，效率低)
-
实例代码
--建表 create table tb_login(userid string,logintime string )row format delimited fields terminated by '\t';load data local inpath '/root/file/login.log' into table tb_login;select * from tb_login;--自连接过滤实现 --a.构建笛卡尔积 selecta.userid as a_userid,a.logintime as a_logintime,b.userid as b_userid,b.logintime as b_logintime from tb_login a,tb_login b;--上述查询结果保存为临时表 create table tb_login_tmp as selecta.userid as a_userid,a.logintime as a_logintime,b.userid as b_userid,b.logintime as b_logintime from tb_login a,tb_login b;--过滤数据：用户id相同并且登陆日期相差1 selecta_userid,a_logintime,b_userid,b_logintime from tb_login_tmp where a_userid = b_useridand cast(substr(a_logintime,9,2) as int) -1 = cast(substr(b_logintime,9,2) as int);--统计连续两天登陆用户 select distinct a_userid from tb_login_tmp where a_userid = b_useridand cast(substr(a_logintime,9,2) as int) -1 = cast(substr(b_logintime,9,2) as int);
-
-
方案二：使用窗口函数来实现
-
窗口函数lead
- 功能：用于从当前数据中基于当前行的数据向后偏移取值
- 语法：lead(colName,N,defautValue)
- colName:取哪一列的值
- N:向后偏移N行
- defaultValue:如果娶不到返回的默认值
-
分析
- 我们可以基于用户的登陆信息，找到如下规律：
- 连续两天登陆：用户下次登陆时间=本次以后的第二天
- 连续三天登陆：用户下下次登陆时间=本次登陆以后的第三天
- 我们可以对用户id进行分区，按照登陆时间进行排序，通过lead函数计算出用户下次登陆时间
- 通过日期函数计算出登陆以后第二天的日期，如果相等即为连续两天登陆
- 我们可以基于用户的登陆信息，找到如下规律：
-
实现代码
--建表 create table tb_login(userid string,logintime string )row format delimited fields terminated by '\t';load data local inpath '/root/file/login.log' into table tb_login;select * from tb_login;--方案二：窗口函数实现 --b.连续两天登陆的实现 with t1 as(selectuserid,logintime,--本次登陆日期的第二天date_add(logintime,1) as nextday,--按照用户i分区，按照登陆日期排序，取下一次登陆时间，娶不到就为0lead(logintime,1,0) over(partition by userid order by logintime) as nextloginfrom tb_login )select distinct userid from t1 where nextday = nextlogin;
-
案例2：级联累加求和
需求：统计每个用户每个月的消费总金额以及当前累计消费总额
- 当前有一份消费数据如下，记录了每个用户在每个月的所有消费记录，数据表中一共有三列
- userId：用户唯一id，唯一标识一个用户
- mth:用户消费的月份，一个用户可以在一个月多次消费
- money：用户每次消费金额
实现方案分析
- 如果要实现以上需求，首先要统计每个用户每个月的消费总金额，分组实现聚合，但是需要按照用户id，将该用户这个月之前的所有月份的的所有消费总额金额尽心累加实现
- 该需求可以通过两种方案来实现：
- 方案一：分组统计每个用户每个月的消费金额，然后构建自连接，根据条件分组聚合
- 方案二：分组统计每个用户每个月的消费金额，然后使用窗口聚合函数实现
代码实现
--2、级联累加求和
--建表加载数据
create table tb_money(userid string,mth string,money int
)row format delimited fields terminated by '\t';--加载数据
load data local inpath '/root/file/money.tsv' into table tb_money;--查看数据是否加载成功
select * from tb_money;--统计得到每个用户每个月的消费总金额，并保存到另外一张表中
create table tb_money_mtn as
selectuserid,mth,sum(money) as m_money
from tb_money
group by userid,mth;--查看创建查询的结果
select * from tb_money_mtn;--方案一：自连接分组聚合
--基于每个用户每个月的消费总金额进行自连接
selecta.*,b.*
from tb_money_mtn a join itcast.tb_money_mtn b on a.userid = b.userid;--同一个用户 同一个月的数据发到同一个组，月份排序selecta.userid,a.mth,max(a.m_money) as current_mth_money, --当月话费sum(b.m_money) as sccumulate_money --累计花费
from tb_money_mtn a join tb_money_mtn b on a.userid = b.userid
where b.mth <= a.mth
group by a.userid,a.mth
order by a.userid,a.mth;--方案二：窗口函数实现
--统计每个用户每个月消费金额及累计总金额
selectuserid,mth,m_money,sum(m_money) over (partition by userid order by mth) as t_money
from tb_money_mtn;select * from tb_money_mtn;案例3：分组TopN
需求：统计查询每个部门薪资最高的前两名员工的薪水
现在有一份数据如下，记录所有员工的信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DIZ5GthJ-1666068736596)(C:\Users\康登云\AppData\Roaming\Typora\typora-user-images\image-20221013154712426.png)]
实现方案分析
- 根据上述需求，这种情况下是无法根据group by分组聚合实现的，因为分组聚合只能实现返回一条聚合的结果，但是需求中需要每个部门返回薪资最高的前两名，有两条结果，这时候就需要用到窗口函数中的分区来实现了。
窗口函数实现
- TopN函数：row_number、rank、dense_rank
- row_number:对每个分区的数据进行编号，如果值相同，继续编号
- rank:对每个分区的数据进行编号，如果值相同，编号相同，但留下空位
- 基于row_number实现，按照部门分区，每个部门按照薪水降序排序
代码实例
drop table if exists tb_emp;
--方案三：TopN函数row_number、rank、dense_rank
--建表加载数据
create table tb_emp(empno string,ename string,job string,managerid string,hiredate string,salary double,bonus double,deptno string
)row format delimited fields terminated by '\t';
--加载数据
load data local inpath '/root/file/emp.txt' into table tb_emp;
--查看数据是否加载成功
select * from tb_emp;--过滤每个部门最高的前两名
with t1 as(select ename,job,salary,deptno,row_number() over(partition by deptno order by salary) as rnfrom tb_emp
)select * from t1 where rn <= 2;
拉链表的设计与实现
数据同步问题
背景
- Hive在实际工作中主要用于构建离线数据仓库，定期的从各种数据源中同步采集数据到Hive中，经过分层转换提供数据应用。
- 例如每天需要从MySQL中同步最新的订单信息，用户信息，店铺信息等到数据仓库中，进行订单分析，用户分析。
解决方案
方案一：在hive中用心的addr覆盖008的老的addr，字节更新
优点：实现最简单，使用起来最方便
缺点:没有历史状态，008的地址是1月2号sh，但是1月2号之前的gz的，如果要查询008的1月2号之前的addr就无法查询，也不能使用sh代替
方案二：每次数据改变，根据日期构建一份全新的快照表，每天一张表
优点：记录了所有数据在不同的状态
缺点：冗余存储了很多没有翻身变化的数据，导致存储的数据量过大
方案三：构建拉链表，通过时间标记发生的变化的数据的每种装填的时间周期
拉链表的设计
功能与应用场景
- 拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题
- 拉链表的设计师将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同的时间三的所有状态，通过时间进行标记每个状态的生命周期，查询时，根据需求可以获取指定时间范围状态的数据，默认用9999-12-31等最大值来表示最新状态。
拉链表的实现
数据准备
--创建拉链表 create table dw_zipper(userid string,phone string,nick string,gender int,addr string,starttime string,endtime string )row format delimited fields terminated by '\t';--加载模拟数据 load data local inpath '/root/file/zipper.txt' into table dw_zipper;--查询 select * from dw_zipper;增量采集
--创建ods层增量表 加载数据 create table ods_zipper_update(userid string,phone string,nick string,gender int,addr string,starttime string,endtime string )row format delimited fields terminated by '\t';load data local inpath '/root/file/update.txt' into table ods_zipper_update;--查看数据是否加载成功 select * from ods_zipper_update;创建临时表
create table tmp_zipper(userid string,phone string,nick string,gender int,addr string,starttime string,endtime string )row format delimited fields terminated by '\t';合并拉链表与增量表
insert overwrite table tmp_zipper select -- 把增量数据拉取过来userid,phone,nick,gender,addr,starttime,endtime from ods_zipper_update union all --查询原来拉链表的所有数据，并将这次需要更新的数据的endTime更改为更新值的startTime selecta.userid,a.phone,a.nick,a.gender,a.addr,a.starttime,--如果这条数据没有更新或者这条数据不是要更改的数据，就保留原来的值，否则就更改为新数据的开始时间-1if(b.userid is null or a.endtime <'9999-12-31',a.endtime,date_sub(b.starttime,1)) as endtime from dw_zipper a left join ods_zipper_update b on a.userid = b.userid;
Hive表设计优化
Hive查询基本原理
- Hive的设计思想通过元数据解析描述将HDFS上的文件映射成表；
- 基本的查询原理是当用户通过HQL语句对Hive中的表进行复杂数据处理和计算时，默认将其转换为分布式计算MapReduce程序对HDFS中的数据进行读取处理的过程。
- 当执行查询计划时，Hive会使用表的最后一级目录作为底层处理数据的输入
- Step2:然后将整个HDFS中表的目录作为底层查询的输入，可以通过explain命令查看执行计划依赖的数据
分区表结构-分区设计思想
- Hive提供了一种特殊的表结构来解决—分区表结构。分区表结构的设计思想是：根据查询的需求，将数据按照查询的条件【一般以时间】进行划分分区存储，将不同分区的数据单独使用一个HDFS目录来进行存储，当底层实现计算时，根据查询的条件，只读取对应分区的数据作为输入，减少不必要的数据加载，提高程序的性能。
Hive中Join的问题
- 默认情况下，Hive底层是通过MapReduce来实现的；
- MapReduce在处理数据之间Join的时候有两种方式：Mapjoin,ReduceJoin ，其中MapJoin效率较高；
- 如果有两张非常大的表要进行Join，底层无法使用MapJoin提高Join的性能，只能走某人的ReduceJoin；
- 而ReduceJoin必须经过Shuffle过程，相对性能比较差，而且产生数据倾斜。
分桶表设计思想
- 分区表时将数据划分不同的目录进行存储，而分桶表是将数据划分不同的文件进行存储。
- 分桶表的设计师按照一定的规则【底层通过MapReduce中的多个Reduce来实现】将数据划分到不同的文件中进行存储，构建分桶表。
- 如果有两张表按照相同的划分规则【比如按照Join的关键字段】将各自的数据进行划分；
- 在join时，就可以实现Bucket与Bucket的Join，避免不必要的比较，减少笛卡尔积数量
Hive中的索引优化
- 在传统的关系型数据库例如Mysql，oracle，为了提高数据的查询效率，可以为表中的字段单独构建索引，查询时，可以基于字段的索引快速的实现查询，过滤等操作。
- Hive中也同样提供了索引的设计，允许用户为字段构建索引，提高数据的查询效率，但是Hive的索引与关系型数据库中的索引并不相同，比如，Hive不支持主键或者外键索引。
- Hive索引可以建立在表中的某些列上，以提升一些操作的效率。
索引的原理
- Hive中的索引的基本原理
- 当某张表的某个字段创建索引时，Hive中会指定创建一张索引表，该表记录了该字段的每个值与数据实际物理位置之间的关系，例如数据所在的HDFS文件地址，以及所在文件中偏移量offset等信息。
- Hive索引目的
- 提高Hive表指定列的查询速度。没有索引时，类似WHERE tabl.coll = 10的查询，Hive会加载整张表或分区，然后处理所有的行，但是如果在字段coll上面存在索引时，那么只会加载和处理文件的一部分
索引的使用
可以使用Hive3.0一下版本的测试
创建索引
--为表中的userid构建索引 create index idx_user_id_login on table tb_login_part(userid) --索引类型为Compact，Hive支持Compact和Biemap类型，存储的索引内容不同 as 'COMPACT' --延迟构建索引 with deferred rebuild;
- 构建索引：通过运行一个mapReduce程序来构建索引
alter index idx_user_id_login ontb_login_part rebuild;
- 查看索引结构
desc default__tb_login_part_idx_user_id_login__;
- 查看索引内容
select * from default__tb_login_part_idx_user_id_login__;
- 删除索引
drop index idx_user_id_login on tb_login_part;Hive索引的问题
- Hive构建索引的过程是通过一个MapReduce程序来实现的；
- 每次Hive中原始数据表的数据发生更新时，索引表不会自动更新；
- 必须手动执行一个Alter index命令来实现通过MapReduce更新索引，导致整体性能较差，维护相对繁琐。
手动更新索引表：通过MapReduce 实现
alter index idx_user_id_login on tb_login_part rebuild;索引设计-小结
- 由于Hive的索引设计过于频繁，所以从Hive3.0版本开始，取消了对Hive Index的支持及使用；
- 如果使用的是Hive1.x或者Hive2.x，在特定的场景下依旧可以使用Hivie Index来提高性能。
- 实际工作场景中，一般不推荐使用Hvie Index，推荐使用ORC文件格式中的索引，物化视图来代替Hive Index提高查询性能。
文件格式-概述
Hive数据存储的本质还是HDFS，所有的数据读写都基于HDFS的文件来实现；
为了提高对HDFS文件读写的性能，Hive提供了多种文件存储格式：TextFile,SequenceFile,ORC,Parquet等；
不同的文件存储格式具有不同的存储特点，有的可以降低存储空间，有的可以提高查询性能。
Hive的格式在建表时指定，默认时TextFile.
stored as file_format文件格式-TextFile
- TextFile时Hivezho默认的文件格式，存储形式按行存储。
- 工作中最常见的数据文件就是TextFile文件，几乎所有的元数据生成都是TextDFile格式，所以Hive设计时考虑到为了避免各种编码及数据错乱的问题，选用了TextFile作为默认的格式
- 建表时不是指定存储格式即为TextFile，导入数据时把数据文件拷贝至Hdfs不进行处理
优点 缺点 应用场景 最简单的数据格式，可以字节查看使用任意的分隔符进行分割，便于和其他工具共享数据可以搭配压缩一起使用 耗费存储空间，IO性能较低，结合压缩时Hive不进行数据切分合并，不能进行并行操作，查询效率低，按行存储，读取列的性能差 适合于小量数据的存储查询，一般用于做第一层数据加载和测试使用。 文件格式-SequenceFile
- SequenceFile是Hadoop里面用来存储列化的键值对即二进制的一种 文件格式。
- SequenceFile文件也可以作为MapReduce作业的输入和输出，Hie也支持这种格式。
优点 缺点 应用场景 以二进制的kv形式存储数据，与底层加护更加友好，性能更快吗，可压缩，可分割，优化磁盘利用率和IO可以并行操作数据，查询效率高，也可以用于 存储多个小文件 存储空间消耗最大，与非Hadoop生态系统之外的工具不兼容，构建sequenceFile需要通过textFile文件转换加载 适合于小量数据，但是查询列比较多的场景。 文件格式-Parquet
- Parquet是一种支持嵌套结构的列存储文件格
- 是一种支持嵌套数据模型对的列式存储系统，作为大户数据系统中OLAP查询的优化方案，它已经被多种查询引擎原生支持，并且部分高性能引擎将其作为默认的文件存储格式。
- 通过数据编码和压缩，以及映射下推和谓词下推功能，Parquet的性能也较之其他文件格式有所提升。
优点 缺点 应用场景 更高效的压缩和编码可压缩，可分割，优化磁盘利用率和I/O，可用于多种数据处理框架 不支持update,insert,delite,ACID 使用与字段数非常多，无更新，只取部分列的查询。 文件格式-ORC
- ORC文件格式也是一种Hadoop生态圈的列式存储格式；
- 它的生产早在2013年初，最初产生自Apache Hive，用于降低Hadoop数据存储空间和家属Hive查询速度；
优点 缺点 应用场景 列式存储，存储效率非常高，可压缩，高效的列存取，查询效率较高，支持索引支持矢量化查询 加载时性能消耗较大，需要通过text问阿金转化生成读取全量数据时性能较差 使用与Hive中大型的存储，查询
数据压缩优化
- Hive底层运行MapReduce程序时，磁盘I/O操作，网络数据传输、shuffle和merge要花大量的时间，尤其是数据规模很大和工作负载密集的情况下。
- 鉴于磁盘I/O和网络宽带是Hadoop的宝贵资源，数据压缩对于节省资源，最小化磁盘I/O和网络传输非诚有帮助。
- Hive压缩实际上说的就是MapReduce的压缩。
压缩的优点
- 减小文件存储所占空间
- 加快文件传输效率，从而提高系统的处理速度
- 降低IO读写的次数
压缩的缺点
使用数据时需要想对文件解压，加重CPU符合，压缩算法越复杂，解压时间越长
数据压缩-Hive中压缩配置
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LM89dlyu-1666068736597)(C:\Users\康登云\AppData\Roaming\Typora\typora-user-images\image-20221014131359109.png)]
数据压缩-Hive中压缩测试
- 创建表，指定为orc格式，并使用snappy压缩
create table tb_sogou_orc_snappy stored as orc tblproperties("orc.compress"="SNAPPY") as select * from tb_sogou_source;
存储优化
存储优化-避免小文件生成
Hive的存储本质还是HDFS，HDFS是不利于小文件存储的，因为每个小文件会产生一条元数据信息，并且不利用MapReduce的处理，MapReduce中每个小文件会启动一个MapTesk计算处理，导致资源的浪费，所以在使用Hive进行处理分析时，要尽量避免小文件的生成。
Hive中提供了一个特殊的机制，可以自动的判断是否是小文件，如果是小文件可以自动将小文件进行合并。
--如果hive的程序，只有MapTask产生的所有小文件进行合并 set hive.merge.mapfiles=true; --如果hive的程序有Map和ReduceTask陈胜的所有小文件进行合并 set hive.merge.mapredfiles=true; --每一个合并的文件的大小（244M） set hive.merge.size.per.task=256000000; --平均每个文件的大小，如果小于这个值就会进行黑冰（15M） set hive.merge.smallfiles.avgsize=16000000;存储优化-合并小文件
如果遇到数据处理的输入是小文件的情况，怎么解决呢？
Hive中提供了一种输入类CombineHiveInputFormat,用于将小文件合并以后，在进行处理。
--设置Hive底层MapReduce读取数据的输入类：将所有文件合并为一个大文件作为输入 set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;存储优化-ORC文件索引
- 在使用ORC文件时，为了加快读取ORC文件中的数据内容，ORC提供了两种索引机制：Row Group Index和Bloom Filter Index可以帮助查询ORC文件的性能
- 当用户写入数据时，可以根据索引提前对数据进行过滤，避免不必要的数据扫描。
存储优化-ORC矢量查询
Hive的默认查询执行引擎一次处理一行，而矢量化查询执行是一种Hive针对ORC文件操作的特性，目的是按照每批1024行读取数据，并且一次性对整个记录整合（而不是对单条记录）应用操作，提升了想过滤，联合，聚合等等操作的性能。
主义：要使用矢量化查询执行，就必须以ORC格式存储数据。
--开启矢量化查询 set hive.vectorized.execution.enabled = true; set hive.vectorized.execution.reduce.enabled = true;
计算Job执行优化
Explain查询计划
explain会解析HQL语句，将整个HQL语句的实现逼走、依赖关系，实施过程都会进行解析返回，可以了解一条HQL语句在底层如何实现数据的查询及处理的过程，辅助用户对Hive进行优化。
常用语法命令如下
EXPLSIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION] query
- FORMATTED：对执行计划进行格式化，返回JSON格式的执行计划
- EXTENDE:提供一些额外的信息，比如文件的路径信息
- DEPENDENCY:以JSON格式返回查询所依赖的表 和分区的列表
- AUTHORIZATION:列出需要被授权的条目，包括输入与输出
每个查询计划由一下几个部分组成
- The Abstract Syntax Tree for the query
- 抽象语法树：Hive使用Antlr解析生成器，可以自动的将HQL生成抽象语法树
- The dependencies between the different stages of the plan
- Stage依赖关系：会列出运行查询划分的stage阶段以及之间的依赖关系
- The description of each of the stages
- Stage内容：包含了每个stage非常重要的信息，比如运行时的operator和sort orders等具体的信息
代码实例
--a.explain执行计划 explain extended select * from tb_emp02;explain select count(*) as cnt from tb_emp01 where deptno = '10';[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PVx6Dxxa-1666068736597)(C:\Users\康登云\AppData\Roaming\Typora\typora-user-images\image-20221014150229345.png)]
MapReduce属性优化
本地模式
- 说你Hive的过程中，有一些数据量大的表也会转化为MapReduce处理，提交到集群时，需要申请资源，等待资源分配，启动JVM进程，在运行Task，一系列的过程比较繁琐，本身数据量并不大，提交到YARN运行返回会导致性能较差的问题。
- Hive为了解决这个问题，使用了MapReduce的设计，提供本地计算模式，允许程序不提交给YARN，字节本地运行，以便于提高小数据量程序的性能。。
本地模式的配置
set hive.exec.mode.local.auto=true;JVM重用
- Hadoop默认会为每个Task启动一个JVM来运行，而在JVM启动时内存开销大；
- job数据量大的情况，如果单个Task数据量比较小，也会申请JVM，这就导致了资源经常及浪费的情况；
- JVM重用可以使得JVM实例在同一个job中重新使用N次，当一个task运行结束以后，JVM不会进行释放，而是继续供下一个Task运行，直到运行了N个Task以后，就会释放
- N的值可以在Hadoop的mapred-site.xml文件中进行配置，通常在10-20之间。
--Hadoop3之前的配置，在mapred-site.xml中添加一下参数 --Hadoop3中已经不在支持该选项了 mapreduce.job.jvm.numtasks=10并行执行
Hive在实现HQL计算运行时，会解析多个STAGE,有时候Stage彼此之间有依赖关系，只能挨个执行，但是又一些别的场景下，很多的Stage之间是没有依赖关系的；
例如Union语句，join语句等等，这些Stage没有依赖关系，但是Hive依旧默认挨个执行每个Stage,这样会导致性能非常差，我们可以通过修改参数，开启并行执行，当国歌Stage之间没有依赖关系时，允许多个Stage并行执行，提高性能。
--开启Stage并行化，默认false set hive.exec.paralle=true; --指定并行化县城数，默认为8 set hive.exec.parallel.thread.number=16;
Join优化
Map Join
应用场景
适合于小表Join大表或者小表Join小表
原理
将下的那份数据给每个MapTask的内存都放一份完整的数据，大的数据每个部分都可以与小数据的完整数据进行join
底层不需要经过shuffle，需要占用内存空间存放小的数据文件
使用
尽量使用Map Join来实现Join过程，Hive中默认自动开启了Map Join：hive.auto.convert.join=true
Hive中小表的大小限制
--2.0版本之前的控制属性 hive.map.join.smalltable.filesize=25M --2.0版本开始有一下参数控制 hive.auto.convert.join.noconditionaltask.size=512000000
Reduce Join
- 应用场景
- 适合大表Join大表
- 原理
- 将两张表的数据在shuffle阶段利用shuffle的分组来讲数据按照关键字段进行合并
- 必须经过shuffle，利用shuffle过程中的分组来实现关联
- 使用
- Hive会自动判断是否满足Map Join，如果不满足Map Join,则会自动执行Reduce Join
Bucket Join
应用场景
- 适合于大表Join大表
原理
- 将两张表按照相同的规则数据划分根据对应的规则的数据进行join减少比较次数，提高性能
使用Bucket Join
- 语法：clustered by colName
- 参数：set hive.optimize.bucketmap.join = true;
- 要求：风筒字段 = join字段，桶的个数相等或者成倍数
使用Sort Merge Bucket Join（SMB）
基于有序的数据Join
语法：clustered by colName sorted by (colName)
参数
set hive.optimize.bucketmapjoin = true; set hive.auto.convert.sortmerge.join=true; set hive.optimize.bucketmapjoin.sortedmerge = true; set hive.auto.convert.sortmerge.join.noconditionaltask=true;要求：分桶字段 = Join字段 = 排序字段 ，桶的数相等或者成倍数
优化器-关联优化
当一个程序中如果有一些操作彼此之间有关联性，是可以在一个MapReduce中实现的，但是Hive会不智能的选择，Hive会使用两个MapReduce来万恒这两个操作。
例如：当我们执行 select … from table group by id order by id desc 。该Sql语句转换为MapReducce时，我们可以有两种方案来实现：
方案一
第一个MapReduce做group by，经过shuffle阶段对id做分组
第二个MapReduce对第一个MapReduce的结果做order by，经过shuffle阶段对id进行排序
方案二
因为都是对id处理，可以使用一个MapReduce的shuffle既可以做分组也可以排序
在这种场景下，Hive会默认选择用第一种方案来实现，这样会导致性能相对较差；
可以在Hive中开启关联优化，对有关联关系的操作进行解析时，可以尽量放在同一个MapReduce中实现。
配置
set hive.optimize.correlation=true;大白话说：就是有些SQL语句中的group by和order by处理的都是同样的字段，默认情况下，hive会开启两个MapReduce,这样会导致性能相对较差，可以通过设置生面配置来实现一个开启一个MapReduce
优化器引擎-背景
- hive 默认的优化器在解析一些聚合统计类的处理时，底层解析的方案有时候不是最佳的方案。
- 例如当前有一张表【共10000条数据】，id构建了索引，id=100值有900条
- 需求：查询所有id=100的数据，sql语句为：select * from tbale where id = 100;
- 方案一
- 由于id这一列构建了索引，索引默认的优化器引擎RBO，会选择从索引中查询id = 100 的值所在的位置，在根据索引记录位置取读取对应的数据，但是这并不是最佳的执行方案。
- 方案二
- 有id=100的值有900条，占了总数据90%，这时候是没有必要检索索引以后检索数据的，可以字节检索索引数据返回，这样的效率会更高，更节省资源，这中方式就是CBO优化器引擎会选择的方案。
优化器引擎–CBO优化器
RBO
rule basic optimise:基于规则的优化器，根据设定号的规则来对程序进行优化（这个是比较死板的优化器，也是系统默认的优化器）
CBO
cost basic optimise:基于代价的优化器，根据不同场景说需要付出的代价来合适选择优化的方案对数据的分布的信息【数值出现的次数，条数，分布】来综合判断用哪种处理的方案是最佳方案
Hive中支持RBD与CBO这两种引擎，默认使用的是RBD优化器引擎。
很明显CBO引擎更加智能，所以在使用Hive时，我们可以配置底层的优化器引擎。
set hive.cbo.enable=true; set hive.compute.query.using.stats=true; set hive.stats.fetch.column.stats=true;优化器-Analyze分析器
功能
用于提前运行一个MapReduce程序将表或者分区的信息构建一些元素【表的信息，分区信息，列的信息】，搭配CBO引擎一起使用
代码实例
--分析优化器 use tb_part; --构建表中分区数据的元数据信息 analyze table tb_login_part partition(logindate) compute statistics; --构建表中列的数据的元数据信息 analyze table tb_login_part compute statistics for columns userid; --查看构建的列的元数据 desc formatted tb_login_part userid;总结
当任务查询的字段较值较少是使用RBD优化器会更快，他会根据系统规则进行检索，比较死板，如果字段值较多的话使用CBO优化器会更快一些，他可以根据不同场景需要付出的代价来来选择合适的优化方案
谓词下推（PPD）
谓词：用来描述或判断客体性质，特征或者客体资金关系的词项，比如“3大于2”中”大于“是一个谓词。
谓词下推Predicate Pushdown(PPD)基本实现：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。简单点说就是在不影响最终结果的情况下，尽量将过滤条件提前执行。
Hive中谓词下推后，过滤条件会下推到map端，提前执行过滤，减少map到reduce的传输数据，提升整体性能。
开启参数【默认开启】
Hive.optimize.ppd=true;
- 例子
推荐形式1的方式，先过滤在join.
--形式1 select a.id,a.value1,b.value2 from table1 a join(select b.* from table2 b where b.ds >= '20181201'and b.ds<'20190101') c--形式2 select a.id,a.value1,b.value2 from table1 a join table b on a.id=b.id where b.ds >= '20181201' and b.ds <'20190101'
- 规则
- 对于join、Full outer join,条件写在on后面，还是where后面，性能上没有区别；
- 对于Left outer Join，右侧的表写在on后面，左侧的表写在where后面，性能上有提高；
- 对于Right outer Join,左侧的表写在on后面，右侧的表写在where后面，性能上有提高；
- 当条件分散在两个表时，谓词下推可按上述结论2和3自由组合。
数据倾斜
分布式计算中最常见的，最容易遇到问题就是数据倾斜；
数据倾斜的现象是，当提交运行一个程序时，这个程序的大多数的Task都已经运行结束了，只有某一个Task一直在运行，迟迟不能结束，导致整体的进度卡在99%或者100%，这时候就可以判断程序出现了数据倾斜的问题、
数据倾斜的原因：数据分配
数据倾斜-Group by、Count(distinct)
当程序中出现group by或者count(distinct)等分组聚合的场景时，根据MapReduce的Hash分区规则，肯定会出现倾斜的现象。
当程序中出现group by或者count（distinct）等分组聚合的场景时，如果数据本身时倾斜的，根据MapReduce的Hash分区规则，肯定会出现数据倾斜的现象。
根本原因是因为分区规则导致的，所以可以通过一下几种方案来解决group by导致的数据倾斜的问题。
数据倾斜的解决方案
方案一：开启Map端聚合
hive.map.aggr=true;
通过减少shuffle数据量和Reducer阶段的执行时间，避免每个Task数据差异过大导致数据倾斜
方案二：实现随机分区
select * from table distribute by rand();
distribute by 用于指定底层按照那个字段作为Key实现分区，分组等
通过rank函数随机值实现随机分区，避免数据倾斜
方案三：数据倾斜时自动负载均衡
hive.groupby.skewindata=true;
开启该参数以后，当前程序会自动通过两个mapReduce来运行
第一个MapReduce自动进行随机分布到Reduce中，每个Reduce做部分聚合操作，输出结果
第二个MapReduce将上一步聚合的结果在按照业务（group by key）进行处理，保证相同的分布到一起，最终聚合得到结果。
数据倾斜-Join
- Join操作时，如果两张表比较大，无法实现Map,Join只能走Reduce Join ,那么当关键字段中某一种值过多的时候依旧会导致数据倾斜的问题；
- 面对Join产生的数据倾斜，核心的思想是尽量避免Reduce Join的产生，有限使用map Join来实现；
- 但往往很多的Join场景不满足MapJoin的需求，那么可以一下几种方案来解决Join常设的数据倾斜问题：
方案一：提前过滤，将大数据变成表数据，实现Map Join
select a.id,a.value1,b.value2 from table1 a join(select b.* from table2 b where b.ds >= '20181201' and b.ds <'20190101') c方案二：使用bucket Join
- 如果使用方案一，过滤后的数据依旧是一张大表，那么最后的Join依旧是一个Reduce Join
- 这种场景下，可以将两张表的数据构建为桶表，实现Bucket Map join,避免数据倾斜
方案三：使用Skew Join
- Skew Join是hvie中一种专门为了避免数据倾斜而设计的特殊的Join过程
- 这种Join的原理是将map Join和Reduce Join进行合并，如果某个值出现了数据倾斜，救护将产生的数据倾斜的数据单独使用Map Join来实现
- 其他没有产生数据倾斜的数据由Reduce Join来实现，这样就避免了Reduce Join中产生数据倾斜的问题
- 最终将Map Join的结果和Reduce Join的结果进行Union合并
方案三：使用Skew join
配置
–开启运行过程中skewjoin
set hive.optimize.skewjoin=true;
–如果这个key的出现的次数超过这个范围
set hive.skewjoin.key=100000;
–在变异时判断是否会产生数据倾斜
set hive.optimize.skewjoin.compiletime=true;
–不合并，提升性能
set hive.optimize.union.remove=true;
–如果Hive的底层走的是MapReduce,必须开启这个属性，才能实现不合并
set mapreduce.input.fileinputformat.input.dir.recursive=true;
Hiive新特性（了解）
Hive执行引擎
- Hive从2.x版本开始就提示不推荐使用MR，未来的版本可能不能使用了，推荐使用Tez或者Spark引擎来代替MapReduce计算。如果依旧要使用MapReduce，需要使用Hive的1.x版本
- 通过实测发现，当下Hive3.1.2版本默认引擎依然是MapReduce.
- 在实际使用Hive的过程中，建议将Hive的底层计算引擎更改为Tez或者Spark引擎。
Hive on Tez
- Tez是Apache社区中的一种支持DAG作业的开源计算框架；
- 可以将多个有依赖的作业转换为一个作业从而大幅度提升DAG作业的性能，最终Tez也会将程序提交给Yarn来实现运行。
- Tez并不直接面向制作用户，事实上它允许开发者为最终用户构建性能更快，扩展性更好的应用程序。
Tez特点
- 灵活的数据流定义
- 灵活的输入，输出，运行时模型
- 与数据类型无关
- 部署方便简洁
- 高性能执行：
- 最佳资源管理
- 计划配置动态更新
- 动态物理数据流决策
LLAP更新（了解）
LLAP是Hive2.0版本就引入的特性，在Hive 3中与Tez集成应用更加成熟；
Hive官方称之为实时长期处理，实现将数据预取，缓存到基于yarn运动的守护进程中，降低和减少系统IO和与HDFS DataNode的交互，以提高程序的性能，LLAP目前只支持tez引擎。
LLAP提供了一种混合执行模型。它有一个长期存在的守护进程（该守护进程替换了与HDFS DataNode的直接交互）以及一个紧密集成的基于DAG的框架组成。诸如缓存，预取，某些查询处理和访问控制之类的功能已移至守护程序中。
此守护程序直接直接处理小/短查询，而任何繁重的工作都将标准Yarn容器中执行。
LLAP在现有的Hive基于流程的执行中工作，以保持Hive的可伸缩性和多功能性。它不会替代现有的执行模型，而是对其进行了增强。它有如下几个特点：
LLAP守护程序时可选的
Hive可以在没有LLAP的情况下工作，并且及时已经部署并启动运行LLAP也可以绕过不执行。
LLAP不是执行引擎
不同于MapReduce或tez,整个执行有现有的Hive执行引擎在LLAP节点以及常规容器上透明的调度和监控。显然，LLAP的支持级别取决于单独的执行引擎，不计划支持MapReduce，但以后可能会添加其他引擎，例如pig等其他框架也可以选择使用LLAP守护程序。
部分执行
LLAP守护程序执行的工作可以构成Hive查询结果的一部分
资源Management
Yarn仍然负责资源的管理和分配。
Metastore独立模式
- hive中所有对象如数据库，表，函数等，他们定义都叫做metadata元数据。
- metastore是元数据存储服务，用于操作访问元数据
- Hive或者其他执行引擎在运行时这些元数据来决定如何解析，授权和有效的执行用户查询。
- metastore元数据可以存储配置为嵌入式的Apache Derby Rdbms或连接到外部rdbms
- metastore本身可以完全嵌入到用户进程中，也可以作为服务运行，供其他进程连接
总结
建表语法解释
students.txt数据
95001,李勇,男,20,CS 95002,刘晨,女,19,IS 95003,王敏,女,22,MA 95004,张立,男,19,IS 95005,刘刚,男,18,MA 95006,孙庆,男,23,CS 95007,易思玲,女,19,MA 95008,李娜,女,18,CS 95009,梦圆圆,女,18,MA 95010,孔小涛,男,19,CS 95011,包小柏,男,18,MA 95012,孙花,女,20,CS 95013,冯伟,男,21,CS 95014,王小丽,\N,19,CS 95015,王君,男,18,MA 95016,钱国,男,21,MA 95017,王风娟,女,18,IS 95018,王一,女,19,IS 95019,邢小丽,女,19,IS 95020,赵钱,男,21,IS 95021,周二,男,17,MA 95022,郑明,男,20,MA建表语句
create database student location '/student';--删除原来有的 student表; drop table if exists students; --创建一张普通表 create external table if not exists students ( --判断这张表是否不存在，不存在就创建一个外部表number bigint comment '学号', --指定字段名，类型，注释name string comment '姓名',sex string comment '性别',age int comment '年龄',dept string comment '部门' ) comment '学生表' --给表添加了注解row format delimitedfields terminated by ',' --指定字段以','号分割collection items terminated by ':' --指定集合每个元素之间使用：分割map keys terminated by '_' --指定map键值对使用_分割stored as textfile --指定存储的文件格式location '/waibubiao/student'; --指定存储文件的路径 --加载数据 load data local inpath '/root/file/students.txt' into table students; --查询表中数据是否创建成功 select * from students;--将普通数据以dept分区以sex分为2桶 create external table if not exists students2 ( --判断这张表是否不存在，不存在就创建一个外部表number bigint comment '学号', --指定字段名，类型，注释name string comment '姓名',sex string comment '性别',age int comment '年龄' ) comment '学生表' --给表添加了注解partitioned by (dept string) --使用字段dept进行创建分区表clustered by (sex) sorted by (age desc) into 2 buckets --按照sex进行分桶，按age降序，分为两桶row format delimitedfields terminated by ',' --指定字段以','号分割collection items terminated by ':' --指定集合每个元素之间使用：分割map keys terminated by '_' --指定map键值对使用_分割stored as textfile --指定存储的文件格式location '/waibubiao/student'; --指定存储文件的路径 insert into students2 select * from students;select * from students2;
ash分区规则，肯定会出现倾斜的现象。
- 当程序中出现group by或者count（distinct）等分组聚合的场景时，如果数据本身时倾斜的，根据MapReduce的Hash分区规则，肯定会出现数据倾斜的现象。
- 根本原因是因为分区规则导致的，所以可以通过一下几种方案来解决group by导致的数据倾斜的问题。
数据倾斜的解决方案
方案一：开启Map端聚合
hive.map.aggr=true;
通过减少shuffle数据量和Reducer阶段的执行时间，避免每个Task数据差异过大导致数据倾斜
方案二：实现随机分区
select * from table distribute by rand();
distribute by 用于指定底层按照那个字段作为Key实现分区，分组等
通过rank函数随机值实现随机分区，避免数据倾斜
方案三：数据倾斜时自动负载均衡
hive.groupby.skewindata=true;
开启该参数以后，当前程序会自动通过两个mapReduce来运行
第一个MapReduce自动进行随机分布到Reduce中，每个Reduce做部分聚合操作，输出结果
第二个MapReduce将上一步聚合的结果在按照业务（group by key）进行处理，保证相同的分布到一起，最终聚合得到结果。
数据倾斜-Join
- Join操作时，如果两张表比较大，无法实现Map,Join只能走Reduce Join ,那么当关键字段中某一种值过多的时候依旧会导致数据倾斜的问题；
- 面对Join产生的数据倾斜，核心的思想是尽量避免Reduce Join的产生，有限使用map Join来实现；
- 但往往很多的Join场景不满足MapJoin的需求，那么可以一下几种方案来解决Join常设的数据倾斜问题：
方案一：提前过滤，将大数据变成表数据，实现Map Join
select a.id,a.value1,b.value2 from table1 a join(select b.* from table2 b where b.ds >= '20181201' and b.ds <'20190101') c方案二：使用bucket Join
- 如果使用方案一，过滤后的数据依旧是一张大表，那么最后的Join依旧是一个Reduce Join
- 这种场景下，可以将两张表的数据构建为桶表，实现Bucket Map join,避免数据倾斜
方案三：使用Skew Join
- Skew Join是hvie中一种专门为了避免数据倾斜而设计的特殊的Join过程
- 这种Join的原理是将map Join和Reduce Join进行合并，如果某个值出现了数据倾斜，救护将产生的数据倾斜的数据单独使用Map Join来实现
- 其他没有产生数据倾斜的数据由Reduce Join来实现，这样就避免了Reduce Join中产生数据倾斜的问题
- 最终将Map Join的结果和Reduce Join的结果进行Union合并
方案三：使用Skew join
配置
–开启运行过程中skewjoin
set hive.optimize.skewjoin=true;
–如果这个key的出现的次数超过这个范围
set hive.skewjoin.key=100000;
–在变异时判断是否会产生数据倾斜
set hive.optimize.skewjoin.compiletime=true;
–不合并，提升性能
set hive.optimize.union.remove=true;
–如果Hive的底层走的是MapReduce,必须开启这个属性，才能实现不合并
set mapreduce.input.fileinputformat.input.dir.recursive=true;
Hiive新特性（了解）
Hive执行引擎
- Hive从2.x版本开始就提示不推荐使用MR，未来的版本可能不能使用了，推荐使用Tez或者Spark引擎来代替MapReduce计算。如果依旧要使用MapReduce，需要使用Hive的1.x版本
- 通过实测发现，当下Hive3.1.2版本默认引擎依然是MapReduce.
- 在实际使用Hive的过程中，建议将Hive的底层计算引擎更改为Tez或者Spark引擎。
Hive on Tez
- Tez是Apache社区中的一种支持DAG作业的开源计算框架；
- 可以将多个有依赖的作业转换为一个作业从而大幅度提升DAG作业的性能，最终Tez也会将程序提交给Yarn来实现运行。
- Tez并不直接面向制作用户，事实上它允许开发者为最终用户构建性能更快，扩展性更好的应用程序。
Tez特点
- 灵活的数据流定义
- 灵活的输入，输出，运行时模型
- 与数据类型无关
- 部署方便简洁
- 高性能执行：
- 最佳资源管理
- 计划配置动态更新
- 动态物理数据流决策
LLAP更新（了解）
LLAP是Hive2.0版本就引入的特性，在Hive 3中与Tez集成应用更加成熟；
Hive官方称之为实时长期处理，实现将数据预取，缓存到基于yarn运动的守护进程中，降低和减少系统IO和与HDFS DataNode的交互，以提高程序的性能，LLAP目前只支持tez引擎。
LLAP提供了一种混合执行模型。它有一个长期存在的守护进程（该守护进程替换了与HDFS DataNode的直接交互）以及一个紧密集成的基于DAG的框架组成。诸如缓存，预取，某些查询处理和访问控制之类的功能已移至守护程序中。
此守护程序直接直接处理小/短查询，而任何繁重的工作都将标准Yarn容器中执行。
LLAP在现有的Hive基于流程的执行中工作，以保持Hive的可伸缩性和多功能性。它不会替代现有的执行模型，而是对其进行了增强。它有如下几个特点：
LLAP守护程序时可选的
Hive可以在没有LLAP的情况下工作，并且及时已经部署并启动运行LLAP也可以绕过不执行。
LLAP不是执行引擎
不同于MapReduce或tez,整个执行有现有的Hive执行引擎在LLAP节点以及常规容器上透明的调度和监控。显然，LLAP的支持级别取决于单独的执行引擎，不计划支持MapReduce，但以后可能会添加其他引擎，例如pig等其他框架也可以选择使用LLAP守护程序。
部分执行
LLAP守护程序执行的工作可以构成Hive查询结果的一部分
资源Management
Yarn仍然负责资源的管理和分配。
Metastore独立模式
- hive中所有对象如数据库，表，函数等，他们定义都叫做metadata元数据。
- metastore是元数据存储服务，用于操作访问元数据
- Hive或者其他执行引擎在运行时这些元数据来决定如何解析，授权和有效的执行用户查询。
- metastore元数据可以存储配置为嵌入式的Apache Derby Rdbms或连接到外部rdbms
- metastore本身可以完全嵌入到用户进程中，也可以作为服务运行，供其他进程连接
总结
建表语法解释
students.txt数据
95001,李勇,男,20,CS 95002,刘晨,女,19,IS 95003,王敏,女,22,MA 95004,张立,男,19,IS 95005,刘刚,男,18,MA 95006,孙庆,男,23,CS 95007,易思玲,女,19,MA 95008,李娜,女,18,CS 95009,梦圆圆,女,18,MA 95010,孔小涛,男,19,CS 95011,包小柏,男,18,MA 95012,孙花,女,20,CS 95013,冯伟,男,21,CS 95014,王小丽,\N,19,CS 95015,王君,男,18,MA 95016,钱国,男,21,MA 95017,王风娟,女,18,IS 95018,王一,女,19,IS 95019,邢小丽,女,19,IS 95020,赵钱,男,21,IS 95021,周二,男,17,MA 95022,郑明,男,20,MA建表语句
create database student location '/student';--删除原来有的 student表; drop table if exists students; --创建一张普通表 create external table if not exists students ( --判断这张表是否不存在，不存在就创建一个外部表number bigint comment '学号', --指定字段名，类型，注释name string comment '姓名',sex string comment '性别',age int comment '年龄',dept string comment '部门' ) comment '学生表' --给表添加了注解row format delimitedfields terminated by ',' --指定字段以','号分割collection items terminated by ':' --指定集合每个元素之间使用：分割map keys terminated by '_' --指定map键值对使用_分割stored as textfile --指定存储的文件格式location '/waibubiao/student'; --指定存储文件的路径 --加载数据 load data local inpath '/root/file/students.txt' into table students; --查询表中数据是否创建成功 select * from students;--将普通数据以dept分区以sex分为2桶 create external table if not exists students2 ( --判断这张表是否不存在，不存在就创建一个外部表number bigint comment '学号', --指定字段名，类型，注释name string comment '姓名',sex string comment '性别',age int comment '年龄' ) comment '学生表' --给表添加了注解partitioned by (dept string) --使用字段dept进行创建分区表clustered by (sex) sorted by (age desc) into 2 buckets --按照sex进行分桶，按age降序，分为两桶row format delimitedfields terminated by ',' --指定字段以','号分割collection items terminated by ':' --指定集合每个元素之间使用：分割map keys terminated by '_' --指定map键值对使用_分割stored as textfile --指定存储的文件格式location '/waibubiao/student'; --指定存储文件的路径 insert into students2 select * from students;select * from students2;
