Hive操作库、操作表及数据仓库的简单介绍

数据仓库和数据库

数据库和数仓区别

数据库与数据仓库的区别实际讲的是OLTP与OLAP的区别

操作型处理(数据库)，叫联机事务处理OLTP（On-Line Transaction Processing），也可以称面向用户交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常进行增删改查操作。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理。

分析型处理(数据仓库)，叫联机分析处理OLAP（On-Line Analytical Processing）,也可以称为面向专业分析人员进行数据分析,通常进行查询分析操作,一般针对某些主题的历史数据进行分析，支持管理决策。

数据仓库主要特征:

面向主题的（Subject-Oriented ）、集成的（Integrated）、非易失的（Non-Volatile）和时变的（Time-Variant ）

数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的。

数据仓库的出现，并不是要取代数据库,主要区别如下:

数据库是面向事务的设计，数据仓库是面向主题设计的。

数据库一般存储业务数据，数据仓库存储的一般是历史数据。

数据库是为捕获数据而设计，数据仓库是为分析数据而设计

数据库设计是尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的User表，记录用户名、密码等简单数据即可，符合业务应用，但是不符合分析。

数据仓库在设计是有意引入冗余，依照分析需求，分析维度、分析指标进行设计。

数仓的分层架构

数据仓库架构可分为三层——源数据层、数据仓库层、数据应用层:

源数据层（ODS）：此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。

数据仓库层（DW）：也称为细节层，DW层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗（去除了杂质）后的数据。

数据应用层（DA或APP）：前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。

ETL和ELT

广义上ETL:数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL（抽取Extract, 转化Transform , 装载Load）的过程。

但是在实际操作中将数据加载到仓库却产生了两种不同做法:ETL和ELT。

狭义上ETL: 先从数据源池中抽取数据，数据保存在临时暂存数据库中（ODS）。然后执行转换操作，将数据结构化并转换为适合目标数据仓库系统的形式,然后将结构化数据加载到数据仓库中进行分析。

ELT: 从数据源中抽取后立即加载。没有专门的临时数据库（ODS），这意味着数据会立即加载到单一的集中存储库中,数据在数据仓库系统中直接进行转换，然后进行分析

数据库操作

创建数据库: create database [if not exists] 库名 [comment '注释'] [location '路径'] [with dbproperties ('k'='v')];

注意: location路径默认是: hdfs://node1:8020/user/hive/warehouse

创建数据库语法:CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION position]; create database if not exists myhive;

use myhive;

查看数据库详细信息

desc database myhive;

数据库本质上就是在HDFS之上的文件夹。

默认数据库的存放路径是HDFS的：/user/hive/warehouse内

创建数据库并指定hdfs存储位置

create database myhive2 location '/myhive2';

使用location关键字，可以指定数据库在HDFS的存储路径。

删除一个空数据库，如果数据库下面有数据表，那么就会报错

drop database myhive;

强制删除数据库，包含数据库下面的表一起删除

drop database myhive2 cascade;

修改数据库的存储位置

alter database 库名set location 'hdfs://node1.itcast.cn:8020/路径';

修改存储位置不会响应之前存入的数据,会影响修改后存入的数据.

修改数据库属性.

修改当前数据库: use 库名;

alter database 库名 set dbproperties(‘k’=’v’);

查看数据库:

查看指定数据库信息: desc database 库名;

查看指定数据库扩展信息: desc database extended 库名;

查看当前使用的数据库: select current_database();

查看所有的数据库: show databases;

查看某库建库语句: show create database 库名;

1. 数据库和HDFS的关系

Hive的库在HDFS上就是一个以.db结尾的目录

默认存储1在：/user/hive/warehouse内

可以通过LOCATION关键字在创建的时候指定存储目录

-- todo ----------------------------hive 库操作-----------------------------
-- 创建数据库--------[常用]
create database if not exists myhive;
-- 使用数据库
use myhive;
-- 查看库的详细信息 -- 创建库就是在hdfs上创建一个文件夹
desc database myhive; -- hdfs://node1.itcast.cn:8020/user/hive/warehouse/myhive.db
-- 创建库并指定路径.[不常用]
create database if not exists myhive2 location '/data/myhive2.db';
desc database myhive2;
-- 删除数据库
drop database myhive2;
-- 强制删除库
-- 为了演示创建一个表.
use myhive;
create table test (id int,name string);
-- 删除库
drop database myhive;-- 删除失败: Database myhive is not empty. One or more tables exist.
-- 强制删除库
drop database myhive cascade ;
-- 修改数据库路径: alter database 库名 set location 'hdfs://node1.itcast.cn:8020/路径'
alter database myhive2 set location '/aaa/myhive2.db'; -- is not absolute. Please specify a complete absolute uri. /aaa/myhive2.db
alter database myhive2 set location 'hdfs://node1.itcast.cn:8020/aaa/myhive2.db';
desc database myhive2; -- hdfs://node1.itcast.cn:8020/aaa/myhive2.db [此时 hdfs页面看不到.]
use myhive2;
create table test (id int,name string); -- 在库下面创建表以后aaa目录和myhive2.db目录下都有test表
-- 修改数据库属性: alter database 库名 set dbproperties ('k'='v');
alter database myhive2 set dbproperties ('name'='tom');-- 可以给库携带键值对信息, 一般不常用.如果使用设置的一般是指定的key和value
-- 查看指定数据库信息: desc database 库名;
desc database myhive2; -- 可以查看库的位置信息,拥有者
-- 查看指定数据库扩展信息: desc database extended 库名;
desc database extended myhive2;-- 可以查看库的位置信息,拥有者,属性信息.
-- 查看当前使用的数据库: select current_database();
select current_database();
-- 查看所有的数据库: show databases;
show databases ;
-- 查看某库建库语句: show create database 库名;
show create database myhive2;

Hive操作表

表操作语法和数据类型

创建表语法

EXTERNAL，创建外部表
PARTITIONED BY， 分区表
CLUSTERED BY，分桶表
STORED AS，存储格式
LOCATION，存储位置
......，这些关键字，后续会慢慢接触。

数据类型

分类	类型	描述	字面量示例
原始类型	BOOLEAN	true/false	TRUE
	TINYINT	1字节的有符号整数 -128~127	1Y
	SMALLINT	2个字节的有符号整数，-32768~32767	1S
	INT	4个字节的带符号整数	1
	BIGINT	8字节带符号整数	1L
	FLOAT	4字节单精度浮点数1.0
	DOUBLE	8字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，变长	“a”,’b’
	VARCHAR	变长字符串	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组
	TIMESTAMP	时间戳，毫秒值精度	122327493795
	DATE	日期	‘2016-03-29’
		时间频率间隔
复杂类型	ARRAY	有序的的同类型的集合	array(1,2)
	MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
	STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
	UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

Hive中支持的数据类型还是比较多的

其中红色的是使用比较多的类型

内部表和外部表区别

表分类

Hive中可以创建的表有好几种类型，分别是：

内部表(MANAGED_TABLE)

分区表

分桶表

外部表(EXTERNAL_TABLE)

分区表

分桶表

在HDFS的表默认存储路径：/user/hive/warehouse/库名.db

查看表类型和详情:

DESC FORMATTED 表名;

内部表和外部表

内部表（CREATE TABLE table_name ...）

未被external关键字修饰的即是内部表,内部表又称管理表或者托管表。

删除内部表:直接删除元数据和存储的业务数据

外部表（CREATE EXTERNAL TABLE table_name ...）

被external关键字修饰的即是外部表,外部表又称非管理表或者非托管表。

删除外部表:仅仅是删除元数据，存储的业务数据并不会被删除

快速对比一下内部表和外部表

1. Hive表的类型?

内部表(MANAGED_TABLE)

分区表

分桶表

外部表(EXTERNAL_TABLE)

分区表

分桶表

2. 内部表区别?

删除内部表:直接删除元数据（metadata）及存储数据

删除外部表:仅仅是删除元数据（表的信息），不会删除数据本身

内部表

创建内部表:

create table [if not exists] 内部表名(

字段名字段类型 , ...

)...;

复制内部表:

复制表结构: CREATE TABLE 表名 like 存在的表名;

复制表结构和数据: CREATE TABLE 表名 as select语句;

删除内部表: drop table 内部表名;

查看表格式化信息:desc formatted 表名;

truncate清空内部表数据: truncate table 内部表名;

1. 创建内部表语法?

create table [if not exists] 内部表名(

字段名字段类型 , ...

)...;

2. 内部表类型?

MANAGED_TABLE

3. 删除内部表效果?

直接删除元数据（metadata）及存储数据

外部表

创建外部表:

create external table [if not exists] 内部表名(

字段名字段类型 , ...

)...;

复制外部表:

复制表结构: CREATE TABLE 表名 like 存在的表名;

删除外部表: drop table 外部表名;

查看表格式化信息: desc formatted 表名;

注意: 外部表不能使用truncate关键字清空数据

1. 创建外部表语法?

create external table [if not exists] 内部表名(

字段名字段类型 , ...

)...;

2. 外部表类型?

EXTERNAL_TABLE

3. 删除外部表效果?

仅仅是删除元数据（表的信息），不会删除数据本身

4. 清空外部表效果?

Cannot truncate non-managed table 表名 -- 外部表不能清空

查看/修改表

查看表操作

查看所有表:

show tables;

查看建表语句:

show create table 表名;

查看表信息:

desc 表名;

查看表结构信息:

desc extended 表名;

查看表格式化信息:

desc formatted 表名;

修改表操作

修改表名:

alter table 旧表名 rename to 新表名;

修改表路径:

alter table 表名 set location 'hdfs中存储路径';

注意: 建议使用默认路径

修改表属性:

alter table 表名 set tblproperties ('属性名'='属性值');

注意: 经常用于内外部表切换

内外部表转换

内部表转外部表

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

外部表转内部表

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

通过stu set tblproperties来修改属性

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

字段的添加:

alter table 表名 add columns (字段名字段类型);

字段的替换:

alter table 表名 replace columns (字段名字段类型 , ...);

字段名和字段类型同时修改:

alter table 表名 change 旧字段名新字段名新字段类型;

注意: 字符串类型不能直接改数值类型

其余属性可参见： https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-listTableProperties

1. 查看建表语句的语法?

show create table 表名;

2. 内部表转外部表

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

3. 外部表转内部表

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

默认分隔符

创建内部表

内部表的创建语法就是标准的：CREATE TABLE table_name......

创建一个基础的表

建库: create database if not exists myhive;

使用库:use myhive;

建表: create table if not exists stu(id int,name string);

插入数据:insert into stu values (1,"zhangsan");

查询数据:select * from stu;

查看表的数据存储

使用谷歌浏览器在HDFS上，查看表的数据存储文件000000_0中内容

数据分隔符

创建hive基础表的时候,默认的数据间分隔符是：\001(^A)是一种特殊字符，是ASCII值

在Notepad++文本编辑器中是显示为SOH的。

Hive的默认字段分隔符?

\001(^A)是一种特殊字符，是ASCII值

在Notepad++文本编辑器中是显示为SOH的。

快速映射表

案例需求:

已知HDFS的/source/products.txt数据文件中,要求创建对应的Hive表,最终把文件中数据快速映射到Hive表中

Hive映射表的流程步骤

数据加载和导出

数据加载 - LOAD语法

我们使用 LOAD 语法，从外部将数据加载到Hive内，语法如下：

建表：

数据：

load data local inpath '/home/hadoop/search_log.txt' into table myhive.test_load;

load data inpath '/tmp/search_log.txt' overwrite into table myhive.test_load;

注意，基于HDFS进行load加载数据，源数据文件会消失（本质是被移动到表所在的目录中）

数据加载 - INSERT SELECT 语法

除了load加载外部数据外，我们也可以通过SQL语句，从其它表中加载数据。

语法：

INSERT [OVERWRITE | INTO] TABLE 表名 [PARTITION (...) ] select语句;

将SELECT查询语句的结果插入到其它表中，被SELECT查询的表可以是内部表或外部表。

示例：

INSERT INTO TABLE tbl1 SELECT * FROM tbl2;

INSERT OVERWRITE TABLE tbl1 SELECT * FROM tbl2;

数据加载 - 多种语法的选择

对于数据加载，我们学习了：LOAD和INSERT SELECT的方式，那么如何选择它们使用呢？

数据文件在window或者mac :

直接使用hdfs页面操作即可

方式1(页面操作): 在hdfsweb页面,直接上传windows文件到指定表的location路径下

数据文件在linux本地

推荐 load data local上传

方式2(load加载): 在hive客户端上,输入load data local inpath '/linux本地文件路径' into table 表名;

put命令上传

方式3(put上传): 在linux命令行,输入hdfs dfs -put /Linux本地文件路径 /HDFS的指定表的location路径下

数据文件在HDFS中: 直接页面移动文件或者使用load data方式移动

方式4(load加载): 在hive客户端上,输入load data inpath '/HDFS的文件路径' into table 表名; 注意: 方式4本质就是把'/HDFS的文件路径'下的文件移动到 /HDFS的指定表的location路径下

数据已经在表中,且没有对应文件

INSERT into 表名 SELECT方式追加导入

INSERT overwrite table 表名 SELECT方式覆盖导入

方式5(insert插入): insert [overwrite | into] table 表名 select 语句;

注意: 方式5如果不加overwrite就是追加写入数据,如果加了overwrite就是覆盖原有数据

load命令注意事项

hive表数据导出

方式1(页面操作): 在hdfsweb页面,直接下载指定hdfs文件,到windows文件系统

方式2(get下载): 在linux命令行,输入hdfs dfs -get /HDFS的指定表的location路径下 /Linux本地文件路径

方式3(覆盖导出到linux): insert overwrite local directory '/Linux本地文件路径' select 语句; *** 方式4(覆盖导出到hdfs): insert overwrite directory '/HDFS文件路径' select 语句;

方式5(执行sql语句重定向到文件): hive -e sql语句 > 文件 #跳过登录直接运行sql命令

方式6(执行sql脚本重定向到文件): hive -f sql脚本 > 文件 #跳过登录直接运行sql脚本

1.导出都windows

2.导出到linux上

3.使用insert 导出到linux上

# 把表导出到linux目录中.分隔符默认 
insert overwrite local directory '/opt/export1' select * from products; 
# 把表导出到linux目录中.分隔符指定为逗号. 
insert overwrite local directory '/opt/export2' row format delimited fields terminated by ',' select * from products;

方式4(覆盖导出到hdfs): insert overwrite directory '/HDFS文件路径' select 语句;

# 不加local就是导出到hdfs上 
insert overwrite directory '/export1' select * from products;

方式5(执行sql语句重定向到文件): hive -e sql语句 > 文件

hive命令在linux执行: # hive -e 'sql' : 免登录执行sql语句. # 把查询结果重定向到文件中 [root@node1 export2]# hive -e 'select * from db1.products' > /opt/export3.txt

方式6(执行sql脚本重定向到文件): hive -f sql脚本 > 文件

# hive -f sql脚本文件 : 免登录执行sql脚本 
[root@node1 opt]# hive -f myhive.sql > products4.txt

分区表

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了

同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天，或者每小时进行切分成一个个的小的文件，这样去操作小的文件就会容易得多了。

如图，一个典型的按月份分区的表

每一个分区，是一个文件夹

同时Hive也支持多个字段作为分区，多分区带有层级关系，如图

分区表的使用

创建单级分区表

注意: 分区的列名不能和数据列名相同.分区列会当做虚拟列出现在数据列的后面.
create table order_one_part(id int,pname string,price int,uint int  )partitioned by (year string) row format delimited fields terminated by ' ';

---添加数据
load data inpath '/order202251.txt' into table order_one_part partition(year='2022');
load data inpath '/order202351.txt' into table order_one_part partition(year='2023');
load data inpath '/order202352.txt' into table order_one_part partition(year='2023');

-- 查询数据 -- 查询2022年的所有订单
-- 查询2022的数据2023目录中的数据没有参数进来.查询效率高.避免全表扫描.
select * from order_one_part where year='2022'; 
-- 全查
select * from order_one_part; -- 不会提升效率

创建多分区表

-- 注意: 分区的列名不能和数据列名相同.分区列会当做虚拟列出现在数据列的后面.
create table order_multi_part(id int,pname string,price int,uint int  )partitioned by (year string,month string,day string) row format delimited fields terminated by ' ';

---添加数据
load data inpath '/order202251.txt' into table order_multi_part partition(year='2022',month='5',day='1');
load data inpath '/order202351.txt' into table order_multi_part partition(year='2023',month='5',day='1');
load data inpath '/order202352.txt' into table order_multi_part partition(year='2023',month='5',day='2');
load data inpath '/order2023415.txt' into table order_multi_part partition(year='2023',month='4',day='15');

-- 查询2023年5月1号中的数据.查询效率高.避免全表扫描.
select * from order_multi_part where year='2023' and month='5' and day='1';

查看分区(注意：score为表名)

show partitions score;

添加一个分区

alter table score add partition(month='202005');

同时添加多个分区

alter table score add partition(month='202004') partition(month='202003');

注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

删除分区

alter table score drop partition(month='202006');

修改分区值

alter table score partition(month='202005') rename to partition(month='202305');

同步/修改分区

msck repair table 分区表名;

1. 分区表概念以及使用场景？

建表的使用partitioned by (分区字段名分区字段类型)关键字

分区其实就是HDFS上的不同文件夹

分区表可以极大的提高特定场景下Hive的操作性能

2. 分区表的完整语法

create table tablename(...) partitioned by (分区列列类型, ...) row format delimited fields terminated by '';

分区表的分区列，在partitioned by 中定义，不在普通列中定义

分桶表

分桶和分区一样，也是一种通过改变表的存储模式，从而完成对表优化的一种调优方式

但和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量的不同文件中进行存储。

如果没有分桶设置，插入（加载）数据只是简单的将数据放入到：

表的数据存储文件夹中（没有分区）

表指定分区的文件夹中（带有分区）

一旦有了分桶设置，比如分桶数量为3，那么，表内文件或分区内数据文件的数量就限定为3

当数据插入的时候，需要一分为3，进入三个桶文件内。

分桶表准备

开启分桶的自动优化开启分桶功能,

注意: Hive2.x版本移除了此项,默认开启

set hive.enforce.bucketing=true;

基础分桶表

创建基本分桶表,设置分桶数量为3

create table course (
cid int,
cname string,
sname string
) 
clustered by(cid) into 3 buckets 
row format delimited fields terminated by '\t';

分桶表关键字解析:

clustered by(cid) : 指定进行分桶的字段,这里是根据字段id进行分桶

into 3 buckets : 设置桶的数量,这里表示设置了3个桶

注意: 桶数量决定了hdfs对应表路径中桶文件的数量

基础分桶表结果如下:

分桶表的关键字

clustered by(字段名) : 指定按照哪个字段进行分桶

into x buckets : 设置分桶数量为x个

什么是桶内排序呢?

定义: 对桶内的数据根据指定的字段列表进行排序

分桶表: 根据age分为三个桶

因为有排序,排序可以减少后续数据的扫描量, 从而减省扫描时间提高效率

分桶表桶内排序语法

1- 在clustered by 后面添加分桶排序关键词: sorted by()

2- 在括号中设置排序字段列表和排序的规则

分桶表桶内排序案例实现

需求:

说明需求:

根据age字段分桶, 并按照age字段进行升序排序, 分桶数量为3

需求:

根据资料提供的teacher.txt文件数据, 构建HIVE分桶表, 要求根据地区进行分2个桶, 然后根据性别升序排序根据年龄进行倒序排列

数据样例

create table teacher(tid string,tname string,sex string,age int,address string
)clustered by (sex) into 2 buckets
row format delimited fields terminated by ',';
load data inpath '/teacher.txt' into table teacher;
select * from teacher;-- 查询数据 -- 没有提高效率
select * from teacher where sex='男'; -- todo 查询数据 -- 提高效率.先计算1在那个桶.然后只查询该桶的数据.

Hash取模

-- todo --- 分桶原理--哈希取模法-- hash(列名) % 桶个数 = 桶编号
-- 取哈希值，列名为'值'
select hash('值');
-- 取哈希值后取绝对值，为正数，列名为'值'
select abs(hash('值'));
-- 取桶编号，设桶个数为3
select abs(hash('值')) % 3;

结果如下：

注意：列名为整数数字，取的哈希值为原数字

Hash算法是一种数据加密算法，其原理我们不去详细讨论，我们只需要知道其主要特征：

同样的值被Hash加密后的结果是一致的

比如字符串“binzi”被Hash后的结果是93742710（仅作为示意），那么无论计算多少次，字符串“binzi”的结果都会是93742710。

比如字符串“bigdata”被Hash后的结果是-114692950（仅作为示意），那么无论计算多少次，字符串“bigdata”的结果都会是-114692950。

基于如上特征，在辅以有3个分桶文件的基础上，将Hash的结果基于3取模（除以3 取余数）

那么，可以得到如下结果：

无论什么数据，得到的取模结果均是：0、1、2 其中一个

同样的数据得到的结果一致，如’binzi’ hash取模结果是0，无论计算多少次，它的取模结果都是0

Hash取模确定数据归属哪个分桶文件

基于Hash取模，数据中的每一个分桶列的值，都被hash取模得到0、1、2其中一个数

基于结果，存入对应序号的桶文件中。

什么是Hash取模？

基于Hash算法，Hash算法是一种数据加密算法,对值进行计算，注意同一个值得到同样的结果

分桶表对分桶字段值hash结果进行取模（除以桶数量得到余数），取模结果一样的放到同一个桶文件

同样分桶列的值的数据，会在同一个桶中。

分桶表的性能提升

如果说分区表的性能提升是：在指定分区列的前提下，减少被操作的数据量，从而提升性能。

分桶表的性能提升就是：基于分桶列的特定操作，如：过滤、JOIN、分组、抽样等，均可带来性能提升。

基于分桶列，过滤单个值

基于分桶列，过滤值hadoop

基于分桶列，过滤值mapreduce

基于分桶列，进行双表JOIN

桶文件对桶文件

基于分桶列，group by 分组

自动归并为各个组了

分桶表能带来什么性能提升？

在基于分桶列做操作的前提下

单值过滤

JOIN

GROUP BY

抽样

复杂类型操作

复杂类型

Hive支持的数据类型很多，除了基本的：int、string、varchar、timestamp等

还有一些复杂的数据类型：

array

集合/数组类型

map

key-value映射类型

struct

结构类型

理解Hive SerDe机制

前面我们已经学习了hive的自定义分隔符格式: ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘指定分隔符’

那么这么长的格式具体是什么意思呢?那就跟着老师一起来了解下Hive SerDe机制吧.

在Hive的建表语句中，和SerDe相关的语法为：

其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。本次我们主要学习DELIMITED关键字相关知识点

如果使用delimited: 表示底层默认使用的Serde类:LazySimpleSerDe类来处理数据。

如果使用serde:表示指定其他的Serde类来处理数据,支持用户自定义SerDe类。

LazySimpleSerDe是Hive默认的序列化类

包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的类型特点灵活搭配使用。

COLLECTION ITEMS TERMINATED BY '分隔符' : 指定集合类型（array）/结构类型(struct)元素的分隔符

MAP KEYS TERMINATED BY '分隔符' 表示映射类型(map)键值对之间用的分隔

array类型

如下数据文件，有2个列，locations列包含多个城市:

说明:name与locations之间制表符分隔，locations中元素之间逗号分隔

可以使用array数组类型，存储locations的数据

建表语句：

create table if not exists table_array(name string,address array<string>)
row format delimited fields terminated by '\t'
collection items terminated by ',';

fields terminated by '\t' 表示列分隔符是\t

COLLECTION ITEMS TERMINATED BY ',' 表示集合（array）元素的分隔符是逗号

导入数据:

-- 加载linux上的数据到hive表中 --特点: 加载完毕后linux上的数据依然存在.
load data local inpath '/mnt/data_for_array_type.txt' into table table_array;

常用array类型查询：

select * from table_array;
-- 需求: 查询zhangsan的地址有几个?
select address from table_array where name='zhangsan';
select name, size(address) as addr_size from table_array where name='zhangsan';-- 需求: 查询zhangsan的第二个地址?
select name,address[1] as addr from table_array where name='zhangsan';-- 需求: 查询zhangsan是否在tianjin住过?
select * from table_array where array_contains(address,'tianjin');
select name,array_contains(address,'tianjin') `入住记录` from table_array where name = 'zhangsan';

结果如下：

1. array类型，主要存储：数组格式

保存一堆同类型的元素，如：1, 2, 3, 4, 5

2. 定义格式：

array<类型>

数组元素之间的分隔符：collection items terminated by '分隔符'

3. 在查询中使用

数组[数字序号]，可以取出指定需要元素（从0开始）

size(数组)，可以统计数组元素个数

array_contains(数组, 数据)，可以查看指定数据是否在数组中存在

struct类型

struct类型是一个复合类型，可以在一个列中存入多个子列，每个子列允许设置类型和名称有如下数据文件，说明：字段之间#分割，struct之间冒号分割

建表语句

create table table_struct(id int,info struct<name:string,age:int ,birth:string>)
row format delimited fields terminated by '#'
collection items terminated by ':';

fields terminated by '#' 表示列分隔符是#

COLLECTION ITEMS TERMINATED BY '：' 表示集合（array）元素的分隔符是逗号

导入数据

-- 加载数据
load data local inpath '/mnt/data_for_struct_type.txt' into table table_struct;

常用查询

-- 查询所有列
select * from table_struct;
-- 需求: 获取所有的姓名
select info.name from table_struct;
-- 需求: 获取所有的年龄
select info.age from table_struct;

结果如下：

1. struct类型，主要存储：复合格式

可以包含多个二级列，二级列支持列名和类型，如 “a”: 1, “b”: “foo”, “c”: “2000-01-01”

2. 定义格式：

struct<name:string, age:int>

struct的分隔符只需要：COLLECTION ITEMS TERMINATED BY '分隔符' 只需要分隔数据即可（数据中不记录key，key是建表定义的固定的）

3. 在查询中使用

struct字段.子字段名即可取得对应的值

map类型

map类型其实就是简单的指代：Key-Value型数据格式。有如下数据文件，其中members字段是key-value型数据字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k-v分隔符：":"

建表语句：

create table if not exists table_map(id int,name string, relation map<string,string>,age int)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';

MAP KEYS TERMINATED BY ':' 表示key-value之间用:分隔

导入数据

-- 加载数据
load data local inpath '/mnt/data_for_map_type.txt' into table table_map;

常用查询

select * from table_map;
-- 需求: 查看所有人的father,mother信息
select name,relation['father'],relation['mother'] from table_map;
-- 需求: 查看所有人的家庭相关角色
select name,map_keys(relation) from table_map;
-- 需求: 查看所有人的家庭相关姓名
select name,map_values(relation) from table_map;
-- 需求: 查看所有人的家庭相关人员个数
select name,size(map_keys(relation)) from table_map;
-- 需求: 查看马大云是否包含brother角色
select name,array_contains(map_keys(relation),'brother') `是否有兄弟` from table_map where name='马大云';

结果如下：

1. map类型，主要存储：K-V键值对类型数据

保存一堆同类型的键值对，如：“a”:1, “b”: 2, “c”: 3

2. 定义格式：

map<key类型, value类型>

不同键值对之间：COLLECTION ITEMS TERMINATED BY '分隔符' 分隔

一个键值对内，使用： MAP KEYS TERMINATED BY '分隔符' 分隔K-V

如：father:xiaoming#mother:xiaohuang#brother:xiaoxu 不同KV之间使用#分隔，同一个KV内用:分隔K和V

3. 在查询中使用

map[key]来获取指定key的值

map_keys(map)取到全部的key作为array返回，map_values(map)取到全部values

size(map)可以统计K-V对的个数

array_contains(map_values(map), 数据) 可以统计map是否包含指定数据

复杂类型array、map、struct总结