本文共 6430 字，大约阅读时间需要 21 分钟。

补充：Hive最关键的两个服务

HiveServer2和Hive Metastore（存元数据的地方）

可以在ambari的localhost:8080里查看

 

 

第一节课

 

1. Hive Views：

听课总结

viewe的特点

1. 位置：纯元数据，只存在metastore，不会在hdfs

2. 类似快捷方式：存到metastore之后，不会自动更新，数据的修改不会影响view

view就是个快捷方式，当你查的时候它才会临时报错，告诉你这个表已经不存在了。

3. 只读：只能查询view，无法LOAD/INSERT/ALTER。ALTER VIEW可以修改viwe的定义。

view一般很少用。

 

view的操作：

1. 建立view

CREATE VIEW  view_name  AS SELECT statment;

2.支持CTE，ORDER BY, LIMIT ,JOIN, etc.

3. 查找视图

SHOW TABLES; （直接是看不出来的，但是你自己建的view你得清楚。

或者就是show  formatted table_name来看table的详细定义区分）

SHOW VIEWS(只在hive 2.2.0以后支持)

4. 显示VIEW定义

SHOW CREATE TABLE view_name; （也可以区分VIEW和TABLE）

5.删除VIEW 

DROP view_name;

6.更改VIEW属性

ALTER VIEW view_name SET TBLPROPERTIES('comment' = 'This is a view');

7. 更改VIEW的定义

ALTER VIEW view_name AS SELECT statement;

还是通过一个SELECT查询来修改VIEW定义

 

——————————————————————————

第1节课-2 复述

 

1. 如何判断一个表是view还是table ？

建view的时候，你可以用vw_来命名，这样后面可以直观区分。

两种主要判断方法（1）desc formatted  employee

可以看到table type：Managed Table 就知道是table了

（2）show create table employee 就能从建表语句看出来是create view还是create table了。

 

2.  Hive  Lateral View（侧视图）

其实表达意思也很简单，主要应用场景就是行转列。

其中explode和lateral view是关键，

explode的作用就是把一个Map结构字段拆成多行，

而lateral view可以把这个结果作为一个虚拟表和原始数据的表做笛卡尔积。

 

例子1：

create table explode_lateral_view

('area'  string,

'goods_id' string,

'sale_info' string

)

ROW FORMAT DELIMITED 

FIELDS TERMINATED BY '|'

STORED AS textfile;

 

现在侧视图已经建立好了，

把数据导入进去

导入数据：

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

表内数据如下

explode的使用：

select explode(split(goods_id, ',')) as goods_id from  explode_later_view;

通过一个explode，轻松的把行转成了列。结果如下：

 

再说一个例子

拆解map字段，语句为select explode(split(area,',')) as area from explode_lateral_view;

我们会得到如下结果：

 

单行拆成列，这已经很棒了，但是这么多被拆的行，需要汇总结果。

比如iname里某一行拆成了miracle和tom   work_place被拆成了多伦多和旧金山。

那我们需要的是他们的笛卡尔积，如下：

name2     work_place2

miracle    多伦多  

miracle     旧金山

tom           多伦多

tom            旧金山

这就是LATERAL VIEW的作用，实现上述结果语句如下。

它配合着explode，完成了多个行转列之后的多列笛卡尔积

select  name2, work_place2 from explode_lateral_view 

LATER VIEW explode(split(name, ' ,'))   name  as  name2       //第二个name是explode分离出来的数组，我们给他起个名字。

LATER VIEW explode(split(work_palce))  work_place as  work_place2     //name2则是个虚拟表，和work_place2一起做笛卡尔积。

 

LATER VIEW outer表示外连接，如果查出空值，我们依旧做笛卡尔积，而不会什么都不显示。

获取空值很简单的  explode(split(null, ', '))  a   这样你就得到一个null的数组，叫a。

——————————————————————————————————

第二课

1. SELECT中用CTE的好处

WITH

a as (...)

b as (xxx...a ....xxx)

SELECT 

a就是a，b就是b，不用嵌套那么多层。

 

2.  进阶语句

Hive 1.0之后都支持正则表达式查询。（不做重点掌握）

首先设定

SET hive.support.quoted.identifiers = none;   //单引号不会再代表其他意义

SELECT  `^o.*`  FROM offers;  //查询出所有以字母o打头的列

 

Virtual  Columns 虚拟列(每个表都有这两个虚拟列)

INPUT__FILE__NAME   //     原文件所在路径，如下图所示

BLOCK__OFFSET__INSIDE_FILE   //·   这一行处于该文件的位置（不理解）

 

 

3. Hive Join·

Join一般有关联条件

内连接就是所有连接上的数据，

左外连接就是左边表的所有数据+所有连接上的数据

右外连接与之类似

全外连接就是两边所有数据，不管连接没连接上都显示

select  ep.age  eb.work_place  from emp_basic eb

join

emp_son ep

on  eb.emp_id=dp.emp_id

limit 5 ;

用一个on关键字来接连接的condition

 

基于IN的查询

 

基于exists的查询

where exists判断是否存在, 效果和join其实是差不多的，你在join后面可以通过on来加条件的。

 

4.MAPJOIN（不要求掌握）

必须是一个小表和一个大表，把小表分发到大表。

MAP阶段直接过滤无法JOIN的数据，这样Shuffle阶段要处理的数据就会很少，

到了Reduce就一定能JOIN到，这样不是很棒嘛，速度很快。

【有一定局限性，不用学写法，了解即可】

 

5.UNION 

UNION去重

UNION ALL 不去重（速度比UNION快很多）

select 1 union select 2;

纵向合并数据

排序order by是在UNION之后

 

6.  数据加载的方法：

（1）LOAD 

相当于剪切粘贴的操作，原始数据被移动到了目标位置。

LOAD DATA LOCAL  INPATH  '/home/dayongd/Downloads/employee/hr.txt'               

 //从本地目录LOAD（不写LOCAL就是到HDFS目录）

LOAD不是hive sql命令，而且无法重复执行，用一次就把数据剪切走了。

 

（2）INSERT （推荐）

 

 

1.直接插值

inset into table a(num）values(6),(7) ;   //插入6和7

insert overwrite   table  a(num)  values(6),(7);  // 错误写法，overwrite不支持指定column。

insert overwrite table  a   values  (6,' ') (7, ' ') ; //正确写法，也就是不要写column

 

只给了num列值，其他列自动补NULL值。

 

2.从query中插入

insert into employee select *  from ctas_employee

insert into employee(name)  select 'John'  from test  limit 1;

 

3. 一个语句（一个job） 多次插入

insert还支持 从一个表插入多次 这样可以提高性能

from ctas_employee

insert overwrite table employee

select *

insert overwrite table employee_internal

select *; 

以上语句由两个Insert  from query 获得，from写在了最上面。

这个句子可以拆成两个insert，但是性能会低一些。

 

一个很经典的例子：

with base as  (select 100)  from base

insert overwrite table a select *

insert into table b(num) select * ;

//其实很容易看懂，with就是可以定义多个子查询

base  as  （select 100 ） 

这句表示我们查出100 把它记做base

然后from base 我们从base这个公共的表中拿数据插入

一种是into方式插 另一种是overwrite方式插。

注意into需要指定列，而overwrite无法指定列，

所以into不把列写全的话，直接补充NULL值。

overwrite的select必须查询所有列。

 

7. INSERT用于hive导出文件

导出的话，只支持overwirte 不支持into。

 

//以下例子为， 从一个ctas_employee中

我们三次取数据，分别用insert overwrite导出到了本地、hdfs和一个table里

from ctas_employee

insert overwrite local directory '/tmp/out1'     （beeline对此命令会报错，这是权限问题 正常。）

select *

insert overwrite directory '/tmp/out2'

select *

insert overwrite table employee_internal

select * ;

 

8. Hive Data Exchange-- IMPORT  and  EXPORT

数据迁移时才会用到，比如说数据从开发环境移动到测试环境。

要点提示：

(1)Hive只支持表的导入导出，不支持数据库导出，除非你写脚本。

(2)导出的目录是hdfs的目录

(3)元数据和数据会一起被导入导出，数据文件结果可以在目录下的子文件夹（data和metadata）中查到。

例子：

EXPORT/IMPORT  TABLE employee TO ' /tmp/output3' ;

EXPORT/IMPORT  TABLE employee_pratitioned partition(year=2014,month=11)

TO '/tmp/output5';

 

 

PS:有时候我们确实会发现自己没有hdfs权限哦，那是因为登陆beelince的时候没有用-n指定 username

 

 

9. ORDER BY （重点掌握，Hive 排序）

这个ORDER BY和sql里面没什么不同。

 

提一个问题，如何把NULL排在100的后头（默认升序排序是把NULL放在最前头）。

方案一：

用case when语句

select * from b order by case when num is null then 101 else num end;

方案二：

nvl(num,101)

select * from b order by nvl(num,101)；

两个方案都是一个意思，如果num为null，把它换成101。

 

9. SORT BY （了解即可）

ORDER BY是全局排序，只用一个Reducer，所以肯定能确保是排好序的，应用范围很广。

SORT BY是每台机器本地排序，用到了分布式，所以关系型数据库不支持。

Reducer数量为1时，两者等价。

 

SORT BY+DISTRIBUTE BY也是有特定应用场景的，

比如说体育比赛里的单项冠军，我游泳有游泳排名，体操有体操排名，

我每个项目分别排序这就可以了，我没必要用ORDER BY对所有项目混合排序吧。

 

DISTRIBUTE BY（只分组，并不排序）其实就是group by，他就是可以把同一个group的数据分到同一台机器上，

然后再在每天机器上做SORT BY。

CLUSTER BY =DISTRIBUTE BY + SORT BY 

 

重点掌握ORDER BY ，另外几种了解即可。

——————————————————————————————————

10. Hive Aggregation（聚合运算） Overview

(1) GROUP 

select max(offervalue) from offers;

 

按特定column：

select category,max(offervalue) from offers group by category; 

要求：select的column，要么出现在group by后面，要不然你必须是个聚合函数。

 

对表达式/case/if的支持：

select  

if(category>4000,'GOOD' ,'BAD') as newcat,

max(offervalue)

from offers group by

if(category>4000,'GOOD' ,'BAD') ;

 这查出来的是啥。。没看懂

 

（2）Having（聚合条件）

select  name，sum(num) as su  from b

where name <>  ' ' 

and name is not null  group by name; 

 //从表b按照name分组，读每个name和sum(num)

条件是name不是' '或者NULL 

 

selct name ,count(*) as cnt from b 

group by name  having count(*)>1

 

 

Hive Basic Aggregation(聚合) 主要聚合函数

对两列去重是不允许的，只能对同一列去重。

max,min,count,sum,avg,collect_set,collect_list 很多主要聚合函数。