Hive
Hive
Hive概述
-
Hive是一个Hadoop生态的数据仓库工具
-
数据仓库和数据库的对比
对比 | 数据库 | 数据仓库 |
---|---|---|
速度 | 低延迟 | 高延迟 |
容量 | 百万级 | 亿级 |
使用场景 | 客户端交互 | 大范围统计计算 |
Hive架构
安装mysql
- 由于Hive自带的derby数据库性能较差,使用不方便,所以我们将Hive的metaStore替换成Mysql数据库
- 安装Mysql
使用自动化脚本安装mysql
#!/bin/bash
echo -e "\033[4;40;31m欢迎使用mysql离线安装自动化脚本 v1.0\033[0m"
echo -e "\033[4;40;31m作者:Amos QQ:410507803 E-Mail:amos@amoscloud.com\033[0m\n"
read -p "请输入mysql8的zx压缩包文件所在路径(eg:/opt/mysql8.xxx.xz):" FILE_PATH
read -p "请输入想要安装的目录(eg:/usr/local/mysql):" DEST_PATH
rpm -e --nodeps $(rpm -qa | grep mariadb)
echo -e "\033[40;32m (1/13)正在解压,请耐心等待解压过程约1-3分钟... \033[0m"
tar Jxf $FILE_PATH -C .
echo -e "\033[40;32m 解压完成 \033[0m"
echo -e "\033[40;32m (2/13)移动加压后的文件到$DEST_PATH \033[0m"
mv mysql-8*x86_64 $DEST_PATH
echo -e "\033[40;32m (3/13)添加环境变量$DEST_PATH \033[0m"
echo "export MYSQL_HOME=$DEST_PATH" >>/etc/profile
echo 'export PATH=.:$MYSQL_HOME/bin:$PATH' >>/etc/profile
source /etc/profile
echo -e "\033[40;32m (4/13)创建data目录 \033[0m"
mkdir $DEST_PATH/data
echo -e "\033[40;32m (5/13)创建my.cnf配置文件 \033[0m"
rm -rf /etc/my.cnf
echo "
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
user=mysql
socket=/tmp/mysql.sock
basedir=$DEST_PATH
datadir=$DEST_PATH/data
log-error=$DEST_PATH/error.log
pid-file = $DEST_PATH/mysql.pid
transaction_isolation = READ-COMMITTED
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1
" > /etc/my.cnf
echo 'sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"' >> /etc/my.cnf
echo -e "\033[40;32m (6/13)创建mysql组 \033[0m"
groupadd mysql
echo -e "\033[40;32m (7/13)创建mysql用户并加入mysql组 \033[0m"
useradd -g mysql mysql
echo -e "\033[40;32m (8/13)修改安装目录权限和所有者 \033[0m"
chown -R mysql:mysql $DEST_PATH
chmod -R 755 $DEST_PATH
echo -e "\033[40;32m (9/13)初始化mysql \033[0m"
$DEST_PATH/bin/mysqld --initialize --user=mysql
echo -e "\033[40;32m (10/13)尝试启动mysql \033[0m"
$DEST_PATH/support-files/mysql.server start
echo -e "\033[40;32m (11/13)将mysqld添加为服务并设置开机自启动 \033[0m"
cp $DEST_PATH/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --level 345 mysqld on
echo -e "\033[40;32m (12/13)重启mysql \033[0m"
service mysqld restart
echo -e "\033[40;32m (13/13)读取临时密码 \033[0m"
TEMP_PW=$(cat $DEST_PATH/error.log | grep 'password' | awk -F' ' '{print $NF}')
echo -e "
\033[40;32m mysql的初始临时密码为:$TEMP_PW \033[0m
\033[40;32m 使用初始密码登录mysql后,您可以使用如下SQL修改初始密码: \033[0m
\033[40;33m ALTER user 'root'@'localhost' IDENTIFIED BY 'a123456'; \033[0m
\033[40;32m 使用如下SQL添加可远程访问的root用户: \033[0m
\033[40;33m CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'a123456'; \033[0m
\033[40;33m GRANT ALL ON *.* TO 'root'@'%'; \033[0m
\033[40;33m FLUSH PRIVILEGES; \033[0m
\033[40;32m 3秒后将使用初始密码登录mysql,感谢您的使用 \033[0m
"
sleep 3
mysql -uroot -p$TEMP_PW
Hive的安装和使用
- 下载解压改名字
当时使用hive2.3.9
由于Hive使用Hadoop的HDFS和MR
所以只需要Hive可以兼容HDFS和MR就可以只用
-
修改配置文件
- hive-env.sh
HADOOP_HOME=/opt/hadoop-2.7.7
export HIVE_CONF_DIR=/opt/hive-2.3.9/conf
export JAVA_HOME=/opt/jdk1.8
- hive-site.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://node02:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
</configuration>
- hive metastore初始化
-- 登录mysql 创建metasotre数据库
mysql -uroot -p123456
create database metastore character set 'utf8';
schematool -dbType mysql -initSchema
- 配置hive环境变量
echo 'export HIVE_HOME=/opt/hive-2.3.9' >> /etc/profile
echo 'export PATH=$HIVE_HOME/bin:$PATH' >> /etc/profile
source /etc/profile
- 启动客户端并验证安装
# 1.启动客户端
hive
# hive>
# 2.查看hive的默认数据库
show databases;
# default
# 3.创建表
create table t1(id int,name string)
# 4.插入数据
insert into table t1 values(1,'amos');
# 5.查询
select * from t1;
Hive使用
- Hive支持类SQL的HiveSQL语句进行数据定义、数据修改和数据查询
- Hive SQL与标准SQL最大的区别在于HQL不支持update操作
DDL
库
-- 创建数据库
create database db1;
-- 查看数据库
show databases;
-- 查看hive中数据库的详细信息
-- hive中有一个默认的库叫default,默认路径在hdfs://xxx/user/hive/warehouse
-- 创建出来的其他数据库在user/hive/warehouse中,比如db1
-- hdfs://xxx/user/hive/warehouse/db1.db
desc database db_name;
-- 使用数据库
use db_name;
-- 删除数据库 如果库非空 可以使用cascade强制递归删除
drop database db_name [cascade];
表
-- 外部
CREATE [EXTERNAL] TABLE [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
-- 受制于
DELIMITED
-- 字段 结束 于 '\t' ' ' '|' '\u0001'
[FIELDS TERMINATED BY char]
-- 集合 元素 结束 于 ','
[COLLECTION ITEMS TERMINATED BY char]
--Map key 结束 于 ':'
[MAP KEYS TERMINATED BY char]
--行 结束 于 '\n'
[LINES TERMINATED BY char]]
-- 设置表存储数据的格式
-- 常见 Text ORC
-- 默认使用 Text
[STORED AS file_format]
-- 建表时指定表映射的hdfs路径
[LOCATION hdfs_path]
DML
Hive数据导入
- load操作
-- local代表本地路径 是复制操作
-- 不加local代表hdfs路径 是移动操作
load data [local] inpath '/root/data/student.txt'
-- load data时 如果分区表的目标分区不存在会自动创建分区
[overwrite] into table student [partition (partcol1=val1,…)];
-
insert插入数据
- 需要先创建结果表
insert into table 表名
select 语句;
- create插入数据
-- 根据查询结果创建表
create table 表名 as select 语句;
-
hdfs客户端直接上传文件
- 由于Hive表的数据实际存储在HDFS的目录中
- 所以可以直接将与表结构相符的文本数据直接通过hdfs客户端上传到表对应的路径中
-- 先查询表所在的路径
desc formatted t_log3;
-- 使用hdfs命令行客户端进行put操作
- 对于Hive的分区表,如果手动创建分区目录或者使用flume之类的数据采集工具创建Hive分区目录,会导致mysql种没有相关的分区信息,所有在查询时需要先修复表的分区结构
-- 修复表的分区
msck repair table 表名;
Hive数据导出
- insert
-- 将查询语句查询的结果导出到Linux本地文件
insert overwrite local directory '/root/data/export/student'
select * from student;
insert overwrite local directory '/root/weblog_0531'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' lines terminated by '\n'
select *
from (
select substr(time, 0, 11) dt, *
from t_orc_format
) t1
where t1.dt='31/Mar/2021';
-
hdfs的get操作进行数据导出
-
使用hive执行查询语句 将输出流重定向到文件
hive -e '查询语句' > 文件
-
使用导入导出关键字进行表的备份
-- 导出备份
export table 表 to hdfs路径
-- 导入数据
import table 表 from hdfs路径
表的截断
truncate
先删除表 再创建表,使用更高效的方式清空表的数据
DQL
-- select用于执行字段提取或者函数调用,用于构建结果集的数据结构
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
-- 数据来源 表 或者是 子查询
FROM table_reference
-- 添加查询结果的过滤条件
[WHERE where_condition]
-- 按照字段进行分组
[GROUP BY col_list]
-- 按照字段进行全局排序
[ORDER BY col_list] [asc|desc]
-- hive种的分区排序操作
[CLUSTER BY col_list |
[DISTRIBUTE BY col_list] [SORT BY col_list] [asc|desc]]
-- 限制读取数据的条数
[LIMIT number]
select * from t1,t2,t3,t4 where t1.xx=t2.xx and t2.xx=t3.xx;
select * from t1 right join t2 on t1.xx=t2.xx join t3 on xxx join t4 on xx
Hive 函数
1. 函数的查询
-- 显示所有可用函数
SHOW FUNCTIONS;
-- 查看函数的说明
DESCRIBE FUNCTION <function_name>;
-- 查看函数详细说明 包含示例
DESCRIBE FUNCTION EXTENDED <function_name>;
-
Hive官网函数文档
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF -
推荐阅读的博客文档
-
日期相关和字符串相关函数需要逐个练习
https://www.cnblogs.com/yuejiaming/p/10572358.html
2. UDF
-
在数据处理中有些操作使用HQL编写逻辑过于繁琐,或者Hive原有的函数库不能提供相应的功能,这时 Hive允许用户使用Java编写一段逻辑将其注册为用户自定义函数,注册之后就可以在HQL操作中使用自定义函数
-
自定义函数实现日志中操作系统类型的提取和日期格式转换
- 创建maven工程引入hive-exec依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>2.3.9</version>
</dependency>
-
编写Java类 继承
org.apache.hadoop.hive.ql.exec.UDF
-
实现方法evaluate,实现函数逻辑
-
将编写好的工程打成jar上传到集群
- 上传jar时注意需要上传到HDFS上,因为Hive底层运行MR时需要所有机器的线程都需要读取udf函数的class文件,如果jar包存储在linux本地目录,需要将jar复制到所有机器的相同路径
-
在hive中注册并使用
CREATE FUNCTION [db_name.]function_name AS class_name
[USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];
-- 使用自定义的jar包创建临时函数
create [temporary] function my_date_format as 'com.xahj.bd2103.hive.udf.DateFormat' using jar 'hdfs://node01:9000/hive_udf/hive-udf-dateForma.jar';
-
hive中创建的临时UDF 当客户端退出后会自动移除
-
如果不加temporary关键字,创建的函数在没有显式删除前可以永久存在
-- 删除用户自定义函数
drop function temporary;
order by、cluster by、distribute by、 sort by区别
关键字 | 作用 |
---|---|
order by | 按照指定字段进行全局排序,数据会聚集在一个reduce中 |
distribute by | 按照指定字段进行MR的分区,配合设置参数mapreduce.job.reduces |
sort by | 按照指定字段进行MR分区内的排序 |
cluster by | 当分区和排序使用相同的字段时,可以使用cluster by代替,cluster by time等价于distribute by time sort by time |
Hive核心概念
1.特殊操作
- 在Hive中直接使用linux本地命令
# ! 命令 ;
hive>!clear;
- 在Hive中直接使用HDFS命令
# dfs 命令 ;
hive>dfs -ls /;
-
每次执行HQL操作都需要进入Hive命令行 操作过于繁琐
-
Hive提供了两种不进入客户端命令行 直接提交HQL或者HQL文件的方式
-
提交一个HQL语句到客户端执行
# 提交Hql脚本到集群执行
hive -e "select * xxxxx"
- 提交一个HQL脚本到客户端执行
# 提交Hql脚本到集群执行
hive -f xxx.sql
2.内部表和外部表
-
内部表(管理表MANAGED_TABLE)
生命周期完全由Hive管理,表在删除时 会同时删除mysql中的元数据和hdfs中的表的内容数据,通常用于自己测试 -
外部表(EXTERNAL_TABLE)
表在删除时仅仅删除mysql中的元数据,HDFS中的目录和文件都保留
在多人开发中,多个用户可能使用HDFS上同一份文件作为表内容,这时所有人使用外部表相当于对原始HDFS文件拥有只读属性
3. Hive的数据类型
- 常用
hive | java |
---|---|
INT | int |
Double | Double |
BIGINT | long |
STRING | String |
- 复杂结构
hive | java |
---|---|
struct | 对象 |
map | map |
array | array |
- 类型强转
-- 类型强转
-- hive中如果强转失败会赋值为NULL
cast(对象 as 目标类型)
4. Hive的分区表
-
Hive的数据处理中通常会将每天产生的增量数据使用Flume等自动采集手段将数据直接写入到HDFS中,Hive将HDFS的数据映射为表结构,使用HQL进行HDFS上数据的SQL处理
-
分区表 使用将一个大表分成多个子目录的形式,将数据按分区条件划分成多个部分,在查询时可以直接指定查询条件用来定位子目录,避免加载全量数据
-
场景
- 将0601、0602、0603、0604、0605加载到hive表中
- 查询6月2日数据中有多少次404访问
由于Hive底层运行MR时,表只有一个目录,读取数据时直接将输入路径指定到表所在的HDFS目录,就会将表的所有文件加载到MR中,如果保存的不是5天而是50年的数据,MR任务依然会每次加载全表数据 - 创建hive表按照日期进行分区,dt=20210601
5. Hive的分桶表
-
Hive的查询中经常需要处理一些分组再排序的操作
-
可以提前在建表时设置分桶方式和排序方式,使得进入表的数据自动按照相应的字段分割成多个文件并排序,提高后期查询时的查询消息
6. Hive的参数优化
hive中提供了各种关于Hive执行和MR执行相关的参数
-- 查看都有哪些配置
set;
-- 查看某个配置当前的设置值
set 配置名称;
-- 在当前客户端中临时修改,立即生效
-- 退出客户端时,下次进入恢复默认
set 配置名称=配置值;
-- 如果想永久修改默认配置可以直接修改hive-site.xml
Hive 实战
日志服务器不断产生访问日志
flume实时采集日志插入Hive分区表 ODS原始数据
# 设置Agent中每个组件的名称
# agent名字avro2HDFS 中的source 有一个起名r1
avro2HDFS.sources = r1
# 给avro2HDFS中的sink起名叫k1
avro2HDFS.sinks = k1
# 给avro2HDFS中channel起名叫c1
avro2HDFS.channels = c1
# 设置source的具体配置
avro2HDFS.sources.r1.type = avro
avro2HDFS.sources.r1.bind = node02
avro2HDFS.sources.r1.port = 55555
# 设置sink
# 配置sink的类型为logger
# logger负责将接收到的数据以INFO级别的日志进行输出
avro2HDFS.sinks.k1.type = HDFS
avro2HDFS.sinks.k1.hdfs.path = hdfs://node01:9000/flume/data
# 设置上传时的文件类型
avro2HDFS.sinks.k1.hdfs.fileType = DataStream
# # 设置上传时的文件格式
avro2HDFS.sinks.k1.hdfs.writeFormat = Text
avro2HDFS.sinks.k1.hdfs.rollInterval = 600
avro2HDFS.sinks.k1.hdfs.rollSize = 133169152
avro2HDFS.sinks.k1.hdfs.rollCount = 0
# 设置channel
# 设置channel使用内存缓存数据
avro2HDFS.channels.c1.type = memory
# 设置channel容量
avro2HDFS.channels.c1.capacity = 1000
# 设置channel接收或者发送event的单次最大吞吐量
avro2HDFS.channels.c1.transactionCapacity = 100
# 设置source sink channel之间的连接
avro2HDFS.sources.r1.channels = c1
avro2HDFS.sinks.k1.channel = c1
编写SQL脚本进行数据清洗 DWS数据服务层
编写SQL脚本进行维度退化 DWD数据维度层
编写SQL脚本进行指标统计 ADS/DM应用层 数据集市层
将ADS加载到Mysql中方便可视化查询