Hive的操作大全（自查手册）

博主： panbh5500
发布时间：2022 年 08 月 22 日
345 次浏览
暂无评论
39197字数
分类： BigData

hive --service metastore

1 Hive数据类型

1.1 基本数据类型

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的字符数。

1.2 集合数据类型

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

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

示例：

假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为：

{
    "name": "songsong",
    "friends": ["bingbing" , "lili"] ,       //列表Array, 
    "children": {                      //键值Map,
        "xiao song": 19 ,
        "xiaoxiao song": 18
    }
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

基于上述数据结构，在Hive里创建对应的表，并导入数据。创建本地测试文件test.txt

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

注意：MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用“_”。

Hive上创建测试表test

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
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'; -- 行分隔符

导入文本数据到测试表

load data local inpath '/opt/module/hive/datas/test.txt' into table test;

访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式

hive (default)> select friends[1],children['xiao song'],address.city from test
where name="songsong";
OK
_c0     _c1     city
lili    18      beijing
Time taken: 0.076 seconds, Fetched: 1 row(s)

1.3 类型转化

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类型不可以转换为任何其它的类型。
可以使用cast操作显示进行数据类型转换

例如cast('1' as int)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。

2 DDL数据定义

2.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

避免要创建的数据库已经存在错误，增加if not exists判断
增加描述数据库信息 comment '描述信息'

hive>create database mydb
    >comment 'this is my database';
hive>desc database mydb;  //显示数据库信息

location用来指定创建位置，数据库在HDFS上的默认存储路径为/user/hive/warehouse/*.db
增加数据库和其相关的键-值对属性信息 with dbproperties(key1=value1， key2=value2 ...)

hive>create database myhive
    >with dbproperties('name'='pbh','data'='2022-07-26');
hive>desc database extended myhive;  //显示数据库详细信息

2.2 查询数据库

2.2.1 显示数据库

显示数据库 show databases

hive> show databases;

利用模糊过滤显示查询的数据库 like

hive> show databases like 'db_hive*';
OK
db_hive
db_hive_1

2.2.2 查看数据库详情

显示数据库信息desc database dbname

hive> desc database db_hive;
OK
db_hive     hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db    pbhUSER

显示数据库详细信息，desc database extended dbname

hive> desc database extended db_hive;
OK
db_hive     hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db    pbhUSER

2.2.3 切换当前数据库

hive (default)> use db_hive;

2.3 修改数据库

用户可以使用alter database命令为某个数据库的dbproperties设置或修改键-值对属性值，来描述这个数据库的属性信息。

hive (default)> alter database db_hive set dbproperties('createtime'='20220726');

2.4 删除数据库

删除空数据库drop database dbname
如果删除的数据库不存在，最好采用 if exists判断数据库是否存在drop database if exists dbname
如果数据库不为空，可以采用cascade命令，强制删除 drop database db_hive cascade

2.5 创建表

建表语法

create [external] table [if not exists] table_name
[(col_name data_type) [comment col_comment], ...]
[comment table_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 delimited fields terminated by xxx] 
[stored as file_format] 
[location hdfs_path]
[tblproperties (property_name=property_value, ...)]
[as select_statement]
[like table_name2]

字段解释说明
1. create table table_name 创建一个名为table_name的表。如果相同名字的表已经存在，则抛出异常；用户可以用 if not exists 选项来忽略这个异常。
2. external 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径(location)，在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3. comment：为表和列添加注释。
4. partitioned by：创建分区表
5. clustered by：创建分桶表
6. sorted by：对桶中的一个或多个列另外排序
7. row format delimited [fiflds 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：允许用户复制现有的表结构，但不复制数据。

2.5.1 管理表（亦称内部表）

理论

默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如， /user/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

创表实操

原始数据

1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
1009 ss9
1010 ss10
1011 ss11
1012 ss12
1013 ss13
1014 ss14
1015 ss15
1016 ss16

普通创建表

create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';

根据查询结果创建表（查询的结果会添加到新创建的表中）

create table if not exists student2 as select id, name from student;

根据已经存在的表结构创建表

create table if not exists student3 like student;

2.5.2 外部表

理论

因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
管理表和外部表的使用场景

每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

2.5.3 内部表与外部表的互相转换

查询表的类型

hive (default)> desc formatted student2;
Table Type:             MANAGED_TABLE

修改内部表 student2 为外部表

alter table student2 set tblproperties('EXTERNAL'='TRUE');

查询表的类型

hive (default)> desc formatted student2;
Table Type:             EXTERNAL_TABLE

修改外部表 student2 为内部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

查询表的类型

hive (default)> desc formatted student2;
Table Type:             MANAGED_TABLE

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

2.6 修改表

2.6.1 重命名表

alter table table_name rename to new_table_name

2.6.2 增加、修改和删除表分区

2.6.3 增加、修改、替换列信息

更新列

alter table table_name change [column] col_old_name col_new_name

增加和替换列

alter table table_name add|replace colu,ms (col_name data_type [comment col_comment], ...)

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

2.7 删除表

drop table table_name;

3 DML数据操作

3.1 数据导入

3.1.1 装载数据（Load）

语法

load data [local] inpath 'data_path' [overwrite] into table student [partition] (partcoll=vall,...)];

load data：表示加载数据

local：表示从本地加载数据到hive表；否则从HDFS加载到hive表

inpath：表示加载数据的路径

overwrite：表示覆盖表中已有数据，否则表示追加

into table：表示加载到哪张表

student：表示具体的表

partition：表示上传到指定分区

实操

create table student(id string, name string)
row format delimited fields terminated by '\t';
-- 加载本地文件到hive
load data local inpath '/opt/module/hive/datas/student.txt' into table default.student;
-- 加载HDFS文件到hive
load data inpath '/user/atguigu/hive/student.txt' into table default.student;

3.1.2 通过查询语句向表中插入数据（Insert）

-- 创建一张表
hive (default)> create table student_par(id int, name string) row format delimited fields terminated by '\t';
-- 插入基本数据
hive (default)> insert into table  student_par values(1,'wangwu'),(2,'zhaoliu');
-- 基本模式插入（根据单张表查询结果）
hive (default)> insert overwrite table student_par 
             select id, name from student ;

-- 多表（多分区）插入模式（根据多张表查询如果）
hive (default)> from student
              insert overwrite table student partition(month='201707')
              select id, name where month='201709'
              insert overwrite table student partition(month='201706')
              select id, name where month='201709';

3.1.3 查询语句中创建表并加载数据（as select）

create table if not exists student3
as select id, name from student;

3.1.4 Import数据到指定Hive表中

hive (default)> import table student2 from 
'/user/hive/warehouse/export/student';

先用export导出后，再将数据导入

3.2 数据导出

3.2.1 Insert导出

-- 将查询的结果导出到本地
hive (default)> insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;
-- 将查询的结果格式化导出到本地
hive(default)>insert overwrite local directory                  '/opt/module/hive/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
select * from student;
-- 将查询的结果格式化导出到HDFS上(没有local)
hive (default)> insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

3.2.2 Hadoop命令导出到本地

hive (default)> dfs -get /user/hive/warehouse/student/student.txt
/opt/module/datas/export/student3.txt;

3.2.3 Hive Sheel命令导出

基本语法：

hive -f/-e 执行语句或者脚本 > filepath

-- 示例
[pbh@hadoop102 hive]$ bin/hive -e 'select * from default.student;' >
 /opt/module/hive/datas/export/student4.txt;

3.2.4 Export 导出到HDFS上

(defahiveult)> export table default.student to
 '/user/hive/warehouse/export/student';

export和import主要用于两个Hadoop平台集群之间Hive表迁移。

3.2.5 Sqoop导出

4 查询

-- 查询语法总览
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

4.0 创建示例所需表

dept_data:

10  ACCOUNTING  1700
20  RESEARCH    1800
30  SALES   1900
40  OPERATIONS  1700

emp_data:

7369    SMITH   CLERK   7902    1980-12-17  800.00      20
7499    ALLEN   SALESMAN    7698    1981-2-20   1600.00 300.00  30
7521    WARD    SALESMAN    7698    1981-2-22   1250.00 500.00  30
7566    JONES   MANAGER 7839    1981-4-2    2975.00     20
7654    MARTIN  SALESMAN    7698    1981-9-28   1250.00 1400.00 30
7698    BLAKE   MANAGER 7839    1981-5-1    2850.00     30
7782    CLARK   MANAGER 7839    1981-6-9    2450.00     10
7788    SCOTT   ANALYST 7566    1987-4-19   3000.00     20
7839    KING    PRESIDENT       1981-11-17  5000.00     10
7844    TURNER  SALESMAN    7698    1981-9-8    1500.00 0.00    30
7876    ADAMS   CLERK   7788    1987-5-23   1100.00     20
7900    JAMES   CLERK   7698    1981-12-3   950.00      30
7902    FORD    ANALYST 7566    1981-12-3   3000.00     20
7934    MILLER  CLERK   7782    1982-1-23   1300.00     10

-- 创建员工表
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';
-- 创建部门表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
-- 导入数据
load data local inpath '/home/pbh/data/emp.txt' into table emp;
load data local inpath '/home/pbh/data/dept.txt' into table dept;

4.1 基本查询

4.1.1 全表和特定列查询

-- 全表查询
select * from table_name;
-- 只查询某几列
select column_name1, column_name2, column_name3 from table_name;

4.1.2 列别名

为了方便，常常给列和表起个别名，一般为列名+as+别名，亦可省略as。

-- 给列起别名
select column_name1 as c1, column_name2 as c2, column_name3 as c3 from table_name;
-- 省略as
select column_name1 c1, column_name2 c2, column_name3 c3 from table_name;

4.1.3 算术运算符

运算符	描述
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按位取反

4.1.4 常用函数

求总行数（count）

select count(*) cnt from table_name;

求最大最小值（max/min）

select max(column_name) cnt from table_name;

求和/平均值（sum/avg）

select sum(column_name) cnt from table_name;

4.1.5 Limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

-- 从下标为0的那一行开始，返回五条语句
hive (default)> select * from emp limit 5;
-- 从下标为2的那一行开始，返回三条语句
hive (default)> select * from emp limit 2,3;

4.1.6 where语句

where紧随from子句，用于将不满足条件的行过滤。

select * from table_name where column_name > 1000;

4.1.7 比较运算符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，如果一边为NULL，返回False
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	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相匹配，而不是只需与其字符串匹配。

4.1.8 Like与RLike

Like--模糊查找

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

RLike--正则

-- 查找以A开头的
select * from emp where ename LIKE 'A%';
-- 查找第二个字母是A的
select * from emp where ename LIKE '_A%';
-- 查找带A的
select * from emp where ename RLIKE '[A]';

4.1.9 逻辑运算符（And/Or/Not）

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

4.2 分组

4.2.1 group by 语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

-- 计算emp表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
-- 计算emp每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t 
group by
t.deptno, t.job;

4.2.2 Having 语句

having与where不同点

where后面不能写分组函数，而having后面可以使用分组函数。

having只用于group by分组后筛选数据。

实操

-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) from emp group by deptno;
-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) avg_sal from emp 
group by deptno 
having avg_sal > 2000;

4.3 join语句

4.3.1 等值 join

-- 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

4.3.2 内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select e.empno, e.ename, d.deptno from emp e 
join dept d 
on e.deptno = d.deptno;

4.3.3 左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e right 
join dept d 
on e.deptno = d.deptno;

4.3.4 右外连接

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e right 
join dept d 
on e.deptno = d.deptno;

4.3.5 满外连接

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

select e.empno, e.ename, d.deptno from emp e full 
join dept d 
on e.deptno = d.deptno;

4.3.6 多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件

数据准备

1700    Beijing
1800    London
1900    Tokyo

创建位置表

create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

导入数据

hive (default)> load data local inpath '/opt/module/datas/location.txt' into table location;

多表连接查询

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;

4.3.7 笛卡尔积

本来只是线性比较，结果成了列向量与行向量的矩阵乘，数据量瞬间爆炸。

会在以下条件中产生：

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

4.4 排序

4.4.1 全局排序（order by）

order by：全局排序，只有一个Reducer，其一般在select语句的结尾

asc表升序（默认），desc表降序

-- 查询员工信息按工资升序排列
select * from emp order by sal;
-- 查询员工信息按工资降序排列
select * from emp order by sal desc;
-- 按照别名排序（以员工薪资的2倍为例）
select ename, sal*2 twosal from emp order by twosal;
-- 多个列排序（按照部门和工资升序排序）
select ename, deptno, sal from emp order by deptno, sal;

4.4.2 每个reduce内部排序（sort by）

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。

Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。

-- 设置reduce个数
set mapreduce.job.reduces=3;
-- 查看设置reduce个数
set mapreduce.job.reduces;

-- 根据部门编号降序查看员工信息
select * from emp sort by deptno desc;

4.4.2 分区（distribute by）

Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

Ø distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。

Ø Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

4.4.3 cluster by (sort by与distribute by结合)

当distribute by和sort by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

5 分区表和分桶表

5.1 分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

5.1.1 基本操作

数据准备

//dept_20200401.log
10  ACCOUNTING  1700
20  RESEARCH    1800

//dept_20200402.log
30  SALES   1900
40  OPERATIONS  1700

//dept_20200403.log
50  TEST    2000
60  DEV 1900

创建分区表语法

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

加载数据

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402');
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');

查询分区表中数据

-- 单分区查询
hive (default)> select * from dept_partition where day='20200401';
-- 多分区联合查询
hive (default)> select * from dept_partition where day='20200401'
              union
              select * from dept_partition where day='20200402'
              union
              select * from dept_partition where day='20200403';
hive (default)> select * from dept_partition where day='20200401' or
                day='20200402' or day='20200403' ;

增加分区

-- 创建单个分区
hive (default)> alter table dept_partition add partition(day='20200404') ;
-- 同时创建多个分区
hive (default)> alter table dept_partition add partition(day='20200405') partition(day='20200406');

删除分区

-- 删除单个分区
hive (default)> alter table dept_partition drop partition (day='20200406');
-- 同时删除多个分区
hive (default)> alter table dept_partition drop partition (day='20200404'), partition(day='20200405');

查看分区表信息

-- 查看分区表有多少分区
hive> show partitions dept_partition;
-- 查看分区表结构
hive> desc formatted dept_partition;

5.1.2 二级分区

创建二级分区表

hive (default)> create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (day string, hour string)
               row format delimited fields terminated by '\t';

加载数据

-- 加载数据到二级分区表中
hive (default)> load data local inpath '/opt/module`/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401', hour='12');
-- 查询分区数据
hive (default)> select * from dept_partition2 where day='20200401' and hour='12';

把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

-- 方式一：上传数据后修复
-- 上传数据
hive (default)> dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
hive (default)> dfs -put /opt/module/datas/dept_20200401.log  /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
-- 查询数据（查询不到刚上传的数据）
hive (default)> select * from dept_partition2 where day='20200401' and hour='13';
-- 执行修复命令
hive> msck repair table dept_partition2;
-- 再次查询数据
hive (default)> select * from dept_partition2 where day='20200401' and hour='13';

-- 方式二：上传数据后添加分区
-- 上传数据
hive (default)> dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;
hive (default)> dfs -put /opt/module/hive/datas/dept_20200401.log  /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;
-- 执行添加分区
hive (default)> alter table dept_partition2 add partition(day='201709',hour='14');
-- 查询数据
hive (default)> select * from dept_partition2 where day='20200401' and hour='14';

-- 方式三：创建文件夹后load数据到分区
-- 创建目录
hive (default)> dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;
-- 上传数据
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401',hour='15');
-- 查询数据
hive (default)> select * from dept_partition2 where day='20200401' and hour='15';

5.1.3 动态分区

关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。

开启动态分区参数设置
1. 开启动态分区功能（默认true，开启）
hive.exec.dynamic.partition=true
1. 设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
hive.exec.dynamic.partition.mode=nonstrict
1. 在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
hive.exec.max.dynamic.partitions=1000
4. 在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

hive.exec.max.dynamic.partitions.pernode=100
1. 整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000
1. 当有空分区生成时，是否抛出异常。一般不需要设置。默认false
hive.error.on.empty.partition=false

实操

需求：将dept表中的数据按照地区（loc字段），插入到目标表dept_partition的相应分区中。

创建目标分区表

hive (default)> create table dept_partition_dy(id int, name string) partitioned by (loc int) row format delimited fields terminated by '\t';

设置动态分区

set hive.exec.dynamic.partition.mode = nonstrict;
hive (default)> insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;

查看目标分区表的分区情况

hive (default)> show partitions dept_partition;

5.2 分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

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

创建分桶表

// 数据准备
1001    ss1
1002    ss2
1003    ss3
1004    ss4
1005    ss5
1006    ss6
1007    ss7
1008    ss8
1009    ss9
1010    ss10
1011    ss11
1012    ss12
1013    ss13
1014    ss14
1015    ss15

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

-- 查看表结构
hive (default)> desc formatted stu_bucket;
Num Buckets:            4

-- 导入数据到分桶表中，load的方式
hive (default)> load data inpath   '/student.txt' into table stu_bucket;

-- 查询分桶的数据
hive(default)> select * from stu_buck;

分桶规则

根据结果可知：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
分桶表操作注意事项

（1）reduce的个数设置为-1,让Job自行决定需要用多少个reduce或者将reduce的个数设置为大于等于分桶表的桶数
（2）从hdfs中load数据到分桶表中，避免本地文件找不到问题
（3）不要使用本地模式

insert 方式将数据导入分桶表

insert into table stu_buck select * from student_insert ;

5.3 抽样查询

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

语法: tablesample(bucket x out of y)

查询表stu_buck中的数据

select * from stu_buck tablesample(bucket 1 out of 4 on id);

注意：x的值必须小于等于y的值

6 函数

6.1 系统内置函数

-- 查看系统自带的函数
hive> show functions;
-- 显示自带的函数的用法
hive> desc function upper;
-- 详细显示自带的函数的用法
hive> desc function extended upper;

6.2 常用内置函数

6.2.1 空字段赋值

函数说明：

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。
示例：

-- 如果emp表中员工的comm为NULL，则用-1代替
hive (default)> select comm,nvl(comm, -1) from emp;

-- 返回结果
+---------+---------+
|  comm   |   _c1   |
+---------+---------+
| NULL    | -1.0    |
| 300.0   | 300.0   |
| 500.0   | 500.0   |
| NULL    | -1.0    |
| 1400.0  | 1400.0  |
| NULL    | -1.0    |
| NULL    | -1.0    |
| NULL    | -1.0    |
| NULL    | -1.0    |
| 0.0     | 0.0     |
| NULL    | -1.0    |
| NULL    | -1.0    |
| NULL    | -1.0    |
| NULL    | -1.0    |
+---------+---------+

6.2.2 case when then else end

语法

case when 条件1 then 结果1 when 条件2 then 结果2 when 条件3 then 结果3 else 结果4 end
示例

-- 数据
name    dept_id sex
悟空  A   男
大海  A   男
宋宋  B   男
凤姐  A   女
婷姐  B   女
婷婷  B   女
-- 求出不同部门男女各多少人。结果应如下：
dept_Id     男       女
A           2       1
B           1       2

-- 创建hive表并导入数据
create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive/datas/emp_sex.txt' into table emp_sex;
-- 按需求查询数据
select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  -- 亦可sum(case when sex='男' then 1 else 0 end) male_count
  sum(case sex when sex='女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;

6.2.3 行转列

相关函数说明

concat(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

concat_ws(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间（类似于python的 'separator'.join(可迭代对象) ）;

select concat('2022', '-', '07', '-', '28');
-- 输出结果
+-------------+
|     _c0     |
+-------------+
| 2022-07-28  |
+-------------+
select concat_ws('-', '2022', '07', '28');
-- 输出结果
+-------------+
|     _c0     |
+-------------+
| 2022-07-28  |
+-------------+

collect_set(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段（类似于python的 list(set(可迭代对象)) ）。

-- 数据
孙悟空 白羊座 A
大海  射手座 A
宋宋  白羊座 B
猪八戒 白羊座 A
凤姐  射手座 A
苍老师 白羊座 B
-- 建表
create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/datas/person_info.txt" into table person_info;
-- 查看collect_set的功能
select collect_set(constellation) from person_info;
-- 输出结果
["白羊座","射手座"]
-- collect_set与concat_ws联合使用
select collect_set(concat_ws('|',constellation,blood_type)) from person_info;
["白羊座|A","射手座|A","白羊座|B"]

实操

-- 把星座和血型一样的人归类到一起。结果如下：
射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋|苍老师
-- 查询数据
SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT NAME ,CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info
)t1 
GROUP BY t1.c_b

6.2.4 列转行

相关函数说明
1. explode(col)：将hive一列中复杂的array或者map结构拆分成多行
2. split(string, '分隔符')：将字符串分割，若分隔符为 "." 或 "|" 等特殊符号，则需要使用 "[.]" 或 "[|]" ，"[]"则使用"\\[\\]"。
3. lateral view： lateral view 主要功能是将原本汇总在一条（行）的数据拆分成多条（行）成虚拟表，再与原表进行笛卡尔积，从而得到明细表。配合UDTF函数使用，一般情况下经常与explode函数搭配，explode的操作对象（列值）是 ARRAY 或者 MAP ,可以通过 split 函数将 String 类型的列值转成 ARRAY 来处理。
  
  用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
实操

-- 原始数据
  movie     category
《疑犯追踪》  悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》   战争,动作,灾难
-- 期望输出结果
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难
-- 创表导数据
create table movie_info(
    movie string, 
    category string) 
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/datas/movie_info.txt" into table movie_info;
-- 查询数据
SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ;

6.2.5 窗口函数

相关函数说明

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行数据
PARTITION BY xx：按xx分区
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
-- 注意：n必须为int类型

实操

-- 数据
name，orderdate，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
-- 需求
/*
（1）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景, 将每个顾客的cost按照日期进行累加
（4）查询每个顾客上次的购买时间
（5）查询前20%时间的订单信息
*/
-- 建表
create table business(
name string, 
d string,
c int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 导数据
load data local inpath "/opt/module/hive/datas/business.txt" into table business;

-- 需求1：查询在2017年4月份购买过的顾客及总人数
select name,count(*) over () 
from business 
where substring(orderdate,1,7) = '2017-04' 
group by name;

-- 需求2：查询顾客的购买明细及月购买总额
select name, d, c, sum(c) over(partition by mouth(d)) 
from business;

-- 需求3：将每个顾客的cost按照日期进行累加
select name, d, c,
sum(c) over() as s1,--所有行相加
sum(c) over(partition by name) as s2,--按name分组，组内数据之和
sum(c) over(partition by name order by d) as s3,--按name分组，组内数据按日期累加
sum(c) over(partition by name order by d rows between unbounded preceding and current row) as s4,--按name分组，组内数据按日期累加(同s3，实际上group by自动实现了窗口不断加1)
sum(c) over(partition by name order by d rows between 1 preceding and current row) as s5, --当前行和前面一行做聚合 
sum(c) over(partition by name order by d rows between 1 preceding AND 1 following) as s6,--当前行和前边一行及后面一行 
sum(c) over(partition by name order by d rows between current row and unbounded following) as s7 --当前行及后面所有行 
from business;

-- 需求4：查看顾客上次的购买时间
select name, d, c, 
lag(d, 1) over(partition by name order by d) t1,--上一次购买日期，无数据则使用默认NULL填充
lag(d, 2) over(partition by name order by d) t2,--倒数第二次购买日期
lag(d, 1, '1978-01-01') over(partition by name order by d) t3--上一次购买日期，无则1978-01-01填充
from business;

-- 需求5：查询前20%时间的订单信息
select t.name, t.d, t.c
from(
select name, d, c, ntile(5) g over(order by d) from business
) t
where g=1;

6.2.6 rank

函数说明

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

实操

-- data
name    subject score
孙悟空 语文  87
孙悟空 数学  95
孙悟空 英语  68
大海  语文  94
大海  数学  56
大海  英语  84
宋宋  语文  64
宋宋  数学  86
宋宋  英语  84
婷婷  语文  65
婷婷  数学  85
婷婷  英语  78
-- 建表导入数据
create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive/datas/score.txt' into table score;
-- 计算每门学科成绩排名
select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

6.2.7 常用日期函数

1. unix_timestamp:返回当前或指定时间的时间戳 
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');

2. from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);

3. current_date：当前日期
select current_date;

4. current_timestamp：当前的日期加时间
select current_timestamp;

5. to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');

6. year：获取年
select year('2020-10-28 12:12:12');

7. month：获取月
select month('2020-10-28 12:12:12');

8. day：获取日
select day('2020-10-28 12:12:12');

9. hour：获取时
select hour('2020-10-28 12:13:14');

10. minute：获取分
select minute('2020-10-28 12:13:14');

11. second：获取秒
select second('2020-10-28 12:13:14');

12. weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');

13. dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');

14. months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');

15. add_months：日期加减月
select add_months('2020-10-28',-3);

16. datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');

17. date_add：日期加天数
select date_add('2020-10-28',4);

18. date_sub：日期减天数
select date_sub('2020-10-28',-4);

19. last_day：日期的当月的最后一天
select last_day('2020-02-30');

20. date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');

6.2.8 常用取整函数

21. round： 四舍五入
select round(3.14);
select round(3.54);

22. ceil：  向上取整
select ceil(3.14);
select ceil(3.54);

23. floor： 向下取整
select floor(3.14);
select floor(3.54);

6.2.9 常用字符串操作函数

24. upper： 转大写
select upper('low');

25. lower： 转小写
select lower('low');

26. length： 长度
select length("atguigu");

27. trim：  前后去空格
select trim(" atguigu ");

28. lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');

29. rpad：  向右补齐，到指定长度
select rpad('atguigu',9,'g');

30. regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');

6.2.10 集合操作

31. size： 集合中元素的个数
select size(friends) from test3;

32. map_keys： 返回map中的key
select map_keys(children) from test3;

33. map_values: 返回map中的value
select map_values(children) from test3;

34. array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;

35. sort_array： 将array中的元素排序
select sort_array(friends) from test3;

6.2.11 多维分析

36. grouping sets:多维分析
--数据
A|3|男|45
B|3|女|55
C|2|女|74
D|3|男|90
E|1|男|80
F|2|女|92
G|1|女|95
H|1|男|95
I|3|女|99
J|3|男|99
--建表导数据
create table s2
(
   name string,
   class int,
   sex string,
   score int
)
row format delimited fields terminated by '|';
load data local inpath 's2.txt' into table s2;
--grouping sets示例
/*
数据表有4个字段：姓名、班级、性别、分数。
如果想按照以下3个粒度汇总：
1、每个班级的平均分数
2、每个性别的平均分数
3、每个班级下不同性别的平均分数
*/
select
    grouping__id, -- 内置变量，只要使用grouping sets就可以调用
    class,
    sex,
    avg(score) as avg_score
from
    s2
group by
    class,
    sex 
grouping sets(
    class, 
    sex,
    (class, sex)
)
-- 输出结果为
+---------------+--------+-------+------------+
| grouping__id  | class  |  sex  | avg_score  |
+---------------+--------+-------+------------+
| 2             | NULL   | 女     | 83.0      |
| 2             | NULL   | 男     | 81.8      |
| 1             | 1      | NULL   | 90.0      |
| 0             | 1      | 女     | 95.0      |
| 0             | 1      | 男     | 87.5      |
| 1             | 2      | NULL   | 83.0      |
| 0             | 2      | 女     | 83.0      |
| 1             | 3      | NULL   | 77.6      |
| 0             | 3      | 女     | 77.0      |
| 0             | 3      | 男     | 78.0      |
+---------------+--------+-------+------------+

最后修改：2022 年 08 月 22 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复

评论 *

私密评论

名称 *

🎲

邮箱

地址

巴涅波赫夫
模型直接用官方的，自己训练的准确率应该就上去了吧，理论上准确度...
Xiao
大佬，准确度比用yolo命令行出来的低了很多，是什么原因造成的...
君菇
大佬牛批
小有名气的道士
牛的，实现了。|´・ω・)ノ
Evan
hello之前的文章呢

Hive的操作大全（自查手册）

panbh5500 • 2022 年 08 月 22 日

<p>hive --service metastore</p>
<h1>1 Hive数据类型</h1>
<h2>1.1 基本数据类型</h2>
<table>
<thead>
<tr>
<th>Hive数据类型</th>
<th>Java数据类型</th>
<th>长度</th>
<th>例子</th>
</tr>
</thead>
<tbody>
<tr>
<td>TINYINT</td>
<td>byte</td>
<td>1byte有符号整数</td>
<td>20</td>
</tr>
<tr>
<td>SMALINT</td>
<td>short</td>
<td>2byte有符号整数</td>
<td>20</td>
</tr>
<tr>
<td><strong>INT</strong></td>
<td>int</td>
<td>4byte有符号整数</td>
<td>20</td>
</tr>
<tr>
<td><strong>BIGINT</strong></td>
<td>long</td>
<td>8byte有符号整数</td>
<td>20</td>
</tr>
<tr>
<td>BOOLEAN</td>
<td>boolean</td>
<td>布尔类型，true或者false</td>
<td>TRUE  FALSE</td>
</tr>
<tr>
<td>FLOAT</td>
<td>float</td>
<td>单精度浮点数</td>
<td>3.14159</td>
</tr>
<tr>
<td><strong>DOUBLE</strong></td>
<td>double</td>
<td>双精度浮点数</td>
<td>3.14159</td>
</tr>
<tr>
<td><strong>STRING</strong></td>
<td>string</td>
<td>字符系列。可以指定字符集。可以使用单引号或者双引号。</td>
<td>‘now is the time’   “for all good men”</td>
</tr>
<tr>
<td>TIMESTAMP</td>
<td></td>
<td>时间类型</td>
<td></td>
</tr>
<tr>
<td>BINARY</td>
<td></td>
<td>字节数组</td>
<td></td>
</tr>
</tbody>
</table>
<p>对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。</p>
<h2>1.2 集合数据类型</h2>
<table>
<thead>
<tr>
<th>数据类型</th>
<th>描述</th>
<th>语法示例</th>
</tr>
</thead>
<tbody>
<tr>
<td>STRUCT</td>
<td>和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last   STRING},那么第1个元素可以通过字段.first来引用。</td>
<td>struct()   例如struct&lt;street:string, city:string&gt;</td>
</tr>
<tr>
<td>MAP</td>
<td>MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键-&gt;值对是’first’-&gt;’John’和’last’-&gt;’Doe’，那么可以通过字段名[‘last’]获取最后一个元素</td>
<td>map()   例如map&lt;string, int&gt;</td>
</tr>
<tr>
<td>ARRAY</td>
<td>数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。</td>
<td>Array()   例如array<string></td>
</tr>
</tbody>
</table>
<p>Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。</p>
<p><strong>示例：</strong></p>
<ol>
<li>假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为：</li>
</ol>
<pre><code class="language-json">{
    "name": "songsong",
    "friends": ["bingbing" , "lili"] ,       //列表Array, 
    "children": {                      //键值Map,
        "xiao song": 19 ,
        "xiaoxiao song": 18
    }
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}</code></pre>
<ol start="2">
<li>基于上述数据结构，在Hive里创建对应的表，并导入数据。创建本地测试文件test.txt</li>
</ol>
<pre><code class="language-txt">songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing</code></pre>
<p>注意：MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用“_”。</p>
<ol start="3">
<li>Hive上创建测试表test</li>
</ol>
<pre><code class="language-sql">create table test(
name string,
friends array&lt;string&gt;,
children map&lt;string, int&gt;,
address struct&lt;street:string, city:string&gt;
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';</code></pre>
<p>字段解释：</p>
<p>row format delimited fields terminated by ','  -- 列分隔符</p>
<p>collection items terminated by '_'                      --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)</p>
<p>map keys terminated by ':'                                  -- MAP中的key与value的分隔符</p>
<p>lines terminated by '\n';                                       -- 行分隔符</p>
<ol start="4">
<li>导入文本数据到测试表</li>
</ol>
<pre><code class="language-shell">load data local inpath '/opt/module/hive/datas/test.txt' into table test;</code></pre>
<ol start="5">
<li>访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; select friends[1],children['xiao song'],address.city from test
where name="songsong";
OK
_c0     _c1     city
lili    18      beijing
Time taken: 0.076 seconds, Fetched: 1 row(s)</code></pre>
<h2>1.3 类型转化</h2>
<p>   Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。</p>
<ol>
<li>
<p><strong>隐式类型转换规则如下</strong></p>
<blockquote>
<ol>
<li>任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。</li>
<li>所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。</li>
<li>TINYINT、SMALLINT、INT都可以转换为FLOAT。</li>
<li>BOOLEAN类型不可以转换为任何其它的类型。</li>
</ol>
</blockquote>
</li>
<li>
<p><strong>可以使用cast操作显示进行数据类型转换</strong></p>
<p>例如cast('1' as int)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。</p>
</li>
</ol>
<h1>2 DDL数据定义</h1>
<h2>2.1 创建数据库</h2>
<pre><code class="language-sql">CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];</code></pre>
<ol>
<li>避免要创建的数据库已经存在错误，增加if not exists判断</li>
<li>增加描述数据库信息 comment '描述信息'</li>
</ol>
<pre><code class="language-sql">hive&gt;create database mydb
    &gt;comment 'this is my database';
hive&gt;desc database mydb;  //显示数据库信息</code></pre>
<ol start="3">
<li>
<p>location用来指定创建位置，数据库在HDFS上的默认存储路径为<strong>/user/hive/warehouse/*.db</strong></p>
</li>
<li>
<p>增加数据库和其相关的键-值对属性信息 with dbproperties(key1=value1， key2=value2 ...)</p>
</li>
</ol>
<pre><code class="language-sql">hive&gt;create database myhive
    &gt;with dbproperties('name'='pbh','data'='2022-07-26');
hive&gt;desc database extended myhive;  //显示数据库详细信息</code></pre>
<h2>2.2 查询数据库</h2>
<h3>2.2.1 显示数据库</h3>
<ol>
<li>显示数据库 show databases</li>
</ol>
<pre><code class="language-sql">hive&gt; show databases;</code></pre>
<ol start="2">
<li>利用模糊过滤显示查询的数据库 like</li>
</ol>
<pre><code class="language-sql">hive&gt; show databases like 'db_hive*';
OK
db_hive
db_hive_1</code></pre>
<h3>2.2.2 查看数据库详情</h3>
<ol>
<li>显示数据库信息desc database dbname</li>
</ol>
<pre><code class="language-sql">hive&gt; desc database db_hive;
OK
db_hive     hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db    pbhUSER </code></pre>
<ol start="2">
<li>显示数据库详细信息，desc database extended dbname</li>
</ol>
<pre><code class="language-sql">hive&gt; desc database extended db_hive;
OK
db_hive     hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db    pbhUSER </code></pre>
<h3>2.2.3 切换当前数据库</h3>
<pre><code class="language-sql">hive (default)&gt; use db_hive;</code></pre>
<h2>2.3 修改数据库</h2>
<p>用户可以使用alter database命令为某个数据库的dbproperties设置或修改键-值对属性值，来描述这个数据库的属性信息。</p>
<pre><code class="language-sql">hive (default)&gt; alter database db_hive set dbproperties('createtime'='20220726');</code></pre>
<h2>2.4 删除数据库</h2>
<ol>
<li>删除空数据库drop database dbname</li>
<li>如果删除的数据库不存在，最好采用 if exists判断数据库是否存在drop database if exists dbname</li>
<li>如果数据库不为空，可以采用cascade命令，<strong>强制删除</strong> drop database db_hive cascade</li>
</ol>
<h2>2.5 创建表</h2>
<ol>
<li>建表语法</li>
</ol>
<pre><code class="language-sql">create [external] table [if not exists] table_name
[(col_name data_type) [comment col_comment], ...]
[comment table_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 delimited fields terminated by xxx] 
[stored as file_format] 
[location hdfs_path]
[tblproperties (property_name=property_value, ...)]
[as select_statement]
[like table_name2]</code></pre>
<ol start="2">
<li>
<p>字段解释说明</p>
<blockquote>
<ol>
<li>
<p>create table table_name 创建一个名为table_name的表。如果相同名字的表已经存在，则抛出异常；用户可以用 if not exists 选项来忽略这个异常。</p>
</li>
<li>
<p>external 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径(location)，在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。</p>
</li>
<li>
<p>comment：为表和列添加注释。</p>
</li>
<li>
<p>partitioned by：创建分区表</p>
</li>
<li>
<p>clustered by：创建分桶表</p>
</li>
<li>
<p>sorted by：对桶中的一个或多个列另外排序</p>
</li>
<li>
<p>row format delimited [fiflds 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, ...)]</p>
<p>用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。</p>
<p>SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。</p>
</li>
<li>
<p>stored as 指定存储文件类型</p>
<p>常用的存储文件类型：sequencefile（二进制序列文件）、textfile（文本）、rcfile（列式存储格式文件）</p>
<p>如果文件数据是纯文本，可以使用stored as textfile。如果数据需要压缩，使用stored as sequencefile。</p>
</li>
<li>
<p>location：指定表在HDFS上的存储位置。</p>
</li>
<li>
<p>as：后跟查询语句，根据查询结果创建表。</p>
</li>
<li>
<p>like：允许用户复制现有的表结构，但不复制数据。</p>
</li>
</ol>
</blockquote>
</li>
</ol>
<h3>2.5.1 管理表（亦称内部表）</h3>
<ol>
<li>理论</li>
</ol>
<blockquote>
<p>默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如， /user/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。</p>
</blockquote>
<ol start="2">
<li>
<p>创表实操</p>
<ol start="0">
<li>原始数据</li>
</ol>
<pre><code class="language-txt">1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
1009 ss9
1010 ss10
1011 ss11
1012 ss12
1013 ss13
1014 ss14
1015 ss15
1016 ss16</code></pre>
<ol>
<li>普通创建表</li>
</ol>
<pre><code class="language-sql">create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';</code></pre>
<ol start="2">
<li>根据查询结果创建表（查询的结果会添加到新创建的表中）</li>
</ol>
<pre><code class="language-sql">create table if not exists student2 as select id, name from student;</code></pre>
<ol start="3">
<li>根据已经存在的表结构创建表</li>
</ol>
<pre><code class="language-sql">create table if not exists student3 like student;</code></pre>
</li>
</ol>
<h3>2.5.2 外部表</h3>
<ol>
<li>
<p>理论</p>
<p>因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。</p>
</li>
<li>
<p>管理表和外部表的使用场景</p>
<p>每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。</p>
</li>
</ol>
<h3>2.5.3 内部表与外部表的互相转换</h3>
<ol>
<li>查询表的类型</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; desc formatted student2;
Table Type:             MANAGED_TABLE</code></pre>
<ol start="2">
<li>修改内部表 student2 为外部表</li>
</ol>
<pre><code class="language-sql">alter table student2 set tblproperties('EXTERNAL'='TRUE');</code></pre>
<ol start="3">
<li>查询表的类型</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; desc formatted student2;
Table Type:             EXTERNAL_TABLE</code></pre>
<ol start="4">
<li>修改外部表 student2 为内部表</li>
</ol>
<pre><code class="language-sql">alter table student2 set tblproperties('EXTERNAL'='FALSE');</code></pre>
<ol start="5">
<li>查询表的类型</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; desc formatted student2;
Table Type:             MANAGED_TABLE</code></pre>
<blockquote>
<p>注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！</p>
</blockquote>
<h2>2.6 修改表</h2>
<h3>2.6.1  重命名表</h3>
<pre><code class="language-sql">alter table table_name rename to new_table_name</code></pre>
<h3>2.6.2 增加、修改和删除表分区</h3>
<h3>2.6.3 增加、修改、替换列信息</h3>
<ol>
<li>更新列</li>
</ol>
<pre><code class="language-sql">alter table table_name change [column] col_old_name col_new_name</code></pre>
<ol start="2">
<li>增加和替换列</li>
</ol>
<pre><code class="language-sql">alter table table_name add|replace colu,ms (col_name data_type [comment col_comment], ...) </code></pre>
<blockquote>
<p>ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。</p>
</blockquote>
<h2>2.7 删除表</h2>
<pre><code class="language-sql">drop table table_name;</code></pre>
<h1>3 DML数据操作</h1>
<h2>3.1 数据导入</h2>
<h3>3.1.1 装载数据（Load）</h3>
<ol>
<li>语法</li>
</ol>
<pre><code class="language-sql">load data [local] inpath 'data_path' [overwrite] into table student [partition] (partcoll=vall,...)];</code></pre>
<blockquote>
<ol>
<li>load data：表示加载数据</li>
<li>local：表示从本地加载数据到hive表；否则从HDFS加载到hive表</li>
<li>inpath：表示加载数据的路径</li>
<li>overwrite：表示覆盖表中已有数据，否则表示追加</li>
<li>into table：表示加载到哪张表</li>
<li>student：表示具体的表</li>
<li>partition：表示上传到指定分区</li>
</ol>
</blockquote>
<ol start="2">
<li>实操</li>
</ol>
<pre><code class="language-sql">create table student(id string, name string)
row format delimited fields terminated by '\t';
-- 加载本地文件到hive
load data local inpath '/opt/module/hive/datas/student.txt' into table default.student;
-- 加载HDFS文件到hive
load data inpath '/user/atguigu/hive/student.txt' into table default.student;</code></pre>
<h3>3.1.2 通过查询语句向表中插入数据（Insert）</h3>
<pre><code class="language-sql">-- 创建一张表
hive (default)&gt; create table student_par(id int, name string) row format delimited fields terminated by '\t';
-- 插入基本数据
hive (default)&gt; insert into table  student_par values(1,'wangwu'),(2,'zhaoliu');
-- 基本模式插入（根据单张表查询结果）
hive (default)&gt; insert overwrite table student_par 
             select id, name from student ; </code></pre>
<pre><code class="language-sql">-- 多表（多分区）插入模式（根据多张表查询如果）
hive (default)&gt; from student
              insert overwrite table student partition(month='201707')
              select id, name where month='201709'
              insert overwrite table student partition(month='201706')
              select id, name where month='201709';</code></pre>
<h3>3.1.3 查询语句中创建表并加载数据（as select）</h3>
<pre><code class="language-sql">create table if not exists student3
as select id, name from student;</code></pre>
<h3>3.1.4  Import数据到指定Hive表中</h3>
<pre><code class="language-sql">hive (default)&gt; import table student2 from 
'/user/hive/warehouse/export/student';</code></pre>
<blockquote>
<p>先用export导出后，再将数据导入</p>
</blockquote>
<h2>3.2 数据导出</h2>
<h3>3.2.1 Insert导出</h3>
<pre><code class="language-sql">-- 将查询的结果导出到本地
hive (default)&gt; insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;
-- 将查询的结果格式化导出到本地
hive(default)&gt;insert overwrite local directory                  '/opt/module/hive/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
select * from student;
-- 将查询的结果格式化导出到HDFS上(没有local)
hive (default)&gt; insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;</code></pre>
<h3>3.2.2 Hadoop命令导出到本地</h3>
<pre><code class="language-sql">hive (default)&gt; dfs -get /user/hive/warehouse/student/student.txt
/opt/module/datas/export/student3.txt;</code></pre>
<h3>3.2.3 Hive Sheel命令导出</h3>
<blockquote>
<p>基本语法：</p>
<p>hive -f/-e 执行语句或者脚本 &gt; filepath</p>
</blockquote>
<pre><code class="language-sql">-- 示例
[pbh@hadoop102 hive]$ bin/hive -e 'select * from default.student;' &gt;
 /opt/module/hive/datas/export/student4.txt;</code></pre>
<h3>3.2.4 Export 导出到HDFS上</h3>
<pre><code class="language-sql">(defahiveult)&gt; export table default.student to
 '/user/hive/warehouse/export/student';</code></pre>
<blockquote>
<p>export和import主要用于两个Hadoop平台集群之间Hive表迁移。</p>
</blockquote>
<h3>3.2.5 Sqoop导出</h3>
<h1>4 查询</h1>
<pre><code class="language-sql">-- 查询语法总览
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]</code></pre>
<h2>4.0 创建示例所需表</h2>
<p>dept_data:</p>
<pre><code class="language-txt">10  ACCOUNTING  1700
20  RESEARCH    1800
30  SALES   1900
40  OPERATIONS  1700</code></pre>
<p>emp_data:</p>
<pre><code class="language-txt">7369    SMITH   CLERK   7902    1980-12-17  800.00      20
7499    ALLEN   SALESMAN    7698    1981-2-20   1600.00 300.00  30
7521    WARD    SALESMAN    7698    1981-2-22   1250.00 500.00  30
7566    JONES   MANAGER 7839    1981-4-2    2975.00     20
7654    MARTIN  SALESMAN    7698    1981-9-28   1250.00 1400.00 30
7698    BLAKE   MANAGER 7839    1981-5-1    2850.00     30
7782    CLARK   MANAGER 7839    1981-6-9    2450.00     10
7788    SCOTT   ANALYST 7566    1987-4-19   3000.00     20
7839    KING    PRESIDENT       1981-11-17  5000.00     10
7844    TURNER  SALESMAN    7698    1981-9-8    1500.00 0.00    30
7876    ADAMS   CLERK   7788    1987-5-23   1100.00     20
7900    JAMES   CLERK   7698    1981-12-3   950.00      30
7902    FORD    ANALYST 7566    1981-12-3   3000.00     20
7934    MILLER  CLERK   7782    1982-1-23   1300.00     10</code></pre>
<pre><code class="language-sql">-- 创建员工表
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';
-- 创建部门表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
-- 导入数据
load data local inpath '/home/pbh/data/emp.txt' into table emp;
load data local inpath '/home/pbh/data/dept.txt' into table dept;</code></pre>
<h2>4.1 基本查询</h2>
<h3>4.1.1 全表和特定列查询</h3>
<pre><code class="language-sql">-- 全表查询
select * from table_name;
-- 只查询某几列
select column_name1, column_name2, column_name3 from table_name;</code></pre>
<h3>4.1.2 列别名</h3>
<p>为了方便，常常给列和表起个别名，一般为列名+as+别名，亦可省略as。</p>
<pre><code class="language-sql">-- 给列起别名
select column_name1 as c1, column_name2 as c2, column_name3 as c3 from table_name;
-- 省略as
select column_name1 c1, column_name2 c2, column_name3 c3 from table_name;</code></pre>
<h3>4.1.3 算术运算符</h3>
<table>
<thead>
<tr>
<th>运算符</th>
<th>描述</th>
</tr>
</thead>
<tbody>
<tr>
<td>A+B</td>
<td>A和B 相加</td>
</tr>
<tr>
<td>A-B</td>
<td>A减去B</td>
</tr>
<tr>
<td>A*B</td>
<td>A和B 相乘</td>
</tr>
<tr>
<td>A/B</td>
<td>A除以B</td>
</tr>
<tr>
<td>A%B</td>
<td>A对B取余</td>
</tr>
<tr>
<td>A&amp;B</td>
<td>A和B按位取与</td>
</tr>
<tr>
<td>A|B</td>
<td>A和B按位取或</td>
</tr>
<tr>
<td>A^B</td>
<td>A和B按位取异或</td>
</tr>
<tr>
<td>~A</td>
<td>A按位取反</td>
</tr>
</tbody>
</table>
<h3>4.1.4  常用函数</h3>
<ol>
<li>求总行数（count）</li>
</ol>
<pre><code class="language-sql">select count(*) cnt from table_name;</code></pre>
<ol start="2">
<li>求最大最小值（max/min）</li>
</ol>
<pre><code class="language-sql">select max(column_name) cnt from table_name;</code></pre>
<ol start="3">
<li>求和/平均值（sum/avg）</li>
</ol>
<pre><code class="language-sql">select sum(column_name) cnt from table_name;</code></pre>
<h3>4.1.5 Limit语句</h3>
<p>典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。</p>
<pre><code class="language-sql">-- 从下标为0的那一行开始，返回五条语句
hive (default)&gt; select * from emp limit 5;
-- 从下标为2的那一行开始，返回三条语句
hive (default)&gt; select * from emp limit 2,3;</code></pre>
<h3>4.1.6 where语句</h3>
<p>where紧随from子句，用于将不满足条件的行过滤。</p>
<pre><code class="language-sql">select * from table_name where column_name &gt; 1000;</code></pre>
<h3>4.1.7 比较运算符</h3>
<table>
<thead>
<tr>
<th>操作符</th>
<th>支持的数据类型</th>
<th>描述</th>
</tr>
</thead>
<tbody>
<tr>
<td>A=B</td>
<td>基本数据类型</td>
<td>如果A等于B则返回TRUE，反之返回FALSE</td>
</tr>
<tr>
<td>A&lt;=&gt;B</td>
<td>基本数据类型</td>
<td>如果A和B都为NULL，则返回TRUE，如果一边为NULL，返回False</td>
</tr>
<tr>
<td>A&lt;&gt;B,   A!=B</td>
<td>基本数据类型</td>
<td>A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE</td>
</tr>
<tr>
<td>A&lt;B</td>
<td>基本数据类型</td>
<td>A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE</td>
</tr>
<tr>
<td>A&lt;=B</td>
<td>基本数据类型</td>
<td>A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE</td>
</tr>
<tr>
<td>A&gt;B</td>
<td>基本数据类型</td>
<td>A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE</td>
</tr>
<tr>
<td>A&gt;=B</td>
<td>基本数据类型</td>
<td>A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE</td>
</tr>
<tr>
<td>A [NOT] BETWEEN B   AND C</td>
<td>基本数据类型</td>
<td>如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。</td>
</tr>
<tr>
<td>A IS NULL</td>
<td>所有数据类型</td>
<td>如果A等于NULL，则返回TRUE，反之返回FALSE</td>
</tr>
<tr>
<td>A IS NOT NULL</td>
<td>所有数据类型</td>
<td>如果A不等于NULL，则返回TRUE，反之返回FALSE</td>
</tr>
<tr>
<td>IN(数值1, 数值2)</td>
<td>所有数据类型</td>
<td>使用 IN运算显示列表中的值</td>
</tr>
<tr>
<td>A [NOT] LIKE B</td>
<td>STRING 类型</td>
<td>B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。</td>
</tr>
<tr>
<td>A RLIKE B, A   REGEXP B</td>
<td>STRING 类型</td>
<td>B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。</td>
</tr>
</tbody>
</table>
<h3>4.1.8 Like与RLike</h3>
<ol>
<li>Like--模糊查找</li>
</ol>
<blockquote>
<p>% 代表零个或多个字符(任意个字符)。</p>
<p>_ 代表一个字符。</p>
</blockquote>
<ol start="2">
<li>RLike--正则</li>
</ol>
<pre><code class="language-sql">-- 查找以A开头的
select * from emp where ename LIKE 'A%';
-- 查找第二个字母是A的
select * from emp where ename LIKE '_A%';
-- 查找带A的
select * from emp where ename RLIKE '[A]';</code></pre>
<h3>4.1.9 逻辑运算符（And/Or/Not）</h3>
<table>
<thead>
<tr>
<th>操作符</th>
<th>含义</th>
</tr>
</thead>
<tbody>
<tr>
<td>AND</td>
<td>逻辑并</td>
</tr>
<tr>
<td>OR</td>
<td>逻辑或</td>
</tr>
<tr>
<td>NOT</td>
<td>逻辑否</td>
</tr>
</tbody>
</table>
<h2>4.2 分组</h2>
<h3>4.2.1 group by 语句</h3>
<p>GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。</p>
<pre><code class="language-sql">-- 计算emp表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
-- 计算emp每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t 
group by
t.deptno, t.job;</code></pre>
<h3>4.2.2 Having 语句</h3>
<ol>
<li>having与where不同点</li>
</ol>
<blockquote>
<ol>
<li>where后面不能写分组函数，而having后面可以使用分组函数。</li>
<li>having只用于group by分组后筛选数据。</li>
</ol>
</blockquote>
<ol start="2">
<li>实操</li>
</ol>
<pre><code class="language-sql">-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) from emp group by deptno;
-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) avg_sal from emp 
group by deptno 
having avg_sal &gt; 2000;</code></pre>
<h2>4.3 join语句</h2>
<h3>4.3.1 等值 join</h3>
<pre><code class="language-sql">-- 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;</code></pre>
<h3>4.3.2 内连接</h3>
<p>内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。</p>
<pre><code class="language-sql">select e.empno, e.ename, d.deptno from emp e 
join dept d 
on e.deptno = d.deptno;</code></pre>
<h3>4.3.3 左外连接</h3>
<p>左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。</p>
<pre><code class="language-sql">select e.empno, e.ename, d.deptno from emp e right 
join dept d 
on e.deptno = d.deptno;</code></pre>
<h3>4.3.4 右外连接</h3>
<p>右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。</p>
<pre><code class="language-sql">select e.empno, e.ename, d.deptno from emp e right 
join dept d 
on e.deptno = d.deptno;</code></pre>
<h3>4.3.5 满外连接</h3>
<p>满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。</p>
<pre><code class="language-sql">select e.empno, e.ename, d.deptno from emp e full 
join dept d 
on e.deptno = d.deptno;</code></pre>
<h3>4.3.6 多表连接</h3>
<p>注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件</p>
<ol start="0">
<li>数据准备</li>
</ol>
<pre><code class="language-txt">1700    Beijing
1800    London
1900    Tokyo</code></pre>
<ol>
<li>创建位置表</li>
</ol>
<pre><code class="language-sql">create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';</code></pre>
<ol start="2">
<li>导入数据</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; load data local inpath '/opt/module/datas/location.txt' into table location;</code></pre>
<ol start="3">
<li>多表连接查询</li>
</ol>
<pre><code class="language-sql">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;</code></pre>
<h3>4.3.7 笛卡尔积</h3>
<p>本来只是线性比较，结果成了列向量与行向量的矩阵乘，数据量瞬间爆炸。</p>
<p>会在以下条件中产生：</p>
<blockquote>
<p>（1）省略连接条件</p>
<p>（2）连接条件无效</p>
<p>（3）所有表中的所有行互相连接</p>
</blockquote>
<h2>4.4 排序</h2>
<h3>4.4.1 全局排序（order by）</h3>
<p>order by：全局排序，只有一个Reducer，其一般在select语句的结尾</p>
<p>asc表升序（默认），desc表降序</p>
<pre><code class="language-sql">-- 查询员工信息按工资升序排列
select * from emp order by sal;
-- 查询员工信息按工资降序排列
select * from emp order by sal desc;
-- 按照别名排序（以员工薪资的2倍为例）
select ename, sal*2 twosal from emp order by twosal;
-- 多个列排序（按照部门和工资升序排序）
select ename, deptno, sal from emp order by deptno, sal;</code></pre>
<h3>4.4.2 每个reduce内部排序（sort by）</h3>
<p>Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用<strong>sort by</strong>。</p>
<p>Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。</p>
<pre><code class="language-sql">-- 设置reduce个数
set mapreduce.job.reduces=3;
-- 查看设置reduce个数
set mapreduce.job.reduces;</code></pre>
<pre><code class="language-sql">-- 根据部门编号降序查看员工信息
select * from emp sort by deptno desc;</code></pre>
<h3>4.4.2 分区（distribute by）</h3>
<p>Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。<strong>distribute by</strong> 子句可以做这件事。<strong>distribute by</strong>类似MR中partition（自定义分区），进行分区，结合sort by使用。 </p>
<p>对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。</p>
<blockquote>
<p>Ø  distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。</p>
<p>Ø  Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。</p>
</blockquote>
<h3>4.4.3 cluster by (sort by与distribute by结合)</h3>
<p>当distribute by和sort by字段相同时，可以使用cluster by方式。</p>
<p>cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。</p>
<pre><code class="language-sql">select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;</code></pre>
<h1>5 分区表和分桶表</h1>
<h2>5.1 分区表</h2>
<p>分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。<strong>Hive中的分区就是分目录</strong>，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。</p>
<h3>5.1.1 基本操作</h3>
<ol>
<li>数据准备</li>
</ol>
<pre><code class="language-txt">//dept_20200401.log
10  ACCOUNTING  1700
20  RESEARCH    1800

//dept_20200402.log
30  SALES   1900
40  OPERATIONS  1700

//dept_20200403.log
50  TEST    2000
60  DEV 1900</code></pre>
<ol start="2">
<li>创建分区表语法</li>
</ol>
<pre><code class="language-sql">create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';</code></pre>
<ol start="3">
<li>加载数据</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
hive (default)&gt; load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402');
hive (default)&gt; load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');</code></pre>
<ol start="4">
<li>查询分区表中数据</li>
</ol>
<pre><code class="language-sql">-- 单分区查询
hive (default)&gt; select * from dept_partition where day='20200401';
-- 多分区联合查询
hive (default)&gt; select * from dept_partition where day='20200401'
              union
              select * from dept_partition where day='20200402'
              union
              select * from dept_partition where day='20200403';
hive (default)&gt; select * from dept_partition where day='20200401' or
                day='20200402' or day='20200403' ;</code></pre>
<ol start="5">
<li>增加分区</li>
</ol>
<pre><code class="language-sql">-- 创建单个分区
hive (default)&gt; alter table dept_partition add partition(day='20200404') ;
-- 同时创建多个分区
hive (default)&gt; alter table dept_partition add partition(day='20200405') partition(day='20200406');</code></pre>
<ol start="6">
<li>删除分区</li>
</ol>
<pre><code class="language-sql">-- 删除单个分区
hive (default)&gt; alter table dept_partition drop partition (day='20200406');
-- 同时删除多个分区
hive (default)&gt; alter table dept_partition drop partition (day='20200404'), partition(day='20200405');</code></pre>
<ol start="7">
<li>查看分区表信息</li>
</ol>
<pre><code class="language-sql">-- 查看分区表有多少分区
hive&gt; show partitions dept_partition;
-- 查看分区表结构
hive&gt; desc formatted dept_partition;</code></pre>
<h3>5.1.2 二级分区</h3>
<ol>
<li>创建二级分区表</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (day string, hour string)
               row format delimited fields terminated by '\t';</code></pre>
<ol start="2">
<li>加载数据</li>
</ol>
<pre><code class="language-sql">-- 加载数据到二级分区表中
hive (default)&gt; load data local inpath '/opt/module`/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401', hour='12');
-- 查询分区数据
hive (default)&gt; select * from dept_partition2 where day='20200401' and hour='12';</code></pre>
<ol start="3">
<li>把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式</li>
</ol>
<pre><code class="language-sql">-- 方式一：上传数据后修复
-- 上传数据
hive (default)&gt; dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
hive (default)&gt; dfs -put /opt/module/datas/dept_20200401.log  /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
-- 查询数据（查询不到刚上传的数据）
hive (default)&gt; select * from dept_partition2 where day='20200401' and hour='13';
-- 执行修复命令
hive&gt; msck repair table dept_partition2;
-- 再次查询数据
hive (default)&gt; select * from dept_partition2 where day='20200401' and hour='13';</code></pre>
<pre><code class="language-sql">-- 方式二：上传数据后添加分区
-- 上传数据
hive (default)&gt; dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;
hive (default)&gt; dfs -put /opt/module/hive/datas/dept_20200401.log  /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;
-- 执行添加分区
hive (default)&gt; alter table dept_partition2 add partition(day='201709',hour='14');
-- 查询数据
hive (default)&gt; select * from dept_partition2 where day='20200401' and hour='14';</code></pre>
<pre><code class="language-sql">-- 方式三：创建文件夹后load数据到分区
-- 创建目录
hive (default)&gt; dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;
-- 上传数据
hive (default)&gt; load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401',hour='15');
-- 查询数据
hive (default)&gt; select * from dept_partition2 where day='20200401' and hour='15';</code></pre>
<h3>5.1.3 动态分区</h3>
<p>关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。</p>
<ol>
<li>
<p>开启动态分区参数设置</p>
<ol>
<li>开启动态分区功能（默认true，开启）</li>
</ol>
<blockquote>
<p>hive.exec.dynamic.partition=true</p>
</blockquote>
<ol start="2">
<li>设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）</li>
</ol>
<blockquote>
<p>hive.exec.dynamic.partition.mode=nonstrict</p>
</blockquote>
<ol start="3">
<li>在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000</li>
</ol>
<blockquote>
<p>hive.exec.max.dynamic.partitions=1000<br />
4. 在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。</p>
</blockquote>
<blockquote>
<p>hive.exec.max.dynamic.partitions.pernode=100</p>
</blockquote>
<ol start="5">
<li>整个MR Job中，最大可以创建多少个HDFS文件。默认100000</li>
</ol>
<blockquote>
<p>hive.exec.max.created.files=100000</p>
</blockquote>
<ol start="6">
<li>当有空分区生成时，是否抛出异常。一般不需要设置。默认false</li>
</ol>
<blockquote>
<p>hive.error.on.empty.partition=false</p>
</blockquote>
</li>
<li>
<p>实操</p>
<p>需求：将dept表中的数据按照地区（loc字段），插入到目标表dept_partition的相应分区中。</p>
<ol>
<li>创建目标分区表</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; create table dept_partition_dy(id int, name string) partitioned by (loc int) row format delimited fields terminated by '\t';</code></pre>
<ol start="2">
<li>设置动态分区</li>
</ol>
<pre><code class="language-sql">set hive.exec.dynamic.partition.mode = nonstrict;
hive (default)&gt; insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;</code></pre>
<ol start="3">
<li>查看目标分区表的分区情况</li>
</ol>
<pre><code class="language-sql">hive (default)&gt; show partitions dept_partition;</code></pre>
</li>
</ol>
<h2>5.2 分桶表</h2>
<p>分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。</p>
<p>分桶是将数据集分解成更容易管理的若干部分的另一个技术。</p>
<p><strong>分区针对的是数据的存储路径；分桶针对的是数据文件。</strong></p>
<ol>
<li>创建分桶表</li>
</ol>
<pre><code class="language-txt">// 数据准备
1001    ss1
1002    ss2
1003    ss3
1004    ss4
1005    ss5
1006    ss6
1007    ss7
1008    ss8
1009    ss9
1010    ss10
1011    ss11
1012    ss12
1013    ss13
1014    ss14
1015    ss15</code></pre>
<pre><code class="language-sql">-- 创建分桶表
create table stu_bucket(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';</code></pre>
<pre><code class="language-sql">-- 查看表结构
hive (default)&gt; desc formatted stu_bucket;
Num Buckets:            4     </code></pre>
<pre><code class="language-sql">-- 导入数据到分桶表中，load的方式
hive (default)&gt; load data inpath   '/student.txt' into table stu_bucket;</code></pre>
<pre><code class="language-sql">-- 查询分桶的数据
hive(default)&gt; select * from stu_buck;</code></pre>
<ol start="2">
<li>
<p>分桶规则</p>
<p>根据结果可知：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。</p>
</li>
<li>
<p>分桶表操作注意事项</p>
</li>
</ol>
<blockquote>
<p>（1）reduce的个数设置为-1,让Job自行决定需要用多少个reduce或者将reduce的个数设置为大于等于分桶表的  桶数<br />
（2）从hdfs中load数据到分桶表中，避免本地文件找不到问题<br />
（3）不要使用本地模式</p>
</blockquote>
<ol start="4">
<li>insert 方式将数据导入分桶表</li>
</ol>
<pre><code class="language-sql">insert into table stu_buck select * from student_insert ;</code></pre>
<h2>5.3 抽样查询</h2>
<p>对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。</p>
<p>语法: tablesample(bucket x out of y)</p>
<p>查询表stu_buck中的数据</p>
<pre><code class="language-sql">select * from stu_buck tablesample(bucket 1 out of 4 on id);</code></pre>
<blockquote>
<p>注意：x的值必须小于等于y的值</p>
</blockquote>
<h1>6 函数</h1>
<h2>6.1 系统内置函数</h2>
<pre><code class="language-sql">-- 查看系统自带的函数
hive&gt; show functions;
-- 显示自带的函数的用法
hive&gt; desc function upper;
-- 详细显示自带的函数的用法
hive&gt; desc function extended upper;</code></pre>
<h2>6.2 常用内置函数</h2>
<h3>6.2.1 空字段赋值</h3>
<ol>
<li>
<p>函数说明：</p>
<p>NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。</p>
</li>
<li>
<p>示例：</p>
</li>
</ol>
<pre><code class="language-sql">-- 如果emp表中员工的comm为NULL，则用-1代替
hive (default)&gt; select comm,nvl(comm, -1) from emp;</code></pre>
<pre><code class="language-sql">-- 返回结果
+---------+---------+
|  comm   |   _c1   |
+---------+---------+
| NULL    | -1.0    |
| 300.0   | 300.0   |
| 500.0   | 500.0   |
| NULL    | -1.0    |
| 1400.0  | 1400.0  |
| NULL    | -1.0    |
| NULL    | -1.0    |
| NULL    | -1.0    |
| NULL    | -1.0    |
| 0.0     | 0.0     |
| NULL    | -1.0    |
| NULL    | -1.0    |
| NULL    | -1.0    |
| NULL    | -1.0    |
+---------+---------+</code></pre>
<h3>6.2.2 case when then else end</h3>
<ol>
<li>
<p>语法</p>
<p>case when 条件1 then 结果1 when 条件2 then 结果2 when 条件3 then 结果3  else 结果4 end</p>
</li>
<li>
<p>示例</p>
</li>
</ol>
<pre><code class="language-sql">-- 数据
name    dept_id sex
悟空  A   男
大海  A   男
宋宋  B   男
凤姐  A   女
婷姐  B   女
婷婷  B   女
-- 求出不同部门男女各多少人。结果应如下：
dept_Id     男       女
A           2       1
B           1       2</code></pre>
<pre><code class="language-sql">-- 创建hive表并导入数据
create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive/datas/emp_sex.txt' into table emp_sex;
-- 按需求查询数据
select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  -- 亦可sum(case when sex='男' then 1 else 0 end) male_count
  sum(case sex when sex='女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;</code></pre>
<h3>6.2.3 行转列</h3>
<ol>
<li>
<p>相关函数说明</p>
<p>concat(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;</p>
<p>concat_ws(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间（类似于python的 <strong>'separator'.join(可迭代对象)</strong> ）;</p>
</li>
</ol>
<pre><code class="language-sql">select concat('2022', '-', '07', '-', '28');
-- 输出结果
+-------------+
|     _c0     |
+-------------+
| 2022-07-28  |
+-------------+
select concat_ws('-', '2022', '07', '28');
-- 输出结果
+-------------+
|     _c0     |
+-------------+
| 2022-07-28  |
+-------------+</code></pre>
<p>   collect_set(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段（类似 于python的 <strong>list(set(可迭代对象))</strong> ）。</p>
<pre><code class="language-sql">-- 数据
孙悟空 白羊座 A
大海  射手座 A
宋宋  白羊座 B
猪八戒 白羊座 A
凤姐  射手座 A
苍老师 白羊座 B
-- 建表
create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/datas/person_info.txt" into table person_info;
-- 查看collect_set的功能
select collect_set(constellation) from person_info;
-- 输出结果
["白羊座","射手座"]
-- collect_set与concat_ws联合使用
select collect_set(concat_ws('|',constellation,blood_type)) from person_info;
["白羊座|A","射手座|A","白羊座|B"]</code></pre>
<ol start="2">
<li>实操</li>
</ol>
<pre><code class="language-sql">-- 把星座和血型一样的人归类到一起。结果如下：
射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋|苍老师
-- 查询数据
SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT NAME ,CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info
)t1 
GROUP BY t1.c_b</code></pre>
<h3>6.2.4 列转行</h3>
<ol>
<li>
<p>相关函数说明</p>
<blockquote>
<ol>
<li>
<p>explode(col)：将hive一列中复杂的array或者map结构拆分成多行</p>
</li>
<li>
<p>split(string, '分隔符')：将字符串分割，若分隔符为 "." 或 "|" 等特殊符号，则需要使用 "[.]" 或 "[|]" ，"[]"则使用"\\[\\]"。</p>
</li>
<li>
<p>lateral view： lateral view 主要功能是将原本汇总在一条（行）的数据拆分成多条（行）成虚拟表，再与原表进行笛卡尔积，从而得到明细表。配合UDTF函数使用，一般情况下经常与explode函数搭配，explode的操作对象（列值）是 ARRAY 或者 MAP ,可以通过 split 函数将 String 类型的列值转成 ARRAY 来处理。</p>
<p>用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias</p>
</li>
</ol>
</blockquote>
</li>
<li>
<p>实操</p>
</li>
</ol>
<pre><code class="language-sql">-- 原始数据
  movie     category
《疑犯追踪》  悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》   战争,动作,灾难
-- 期望输出结果
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难
-- 创表导数据
create table movie_info(
    movie string, 
    category string) 
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/datas/movie_info.txt" into table movie_info;
-- 查询数据
SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ;</code></pre>
<h3>6.2.5 窗口函数</h3>
<ol>
<li>相关函数说明</li>
</ol>
<pre><code class="language-sql">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行数据
PARTITION BY xx：按xx分区
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
-- 注意：n必须为int类型</code></pre>
<ol start="2">
<li>实操</li>
</ol>
<pre><code class="language-sql">-- 数据
name，orderdate，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
-- 需求
/*
（1）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景, 将每个顾客的cost按照日期进行累加
（4）查询每个顾客上次的购买时间
（5）查询前20%时间的订单信息
*/
-- 建表
create table business(
name string, 
d string,
c int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 导数据
load data local inpath "/opt/module/hive/datas/business.txt" into table business;

-- 需求1：查询在2017年4月份购买过的顾客及总人数
select name,count(*) over () 
from business 
where substring(orderdate,1,7) = '2017-04' 
group by name;

-- 需求2：查询顾客的购买明细及月购买总额
select name, d, c, sum(c) over(partition by mouth(d)) 
from business;

-- 需求3：将每个顾客的cost按照日期进行累加
select name, d, c,
sum(c) over() as s1,--所有行相加
sum(c) over(partition by name) as s2,--按name分组，组内数据之和
sum(c) over(partition by name order by d) as s3,--按name分组，组内数据按日期累加
sum(c) over(partition by name order by d rows between unbounded preceding and current row) as s4,--按name分组，组内数据按日期累加(同s3，实际上group by自动实现了窗口不断加1)
sum(c) over(partition by name order by d rows between 1 preceding and current row) as s5, --当前行和前面一行做聚合 
sum(c) over(partition by name order by d rows between 1 preceding AND 1 following) as s6,--当前行和前边一行及后面一行 
sum(c) over(partition by name order by d rows between current row and unbounded following) as s7 --当前行及后面所有行 
from business;

-- 需求4：查看顾客上次的购买时间
select name, d, c, 
lag(d, 1) over(partition by name order by d) t1,--上一次购买日期，无数据则使用默认NULL填充
lag(d, 2) over(partition by name order by d) t2,--倒数第二次购买日期
lag(d, 1, '1978-01-01') over(partition by name order by d) t3--上一次购买日期，无则1978-01-01填充
from business;

-- 需求5：查询前20%时间的订单信息
select t.name, t.d, t.c
from(
select name, d, c, ntile(5) g over(order by d) from business
) t
where g=1;</code></pre>
<h3>6.2.6 rank</h3>
<ol>
<li>函数说明</li>
</ol>
<blockquote>
<p>RANK() 排序相同时会重复，总数不会变</p>
<p>DENSE_RANK() 排序相同时会重复，总数会减少</p>
<p>ROW_NUMBER() 会根据顺序计算</p>
</blockquote>
<ol start="2">
<li>实操</li>
</ol>
<pre><code class="language-sql">-- data
name    subject score
孙悟空 语文  87
孙悟空 数学  95
孙悟空 英语  68
大海  语文  94
大海  数学  56
大海  英语  84
宋宋  语文  64
宋宋  数学  86
宋宋  英语  84
婷婷  语文  65
婷婷  数学  85
婷婷  英语  78
-- 建表导入数据
create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive/datas/score.txt' into table score;
-- 计算每门学科成绩排名
select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;</code></pre>
<h3>6.2.7 常用日期函数</h3>
<pre><code class="language-sql">1. unix_timestamp:返回当前或指定时间的时间戳 
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');

2. from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);

3. current_date：当前日期
select current_date;

4. current_timestamp：当前的日期加时间
select current_timestamp;

5. to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');

6. year：获取年
select year('2020-10-28 12:12:12');

7. month：获取月
select month('2020-10-28 12:12:12');

8. day：获取日
select day('2020-10-28 12:12:12');

9. hour：获取时
select hour('2020-10-28 12:13:14');

10. minute：获取分
select minute('2020-10-28 12:13:14');

11. second：获取秒
select second('2020-10-28 12:13:14');

12. weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');

13. dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');

14. months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');

15. add_months：日期加减月
select add_months('2020-10-28',-3);

16. datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');

17. date_add：日期加天数
select date_add('2020-10-28',4);

18. date_sub：日期减天数
select date_sub('2020-10-28',-4);

19. last_day：日期的当月的最后一天
select last_day('2020-02-30');

20. date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');</code></pre>
<h3>6.2.8 常用取整函数</h3>
<pre><code class="language-sql">21. round： 四舍五入
select round(3.14);
select round(3.54);

22. ceil：  向上取整
select ceil(3.14);
select ceil(3.54);

23. floor： 向下取整
select floor(3.14);
select floor(3.54);</code></pre>
<h3>6.2.9 常用字符串操作函数</h3>
<pre><code class="language-sql">24. upper： 转大写
select upper('low');

25. lower： 转小写
select lower('low');

26. length： 长度
select length("atguigu");

27. trim：  前后去空格
select trim(" atguigu ");

28. lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');

29. rpad：  向右补齐，到指定长度
select rpad('atguigu',9,'g');

30. regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');</code></pre>
<h3>6.2.10 集合操作</h3>
<pre><code class="language-sql">31. size： 集合中元素的个数
select size(friends) from test3;

32. map_keys： 返回map中的key
select map_keys(children) from test3;

33. map_values: 返回map中的value
select map_values(children) from test3;

34. array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;

35. sort_array： 将array中的元素排序
select sort_array(friends) from test3;</code></pre>
<h3>6.2.11 多维分析</h3>
<pre><code class="language-sql">36. grouping sets:多维分析
--数据
A|3|男|45
B|3|女|55
C|2|女|74
D|3|男|90
E|1|男|80
F|2|女|92
G|1|女|95
H|1|男|95
I|3|女|99
J|3|男|99
--建表导数据
create table s2
(
   name string,
   class int,
   sex string,
   score int
)
row format delimited fields terminated by '|';
load data local inpath 's2.txt' into table s2;
--grouping sets示例
/*
数据表有4个字段：姓名、班级、性别、分数。
如果想按照以下3个粒度汇总：
1、每个班级的平均分数
2、每个性别的平均分数
3、每个班级下不同性别的平均分数
*/
select
    grouping__id, -- 内置变量，只要使用grouping sets就可以调用
    class,
    sex,
    avg(score) as avg_score
from
    s2
group by
    class,
    sex 
grouping sets(
    class, 
    sex,
    (class, sex)
)
-- 输出结果为
+---------------+--------+-------+------------+
| grouping__id  | class  |  sex  | avg_score  |
+---------------+--------+-------+------------+
| 2             | NULL   | 女     | 83.0      |
| 2             | NULL   | 男     | 81.8      |
| 1             | 1      | NULL   | 90.0      |
| 0             | 1      | 女     | 95.0      |
| 0             | 1      | 男     | 87.5      |
| 1             | 2      | NULL   | 83.0      |
| 0             | 2      | 女     | 83.0      |
| 1             | 3      | NULL   | 77.6      |
| 0             | 3      | 女     | 77.0      |
| 0             | 3      | 男     | 78.0      |
+---------------+--------+-------+------------+</code></pre>