大数据实训第5天

分区表和分桶表的区别: 

  Hive 数据表可以根据某些字段进行分区操作,细化数据管理,可以让部分查询更快。同 时表和分区也可以进一步被划分为 Buckets,分桶表的原理和 MapReduce 编程中的 HashPartitioner 的原理类似。

  分区和分桶都是细化数据管理,但是分区表是手动添加区分,由于 Hive 是读模式,所 以对添加进分区的数据不做模式校验,分桶表中的数据是按照某些分桶字段进行 hash 散列 形成的多个文件,所以数据的准确性也高很多

内部表和外部表的使用选择:

  大多数情况,他们的区别不明显,如果数据的所有处理都在 Hive 中进行,那么倾向于 选择内部表,但是如果 Hive 和其他工具要针对相同的数据集进行处理,外部表更合适。

  使用外部表访问存储在 HDFS 上的初始数据,然后通过 Hive 转换数据并存到内部表中

  使用外部表的场景是针对一个数据集有多个不同的 Schema

  通过外部表和内部表的区别和使用选择的对比可以看出来,hive 其实仅仅只是对存储在 HDFS 上的数据提供了一种新的抽象。而不是管理存储在 HDFS 上的数据。所以不管创建内部 表还是外部表,都可以对 hive 表的数据存储目录中的数据进行增删操作。

HIve Sql案例

(1)查询在2017年4月份购买过的顾客及总人数

(2)查询顾客的购买明细及月购买总额

(3)上述的场景,要将cost按照日期进行累加

(4)查询顾客上次的购买时间

(5)查询前20%时间的订单信息

创建本地business.txt,导入数据

 

[atguigu@hadoop102 datas]$ vi business.txt

创建hive表并导入数据

 

create table business(

name string,

orderdate string,

cost int

) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

 

load data local inpath "/opt/module/datas/business.txt" into table business;

按需求查询数据

 

查询在2017年4月份购买过的顾客及总人数

 

select name,count(*) over ()

from business 

where substring(orderdate,1,7) = '2017-04'

group by name;

查询顾客的购买明细及月购买总额

 

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from

 business;

上述的场景,要将cost按照日期进行累加

 

select name,orderdate,cost,

sum(cost) over() as sample1,--所有行相加

sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加

sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加

sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合

sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合

sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行

sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行

from business;

查看顾客上次的购买时间

 

select name,orderdate,cost,

lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2

from business;

 

查询前20%时间的订单信息

 

select * from (

    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted

    from business

) t

where sorted = 1;

 

上一篇:hive窗口函数


下一篇:行转列