大数据面试总结Hive

下一篇 Kafka  https://blog.csdn.net/Mr_Fjl/article/details/97367929

Hive是基于Hadoop的一个数据仓库处理工具&＃xff0c;是一种数据库技术&＃xff0c;用于查询和管理存储在分布式环境下的大数据集&＃xff0c;可以定义数据库和表来分析结构化数据&＃xff0c;适合处理相对静态的海量的数据集。可以将结构化的数据映射为一张数据库表&＃xff0c;提供简单的SQL的查询功能&＃xff0c;将SQL语句转化为MapReduce任务提交到Hadoop集群运行&＃xff0c;十分适合数据仓库的统计分析。

Hive并不提供实时的查询和基于行级的数据更新操作&＃xff0c;Hive在加载数据的过程中不会对数据进行任何修改&＃xff0c;只是将数据

移动到Hive设定&＃xff08;hive-site.xml&＃xff09;的HDFS目录中&＃xff0c;将HDFS中的数据变成关系型数据库的逻辑结构&＃xff0c;在Hive中创建的数据库其实是假的&＃xff0c;所以需要一个映射关系数据&＃xff08;即元数据&＃xff08;metastore&＃xff09;&＃xff09;&＃xff0c;因此&＃xff0c;Hive不支持对数据的改写和添加。Hive的最佳使用场合就是大数据集的批处理操作。

目前Hive将metastore数据存储在RDBMS数据库中&＃xff0c;HIve有三种模式可以访问数据库中的metastore内容&＃xff1a;

&＃xff08;1&＃xff09;单用户本地模式&＃xff1a;使用简单基于内存的数据库derby

  (2)   多用户本地模式&＃xff1a;使用本地的关系数据库Mysql&＃xff0c;提供多用户并发访问

&＃xff08;3&＃xff09; 远程服务器模式&＃xff1a;使用单独的机器部署数据库服务器&＃xff0c;配置远程访问权限

面试题总结&＃xff1a;

1.Hive数据倾斜

什么是数据倾斜&＃xff1a;由于数据分布不均匀&＃xff0c;造成数据大量的集中到一点&＃xff0c;造成数据热点

原因&＃xff1a;

1. key分布不均匀
2. 业务数据本身的特性
3. SQL语句造成数据倾斜

hadoop框架的特性&＃xff1a;

       A、不怕数据大&＃xff0c;怕数据倾斜

　　B、Jobs 数比较多的作业运行效率相对比较低&＃xff0c;如子查询比较多

　　C、 sum,count,max,min 等聚集函数&＃xff0c;通常不会有数据倾斜问题

主要表现&＃xff1a;

任务进度长时间维持在 99%或者 100%的附近&＃xff0c;查看任务监控页面&＃xff0c;发现只有少量 reduce 子任务未完成&＃xff0c;因为其处理的数据量和其他的 reduce 差异过大。 单一 reduce 处理的记录数和平均记录数相差太大&＃xff0c;通常达到好几倍之多&＃xff0c;最长时间远大 于平均时长。

解决方法
hive设置hive.map.aggr&＃61;true和hive.groupby.skewindata&＃61;true
有数据倾斜的时候进行负载均衡&＃xff0c;当选项设定为true,生成的查询计划会有两个MR Job。第一个MR Job中&＃xff0c;Map的输出结果集合会随机分布到Reduce中&＃xff0c;每个Reduce做部分聚合操作&＃xff0c;并输出结果&＃xff0c;这样处理的结果是相同Group By Key有可能被分发到不同的Reduce中&＃xff0c;从而达到负载均衡的目的&＃xff1b;第二个MR Job在根据预处理的数据结果按照 Group By Key 分布到Reduce中(这个过程可以保证相同的 Group By Key 被分布到同一个Reduce中)&＃xff0c;最后完成最终的聚合操作。
SQL语句调整: 
选用join key 分布最均匀的表作为驱动表。做好列裁剪和filter操作&＃xff0c;以达到两表join的时候&＃xff0c;数据量相对变小的效果。
大小表Join&＃xff1a; 使用map join让小的维度表&＃xff08;1000条以下的记录条数&＃xff09;先进内存。在Map端完成Reduce。
大表Join大表&＃xff1a;把空值的Key变成一个字符串加上一个随机数&＃xff0c;把倾斜的数据分到不同的reduce上&＃xff0c;由于null值关联不上&＃xff0c;处理后并不影响最终的结果。
count distinct大量相同特殊值&＃xff1a;count distinct时&＃xff0c;将值为空的情况单独处理&＃xff0c;如果是计算count distinct&＃xff0c;可以不用处理&＃xff0c;直接过滤&＃xff0c;在做后结果中加1。如果还有其他计算&＃xff0c;需要进行group by&＃xff0c;可以先将值为空的记录单独处理&＃xff0c;再和其他计算结果进行union.
 

2.hive大小表优化

MAPJOIN 当一个大表和一个或多个小表做JOIN时&＃xff0c;最好使用MAPJOIN&＃xff0c;性能比普通的JOIN要快很多。 另外&＃xff0c;MAPJOIN 还能解决数据倾斜的问题。 MAPJOIN的基本原理是&＃xff1a;在小数据量情况下&＃xff0c;SQL会将用户指定的小表全部加载到执行JOIN操作的程序的内存中&＃xff0c;从而加快JOIN的执行速度。

1、小、大表 join

在小表和大表进行join时&＃xff0c;将小表放在前边&＃xff0c;效率会高。hive会将小表进行缓存。

2、mapjoin

使用mapjoin将小表放入内存&＃xff0c;在map端和大表逐一匹配。从而省去reduce。

样例&＃xff1a;

select /*&＃43;MAPJOIN(b)*/ a.a1,a.a2,b.b2 from tablea a JOIN tableb b ON a.a1&＃61;b.b1

缓存多张小表&＃xff1a;
select /*&＃43;MAPJOIN(b,c)*/ a.a1,a.a2,b.b2 from tablea a JOIN tableb b ON a.a1&＃61;b.b1 JOIN tbalec c on a.a1&＃61;c.c1
mapjoin的join发生在map阶段&＃xff0c;join的join发生在reduce阶段&＃xff0c;mapjoin可以提高效率
 

3.请谈一下hive的特点是什么&＃xff1f;hive和RDBMS有什么异同&＃xff1f;

hive是基于Hadoop的一个数据仓库工具&＃xff0c;可以将结构化的数据文件映射为一张数据库表&＃xff0c;并提供完整的sql查询功能&＃xff0c;可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低&＃xff0c;可以通过类SQL语句快速实现简单的MapReduce统计&＃xff0c;不必开发专门的MapReduce应用&＃xff0c;十分适合数据仓库的统计分析。

4.请把下一语句用hive方式实现&＃xff1f;

SELECT a.key,a.value

FROM a

WHERE a.key not in (SELECT b.key FROM b)

答案&＃xff1a;

select a.key,a.value from a where a.key not exists (select b.key from b)

5.请说明hive中 Sort By&＃xff0c;Order By&＃xff0c;Cluster By&＃xff0c;Distrbute By各代表什么意思。

order by&＃xff1a;会对输入做全局排序&＃xff0c;因此只有一个reducer&＃xff08;多个reducer无法保证全局有序&＃xff09;。只有一个reducer&＃xff0c;会导致当输入规模较大时&＃xff0c;需要较长的计算时间。

sort by&＃xff1a;不是全局排序&＃xff0c;其在数据进入reducer前完成排序。

distribute by&＃xff1a;按照指定的字段对数据进行划分输出到不同的reduce中。

cluster by&＃xff1a;除了具有 distribute by 的功能外还兼具 sort by 的功能。

6.海量数据分布在100台电脑中&＃xff0c;想个办法高效统计出这批数据的TOP10

方案1:
在每台电脑上求出TOP10&＃xff0c;可以采用包含10个元素的堆完成(TOP10小&＃xff0c;用最大堆&＃xff0c;TOP10大&＃xff0c;用最小堆)。
比如求TOP10大&＃xff0c;我们首先取前10个元素调整成最小堆&＃xff0c;如果发现&＃xff0c;然后扫描后面的数据&＃xff0c;并与堆顶元素比较&＃xff0c;如果比堆顶元素大&＃xff0c;那么用该元素替换堆顶&＃xff0c;然后再调整为最小堆。
最后堆中的元素就是TOP10大。
方案2
求出每台电脑上的TOP10后&＃xff0c;然后把这100台电脑上的TOP10组合起来&＃xff0c;共1000个数据
再利用上面类似的方法求出TOP10就可以了。

7.Hive中追加导入数据的4种方式是什么&＃xff1f;请写出简要语法

1.从本地导入&＃xff1a; load data local inpath ‘/home/1.txt’ (overwrite)into table student;
2.从Hdfs导入&＃xff1a; load data inpath ‘/user/hive/warehouse/1.txt’ (overwrite)into table student;
3.查询导入&＃xff1a; create table student1 as select * from student;(也可以具体查询某项数据)
4.查询结果导入&＃xff1a;insert &＃xff08;overwrite&＃xff09;into table staff select * from track_log;
 

8.HIVE体系结构图

9.hive 内部表和外部表区别

创建表时&＃xff1a;创建内部表时&＃xff0c;会将数据移动到数据仓库指向的路径&＃xff1b;若创建外部表&＃xff0c;仅记录数据所在的路径&＃xff0c; 不对数据的位置做任何改变。
删除表时&＃xff1a;在删除表的时候&＃xff0c;内部表的元数据和数据会被一起删除&＃xff0c; 而外部表只删除元数据&＃xff0c;不删除数据。这样外部表相对来说更加安全些&＃xff0c;数据组织也更加灵活&＃xff0c;方便共享源数据。
 

10.分区和分桶的区别

分区
是指按照数据表的某列或某些列分为多个区&＃xff0c;区从形式上可以理解为文件夹&＃xff0c;比如我们要收集某个大型网站的日志数据&＃xff0c;一个网站每天的日志数据存在同一张表上&＃xff0c;由于每天会生成大量的日志&＃xff0c;导致数据表的内容巨大&＃xff0c;在查询时进行全表扫描耗费的资源非常多。
那其实这个情况下&＃xff0c;我们可以按照日期对数据表进行分区&＃xff0c;不同日期的数据存放在不同的分区&＃xff0c;在查询时只要指定分区字段的值就可以直接从该分区查找
分桶
分桶是相对分区进行更细粒度的划分。
分桶将整个数据内容安装某列属性值得hash值进行区分&＃xff0c;如要按照name属性分为3个桶&＃xff0c;就是对name属性值的hash值对3取摸&＃xff0c;按照取模结果对数据分桶。
如取模结果为0的数据记录存放到一个文件&＃xff0c;取模为1的数据存放到一个文件&＃xff0c;取模为2的数据存放到一个文件
 

11.Hive优化

通用设置
hive.optimize.cp&＃61;true&＃xff1a;列裁剪 
hive.optimize.prunner&＃xff1a;分区裁剪 
hive.limit.optimize.enable&＃61;true&＃xff1a;优化LIMIT n语句 
hive.limit.row.max.size&＃61;1000000&＃xff1a; 
hive.limit.optimize.limit.file&＃61;10&＃xff1a;最大文件数

本地模式(小任务)
job的输入数据大小必须小于参数&＃xff1a;hive.exec.mode.local.auto.inputbytes.max(默认128MB)
job的map数必须小于参数&＃xff1a;hive.exec.mode.local.auto.tasks.max(默认4)
job的reduce数必须为0或者1 
hive.exec.mode.local.auto.inputbytes.max&＃61;134217728 
hive.exec.mode.local.auto.tasks.max&＃61;4 
hive.exec.mode.local.auto&＃61;true 
hive.mapred.local.mem&＃xff1a;本地模式启动的JVM内存大小
并发执行
hive.exec.parallel&＃61;true &＃xff0c;默认为false 
hive.exec.parallel.thread.number&＃61;8

Strict Mode&＃xff1a;
hive.mapred.mode&＃61;true&＃xff0c;严格模式不允许执行以下查询&＃xff1a; 
分区表上没有指定了分区
没有limit限制的order by语句
笛卡尔积&＃xff1a;JOIN时没有ON语句
动态分区
hive.exec.dynamic.partition.mode&＃61;strict&＃xff1a;该模式下必须指定一个静态分区 
hive.exec.max.dynamic.partitions&＃61;1000 
hive.exec.max.dynamic.partitions.pernode&＃61;100&＃xff1a;在每一个mapper/reducer节点允许创建的最大分区数 
DATANODE&＃xff1a;dfs.datanode.max.xceivers&＃61;8192&＃xff1a;允许DATANODE打开多少个文件

排序
ORDER BY colName ASC/DESC 
hive.mapred.mode&＃61;strict时需要跟limit子句 
hive.mapred.mode&＃61;nonstrict时使用单个reduce完成排序 
SORT BY colName ASC/DESC &＃xff1a;每个reduce内排序 
DISTRIBUTE BY(子查询情况下使用 )&＃xff1a;控制特定行应该到哪个reducer&＃xff0c;并不保证reduce内数据的顺序 
CLUSTER BY &＃xff1a;当SORT BY 、DISTRIBUTE BY使用相同的列时。

合并小文件
hive.merg.mapfiles&＃61;true&＃xff1a;合并map输出 
hive.merge.mapredfiles&＃61;false&＃xff1a;合并reduce输出 
hive.merge.size.per.task&＃61;256*1000*1000&＃xff1a;合并文件的大小 
hive.mergejob.maponly&＃61;true&＃xff1a;如果支持CombineHiveInputFormat则生成只有Map的任务执行merge 
hive.merge.smallfiles.avgsize&＃61;16000000&＃xff1a;文件的平均大小小于该值时&＃xff0c;会启动一个MR任务执行merge。

自定义map/reduce数目
减少map数目&＃xff1a; 
　　set mapred.max.split.size 
　　set mapred.min.split.size 
　　set mapred.min.split.size.per.node 
　　set mapred.min.split.size.per.rack 
　　set hive.input.format&＃61;org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
增加map数目&＃xff1a; 
当input的文件都很大&＃xff0c;任务逻辑复杂&＃xff0c;map执行非常慢的时候&＃xff0c;可以考虑增加Map数&＃xff0c;来使得每个map处理的数据量减少&＃xff0c;从而提高任务的执行效率。
假设有这样一个任务&＃xff1a; 
select data_desc, count(1), count(distinct id),sum(case when …),sum(case when ...),sum(…) from a group by data_desc
如果表a只有一个文件&＃xff0c;大小为120M&＃xff0c;但包含几千万的记录&＃xff0c;如果用1个map去完成这个任务&＃xff0c;肯定是比较耗时的&＃xff0c;这种情况下&＃xff0c;我们要考虑将这一个文件合理的拆分成多个&＃xff0c;这样就可以用多个map任务去完成。 
　　set mapred.reduce.tasks&＃61;10; 
　　create table a_1 as select * from a distribute by rand(123);
这样会将a表的记录&＃xff0c;随机的分散到包含10个文件的a_1表中&＃xff0c;再用a_1代替上面sql中的a表&＃xff0c;则会用10个map任务去完成。每个map任务处理大于12M&＃xff08;几百万记录&＃xff09;的数据&＃xff0c;效率肯定会好很多。
reduce数目设置&＃xff1a; 
参数1&＃xff1a;hive.exec.reducers.bytes.per.reducer&＃61;1G&＃xff1a;每个reduce任务处理的数据量
参数2&＃xff1a;hive.exec.reducers.max&＃61;999(0.95*TaskTracker数)&＃xff1a;每个任务最大的reduce数目
reducer数&＃61;min(参数2,总输入数据量/参数1)
set mapred.reduce.tasks&＃xff1a;每个任务默认的reduce数目。典型为0.99*reduce槽数&＃xff0c;hive将其设置为-1&＃xff0c;自动确定reduce数目。
使用索引&＃xff1a;
hive.optimize.index.filter&＃xff1a;自动使用索引 
hive.optimize.index.groupby&＃xff1a;使用聚合索引优化GROUP BY操作
 

数据库的操作&＃xff1a;

CREATE DATABASE yexin; 或者 CREATE SCHEMA yexin;                 --------------------创建数据库

DROP DATABASE IF EXISTS yexin&＃xff1b;或者 DROP SCHEMA yexin--------------------------删除数据库

--创建数据库

create database if not exists sopdm

comment ‘this is test database’

with dbproperties(‘creator’&＃61;’gxw’,’date’&＃61;’2014-11-12’) --数据库键值对属性信息

location ‘/my/preferred/directory’;

--查看数据库的描述信息和文件目录位置路径信息

describe database sopdm;

--查看数据库的描述信息和文件目录位置路径信息(加上数据库键值对的属性信息)

describe database extended sopdm

--删除数据库

drop database if exists sopdm;

--级联删除数据库(当数据库还有表时&＃xff0c;级联删除表后在删除数据库),默认是restrict

drop database if exists sopdm cascade;

--修改数据库

--只能修改数据库的键值对属性值。数据库名和数据库所在的目录位置不能修改

alter database sopdm set dmproperties(‘edited-by’&＃61;’gaoxianwei’);

--创建表

--其中tblproperties作用&＃xff1a;按照键值对的格式为表增加额外的文档说明&＃xff0c;也可用来表示数据库连接的必要的元数据信息

--hive会自动增加二个表属性&＃xff1a;last_modified_by(最后修改表的用户名)&＃xff0c;last_modified_time(最后一次修改的时间)

create table if not exists sopdm.test1(name string comment ‘姓名’,salary float comment ‘薪水’)

comment ‘这是一个测试的表’

tblproperties(‘creator’&＃61;’me’,’created_at’&＃61;’2014-11-13 09:50:33’)

location ‘/user/hive/warehouse/sopdm.db/test1’

--查看和列举表的tblproperties属性信息

show tblproperties table_name;

--使用like在创建表的时候&＃xff0c;拷贝表模式(而无需拷贝数据)

create table if not exists sopdm.test2 like sopdm.test1;

--查看表的详细结构信息&＃xff08;也可以显示表是管理表&＃xff0c;还是外部表。还有分区信息&＃xff09;

describe extended sopdm.test1;

--使用formatted信息更多些&＃xff0c;可读性更强

describe formatted sopdm.test1;

--创建外部表

--删除表时&＃xff0c;表的元数据会被删除掉&＃xff0c;但是数据不会被删除

--如果数据被多个工具&＃xff08;如pig等&＃xff09;共享&＃xff0c;可以创建外部表

create external table if not exists sopdm.test1(

name string comment ‘姓名’,

salary float comment ‘薪水’)

comment ‘这是一个测试的表’

tblproperties(‘creator’&＃61;’me’,’created_at’&＃61;’2014-11-13 09:50:33’)

location ‘/user/hive/warehouse/sopdm.db/test1’

--分区表

create table if not exists sopdm.test1(

name string comment ‘姓名’,

salary float comment ‘薪水’)

comment ‘这是一个测试的表’

partitioned by(country string,state string)

STORED AS rcfile

tblproperties(‘creator’&＃61;’me’,’created_at’&＃61;’2014-11-13 09:50:33’)

location ‘/user/hive/warehouse/sopdm.db/test1’

--查看表中存在的所有分区

show partitions table_name;

--查看表中特定分区

show partitions table_name partition(country&＃61;’US’);

--可以在表载入数据的时候创建分区

load data local inpath ‘${env:HOME/employees}’

into table employees

partition(country&＃61;’US’,state&＃61;’CA’);

--删除表

drop table if exists table_name;

--修改表-表重命名

alter table old_table_name rename to new_table_name;

--增加分区

alter table table_name add if not exists partition(year&＃61;2011,month&＃61;1,day&＃61;1)

location ‘/logs/2011/01/01’;

--修改分区存储路径

alter table table_name partition(year&＃61;2011,month&＃61;1,day&＃61;2)

set location ‘/logs/2011/01/02’;

--删除某个分区

alter table table_name drop if exists partition(year&＃61;2011,month&＃61;1,day&＃61;2);

--修改列信息

alter table table_name

change column old_name new_name int

comment ‘this is comment’

after severity; --字段移到severity字段之后&＃xff08;移动到第一个位置&＃xff0c;使用first关键字&＃xff09;

--增加列

alter table table_name add columns(app_name string comment ‘application name’);

--删除或者替换列

alter table table_name replace columns(hms int comment ‘hhh’);

--修改表属性

alter table table_name set tblproperties(‘notes’&＃61;’this is a notes’);

--修改存储属性

alter table table_name partition(year&＃61;2011,month&＃61;1,day&＃61;1) set fileformat sequencefile;

--指定新的SerDe,并指定SerDe属性

alter table table_name

set serde “com.example.JSONSerDe”

with serdeproperties(‘prop1’&＃61;‘value1’, ‘prop2’&＃61;‘value2’);

--增加执行“钩子”——当表中存储的文在hive之外被修改了&＃xff0c;就会触发钩子的执行

alter table table_name touch partition(year&＃61;2012,month&＃61;1,day&＃61;1);

--将分区内的文件打成hadoop压缩包文件&＃xff0c;只会降低文件系统中的文件数&＃xff0c;减轻NameNode的压力&＃xff0c;而不会减少任何的存储空间

--使用unarchive替换archive起到反向操作

alter table table_name archive partition(year&＃61;2012,month&＃61;1,day&＃61;1);

--防止分区被删除和被查询(使用enable替代disable可以起到反向的操作目的)

alter table table_name partition(year&＃61;2012,month&＃61;1,day&＃61;1) disable no_drop;

alter table table_name partition(year&＃61;2012,month&＃61;1,day&＃61;1) disable offline;

--向管理表中装载数据

-- inpath为一个目录&＃xff0c;而且这个路径下不可以包含任何文件夹

load data local inpath ‘${env:HOME}/table_name’

overwrite into table table_name

partition(country&＃61;’US’);

--通过查询语句向表中插入数据

--overwrite是覆盖&＃xff0c;into是追加

insert overwrite table table_name

partition(country&＃61;’US’)

select * from table_name2 tn where tn.cnty&＃61;’US’

--高效方式-查询语句插入多个分区

from table_name2 tn

insert overwrite table table_name

partition(country&＃61;’US’,state&＃61;’OR’)

select * where tn.cnty&＃61;’US’ and tn.st&＃61;’OR’

insert overwrite table table_name

partition(country&＃61;’US’,state&＃61;’CA’)

select * where tn.cnty&＃61;’US’ and tn.st&＃61;’CA’

--动态插入分区

--hive根据select语句最后2列确定分区字段country和state的值&＃xff08;根据位置&＃xff09;

insert overwrite table table_name

partition(country,state)

select …,se.cnty,se.st

from employees se;

--动态和静态分区结合

--country为静态分区&＃xff0c;state为动态分区&＃xff08;静态分区必须在动态分区之前&＃xff09;

insert overwrite table table_name

partition(country&＃61;‘US’,state)

select …,se.cnty,se.st

from employees se

where se.cnty&＃61;’US’;

--单个查询语句中创建表并加载数据

create table table_name1

as select name,salary,address from table_name2 where state&＃61;’CA’;

--导出数据——拷贝文件

--如果数据文件恰好是用户需要的格式&＃xff0c;那么只需要简单的拷贝文件或文件夹就可以。

hadoop fs –cp source_path target_path

--导出数据

insert overwrite local directory ‘/tmp/employees’

select name,salary,address from employees se where se.state&＃61;’CA’

--导出数据到多个输出文件夹

from employees se

insert overwrite local directory ‘/tmp/or_employees’

select * se where se.cty&＃61;’US’ and se.st&＃61;’OR’

insert overwrite local directory ‘/tmp/ca_employees’

select * se where se.cty&＃61;’US’ and se.st&＃61;’CA’