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的安装和使用

1. 下载解压改名字

当时使用hive2.3.9
由于Hive使用Hadoop的HDFS和MR
所以只需要Hive可以兼容HDFS和MR就可以只用

2. 修改配置文件

   • 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>

3. hive metastore初始化

-- 登录mysql 创建metasotre数据库
mysql -uroot -p123456
create database metastore character set 'utf8';

schematool -dbType mysql -initSchema

4. 配置hive环境变量

echo 'export HIVE_HOME=/opt/hive-2.3.9' >> /etc/profile
echo 'export PATH=$HIVE_HOME/bin:$PATH' >> /etc/profile
source /etc/profile

5. 启动客户端并验证安装

# 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数据导入

1. load操作

-- local代表本地路径  是复制操作
-- 不加local代表hdfs路径  是移动操作
load data [local] inpath '/root/data/student.txt'
-- load data时 如果分区表的目标分区不存在会自动创建分区
[overwrite] into table student [partition (partcol1=val1,…)];

2. insert插入数据

   • 需要先创建结果表

insert into table 表名
select 语句;

3. create插入数据

-- 根据查询结果创建表
create table 表名 as select 语句;

4. hdfs客户端直接上传文件

   • 由于Hive表的数据实际存储在HDFS的目录中
   • 所以可以直接将与表结构相符的文本数据直接通过hdfs客户端上传到表对应的路径中

-- 先查询表所在的路径
desc formatted t_log3;
-- 使用hdfs命令行客户端进行put操作

• 对于Hive的分区表，如果手动创建分区目录或者使用flume之类的数据采集工具创建Hive分区目录，会导致mysql种没有相关的分区信息，所有在查询时需要先修复表的分区结构

-- 修复表的分区
msck repair table 表名;

Hive数据导出

1. 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';

2. hdfs的get操作进行数据导出

3. 使用hive执行查询语句 将输出流重定向到文件
   hive -e '查询语句' > 文件

4. 使用导入导出关键字进行表的备份

-- 导出备份
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操作中使用自定义函数

• 自定义函数实现日志中操作系统类型的提取和日期格式转换

1. 创建maven工程引入hive-exec依赖

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>2.3.9</version>
</dependency>

2. 编写Java类 继承 org.apache.hadoop.hive.ql.exec.UDF

3. 实现方法evaluate，实现函数逻辑

4. 将编写好的工程打成jar上传到集群

   • 上传jar时注意需要上传到HDFS上，因为Hive底层运行MR时需要所有机器的线程都需要读取udf函数的class文件，如果jar包存储在linux本地目录，需要将jar复制到所有机器的相同路径

5. 在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区别

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中如果强转失败会赋值为NULL
cast(对象 as 目标类型)

4. Hive的分区表

• Hive的数据处理中通常会将每天产生的增量数据使用Flume等自动采集手段将数据直接写入到HDFS中，Hive将HDFS的数据映射为表结构，使用HQL进行HDFS上数据的SQL处理

• 分区表 使用将一个大表分成多个子目录的形式，将数据按分区条件划分成多个部分，在查询时可以直接指定查询条件用来定位子目录，避免加载全量数据

• 场景

  1. 将0601、0602、0603、0604、0605加载到hive表中
  2. 查询6月2日数据中有多少次404访问
     由于Hive底层运行MR时，表只有一个目录，读取数据时直接将输入路径指定到表所在的HDFS目录，就会将表的所有文件加载到MR中，如果保存的不是5天而是50年的数据，MR任务依然会每次加载全表数据
  3. 创建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中方便可视化查询