大数据学习----day27----hive02------1. 分桶表以及分桶抽样查询 2. 导出数据 3.Hive数据类型 4 逐行运算查询基本语法(group by用法,原理补充) 5.case when(练习题,多表关联)6 排序

1. 分桶表以及分桶抽样查询

1.1 分桶表

对Hive(Inceptor)表分桶可以将表中记录按分桶键(某个字段对应的的值)的哈希值分散进多个文件中,这些小文件称为桶。

如要按照name属性分为3个桶,就是对name属性值的hash值对3取摸,按照取模结果对数据分桶。如取模结果为0的数据记录存放到一个文件,取模为1的数据存放到一个文件,取模为2的数据存放到一个文件。

分区提供一个隔离数据和优化查询的便利方式。不过,并非所有的数据集都可形成合理的分区,特别是之前所提到过的要确定合适的划分大小这个疑虑(可能某个分区的数据过大)。

分区针对的是数据的存储路径;分桶针对的是数据文件

把表或分区划分成bucket有两个理由

  • 更快,桶为表加上额外结构,链接相同列划分了桶的表,可以使用map-side join更加高效。
  • 取样sampling更高效。没有分区的话需要扫描整个数据集。

数据如下(student.txt)

![]()

(1)创建分桶表

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

(2)建一个普通的stu表

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

(3)向普通的stu表中导入数据

load data local inpath "/root/hive/student.txt" into table buck_demo;

(4)设置分桶表的属性

 set hive.enforce.bucketing=true;     -- 开启分桶
 set mapreduce.job.reduces=-1;         --  默认reduce个数

(5)将数据导入分桶表(不能用load直接导入,会报错)

insert into table buck_demo
select id, name from stu;

(6)查看结果

![]()

1.2 分桶抽样查询

对于非常大的数据集,有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

从大量的数据中根据某个字段的hashcode%y(样本的个数 ,桶数)获取部分样本数据,对抽样的表是没有要求的 , 分桶表普通表都可以

例:查询表buck_sku中的数据

hive (default)> select * from  buck_demo tablesample(bucket 1 out of 4 on id);

注:tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y) ,其中y为数据按照字段分的份数,x为从数据中获取第几份,x<=y

查询结果:

![]()

2. 导出数据

2.1 将查询的数据导出到本地的文件夹中

 insert  overwrite  local  directory &quot;路径&quot;
      select *  from tbname ;

例:将stu表中查出的数据导入/root/hive/stu中

(1)导出的数据无分割符

 insert  overwrite  local  directory &quot;/root/hive/stu&quot;
      select *  from stu ;

结果

![]()

可见,导入到本地后的数据是被格式化的(无分割符)

(2)导出的数据可以自定义分割符

1 insert  overwrite  local  directory &quot;/root/hive/stu2/&quot;
2 ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
3 select *  from stu ;

结果:

![]()

2.2 将数据导出到HDFS系统中

同2.1(只是去掉local)

2.3 直接在linux的终端执行hivesql命令的方式将数据导出数据

补充:

hive -e   sql语句
hive -f   sql内容的文件

例如,需求同2.1

hive -e  &quot;use mydb1;select * from stu;&quot;   &gt;  // &gt;&gt;  /root/hive/res.txt

此处一个“>”表示覆盖导出,“>>”表示追加导出

2.4 export的形式

export 导出特殊格式 结合 inport导入使用

export  table  tbname  to  &quot;文件夹&quot;

3 Hive的数据类型(见文档)

3.1 基本数据类型

见文档

3.2 集合数据类型

![]()

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似,而STRUCT与C语言中的Struct类似,它封装了一个命名字段集合,复杂数据类型允许任意层次的嵌套

4 逐行运算查询基本语法

4.1 select....from tb_name(省略号处的用法)

select 运算关键字
    1 获取变量
    2 打印常量
    3 执行算术运算
    4 调用函数
      from  数据集
&lt;strong&gt;遍历每行执行一次&lt;/strong&gt;

以查询stu表为例

select
id   ,  --表中的字段名 (遍历表中的每行数据 获取这个变量名的值 展示)
name  ,
id+1   newid1 , -- 1002.0  隐式类型转换
cast(id as int) +1  intid,   -- 强制转换 1002
&quot;hello&quot;+name  helloname ,-- null+any  null
concat(&quot;hello &quot; , name) newname , -- 每行执行
&quot;tomcat&quot; , 
1+1,
current_database()   --  当前数据库
from
stu ;

结果

![]()

4.2 where,group by的用法

(1) where......
where表示对表中所有原始数据的过滤,其后面可接:> >= < <= and or in like not null between and,与where对应的即是having,having表示对表中分组后的数据进行过滤
(2)group by 分组,在前面使用如下聚合函数

sum avg min max count

补充:group by 的用法和原理(https://blog.csdn.net/hengji666/article/details/54924387

为什么不能够select * from Table group by id,为什么一定不能是*,而是某一个列或者某个列的聚合函数,group by 多个字段可以怎么去很好的理解呢?不过最后还是转过来了

=========正文开始===========

先来看下表1,表名为test:

![]()

表1

执行如下SQL语句:

1 SELECT name FROM test

2 GROUP BY name

你应该很容易知道运行的结果,没错,就是下表2:

![]()

表2

可是为了能够更好的理解“group by”多个列“和”聚合函数“的应用,我建议在思考的过程中,由表1到表2的过程中,增加一个虚构的中间表:虚拟表3。下面说说如何来思考上面SQL语句执行情况:

1.FROM test:该句执行后,应该结果和表1一样,就是原来的表。

2.FROM test Group BY name:该句执行后,我们想象生成了虚拟表3,如下所图所示,生成过程是这样的:group by name,那么找name那一列,具有相同name值的行,合并成一行,如对于name值为aa的,那么<1 aa 2>与<2 aa 3>两行合并成1行,所有的id值和number值写到一个单元格里面。

![]()

3.接下来就要针对虚拟表3执行Select语句了:

(1)如果执行select *的话,那么返回的结果应该是虚拟表3,可是id和number中有的单元格里面的内容是多个值的,而关系数据库就是基于关系的,单元格中是不允许有多个值的,所以你看,执行select * 语句就报错了。

(2)我们再看name列,每个单元格只有一个数据,所以我们select name的话,就没有问题了。为什么name列每个单元格只有一个值呢,因为我们就是用name列来group by的。

(3)那么对于id和number里面的单元格有多个数据的情况怎么办呢?答案就是用聚合函数,聚合函数就用来输入多个数据,输出一个数据的。如cout(id),sum(number),而每个聚合函数的输入就是每一个多数据的单元格。

(4)例如我们执行select name,sum(number) from test group by name,那么sum就对虚拟表3的number列的每个单元格进行sum操作,例如对name为aa的那一行的number列执行sum操作,即2+3,返回5,最后执行结果如下:

![]()

(5)group by 多个字段该怎么理解呢:如group by name,number,我们可以把name和number 看成一个整体字段,以他们整体来进行分组的。如下图

![]()

(6)接下来就可以配合select和聚合函数进行操作了。如执行select name,sum(id) from test group by name,number,结果如下图:

![]()

例:
数据

![]()

创建表

![]()![]()```
create table groupdemo(
uid string ,
name string ,
age int ,
gender string ,
address string
)
row format delimited fields terminated by "," ;
load data local inpath "/hive/groupdemo.txt" into table groupdemo ;


View Code

sql语句

![]()![]()```
 select 
 gender ,
 address,
 count(*)  num
 from 
 groupdemo 
 -- where....
 group  by  gender  , address
 -- having num &gt;=2     -- 对分组后的数据进行过滤
 order by num  desc , address  asc
 limit  1  , 2   --  数字一 数据的起始行(0开始计数) 数字二是显示的行数

View Code

5. case when

案例:

数据

![]()

需求:

求出不同部门男女各多少人。结果如下:

![]()

(1)建表

create table case_demo(
name string ,
dname string ,
gender string
)
row format delimited fields terminated by &quot;\t&quot;;
load data local inpath &quot;/root/hive/case.txt&quot; into table case_demo;

(2)初步尝试

select
dname , gender ,
count(*)   // count(字段) 若字段为null 则不会统计
from
case_demo
group  by dname , gender  ;

此处的group by用法见4

![]()

(3)第一种语法

select
dname ,
sum(case gender when &quot;男&quot; then 1 else 0 end)  M ,
sum(case gender when &quot;女&quot; then 1 else 0 end)  F
from
case_demo 
group by dname 
;

结果(此案例答案)

![]()

第二种语法

select
* ,
case  
  when  dname=&quot;A&quot; then &quot;销售部&quot; 
  when  dname=&quot;B&quot; then &quot;财务部&quot;
  else &quot;&quot; end  cname
from
case_demo  ;

5.2 练习

有三张表,如下

![]()

准备:

建立三张表并导入相应的数据

工资表

![]()![]()```
create table gz(
uid int,
jb int,
jj int,
tc int,
deptno int
)
row format delimited fields terminated by ",";
load data local inpath "/hive/salary.txt" into table gz;


View Code

部门表

![]()![]()```
create table bm(
deptno string ,
name string
)
row format delimited fields terminated by &quot;,&quot;;
load data local inpath &quot;/hive/bm.txt&quot; into table bm;

View Code

员工信息表

![]()![]()```
create table yg(
uid int,
name string,
gender string,
age int
)
row format delimited fields terminated by ",";
load data local inpath "/hive/yuangong.txt" into table yg;


View Code

 (1)求出公司中每个员工的姓名和三类收入中最高那种收入的类型

 sql语句(自己的写法)如下

select
yg.name,greatest(gz.jb, gz.jj, gz.tc) max_salary,
case
when gz.jb == greatest(gz.jb, gz.jj, gz.tc) then "jb"
when gz.jj == greatest(gz.jb, gz.jj, gz.tc) then "jj"
when gz.tc == greatest(gz.jb, gz.jj, gz.tc) then "tc"
else "" end category
from
yg
join gz
on gz.uid=yg.uid;


自己犯的错误:

 when后面不能使用max\_salary来代替greatest(gz.jb, gz.jj, gz.tc)

结果

![]()

改进:使用子查询

![]()![]()```
select
yg.name,
t.max_sal ,
t.category
from
yg
join
 (select
 uid ,
 greatest(jb , jj , tc) max_sal,
 case when jj == greatest(jb , jj , tc) then &quot;jj&quot;
 when jb == greatest(jb , jj , tc) then &quot;jb&quot;
 when tc == greatest(jb , jj , tc) then &quot;tc&quot;
 end  category
 from
 gz ) t
on yg.uid = t.uid;

View Code

(2)求出公司中每个岗位(部门)的薪资综合

自己犯的错:

select bm.name,sum(gz.jb+gz.jj+gz.tc)
from bm join gz on
bm.deptno = gz.deptno ;
sum(gz.jb+gz.jj+gz.tc)表示求选中的字段对应值的总和,其得到的时一个值,而bm.name为多个值,这样没法查询(sum一般时在分组后使用)

第一步:获取包含用户名,salary,部门编号的表

select * from bm join gz on bm.deptno = gz.deptno ;

结果

![]()

第二步:在第一步的基础上进行分组聚合

select name, 
sum(jb+jj+tc) total_salary 
from
(select * from bm join gz on bm.deptno = gz.deptno) t 
group by name;

结果

![]()

(3)求出公司中每个岗位不同性别员工薪资总和

知识补充:多表关联的两种方式(以此例为例)

// 第一种:from 表A join 表B on。。。join 表C on ... 
select yg.gender,gz.jj ,gz.jb,gz.tc,bm.name from gz join yg on gz.uid=yg.uid join bm on gz.deptno=bm.deptno

// 第二种:from 表A join 表B join 表C on 表A与表B关联条件 and 与表C关联的条件
select yg.gender,gz.jj ,gz.jb,gz.tc,bm.name from yg join gz join bm on yg.uid = gz.uid and gz.deptno = bm.deptno

第二种关联形式一定要注意关联条件的顺序

思路:需求中存在三个表中的字段,所以先要将三张表管关联起来,然后进行分组聚合

select
name,gender,
sum(jb+jj+tc) total_salary 
from
(select yg.gender,gz.jj ,gz.jb,gz.tc,bm.name from gz join yg on gz.uid=yg.uid join bm on gz.deptno=bm.deptno) t 
group by 
name,gender;

结果

![]()

(4)求出公司中不同性别、不同年龄阶段(20-30,31-40,41-50)的员工薪资总和

思路:先对表进行关联操作,获取包含性别,年龄阶段(使用case when 获取)工资的表,然后进行分组聚合

第一步:获取包含需要字段的表

select 
gender,age_level,gz.jb,gz.jj,gz.tc 
from
(select gender,uid,
case
when age &gt;=20 and age &lt;=30 then &quot;20~30&quot;
when age &gt;=31 and age &lt;=40 then &quot;31~40&quot;
when age &gt;=41 and age &lt;=50 then &quot;41~50&quot;
else &quot;&quot; end age_level
from yg ) t
join gz on gz.uid=t.uid

结果

![]()

第二步:分组聚合

select
gender, age_level,
sum(jb+jj+tc) total_salary
from
(select 
gender,age_level,gz.jb,gz.jj,gz.tc 
from
(select gender,uid,
case
when age &gt;=20 and age &lt;=30 then &quot;20~30&quot;
when age &gt;=31 and age &lt;=40 then &quot;31~40&quot;
when age &gt;=41 and age &lt;=50 then &quot;41~50&quot;
else &quot;&quot; end age_level
from yg ) t
join gz on gz.uid=t.uid
) t1
group by 
gender, age_level

结果

![]()

  1. 排序
order by:全局排序
sort by:每个区内排序

6.1 设置reduce的个数

set mapreduce.job.reduces=n;

查看reduce任务的个数

set mapreduce.job.reduces;

6.2 案例:以stu表为例,stu表结构如下

![]()

(1)不指定分区字段

设置reduce的个数为3(即3个分区)

sql语句:

insert overwrite  local directory &quot;/hive/stu_sort/&quot;
row format delimited fields terminated by &quot;\t&quot;
  select * from stu distribute by  sname  sort by sid  desc ;

结果

![]()

(2)指定分区字段 distribute by name(按照name的hashcode值 mod reduce任务的个数 从而得到分区数)

insert overwrite  local directory &quot;/root/hive/stu_sort1/&quot;
row format delimited fields terminated by &quot;\t&quot;
  select * from stu distribute by  name  sort by id  desc ;

结果

![]()

(3)cluster by

当分区字段和排序字段相同时,可以直接使用cluster by 相同的字段,下面两种sql写法等价

select * from stu cluster by id;
select * from emp distribute by id sort by id;

(4)小结

  1 order by  全局排序  desc  asc 
  2 sort by   区内排序   默认分区
  3 distribute by  指定分区字段 结合 sort by 区内排序
  4 cluster by  当  distribute by  和 sort by 是同一个字段 且升序的时候使用cluster by 替代
    (&lt;strong&gt;只能升序&lt;/strong&gt;)

声明:该文章系转载,转载该文章的目的在于更广泛的传递信息,并不代表本网站赞同其观点,文章内容仅供参考。

本站是一个个人学习和交流平台,网站上部分文章为网站管理员和网友从相关媒体转载而来,并不用于任何商业目的,内容为作者个人观点, 并不代表本网站赞同其观点和对其真实性负责。

我们已经尽可能的对作者和来源进行了通告,但是可能由于能力有限或疏忽,导致作者和来源有误,亦可能您并不期望您的作品在我们的网站上发布。我们为这些问题向您致歉,如果您在我站上发现此类问题,请及时联系我们,我们将根据您的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。