外国优秀网站,php带数据库的网站,可以免费观看电视电影,打开网站乱码怎么做文章目录 一、Hive入门与安装1、Hive入门1.1 简介1.2 Hive架构原理 2、Hive安装2.1 安装地址2.2 Hive最小化安装(测试用)2.3 MySQL安装2.4 配置Hive元数据存储到MySQL2.5 Hive服务部署2.6 Hive服务启动脚本(了解) 3、Hive使用技巧3.1 Hive常用交互命令3.2 Hive参数配置方式3.3 … 文章目录 一、Hive入门与安装1、Hive入门1.1 简介1.2 Hive架构原理 2、Hive安装2.1 安装地址2.2 Hive最小化安装(测试用)2.3 MySQL安装2.4 配置Hive元数据存储到MySQL2.5 Hive服务部署2.6 Hive服务启动脚本(了解) 3、Hive使用技巧3.1 Hive常用交互命令3.2 Hive参数配置方式3.3 Hive常见属性配置 二、DDL\DML语句1、DDL(Data Definition Language)数据定义1.1 数据库1.2 表(table)创建1.3 表的增删查改 2、DML(Data Manipulation Language)数据操作2.1 Load2.2 Insert2.3 Export\Import 三、查询1、基础语法2、基本查询Select…From2.1 数据准备2.2 基本查询操作2.3 关系运算函数2.4 逻辑运算函数2.5 聚合函数 3、分组3.1 Group By语句3.2 Having语句 4、Join语句5、排序5.1 全局排序Order By5.2 每个Reduce内部排序Sort By5.3 分区Distribute By5.4 分区排序Cluster By 6、函数6.1 简介6.2 单行函数6.3 高级聚合函数6.4 炸裂函数6.5 窗口函数开窗函数 7、自定义函数7.1 介绍7.2 自定义UDF函数 四、分区、分桶表和文件1、分区表1.1 分区表基本语法1.2 二级分区表1.3 动态分区 2、分桶表2.1 概述2.2 分桶表基本语法2.3 分桶排序表 3、Hive文件格式3.1 Text File3.2 ORC3.3 parquet 4、压缩4.1 Hive表数据进行压缩4.2 计算过程中使用压缩 五、企业级调优1、计算资源配置1.1 Yarn资源配置1.2 MapReduce资源配置 2、测试用表3、Explain查看执行计划(重点)3.1 Explain执行计划概述3.2 基本用法 4、 HQL之分组聚合优化4.1 优化说明 5、HQL之Join优化5.1 Join算法概述5.2 Map Join5.3 Bucket Map Join5.4 Sort Merge Bucket Map Join 6、HQL之数据倾斜6.1 数据倾斜概述6.2 分组聚合导致的数据倾斜6.3 Join导致的数据倾斜 7、HQL之任务并行度7.1 Map端并行度7.2 Reduce端并行度 8、HQL之小文件合并8.1 Map端输入文件合并8.2 Reduce输出文件合并 9、其他优化9.1 CBO优化9.2 谓词下推9.3 矢量化查询9.4 Fetch抓取9.5 本地模式9.6 并行执行9.7 严格模式 六、实战案例1、同时在线人数问题2、会话划分问题3、间断连续登录用户问题4、日期交叉问题 一、Hive入门与安装
1、Hive入门
1.1 简介 Hive是由Facebook开源基于Hadoop的一个数据仓库工具可以将结构化的数据文件映射为一张表并提供类SQL查询功能 Hive是一个Hadoop客户端用于将HQLHive SQL转化成MapReduce程序
Hive中每张表的数据存储在HDFSHive分析数据底层的实现是MapReduce也可配置为Spark或者Tez 执行程序运行在Yarn上
1.2 Hive架构原理 用户接口Client
JDBC的移植性比ODBC好通常情况下安装完ODBC驱动程序之后还需要经过确定的配置才能够应用。而不相同的配置在不相同数据库服务器之间不能够通用。所以安装一次就需要再配置一次。JDBC只需要选取适当的JDBC数据库驱动程序就不需要额外的配置。在安装过程中JDBC数据库驱动程序会自己完成有关的配置。两者使用的语言不同JDBC在Java编程时使用ODBC一般在C/C编程时使用
元数据Metastore
元数据包括数据库默认是default、表名、表的拥有者、列/分区字段、表的类型是否是外部表、表的数据所在目录等。默认存储在自带的derby数据库中由于derby数据库只支持单客户端访问生产环境中为了多人开发推荐使用MySQL存储Metastore
驱动器Driver
解析器SQLParser将SQL字符串转换成抽象语法树AST语义分析Semantic Analyzer将AST进一步划分为QeuryBlock逻辑计划生成器Logical Plan Gen将语法树生成逻辑计划逻辑优化器Logical Optimizer对逻辑计划进行优化物理计划生成器Physical Plan Gen根据优化后的逻辑计划生成物理计划物理优化器Physical Optimizer对物理计划进行优化执行器Execution执行该计划得到查询结果并返回给客户端 2、Hive安装
2.1 安装地址
Hive官网地址http://hive.apache.org/
文档查看地址https://cwiki.apache.org/confluence/display/Hive/GettingStarted
下载地址http://archive.apache.org/dist/hive/
github地址https://github.com/apache/hive
2.2 Hive最小化安装(测试用)
# 本文章前请先学习hadoop
# myhadoop.sh start 首先开启集群hadoop
# jpsall
wget http://archive.apache.org/dist/hive/hive-3.1.3/apache-hive-3.1.3-bin.tar.gz
# 解压apache-hive-3.1.3-bin.tar.gz到/opt/module/目录下面
tar -zxvf apache-hive-3.1.3-bin.tar.gz -C /opt/module/
cd /opt/module/
mv /opt/module/apache-hive-3.1.3-bin/ /opt/module/hive
# 修改/etc/profile.d/my_env.sh添加环境变量
sudo vim /etc/profile.d/my_env.sh
#HIVE_HOME
export HIVE_HOME/opt/module/hive
export PATH$PATH:$HIVE_HOME/binsource /etc/profile.d/my_env.sh# 初始化元数据库默认是derby数据库
bin/schematool -dbType derby -initSchema# 这里启动可能会报错这是因为hadoop和hive的两个guava.jar版本不一致
# 需要将hadoop的高版本guava来代替hive中的jar包
mv lib/guava-19.0.jar lib/guava-19.0.jar.bak
cp /opt/ha/hadoop-3.1.3/share/hadoop/common/lib/guava-27.0-jre.jar /opt/module/hive/lib/guava-27.0-jre.jar# 启动Hive只能使用命令行
bin/hive
# 使用Hive
hive show databases;
hive show tables;
hive create table stu(id int, name string);
hive insert into stu values(1,ss);
hive select * from stu;
# Hive中的表在Hadoop中是目录Hive中的数据在Hadoop中是文件# 在Xshell窗口中开启另一个窗口开启Hive在/tmp/atguigu目录下监控hive.log文件
tail -f hive.log
# 原因在于Hive默认使用的元数据库为derby。derby数据库的特点是同一时间只允许一个客户端访问。如果多个Hive客户端同时访问就会报错
# 我们可以将Hive的元数据改为用MySQL存储MySQL支持多客户端同时访问# 首先退出hive客户端。然后在Hive的安装目录下将derby.log和metastore_db删除顺便将HDFS上目录删除
hive quit;
rm -rf derby.log metastore_db
# 删除HDFS中/user/hive/warehouse/stu中数据
hadoop fs -rm -r /user
2.3 MySQL安装
# 下载mysql,离线安装
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
# 解压MySQL安装包
mkdir /opt/software/mysql_lib
tar -xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar -C /opt/software/mysql_lib
# 卸载系统自带的mariadb
sudo rpm -qa | grep mariadb | xargs sudo rpm -e --nodeps
# 安装MySQL依赖
cd mysql_lib
sudo rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm# 安装mysql-client
sudo rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
# 安装mysql-server
sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm# 若出现以下错误sudo yum -y install libaio
# 启动MySQL
sudo systemctl start mysqld
# 查看MySQL密码
sudo cat /var/log/mysqld.log | grep password# 配置MySQL
# 配置主要是root用户 密码在任何主机上都能登录MySQL数据库
mysql -uroot -ppassword
# 设置复杂密码由于MySQL密码策略此密码必须足够复杂
mysql set passwordpassword(Qs23zs32);
# 更改MySQL密码策略
mysql set global validate_password_policy0;
mysql set global validate_password_length4;
# 设置简单好记的密码
set passwordpassword(123456);
# 进入MySQL库
use mysql
# 查询user表
select user, host from user;
# 修改user表把Host表内容修改为%
update user set host% where userroot;
flush privileges;
quit;# 这里有个数据库建模软件很好用http://www.ezdml.com/# 卸载MySQL说明
# 若因为安装失败或者其他原因MySQL需要卸载重装
# 通过/etc/my.cnf查看MySQL数据的存储位置
sudo cat /etc/my.cnf
# 去往/var/lib/mysql路径需要root权限
cd /var/lib/mysql
rm -rf *
# 卸载MySQL相关包
sudo rpm -qa | grep -i -E mysql
rpm -qa | grep -i -E mysql\|mariadb | xargs -n1 sudo rpm -e --nodeps
2.4 配置Hive元数据存储到MySQL
# 新建Hive元数据库
mysql -uroot -p123456
mysql create database metastore;
mysql quit;
# 将MySQL的JDBC驱动拷贝到Hive的lib目录下,驱动自行下载
cp /opt/software/mysql-connector-java-5.1.37.jar $HIVE_HOME/lib# 解决日志Jar包冲突进入/opt/module/hive/lib目录
mv log4j-slf4j-impl-2.10.0.jar log4j-slf4j-impl-2.10.0.jar.bak# 在$HIVE_HOME/conf目录下新建hive-site.xml文件
vim $HIVE_HOME/conf/hive-site.xml# 修改元数据库字符集
# Hive元数据库的字符集默认为Latin1由于其不支持中文字符故若建表语句中包含中文注释会出现乱码现象
# 修改Hive元数据库中存储注释的字段的字符集为utf-8进入metastore库jdbc也要变成utf-8
# 字段注释
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
# 表注释
alter table TABLE_PARAMS modify column PARAM_VALUE mediumtext character set utf8;
添加如下内容
?xml version1.0?
?xml-stylesheet typetext/xsl hrefconfiguration.xsl?configuration!-- jdbc连接的URL连接也要申明utf-8 --propertynamejavax.jdo.option.ConnectionURL/name!-- jdbc:mysql://hadoop102:3306/metastore?useSSLfalseamp;useUnicodetrueamp;characterEncodingUTF-8 --valuejdbc:mysql://hadoop102:3306/metastore?useSSLfalse/value/property!-- jdbc连接的Driver--propertynamejavax.jdo.option.ConnectionDriverName/namevaluecom.mysql.jdbc.Driver/value/property!-- jdbc连接的username--propertynamejavax.jdo.option.ConnectionUserName/namevalueroot/value/property!-- jdbc连接的password --propertynamejavax.jdo.option.ConnectionPassword/namevalue123456/value/property!-- Hive默认在HDFS的工作目录 --propertynamehive.metastore.warehouse.dir/namevalue/user/hive/warehouse/value/property!-- 下面可以选择不填 --!-- 关闭元数据校验 --propertynamehive.metastore.schema.verification/namevaluefalse/value/propertypropertynamehive.metastore.event.db.notification.api.auth/namevaluefalse/value/property!-- 一些打印信息 --propertynamehive.cli.print.header/namevaluetrue/value/propertypropertynamehive.cli.print.current.db/namevaluetrue/value/property
/configuration# 初始化Hive元数据库修改为采用MySQL存储元数据
bin/schematool -dbType mysql -initSchema -verbose# 启动Hive
bin/hive
hive show databases;
hive show tables;
hive create table stu(id int, name string);
hive insert into stu values(1,ss);
hive select * from stu;# 看MySQL中的元数据
mysql -uroot -p123456
mysql show databases;
mysql use metastore;
mysql show tables;
# 查看元数据库中存储的库信息
select * from DBS;
# 查看元数据库中存储的表信息
select * from TBLS;
# 查看元数据库中存储的表中列相关信息
select * from COLUMNS_V2;
2.5 Hive服务部署 Hive的hiveserver2服务的作用是提供jdbc/odbc接口为用户提供远程访问Hive数据的功能例如用户期望在个人电脑中访问远程服务中的Hive数据就需要用到Hiveserver2 在远程访问Hive数据时客户端并未直接访问Hadoop集群而是由Hivesever2代理访问。由于Hadoop集群中的数据具备访问权限控制所以此时需考虑一个问题那就是访问Hadoop集群的用户身份是谁是Hiveserver2的启动用户还是客户端的登录用户
答案是都有可能具体是谁由Hiveserver2的hive.server2.enable.doAs参数决定该参数的含义是是否启用Hiveserver2用户模拟的功能。若启用则Hiveserver2会模拟成客户端的登录用户去访问Hadoop集群的数据不启用则Hivesever2会直接使用启动用户访问Hadoop集群数据。模拟用户的功能默认是开启的。生产环境推荐开启用户模拟功能因为开启后才能保证各用户之间的权限隔离
# hivesever2的模拟用户功能依赖于Hadoop提供的proxy user代理用户功能只有Hadoop中的代理用户才能模拟其他用户的身份访问Hadoop集群。
# 因此需要将hiveserver2的启动用户设置为Hadoop的代理用户
# 修改配置文件core-site.xml然后记得分发三台机器
cd $HADOOP_HOME/etc/hadoop
vim core-site.xml
# 增加配置
!--配置所有节点的atguigu用户都可作为代理用户--
propertynamehadoop.proxyuser.atguigu.hosts/namevalue*/value
/property!--配置atguigu用户能够代理的用户组为任意组--
propertynamehadoop.proxyuser.atguigu.groups/namevalue*/value
/property!--配置atguigu用户能够代理的用户为任意用户--
propertynamehadoop.proxyuser.atguigu.users/namevalue*/value
/property
# 分发
xsync core-site.xml
# 重启hadoop
myhadoop.sh stop/start# Hive端配置
# 在hive-site.xml文件中添加如下配置信息在conf目录下
vim hive-site.xml
!-- 指定hiveserver2连接的host --
propertynamehive.server2.thrift.bind.host/namevaluehadoop102/value
/property!-- 指定hiveserver2连接的端口号 --
propertynamehive.server2.thrift.port/namevalue10000/value
/property# 启动hiveserver2
bin/hive --service hiveserver2
nohup bin/hive --service hiveserver2 /dev/null 21
# 使用命令行客户端beeline进行远程访问
./bin/beeline
bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu
# 另一种使用Datagrip图形化客户端进行远程访问# 若datagrip查询发现列的元数据信息不显示需要在hive-site.xml添加以下配置
propertynamemetastore.storage.schema.reader.impl/namevalueorg.apache.hadoop.hive.metastore.SerDeStorageSchemaReader/value
/property
接下来进行metastore部署
Hive的metastore服务的作用是为Hive CLI或者Hiveserver2提供元数据访问接口metastore有两种运行模式分别为嵌入式模式(之前默认就是嵌入式启动)和独立服务模式。
生产环境中不推荐使用嵌入式模式。因为其存在以下两个问题嵌入式模式下每个Hive CLI都需要直接连接元数据库当Hive CLI较多时数据库压力会比较大每个客户端都需要用户元数据库的读写权限元数据库的安全得不到很好的保证。
# 嵌入式模式
# 嵌入式模式下只需保证Hiveserver2和每个Hive CLI的配置文件hive-site.xml中包含连接元数据库所需要的以下参数即可
!-- jdbc连接的URL --
propertynamejavax.jdo.option.ConnectionURL/namevaluejdbc:mysql://hadoop102:3306/metastore?useSSLfalse/value
/property!-- jdbc连接的Driver--
propertynamejavax.jdo.option.ConnectionDriverName/namevaluecom.mysql.jdbc.Driver/value
/property!-- jdbc连接的username--
propertynamejavax.jdo.option.ConnectionUserName/namevalueroot/value
/property!-- jdbc连接的password(选) --
propertynamejavax.jdo.option.ConnectionPassword/namevalue123456/value
/property# 独立服务模式
scp -r /opt/module/hive/ hadoop103:/opt/module/
# 然后在hadoop102启动metastore服务
nohup hive --service metastore /dev/null 21
jps -ml# 然后来到103节点配置参数即使配置了嵌入式的也是访问这个没有就报错
!-- 指定metastore服务的地址 --
propertynamehive.metastore.uris/namevaluethrift://hadoop102:9083/value
/property
# 注意主机名需要改为metastore服务所在节点端口号无需修改metastore服务的默认端口就是9083
# 测试发现可以正常访问
bin/hive
2.6 Hive服务启动脚本(了解)
nohup放在命令开头表示不挂起也就是关闭终端进程也继续保持运行状态/dev/null是Linux文件系统中的一个文件被称为黑洞所有写入该文件的内容都会被自动丢弃21表示将错误重定向到标准输出上放在命令结尾表示后台运行
一般会组合使用nohup [xxx命令操作] file 21 表示将xxx命令运行的结果输出到file中并保持命令启动的进程在后台运行。
# 直接编写脚本来管理服务的启动和关闭
vim $HIVE_HOME/bin/hiveservices.sh#!/bin/bash
HIVE_LOG_DIR$HIVE_HOME/logs
if [ ! -d $HIVE_LOG_DIR ]
thenmkdir -p $HIVE_LOG_DIR
fi#检查进程是否运行正常参数1为进程名参数2为进程端口
function check_process()
{pid$(ps -ef 2/dev/null | grep -v grep | grep -i $1 | awk {print $2})ppid$(netstat -nltp 2/dev/null | grep $2 | awk {print $7} | cut -d / -f 1)echo $pid[[ $pid ~ $ppid ]] [ $ppid ] return 0 || return 1
}function hive_start()
{metapid$(check_process HiveMetastore 9083)cmdnohup hive --service metastore $HIVE_LOG_DIR/metastore.log 21 [ -z $metapid ] eval $cmd || echo Metastroe服务已启动server2pid$(check_process HiveServer2 10000)cmdnohup hive --service hiveserver2 $HIVE_LOG_DIR/hiveServer2.log 21 [ -z $server2pid ] eval $cmd || echo HiveServer2服务已启动
}function hive_stop()
{
metapid$(check_process HiveMetastore 9083)[ $metapid ] kill $metapid || echo Metastore服务未启动server2pid$(check_process HiveServer2 10000)[ $server2pid ] kill $server2pid || echo HiveServer2服务未启动
}case $1 in
start)hive_start;;
stop)hive_stop;;
restart)hive_stopsleep 2hive_start;;
status)check_process HiveMetastore 9083 /dev/null echo Metastore服务运行正常 || echo Metastore服务运行异常check_process HiveServer2 10000 /dev/null echo HiveServer2服务运行正常 || echo HiveServer2服务运行异常;;
*)echo Invalid Args!echo Usage: $(basename $0) start|stop|restart|status;;
esac# 添加执行权限
chmod x $HIVE_HOME/bin/hiveservices.sh
# 启动Hive后台服务
hiveservices.sh start
3、Hive使用技巧
3.1 Hive常用交互命令
hive -help
# “-e”不进入hive的交互窗口执行hql语句
hive -e select id from stu;
# “-f”执行脚本中的hql语句
# 在/opt/module/hive/下创建datas目录并在datas目录下创建hivef.sql文件
mkdir datas
vim hivef.sql
# 文件中写入正确的hql语句
select * from stu;
bin/hive -f /opt/module/hive/datas/hivef.sql
# 执行文件中的hql语句并将结果写入文件中
bin/hive -f /opt/module/hive/datas/hivef.sql /opt/module/hive/datas/hive_result.txt
3.2 Hive参数配置方式
# 查看当前所有的配置信息
hiveset;# 参数的配置三种方式
# 1、默认配置文件hive-default.xml
# 用户自定义配置文件hive-site.xml
# 注意用户自定义配置会覆盖默认配置。另外Hive也会读入Hadoop的配置因为Hive是作为Hadoop的客户端启动的Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效# 2、命令行参数方式
# 启动Hive时可以在命令行添加-hiveconf paramvalue来设定参数
# 注意仅对本次Hive启动有效
bin/hive -hiveconf mapreduce.job.reduces10;
# 查看参数设置
hive (default) set mapreduce.job.reduces;# 3、参数声明方式
# 以在HQL中使用SET关键字设定参数例如
hive(default) set mapreduce.job.reduces;# 上述三种设定方式的优先级依次递增。即配置文件 命令行参数 参数声明。
# 注意某些系统级的参数例如log4j相关的设定必须用前两种方式设定因为那些参数的读取在会话建立以前已经完成了
3.3 Hive常见属性配置
Hive客户端显示当前库和表头vim hive-site.xml
propertynamehive.cli.print.header/namevaluetrue/valuedescriptionWhether to print the names of the columns in query output./description
/property
propertynamehive.cli.print.current.db/namevaluetrue/valuedescriptionWhether to include the current database in the Hive prompt./description
/propertyHive运行日志路径配置Hive的log默认存放在/tmp/atguigu/hive.log目录下当前用户名下修改Hive的log存放日志到/opt/module/hive/logs
# 修改$HIVE_HOME/conf/hive-log4j2.properties.template文件名称为hive-log4j2.properties
mv hive-log4j2.properties.template hive-log4j2.properties
# 在hive-log4j2.properties文件中修改log存放位置
property.hive.log.dir/opt/module/hive/logs
Hive的JVM堆内存设置新版本的Hive启动的时候默认申请的JVM堆内存大小为256MJVM堆内存申请的太小导致后期开启本地模式执行复杂的SQL时经常会报错java.lang.OutOfMemoryError: Java heap space因此最好提前调整一下HADOOP_HEAPSIZE这个参数
# 修改$HIVE_HOME/conf下的hive-env.sh.template为hive-env.sh
mv hive-env.sh.template hive-env.sh
# 将hive-env.sh其中的参数 export HADOOP_HEAPSIZE修改为2048重启Hive。
# The heap size of the jvm stared by hive shell script can be controlled via:
export HADOOP_HEAPSIZE2048
关闭Hadoop虚拟内存检查在yarn-site.xml中关闭虚拟内存检查虚拟内存校验如果已经关闭了就不需要配了修改完后记得分发yarn-site.xml并重启yarn。vim /opt/module/hadoop-3.1.3/etc/hadoop/yarn-site.xml
propertynameyarn.nodemanager.vmem-check-enabled/namevaluefalse/value
/property二、DDLDML语句
1、DDL(Data Definition Language)数据定义
1.1 数据库
# 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_nameproperty_value, ...)];# 创建一个数据库不指定路径
# 注若不指定路径其默认路径为${hive.metastore.warehouse.dir}/database_name.db
hive (default) create database db_hive1;
hive (default) create database db_hive2 location /db_hive2;
hive (default) create database db_hive3 with dbproperties(create_date2022-11-18);# 查询数据库
# 注like通配表达式说明*表示任意个任意字符|表示或的关系
SHOW DATABASES [LIKE identifier_with_wildcards];
hive show databases like db_hive*;
# 查看数据库信息
DESCRIBE DATABASE [EXTENDED] db_name;
hive desc database db_hive3;
# 查看更多信息
hive desc database extended db_hive3;# 修改数据库
# 用户可以使用alter database命令修改数据库某些信息其中能够修改的信息包括dbproperties、location、owner user。
# 需要注意的是修改数据库location不会改变当前已有表的路径信息而只是改变后续创建的新表的默认的父目录
# 修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_nameproperty_value, ...);
# 修改location
ALTER DATABASE database_name SET LOCATION hdfs_path;
# 修改owner user
ALTER DATABASE database_name SET OWNER USER user_name;hive ALTER DATABASE db_hive3 SET DBPROPERTIES (create_date2022-11-20);# 删除数据库
# 注RESTRICT严格模式若数据库不为空则会删除失败默认为该模式。
# CASCADE级联模式若数据库不为空则会将库中的表一并删除
DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];hive drop database db_hive2;
hive drop database db_hive3 cascade;# 切换当前数据库
USE database_name;
1.2 表(table)创建
普通建表
# 创建表
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_nameproperty_value, ...)]# 可以预查看一些基本默认设置
show create table 表名
TEMPORARY临时表该表只在当前会话可见会话结束表会被删除EXTERNAL重点外部表与之相对应的是内部表管理表。管理表意味着Hive会完全接管该表包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据而不完全接管HDFS中的数据data_type重点Hive中的字段类型可分为基本数据类型和复杂数据类型 Hive说明定义t****inyint1byte有符号整数s****mallint2byte有符号整数i****nt4byte有符号整数b****igint8byte有符号整数b****oolean布尔类型true或者falsef****loat单精度浮点数d****ouble双精度浮点数decimal十进制精准数字类型decimal(16,2)varchar字符序列需指定最大长度最大长度的范围是[1,65535]varchar(32)string字符串无需指定最大长度t****imestamp时间类型b****inary二进制数据复杂数据类型如下类型说明定义--------------------------------------------------------------array数组是一组相同类型的值的集合arraystringmapmap是一组相同类型的键-值对集合mapstring, intstruct结构体由多个属性组成每个属性都有自己的属性名和数据类型structid:int, name:stringHive的基本数据类型可以做类型转换转换的方式包括隐式转换以及显示转换详情可参考Hive官方说明Allowed Implicit Conversions 任何整数类型都可以隐式地转换为一个范围更广的类型如tinyint可以转换成intint可以转换成bigint所有整数类型、float和string类型都可以隐式地转换成double。tinyint、smallint、int都可以转换为floatboolean类型不可以转换为任何其它的类型 # 显示转换可以借助cast函数完成显示的类型转换
# cast(expr as type)
hive (default) select 1 2, cast(1 as int) 2; PARTITIONED BY重点,创建分区表CLUSTERED BY … SORTED BY…INTO … BUCKETS重点,创建分桶表ROW FORMAT重点指定SERDESERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。详情可参考 Hive-Serde# 语法一DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割其会使用默认的SERDE对每行数据进行序列化和反序列化
ROW FORAMT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
[NULL DEFINED AS char]
# fields terminated by 列分隔符
# collection items terminated by map、struct和array中每个元素之间的分隔符
# map keys terminated by map中的key与value的分隔符
# lines terminated by 行分隔符
# [TBLPROPERTIES (property_nameproperty_value, ...)]
# [AS select_statement]
# null 一般在hdfs用/N表示# 语法二SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE可用于处理JSON字符串
ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_nameproperty_value,property_nameproperty_value, ...)] STORED AS重点,指定文件格式常用的文件格式有textfile默认值sequence fileorc file、parquet file等等xxxxxxxxxx # 进入解压后的Hadoop源码目录下#开始编译mvn clean package -DskipTests -Pdist,native -Dtar# 注意第一次编译需要下载很多依赖jar包编译时间会很久预计1小时左右最终成功是全部SUCCESS# 成功的64位hadoop包在/opt/module/hadoop_source/hadoop-3.1.3-src/hadoop-dist/target下bashTBLPROPERTIES用于配置表的一些KV键值对参数
Create Table As SelectCTAS建表
# 该语法允许用户利用select查询语句返回的结果直接建表表的结构和查询语句的结构保持一致且保证包含select查询语句放回的内容。
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_nameproperty_value, ...)]
[AS select_statement]Create Table Like语法
# 该语法允许用户复刻一张已经存在的表结构与上述的CTAS语法不同该语法创建出来的表中不包含数据
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[LIKE exist_table_name]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_nameproperty_value, ...)]
案例说明
# 内部表与外部表
# 内部表# Hive中默认创建的表都是的内部表有时也被称为管理表。对于内部表Hive会完全管理表的元数据和数据文件
create table if not exists student(id int, name string
)
row format delimited fields terminated by \t
location /user/hive/warehouse/student;# vim /opt/module/datas/student.txt插入数据
1001 student1
1002 student2
1003 student3
1004 student4
1005 student5
1006 student6
1007 student7
1008 student8hadoop fs -put student.txt /user/hive/warehouse/student
# 删除表发现数据也没了
hive (default) drop table student;# 外部表
# 外部表通常可用于处理其他工具上传的数据文件对于外部表Hive只负责管理元数据不负责管理HDFS中的数据文件
create external table if not exists student(id int, name string
)
row format delimited fields terminated by \t
location /user/hive/warehouse/student;# SERDE和复杂数据类型
# 若现有如下格式的JSON文件需要由Hive进行分析处理请考虑如何设计表
# 注以下内容为格式化之后的结果文件中每行数据为一个完整的JSON字符串
{name: dasongsong,friends: [bingbing,lili],students: {xiaohaihai: 18,xiaoyangyang: 16},address: {street: hui long guan,city: beijing,postal_code: 10010}
}
# 设计表
create table teacher
(name string,friends arraystring,students mapstring,int,address structcity:string,street:string,postal_code:int
)
row format serde org.apache.hadoop.hive.serde2.JsonSerDe
location /user/hive/warehouse/teacher;# 创建json然后上传注意不能有空格
vim /opt/module/datas/teacher.txt
{name:dasongsong,friends:[bingbing,lili],students:{xiaohaihai:18,xiaoyangyang:16},address:{street:hui long guan,city:beijing,postal_code:10010}}
# 上传
hadoop fs -put teacher.txt /user/hive/warehouse/teacher
# 尝试从复杂数据类型的字段中取值
select friends[0],students[xiaohaihai],address.city from teacher# create table as select和create table like
create table teacher1 as select * from teacher;
create table teacher2 like teacher;
1.3 表的增删查改
# 查看表
# 展示所有表
SHOW TABLES [IN database_name] LIKE [identifier_with_wildcards];
show tables like stu*;
# 查看表信息
DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name
# EXTENDED展示详细信息
# FORMATTED对详细信息进行格式化的展示
desc stu;
desc formatted stu;# 修改表
# 重命名表
ALTER TABLE table_name RENAME TO new_table_name
alter table stu rename to stu1;
# 修改列信息
# 增加列,该语句允许用户增加新的列新增列的位置位于末尾
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
# 更新列,该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
# 替换列,该语句允许用户用新的列集替换表中原有的全部列
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
# 案例
desc stu;
alter table stu add columns(age int);
desc stu;
alter table stu change column age ages double;
alter table stu replace columns(id int, name string);# 删除表
DROP TABLE [IF EXISTS] table_name;
drop table stu;
# 清空表
# 注意truncate只能清空管理表不能删除外部表中数据
TRUNCATE [TABLE] table_name
truncate table student;
2、DML(Data Manipulation Language)数据操作
2.1 Load
Load语句可将文件导入到Hive表中关键字说明
local表示从本地加载数据到Hive表否则从HDFS加载数据到Hive表overwrite表示覆盖表中已有数据否则表示追加partition表示上传到指定分区若目标是分区表需指定分区
# 语法
LOAD DATA [LOCAL] INPATH filepath [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1val1, partcol2val2 ...)];# 实操案例
# 创建表
create table student(id int, name string
)
row format delimited fields terminated by \t;
# 加载本地文件到hive
load data local inpath /opt/module/datas/student.txt into table student;
# 加载HDFS文件到hive中上传文件到HDFS
hadoop fs -put /opt/module/datas/student.txt /user/atguigu
# 加载HDFS上数据导入完成后去HDFS上查看文件是否还存在?答案是不在了说明hdfs里面是元数据的修改
load data inpath /user/atguigu/student.txt into table student;
# 加载数据覆盖表中已有的数据
dfs -put /opt/module/datas/student.txt /user/atguigu;
load data inpath /user/atguigu/student.txt overwrite into table student;
2.2 Insert
LOAD DATA用于将外部数据直接加载到Hive表中而INSERT INTO用于将数据从一个表插入到另一个表中或将查询结果插入到表中并可以进行数据转换、过滤或聚合操作
# 将查询结果插入表中
INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1val1, partcol2val2 ...)] select_statement;
# INTO将结果追加到目标表
# OVERWRITE用结果覆盖原有数据# 案例
create table student1(id int, name string
)
row format delimited fields terminated by \t;
# 要跑MP
insert overwrite table student1
select id, name
from student;# 将给定Values插入表中
INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[val1], partcol2[val2] ...)] VALUES values_row [, values_row ...]
insert into table student1 values(1,wangwu),(2,zhaoliu);# 将查询结果写入目标路径
INSERT OVERWRITE [LOCAL] DIRECTORY directory [ROW FORMAT row_format] [STORED AS file_format] select_statement;
# 案例
insert overwrite local directory /opt/module/datas/student ROW FORMAT SERDE org.apache.hadoop.hive.serde2.JsonSerDe select id,name from student;
2.3 ExportImport
Export导出语句可将表的数据和元数据信息一并到处的HDFS路径Import可将Export导出的内容导入Hive表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移
--导出
EXPORT TABLE tablename TO export_target_path--导入
IMPORT [EXTERNAL] TABLE new_or_original_tablename FROM source_path [LOCATION import_target_path]# 案例
--导出
export table default.student to /user/hive/warehouse/export/student;
--导入
import table student2 from /user/hive/warehouse/export/student;
三、查询
1、基础语法 https://cwiki.apache.org/confluence/display/Hive/LanguageManualSelect 基本语法
SELECT [ALL | DISTINCT] select_expr, select_expr, ...FROM table_reference -- 从什么表查[WHERE where_condition] -- 过滤[GROUP BY col_list] -- 分组查询[HAVING col_list] -- 分组后过滤[ORDER BY col_list] -- 排序[CLUSTER BY col_list][DISTRIBUTE BY col_list] [SORT BY col_list]][LIMIT number] -- 限制输出的行数2、基本查询Select…From
2.1 数据准备
# 在/opt/module/hive/datas/路径上创建dept.txt文件并赋值如下内容
# 部门编号 部门名称 部门位置id
vim dept.txt10 行政部 1700
20 财务部 1800
30 教学部 1900
40 销售部 1700# 在/opt/module/hive/datas/路径上创建emp.txt文件
# hive中如果不指定默认null为/N
vim emp.txt
7369 张三 研发 800.00 30
7499 李四 财务 1600.00 20
7521 王五 行政 1250.00 10
7566 赵六 销售 2975.00 40
7654 侯七 研发 1250.00 30
7698 马八 研发 2850.00 30
7782 金九 \N 2450.0 30
7788 银十 行政 3000.00 10
7839 小芳 销售 5000.00 40
7844 小明 销售 1500.00 40
7876 小李 行政 1100.00 10
7900 小元 讲师 950.00 30
7902 小海 行政 3000.00 10
7934 小红明 讲师 1300.00 30# 创建部门表
create table if not exists dept(deptno int, -- 部门编号dname string, -- 部门名称loc int -- 部门位置
)
row format delimited fields terminated by \t;
# 创建员工表
create table if not exists emp(empno int, -- 员工编号ename string, -- 员工姓名job string, -- 员工岗位大数据工程师、前端工程师、java工程师sal double, -- 员工薪资deptno int -- 部门编号
)
row format delimited fields terminated by \t;# 导入数据
load data local inpath /opt/module/hive/datas/dept.txt into table dept;
load data local inpath /opt/module/hive/datas/emp.txt into table emp;
2.2 基本查询操作
# SQL 语言大小写不敏感
# 全表查询
select * from emp;
# 选择特定列查询
select empno, ename from emp;# 列别名
select ename AS name, deptno dn
from emp;# Limit语句
# 典型的查询会返回多行数据。limit子句用于限制返回的行数
select * from emp limit 5;
select * from emp limit 2,3;# Where语句,where子句中不能使用字段别名
select * from emp where sal 1000;
2.3 关系运算函数
操作符支持的数据类型描述AB基本数据类型如果A等于B则返回true反之返回falseAB基本数据类型如果A和B都为null或者都不为null则返回true如果只有一边为null返回falseAB, A!B基本数据类型A或者B为null则返回null如果A不等于B则返回true反之返回falseAB基本数据类型A或者B为null则返回null如果A小于B则返回true反之返回falseAB基本数据类型A或者B为null则返回null如果A小于等于B则返回true反之返回falseAB基本数据类型A或者B为null则返回null如果A大于B则返回true反之返回falseAB基本数据类型A或者B为null则返回null如果A大于等于B则返回true反之返回falseA [not] between B and C基本数据类型如果AB或者C任一为null则结果为null。如果A的值大于等于B而且小于或等于C则结果为true反之为false。如果使用not关键字则可达到相反的效果。A is null所有数据类型如果A等于null则返回true反之返回falseA is not null所有数据类型如果A不等于null则返回true反之返回falsein数值1数值2所有数据类型使用 in运算显示列表中的值A [not] like Bstring 类型B是一个SQL下的简单正则表达式也叫通配符模式如果A与其匹配的话则返回true反之返回false。B的表达式说明如下‘x%’表示A必须以字母‘x’开头‘%x’表示A必须以字母‘x’结尾而‘%x%’表示A包含有字母‘x’,可以位于开头结尾或者字符串中间。如果使用not关键字则可达到相反的效果。A rlike B, A regexp Bstring 类型B是基于java的正则表达式如果A与其匹配则返回true反之返回false。匹配使用的是JDK中的正则表达式接口实现的因为正则也依据其中的规则。例如正则表达式必须和整个字符串A相匹配而不是只需与其字符串匹配。
2.4 逻辑运算函数
# 基本语法and/or/not
select * from emp
where sal 1000 and deptno 30;select * from emp
where sal1000 or deptno30;select * from emp
where deptno not in(30, 20);2.5 聚合函数
count(*)表示统计所有行数包含null值count(某列)表示该列一共有多少行不包含null值max()求最大值不包含null除非所有值都是nullmin()求最小值不包含null除非所有值都是nullsum()求和不包含nullavg()求平均值不包含null
# 本地模式mapreduce都运行在一个进程内里面各个线程之前配的是yarn
# tail -500 /tmp/atguigu/hive.log 查看错误信息发现堆内存报错需要调大堆内存
# mv hive-env.sh.template hive-env.sh
# 然后重启
set mapreduce.framework.name local
# 求总行数count
# count(某字段)null值不会统计
select count(*) cnt from emp;
# MP执行流程如下图3、分组
3.1 Group By语句
Group By语句通常会和聚合函数一起使用按照一个或者多个列队结果进行分组然后对每个组执行聚合操作
# 计算emp表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal
from emp t
group by t.deptno;
# 计算emp每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal
from emp t
group by t.deptno, t.job;
3.2 Having语句
having与where不同点
where后面不能写分组聚合函数而having后面可以使用分组聚合函数having只用于group by分组统计语句
# 求每个部门的平均工资
select deptno, avg(sal)
from emp
group by deptno;
# 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) avg_sal
from emp
group by deptno
having avg_sal 2000;
4、Join语句
# 等值Join
# Hive支持通常的sql join语句但是只支持等值连接不支持非等值连接
# 根据员工表和部门表中的部门编号相等查询员工编号、员工名称和部门名称
select e.empno, e.ename, d.dname
from emp e
join dept d
on e.deptno d.deptno;# 内连接只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来
# 左外连接join操作符左边表中符合where子句的所有记录将会被返回
# 右外连接join操作符右边表中符合where子句的所有记录将会被返回
# 满外连接将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话那么就使用null值替代
select e.empno, e.ename, d.deptno
from emp e
full join dept d
on e.deptno d.deptno;
# 多表连接
# 大多数情况下Hive会对每对join连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l进行连接操作。
# 注意为什么不是表d和表l先进行连接操作呢这是因为Hive总是按照从左到右的顺序执行的。# 笛卡尔集
# 笛卡尔集会在下面条件下产生:省略连接条件;连接条件无效;所有表中的所有行互相连接# 联合union union all
# union和union all都是上下拼接sql的结果这点是和join有区别的join是左右关联union和union all是上下拼接。union去重union all不去重。
# union和union all在上下拼接sql结果时有两个要求两个sql的结果列的个数必须相同;两个sql的结果上下所对应列的类型必须一致
5、排序
5.1 全局排序Order By Order By全局排序只有一个Reduce ascascend升序默认descdescend降序
# 通常和limit配合使用
# 按照部门和工资升序排序
select ename, deptno, sal
from emp
order by deptno, sal;5.2 每个Reduce内部排序Sort By
Sort By对于大规模的数据集order by的效率非常低。在很多情况下并不需要全局排序此时可以使用Sort by。Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序对全局结果集来说不是排序。
# 设置reduce个数
set mapreduce.job.reduces3;
# 查看设置reduce个数
set mapreduce.job.reduces;
# 根据部门编号降序查看员工信息
select *
from emp
sort by deptno desc;# 将查询结果导入到文件中按照部门编号降序排序,可以查看每个文件按照顺序排序
insert overwrite local directory /opt/module/hive/datas/sortby-result select * from emp sort by deptno desc; 5.3 分区Distribute By
Distribute By在有些情况下我们需要控制某个特定行应该到哪个Reducer通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition自定义分区进行分区结合sort by使用。
对于distribute by进行测试一定要分配多reduce进行处理否则无法看到distribute by的效果
set mapreduce.job.reduces3;
# 先按照部门编号分区再按照员工编号薪资排序
insert overwrite local directory
/opt/module/hive/datas/distribute-result
select *
from emp
distribute by deptno
sort by sal desc;
distribute by的分区规则是根据分区字段的hash码与reduce的个数进行相除后余数相同的分到一个区Hive要求distribute by语句要写在sort by语句之前
注意演示完以后mapreduce.job.reduces的值要设置回-1否则下面分区or分桶表load跑MapReduce的时候会报错
5.4 分区排序Cluster By
当distribute by和sort by字段相同时可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序不能指定排序规则为asc或者desc
# 注意按照部门编号分区不一定就是固定死的数值可以是20号和30号部门分到一个分区里面去
select *
from emp
cluster by deptno;select *
from emp
distribute by deptno
sort by deptno;6、函数
6.1 简介 Hive会将常用的逻辑封装成函数给用户进行使用类似于Java中的函数文档参考https://cwiki.apache.org/confluence/display/Hive/LanguageManualUDF # 查看系统内置函数
show functions;
# 查看内置函数用法
desc function upper;
# 查看内置函数详细信息
desc function extended upper;
6.2 单行函数 单行函数按照功能可分为如下几类: 日期函数、字符串函数、集合函数、数学函数、流程控制函数等 # 数值函数
# round四舍五入
# ceil向上取整
# floor向下取整
select round(3.3); 3# 字符串函数
# substring截取字符串,返回字符串A从start位置到结尾的字符串
substring(string A, int start)
# substring(string A, int start, int len) ,返回字符串A从start位置开始长度为len的字符串
# replace 替换
# replace(string A, string B, string C),将字符串A中的子字符串B替换为C
# regexp_replace正则替换
# regexp_replace(string A, string B, string C),将字符串A中的符合java正则表达式B的部分替换为C。注意在有些情况下要使用转义字符
select regexp_replace(100-200, (\\d), num)
# regexp正则匹配若字符串符合正则表达式则返回true否则返回false。
select dfsaaaa regexp dfsa
# repeat重复字符串
# repeat(string A, int n)将字符串A重复n遍
select repeat(123, 3);
# split 字符串切割
# split(string str, string pat),按照正则表达式pat匹配到的内容分割str分割后的字符串以数组的形式返回
select split(a-b-c-d,-);
# nvl 替换null值
# nvl(A,B) ,若A的值不为null则返回A否则返回B
select nvl(null,1);
# concat 拼接字符串
# concat(string A, string B, string C, ……) 将A,B,C……等字符拼接为一个字符串
select concat(beijing,-,shanghai,-,shenzhen);
# concat_ws以指定分隔符拼接字符串或者字符串数组
# concat_ws(string A, string…| array(string)) 使用分隔符A拼接多个字符串或者一个数组的所有元素
select concat_ws(-,beijing,shanghai,shenzhen);
# get_json_object解析json字符串
# get_json_object(string json_string, string path) 解析json的字符串json_string返回path指定的内容。如果输入的json字符串无效那么返回NULL
select get_json_object([{name:大海海,sex:男,age:25},{name:小宋宋,sex:男,age:47}],$.[0].name);
# 获取json数组里面的数据
select get_json_object([{name:大海海,sex:男,age:25},{name:小宋宋,sex:男,age:47}],$.[0]);# 日期函数
# unix_timestamp返回当前或指定时间的时间戳前面是日期后面是指日期传进来的具体格式
select unix_timestamp(2022/08/08 08-08-08,yyyy/MM/dd HH-mm-ss);
# from_unixtime转化UNIX时间戳从 1970-01-01 00:00:00 UTC 到指定时间的秒数到当前时区的时间格式
select from_unixtime(1659946088);
# current_date当前日期
select current_date;
# current_timestamp当前的日期加时间并且精确的毫秒
select current_timestamp;
# month获取日期中的月
select month(2022-08-08 08:08:08);
# day获取日期中的日
select day(2022-08-08 08:08:08)
# hour获取日期中的小时
# datediff两个日期相差的天数结束日期减去开始日期的天数
select datediff(2021-08-08,2022-10-09);
# date_add日期加天数date_sub日期减天数
select date_add(2022-08-08,2);
# date_format:将标准日期解析成指定格式字符串
select date_format(2022-08-08,yyyy年-MM月-dd日)# 流程控制函数
# case when条件判断函数
# 语法一case when a then b [when c then d] * [else e] end
# 说明如果a为true则返回b如果c为true则返回d否则返回 e
select case when 12 then tom when 22 then mary else tim end from tableName;
# 语法二 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 from tableName; # if: 条件判断类似于Java中三元运算符
# 语法ifboolean testCondition, T valueTrue, T valueFalseOrNull
select if(10 5,正确,错误);# 集合函数
# size集合中元素的个数
select size(friends) from test;
# map创建map集合
# 语法map (key1, value1, key2, value2, …)
select map(xiaohai,1,dahai,2);
# map_keys 返回map中的key
select map_keys(map(xiaohai,1,dahai,2));
# map_values: 返回map中的value
select map_values(map(xiaohai,1,dahai,2));
# array_contains: 判断array中是否包含某个元素
select array_contains(array(a,b,c,d),a);
# sort_array将array中的元素排序
select sort_array(array(a,d,c));
# struct声明struct中的各属性
select struct(name,age,weight);
# named_struct声明struct的属性和值
select named_struct(name,xiaosong,age,18,weight,80);
6.3 高级聚合函数
# 普通聚合 count/sum
# collect_list 收集并形成list集合结果不去重
select sex,collect_list(job)
fromemployee
group by sex# collect_list不去重
# collect_set 收集并形成set集合结果去重
select sex,collect_set(job)
fromemployee
group by sex6.4 炸裂函数 UDTF (Table-GeneratingFunctions)接收一行数据输出一行或多行数据 # 使用语法
select explode(array(a,b,c)) as item;
select explode(map(a,1,b ,2,c,3)) as (key,value);# 还会返回索引
select posexplode(array(a,b,c)) as (pos,item);
# 返回特定的
select inline(array(named_struct(id,1, name, zs),named_struct(id,2, name, Is),named_struct(id,3, name, ww))) as (id, name);# Latera View通常与UDTF配合使用。
# Lateral View可以将UDTF应用到源表的每行数据将每行数据转换为一行或多行并将源表中每行的输出结果与该行连接起来形成一个虚拟表。
# 把其中一个列表分别输出
select id,name,hobbies,hobby from person lateral view explode(hobbies) tmp as hobby;6.5 窗口函数开窗函数 窗口函数能为每行数据划分—个窗口然后对窗口范围内的数据进行计算最后将计算结果返回给该行数据。https://cwiki.apache.org/confluence/display/Hive/LanguageManualWindowingAndAnalytics 窗口函数的语法中主要包括“窗口”和“函数”两部分。其中“窗口”用于定义计算范围“函数”用于定义计算逻辑。
select order_id,order_date,amount,函数(amount) over(窗口范围) total_amount from order_info;
# 按照功能常用窗口可划分为如下几类聚合函数、跨行取值函数、排名函数
# 绝大多数的聚合函数都可以配合窗口使用例如max(),min(),sum(),count(),avg()等。
# 需要确定order by因为MP会分成很多小任务窗口范围的定义分为两种类型一种是基于行的一种是基于值的
基于行示例:要求每行数据的窗口为上一行到当前行 基于值示例:要求每行数据的窗口为值位于当前值-1到当前值(关键词range) 窗口分区定义窗口范围时可以指定分区字段每个分区单独划分窗口 对于窗口缺省函数
partition by省略不写表示不分区order by 省略不写表示不排序(rows|range) between … and …省略不写则使用其默认值默认值如下:若over()中包含order by则默认值为range between unbounded preceding and current row若over()中不包含order by则默认值为rows between unbounded preceding and unbounded following
下面举例其他常用的窗口函数lead和lagfirst_value和last_value排名函数注lag和leadrank 、dense_rank、row_number函数不支持自定义窗口 # 案例实操
create table order_info
(order_id string, --订单iduser_id string, -- 用户iduser_name string, -- 用户姓名order_date string, -- 下单日期order_amount int -- 订单金额
);insert overwrite table order_info
values (1, 1001, 小元, 2022-01-01, 10),(2, 1002, 小海, 2022-01-02, 15),(3, 1001, 小元, 2022-02-03, 23),(4, 1002, 小海, 2022-01-04, 29),(5, 1001, 小元, 2022-01-05, 46),(6, 1001, 小元, 2022-04-06, 42),(7, 1002, 小海, 2022-01-07, 50),(8, 1001, 小元, 2022-01-08, 50),(9, 1003, 小辉, 2022-04-08, 62),(10, 1003, 小辉, 2022-04-09, 62),(11, 1004, 小猛, 2022-05-10, 12),(12, 1003, 小辉, 2022-04-11, 75),(13, 1004, 小猛, 2022-06-12, 80),(14, 1003, 小辉, 2022-04-13, 94);# 统计每个用户截至每次下单的累积下单总额
selectorder_id,user_id,user_name,order_date,order_amount,sum(order_amount) over(partition by user_id order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;# 统计每个用户截至每次下单的当月累积下单总额
selectorder_id,user_id,user_name,order_date,order_amount,sum(order_amount) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;# 统计每个用户每次下单距离上次下单相隔的天数首次下单按0天算
selectorder_id,user_id,user_name,order_date,order_amount,nvl(datediff(order_date,last_order_date),0) diff
from
(selectorder_id,user_id,user_name,order_date,order_amount,lag(order_date,1,null) over(partition by user_id order by order_date) last_order_datefrom order_info
)t1# 查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期
selectorder_id,user_id,user_name,order_date,order_amount,first_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date) first_date,last_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date
from order_info;# 为每个用户的所有下单记录按照订单金额进行排名
selectorder_id,user_id,user_name,order_date,order_amount,rank() over(partition by user_id order by order_amount desc) rk,dense_rank() over(partition by user_id order by order_amount desc) drk,row_number() over(partition by user_id order by order_amount desc) rn
from order_info;
7、自定义函数
7.1 介绍 官网文档地址https://cwiki.apache.org/confluence/display/Hive/HivePlugins Hive自带了一些函数比如max/min等但是数量有限自己可以通过自定义UDF来方便的扩展当Hive提供的内置函数无法满足你的业务处理需要时此时就可以考虑使用用户自定义函数UDFuser-defined function
根据用户自定义函数类别分为以下三种
UDFUser-Defined-Function一进一出UDAFUser-Defined Aggregation Function用户自定义聚合函数多进一出。类似于count/max/minUDTFUser-Defined Table-Generating Functions用户自定义表生成函数一进多出。如lateral view explode()
# 编程步骤如下
#1继承Hive提供的类
#org.apache.hadoop.hive.ql.udf.generic.GenericUDF
#org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
#2实现类中的抽象方法
#3在hive的命令行窗口创建函数
# 添加jar
add jar linux_jar_path
# 创建function。
create [temporary] function [dbname.]function_name AS class_name;
#4在hive的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;7.2 自定义UDF函数
需求自定义一个UDF实现计算给定基本数据类型的长度例如select my_len(abcd);首先创建一个Maven工程Hive导入依赖自定义UDTF也是同理
dependenciesdependencygroupIdorg.apache.hive/groupIdartifactIdhive-exec/artifactIdversion3.1.3/version/dependency
/dependencies创建类
/*** 我们需计算一个要给定基本数据类型的长度*/
public class MyUDF extends GenericUDF {/*** 判断传进来的参数的类型和长度* 约定返回的数据类型*/Overridepublic ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {if (arguments.length !1) {throw new UDFArgumentLengthException(please give me only one arg);}if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){throw new UDFArgumentTypeException(1, i need primitive type arg);}return PrimitiveObjectInspectorFactory.javaIntObjectInspector;}/*** 解决具体逻辑的*/Overridepublic Object evaluate(DeferredObject[] arguments) throws HiveException {Object o arguments[0].get();if(onull){return 0;}return o.toString().length();}Override// 用于获取解释的字符串public String getDisplayString(String[] children) {return ;}
}# 创建临时函数
# 打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
# 将jar包添加到hive的classpath临时生效
add jar /opt/module/hive/datas/myudf.jar;
# 创建临时函数与开发好的java class关联
create temporary function my_len as com.atguigu.hive.udf.MyUDF;
# 即可在hql中使用自定义的临时函数
select ename,my_len(ename) ename_len
from emp;
# 删除临时函数
drop temporary function my_len;
# 注意临时函数只跟会话有关系跟库没有关系。只要创建临时函数的会话不断在当前会话下任意一个库都可以使用其他会话全都不能使用# 创建永久函数
# 注意因为add jar本身也是临时生效所以在创建永久函数的时候需要制定路径并且因为元数据的原因这个路径还得是HDFS上的路径
create function my_len2 as com.atguigu.hive.udf.MyUDF using jar hdfs://hadoop102:8020/udf/myudf.jar;
# 即可在hql中使用自定义的永久函数
select ename,my_len2(ename) ename_len
from emp;
# 删除永久函数
drop function my_len2;
# 注意永久函数跟会话没有关系创建函数的会话断了以后其他会话也可以使用。
# 永久函数创建的时候在函数名之前需要自己加上库名如果不指定库名的话会默认把当前库的库名给加上。
# 永久函数使用的时候需要在指定的库里面操作或者在其他库里面使用的话加上库名.函数名。四、分区、分桶表和文件
1、分区表 Hive中的分区就是把一张大表的数据按照业务需要分散的存储到多个目录每个目录就称为该表的一个分区。在查询时通过where子句中的表达式选择查询所需要的分区这样的查询效率会提高很多 1.1 分区表基本语法
create table dept_partition
(deptno int, --部门编号dname string, --部门名称loc string --部门位置
)partitioned by (day string)
row format delimited fields terminated by \t;# 写入数据
# load写法
# 在/opt/module/hive/datas/路径上创建文件dept_20220401.log
vim dept_20220401.log
10 行政部 1700
20 财务部 1800
# 装载语句
load data local inpath /opt/module/hive/datas/dept_20220401.log into table dept_partition partition(day20220401);
# insert写法
# 将day20220401分区的数据插入到day20220402分区
insert overwrite table dept_partition partition (day 20220402)
select deptno, dname, loc
from dept_partition
where day 20220401;# 读数据
# 查询分区表数据时可以将分区字段看作表的伪列可像使用其他字段一样使用分区字段
select deptno, dname, loc ,day
from dept_partition
where day 20220401;# 分区表基本操作
# 查看所有分区信息
show partitions dept_partition;
# 增加分区
alter table dept_partition add partition(day20220403);
# 同时创建多个分区分区之间不能有逗号
alter table dept_partition add partition(day20220404) partition(day20220405);
# 删除分区单个分区
alter table dept_partition drop partition (day20220403);
# 同时删除多个分区分区之间必须有逗号
alter table dept_partition drop partition (day20220404), partition(day20220405);
修复分区
Hive将分区表的所有分区信息都保存在了元数据中只有元数据与HDFS上的分区路径一致时分区表才能正常读写数据。若用户手动创建/删除分区路径Hive都是感知不到的这样就会导致Hive的元数据和HDFS的分区路径不一致。再比如若分区表为外部表用户执行drop partition命令后分区元数据会被删除而HDFS的分区路径不会被删除同样会导致Hive的元数据和HDFS的分区路径不一致。
若出现元数据和HDFS路径不一致的情况可通过如下几种手段进行修复
add partition若手动创建HDFS的分区路径Hive无法识别可通过add partition命令增加分区元数据信息从而使元数据和分区路径保持一致drop partition若手动删除HDFS的分区路径Hive无法识别可通过drop partition命令删除分区元数据信息从而使元数据和分区路径保持一致msck若分区元数据和HDFS的分区路径不一致还可使用msck命令进行修复
msck repair table table_name [add/drop/sync partitions];
# msck repair table table_name add partitions该命令会增加HDFS路径存在但元数据缺失的分区信息
# msck repair table table_name drop partitions该命令会删除HDFS路径已经删除但元数据仍然存在的分区信息
# msck repair table table_name sync partitions该命令会同步HDFS路径和元数据分区信息相当于同时执行上述的两个命令
# msck repair table table_name等价于msck repair table table_name add partitions命令
1.2 二级分区表
# 如果一天内的日志数据量也很大如何再将数据拆分?答案是二级分区表例如可以在按天分区的基础上再对每天的数据按小时进行分区
# 二级分区表建表语句
create table dept_partition2(deptno int, -- 部门编号dname string, -- 部门名称loc string -- 部门位置
)
partitioned by (day string, hour string)
row format delimited fields terminated by \t;# 数据装载语句
load data local inpath /opt/module/hive/datas/dept_20220401.log
into table dept_partition2
partition(day20220401, hour12);# 查询分区数据
select *
from dept_partition2
where day20220401 and hour12;
1.3 动态分区
动态分区是指向分区表insert数据时被写往的分区不由用户指定而是由每行数据的最后一个字段的值来动态的决定。使用动态分区可只用一个insert语句将数据写入多个分区
# 动态分区功能总开关默认true开启
set hive.exec.dynamic.partitiontrue
# 严格模式和非严格模式
# 动态分区的模式默认strict严格模式要求必须指定至少一个分区为静态分区nonstrict非严格模式允许所有的分区字段都使用动态分区
set hive.exec.dynamic.partition.modenonstrict
# 一条insert语句可同时创建的最大的分区个数默认为1000
set hive.exec.max.dynamic.partitions1000
# 单个Mapper或者Reducer可同时创建的最大的分区个数默认为100
set hive.exec.max.dynamic.partitions.pernode100
# 一条insert语句可以创建的最大的文件个数默认100000
hive.exec.max.created.files100000
# 当查询结果为空时且进行动态分区时是否抛出异常默认false
hive.error.on.empty.partitionfalse# 案例
# 需求将dept表中的数据按照地区loc字段插入到目标表dept_partition_dynamic的相应分区中
# 创建目标分区表
create table dept_partition_dynamic(id int, name string
)
partitioned by (loc int)
row format delimited fields terminated by \t;
# 设置动态分区
set hive.exec.dynamic.partition.mode nonstrict;
insert into table dept_partition_dynamic
partition(loc)
select deptno, dname, loc
from dept;# 查看目标分区表的分区情况
show partitions dept_partition_dynamic;
2、分桶表
2.1 概述
分区提供一个隔离数据和优化查询的便利方式。不过并非所有的数据集都可形成合理的分区。对于一张表或者分区Hive 可以进一步组织成桶也就是更为细粒度的数据范围划分分区针对的是数据的存储路径分桶针对的是数据文件。
分桶表的基本原理是首先为每行数据计算一个指定字段的数据的hash值然后模以一个指定的分桶数最后将取模运算结果相同的行写入同一个文件中这个文件就称为一个分桶bucket
2.2 分桶表基本语法
# 建表语句
create table stu_buck(id int, name string
)
clustered by(id)
into 4 buckets
row format delimited fields terminated by \t;# 数据装载
# 在/opt/module/hive/datas/路径上创建student.txt文件
# 说明Hive新版本load数据可以直接跑MapReduce老版的Hive需要将数据传到一张表里再通过查询的方式导入到分桶表里面
load data local inpath /opt/module/hive/datas/student.txt into table stu_buck;
# 查看创建的分桶表中是否分成4个桶,即hdfs生成四个文件
2.3 分桶排序表
# 排序是指在分桶的基础上对每个桶中的数据按照某一列或多列进行排序。排序可以优化查询性能因为查询可以通过更有效地利用数据的有序性来加速执行。排序可以在创建表格时进行定义指定排序的列和排序顺序
# 建表语句
create table stu_buck_sort(id int, name string
)
clustered by(id) sorted by(id)
into 4 buckets
row format delimited fields terminated by \t;load data local inpath /opt/module/hive/datas/student.txt into table stu_buck_sort;
3、Hive文件格式 为Hive表中的数据选择一个合适的文件格式对提高查询性能的提高是十分有益的。Hive表数据的存储格式可以选择text file、orc、parquet、sequence file等 3.1 Text File
文本文件是Hive默认使用的文件格式文本文件中的一行内容就对应Hive表中的一行记录。可通过以下建表语句指定文件格式为文本文件:
create table textfile_table(column_specs) stored as textfile;3.2 ORC
ORCOptimized Row Columnarfile format是Hive 0.11版里引入的一种列式存储的文件格式。ORC文件能够提高Hive读写数据和处理数据的性能。与列式存储相对的是行式存 行存储的特点 查询满足条件的一整行数据的时候列存储则需要去每个聚集的字段找到对应的每个列的值行存储只需要找到其中一个值其余的值都在相邻地方所以此时行存储查询的速度更快。 列存储的特点 因为每个字段的数据聚集存储在查询只需要少数几个字段的时候能大大减少读取的数据量每个字段的数据类型一定是相同的列式存储可以针对性的设计更好的设计压缩算法
前文提到的text file和sequence file都是基于行存储的orc和parquet是基于列式存储的。orc文件的具体结构如下图所示 每个ORC文件由Header、Body和Tail三部分组成其中Header内容为ORC用于表示文件类型。Body由1个或多个stripe组成每个stripe一般为HDFS的块大小每一个stripe包含多条记录这些记录按照列进行独立存储每个stripe里有三部分组成分别是Index DataRow DataStripe Footer。
**Index Data**一个轻量级的index默认是为各列每隔1W行做一个索引。每个索引会记录第n万行的位置和最近一万行的最大值和最小值等信息**Row Data**存的是具体的数据按列进行存储并对每个列进行编码分成多个Stream来存储**Stripe Footer**存放的是各个Stream的位置以及各column的编码信息
Tail由File Footer和PostScript组成。File Footer中保存了各Stripe的其实位置、索引长度、数据长度等信息各Column的统计信息等PostScript记录了整个文件的压缩类型以及File Footer的长度信息等。
在读取ORC文件时会先从最后一个字节读取PostScript长度进而读取到PostScript从里面解析到File Footer长度进而读取FileFooter从中解析到各个Stripe信息再读各个Stripe即从后往前读。
# 建表语句
create table orc_table
(column_specs)
stored as orc
tblproperties (property_nameproperty_value, ...);
# ORC文件格式支持的参数如下参数默认值说明orc.compressZLIB压缩格式可选项NONE、ZLIB,、SNAPPY一般和SNAPPY配合orc.compress.size262,144每个压缩块的大小ORC文件是分块压缩的orc.stripe.size67,108,864每个stripe的大小orc.row.index.stride10,000索引步长每隔多少行数据建一条索引
3.3 parquet
Parquet文件是Hadoop生态中的一个通用的文件格式它也是一个列式存储的文件格式。Parquet文件的格式如下图所示 上图展示了一个Parquet文件的基本结构文件的首尾都是该文件的Magic Code用于校验它是否是一个Parquet文件。首尾中间由若干个Row Group和一个FooterFile Meta Data组成。每个Row Group包含多个Column Chunk每个Column Chunk包含多个Page。以下是Row Group、ColumnChunk和Page三个概念的说明
行组Row Group一个行组对应逻辑表中的若干行列块Column Chunk一个行组中的一列保存在一个列块中页Page一个列块的数据会划分为若干个页FooterFile Meta Data中存储了每个行组Row Group中的每个列快Column Chunk的元数据信息元数据信息包含了该列的数据类型、该列的编码方式、该类的Data Page位置等信息。
Create table parquet_table
(column_specs)
stored as parquet
tblproperties (property_nameproperty_value, ...);
参数默认值说明parquet.compressionuncompressed压缩格式可选项uncompressedsnappygziplzobrotlilz4parquet.block.size134217728行组大小通常与HDFS块大小保持一致parquet.page.size1048576页大小
4、压缩
hive的压缩和hadoop保持一致详情参考hadoop
压缩格式算法文件扩展名是否可切分DEFLATEDEFLATE.deflate否GzipDEFLATE.gz否bzip2bzip2.bz2是LZOLZO.lzo是SnappySnappy.snappy否
4.1 Hive表数据进行压缩 在Hive中不同文件类型的表声明数据压缩的方式是不同的 TextFile
若一张表的文件类型为TextFile若需要对该表中的数据进行压缩多数情况下无需在建表语句做出声明。直接将压缩后的文件导入到该表即可Hive在查询表中数据时可自动识别其压缩格式进行解压。需要注意的是在执行往表中导入数据的SQL语句时用户需设置以下参数来保证写入表中的数据是被压缩的
--SQL语句的最终输出结果是否压缩
set hive.exec.compress.outputtrue;
--输出结果的压缩格式以下示例为snappy
set mapreduce.output.fileoutputformat.compress.codec org.apache.hadoop.io.compress.SnappyCodec;
ORC
若一张表的文件类型为ORC若需要对该表数据进行压缩需在建表语句中声明压缩
create table orc_table
(column_specs)
stored as orc
tblproperties (orc.compresssnappy);Parquet
若一张表的文件类型为Parquet若需要对该表数据进行压缩
create table orc_table
(column_specs)
stored as parquet
tblproperties (parquet.compressionsnappy);4.2 计算过程中使用压缩
# 单个MR的中间结果进行压缩
# 单个MR的中间结果是指Mapper输出的数据对其进行压缩可降低shuffle阶段的网络IO
--开启MapReduce中间数据压缩功能
set mapreduce.map.output.compresstrue;
--设置MapReduce中间数据数据的压缩方式以下示例为snappy
set mapreduce.map.output.compress.codecorg.apache.hadoop.io.compress.SnappyCodec;# 单条SQL语句的中间结果进行压缩
# 单条SQL语句的中间结果是指两个MR一条SQL语句可能需要通过MR进行计算之间的临时数据
--是否对两个MR之间的临时数据进行压缩
set hive.exec.compress.intermediatetrue;
--压缩格式以下示例为snappy
set hive.intermediate.compression.codec org.apache.hadoop.io.compress.SnappyCodec;
五、企业级调优
1、计算资源配置
1.1 Yarn资源配置
yarn.nodemanager.resource.memory-mb该参数的含义是一个NodeManager节点分配给Container使用的内存。该参数的配置取决于NodeManager所在节点的总内存容量和该节点运行的其他服务的数量默认8Gyarn.nodemanager.resource.cpu-vcores该参数的含义是一个NodeManager节点分配给Container使用的CPU核数。该参数的配置同样取决于NodeManager所在节点的总CPU核数和该节点运行的其他服务默认8核一般一个核心对于4gyarn.scheduler.maximum-allocation-mb该参数的含义是单个Container能够使用的最大内存yarn.scheduler.minimum-allocation-mb该参数的含义是单个Container能够使用的最小内存
修改$HADOOP_HOME/etc/hadoop/yarn-site.xml文件
propertynameyarn.nodemanager.resource.memory-mb/namevalue65536/value
/property
propertynameyarn.nodemanager.resource.cpu-vcores/namevalue16/value
/property
propertynameyarn.scheduler.maximum-allocation-mb/namevalue16384/value
/property
propertynameyarn.scheduler.minimum-allocation-mb/namevalue512/value
/property1.2 MapReduce资源配置
MapReduce资源配置主要包括Map Task的内存和CPU核数以及Reduce Task的内存和CPU核数。核心配置参数如下
mapreduce.map.memory.mb 该参数的含义是单个Map Task申请的container容器内存大小其默认值为1024。该值不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。该参数需要根据不同的计算任务单独进行配置在hive中可直接使用如下方式为每个SQL语句单独进行配置
set mapreduce.map.memory.mb2048;mapreduce.map.cpu.vcores 该参数的含义是单个Map Task申请的container容器cpu核数其默认值为1。该值一般无需调整mapreduce.reduce.memory.mb 该参数的含义是单个Reduce Task申请的container容器内存大小其默认值为1024。该值同样不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。该参数需要根据不同的计算任务单独进行配置在hive中可直接使用如下方式为每个SQL语句单独进行配置
set mapreduce.reduce.memory.mb2048;mapreduce.reduce.cpu.vcores 该参数的含义是单个Reduce Task申请的container容器cpu核数其默认值为1。该值一般无需调整
2、测试用表
# 资料包自行领取https://download.csdn.net/download/lemon_TT/87685013
# 订单表(2000w条数据)
drop table if exists order_detail;
create table order_detail(id string comment 订单id,user_id string comment 用户id,product_id string comment 商品id,province_id string comment 省份id,create_time string comment 下单时间,product_num int comment 商品件数,total_amount decimal(16, 2) comment 下单金额
)
partitioned by (dt string)
row format delimited fields terminated by \t;
# 装载
load data local inpath /opt/module/hive/datas/order_detail.txt overwrite into table order_detail partition(dt2020-06-14);
# 支付表(600w条数据)
drop table if exists payment_detail;
create table payment_detail(id string comment 支付id,order_detail_id string comment 订单明细id,user_id string comment 用户id,payment_time string comment 支付时间,total_amount decimal(16, 2) comment 支付金额
)
partitioned by (dt string)
row format delimited fields terminated by \t;
# 装载
load data local inpath /opt/module/hive/datas/payment_detail.txt overwrite into table payment_detail partition(dt2020-06-14);
# 商品信息表(100w条数据)
drop table if exists product_info;
create table product_info(id string comment 商品id,product_name string comment 商品名称,price decimal(16, 2) comment 价格,category_id string comment 分类id
)
row format delimited fields terminated by \t;
load data local inpath /opt/module/hive/datas/product_info.txt overwrite into table product_info;
# 省份信息表(34条数据)
drop table if exists province_info;
create table province_info(id string comment 省份id,province_name string comment 省份名称
)
row format delimited fields terminated by \t;
load data local inpath /opt/module/hive/datas/province_info.txt overwrite into table province_info;
3、Explain查看执行计划(重点)
3.1 Explain执行计划概述
Explain呈现的执行计划由一系列Stage组成这一系列Stage具有依赖关系每个Stage对应一个MapReduce Job或者一个文件系统操作等。
若某个Stage对应的一个MapReduce Job其Map端和Reduce端的计算逻辑分别由Map Operator Tree和Reduce Operator Tree进行描述Operator Tree由一系列的Operator组成一个Operator代表在Map或Reduce阶段的一个单一的逻辑操作例如TableScan OperatorSelect OperatorJoin Operator等
TableScan表扫描操作通常map端第一个操作肯定是表扫描操作Select Operator选取操作Group By Operator分组聚合操作Reduce Output Operator输出到 reduce 操作Filter Operator过滤操作Join Operatorjoin 操作File Output Operator文件输出操作Fetch Operator 客户端获取数据操作
3.2 基本用法
EXPLAIN [FORMATTED | EXTENDED | DEPENDENCY] query-sql
# FORMATTED、EXTENDED、DEPENDENCY关键字为可选项各自作用如下。
# FORMATTED将执行计划以JSON字符串的形式输出
# EXTENDED输出执行计划中的额外信息通常是读写的文件名等信息
# DEPENDENCY输出执行计划读取的表及分区explain
selectuser_id,count(*)
from order_detail
group by user_id;
4、 HQL之分组聚合优化
4.1 优化说明
Hive中未经优化的分组聚合是通过一个MapReduce Job实现的。Map端负责读取数据并按照分组字段分区通过Shuffle将数据发往Reduce端各组数据在Reduce端完成最终的聚合运算。
Hive对分组聚合的优化主要围绕着减少Shuffle数据量进行具体做法是map-side聚合。所谓map-side聚合就是在map端维护一个hash table利用其完成部分的聚合然后将部分聚合的结果按照分组字段分区发送至reduce端完成最终的聚合。map-side聚合能有效减少shuffle的数据量提高分组聚合运算的效率
--启用map-side聚合
set hive.map.aggrtrue;--用于检测源表数据是否适合进行map-side聚合。检测的方法是先对若干条数据进行map-side聚合
# 若聚合后的条数和聚合前的条数比值小于该值则认为该表适合进行map-side聚合否则认为该表数据不适合进行map-side聚合后续数据便不再进行map-side聚合。
set hive.map.aggr.hash.min.reduction0.5;--用于检测源表是否适合map-side聚合的条数。
set hive.groupby.mapaggr.checkinterval100000;--map-side聚合所用的hash table占用map task堆内存的最大比例若超出该值则会对hash table进行一次flush。
set hive.map.aggr.hash.force.flush.memory.threshold0.9;-- 举例
selectproduct_id,count(*)
from order_detail
group by product_id;
5、HQL之Join优化
5.1 Join算法概述 Hive拥有多种join算法包括Common JoinMap JoinBucket Map JoinSort Merge Buckt Map Join等 Common Join
Common Join是Hive中最稳定的join算法其通过一个MapReduce Job完成一个join操作。Map端负责读取join操作所需表的数据并按照关联字段进行分区通过Shuffle将其发送到Reduce端相同key的数据在Reduce端完成最终的Join操作 需要注意的是sql语句中的join操作和执行计划中的Common Join任务并非一对一的关系一个sql语句中的相邻的且关联字段相同的多个join操作可以合并为一个Common Join任务
Map Join
Map Join算法可以通过两个只有map阶段的Job完成一个join操作。其适用场景为大表join小表。若某join操作满足要求则第一个Job会读取小表数据将其制作为hash table并上传至Hadoop分布式缓存本质上是上传至HDFS。第二个Job会先从分布式缓存中读取小表数据并缓存在Map Task的内存中然后扫描大表数据这样在map端即可完成关联操作 Bucket Map Join
Bucket Map Join是对Map Join算法的改进其打破了Map Join只适用于大表join小表的限制可用于大表join大表的场景。Bucket Map Join的核心思想是若能保证参与join的表均为分桶表且关联字段为分桶字段且其中一张表的分桶数量是另外一张表分桶数量的整数倍就能保证参与join的两张表的分桶之间具有明确的关联关系所以就可以在两表的分桶间进行Map Join操作了。这样一来第二个Job的Map端就无需再缓存小表的全表数据了而只需缓存其所需的分桶即可。 Sort Merge Bucket Map Join
Sort Merge Bucket Map Join简称SMB Map Join基于Bucket Map Join。SMB Map Join要求参与join的表均为分桶表且需保证分桶内的数据是有序的且分桶字段、排序字段和关联字段为相同字段且其中一张表的分桶数量是另外一张表分桶数量的整数倍。
SMB Map Join同Bucket Join一样同样是利用两表各分桶之间的关联关系在分桶之间进行join操作不同的是分桶之间的join操作的实现原理。Bucket Map Join两个分桶之间的join实现原理为Hash Join算法而SMB Map Join两个分桶之间的join实现原理为Sort Merge Join算法。Hash Join和Sort Merge Join均为关系型数据库中常见的Join实现算法。Hash Join的原理相对简单就是对参与join的一张表构建hash table然后扫描另外一张表然后进行逐行匹配。Sort Merge Join需要在两张按照关联字段排好序的表中进行 Hive中的SMB Map Join就是对两个分桶的数据按照上述思路进行Join操作。可以看出SMB Map Join与Bucket Map Join相比在进行Join操作时Map端是无需对整个Bucket构建hash table也无需在Map端缓存整个Bucket数据的每个Mapper只需按顺序逐个key读取两个分桶的数据进行join即可
5.2 Map Join Map Join有两种触发方式一种是用户在SQL语句中增加hint提示另外一种是Hive优化器根据参与join表的数据量大小自动触发 **Hint提示**用户可通过如下方式指定通过map join算法并且ta将作为map join中的小表。这种方式已经过时不推荐使用。
select /* mapjoin(ta) */ta.id,tb.id
from table_a ta
join table_b tb
on ta.idtb.id;自动触发
Hive在编译SQL语句阶段起初所有的join操作均采用Common Join算法实现。之后在物理优化阶段Hive会根据每个Common Join任务所需表的大小判断该Common Join任务是否能够转换为Map Join任务若满足要求便将Common Join任务自动转换为Map Join任务。
但有些Common Join任务所需的表大小在SQL的编译阶段是未知的例如对子查询进行join操作所以这种Common Join任务是否能转换成Map Join任务在编译阶是无法确定的。针对这种情况Hive会在编译阶段生成一个条件任务Conditional Task其下会包含一个计划列表计划列表中包含转换后的Map Join任务以及原有的Common Join任务。最终具体采用哪个计划是在运行时决定的 --启动Map Join自动转换
set hive.auto.convert.jointrue;--一个Common Join operator转为Map Join operator的判断条件,若该Common Join相关的表中,存在n-1张表的已知大小总和该值,则生成一个Map Join计划,此时可能存在多种n-1张表的组合均满足该条件,
# 则hive会为每种满足条件的组合均生成一个Map Join计划,同时还会保留原有的Common Join计划作为后备(back up)计划,实际运行时,优先执行Map Join计划若不能执行成功则启动Common Join后备计划。
set hive.mapjoin.smalltable.filesize250000;--开启无条件转Map Join
set hive.auto.convert.join.noconditionaltasktrue;--无条件转Map Join时的小表之和阈值,若一个Common Join operator相关的表中存在n-1张表的大小总和该值,此时hive便不会再为每种n-1张表的组合均生成Map Join计划,同时也不会保留Common Join作为后备计划。而是只生成一个最优的Map Join计划。
set hive.auto.convert.join.noconditionaltask.size10000000;优化案例
# 示例
select*
from order_detail od
join product_info product on od.product_id product.id
join province_info province on od.province_id province.id;
# 有三张表进行两次join操作且两次join操作的关联字段不同。故优化前的执行计划应该包含两个Common Join operator也就是由两个MapReduce任务实现# 优化思路
# 可使用如下语句获取表/分区的大小信息
desc formatted table_name partition(partition_colpartition);
# 三张表中product_info和province_info数据量较小可考虑将其作为小表进行Map Join优化# 方案一
# 启用Map Join自动转换
set hive.auto.convert.jointrue;
# 不使用无条件转Map Join
set hive.auto.convert.join.noconditionaltaskfalse;
# 调整hive.mapjoin.smalltable.filesize参数使其大于等于product_info,会生成多种可能的mapjoin
set hive.mapjoin.smalltable.filesize25285707;# 方案二
set hive.auto.convert.jointrue;
# 使用无条件转Map Join
set hive.auto.convert.join.noconditionaltasktrue;
# 调整hive.auto.convert.join.noconditionaltask.size参数使其大于等于product_info和province_info之和
set hive.auto.convert.join.noconditionaltask.size25286076;
# 这样可直接将两个Common Join operator转为两个Map Join operator并且由于两个Map Join operator的小表大小之和小于等于hive.auto.convert.join.noconditionaltask.size
# 故两个Map Join operator任务可合并为同一个。这个方案计算效率最高但需要的内存也是最多的# 方案三
set hive.auto.convert.jointrue;
set hive.auto.convert.join.noconditionaltasktrue;
# 调整hive.auto.convert.join.noconditionaltask.size参数使其等于product_info
set hive.auto.convert.join.noconditionaltask.size25285707;
# 这样可直接将两个Common Join operator转为Map Join operator但不会将两个Map Join的任务合并。该方案计算效率比方案二低但需要的内存也更少
5.3 Bucket Map Join
Bucket Map Join不支持自动转换发须通过用户在SQL语句中提供如下Hint提示并配置如下相关参数方可使用
select /* mapjoin(ta) */ta.id,tb.id
from table_a ta
join table_b tb on ta.idtb.id;# 相关参数
--关闭cbo优化cbo会导致hint信息被忽略
set hive.cbo.enablefalse;
--map join hint默认会被忽略(因为已经过时)需将如下参数设置为false
set hive.ignore.mapjoin.hintfalse;
--启用bucket map join优化功能
set hive.optimize.bucketmapjoin true;
两张表都相对较大若采用普通的Map Join算法则Map端需要较多的内存来缓存数据当然可以选择为Map段分配更多的内存来保证任务运行成功。但是Map端的内存不可能无上限的分配所以当参与Join的表数据量均过大时就可以考虑采用Bucket Map Join算法
5.4 Sort Merge Bucket Map Join
Sort Merge Bucket Map Join有两种触发方式包括Hint提示和自动转换。Hint提示已过时不推荐使用
--启动Sort Merge Bucket Map Join优化
set hive.optimize.bucketmapjoin.sortedmergetrue;
--使用自动转换SMB Join
set hive.auto.convert.sortmerge.jointrue;6、HQL之数据倾斜
6.1 数据倾斜概述
数据倾斜问题通常是指参与计算的数据分布不均即某个key或者某些key的数据量远超其他key导致在shuffle阶段大量相同key的数据被发往同一个Reduce进而导致该Reduce所需的时间远超其他Reduce成为整个任务的瓶颈。
6.2 分组聚合导致的数据倾斜
Hive中未经优化的分组聚合是通过一个MapReduce Job实现的。Map端负责读取数据并按照分组字段分区通过Shuffle将数据发往Reduce端各组数据在Reduce端完成最终的聚合运算。如果group by分组字段的值分布不均就可能导致大量相同的key进入同一Reduce从而导致数据倾斜问题由分组聚合导致的数据倾斜问题有以下两种解决思路Map-Side聚合和Skew-GroupBy优化
Map-Side聚合
开启Map-Side聚合后数据会现在Map端完成部分聚合工作。这样一来即便原始数据是倾斜的经过Map端的初步聚合后发往Reduce的数据也就不再倾斜了。最佳状态下Map-端聚合能完全屏蔽数据倾斜问题
--启用map-side聚合默认开启的若想看到数据倾斜的现象需要先将hive.map.aggr参数设置为false
set hive.map.aggrtrue;--用于检测源表数据是否适合进行map-side聚合。检测的方法是先对若干条数据进行map-side聚合若聚合后的条数和聚合前的条数比值小于该值则认为该表适合进行map-side聚合否则认为该表数据不适合进行map-side聚合后续数据便不再进行map-side聚合。
set hive.map.aggr.hash.min.reduction0.5;--用于检测源表是否适合map-side聚合的条数。
set hive.groupby.mapaggr.checkinterval100000;--map-side聚合所用的hash table占用map task堆内存的最大比例若超出该值则会对hash table进行一次flush。
set hive.map.aggr.hash.force.flush.memory.threshold0.9;Skew-GroupBy优化
Skew-GroupBy的原理是启动两个MR任务第一个MR按照随机数分区将数据分散发送到Reduce完成部分聚合第二个MR按照分组字段分区完成最终聚合
--启用分组聚合数据倾斜优化
set hive.groupby.skewindatatrue;6.3 Join导致的数据倾斜
未经优化的join操作默认是使用common join算法也就是通过一个MapReduce Job完成计算。Map端负责读取join操作所需表的数据并按照关联字段进行分区通过Shuffle将其发送到Reduce端相同key的数据在Reduce端完成最终的Join操作。如果关联字段的值分布不均就可能导致大量相同的key进入同一Reduce从而导致数据倾斜问题。由join导致的数据倾斜问题有如下几种解决方案
map join
使用map join算法join操作仅在map端就能完成没有shuffle操作没有reduce阶段自然不会产生reduce端的数据倾斜。该方案适用于大表join小表时发生数据倾斜的场景
--启动Map Join自动转换
set hive.auto.convert.jointrue;--一个Common Join operator转为Map Join operator的判断条件,若该Common Join相关的表中,存在n-1张表的大小总和该值,则生成一个Map Join计划,此时可能存在多种n-1张表的组合均满足该条件,则hive会为每种满足条件的组合均生成一个Map Join计划,同时还会保留原有的Common Join计划作为后备(back up)计划,实际运行时,优先执行Map Join计划若不能执行成功则启动Common Join后备计划。
set hive.mapjoin.smalltable.filesize250000;--开启无条件转Map Join
set hive.auto.convert.join.noconditionaltasktrue;--无条件转Map Join时的小表之和阈值,若一个Common Join operator相关的表中存在n-1张表的大小总和该值,此时hive便不会再为每种n-1张表的组合均生成Map Join计划,同时也不会保留Common Join作为后备计划。而是只生成一个最优的Map Join计划。
set hive.auto.convert.join.noconditionaltask.size10000000;skew join
skew join的原理是为倾斜的大key单独启动一个map join任务进行计算其余key进行正常的common join --启用skew join优化
set hive.optimize.skewjointrue;
--触发skew join的阈值若某个key的行数超过该参数值则触发
set hive.skewjoin.key100000;
-- 这种方案对参与join的源表大小没有要求但是对两表中倾斜的key的数据量有要求要求一张表中的倾斜key的数据量比较小方便走mapjoin调整SQL语句
若参与join的两表均为大表其中一张表的数据是倾斜的此时也可通过以下方式对SQL语句进行相应的调整
-- 相同的id的进入同一个reducer进行后序操作
select*
from A
join B
on A.idB.id;-- 调整sql
select*
from(select --打散操作concat(id,_,cast(rand()*2 as int)) id,valuefrom A
)ta
join(select --扩容操作concat(id,_,0) id,valuefrom Bunion allselectconcat(id,_,1) id,valuefrom B
)tb
on ta.idtb.id;
7、HQL之任务并行度 对于一个分布式的计算任务而言设置一个合适的并行度十分重要。Hive的计算任务由MapReduce完成故并行度的调整需要分为Map端和Reduce端 7.1 Map端并行度
Map端的并行度也就是Map的个数。是由输入文件的切片数决定的。一般情况下Map端的并行度无需手动调整。以下特殊情况可考虑调整map端并行度
查询的表中存在大量小文件
按照Hadoop默认的切片策略一个小文件会单独启动一个map task负责计算。若查询的表中存在大量小文件则会启动大量map task造成计算资源的浪费。这种情况下可以使用Hive提供的CombineHiveInputFormat多个小文件合并为一个切片从而控制map task个数默认开启
set hive.input.formatorg.apache.hadoop.hive.ql.io.CombineHiveInputFormat;map端有复杂的查询逻辑
若SQL语句中有正则替换、json解析等复杂耗时的查询逻辑时map端的计算会相对慢一些。若想加快计算速度在计算资源充足的情况下可考虑增大map端的并行度令map task多一些每个map task计算的数据少一些
--一个切片的最大值,默认256M
set mapreduce.input.fileinputformat.split.maxsize256000000;7.2 Reduce端并行度
Reduce端的并行度也就是Reduce个数。相对来说更需要关注。Reduce端的并行度可由用户自己指定也可由Hive自行根据该MR Job输入的文件大小进行估算
--指定Reduce端并行度默认值为-1表示用户未指定
set mapreduce.job.reduces;
--Reduce端并行度最大值
set hive.exec.reducers.max;
--单个Reduce Task计算的数据量用于估算Reduce并行度
set hive.exec.reducers.bytes.per.reducer;8、HQL之小文件合并 小文件合并优化分为两个方面分别是Map端输入的小文件合并和Reduce端输出的小文件合并 8.1 Map端输入文件合并
合并Map端输入的小文件是指将多个小文件划分到一个切片中进而由一个Map Task去处理。目的是防止为单个小文件启动一个Map Task浪费计算资源
--可将多个小文件切片合并为一个切片进而由一个map任务处理
set hive.input.formatorg.apache.hadoop.hive.ql.io.CombineHiveInputFormat;8.2 Reduce输出文件合并
合并Reduce端输出的小文件是指将多个小文件合并成大文件。目的是减少HDFS小文件数量。其原理是根据计算任务输出文件的平均大小进行判断若符合条件则单独启动一个额外的任务进行合并
--开启合并map only任务输出的小文件,默认false
set hive.merge.mapfilestrue;--开启合并map reduce任务输出的小文件,默认false
set hive.merge.mapredfilestrue;--合并后的文件大小
set hive.merge.size.per.task256000000;--触发小文件合并任务的阈值若某计算任务输出的文件平均大小低于该值则触发合并
set hive.merge.smallfiles.avgsize16000000;9、其他优化
9.1 CBO优化
CBO是指Cost based Optimizer即基于计算成本的优化。在Hive中计算成本模型考虑到了数据的行数、CPU、本地IO、HDFS IO、网络IO等方面。Hive会计算同一SQL语句的不同执行计划的计算成本并选出成本最低的执行计划。目前CBO在hive的MR引擎下主要用于join的优化例如多表join的join顺序
--是否启用cbo优化默认开启
set hive.cbo.enabletrue;-- 示例演示
select*
from order_detail od
join product_info product on od.product_idproduct.id
join province_info province on od.province_idprovince.id;--关闭cbo优化
set hive.cbo.enablefalse;
--为了测试效果更加直观关闭map join自动转换
set hive.auto.convert.joinfalse;--开启cbo优化
set hive.cbo.enabletrue;
--为了测试效果更加直观关闭map join自动转换
set hive.auto.convert.joinfalse;-- CBO优化对于执行计划中join顺序是有影响的其之所以会将province_info的join顺序提前是因为province info的数据量较小
-- 将其提前会有更大的概率使得中间结果的数据量变小从而使整个计算任务的数据量减小也就是使计算成本变小9.2 谓词下推
谓词下推predicate pushdown是指尽量将过滤操作前移以减少后续计算步骤的数据量
--是否启动谓词下推predicate pushdown优化
set hive.optimize.ppd true;
-- CBO优化也会完成一部分的谓词下推优化工作因为在执行计划中谓词越靠前整个计划的计算成本就会越低-- 测试实例
--是否启动谓词下推predicate pushdown优化
set hive.optimize.ppd false;
--为了测试效果更加直观关闭cbo优化
set hive.cbo.enablefalse;
9.3 矢量化查询
Hive的矢量化查询优化依赖于CPU的矢量化计算。Hive的矢量化查询可以极大的提高一些典型查询场景例如scans, filters, aggregates, and joins下的CPU使用效率。参考https://cwiki.apache.org/confluence/display/Hive/VectorizedQueryExecution#VectorizedQueryExecution-Limitations
-- 若执行计划中出现“Execution mode: vectorized”字样即表明使用了矢量化计算
set hive.vectorized.execution.enabledtrue;
9.4 Fetch抓取
Fetch抓取是指Hive中对某些情况的查询可以不必使用MapReduce计算。例如select * from emp;在这种情况下Hive可以简单地读取emp对应的存储目录下的文件然后输出查询结果到控制台
--是否在特定场景转换为fetch 任务
--设置为none表示不转换
--设置为minimal表示支持select *分区字段过滤Limit等
--设置为more表示支持select 任意字段,包括函数过滤和limit等
set hive.fetch.task.conversionmore;9.5 本地模式
大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过有时Hive的输入数据量是非常小的。在这种情况下为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集执行时间可以明显被缩短
--开启自动转换为本地模式
set hive.exec.mode.local.autotrue; --设置local MapReduce的最大输入数据量当输入数据量小于这个值时采用local MapReduce的方式默认为134217728即128M
set hive.exec.mode.local.auto.inputbytes.max50000000;--设置local MapReduce的最大输入文件个数当输入文件个数小于这个值时采用local MapReduce的方式默认为4
set hive.exec.mode.local.auto.input.files.max10;9.6 并行执行
Hive会将一个SQL语句转化成一个或者多个Stage每个Stage对应一个MR Job。默认情况下Hive同时只会执行一个Stage。但是某SQL语句可能会包含多个Stage但这多个Stage可能并非完全互相依赖也就是说有些Stage是可以并行执行的。此处提到的并行执行就是指这些Stage的并行执行
--启用并行执行优化
set hive.exec.paralleltrue; --同一个sql允许最大并行度默认为8
set hive.exec.parallel.thread.number8; 9.7 严格模式 Hive可以通过设置某些参数防止危险操作 分区表不使用分区过滤
将hive.strict.checks.no.partition.filter设置为true时对于分区表除非where语句中含有分区字段过滤条件来限制范围否则不允许执行。换句话说就是用户不允许扫描所有分区。进行这个限制的原因是通常分区表都拥有非常大的数据集而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
使用order by没有limit过滤
将hive.strict.checks.orderby.no.limit设置为true时对于使用了order by语句的查询要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reduce中进行处理强制要求用户增加这个limit语句可以防止Reduce额外执行很长一段时间开启了limit可以在数据进入到Reduce之前就减少一部分数据。
笛卡尔积
将hive.strict.checks.cartesian.product设置为true时会限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是Hive并不会执行这种优化因此如果表足够大那么这个查询就会出现不可控的情况。
# 可以进入hive-site.xml修改
propertynamehive.exec.dynamic.partition.mode/namevaluenonstrict/value
/property
六、实战案例 详情可以查看https://download.csdn.net/download/lemon_TT/87685013 1、同时在线人数问题
现有各直播间的用户访问记录表live_events如下表中每行数据表达的信息为一个用户何时进入了一个直播间又在何时离开了该直播间现要求统计各直播间最大同时在线人数
drop table if exists live_events;
create table if not exists live_events
(user_id int comment 用户id,live_id int comment 直播id,in_datetime string comment 进入直播间时间,out_datetime string comment 离开直播间时间
)comment 直播间访问记录;selectlive_id,max(user_count) max_user_count
from
(selectuser_id,live_id,sum(user_change) over(partition by live_id order by event_time) user_countfrom(select user_id,live_id,in_datetime event_time,1 user_changefrom live_eventsunion allselect user_id,live_id,out_datetime,-1from live_events)t1
)t2
group by live_id;
2、会话划分问题
现有页面浏览记录表page_view_events表中有每个用户的每次页面访问记录规定若同一用户的相邻两次访问记录时间间隔小于60s则认为两次浏览记录属于同一会话。现有如下需求为属于同一会话的访问记录增加一个相同的会话id字段
drop table if exists page_view_events;
create table if not exists page_view_events
(user_id int comment 用户id,page_id string comment 页面id,view_timestamp bigint comment 访问时间戳
)comment 页面访问记录;select user_id,page_id,view_timestamp,concat(user_id, -, sum(session_start_point) over (partition by user_id order by view_timestamp)) session_id
from (select user_id,page_id,view_timestamp,if(view_timestamp - lagts 60, 1, 0) session_start_pointfrom (select user_id,page_id,view_timestamp,lag(view_timestamp, 1, 0) over (partition by user_id order by view_timestamp) lagtsfrom page_view_events) t1) t2;
3、间断连续登录用户问题
现有各用户的登录记录表login_events表中每行数据表达的信息是一个用户何时登录了平台现要求统计各用户最长的连续登录天数间断一天也算作连续例如一个用户在1,3,5,6登录则视为连续6天登录。
drop table if exists login_events;
create table if not exists login_events
(user_id int comment 用户id,login_datetime string comment 登录时间
)comment 直播间访问记录;selectuser_id,max(recent_days) max_recent_days --求出每个用户最大的连续天数
from
(selectuser_id,user_flag,datediff(max(login_date),min(login_date)) 1 recent_days --按照分组求每个用户每次连续的天数(记得加1)from(selectuser_id,login_date,lag1_date,concat(user_id,_,flag) user_flag --拼接用户和标签分组from(selectuser_id,login_date,lag1_date,sum(if(datediff(login_date,lag1_date)2,1,0)) over(partition by user_id order by login_date) flag --获取大于2的标签from(selectuser_id,login_date,lag(login_date,1,1970-01-01) over(partition by user_id order by login_date) lag1_date --获取上一次登录日期from(selectuser_id,date_format(login_datetime,yyyy-MM-dd) login_datefrom login_eventsgroup by user_id,date_format(login_datetime,yyyy-MM-dd) --按照用户和日期去重)t1)t2)t3)t4group by user_id,user_flag
)t5
group by user_id;
4、日期交叉问题
现有各品牌优惠周期表promotion_info其记录了每个品牌的每个优惠活动的周期其中同一品牌的不同优惠活动的周期可能会有交叉现要求统计每个品牌的优惠总天数若某个品牌在同一天有多个优惠活动则只按一天计算
drop table if exists promotion_info;
create table promotion_info
(promotion_id string comment 优惠活动id,brand string comment 优惠品牌,start_date string comment 优惠活动开始日期,end_date string comment 优惠活动结束日期
) comment 各品牌活动周期表;selectbrand,sum(datediff(end_date,start_date)1) promotion_day_count
from
(selectbrand,max_end_date,if(max_end_date is null or start_datemax_end_date,start_date,date_add(max_end_date,1)) start_date,end_datefrom(selectbrand,start_date,end_date,max(end_date) over(partition by brand order by start_date rows between unbounded preceding and 1 preceding) max_end_datefrom promotion_info)t1
)t2
where end_datestart_date
group by brand;
