Hive详细讲解-基础语法快速入门

文章目录

- 1.DDL数据库相关操作
- - 1.1创建数据库
  - 1.2指定路径下创建数据库
  - 1.3添加额外信息创建with dbproperties
  - 1.4查看数据库结合like模糊查询
- 2.查看某一个数据库的相关信息
- - 2.1.如何查看数据库信息，extended可选
  - 2.2修改数据库
- 3.Hive基本数据类型
- 4.复杂数据类型
- 5.类型转换
- - 5.1类型隐士转换
  - 5.2类型显式转换
- 6.建表逻辑*
- - Hive的读流程：
  - Hive的写流程：
- 7.CTAS建表
- 8.create table like建表
- 9.创建表应用案例
- 10.查看表语法
- 11.DML
- - 1.Load
  - 2.Insert
  - 3.export
  - 4.import
- 12.Hive查询语句
- - 12.1关系运算函数
  - 12.2聚合函数*
  - 12.3 group by
- 13.Join语句
- 14.Union
- 15.Order by排序

1.DDL数据库相关操作

HiveSQL分为两部分DDL数据定义，DML数据操作
下述代码块中所有[]圈起来的操作都表示可选的操作

1.1创建数据库

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

COMMENT表示注释
Location表示HDFS路径
With dbproperties表示可以向数据库添加附加参数
案例如下所示：

在这里插入图片描述

注：若不指定路径，其默认路径为${hive.metastore.warehouse.dir}/database_name.db

在这里插入图片描述

1.2指定路径下创建数据库

在这里插入图片描述

1.3添加额外信息创建with dbproperties

create database db_hive2
with dbproperties ('name'='junchao');

1.4查看数据库结合like模糊查询

like查询可以使用通配符

*表示匹配任意个字符

|表示或的关系

在这里插入图片描述

2.查看某一个数据库的相关信息

2.1.如何查看数据库信息，extended可选

在这里插入图片描述

desc和describe效果相同

只有extended加入之后数据库才会展示parameters

在这里插入图片描述

2.2修改数据库

在这里插入图片描述

3.Hive基本数据类型

在这里插入图片描述

所有数据类型均小写
string和varchar的区别，varchar（2000）需要指定长度例如2000不能超过65535，string不需要
decimal表示小数10进度准度decimal(总位数，小数位数)
像decimal和varchar等需要传参的

4.复杂数据类型

在这里插入图片描述

建表建立一个字段的名称：array 表示该字段为数组类型，元素为string
map和java同理，但是请注意小写；
struct表示结构体，和JavaBean类似。理解定义和取值即可。

5.类型转换

类型转换情景：
- int类型数据和bigint类型数据。
- 往一张表insert数据-类型不匹配可以转换

5.1类型隐士转换

仅学习规则即可
转换为参与运算的两个类型的最小转换类型

在这里插入图片描述

具体逻辑如上图所示，其中，横轴从左到右类型逐渐变大。

5.2类型显式转换

cast函数做显示类型转换。
string转int数据如下所示

在这里插入图片描述

当然注意事项，你这个字符必须是能转化为int类型的数据。

6.建表逻辑*

在这里插入图片描述

row format关键字，可选，表示对每行数据进行序列化和反序列化
stored as：指定文件格式，textfile(默认)、sequence file、orc file、parquet file.和底层的文件格式相关
partitioned by

指定hive的分区字段

分区指的是hive一张表的数据按照你指定的分区字段将数据存储到不同的字段中。

clustered by

只当分桶表

将hive一张表的数据分散的存储到多个文件中。

Hive的读流程：

在这里插入图片描述

Hive的写流程：

在这里插入图片描述

hive中任意一张表的建表语句都需要inputformat、outputformat、serializer

若不配置，则默认配置序列化反序列化器

在这里插入图片描述

stored as后面紧跟inputformat、outputformat
row format重点：

ROW FORAMT DELIMITED 
[FIELDS TERMINATED BY char] 
[COLLECTION ITEMS TERMINATED BY char] 
[MAP KEYS TERMINATED BY char] 
[LINES TERMINATED BY char] 
[NULL DEFINED AS char]

1.delimited只要声明，hive就会默认对每行数据进行序列化和反序列化。
delimited关键字可选一些分隔符

在这里插入图片描述

null值的存储格式；

配置null defined as ;默认值是\N

2.serde序列化方式

与上述delimited序列化方式不同，serde可以指定特定的序列化方式如json格式。也可以自定义序列化格式。

row format serde serdename

3.stored as 跟文件格式，hive底层可以直接进行映射例如textinputformat

当前章节仅了解即可，后续详细讲解stored as等

7.CTAS建表

用户利用select查询结果进行建表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name 
[COMMENT table_comment] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

此处create 后面关键字仅有temporary

先前的可以指定external外部表等这是CTAS限制；

8.create table like建表

用户可以在一张已经存在的表上进行复刻，但是与先前的区别是此建表是没有数据的

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[LIKE exist_table_name]
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

9.创建表应用案例

创建一张表

在这里插入图片描述

向hdfs中直接CLI方式传入数据

在这里插入图片描述

按然后直接查询该表就能看到数据

在这里插入图片描述

这个表创建时没有声明外部表，external

那么默认表示是内部表

删除表之后

在这里插入图片描述

创建外部表测试案例

在这里插入图片描述

删除之后查看hdfs

在这里插入图片描述

仍然存在

练习2，serde和复杂数据类型
首先创建一个json格式文件

在这里插入图片描述

关于json格式文件存储格式，hive已经想好了，使用hive定义的JSON serde即可
json一级字段格式要求和表的字段一致
json的值和表的字段值要保持一致，可能使用到复杂数据类型。

在这里插入图片描述

一级字段要保持一致
结构体写法
struct<name:string> 
row format格式要求json
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
注意一下复杂数据类型即可
myhadoop.json的文件格式一定要是压平之后的文件格式。否则报错
此外服务器可以向hdfs上传任何后缀的文件，hive都能够识别到，只要文件格式和当前定义保持一致即可

在这里插入图片描述

练习3，复杂数据类型取值

在这里插入图片描述

练习4CTAS建表语句

在这里插入图片描述

练习5CTL建表语句

在这里插入图片描述

10.查看表语法

1.展示所有表

在这里插入图片描述

2.查看某一个表的具体信息

在这里插入图片描述

formatted效果更全

在这里插入图片描述

3.表的重命名 rename to

在这里插入图片描述

4.修改表的列信息

增加列
更新列
替换列

请你注意：

三种列的修改仅仅对表的元数据信息进行修改，而hdfs源文件不会进行修改，若有修改需求请你手动修改
新增列新增的column仅会追加到末尾

在这里插入图片描述

新增列

在这里插入图片描述

替换列

在这里插入图片描述

替换列指的是将所有字段重新赋值，也就是所有column进行重新替换。

更新列

在这里插入图片描述

经验小结：

所有sql语句后面table 后面大多跟表明，可能和语法有关

上述修改表字段类型时，参考隐式转换，string式不能直接转int

如果非要string转int，可以使用hive提供的强转
set hive.metastore.disallow.incompatible.col.type.changes=false;

11.DML

DML（data manipulate language）
DML学习章节没有数据库删除语句，原因式hive是海量数据的分析计算引擎，很少对数据进行删除操作。

1.Load

将文件导入表中，此功能和put一样

在这里插入图片描述

local指的是当前客户端向hdfs上传文件

命令行客户端那么表示命令行当前节点

hiveserver2表示hiverserver2所在节点

在这里插入图片描述

可以多次into操作，表示追加

在这里插入图片描述

将hdfs路径下数据load

在这里插入图片描述

overwrite方式load数据在这里插入图片描述

load从客户端load 文件时copy方式

hdfs load，是hdfs文件移动方式

2.Insert

在这里插入图片描述

普通插入语句

在这里插入图片描述

into可以替换为overwrite

与load不同

load若进行覆盖写，那么直接overwrite into 或into

而insert 要么into 要么overwrite

用户点名数据插入到表中

在这里插入图片描述

此处注意的点是：values后面一个括号表示要插入的一行数据

将查询结果插入到目标路径

此处必须用overwrite而不能用into

在这里插入图片描述

local表示本地否则是hdfs路径

row format声明行格式或声明每一行序列化反序列化

stored as声明文件格式

3.export

4.import

在这里插入图片描述

我的暂时实现不了，先暂定

12.Hive查询语句

基本语法

在这里插入图片描述

默认ALL模式除非单独加入distinct
select from where groupby having order by limit
where是对每一行数据进行过滤
having是对分组数据进行过滤

在这里插入图片描述

12.1关系运算函数

where，having后面紧跟过滤条件，不可避免会跟关系运算函数

在这里插入图片描述

between and关键字表示区间判断[a,b]，同理between前面可以加入not达到相反的效果
判空操作用is null ，is not null，你就记住，null是string类型，所以is很合理。
in 关键字和一个集合做判断，若在集合中，那么返回true
like关键字模糊匹配，此处模糊和show后面的like表示不一样，show 后面的like可以用*表示全部，此处平替为了%
注意一下正则匹配rlike regexp

在这里插入图片描述

12.2聚合函数*

将多行数据进行聚合到一行进行计算如count
语法

在这里插入图片描述

查询总行数

在这里插入图片描述

count(*)统计总行数不会忽略null
count(1)统计非Null总行数

COUNT(*) 会对所有列进行检查（即使不会读取实际列值），以确保行的存在性。

COUNT(1) 只会检查一列的存在性或用虚拟列来替代。

性能上count(1)略微高效

Count（id）此用法和count(1)一样，id可能为空因此不会统计在内

在这里插入图片描述

SQL跑MR执行过程

在这里插入图片描述

查询所有员工的薪水最大值

在这里插入图片描述

计算最大值，max聚合函数要放到select后面而不是过滤条件处
min聚合函数与count相同
平均值avg()同

12.3 group by

案例需求，我现在想要做更为复杂的统计，此处肯定不能count

在这里插入图片描述

当然你直接将job进行分组后还是要统计行数的

因此需要和count(*)一起使用。

在这里插入图片描述

老师精讲：分组聚合做的第一部将group by应用到用户指定的字段例如job，然后使用你指定的聚合函数对每一组进行统计等。

在这里插入图片描述

细节：

hive语法规定，你使用了分组聚合那么就不能在select查询分组以外的数据了

你能选的字段，分组字段和聚合函数

对分组聚合函数进行过滤

在这里插入图片描述

having专门对group by进行过滤。

13.Join语句

多张表进行横向拼接成一个宽表
join操作会生成一张虚拟表，此表包括join表的所有字段。当然deptno两张表都有，因此虚拟表都会存在

在这里插入图片描述

计算dept表中每一个loc有多少个员工

直接在join完成的虚拟表进行分组聚合操作即可

在这里插入图片描述

Join连接类型

等值连接，非等值连接（非等值连接不常用）

DG快捷键，格式化当前sql代码；

ctrl shift enter

在这里插入图片描述

Join连接

内连接

外连接

左外，右外连接，端外连接，多表连接，笛卡尔连接，联合

内连接

1.默认情况下单写一个join表示内连接。

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

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

在这里插入图片描述

内连接实现的效果就是二者连接条件的公共字段被返回

左外连接

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

左外连接

行返回的是左表的全部行
列返回的是左右量表的全部列
关于连接条件d表能够匹配上e表就直接返回，如果没有匹配上返回null

在这里插入图片描述

由外连接和左外连接相同

返回的行是右表的所有行，列是所有列

注意：若单join默认就是内连接，left join，right join，full join默认就是外连接

满外连接
full join 或 full outer join

在这里插入图片描述

返回所有行所有列，没匹配上返回null

在这里插入图片描述

笛卡尔积（慎用）
ab两个表粉笔m，n行数据，进行笛卡尔积操作，a表的每一行作为k与b表的每一行进行join关联

在这里插入图片描述

出现笛卡尔积的情况
- 内连接不写连接条件
- 连接条件无效例如on 1=1；
- 特殊写法
```
select * from emp,location
```

14.Union

Join将两个表的数据进行横向的拼接，而Union是将两个表的数据进行纵向拼接

在这里插入图片描述

上下两表union的前置要求
- 两个表的字段个数相同
- 每个字段的类型也要前后对应
union连接两个查询语句

在这里插入图片描述

Union注意事项
- 1.连接的必须是select查询语句，不能表union表
- 2.字段名不一致不影响union，只要字段个数，字段类型对应即可。字段名不一致，最终返回结果的字段名取第一个表定义的字段名；
- 3.union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。

15.Order by排序

按照工资升序排序 order by 默认升序

-- 15.按照工资升序排序
select * from emp
order by sal;

降序的话使用desc

-- 15.按照工资升序排序
select * from emp
order by sal desc;

注意：生产环境Order by是高危操作，可以看一下底层执行计划

在这里插入图片描述