AmosCloud

Library

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Hive

Hive

是什么

Hive是一个基于Hadoop的数据仓库工具
用于管理存储在HDFS上的海量数据
Hive可以将HDFS数据映射为关系表
将用户编写的HiveSQL翻译成MapReduce任务提交到Hadoop执行

  • 优势
    使用流行度更为广泛的SQL作为开发语言 大大降低了分布式平台的学习和使用成本
    Hive将数据存储在HDFS中,并且使用MapReduce作为计算引擎,所以具备Hadoop的可扩展特性,可以处理海量数据
    Hive支持灵活的用户自定义函数,可以扩展HQL的处理能力

  • 缺陷
    HQL表达能力差,只能完成结构化数据的处理
    Hive性能较差,底层使用MR框架,优化粒度较粗

Hive架构

file

Hive安装

1. 安装MySQL

  • Hive使用关系型数据库存储meta数据

  • meta数据就是Hive中管理的库和表的结构信息

  • Hive自带一个轻量级的关系型数据库Derby,生产环境中将derby替换为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

2.安装配置Hive

  1. 上传解压

  2. 修改配置文件

    • hive-env.sh
# HadoopHome
HADOOP_HOME=/opt/hadoop-2.7.7
# Hive配置文件路径
export HIVE_CONF_DIR=/opt/hive-2.3.9/conf
# Jdk路径
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://host01:3306/hive</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>
  • 上传jdbc驱动jar包到hive的lib中
  1. 配置环境变量
echo 'export HIVE_HOME=/opt/hive-2.3.9'  >> /etc/profile
echo 'export PATH=$HIVE_HOME/bin:$PATH' >> /etc/profile
source /etc/profile
  1. 初始化元数据库
schematool -dbType mysql -initSchema
  1. 启动客户端
hive
select
  t1.ip ip,
  count(*) count
from(
   select
      split(line,' ')[0] ip
   from t_weblog) t1
group by t1.ip;

Hive操作

Hive客户端操作

  1. 使用shell命令
# hive终端使用!开头运行shell命令
hive>!clear;
  1. 使用hdfs命令
# hive终端使用dfs开头运行shell命令
hive> dfs -ls /;

DDL

关于数据库database

-- 查看包含哪些数据库
show databases;
-- 创建数组库
create database db2;
-- 查看数据库详细信息
desc database db2;
-- 删除数据库
drop database db2;
-- 递归删除数据库和库中的表
drop database db_test1 cascade;

关于表 table

-- 创建表
-- 创建   外部     表    是否存在       表名
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 [FIELDS TERMINATED BY ' \t  |  #$']
-- 设置集合的元素分割符
[COLLECTION ITEMS TERMINATED BY ',']
-- 设置map中key value的分割符
[MAP KEYS TERMINATED BY ':']
-- 设置原始数据中行之前的分隔符
[LINES TERMINATED BY '\n']
-- 设置表存储数据时使用的文件格式
-- 默认使用text
-- Hive中建议使用orc格式  SparkSQL建议使用parquet
-- orc和parquet都是列式存储的数据结构
-- 列式存储 占用空间较小 查询速度较快,是生产环境除原始表之外其他表必备的设置
[STORED AS file_format]
-- 设置表的存储位置
-- 生产环境通常会在建表时 指定表的路径到已经存在的文件路径
--   并且使用外部表,这样多个开发者可以同一份数据 
--   而且采集和处理解耦
[LOCATION hdfs_path]
  • 创建orc格式表
CREATE TABLE `t_weblog_info_orc`(
  `ip` string,
  `dt` string,
  `req_type` string,
  `res_code` string,
  `up` bigint,
  `down` bigint)
stored as orc;
  • 设置数据的格式
create table t_person2(
 name string, age int ,skill map<string,int>
)row format delimited fields terminated by ' '
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

DML

  • 从HDFS或者本地文件加载数据到表中
    • load data
load data [local] inpath 'hdfs/local path' into table 表名;

load data inpath '/logs/access.log-20211001' into table db_test1.t_weblog;
load data inpath '/logs/access.log-20211002' into table db_test1.t_weblog_ext;
  • 通过已经存在的表使用select插入到新的表中
create table 表名
行格式 表文件格式
as select 子查询
insert into table 表
select 查询
  • 创建外部表 同时指定表读取的文件位置
create external table 表名(字段)
location 'hdfs:// path'
  • 根据自定义格式导出数据到磁盘(本地/HDFS)
insert overwrite [local] directory '/root/weblog_info'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from t_weblog_info;
  • 导入导出可以使用hive自带的命令
export table [partition(指定分区)] 表 to 'hdfs路径';
import table [partition(指定分区)] 表 from 'hdfs路径';
  • 截断表
truncate table 表;
-- 截断表等价于 将表删除 再使用相同的表结果创建新的表
-- 数据库中用于清空表数据
-- Hive中只有管理表可以被truncate

DQL

SELECT
  [ALL | DISTINCT] select_expr,     select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
-- Hive中的order by 相当于底层的MR只启动1个reduce实例
-- 会将所有的数据在同一个reduce中进行全局排序,可能造成数据倾斜
  [ORDER BY col_list]
-- 如果
-- DISTRIBUTE BY 字段1 SORT BY 字段1
-- 可以直接简写为 CLUSTER BY 字段1
-- 区别是CLUSTER BY 只能进行升序排列
  [CLUSTER BY col_list
    |
-- DISTRIBUTE BY设置将数据按照某些字段进行分区(底层MR程序的分区)
  [DISTRIBUTE BY col_list]
-- SORT BY设置底层MR程序 分区内部按照哪些字段排序
  [SORT BY col_list ASC/DESC]]
 [LIMIT number]

Hive的函数

  • Hive于标准SQL不同的是,主要用于数据的处理,所以提供了丰富的函数库进行各种操作
# 查询所有函数
show functions;
# 查看某个函数的用法
describe function 函数名;
# 查看函数的详细用法
describe function extended 函数名;
  • 学习HiveSQL函数的方式

    1. 直接使用命令行查看案例 练习

    2. 查看官网文档练习
      https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringOperators

    3. 查看第三方博客练习
      https://www.cnblogs.com/yuejiaming/p/10572358.html#14_date_add

  • 学习程度
    自己将日期相关和字符串相关的所有函数至少操作一遍,留下印象。

  • UDF用户自定义函数
    Hive已经提供了大量函数,但是在数据处理中有一部分操作依然无法覆盖到,Hive允许开发者使用Java语言编写具体的函数功能,并将函数注册到Hive的函数库

    1. 创建maven工程
    2. 引入 hive-exec依赖
    3. 定义一个类继承UDF类
    4. 定义一个方法evaluate()在方法中接收需要处理的参数,并将处理结果返回
    5. 将工程编译成jar,上传到HDFS
    6. 在Hive中注册并使用

-- 创建自定义函数
-- 通常使用临时创建的方式
CREATE TEMPORARY FUNCTION my_date_format
AS 'com.xahj.bd2105.hive.udf.DateFormatUDF'  USING JAR 'hdfs://host01:8020/udf_jars/hive-udf.jar';

-- 移除自定义函数
drop function 函数名;

核心概念

外部表和管理表

区别 内部表(管理表) 外部表
创建 不需要使用external 需要添加external
删除 同时删除元数据和hdfs上表文件 只删除元数据
使用场景 一些需要自己管理生命周期的测试表或者中间结果表 生产环境所有公共数据默认使用外部表

分区表

  • 需求
    在访问日志表中查询10月4号访客数量
select
 count(distinct t1.ip) count
from (
select
 split(line,' ')[0] ip,
 substr(split(line,' ')[3],2,11) `date`
from
  t_weblog_ext_par where dt='20211001'  ) t1
where t1.`date`='04/Oct/2021';
  • 上面的场景中 底层的MR会直接将t_weblog_ext的目录作为输入目录
  • 每次都会加载全表数据进入MR 效率极低

FileInputFormat.setInputPath(new Path("/table/dt=20211001"))

  • Hive中对表进行分区 本质上就是根据条件将表划分成多个子目录
-- 创建包含分区的表 将数据按照日期放在不同的分区(hdfs文件夹)
create external table t_weblog_ext_par(line string)
partitioned by (dt string);

-- 将数据加载到对应的分区
load data local inpath '/root/log/access.log-20211003' into table t_weblog_ext_par partition (dt='20211003');

-- 查询分区表数据时直接指定分区作为where条件
select
 count(distinct t1.ip) count
from (
select
 split(line,' ')[0] ip
from
  db_test1.t_weblog_ext_par where dt='20211001' ) t1;
  • 还可以根据需要设置多级分区
create external table t_weblog_ext_par1(line string)
partitioned by (`year` string,`month` string ,`day` string);

load data local inpath '/root/log/access.log-20211003' into table t_weblog_ext_par1 partition (year='2021',month='10',day='03');

分桶表

  • 如果一个表中包含一个大文件(GB、TB),而查询时需要进行按照某个字段的排序操作,全局排序需要消耗大量的资源
  • 如果将表的数据提前排序,并分割成多个小文件(GB、MB)
  • Hive支持在创建表时 指定按照某些字段进行文件的划分和预排序
    • 分桶表的优势,只需要进行一次桶的切割,就可以优化后期的所有查询
    • 劣势, 分桶表无法直接创建,load data也无法将数据自动进行桶的划分,需要提前创建中间结果表,再从中间结果表将数据查询插入到分桶表
      insert into tbl_bucket select * from 中间表
      会占用一部分中间结果表的空间
-- 清洗原始表 ,将结果保存到中间结果表
create table t_weblog_info as
select
   t1.strs[0] ip,
   substr(t1.strs[3],2) dt,
   substr(t1.strs[5],2) req_type,
   t1.strs[8] res_code,
   cast(t1.strs[9] as bigint) up,
   cast(t1.strs[size(t1.strs)-1] as bigint) down
from(
   select split(line,' ') strs from db_test1.t_weblog_whole
   )t1;
create table t_weblog_info_bucketed(
 ip string,
 dt string,
 req_type string,
 res_code string,
 up bigint,
 down bigint
)
CLUSTERED BY (res_code)
SORTED BY (up desc) INTO 5 BUCKETS;