Hive

2021-11月-05 11:11 Views(471) 2105笔记 1

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

上传解压
修改配置文件
- 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中

配置环境变量

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

初始化元数据库

schematool -dbType mysql -initSchema

启动客户端

hive

select
  t1.ip ip,
  count(*) count
from(
   select
      split(line,' ')[0] ip
   from t_weblog) t1
group by t1.ip;

Hive操作

Hive客户端操作

使用shell命令

# hive终端使用!开头运行shell命令
hive>!clear;

使用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 ':';

数据类型
http://www.amoscloud.com/?p=1564
- hive中常用的类型
  int bigint double boolean
  map array
- 类型强转
  cast(原对象 as 目标类型)

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;

AmosCloud

Have a Question?

Hive

Hive

是什么

Hive架构

Hive安装

1. 安装MySQL

2.安装配置Hive

Hive操作

Hive客户端操作

DDL

关于数据库database

关于表 table

DML

DQL

Hive的函数

核心概念

外部表和管理表

分区表

分桶表