Hive

最新推荐文章于 2023-07-11 14:45:12 发布

zhangli_5

最新推荐文章于 2023-07-11 14:45:12 发布

阅读量652

点赞数 1

分类专栏： Hive 文章标签： hive

本文链接：https://blog.csdn.net/zhangli_5/article/details/53612066

版权

Hive 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Hive介绍

   Hadoop的一个数据仓库工具，可将结构化数据映射为一张数据库表，并提供完整的sql查询功能。Hive将sql语句转换为mr任务执行，使用类sql语言查询，称为hql，也允许熟悉mr开发者开发自定义mr程序完成复杂的分析工作。
   与关系型数据库区别如下：
   1. 存储文件系统不同,hive使用hdfs，关系数据库使用的是服务器本地的文件系统。
   2. Hive计算模型使用的是mr，而关系数据库则使用自己的计算模型。
   3. 关系数据库是为实时业务查询设计，而hive是为海量数据做数据挖掘使用，实时性较差。
   4. Hive很容易扩展自己的存储能力、计算能力，关系数据库不行

Hive技术架构

这里写图片描述

Hive组件介绍

服务端组件

Driver:该组件包括Complier、Optimizer、Executer，将hql语句进行解析、编译、生成执行计划，然后调用底层的mr计算框架。
Metastore:元数据服务组件，hive的元数据存储在关系型数据库中，目前支持的数据库有derby、mysql等。
Thrift:用来进行可扩展并且跨语言服务的开发，hive集成该接口，能让不同编程语言调用hive的接口。

客户端组件

CLI:命令行接口
Thrift Client:如JDBC、ODBC
WebGUI:Hive提供了一种以网页方式访问hive服务的方式，需要开启hwi(Hive Web Interface)服务。

Hive运行流程

这里写图片描述

Hive建表

因为使用外部表较多，这里主要说一下外部表，格式如下：

CREATE EXTERNAL TABLE tab_name(
col_name  type,
......
)
PARTITIONED BY(col_name type,......)
STORED AS INPUTFORMAT 'inputformat'
OUTPUTFORMAT'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 'hdfs_path'

Hive分区、桶

   两者均是把数据划分成块，分区是粗粒度的划分，桶是细粒度的划分，这样可以让查询发生在小范围的数据上从而提高效率，每个分区是一个单独的目录。
   创建分区如上，创建表的时候指明用于分区的字段PARTITIONED BY(col_name type,......)，加载数据的时候也可以指定将数据加载到指定的分区里。
   创建桶类似分区，在建表的时候指定桶列及桶个数，例如：clustered by (id) into 4 buckets。

使用案例

jdbc连接
与普通的java连接jdbc相同，使用方式也一样，不再贴代码

Hive查询优化总结

自己总结的几点查询优化的地方：

一、hive创建表：
create external table login(
userid bigint,
ip string comment 'ip',
time bigint
)
comment 'user login table'
partitioned by(y string,m string)
row format delimited
fields terminated by '\t'
stored as textfile
location '/user/mart_ebs/input_data/cbj';

二、Hive常见函数：
sysdate()、date_format()、to_date()、hour(to_date())、concat、substr、like，split等

三、sql优化：
1、Select user,itemfrom order_tablelimit 10;
=》
Select * from order_tablelimit 10; (不会生成mapreduce程序)

2、如果join的key相同，不管多少个表，都只会生成一个job
SELECT a.val, b.val, c.valFROM a JOIN b ON (a.key= b.key1) JOIN c ON (c.key= b.key1 ) -> 1个JOB
SELECT a.val, b.val, c.valFROM a JOIN b ON (a.key= b.key1) JOIN c ON (c.key= b.key2) -> 2个JOB

3、在JOIN前过滤掉不需要的数据
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'
=》
SELECT a.val, b.val FROM (select key,valfrom a where a.ds=‘2009-07-07’ ) x LEFT OUTER JOIN (select key,valfrom b where b.ds=‘2009-07-07’ ) y ON x.key=y.key

4、小表放前大表放后原则
因为在reduce阶段，位于join操作符左边的表的内容会被加载到内存，小表放左可减少内存溢出

5、当小表与大表JOIN时，采用mapjoin,即在map端完成。同时也可以避免小表与大表JOIN 产生的数据倾斜。
SELECT/*+ MAPJOIN(b) */a.key, a.value FROM a join b on a.key= b.key

6、left semi join：规律是如果左表的主键在右边表中存在，则打印左表的数据，不打印右表的数据
(1)SELECT a.key,a.value FROM a WHERE a.key in (SELECT b.key FROM b) （不支持）
(2)通过left outer join 实现in 查询
select a.key,a.value from a left outer join b on a.key=b.key where b.keyis not null
(3)通过left semi join 实现in
SELECT a.key,a.value FROM a LEFT SEMI JOIN b on (a.key= b.key)
(4)通过join 实现in
SELECT a.key,a.valueFROM a JOIN b on (a.key= b.key)

7、Left semi join 与JOIN 的区别：
B表有重复值的情况下left semi join 产生一条，join 会产生多条

8、向表的某个分区插入数据：
insert overwrite table lxw_test1 partition (dt) select sndaid,mobile,dtfrom woa_user_info_mes_tmp1

9、union all 优化：
select * from
(select ci,c2,c3 from t1 Group by c1,c2,c3
Union all
Select c1,c2,c3 from t2 Group by c1,c2,c3
) t3;->job数：3

select * from
(
select * from t1
Union all
Select * from t2
) t3
Group by c1,c2,c3;->job数1

10、合理使用UDTF(数据由横向转纵向展示)：
(1)select id ,user,name,cityfrom myTableLATERAL VIEW explode(split(all_citys,',')) adTableAS city
(2)
select * from (
select ‘1',province,sum(sales) from order_tablegroup by province
union all
select ‘2',city,sum(sales) from order_tablegroup by city
Union all
select ‘3',county,sum(sales) from order_tablegroup by county
) df
=>
select type,code,sum(sales) from (
select split(part,'_')[1] as type,
split(part,'_')[0] as code ,
sales from order_tableLATERAL VIEW
explode(split(concat(province,'_1-',city,'_2-',county,'_3'),'-')) adTableAS part
) dfgroup by type,code

11、尽量避免使用DISTINCT 进行排重，特别是大表操作，用GROUP BY 代替。

12、排序优化：
Order by 实现全局排序，一个reduce实现，由于不能并发执行，所以效率偏低
Sort by 实现部分有序，单个reduce输出的结果是有序的，效率高，通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字可以指定map 到reduce端的分发key）
CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1 但不能指定排序规则

13、JDH排序优化
(1)order by:全局排序，因此只会有一个reduce,效率慢
(2)distribute by:类似于分区，通常和sort by一起使用，根据distribute by指定的字段把数据划分到不同的输出reduce文件中去
(3)sort by:可以基于分区排序，数据在进入reduce前完成排序，因此当有多个reduce时，只能保证单个reduce输出有序
(4)cluster by:等同于distribute by + sort by,但是默认升序，不能指定排序规则，所以没有distribute by + sort by实用

14、JDH开发常用日期处理函数：
sysdate(整数数字)
to_date(string timestamp)
date_format(string timestamp,format1,format2)：Select date_format('2015-01-28','yyyy-MM-dd','yyyyMMdd') from tablename limit 1
datediff(string timestamp,int)：Select datediff ('2014-12-28','2015-01-28') from tablename limit 1
date_add(stringtimestamp,int)：Select date_add('2015-01-28',1) from tablename limit 1
date_sub(string timestamp,int)：Select date_sub ('2015-01-28',1) from tablename limit 1

15、查看表分区：
show partitions + 表名

16、创建表并创建索引字段：CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING); 

17、复制一个空表：CREATE TABLE empty_key_value_store LIKE key_value_store;

18、添加一列：ALTER TABLE pokes ADD COLUMNS (new_col INT);

19、更改表名：ALTER TABLE events RENAME TO 3koobecaf;