HIVE 基础-->函数操作

分享 123456789987654321 ⋅ 于 2021-07-02 14:05:02 ⋅ 182 阅读

Hive

一、表SQL操作

1. 通过select数据集创建表

--只能是内部表,不支持分区,分桶
--通过select数据集创建表语法格式
create table table_name [stored as orc]  
as
select  ......

--例如
create table inner_test1 as
select word,num from word_table;

2 复制一个表(空表)

--只复制表结构,不复制表数据
CREATE TABLE 要复制的表名 LIKE 被复制的那张表;
--例如
CREATE TABLE empty_key_value_store LIKE key_value_store;

3.显示表列表

--显示所有表
SHOW TABLES;

--按条件(正则表达式)显示表
SHOW TABLES '.*s';
show tables 'user*';

-- 最通用的写法
show tables like '*user*';

-- 显示函数
show fuctions like '*count*';

4. 删除表

--外部表:  只删除元数据,不删除数据目录, (加PURGE也不删)
--内部表:  只删除表的元数据,表的数据放到hdfs回收站

--删除表
DROP TABLE [IF EXISTS] table_name [PURGE]; 
--例如   如果增加PURGE则不进回收站数据直接删除,不能恢复; //PURGE 不要加
drop table inner_test1 ;

5 清除表(只针对内部表)

-- 1)删除表和删除分区数据,不删除元数据(表结构)
-- 2)外部表不支持使用TRUNCATE语句。

--清除表语法:
TRUNCATE TABLE table_name [PARTITION partition_spec];
--例如:
TRUNCATE TABLE inner_test1;

二、常用修改表操作

内部表 外部表
修改表名 元数据和hdfs目录均修改 只修改元数据,hdfs目录不改
增加表分区 自动创建hdfs目录 自动创建hdfs目录
删除表分区 元数据和hdfs目录均被删除 只删除元数据,hdfs目录不删除
修改分区路径 元数据修改,但目标分区路径的hdfs目录没有创建 元数据修改,但目标分区路径的hdfs目录没有创建
表分区重命名 元数据和hdfs目录均修改 只修改元数据,hdfs目录不修改

1.改表名

--改表名
ALTER TABLE 旧的表名 RENAME TO 新的表名;

--内部表修改了表名之后,表对应的存储文件地址也跟着改,相当于作了HDFS的目录重命名。
--外部表不会改对应的location地址。

--内部表修改表名
ALTER TABLE user_info RENAME TO user_info_2;

--外部表修改表名
alter table ext_test rename to ext_test_2;

2. 修改表属性

--语法
ALTER TABLE 表名 SET TBLPROPERTIES table_properties(''='');

--示例  修改表的描述
alter table ext_test set tblproperties ('comment' = 'EXTERNAL table a!');
-- 内部表转外部表
alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE'); 
  -- 外部表转内部表
alter table table_name set TBLPROPERTIES ('EXTERNAL'='FALSE');

3 增加表分区

--内部表增加表分区,自动创建目录。
--外部表增加表分区,自动创建目录。

-- 1. 分区表外部表
CREATE EXTERNAL TABLE ext_task(
word STRING,
num INT
)
PARTITIONED BY (taskname STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/xsmpf/hive32/ext_task';

--2. 分区表添加分区     字段值=指定hdfs目录位置
--alter table ext_task add partition(分区字段名='字段值') location '指定hdfs目录位置';

alter table ext_task add partition(taskname='wordcount') location 'wordcount';

--3. Navicat 查询具体表的分区目录

select t1.`NAME`, t2.TBL_NAME,t4.PART_NAME, t3.LOCATION from DBS t1, TBLS t2 , SDS t3 , `PARTITIONS` t4 
where t1.DB_ID=t2.DB_ID and 
            t4.SD_ID = t3.SD_ID AND 
            t2.TBL_ID = t4.TBL_ID and 
            t1.`NAME` = '数据库名' AND 
            t2.TBL_NAME like '%表名' 

4 删除分区

--内部表删除分区,删除元数据和 分区数据        
--外部表删除分区不删除分区对应的目录。 只删除元数据, 分区数据不删除

--删除表分区的语法格式
ALTER TABLE 表名 DROP [IF EXISTS] PARTITION(分区字段名 = 分区字段值);

--例如
alter table inner_task DROP IF EXISTS partition(taskname='sortword');

5 修改表或分区的路径

-- 内部表修改分区路径,元数据修改,但hdfs目录没有创建,等导入数据时创建,或者自己主动创建。
-- 外部表修改分区路径,元数据修改,但hdfs目录没有创建,等导入数据时创建,或者自己主动创建。

-- 无论是外部表还是内部表,修改分区路径,需要自己手动创建路径,或者等导入数据时创建。
--修改完分区后,原来的分区还会存在???

--修改表或分区路径: 语法:
ALTER TABLE 表名 (字段名= 字段值) SET LOCATION "要修改的分区路径";

--内部表修改分区路径               location的值可以和分区的名字不一样
alter table inner_task PARTITION  (taskname='wordcount') set location "hdfs://ns1/hive/warehouse/class12.db/inner_task/wordcount1";

6 分区重命名

--如果是内部表,分区重命名,分区对应的 元数据和hdfs目录均修改会跟着改变,
--外部表不会。 --只修改元数据,hdfs目录不修改

--分区重命名语法格式
ALTER TABLE 表名 PARTITION (字段名= 字段值) RENAME TO PARTITION (字段名= 字段值);

--内部表分区重命名会改变分区对应的地址
alter table inner_task partition (taskname='maxword') rename to partition (taskname='maxword01');

--外部表分区重命名不会改变分区对应的地址
alter table ext_task partition (taskname='maxword') rename to partition (taskname='maxword01');

7 增加表字段

--增加表字段、使用新列集合替换现有数据列的语法格式
ALTER TABLE 表名 ADD|REPLACE COLUMNS (字段名 字段类型 , ...)

--1)ADD COLUMNS 可以在表列的最后和分区字段前面增加字段。

--例如
alter table ext_test_c add columns (test_col string);

8 修改表字段

--语法格式
ALTER TABLE 表名 CHANGE [COLUMN] 字段源名 字段新名 新的字段类型 ;

--示例:
alter table ext_test_c CHANGE  COLUMN col_new col_new2 string;

三、视图操作

--只有逻辑视图,没有物化视图; 
--视图只能查询,
-- 无论 使用视图还是不使用视图,执行的任务数不变。主要是为了简化SQL的编写;

--视图在创建时候,只是保存了一份元数据,
--在hdfs中没有体现,当查询的时候,才执行视图对应的那些子查询,如果子查询简单,不会生成MapReduce任务;

1.创建视图

--语法
CREATE VIEW [IF NOT EXISTS] 视图名称 
AS SELECT ...;--查询语句,把查询结果装进视图

-- 查看创建视图的语句
show create table xxx;

--查看列表
show tables;

--例子
create view tt_country_view
as 
select count(1) n,country from user_install_status_limit group by country;

-- 创建视图
create view query1 as select country,count(distinct aid) c from user_install_status_limit group by country order by c desc limit 1;
-- 查询视图
select * from query1;

四、数据加载(hive 数据导入与导出实现)

1.语法

-- local  filepath  overwrite
--1 语法:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename  PARTITION(分区名=分区值)

--1)hive 的数据加载不会对本地数据文件做任何处理,只是将文件或目录中的所有文件拷贝到表定义的数据目录。
--2)filepath 可以是目录,但该目录下不能包含子目录。
--3)指定LOCAL本地文件上传,如果没有指定LOCAL,则使用表一样的文件系统 (本地文件不删除)。
--4)文件加载hive没有做严格校验,文件格式和压缩选项等匹配需要用户自己保证。
--5)分区表要指定具体加载数据分区。
--6)如果指定OVERWRITE会覆盖相应表数据或分区数据,相当于 rm 原有目录数据,然后上传新数据文件HDFS执行MV     操作。

2 .将hdfs中的文件load到表中(move)

hdfs load

--mv操作
--分区表不创建分区,导入时自动创建分区,并创建分区目录
--*******如果把hdfs本地数据加载到分区下,需要先手动创建分区,否则会自动创建 分区名=分区值 的的分区******

-- 以覆盖或追加的方式 , 从linux本地 copy文件到表或表分区里
LOAD DATA LOCAL INPATH 'filepath' [OVERWRITE] INTO TABLE tablename PARTITION(分区名=分区值)

--以覆盖或追加的方式 , 从hdfs move 文件到表或表分区里
LOAD DATA INPATH 'filepath' [OVERWRITE] INTO TABLE tablename  PARTITION(分区名=分区值)

3 .将本地文件load到表中

本地load

--copy操作
--导入时会自动创建分区,并创建分区目录,如果有分区和分区目录,导入时也不用那个分区目录,自己创建分区目录
--本地load到表中时,即使给表创建分区,也不用创建分区目录,load时会自动创建目录

--例子:
--添加partition
alter table ext_task add IF NOT EXISTS partition(taskname='wordcount04') location 'wordcount04';

--本地文件load到表中,相当于将本地文件上传到hdfs中,本地文件不删除
load data local INPATH '/home/panniu/hive_test/word' overwrite into table ext_task PARTITION (taskname='wordcount04'); 

4.通过select加载数据

--1)当加了 IF NOT EXISTS,如果存在分区就跳过下面的select语句。
--2)INSERT OVERWRITE 会覆盖表或分区数据,但覆盖分区时增加IF NOT EXISTS,如果分区已经存在则不会覆盖。
--3)INSERT INTO 向表或分区追加数据,不影响历史数据。

--通过select,将select数据覆盖表或分区的语法格式
INSERT OVERWRITE TABLE 表名 PARTITION(分区名= 分区值)  [IF NOT EXISTS]] 
select_statement1 FROM from_statement;

--通过select,将select数据追加到表或分区的语法格式
INSERT INTO TABLE tablename1 PARTITION(分区名= 分区值) 
select_statement1 FROM from_statement;

--例子:

--1.添加分区
alter table ext_task add IF NOT EXISTS partition(taskname='wordcount05') location 'wordcount05';

--2.导入数据
insert overwrite table ext_task partition(taskname='wordcount05')
select
word,
num
from ext_task
where taskname='wordcount01';

五、hive分区

1.动态分区

--至少有一个静态分区才能用动态分区

-- hive默认不开启动态分区
--开启动态分区
set hive.exec.dynamic.partition=true;  
--这个属性默认是strict,即限制模式,strict是避免全分区字段是动态的,必须至少一个分区字段是指定有值即静态的,且必须放在最前面。设置为nonstrict之后所有的分区都可以是动态的了。
set hive.exec.dynamic.partition.mode=nonstrict;

-----------------------------------------------------------------------------------------

--表示每个节点生成动态分区的最大个数,默认是100
set hive.exec.max.dynamic.partitions.pernode=10000;  

--表示一个DML操作可以创建的最大动态分区数,默认是1000
set hive.exec.max.dynamic.partitions=100000;

--表示一个DML操作可以创建的最大文件数,默认是100000
set hive.exec.max.created.files=150000

--将select数据,覆盖到表的动态分区
insert overwrite table table1 partition (ds, hr)  --会自动创建 ds  和   hr 的动态分区
select key, value, ds, hr FROM table2 WHERE ds is not null;

六、将数据写入文件系统(HDFS)

1.查询表数据导出到某个文件【linux本地/ hdfs】

--语法格式
INSERT OVERWRITE [LOCAL] DIRECTORY '要写入的目录'
  [ROW FORMAT row_format] [STORED AS file_format] 
  SELECT ... FROM ...   --sql语句

2.示例 往本地/HDFS 目录倒

-- 将select的数据写入到hdfs文件中,多层目录会帮你创建

-- 查询ext_task 表中分区为wordcount03 的数据,以AVRO 格式写入到hdfs指定目录
insert overwrite directory 'hdfs://ns1/user/su/hive/output1' STORED AS AVRO
select r_key, r_val from inner_task where taskname='wordcount';

-- 查询ext_task 表中分区为wordcount03 的数据,以AVRO 格式写入到本地目录中
insert overwrite local directory  '/home/hadoop/myhive/output1' STORED AS AVRO 
select r_key, r_val from inner_task where taskname='wordcount';

3.avro、orc文件格式,如果不加分区导出时,会报错

--如果是分区表,导出所有数据时,导出的格式是avro、orc文件格式,如果不加分区导出时,会报错。

1).将数据写入多个文件

--语法格式
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 row_format
select_statement1 where 
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 row_format
select_statement2 where ] ...
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]
--1)导出到文件系统的数据都序列化成text非原始类型字段会序列化成json,导出文件以^A分隔 \n结尾的文本数据。
--2)INSERT OVERWRITE到HDFS目录,可以通过MRjob实现并行写入。在集群上抽取数据不仅速度快,而且还很方便。
--3)批量导出多个文件,需要导出文件的类型一致,如果一个是avro,一个是text,报错。

-- 从一张表中导出两个文件到不同的路径
-- 分区表:需要两个分区一致,在一个分区下,来导出不同条件的数据 

--举例 分区表:
from ext_task 
insert overwrite directory 'hdfs://ns1/user/xsmpf/hive32/output_avro5' STORED AS AVRO 
select 
word,
num 
where taskname='wordcount01' and word='aa' --where 分区名= 分区值
insert overwrite local directory '/home/xsmpf/hive_test/output_avro4' STORED AS AVRO 
select 
word,
num 
where taskname='wordcount01' and word='bb';

--举例 不是分区表:
insert overwrite directory 'hdfs://ns1/user/xsmpf/hive32/output_avro5' STORED AS AVRO 
select 
id,name
where age=12
insert overwrite local directory '/home/xsmpf/hive_test/output_avro4' STORED AS AVRO 
select 
id,name
where age=13

--********坑坑坑******
--如果多个输出目录,导出的格式不同,也报错。      

2).通过hive -e 命令导出

hive -e "sql"  --> 推荐 小规模SQL

-- 后台运行,错误信息不关心,放到无底洞里   /dev/null 
nohup hive -e "use db1;select r_key, r_val from inner_task where taskname='wordcount';" 1>./out1 2>/dev/null &

-- 导出的数据导出到out1
-- 错误的信息,导出到err.log
nohup hive -e "use db1;select r_key, r_val from inner_task where taskname='wordcount';" 1>./out1 2>./err.log &

3).hive -f sqlfile 大规模SQL 执行sql文件

vim test.sql --把sql放到文件里面去  --test.sql --文件名

hive -f ./test.sql 1>./out2 2>>./err.log --执行这个文件

七、Explain慢查询

--以下顺序不是绝对的,会根据语句的不同,有所调整

--Map阶段:
    1.执行from加载,进行表的查找与加载
    2.执行where过滤,进行条件过滤与筛选
    3.执行select查询:进行输出项的筛选
    4.执行group by分组:描述了分组后需要计算的函数
    5.map端文件合并:map端本地溢出写文件的合并操作,每个map最终形成一个临时文件。
                   然后按列映射到对应的Reduce阶段:

--Reduce阶段:
    1.group by:对map端发送过来的数据进行分组并进行计算。
    2.select:最后过滤列用于输出结果
    3.limit排序后进行结果输出到HDFS文件

八、HIVE连接查询 (Hive Join)

--hive可以join两个以上的表。          
    --如果两个以上join,join的字段都一样,类型也一样,那就只生成一个mapreduce任务。
--hive只支持等连接,外连接。

1.Hive Join 等值连接

--hive只支持等值连接,外连接。
--hive不支持非相等的join条件(通过其他方式实现,如left outer join),因为它很难在map/reduce job实现这样的条件。           
hive可以join两个以上的表。

--1.inner join

-- 关闭mapjon inner join 
set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;
select * from test_a a inner join test_b b on a.id=b.id;
--等值连接:inner join
select * from test_a a inner join test_b b on a.id=b.id;
--左连接:left   join  空值展示位null 
select a.*,b.* from test_a a left join test_b b on a.id=b.id;
--右连接: right join  空值展示为null
select a.*,b.* from test_a a right join test_b b on a.id=b.id;
--full join 全连接 
select a.*,b.* from test_a a full join test_b b on a.id=b.id;

2.实现非等值连接(重要)

-- 查询a有,b没有,用 left join  + is null
--在得到join结果后,再根据where条件筛选

select a.*,b.* from test_a a 
left join test_b b 
on a.id=b.id 
where b.id is null;

3.多表join

--三表inner join 
select a.*,b.name,c.name from test_a a
inner join test_b b on a.id=b.id
inner join test_c c on b.id=c.id;
--join字段相同,只生成一个任务

--join字段不同,不一定生成一个任务

-- 关闭mapjon
set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;
select * from test_a a 
inner join test_b b on a.id=b.id 
inner join test_c c on a.name=c.name;

计算百分比

select t1.country, t1.num, t2.num, concat(round(t1.num/t2.num * 100,2), '%') from 
(select country, count(*) as num, 'link' as link from user_install_status_limit group by country) t1 
inner join 
(select count(*) as num, 'link' as link from user_install_status_limit) t2 
on t1.link=t2.link;

要避免的查询操作

--笛卡尔积的SQL
select * from test_a inner join test_b;

4.hive查询严格模式

-- 设置hive的严格校验(建议 很麻烦 确实可以帮助我们进行数据上的查询失误)
set hive.mapred.mode=strict;

设置这个参数,可以限制以下情况:
1)限制执行可能形成笛卡尔积的SQL;
2)partition表使用时不加分区;
3)order by全局排序的时候不加limit的情况;

-- 取消严格校验模式
set hive.mapred.mode=nonstrict;

5.无条件关联 full outer join

--为每个表加一个相同的字段名称 jc 进行关联
select a.*,b.* from test_a a
full outer join
test_b b
on a.id=b.id;

6.union的使用(不去重) unionall结果去重

--用 left join union right join 实现 full outer join  字段名必须打全
select a.id aid,a.name aname, b.id bid,b.name bname from test_a a left join test_b b on a.id=b.id
union
select a.id aid,a.name aname, b.id bid,b.name bname from test_a a right join test_b b on a.id=b.id;

hive执行优化

#增加reducer任务数量
set mapred.reduce.tasks=20;

#在同一个sql中的不同的job是否可以同时运行,默认为false  开启并行运算
set hive.exec.parallel=true;

#增加同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;

#设置reducer内存大小
set mapreduce.reduce.memory.mb=4096;
set mapreduce.reduce.java.opts=-Xmx3584m;

九、连接查询的优化( map端的join)

mapjoin semijoin 自动开启

--类似于mapreduce的semijoin,在map端join,无reduce。
--小表放内存,与大表的数据在map端进行join,
--hive默认开启map端join。

1.配置参数 -- 开启自动mapjoin

-- 开启自动mapjoin
-- 将小表刷入内存中,默认是true 
set hive.auto.convert.join=true;
set hive.ignore.mapjoin.hint=true; 

--hive 0.11之后,在表的大小符合设置时
-- 1.是否自动转换为mapjoin
hive.auto.convert.join.noconditionaltask=true

-- 2.是否将多个mapjoin合并为一个这个参数控制多大的表可以放进内存,默认值为10000000L(10M),该值表示可      以被转换为哈希映射的表大小的总和。  
-- 刷入内存表的大小(字节),根据自己的数据集加大
hive.auto.convert.join.noconditionaltask.size=10000

-- 3.小表的最大文件大小,默认为25000000,即25M   
--设置太大也不会校验,所以要根据实际情况来设置
hive.mapjoin.smalltable.filesize=25000000

1.1不开启mapjoin 设置:

set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;

2.在设置成false 或 true时,可以手动的 /+ MAPJOIN(c) / 。

--MAPJOIN(小表的别名)
--其中:/*+ MAPJOIN(c) */ 这里面的c,就代表那个表的数据放入内存

--示例:
select /*+ MAPJOIN(c) */ 字段名 from 数据库名.表名 u
inner join country_dict c
on u.country=c.code
where u.dt='20141228'
limit 10;

--当用mapjoin实现左连接时,必须右表数据放内存;(数据库名.表名)
select /*+ MAPJOIN(u) */  字段名 from 数据库名.表名 u
left join country_dict c
on u.country=c.code
where u.dt='20141228'
limit 10;

--当用mapjoin实现右连接时,必须左表数据放内存;

3.如果是自动mapjoin,在使用MAPJOIN时,需要注意:

1、LEFT OUTER JOIN的左表必须是大表;
2、RIGHT OUTER JOIN的右表必须是大表; 
3、INNER JOIN左表或右表均可以作为大表;
4、FULL OUTER JOIN不能使用MAPJOIN;
5、MAPJOIN支持小表为子查询;
6、使用MAPJOIN时需要引用小表或是子查询时,需要引用别名;
7、在MAPJOIN中,可以使用不等值连接或者使用OR连接多个条件;
mapjoin里写的是小表,且left outer join时小表写在join的后面;
hive中使用mapjoin有时可以大大提高sql语句的执行效率。
--其原理是:它会把小表全部读入内存中,在map的时候直接拿另外一张表的数据和内存中表的数据做匹配,进行join操作,这样省去了reduce。
在“关联操作中有一个表非常小,另一个表很大”的场景下,mapjoin就不会由于数据倾斜而导致某个reduce上落数据太多而失败;

十、hive 分组查询

1.GROUPING SETS

--grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。
--将多个group by 放在一个括号里面

--GROUP BY a, b GROUPING SETS ((a, b), a, b, ())
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a, b), a, b, ())

--等于
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b 
UNION ALL
SELECT a, null, SUM(c) FROM tab1 GROUP BY a
UNION ALL
SELECT null, b, SUM(c) FROM tab1 GROUP BY b 
UNION ALL
SELECT null, null, SUM(c) FROM tab1

2.select中的条件判断

--类似三目运算符  条件表达式 ? true的结果 : false的结果
IF(条件表达式, true的结果, false的结果)

if(a is null,'all',a)

--COALESCE
--返回参数中的第一个非空值
COALESCE( value1,value2,… )     
--
select coalesce(null,null,5,null,6) from dual;

3.with cube

--with cube是 需要 group by 字段中所有key的组合 

with cube = GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),a,b,c,())

--with cube 代替了GROUPING SETS全部字段
SELECT if(age is not null, age, 'ALL'), 
case when sex is not null then sex else 'ALL' end as age,
count(id) 
FROM student_grouping 
GROUP BY age, sex with cube;

-- GROUP BY age, sex
-- with cube (age,sex),age,sex,()

4.with rollup

--右侧递减的意思就是从左到右 依次减去最右面的;
GROUPING SETS ( (age,sex),age,() );   --从右侧每次减少一个参数

-- 相当于按右侧递减的顺序group by
SELECT if(age is not null, age, '-'), 
case when sex is not null then sex else '-' end as age,
count(id) FROM student_grouping GROUP BY age, sex 
with rollup;
--等于
SELECT if(age is not null, age, '-'), 
case when sex is not null then sex
else '-'
end as age,
count(id) FROM student_grouping GROUP BY age, sex
--GROUPING SETS ( (age,sex),age,() );

十一、hive 排序查询

1.order by

-- 保证全局结果有序 (一个ruduce) 
--order by只能有一个reducer,设置了reducer也不起作用
select * from user_install_status_limit order by uptime limit 10;

2.sort by

--不是全局排序,其在数据进入reducer前完成排序。 因此,如果用sort by进行排序,并且设置        mapred.reduce.tasks>1, 则sort by只保证每个reducer的输出有序,不保证全局有序

--sort by只能保证单个文件内有序,如果设置成一个reducer那作用和order是一样的

3.cluster by 集群排序

 --cluster by column = distribute by column + sort by column  
 --(注意,都是针对column列,且采用  默认ASC,不能指定排序规则为asc 或者desc)

4.distribute by

--distribute by + sort by 多reduce排序 =cluster by

select * from user_install_status distribute by country sort by uptime desc;

--distribute by and sort by = cluster by 
--如果distribute by和sort by字段一样 可以用 cluster by 

--例如
select * from join_table1 cluster by country desc; --正序排序
-- cluster by country  = distribute by + sort by

--倒序排序 先sex排序,再cluster by  排序
select * from join_table1 cluster by sex country desc;

--排序的时候一次排序只能排一个
--想实现降序,需要用  distribute by country sort by country组合

十二、hive 的窗口函数

--把原本的查询结果分成多分查询结果之后再进行排序
语法:主要是 over ( PARTITION BY (根据某条件分组,形成一个小组)….ORDER BY(再组内进行排序) …. ) 

三种 : 序列函数

--row_number:
  会对所有数值,输出不同的序号,序号唯一且连续,如:1、2、3、4、5。
--rank:
  会对相同数值,输出相同的序号,而且下一个序号间断,如:1、1、3、3、5。
--dense_rank:
  会对相同数值,输出相同的序号,但下一个序号不间断,如:1、1、2、2、3。

ROW_NUMBER() 不固定行数

row_number:会对所有数值,输出不同的序号,序号唯一且连续,如:1、2、3、4、5。

-- rn_id 按照性别分组,按照id排序
select sex,name,age,id,
row_number() over(partition by sex order by age desc) as rn_id,
rank() over(partition by sex order by age desc) as rank_id,
dense_rank() over(partition by sex order by age desc) as dense_id
from student_grouping;

--数据会从 每个partitioner 从1 开始重新排序 

over中partition by和distribute by区别

1)partition by [key..] order by [key..]只能在窗口函数中使用,而distribute by [key...] sort by [key...]在窗口函数和select中都可以使用。

2)窗口函数中两者是没有区别的

3)where后面不能用partition by

-- partition  by  不在窗口函数中使用-->报错

ROWS 窗口函数 固定行数

--语法:
rows between 
[n|unbounded preceding]|[n|unbounded following]|[current row]
and 
[n|unbounded preceding]|[n|unbounded following]|[current row]

--参数解释:
n行数
unbounded不限行数
preceding在前N行
following在后N行
current row当前行

-- 组合出的结果:

rows between unbounded preceding and unbounded following
-- 从 前无限行 到 当前行
rows between unbounded preceding and current row
-- 从 当前行的前两行 到 当前行
rows between 2 preceding and current row
-- 从 当前行 到 当前行后两行
rows between current row and 2 following
-- 当前行 到 后不限行
rows between current row and unbounded following

-- 查询当月销售额和近三个月的销售额
SELECT 
y,
m,
sales,
sum(sales) OVER(order by y asc,m desc ROWS BETWEEN 2 preceding AND current row) AS s1
FROM sale_table where m in ('09','08','07') and y = '2019' limit 1;
版权声明:原创作品,允许转载,转载时务必以超链接的形式表明出处和作者信息。否则将追究法律责任。来自海牛部落-123456789987654321,http://hainiubl.com/topics/75737
回复数量: 0
    暂无评论~~
    • 请注意单词拼写,以及中英文排版,参考此页
    • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
    • 支持表情,可用Emoji的自动补全, 在输入的时候只需要 ":" 就可以自动提示了 :metal: :point_right: 表情列表 :star: :sparkles:
    • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif,教程
    • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
    Ctrl+Enter