实践Hive的点点滴滴

最新推荐文章于 2022-12-19 23:05:13 发布

置顶莫莫绵

最新推荐文章于 2022-12-19 23:05:13 发布

阅读量338

点赞数 1

本文链接：https://blog.csdn.net/qq_36428889/article/details/107507444

版权

大数据专栏收录该内容

4 篇文章 0 订阅

订阅专栏

遇到问题，解决问题。技术故事往往起于此。

遇见问题

如果业务突飞猛进，数据量猛增，出于对成本的控制，或许要考虑一下大数据组件了，To C的业务和To B的业务往往存在很大的差距。如何用当前可承受成本拿出适合的解决方案是首要思考任务。
我需要对相当大的表做查询，可是直接从生产MySQL上查询无疑会增加它的压力。数据量越来越多，MySQL使用成本逐渐增高，现在我需要寻求更好的解决方案。
首先想到的是Hadoop体系，它的解决方案是合理利用多个廉价的服务器来完成我的计算任务，这样我就不必花费大价钱买超高性能服务器。这就是Hadoop希望的``在这之前你可能会考虑以下问题。

数据存在哪里？既然我们是跨多个服务器的存储，必然要有个分布式的文件存储系统，Hadoop推荐你使用HDFS。
有了分布式文件存储系统，关系数据库（如MySQL）的数据怎么搞到HDFS里去呢？如果用户是一个专业的数据库管理员,专业到不想学Java，也就不想用Java API去操作Hadoop. 这时候他就会看到：Hive

简单概念

前面已经知道数据是存在HDFS里的。而Hive并不算是一个完整的数据库，它的核心功能是实现将SQL转换为MapReduce的任务进行运算。这使得完全不会Hadoop的同学，只要会写写SQL语句即可完成一些大数据离线查询计算功能。我们称这个SQL语句叫HiveSQL或HSQL，别担心，这不是一个新的语言，他和MYSQL语句差不了多少。
不过遗憾是，你不能像使用MySQL一样操作Hive。也没有MySQL给你提供的引擎查询优化。不像普通的关系型数据库，大数据通常不允许做行级的记录增删改操作。Hive一般是用作于维护海量的数据，对静态的数据慢慢的做分析，形成报表啊啥的。如果你的业务需要实时修改数据，你应该考虑考虑其他的组件了，比如HBase。

可以怎么用？

很多也业务希望对一些数据做统计，不需要实时，可以包容到第二天才统计输出给用户，如日报表等。一般我们需要的原始数据在类似MySQL的关系型数据库，那么每天凌晨（根据实际情况）就可以跑定时任务的将昨日数据迁移到hive上，每天的数据可以按照日期（或其他）做一个分区。迁移完之后接着我们可以预估迁移完成的时间(当然也可以使用hue管理起来，串行的跑)，采用定时任务在hive的服务器上写定时脚本执行SQL统计当天分区数据输出到统计表，再预估其完成时间将统计表结果迁移回MySQL.保证第二天用户正常的显示。
当然你可以对过往的数据做一些统计分析等。
以上我们不依赖Java或其他应用来处理大数据，这对于某些业务是有好处的，至少我们使用大数据查询统计的时候不用关心生产应用，使用hive也就可以不用开发工程师的参与，节约成本，但是当你的公司逐渐变大的时候，管理维护大量的脚本绝对不是个好事，这时候就可以不采取这种方式了。

如果你的目标是想在实践中操作hive，那么下面的这些命令还是要会的，当然你可能正在使用CDH,或者是使用Hue等等，它们都能让你更快的接触到实战Hive。之后你可能考虑的问题就是根据具体的业务查询了，如果你使用MapReduce引擎计算，你必须了解MapReduce的原理，否则你将很难写出优秀的SQL，并且在对几个小时才能查询出结果的MapReduce嗤之以鼻。
有时候我们可以尝试使用基于内存的Impala，不过要特别注意监控它的运行情况。目前CDH5版本我没有找到可以配置Impala的限制内存地方，如果你认为你的查询不需要太多内存，你可以方心大胆的使用它，它会比用MapReduce查询快上甚至几十倍。

这里给出一些简单的hive操作。

简单客户端命令

在装有hive客户端的服务器上，使用命令hive即可访问hive库，注意之后的命令都要加;号执行哦。
首先我们可以查看下数据库show databases;,
之后我么可以选择使用某个数据库use database_name;
然后显示下show tables;
查看表结构 desc table_name;
查看表创建语句show create table database_name.table_name;
创建库语句：CREATE DATABASE database_name;
有时候我们希望不进入客户端执行命令，比如写脚本执行hql
不进入客户端一次性执行.sql或.hql文件：hive -f /xxx.sql
不进入客户端执行语句:hive -e "select db.xx ..."
如果返回的数据量过多，可以将结果输入文件中，使用-S开启静默模式 hive -S -e "select ... limit 20" >> /xx/xx.log
hive客户端可以使用dfs命令如dfs -ls /;
hql脚本中使用 --标识注释

在hive 进入客户端之前，会自动执行$HOME/.hiverc 文件中的命令。

使用自定义变量：--define key=value 或者--hivevar key=value 这个值会放到hivevar命名空间
可以使用set命令显示或者修改变量值
使用set设置或替换变量值： set --hivevar:test=bar;
命名空间：
hivevar : 可读可写，用户自定义变量
hiveconf ： Hive相关配置属性
system： Java定义的配置属性
env：shell环境定义的环境变量

创建Hive表

hive创建无分区内部表

CREATE TABLE `table_name`(
  `id` string COMMENT '主键ID',
  `phone` string COMMENT '号码',
  `stat_time` timestamp COMMENT '统计时间',
  `total_count` int COMMENT '总量'
  )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

简单说明

这里我们创建的表信息属于hive的元数据，元数据通常是存在MySQL这样的数据库中的（可以配置），以方便hive表元数据的修改。我们要告诉hive存储数据的格式：这里存储为一个文本文件（如果需要压缩可以使用：STORED AS SEQUENCEFILE），使用一个统一的分隔符分割数据很重要，ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 这里使用的是\t 分隔，读者可根据实际情况选择自己的分割符号。
你还可以使用like关键字对一个表结构做复制（不复制数据）。

hive创建分区内部表

CREATE TABLE `table_name`(
  `id` string COMMENT '主键ID',
  `phone` string COMMENT '号码',
  `stat_time` timestamp COMMENT '统计时间',
  `total_count` int COMMENT '总量'
  )
PARTITIONED BY (`date_time` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

简单说明

PARTITIONED BY (date_time string) hive有分区表的概念，根据具体的业务可选择不同的分区方式，这里按照日期来分区，hive在创建表后会自动在尾部增加分区字段，这里是分区的字段名为date_time.
同样你可以创建外部分区表，这里不多说。

hive创建分区

ALTER TABLE database_name.table_name ADD IF NOT EXISTS PARTITION (date_time='${hiveconf:input_date}');

这里使用当前的日期创建分区，这是常用的方式。我们可以设定一个定时任务，根据具体的业务，比如:我们可以每天定时的来执行这个语句，达到每天都创建一个新的分区的目的。

hive创建外部表

Hive创建的表包括内部表和外部表，外部表需要使用关键字EXTERNAL修饰。两者区别是：内部表默认会把数据存在hive.metastore.warehouse.dir配置的位置文件夹中，其删除表操作会删除对应的数据，而外部表删除操作仅删除元数据信息。
一般情况下，我们会默认配置Hive从HDFS的某个路径中读取数据，如果我们需要从文件系统的其他地方拿数据做统计怎么办？比如我们可能使用Pig维护一些数据，但是这些数据我突然想用hive来做些计算查询，那我们可以创建一个外部表，将其指向Pig存放数据地址。在hive中你只具有使用权限，没法删除其数据。
我们只需要在创建表的时候给其加上location指定读取数据地址即可。

CREATE EXTERNAL TABLE IF NOT EXISTS `table_name`(
  `id` string COMMENT '主键ID',
  `phone` string COMMENT '号码',
  `stat_time` timestamp COMMENT '统计时间',
  `total_count` int COMMENT '总量'
  )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/other/data/path';

新增、加载数据

Hive不支持行级的增删改，我们只能一次写入数据，overwrite关键字会在加入之前删除之前的数据即覆盖,如果是分区表，那之前的分区内容也会被覆盖。虽然我们无法删除部分数据，往往会采取这种覆盖的方式来达到删除一些数据的目的。

csv文件上传数据到Hive

load data local inpath ‘服务器绝对路径/data_info.csv’ into table table_name;
-- 加载数据并覆盖已有数据
load data local inpath ‘服务器绝对路径/data_info.csv’ overwrite into table table_name;

当然你也可以使用其他文件。实际上这里的应用与备份或转移数据。

备份数据

分区表数据备份

insert overwrite local directory '${backupdir}/${database}/${tablename}/${dateTime}'  
row format delimited fields terminated by '\t' 
select * from ${database}.${tablename} where ${tablepartitionfield}=${dateTime};

以上${backupdir}表示将数据备份到某个文件夹里，如果你的数据量很大，注意挂载下盘，扩张其容量大小。可以使用阿里云nas盘挂载上去。
分区表数据导回
上面我们把数据备份了，接下来如果要导回分区表同样给你一个解决方案：写脚本
脚本的核心代码如下，其他变量的部分自己研究吧。

for dateTime in ${tablepartitiondatetime}
    do
        echo "开始插入${database}.${tablename}.${dateTime}的数据到${backupdir}目录下"
        tsql="LOAD DATA LOCAL INPATH '${backupdir}/${database}/${tablename}/${dateTime}' OVERWRITE INTO TABLE callai.${tablename} partition(${tablepartitionfield}=${dateTime})"
        hive -e "${tsql}"
    done

如果你会了上述，未分区表的备份及导入也就没啥难度了。

迁移到MySQL

实际上，这并不是属于Hive的功能，只不过如果你不知道如果把MySQL的数据迁移到Hive中，那么你可能直接就萎了。实际上这样的迁移工具有很多，我比较熟悉的使用datax,sqoop，这是很容易做到的。如果你需要定时迁移，可以使用crontab 去执行脚本
当你安装好datax之后，只需要找个目录写一个json文件，然后将执行命令配置到脚本里，执行即可。这里不详细说明，这部分网上资料很多，自己研究哈。

查询插入

这里我们常在版本更新中会新增字段，那么旧的hive数据就缺失了那个字段。这样在迁移的时候可能导致字段缺失异常。那么我们需要将旧的数据加上字段值，这里简单处理加为NULL。overwrite关键字会将之前的数据覆盖处理。后面更新章节的时候也会说到。

查询插入的一般情况：

insert overwrite table table_name [partition (time = '20200907') select * from 
table_name2 t2 where t2.filed_name = 'xx';

如果使用追加方式，不要加overwrite, 可以使用into

insert into database_name.table_name(字段...) select * from where xxx

查询

简单的客户端查询命令

-- 查询表详细信息
describe extended xxdb.table_name;
-- 显示表结构，包括数据存储位置
show create table xxdb.table_name;

hive查询分区

-- 显示分区信息
show partitions tablename;
-- 根据分区查询数据
select table_coulm from table_name where partition_name = '2020-08-25';
-- 联合查询多个分区
select * from table_name where partition_name = '2020-08-22' union all select * from table_name where partition_name = '2020-08-23';

hive查询简单优化

hive所使用的SQL语句不会自己进行优化，因此对于查询时优化SQL时必要的。使用EXPLAIN关键字查看你的语句是怎么MapReduce的，下面给出几个常用语句的优化方式：

我们在查询的时候经常会想要去重操作：最简单的如

select count (distinct fileld_name) from table_name where xxx;

查询的时候初步估算下跑大约50亿以上的数据查询需要~起码到第二天了。
之后查阅资料发现采用distinct关键字的时候查询number of reducers等于1，这就没办法并行玩了。（order by关键字也是这样）
于是改用优化方式：使用子查询

select count(fileld_name) from ( select distinct fileld_name from table_name where xxx) t;

果然，效率提高了至少3倍以上。不过即便是如此40亿数据也查了半天，更好的优化肯定是有的，但是提升的大概也不太高。如果可能的话使用Impala会快很多很多倍。但是线上统计害怕爆内存，于是暂时先放弃了。

如果我们需要分组查询各个业务类型的去重数：(总不能执行 where 业务类型 = “xxx”，然后每个类型都执行吧。)

insert overwrite table table_name_temp select fileld_type_name，fileld_name，count(1) as num from table_name group by fileld_type_name，fileld_name；

这里我们将查询的结果写入一个新的表，然后我们再通过新的表统计就好啦。注意这里使用了group by语句，可能会出现数据倾斜的情况，可以尝试在sql前面加上 Set hive.groupby.skewindata = true

推荐一个比较可以理论的总结 Hive/HiveSQL常用优化方法全面总结
分区表的left join 优化

我们看一个简单的sql

select  t.name,t.id
    from database_name.table_name_one c
    left join database_name.table_name_one_two t on c.xx_id = t.id 
    where c.create_time >= '20210330'  and c.create_time <= '20210430' ;

其中table_name_one 是一个大的分区表，table_name_one_two是个小表，此时我们的目标是根据小表某个字段关联大表找到符合要求的数据拉取出来，但是如果直接执行这个sql你会发现mapper数非常大，执行太慢，磁盘IO飙升，原因在于这个sql在left join的时候其实是把两个表的所有数据拉出来匹配，实际上我们往往只需要大表的某个分区(create_time为分区字段)即可，优化如下：

select  t.name,t.id
    from database_name.table_name_one c
    left join (select name,xx_id from database_name.table_name_one_two where create_time >= '20210330'  and create_time <= '20210430' ) t on c.xx_id = t.id ;

只需要把where塞到join里就好，mapper数立马从一万个降低到4个，真是差之毫厘，谬之千里呀。
如果这两个表是一对多的关系，那么得到的数据会有重复，这时候做个分组就好

select  t.name,t.id
    from database_name.table_name_one c
    left join 
    (select name,xx_id from database_name.table_name_one_two 
    	where create_time >= '20210330'  and create_time <= '20210430' ) t 
    on c.xx_id = t.id 
group by t.name,t.id

至于为什么没有更多优化内容了呢？因为后来我一直在用Impala查询了。

更新

注意：hive的某些元数据（数据库名，数据库所在位置）信息是不可修改的,但我们可以增加删除字段。另外,如果你新增了字段，也仅仅是在元数据上做了修改表结构操作，那么旧的数据依然没有该字段的值,因此你的新数据和旧数据的字段数可能不一样，导致一些迁移失败问题。你可以insert overwrite table将旧的数据重新写入表。

hive新增表字段

业务中经常会在发布新版本的时候给统计表新增字段。
可以使用以下语句：

-- 新增表字段
alter table `database_name`.`table_name` add columns(`new_name` string COMMENT '新字段');

另外，如果你的表是分区表，上述操作需要指定分区。否则你可能只会在当前分区新增字段，而其他分区不会增加，你可以尝试cascade关键字

alter table `database_name`.`table_name` add columns(
`new_name` string COMMENT '新字段'
) cascade;

你还可以使用replace，注意这个操作会将你的表所有字段全部替换为replace columns()括号里面的字段,所以如果你仅仅想新增字段，也必须把旧的字段加上。记得使用desc table_name;检查你的表结构，确保它正确。

alter table `database_name`.`table_name` replace columns(
`new_name` string COMMENT '字段1',`new_name2` string COMMENT '字段2',
);

将自己的历史数据重新写入表，值默认为NULL。（这一点要自己在hive中做配置）

insert overwrite  table `database_name`.`table_name`  select * from 
`database_name`.`table_name`;

hive表数据去重(推荐方案2)

有时候我们各种瞎操作，搞完发现hive表中存在脏数据，要做去重操作。这里给出我的一些实践：
方案1：注意，这个方案时可能会改变表的结构，我在使用这个方案完，再使用迁移工具迁移的时候发现报错。原因可能是创建表的分隔符不同导致，因此这里还需要去将表格式改成和之前一样。或者在创建表的时候指明格式，下面语句没有写，请自行研究。

-- 首先创建一个新表将数据去重后写入,如果仅仅是根据字段去重distinct后加字段即可
create table table_bak as select distinct *  from table_name;
-- 然后将原有的数据表删除 注意这里不能用delete ）。
drop table if exists table_name;
-- 再重命名现有的表
ALTER TABLE table_bak RENAME TO table_name;
--修改表的格式。依据情况处理

方案2：先将该表重命名，再重新创建一个空表，和之前结构一样。然后将旧表数据去重后插入新表，旧的就不用了。

--
ALTER TABLE table_name RENAME TO table_name_bak;
insert into table_name(表字段)  select distinct * FROM table_name_bak；

其他更新操作

-- 表重命名
ALTER TABLE table_name RENAME TO table_name_bak;

删除

hive删除分区

alter table table_name drop partition(date_time='2020-07-22');

注意:删除后其实HDFS会生成一个Trash备份文件，如果你是真的不需要这些数据了，以方便释放磁盘，你必须把这个备份文件干掉。

真正删除

# 首先在装hdfs的节点服务器找到存hive表的位置
hdfs dfs -du -s -h /opt/cdh/hive/warehouse/xxx.db
# 在drop之后，会在目录下生成一个.Trash文件
hdfs dfs -du -h /user/root/.Trash
# 把这个文件删除，就真的没有了，慎重
hdfs dfs -rm -r /user/root/.Trash

哇哈哈哈

删库跑路

对于内部表，会删除表中数据和元数据。对于外部表仅删除元数据。

-- cascade表示如果数据库存在表就先删除表，然后删除库，温馨提示，慎重删库！
drop database name cascade; 

-- 数据表删除 注意这里不能用delete 注意drop table之后，表当中的数据依然保留在hdfs备份文件上，可以复原.把这个备份文件干掉就真的没了。
drop table if exists table_name;

在这里插入图片描述

本文写的不太多，仅仅是我几个月实践和学习的基础内容，学习的环境也有很多限制，有自己的实践，也参考了一些书籍，全篇没啥图片（因为懒），还有很多地方不完善，比如Hive的相关安装、配置，这里没有描述，因为方案有很多，我自己是使用的CDH,配置起来也比较简单。总之学的差不多了，先拿出来溜溜看吧。文章写的比较乱，多多包涵。讲道理你要是能看到这里，就说明你可能只看了这里。^-^.

水平有限，如果你觉得上述有任何疑问、不足、错误的地方，欢迎在评论区指正。