数据仓库工具 hive，查缺补漏

文章内容输出来源：拉勾教育大数据训练营；

---

数据仓库工具 hive

hive的执行引擎：MapReduce / Tez / Spark / Flink

hive的安装

安装包准备：

apache-hive-2.3.7-bin.tar.gz

mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

mysql-connector-java-5.1.46.jar

节点规划：

安装 mysql

在134节点上安装

1、删除有冲突的依赖包

查看依赖：rpm -qa | grep mariadb		（mariadb是mysql的分支版本）
删除依赖：rpm -e --nodeps mariadb-libs

2、安装所需依赖

yum install -y perl
yum install -y net-tools

3、上传到 /opt/lagou/software 并解压 mysql 压缩包

tar -xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

4、依次安装

rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm

5、启动 mysql

查看状态：systemctl status mysqld
启动：systemctl start mysqld

6、查看 mysql 的 root 初始密码 （我的是MoxCuW6g%SoU）

grep password /var/log/mysqld.log

7、进入 mysql

mysql -uroot -pMoxCuW6g%SoU

8、修改 root 用户的密码

查看密码规则：show variables like "%password%";
设置密码强度：set global validate_password_policy=0;
设置密码长度：set global validate_password_length=5;
修改密码：set password for 'root'@'localhost'=password('12345678');
刷新：flush privileges;

密码强度0：密码长度不少于8位

密码强度1：密码长度不少于8位，要有数字、大写字母、小写字母、特殊符号

密码强度2：不能存在字典文件中

9、重新登录验证：

退出mysql：exit;
登录：mysql -uroot -p12345678

10、创建 hive 用户

create user 'hive'@'%' identified by '12345678';

11、给 hive 用户授权

grant all on *.* to 'hive'@'%';
flush privileges;

12、用 hive 身份登录 mysql

exit;
mysql -uhive -p12345678;
show databases;

安装 hive

1、上传到 /opt/lagou/software 并解压 hive 压缩包，修改软件名

tar -zxvf apache-hive-2.3.7-bin.tar.gz -C /opt/lagou/servers
mv apache-hive-2.3.7-bin hive-2.3.7-bin

2、配置环境变量 vim /etc/profile

#HIVE_HOME
export HIVE_HOME=/opt/lagou/servers/hive-2.3.7-bin
export PATH=$PATH:$HIVE_HOME/bin

要 source /etc/profile

3、配置 $HIVE_HOME/conf/hive-site.xml

cd $HIVE_HOME/conf
vim hive-site.xml
添加如下内容：
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
  <!-- hive元数据的存储位置 -->
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://linux134:3306/hivemetadata?createDatabaseIfNotExist=true&amp;useSSL=false</value>
    <description>JDBC connect string for a JDBCmetastore</description>
  </property>
  <!-- 指定驱动程序 -->
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBCmetastore</description>
  </property>
  <!-- 连接数据库的用户名 -->
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>hive</value>
    <description>username to use against metastoredatabase</description>
  </property>
  <!-- 连接数据库的口令 -->
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>12345678</value>
    <description>password to use against metastoredatabase</description>
  </property>
</configuration>

4、把 mysql-connector-java-5.1.46.jar 放到 hive 的 lib 目录下

5、初始化元数据库

schematool -dbType mysql -initSchema

6、验证

登录 mysql 的 hive 用户，查看是否多了一个 hivemetadata 库（库名是 hive-site.xml 中配置的）

mysql -uhive -p12345678
show databases;

7、启动 hive，启动前先开启 hdfs 和 yarn ，并且要等待安全模式结束后才能进入hive

hive的常见属性配置

属性配置修改的是 hive-site.xml 文件

数据的存储位置，默认使用的是 hdfs 路径

  <!--hive数据的存储位置-->
  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
  </property>

显示当前库名

  <!--显示当前正在使用的库名-->
  <property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
  </property>

显示表头

  <!--显示表头信息-->
  <property>
    <name>hive.cli.print.header</name>
    <value>true</value>
  </property>

使用本地模式

  <!--测试时使用本地模式-->
  <property>
    <name>hive.exec.mode.local.auto</name>
    <value>true</value>
  </property>

本地模式的开启条件要满足以下三个：

-》hive.exec.mode.local.auto.inputbytes.max 默认128MB 数据量大小

-》hive.exec.mode.local.auto.tasks.max 默认4个 map的启动个数

-》reduce个数为0或1

hive 的 log 日志文件

hive的日志默认存放在 /tmp/root/hive.log

如果要修改位置：

cd $HIVE_HOME/conf
cp hive-log4j2.properties.template hive-log4j2.properties
vim hive-log4j2.properties
修改 property.hive.log.dir 的内容，并且给对应目录777权限

hive的分隔符

在linux中要打出 ^A ，要按 <ctrl>+v+a

hive表的创建

常规建表

例如有如下格式的数据

2;zhangsan;book,TVcode;beijing:chaoyang,shanghai:pudong
3;lisi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

建表语句

create table t1(
  id int,
  name string,
  hobby array<string>,
  addr map<string,string>
)
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':'
;

加载数据到表中

load data local inpath '/opt/lagou/datas/test1.txt' into table t1;

查看表数据

select * from t1 ;

查询结果

t1.id	t1.name	t1.hobby	t1.addr
2	zhangsan	["book","TVcode"]	{"beijing":"chaoyang","shanghai":"pudong"}
3	lisi	["book","code"]	{"nanjing":"jiangning","taiwan":"taibei"}
4	wangwu	["music","book"]	{"heilongjiang":"haerbin"}

分桶表

创建分桶表语法示例

create table t_course(
  id int,
  name string,
  score int
)
clustered by(id) into 3 buckets
row format delimited fields terminated by '\t';

给分桶表填充数据，数据会根据分桶字段的 hash 值，对 bucket 的数量进行取模

只能通过 insert 的方式

insert into table t_course 
select * from course_common;

如果是分区表，必须指明分区条件

hive数据导入

load

加载本地数据

load data local inpath '本地路径' 
into或overwrite into 
table 表名 
partition(分区条件);

加载hdfs上的数据时，非复制数据而是移动数据

insert

insert overwrite或into 
table 表名
partition(分区条件)
select * from 来源表

create

create table 表名 as
select * from 来源表

import

通过 export 方式导出的数据，可以通过 import 方式导入

-- 先复制表结构
create table 新表名 like export的表名 ;

-- 再向表中填充数据
import table 新表名 from 'export导出的数据的hdfs路径';

create table like 和 as 的区别：

like 可以完整的复制表结构，包括分区字段

as 无法复制到分区字段

hive数据导出

insert

没有 into，只有 overwrite

insert overwrite local directory '/opt/lagou/datas'
select * from 表名;

格式化导出方式到hdfs

insert overwrite directory 'hdfs目录'
row format delimited 
fields terminated by '\t'
select * from 表名;

get

hive命令行

可通过 desc formatted 表名 来查看hdfs的数据存放路径

hive> dfs -get hdfs路径 本地路径

查询结果重定向

hive -e "select * from 表名;" > xxx.log

export

export table 表名 to 'hdfs路径';

rlike正则表达式

案例：找出姓名以 A 或者 S 开头的学生记录

select * from student s
where s.name rlike '^(A|S).*'
;

日期函数

当前时间

select current_timestamp();

当前日期

select current_date;

时间戳

select unix_timestamp();

时间戳转日期

select from_unixtime(unix_timestamp(),'yyyy/MM/dd HH:mm:ss');

日期差函数

select datediff(日期1,日期2);

某日期是该月份的第几天

select dayofmonth(日期);

某月的最后一天

select last_day(日期);

日期加一天

select date_add(current_date,1)

某月的第一天

select date_sub(current_date,dayofmonth(current_date)-1);
select add_months(date_add(last_day(current_date),1),-1);

月份加1

select add_months(current_date,1);

字符串转时间格式

select to_date('2020-02-28');

日期格式化

select date_format('2020-08-21','yyyy/MM/dd HH:mm:ss');

获取季度

select case when month(current_date) <=3 then 1
            when month(current_date) <=6 then 2
            when month(current_date) <=9 then 3
            else 4 end quarter ;
或
select floor(month(current_date)/3.1)+1 as quarter ;

窗口函数

windows子句

over( partition by aaa order by bbb rows between … and … )

语法 描述
unbounded preceding 窗口内的第一行数据
n preceding 当前行往前第 n 行数据
current row 当前行数据
n following 当前行往后第 n 行数据
unbounded following 窗口内最后一行数据

序列函数

lag( col ) over( … )

语法 描述
lag(aaa) 当前行的上一行数据，如果上一行没有数据显示为NULL
lag(aaa,2) 当前行的上一行的上一行数据
lead(aaa) 当前行的下一行数据
lead(aaa,2) 当前行的下一行的下一行数据
first_value(aaa) 窗口中截止到当前行的第一行数据
last_value(aaa) 窗口中截止到当前行的最后一行数据，即自身
ntile(n) 将每个窗口的数据分成 n 份

自定义函数

UDF 一进一出

需求：拓展 nvl 功能为 null、’‘和’ ’

1、编写代码

导入 maven 依赖

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

编写代码 ，extends UDF

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public class nvl extends UDF {
    public Text evaluate(final Text x,final Text y){
        if( x==null || x.toString().trim().length()==0 ){
            return y;
        }
        return x;
    }
}

2、打包上传到 linux

3、进入 hive 命令行，添加 jar 包

add jar /opt/lagou/jar/xxx.jar

4、临时/永久注册成为 hive 函数

create temporary function mynvl as '包全类名';

删除函数：drop function mynvl ;

查看函数：show functions ;

UDAF 多进一出

UDTF 一进多出

hive 的事务

原子性 Atomicity 事务是最小的工作单位，单位中的操作要么全部成功，要么全部失败

一致性 Consistency 不论有多少个事务，数据库的状态是一致的

隔离性 Isolation 事务之间不会相互影响，我买东西时，别人也可以买，且不会影响到正在买的我

持久性 Durability 提交的数据永久存在

hive事务的限制：
只能进行行级别的 ACID 语义

自动提交，没有 commit 、rollback、begin

只支持ORC格式的文件

hive事务默认关闭，要手动开启

表的事务属性要开启 transactional = true

表必须是分桶表

分桶字段的值不可被修改

表必须是内部表

必须使用事务管理器 org.apache.hadoop.hive.ql.lockmgr.DbTxnManager

只支持快照级别的隔离

hive开启事务功能

hive.support.concurrency=true
hive.enforce.bucketing=true
hive.exec.dynamic.partition.mode=nonstrict
hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager

表要store as orc
表的tblproperties('transactional'='true')

hive 元数据管理与存储

metastore 是元数据服务

内嵌模式

jvm：driver + metastore + derby

本地模式

jvm1：driver1 + metastore1

jvm2：driver2 + metastore2

jvm3：mysql

存在问题：

如果 mysql 的端口号更换，或者密码更换，需要手动修改 hive-site.xml 里的配置，如果集群节点很多，修改起来不方便

密码直接被暴露在 hive-site.xml 文件中，不安全

driver1只能通过 metastore1 进行元数据服务，如果 metastore1 元数据服务挂了，driver1就不能用了

远程模式

driver1 可以使用 metastore1 也可以使用 metastore2

hive可以安装在多台节点上，metastore服务只需要两个节点，修改密码或端口信息时只需要改两次即可

配置远程模式

节点规划：

1、将 134 节点的 hive 和 /etc/profile 文件分发给 131 和 132

2、在 131 、 132 和 134 节点

yum install lsof -y

3、在 131 和 134 节点启动元数据服务

nohup hive --service metastore &

4、查看端口占用情况

lsof -i:9083

5、修改 132 节点的 hive-site.xml 文件

删除：
  <!-- hive元数据的存储位置 -->
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://linux134:3306/hivemetadata?createDatabaseIfNotExist=true&amp;useSSL=false</value>
    <description>JDBC connect string for a JDBCmetastore</description>
  </property>
  <!-- 指定驱动程序 -->
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBCmetastore</description>
  </property>
  <!-- 连接数据库的用户名 -->
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>hive</value>
    <description>username to use against metastoredatabase</description>
  </property>
  <!-- 连接数据库的口令 -->
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>12345678</value>
    <description>password to use against metastoredatabase</description>
  </property>

添加：
  <!-- hive metastore 服务地址-->
  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://linux131:9083,thrift://linux134:9083</value>
  </property>

6、在 132 节点登录 hive 验证使用

HiveServer2服务

基于 thrift RPC 的服务，可通过 java、c、python 调用，跨平台+跨语言

节点规划

1、stop-dfs.sh

2、配置 131 、132、134 的 /opt/lagou/servers/hadoop-2.9.2/etc/hadoop/core-site.xml

添加：
  <property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
  </property>

  <property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
  </property>

3、配置 131 、132、134 的 /opt/lagou/servers/hadoop-2.9.2/etc/hadoop/hdfs-site.xml

添加：
  <property>
    <name>dfs.webhdfs.enabled</name>
    <value>true</value>
  </property>

4、start-dfs.sh

5、启动 134 的 HiveServer2 服务

nohup hiveserver2 &

hiveserver2 的 web 页面：linux134:10002

6、在 132 启动 beeline

! connect jdbc:hive2://linux134:10000   一直回车

需要保证/tmp目录的权限是开放的，否则报权限问题 ： hdfs dfs -chmod -R 777 /tmp

HCatalog元数据服务

允许不同的工具如 pig 、mapreduce 等通过 HCatalog 直接访问存储在 HDFS 上的底层文件

使得第三方应用可以直接从 Hive 的数据仓库中读写数据

hcat 只接收不产生 MR 的任务

进入 hcat 命令行：$HIVE_HOME/hcatalog/bin/hcat

Hive的存储格式

默认为 TextFile

行存储：TextFile、SequenceFile

列存储：RCFile、ORCFile、parquet

工作中常用格式：TextFile、ORCFile、parquet

TextFile：常用于将文本文件内容转换为 ORC、parquet 等格式的表数据

ORC：有事务型操作时必须用 ORC

parquet：可被多种计算框架和查询引擎

hive优化

hive架构优化

表的设计阶段就需要考虑的优化

1、更换hive的执行引擎 hive.execution.engine 参数

MR引擎：每个job都会落地一次，频繁产生IO影响速度

Tez引擎：不会在中间过程落地

2、更换优化器

Vectorize 向量优化器

必须为Tez引擎，存储格式必须为ORC，一次计算1024行的数据（MR一次计算一行）

set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled=true;

Cost-Based Optimization 成本优化器

通过解析各语段的执行成本，自动优化成最高效的执行计划，例如大小表join在0.7版本之后谁在前都可以，都会被自动优化

set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;

定期执行analyze进行成本分析，分析后的信息会存放在元数据库中

3、合理设计分区表

如果数据过大，按什么分区

地域：国家、省份、城市

时间：年、月、日、小时

业务逻辑：部门、销售区域、目标客户

4、合理设计分桶表

如果join的两个表的分桶结构是一样的，可以提高join的性能

分桶的字段要一致

两个表的分桶数量需要是倍数关系

5、选择合适的文件格式

6、开启 map 到 reduce 过程的数据压缩 compress

需要综合考虑压缩占用的时间

hive参数优化

1、开启本地模式

数据量小并且文件个数少时自动开启本地模式，节省集群资源和通信时间

2、开启严格模式

不允许某些语句的执行

产生笛卡尔积

order by 必须加 limit 

查询分区表必须有分区条件

3、开启jvm重用

同一个job内的不同task阶段可以重用jvm

如果job内的task很多，可以设置重用的次数

set mapreduce.job.jvm.numtasks=5;

如果job内的某个reduce task执行时间很长，会一直占用插槽

4、开启并行执行

set hive.exec.parallel=true
set hive.exec.parallel.thread.number=16

并行执行占用的资源会增加，但时间比串行快

5、推测执行

如果 节点A 的 map task 任务是慢任务，会在 节点B 开启一个与 节点A 相同的 map task，谁先执行完成，就关闭另一个节点的任务

set mapreduce.map.speculative=true;
set mapreduce.reduce.speculative=true;
set hive.mapred.reduce.tasks.speculative.execution=true;

6、合并小文件

在map执行前合并可以减少map数

在mapreduce结束时合并可避免生成小文件

set hive.merge.mapfiles=true;
set hive.merge.mapredfiles=true;
set hive.merge.size.per.task=268435456;
set hive.merge.smallfiles.avgsize=16777216;

7、fetch模式

默认为more，不需要走MR任务的语句就不走MR任务

hive sql优化

1、列裁剪和分区裁剪

用 select 具体字段，代替 select *

查询分区表时加上分区条件可减少扫描的数据量

2、尽量用 distribute by + sort by 代替 order by

3、group by 代替 count (distinct)

4、group by 调优

开启map阶段的预聚合，可减少reduce阶段输入的数据量

set hive.map.aggr=true
set hive.groupby.mapaggr.checkinterval=10000

当发生数据倾斜时，开启负载均衡机制，此时会有2个job，第一个做负载均衡，第二个做结果聚合

set hive.groupby.skewindata=true

5、join基础优化

common join：会先对两个表进行shuffle，相同的 id 放到同一个数据块再匹配

map join是默认开启的，小表（默认25M）会加载到内存中，每个任务节点的内存中都拷贝一份，不需要先进行shuffle过程

0.7版本之前，需要小表在前大表在后

0.7版本谁在前都会被优化器优化为小表在前，小表的大小设置：
hive.auto.convert.join=true
hive.smalltable.filesize=25000000

0.8.1版本之后：
hive.mapjoin.smalltable.filesize=25000000

如果两个表都是大表，可以设计成桶个数相同的分桶表，因为分桶时已经按 id 进行了处理，也省了 shuffle 的过程

SQL面试题

1、查出连续 7 天登录的用户

with data as (
	select 
		 t.user_id
		,t.user_name
		,t.login_time
		,row_number() over(partition by user_id order by login_time) as rn
	from user_login t
)
select
	user_id,
	user_name,
	login_time,
	date_add(login_time, -rn ) as duration_flag
from data d ;

对以上结果统计 duration_flag 相同的数量
select 
	user_id,duration_flag,count(1) 
from 以上结果表 
group by user_id,duration_flag 
having count(1)>=7 ;

2、查询出每个班级成绩排名前 3 的学生，并新增一列显示与上一名相差多少分

数据： test2.txt 
id	class	score
1	1901	90
2	1901	90
3	1901	83
4	1901	60
5	1902	66
6	1902	23
7	1902	99
8	1902	67
9	1902	87

建表：
create table test2 (
 id int,
 class string,
 score int
)
row format delimited
fields terminated by '\t';

加载数据：
load data local inpath '/opt/lagou/datas/test2.txt' overwrite into table test2 ;

查询：
select
  t.*,
  nvl(score - (lag(score) over( partition by class order by score desc )) ,0) as diff_score
from (
  select 
    id,
    class,
    score,
    dense_rank() over( partition by class order by score desc ) as rank
  from test2
) t
where rank <=3
;
或者：
select * from 
(
  select
    id,
    class,
    score,
    dense_rank() over( partition by class order by score desc ) as rank,
    nvl(score - (lag(score) over( partition by class order by score desc )),0) as diff_score
  from test2
) t
where rank <= 3 ;

3、行转列

数据：test3.txt
id	tag	flag
a	b	2
a	b	1
a	b	3
c	d	6
c	d	8
c	d	8

要求结果：
id	tag	flag
a	b	2|1|3
c	d	6|8

create table test3(
  id string,
  tag string,
  flag int
) row format delimited fields terminated by '\t' ;

load data local inpath '/opt/lagou/datas/test3.txt' overwrite into table test3;

select id,tag,concat_ws('|',collect_set(cast(flag as string)))
from test3
group by id,tag;

4、行列置换

数据：test4.txt
id	course
1	java
1	hadoop
1	hive
1	hbase
2	java
2	hive
2	spark
2	flink
3	java
3	hadoop
3	hive
3	kafka

要求结果：
id		java	hadoop	hive	hbase	spark	flink	kafka
1		1		1		1		1		0		0		0
2		1		0		1		0		1		1		0
3		1		1		1		0		0		0		1

create table test4(
  id int,
  course string
) row format delimited fields terminated by '\t' ;

load data local inpath '/opt/lagou/datas/test4.txt' overwrite into table test4;

with data as (
select
  id,
  case course when 'java' then 1 else 0 end as java,
  case course when 'hadoop' then 1 else 0 end as hadoop,
  case course when 'hive' then 1 else 0 end as hive,
  case course when 'hbase' then 1 else 0 end as hbase,
  case course when 'spark' then 1 else 0 end as spark,
  case course when 'flink' then 1 else 0 end as flink,
  case course when 'kafka' then 1 else 0 end as kafka
from test4 
)
select 
  id,
  max(java) as java,
  max(hadoop) as hadoop,
  max(hive) as hive,
  max(hbase) as hbase,
  max(spark) as spark,
  max(flink) as flink,
  max(kafka) as kafka
from data 
group by id ;