hive操作指南

hive操作指南

Hive 操作指南

数据类型

Hive数据类型 Java数据类型 长度 例子
TINYINT byte 1byte有符号整数 20
SMALINT short 2byte有符号整数 20
INT int 4byte有符号整数 20
BIGINT long 8byte有符号整数 20
BOOLEAN boolean 布尔类型,true或者false TRUE FALSE
FLOAT float 单精度浮点数 3.14159
DOUBLE double 双精度浮点数 3.14159
STRING string 字符系列。可以指定字符集。可以使用单引号或者双引号。 ‘now is the time’ “for all good men”
TIMESTAMP 时间类型
BINARY 字节数组

Hive的String类型相当于数据库的varchar类型,该类型是一个可变的字符串,不过它不能声明其中最多能存储多少个字符,理论上它可以存储2GB的字符数

数据类型 描述 语法示例
STRUCT 和c语言中的struct类似,都可以通过“点”符号访问元素内容。例如,如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 struct() 例如struct
MAP MAP是一组键-值对元组集合,使用数组表示法可以访问数据。例如,如果某个列的数据类型是MAP,其中键->值对是’first’->’John’和’last’->’Doe’,那么可以通过字段名[‘last’]获取最后一个元素 map() 例如map
ARRAY 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素,每个数组元素都有一个编号,编号从零开始。例如,数组值为[‘John’, ‘Doe’],那么第2个元素可以通过数组名[1]进行引用。 Array() 例如array\

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

示例:

1
2
3
4
5
6
7
8
9
10
create table test(
name string,
friends array,
children map,
address struct
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

row format delimited fields terminated by ‘,’ – 列分隔符

collection items terminated by ‘_’ –MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)

map keys terminated by ‘:’ – MAP中的key与value的分隔符

lines terminated by ‘\n’; – 行分隔符

1
2
hive (default)> select friends[1],children['xiao song'],address.city from test
where name="songsong";

类型转化规则

Hive的原子数据类型是可以进行隐式转换的,类似于Java的类型转换,例如某表达式使用INT类型,TINYINT会自动转换为INT类型,但是Hive不会进行反向转化,例如,某表达式使用TINYINT类型,INT不会自动转换为TINYINT类型,它会返回错误,除非使用CAST操作

  1. 任何整数类型都可以隐式地转换为一个范围更广的类型,如TINYINT可以转换成INT,INT可以转换成BIGINT
  2. 所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE
  3. TINYINT、SMALLINT、INT都可以转换为FLOAT
  4. BOOLEAN类型不可以转换为任何其它的类型
  5. 使用cast可以指定转化类型:select ‘1’+2, cast(‘1’as int) + 2;

数据库相关操作

  • DDL数据定义
1
2
3
4
CREATE DATABASE [IF NOT EXISTS] database_name -- 数据库名称
[COMMENT database_comment] -- 数据库备注
[LOCATION hdfs_path] -- 数据库在hdfs上保存的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; -- 指定数据库的一些属性
  • 创建数据库
1
2
3
4
create database if not exists hive_test
comment "My first hive database"
location "/hive/database/hive_test"
with dbproperties("owner" = "thatcher");
  • 删除数据库
1
2
drop database hive_test; -- 删除空数据库
drop database hive_test cascade; -- 强制删除数据库
  • 显示数据库相关信息
1
2
3
desc database hive_test; -- 不带扩展信息
desc database extended hive_test; -- 带有扩展信息
show databases; -- 查看当前所有的数据库
  • 切换数据库
1
use hive_test;
  • 修改数据库,修改数据库只限于修改数据的dbproperties,其他元数据信息无法修改
1
alter database hive_test set dbproperties("owner" = "lucifer");

表相关操作

  • DDL数据定义
1
2
3
4
5
6
7
8
9
10
11
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name -- 指定表名称,external关键字可以指定表为外部表还是内部表
[(col_name data_type [COMMENT col_comment], ...)] -- 指定列,并且列可以写comment
[COMMENT table_comment] -- 指定表的comment
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] -- 指定分区键
[CLUSTERED BY (col_name, col_name, ...) -- 指定分区桶
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] -- 对桶中的一个列或者多个列排序
[ROW FORMAT row_format] -- 表的原始描述,也就是表与文件信息的对应规则
[STORED AS file_format] -- 指定文件存储类型
[LOCATION hdfs_path] -- 指定表在HDFS上的存储位置
[TBLPROPERTIES (property_name=property_value, ...)] -- 指定表属性
[AS select_statement] -- 使用select语句创建表

(1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。

(2)EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。

(3)COMMENT:为表和列添加注释。

(4)PARTITIONED BY创建分区表

(5)CLUSTERED BY创建分桶表

(6)SORTED BY不常用,对桶中的一个或多个列另外排序

(7)ROW FORMAT

1
2
3
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体的列的数据。

SerDe是Serialize/Deserilize的简称, hive使用Serde进行行对象的序列与反序列化。

(8)STORED AS指定存储文件类型

常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)

如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。

(9)LOCATION :指定表在HDFS上的存储位置。

(10)AS:后跟查询语句,根据查询结果创建表。

(11)LIKE允许用户复制现有的表结构,但是不复制数据

  • 创建表
1
2
3
4
5
6
7
8
create table if not exists student2(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student2'; -- 直接创建

create table if not exists student3 as select id, name from student; -- 使用as子句
  • 内部表与外部表

    • 内部表:删除一个内部表时,Hive也会从HDFS上删除这个表中数据,内部表不适合和其他工具共享数据
    • 外部表:Hive并非认为其完全拥有外部表数据。删除外部表并不会删除掉数据,不过描述表的元数据信息会被删除掉
    • 相互转化
    1
    2
    alter table student2 set tblproperties('EXTERNAL'='TRUE'); -- 该属性必须得全部大写
    alter table student2 set tblproperties('EXTERNAL'='FALSE');
  • 分区表:分区表实际上就是对应一个HDFS文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件,Hive分区就是分目录,在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区,这样的查询效率会提高很多

  • 创建分区表

1
2
3
4
create table dept_partition
(deptno int, dname string, loc string)
partitioned by (month string)
row format delimited fields terminated by '\t';
  • 查询分区表的分区
1
show partitions dept_partition;
  • 向表中的插入数据
1
2
3
4
load data local inpath '/opt/module/data/01.txt' into table dept_partition
partition (month = '01');
load data local inpath '/opt/module/data/02.txt' into table dept_partition
partition (month = '02');
  • 修复提前准备元数据,但是没有对应分区信息,也就是在HDFS上新建了对应分区的文件夹,并增加了数据文件
1
2
3
4
5
6
7
8
-- 添加分区
alter table dept_partition add partition(month = '03');

-- 直接修复
msck repair table dept_partition;

-- 上传数据直接带分区
load data local inpath '/opt/module/data/03.txt' into table dept_partition partition (month = '03');
  • 建立二级分区表
1
2
3
4
5
6
create table dept_partition2
(deptno int, dname string, loc string)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';

-- 所谓二级分区表,就是在第一个分区的基础上继续分区,插入表数据的时候也需要制定两个分区,在HDFS上目录组织是这样的,首先month会是外层目录,day将会在month目录里继续分区
  • 分区增删查改
1
2
3
4
5
6
7
8
9
10
11
-- 增加分区
alter table dept_partition add partition(month = '03');

-- 增加多个分区
alter table dept_partition add partition(month = '03') partition(month = '04');

-- 删除分区
alter table dept_partition drop partition(month = '03');

-- 删除多个分区
alter table dept_partition drop partition(month = '03'), partition(month = '04');
  • 重命名表
1
ALTER TABLE table_name RENAME TO new_table_name
  • 更新表列的信息
1
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
  • 增加和替换表列的信息
1
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

ADD是代表新增一字段,字段位置在所有列后面(partition列前),REPLACE则是表示替换表中所有字段

DML操作

  • 数据导入
1
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

(1)load data:表示加载数据

(2)local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表,且如果从HDFS中导入的话,会把数据文件移动到表目录下

(3)inpath:表示加载数据的路径

(4)overwrite:表示覆盖表中已有数据,否则表示追加

(5)into table:表示加载到哪张表

(6)student:表示具体的表

(7)partition:表示上传到指定分区

  • 数据插入
1
2
3
4
insert into table student_par partition(month='201709') values(1,'wangwu'),(2,'zhaoliu'); -- 基本插入

insert overwrite table student partition(month='201708')
select id, name from student where month='201709';

insert into:以追加数据的方式插入到表或分区,原有数据不会删除

insert overwrite:会覆盖表或分区中已存在的数据

注意:insert不支持插入部分字段

  • 建表时候通过location直接加载数据
1
2
3
4
create external table student 
(id int, name string)
row format delimited fields terminated '\t'
location '/student_table';
  • 数据导入导出

    • 导出

      • insert导出
      1
      2
      3
      4
      5
      6
      7
      8
      -- 不带格式导出
      insert overwrite local directory '/opt/module/datas/export/student'
      select * from student;

      -- 带格式
      insert overwrite local directory '/opt/module/datas/export/student'
      ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
      select * from student;
      • bash命令行导出
      1
      hive -e "select * from default.student" > /opt/module/data/student.txt;
      • export导出
      1
      2
      3
      4
      export table default.student to '/user/hive/warehouse/export/student';

      -- 导入数据
      import table student from '/export/student';
    • 导入

      • import导入
      1
      2
      -- 导入数据
      import table student from '/export/student';
  • 数据删除

1
truncate table student;

查询操作

基本查询

  • 全表查询
1
select * from emp;
  • 查询某些列
1
2
3
select id, number from emp;

select id as a, number as b from emp;
  • 算术运算符
运算符 描述
A+B A和B 相加
A-B A减去B
A*B A和B 相乘
A/B A除以B
A%B A对B取余
A&B A和B按位取与
A|B A和B按位取或
A^B A和B按位取异或
~A A按位取反
  • limit语句,限制返回的行数
1
select * from emp limit 5;
  • 函数类型
1
2
3
-- UDF函数 一对一
-- UDAF函数 多对一
-- UDTF函数 一对多
  • where子句
1
select * from emp where sal > 1000;

比较查询(between/in/is Null)

操作符 支持的数据类型 描述
A=B 基本数据类型 如果A等于B则返回TRUE,反之返回FALSE
A<>B 基本数据类型 如果A和B都为NULL,则返回TRUE,其他的和等号(=)操作符的结果一致,如果任一为NULL则结果为NULL
A<>B, A!=B 基本数据类型 A或者B为NULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE
A<B 基本数据类型 A或者B为NULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE
AB 基本数据类型 A或者B为NULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE
A>=B 基本数据类型 A或者B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE
A [NOT] BETWEEN B AND C 基本数据类型 如果A,B或者C任一为NULL,则结果为NULL。如果A的值大于等于B而且小于或等于C,则结果为TRUE,反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL 所有数据类型 如果A等于NULL,则返回TRUE,反之返回FALSE
A IS NOT NULL 所有数据类型 如果A不等于NULL,则返回TRUE,反之返回FALSE
IN(数值1, 数值2) 所有数据类型 使用 IN运算显示列表中的值
A [NOT] LIKE B STRING 类型 B是一个SQL下的简单正则表达式,也叫通配符模式,如果A与其匹配的话,则返回TRUE;反之返回FALSE。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母’x’结尾,而‘%x%’表示A包含有字母’x’,可以位于开头,结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B STRING 类型 B是基于java的正则表达式,如果A与其匹配,则返回TRUE;反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。

like和rlike

  • like用法和普通sql一致
1
select * from student where name like '%thatcher%';
  • rlike可支持正则表达式
1
select * from emp where sal rlike '[2]';

逻辑比较(and / or / not)

1
2
3
select * from emp where sal>1000 and deptno=30;
select * from emp where sal>1000 or deptno=30;
select * from emp where deptno not IN(30, 20);

分组

  • group by
1
2
select deptno, avg(sal) as avg_sal from emp
group by deptno;
  • having
1
2
3
select deptno, avg(sal) as avg_sal from emp
group by deptno
having avg_sal > 2000;

连接

  • 两表连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select a.ename, a.empno, d.dname
from emp a
left join dept d
on a.deptno = d.deptno -- left join

select a.ename, a.empno, d.dname
from emp a
right join dept d
on a.deptno = d.deptno -- right join

select a.ename, a.empno, d.dname
from emp a
inner join dept d
on a.deptno = d.deptno -- inner join

select a.ename, a.empno, d.dname
from emp a
full join dept d
on a.deptno = d.deptno -- full join
  • 多表连接
1
2
3
4
5
6
SELECT e.ename, d.dname, l.loc_name
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
JOIN location l
ON d.loc = l.loc;

Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作,然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l,进行连接操作;如果多个表进行join的连接,如果每个on子句都使用相同的连接话,那么只会产生一个MapReduce Job,这也是一个优化的小技巧。

注:Hive1,2连接不支持or关键字,Hive3已经支持or

排序

  • 全局排序
1
2
select * from emp
order by sal desc;
  • 排序取前几
1
2
3
select * from emp
order by sal desc
limit 10;
  • 局部排序
1
2
select * from emp
sort by empno desc;
  • 多条件排序
1
2
3
select * from emp
order by deptno asc
sal desc;
  • 指定局部排序的分区字段
1
2
3
4
5
6
select * from emp
distribute by empno
sort by empno desc;

select * from emp
cluster by empno;

按照deptno字段分区,并且将每个区的数据按照sal排序

如果distribute和sort的字段一致,可以用cluster by代替

分桶表

  • 创建分桶表
1
2
3
4
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';

Hive的分桶采用对分桶字段的值进行哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

  • 分桶抽样
1
2
select * from stu_buck tablesample(bucket 1 out of 4 on id);
-- 按照id分成4份,从中间取出第1份

注:tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y)

y必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。例如,table总共分了4份,当y=2时,抽取(4/2=)2个bucket的数据,当y=8时,抽取(4/8=)1/2个bucket的数据。

x表示从哪个bucket开始抽取,如果需要取多个分区,以后的分区号为当前分区号加上y。例如,table总bucket数为4,tablesample(bucket 1 out of 2),表示总共抽取(4/2=)2个bucket的数据,抽取第1(x)个和第3(x+y)个bucket的数据。

注意:x的值必须小于等于y的值,否则会提示如下错误:

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buckFAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

函数操作

常用查询函数

  • nvl()
1
select comm, nvl(comm, -1) from emp; -- nvl会讲comm列为空的值替换为-1
  • case when
1
2
3
4
5
6
7
8
select 
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;
  • concat(),字符串连接函数
  • concat_ws(),将字符串用分隔符连接起来
  • collect_set(),将分组后的某一个字段进行汇总,变成array
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
+-------------------+----------------------------+-------------------------+
| person_info.name | person_info.constellation | person_info.blood_type |
+-------------------+----------------------------+-------------------------+
| 孙悟空 | 白羊座 | A |
| 大海 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 猪八戒 | 白羊座 | A |
| 凤姐 | 射手座 | A |
| 苍老师 | 白羊座 | B |
+-------------------+----------------------------+-------------------------+

+------------+----------+
| base.type | name |
+------------+----------+
| 射手座,A | 大海|凤姐 |
| 白羊座,A | 孙悟空|猪八戒 |
| 白羊座,B | 宋宋|苍老师 |
+------------+----------+

1
2
3
4
5
select base.type, concat_ws('|', collect_set(base.name)) as name
from
(select name, concat_ws(',', constellation, blood_type) as type
from person_info) base
group by base.type;
  • explode(),将hive一列中复杂的array或者map拆分成多行
  • lateral view udtf(expression) tableAlias as columnAlias
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
+-------------------+-----------------------------+
| movie_info.movie | movie_info.category |
+-------------------+-----------------------------+
| 《疑犯追踪》 | ["悬疑","动作","科幻","剧情"] |
| 《Lie to me》 | ["悬疑","警匪","动作","心理","剧情"] |
| 《战狼2》 | ["战争","动作","灾难"] |
+-------------------+-----------------------------+

+--------------+----------------+
| movie | category_name |
+--------------+----------------+
| 《疑犯追踪》 | 悬疑 |
| 《疑犯追踪》 | 动作 |
| 《疑犯追踪》 | 科幻 |
| 《疑犯追踪》 | 剧情 |
| 《Lie to me》 | 悬疑 |
| 《Lie to me》 | 警匪 |
| 《Lie to me》 | 动作 |
| 《Lie to me》 | 心理 |
| 《Lie to me》 | 剧情 |
| 《战狼2》 | 战争 |
| 《战狼2》 | 动作 |
| 《战狼2》 | 灾难 |

1
2
select movie, category_name from movie_info
lateral view explode(category) table_tmp as category_name;

开窗函数

基本概念
  • OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
  • CURRENT ROW:当前行
  • n PRECEDING:往前n行数据
  • n FOLLOWING:往后n行数据
  • UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING表示到后面的终点
  • LAG(col,n,default_val):往前第n行数据
  • LEAD(col,n, default_val):往后第n行数据
  • NTILE(n):把有序窗口的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型
案例操作

有如下数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+----------------+---------------------+----------------+
| business.name | business.orderdate | business.cost |
+----------------+---------------------+----------------+
| jack | 2017-01-01 | 10 |
| tony | 2017-01-02 | 15 |
| jack | 2017-02-03 | 23 |
| tony | 2017-01-04 | 29 |
| jack | 2017-01-05 | 46 |
| jack | 2017-04-06 | 42 |
| tony | 2017-01-07 | 50 |
| jack | 2017-01-08 | 55 |
| mart | 2017-04-08 | 62 |
| mart | 2017-04-09 | 68 |
| neil | 2017-05-10 | 12 |
| mart | 2017-04-11 | 75 |
| neil | 2017-06-12 | 80 |
| mart | 2017-04-13 | 94 |
+----------------+---------------------+----------------+
  • 查询在2017年4月份购买过的顾客及总人数
1
2
3
4
5
select name,
count(*) over() as people_sum -- over()限定了count()聚合函数的范围,表示为当前select 出来的东西进行计数,所以开窗函数的作用就是给聚合函数提供了限制范围
from business
where substring(orderdate, 1, 7) = '2017-04'
group by name;
  • 查询2017年4月顾客的购买明细及月购买总额
1
2
3
4
select name, orderdate, cost,
sum(cost) over(partition by name) as sum_cost -- partition by name 表示了sum函数的聚合范围为不同的name进行聚合
from business
where substring(orderdate, 1, 7) = '2017-04';
  • 上述的场景, 将每个顾客的cost按照日期进行累加
1
2
3
4
5
6
7
8
9
10
11
select name, orderdate, cost,
sum(cost) over(partition by name order by orderdate asc
rows between unbounded preceding and current row) as sum_cost -- order by限定了以订单日期进行排序,rows between xx and yy 限定了哪些行进行累加
from business
where substring(orderdate, 1, 7) = '2017-04'; -- 升序累加

select name, orderdate, cost,
sum(cost) over(partition by name order by orderdate desc
rows between current row and unbounded following) as sum_cost
from business
where substring(orderdate, 1, 7) = '2017-04'; -- 降序累加
  • 求消费明细以及每个月有哪些顾客来过
1
2
3
select name,orderdate,cost,
collect_set(name) over(partition by substring(orderdate, 1, 7)) people
from business;
  • 查看顾客上次的购买时间
1
2
3
select name, orderdate, cost,
lag(orderdate, 1, '1970-01-01') over (partition by name order by orderdate) last_order -- lag后面必须跟一个排序窗口
from business;
  • 查询前20%订单的信息
1
2
3
4
5
6
7
8
9
10
11
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted -- ntile函数会把窗口分成几份,然后把份数填进去
from business
) t
where sorted = 1;

select * from
(select name,orderdate,cost,
percent_rank() over (order by orderdate) pr
from business) a
where a.pr <= 0.2;
  • 关于窗口限定范围的详细范例
1
2
3
4
5
6
7
8
9
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;
  • 哪些顾客两天内来过我的店
1
2
3
4
5
6
7
select distinct name
from (
select name,orderdate,
lag(orderdate, 1) over(partition by name order by orderdate asc) last_date
from business
) a
where datediff(orderdate, last_date) = 2;
Rank
  • RANK() 排序相同时会重复,总数不会变
  • DENSE_RANK() 排序相同时会重复,总数会减少
  • ROW_NUMBER() 会根据顺序计算
  • 案例操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-------------+----------------+--------------+
| score.name | score.subject | score.score |
+-------------+----------------+--------------+
| 孙悟空 | 语文 | 87 |
| 孙悟空 | 数学 | 95 |
| 孙悟空 | 英语 | 68 |
| 大海 | 语文 | 94 |
| 大海 | 数学 | 56 |
| 大海 | 英语 | 84 |
| 宋宋 | 语文 | 64 |
| 宋宋 | 数学 | 86 |
| 宋宋 | 英语 | 84 |
| 婷婷 | 语文 | 65 |
| 婷婷 | 数学 | 85 |
| 婷婷 | 英语 | 78 |
+-------------+----------------+--------------+

根据以上数据计算各科成绩排名

1
2
3
4
5
select *,
rank() over(partition by subject order by score desc), -- rank会根据窗口里的排序给予对应的序号,排序相同时序号会相同,总数不会少
dense_rank() over(partition by subject order by score desc), -- dense_rank会根据窗口里的排序给予对应的序号,排序相同时序号会相同,但是不会累加,会导致排名减少
row_number() over (partition by subject order by score desc) -- 给每一行进行编号,按照顺序给予号码
from score;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-------------+----------------+--------------+----------------+----------------------+----------------------+
| score.name | score.subject | score.score | rank_window_0 | dense_rank_window_1 | row_number_window_2 |
+-------------+----------------+--------------+----------------+----------------------+----------------------+
| 孙悟空 | 数学 | 95 | 1 | 1 | 1 |
| 宋宋 | 数学 | 86 | 2 | 2 | 2 |
| 婷婷 | 数学 | 85 | 3 | 3 | 3 |
| 大海 | 数学 | 56 | 4 | 4 | 4 |
| 大海 | 英语 | 84 | 1 | 1 | 1 |
| 宋宋 | 英语 | 84 | 1 | 1 | 2 |
| 婷婷 | 英语 | 78 | 3 | 2 | 3 |
| 孙悟空 | 英语 | 68 | 4 | 3 | 4 |
| 大海 | 语文 | 94 | 1 | 1 | 1 |
| 孙悟空 | 语文 | 87 | 2 | 2 | 2 |
| 婷婷 | 语文 | 65 | 3 | 3 | 3 |
| 宋宋 | 语文 | 64 | 4 | 4 | 4 |
+-------------+----------------+--------------+----------------+----------------------+----------------------+

日期操作函数

  • current_date(),返回当前日期
  • 日期加减
    • date_add(date, num_days),计算date往后推num_days的日期
    • date_sub(date, num_days),计算date往前推num_days的日期
    • datediff(date1, date2),计算两个日期差值

自定义函数

函数类型
  • UDF,一进多出
  • UDTF,一进多出,表生成
  • UDAF,多进一出,聚合函数
定义流程
  • 继承类
  • 实现方法
  • jar包上传到hive中

压缩与存储

概述

如果不进行配置的话,HDFS上存储数据默认为text文本格式,同时也不会将数据进行压缩存储,导致会浪费大量性能;第二Hadoop MR流程不会进行数据压缩

Hadoop对各种压缩格式的支持

压缩格式 工具 算法 文件扩展名 是否可切分
DEFLATE DEFLATE .deflate
Gzip gzip DEFLATE .gz
bzip2 bzip2 bzip2 .bz2
LZO lzop LZO .lzo
Snappy Snappy .snappy
压缩格式 对应的编码/解码器
DEFLATE org.apache.hadoop.io.compress.DefaultCodec
gzip org.apache.hadoop.io.compress.GzipCodec
bzip2 org.apache.hadoop.io.compress.BZip2Codec
LZO com.hadoop.compression.lzo.LzopCodec
Snappy org.apache.hadoop.io.compress.SnappyCodec
压缩算法 原始文件大小 压缩文件大小 压缩速度 解压速度
gzip 8.3GB 1.8GB 17.5MB/s 58MB/s
bzip2 8.3GB 1.1GB 2.4MB/s 9.5MB/s
LZO 8.3GB 2.9GB 49.3MB/s 74.6MB/s

压缩参数配置

参数 默认值 阶段 建议
io.compression.codecs (在core-site.xml中配置) org.apache.hadoop.io.compress.DefaultCodec, org.apache.hadoop.io.compress.GzipCodec, org.apache.hadoop.io.compress.BZip2Codec, org.apache.hadoop.io.compress.Lz4Codec 输入压缩 Hadoop使用文件扩展名判断是否支持某种编解码器
mapreduce.map.output.compress false mapper输出 这个参数设为true启用压缩
mapreduce.map.output.compress.codec org.apache.hadoop.io.compress.DefaultCodec mapper输出 使用LZO、LZ4或snappy编解码器在此阶段压缩数据
mapreduce.output.fileoutputformat.compress false reducer输出 这个参数设为true启用压缩
mapreduce.output.fileoutputformat.compress.codec org.apache.hadoop.io.compress. DefaultCodec reducer输出 使用标准工具或者编解码器,如gzip和bzip2
mapreduce.output.fileoutputformat.compress.type RECORD reducer输出 SequenceFile输出使用的压缩类型:NONE和BLOCK

开启Map阶段压缩

  • 开启hive中间传输数据压缩功能
1
set hive.exec.compress.intermediate=true;
  • 开启mapreduce中map输出压缩功能
1
set mapreduce.map.output.compress=true;
  • 设置mapreduce中map输出数据的压缩方式
1
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

开启Reduce阶段压缩

  • 开启hive最终输出数据压缩功能
1
set hive.exec.compress.output=true;
  • 开启mapreduce最终输出数据压缩
1
set mapreduce.output.fileoutputformat.compress=true;
  • 设置mapreduce最终数据输出压缩方式
1
set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

Hive文件存储格式

  • TEXTFILE – 基于行存储
  • SEQUENCEFILE – 基于行存储
  • ORC – 基于列存储
  • PARQUET – 基于列存储
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="SNAPPY"); -- 创建一个用snappy方式压缩的orc表

create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as parquet
tblproperties("orc.compress"="SNAPPY"); -- 创建一个用snappy方式压缩的parquet表

Hive sql优化

  • 大表小表join,小表在左边比较好
  • 大表join大表,对空key进行过滤,进行初步数据清洗,或者对空key进行赋值,使其数据均匀
  • 动态分区
1
2
3
4
5
set hive.exec.dynamic.partition.mode=nonstrict; -- 开启hive动态分区

create table dept_partition(id int, name string) partitioned by (location int) row format delimited fields terminated by '\t'; -- 创建分区表

insert into dept_partition partition(location) select deptno, dname, loc from dept; -- 往分区表中插入数据,前两个键会默认匹配,第三个键被认为是分区键
发布于

2021-07-01

更新于

2021-07-01

许可协议

评论

:D 一言句子获取中...