Hive DDL建表语句与内部表、外部表、分区表、分桶表_分区表建表语句

1、SQl 四大定义语言

DDL： 数据库定义语言 注意: 不涉及对表数据的操作
DML： 数据库操作语言
DQL ：数据库查询语言
DCL：数据库控制语言

Hive的建库建表语句

tempproary：临时，通过此关键词可以创建一张临时表（仅在当前会话有效）
eg：
	create tempproary table if not exists 数据库名.表名；
external：外部的 通过此关键词可以创建一个外部表
eg：
	create external table if not exists 数据库名.表名；
commect：说明信息

partitioned by：分区，通过此关键词可以创建分区表
clustered by ： 分桶 通过此关键词可以创建一张分通表
	sorted by：在进行分桶的过程中 可以对每一个桶进行排序 保证每一个桶存入的数据都是有序的
	into Num_buckets BUCKETS:需要分多少个桶
row format delimited|sered ：用于设置表的相关的分隔符号
stored as：用于设置表的数据存储格式（textfile（普通文本），ORC····）
location：指定表应该从HDFS的什么位置上加载数据
tblproperties：对于对表 设置相关参数信息

注意：
	上面的是建表语法的关键字 用于指定某些功能。
	[] 中括号的语法表示可以选
	| 表示使用的时候左右语法二选一
	建表语句中的语法顺序要和上述语法规则保持一致
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2、hive的数据类型介绍

在hive中 主要是有两种数据类型：第一种是 原生的数据类型 第二种是复杂的数据类型

• 常用的原生数据类型
  

数值类型：
	int：整型 就是整数
	double：小数
	decimal（总位数据，小数位数量）：小数
		eg： decimal(10，2)：整个小数的位数总共10位，其中小数位位2位 整数位是8位。
时间类型：
	timestamp：时间戳
	date： 日期
字符串类型：
	string：相当于mysql中的text（表示一个大大的文本）
布尔类型：
	boolean：只有true 和false
1
2
3
4
5
6
7
8
9
10
11
12

• 复杂的数据类型
  

array类型：表示数组
	格式：array<原生类型>
map类型：表示键值对
	格式：map<key原生类型，value原生类型> 就是Python中的键值对那种形式
1
2
3
4

关于Hive的数据类型，需要注意：

• 英文字母的大小写不敏感：
• 除了SQL数据类型外，还支持Java数据类型，比如string；
• int和string是使用最多的，大多数函数都支持；
• 复杂数据类型的使用通常需要和分隔符指定语法配合使用
• 如果定义的数据类型和文件不一致，hive会尝试隐式转换，但是不保证成功。

如何实现类型转换呢?

• hive所支持的隐式转换 : 小的可以自动转换大的, 但是大的不能自动转换为小的
  
• 显示的类型转换操作: cast
  • 例如，CAST（'100’as INT）会将100字符串转换为100整数值。

hive是如何将hdfs的结构化文件映射称为一个表的呢?

默认的存储路径:
	/user/hive/warehouse 
	会在此目录下构建一个同名的库或者表的目录

指定存储路径:
	在建表的时候,通过location '指定加载数据路径'
1
2
3
4
5
6

用于设置分隔符的类

• 默认的类： LazySimpleSerDe ： 此类 是用于设置各种分隔符号的, 同时还需要对数据进行序列化和反序列化操作
  

上图非常的重要，上面标识了所有hive默认支持设置分割符号：
组合方式：row format delimited
	设置字段与字段之间分隔符号：fields terminated by '分隔符号'
	集合中元素与元素之间分隔符号：collection items terminated by '分隔符号'
	map中 kv键值对之间分隔符号：map keys terminated by '分隔符号'
	行数据之间符号：lines terminated by '分隔符号'

默认的字段与字段之间的分隔符号为：\001
默认行分隔符号为：回车符号
1
2
3
4
5
6
7
8
9

• 读取数据的类: TextInputFormat
  此类是默认用于读取数据的类, 默认读取方案就是一行一行的读取,读取出来后, 转换为 k1 和 v1
  其中 v1中放置一行数据 而k1仅仅是放置这一行的标记信息
• 写入数据的类: TextoutputFormat
  此类是默认用于写入数据的类, 默认方式就是一行一行写入操作

在hive中, 支持一个高阶操作: 自定义序列化和反序列类

2、建表的综合案例

- 案例一：原生数据案例

给大家展示一个表

字段说明:

ID	英雄	最大生命	最大法力	最高物攻	最大物防	 攻击范围	   主要定位	    次要定位 
id	name	hp_max	    mp_max	   attack_max	defense_max	 attack_range	role_main	role_assist

数据内容:
1	后羿	5986	1784	396	336	remotely	archer
2	马可波罗	5584	200	362	344	remotely	archer
3	鲁班七号	5989	1756	400	323	remotely	archer
4	李元芳	5725	1770	396	340	remotely	archer
5	孙尚香	6014	1756	411	346	remotely	archer
6	黄忠	5898	1784	403	319	remotely	archer
7	狄仁杰	5710	1770	376	338	remotely	archer
8	虞姬	5669	1770	407	329	remotely	archer
9	成吉思汗	5799	1742	394	329	remotely	archer
10	百里守约	5611	1784	410	329	remotely	archer	assassin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#假设我们在Linux的/root/hivedata 目录下使用vim 写了上面这个表 名字叫hero.txt

#第一步： 将其上传到HDFS的/hivedata/hero/下
hdfs dfs -mkdir -p /hivedata/hero/
hdfs dfs -put hero.txt /hivedata/hero/

#第二步: 在hive中根据字段说明以及字段分隔符号进行建表操作
create  table hero(
  id int, 
  name string,
  hp_max int,
  mp_max int,
  attack_max int,
  defense_max int,
  attack_range string,
  role_main string,
  role_assist string
) row format delimited fields terminated by '\t';

第三步: 将 /hivedata/hero/hero.txt 拷贝到 /user/hive/warehouse/hero
hdfs dfs -cp /hivedata/hero/hero.txt /user/hive/warehouse/hero

第四步: select * from hero;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

• 第二种 采用loction 直接指定加载数据的位置就在/hivedata/hero/下

create  table sheshou_2(
  id int, 
  name string,
  hp_max int,
  mp_max int,
  attack_max int,
  defense_max int,
  attack_range string,
  role_main string,
  role_assist string
) row format delimited fields terminated by '\t' location '/hivedata/archer/';

直接将数据加载出来, 好处: '不会改变原有数据的路径'
1
2
3
4
5
6
7
8
9
10
11
12
13

- 案例二：复杂数据类型

文件名也叫'hero_1.txt'
字段：id、name（英雄名称）、win_rate（胜率）、skin_price（皮肤及价格）
文件内容:
1,孙悟空,53,西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688
2,鲁班七号,54,木偶奇遇记:288-福禄兄弟:288-黑桃队长:60-电玩小子:2288-星空梦想:0
3,后裔,53,精灵王:288-阿尔法小队:588-辉光之辰:888-黄金射手座:1688-如梦令:1314
4,铠,52,龙域领主:288-曙光守护者:1776
5,韩信,52,飞衡:1788-逐梦之影:888-白龙吟:1188-教廷特使:0-街头霸王:888
1
2
3
4
5
6
7
8

# 第一步： 将hero_1.txt文件上传到HDFS中
hdfs dfs -mkdir -p /hivedata/hero_1/
hdfs dfs -put hero_1.txt /hivedata/hero_1/
1
2
3

-- 第二步：在hive中创建表 做映射操作
create database hero_1;
use hero_1;
create table hero_1.hero_1(
id int,
    name string,
    win_rate int,
    skin_price map<String,int>
)row format delimited 
    fields terminated by ','
    collection items terminated by '-'
    map keys terminated  by ':';
1
2
3
4
5
6
7
8
9
10
11
12

# 第三步： 将 /hivedata/hero_1/hero_1.txt 拷贝到 /user/hive/warehouse/hero_1.db/hero_1
hdfs dfs -cp /hivedata/hot/hero_1.txt /user/hive/warehouse/hero_1.db/hero_1
1
2

-- 第四步: 查看数据
select * from hero_1;
select map_keys(skin_price) from hero_1;
select map_values(skin_price) from hero_1;
1
2
3
4

3、DDL语句建表高阶

3.1 内部表和外部表

• 什么是内部表： 在hive中, 默认创建的表其实就是内部表, 当然内部表也被称为管理表, hive对内部表的元数据以及数据是有绝对的控制权, 当删除表的时候, 对应元数据和数据都会被删除

可以使用DESCRIBE FORMATTED 数据库名.表名来获取表的描述信息，从中可以看出表的类型

查看上面创建的hero_1表
describe formatted hero_1.hero_1;
1
2

接下来, 将这个表进行删除操作:

drop table hero_1.hero_1;
1

发现 元数据和数据都被删除掉了

• 什么是外部表 在hive中, 如果想构建一张外部表, 必须使用external 关键词来修饰, 构建了外部表, hive认为此表对数据是没有管理权, 仅仅对表的元数据有管理权, 当删除表的时候, 仅会删除表元数据, 而不会删除hdfs上数据

创建一张外部表

create external table sheshou_2(
 id int, 
  name string,
  hp_max int,
  mp_max int,
  attack_max int,
  defense_max int,
  attack_range string,
  role_main string,
  role_assist string
)
row format delimited fields terminated by '\t' 
location '/hivedata/hero';
1
2
3
4
5
6
7
8
9
10
11
12
13

删除这个外部表:

drop table sheshou_2;
1

3.2内部表和外部表的区别:

在实际生产中，我们什么时候使用内部表 什么时候使用外部表呢？？

' 内部表和外部表都用 具体使用那种表, 取决于是否表数据有绝对的控制权 '
另一种说明:
	如果这个数据, 需要被多方人共享使用, 那么构建的一定是一张外部表, 如果这个表仅被自己使用, 构建为内部表
	如果其他的情况, 无所谓 
1
2
3
4

3.3 分区表

分区表 就需要有多张表了

3) 加载数据  
hive的数据加载命令
	load  data [local] inpath '路径' into table 表名;
	说明: 
		local 表示从hiveserver2所在节点的Linux服务器本地路径是上加载数据
		如果不加local  表示从hdfs的路径上加载数据
操作:
	load data inpath '/hivedata/hero' into table hero_1;
	
4) 查看数据
	select * from hero_1 limit 20;
1
2
3
4
5
6
7
8
9
10
11

查询的需求:  查询role_main主要定位是射手并且hp_max最大生命大于6000的有几个
select  
    count(1)
from hero_1 where role_main = 'archer' and hp_max >6000;

思考：
 	执行以上SQL的时候, MR会读取这个表下所有的6个文件中的数据, 从所有的数据中筛选出符合条件的数据, 然后进行统计计数操作
 
 但是: 实际上这个数据, 只需要查询其中表中其中的射手文件的数据即可
1
2
3
4
5
6
7
8
9

如何进行优化呢? 需要使用hive的分区表实现

分区表的基本操作：
格式：

create [external] table table_name(
	column1 data_type,
	column2 data_type,
)partitioned by(partition1 data_type, partition2 data_type,….);

说明： ' 分区表不是独立的表结构，需要结合内部表和外部表，可以有内部分区表和外部分区表 '
1
2
3
4
5
6

根据上面描述进行建表
create  table hero_parti_1(
  id int, 
  name string,
  hp_max int,
  mp_max int,
  attack_max int,
  defense_max int,
  attack_range string,
  role_main string,
  role_assist string
) 
partitioned by (role string)
row format delimited fields terminated by '\t';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

加载数据到分区表中 : ' 静态加载数据方式 '
-- 加载射手文件数据到射手分区下
load data inpath '/hivedata/hero/archer.txt' into table hero_parti_1 partition(role='sheshou');
以此类推, 其他的分区的加载
load data inpath '/hivedata/hero/assassin.txt' into table hero_parti_1 partition(role='cike');
load data inpath '/hivedata/hero/mage.txt' into table hero_parti_1 partition(role='fashi');
load data inpath '/hivedata/hero/support.txt' into table hero_parti_1 partition(role='fuzhu');
load data inpath '/hivedata/hero/tank.txt' into table hero_parti_1 partition(role='tanke');
load data inpath '/hivedata/hero/warrior.txt' into table hero_parti_1 partition(role='zhanshi');

1
2
3
4
5
6
7
8
9
10

查看 hdfs

查询数据:
select  
    count(1)
from hero_parti_1 where role = 'sheshou' and hp_max >6000;
1
2
3
4

总结一下：

	分区表，' 本质上就是用来划分文件夹的，根据分区字段的值，将各个文件划分在不同的区域下，当查询数据的时候，根据分区字段，查询对应区域下的数据即可，实现不需要读取全表数据，只需要读取某几个区域下的数据即可，从而起到减少数据扫描情况，以此来提升效率。 '
	' **分区表可以认为是hive的一种优化手段** '
1
2

注意事项：

' 一、分区表不是建表的必要语法规则，是一种优化手段表，可选 '
' 二、分区字段不能是表中已有的字段，不能重复 '
' 三、分区字段是虚拟字段，其数据并不能存储在底层的文件中 '
' 四、分区字段的值确定来自于用户价值数据手动指定(静态分区)或者根据查询结果位置自动推断（动态分区） '
' 五、Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度 ' 
1
2
3
4
5

3.4动态分区实现

动态分区: 帮助一次性构建多个分区操作

第一步:  创建  hero_parti_2
create  table hero_parti_2(
  id int, 
  name string,
  hp_max int,
  mp_max int,
  attack_max int,
  defense_max int,
  attack_range string,
  role_main string,
  role_assist string
) 
partitioned by (role string)
row format delimited fields terminated by '\t';

第二步: 将 hero_parti_1对应分区下数据导入到 hero_parti_2

静态分区: 
	load data inpath '/user/hive/warehouse/hero_parti_1/role=sheshou' into table hero_parti_2 partition(role='sheshou');
	这样可以实现, 但是有点麻烦, 需要知道原有表的数据在那个位置上存储的, 但是有的时候我们并不知道
    insert into hero_parti_2 partition(role='sheshou')
    select  
       id,
       name,
       hp_max,
       mp_max,
       attack_max,
       defense_max,
       attack_range,
       role_main,
       role_assist
    from hero_parti_1 where role='sheshou';
	其他的分区也是如此操作

动态分区: 
set hive.exec.dynamic.partition.mode=nonstrict;  -- 打开非严格模式
set hive.exec.dynamic.partition=true;  --开启动态分区支持 (默认为true)
insert into hero_parti_2 partition(role)
select  
    id,
    name,
    hp_max,
    mp_max,
    attack_max,
    defense_max,
    attack_range,
    role_main,
    role_assist,
    role
from hero_parti_1 ;

注意事项: 
	1) 必须开启hive的非严格模式: set hive.exec.dynamic.partition.mode=nonstrict;
	2) 必须开启动态分区的支持:  set hive.exec.dynamic.partition=true;(默认为true)
	3) select的语句的最后的字段一定是分区字段的值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

3.5多重分区

多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。

--单分区表，按省份分区
create table t_user_province (id int, name string,age int) partitioned by (province string);

--双分区表，按省份和市分区
create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string);

--三分区表，按省份、市、县分区
create table t_user_province_city_county (
    id int, 
    name string,
    age int
) partitioned by (province string, city string,county string);
1
2
3
4
5
6
7
8
9
10
11
12

3.6分桶表

分桶表：用于划分文件的，将一个文件根据分桶表的字段以及分桶的数量，划分为多个文件的操作，类似于MR中分区操作，这是一个更细粒度划分操作，甚至可以在分区上再次进行分桶操作

' 构建分桶表的语句 '
create [external] table [db_name].table_name
[(col_name data_type,······)]
clustered by (col_name) [sorted by (col_name)] into N BUCKETS;
1
2
3
4

现有美国2021-1-28号，各个县county的新冠疫情累计案例信息，包括确诊病例和死亡病例
字段含义如下：count_date（统计日期）,county（县）,state（州）,fips（县编码code）,cases（累计确诊病例）,deaths（累计死亡病例）。
数据内容:
2021-01-28,Autauga,Alabama,01001,5554,69
2021-01-28,Baldwin,Alabama,01003,17779,225
2021-01-28,Barbour,Alabama,01005,1920,40
2021-01-28,Bibb,Alabama,01007,2271,51
2021-01-28,Blount,Alabama,01009,5612,98
1
2
3
4
5
6
7
8

根据state州把数据分为5桶，建表语句如下

create  table us_yiqing(
    count_date string,
    county string,
    state string,
    fips string,
    cases int,
    deaths int
) 
clustered by (state) sorted by(cases) into 5 buckets
row format delimited fields terminated by ',';
1
2
3
4
5
6
7
8
9
10

导入数据

方式一: 通过 load导入数据
	说明: 先将数据上传到hdfs的/hivedata/us/
	load data inpath '/hivedata/us/us-covid19-counties.dat' into table us_yiqing;
	发现: 可以正常加载数据到hive表中, 并且形成多个分桶的数据
		但是: 此种操作, 仅在hive3.x支持, 如果使用hive3.x以下版本, 不支持的

方式二: 先创建一张临时表(必然不是分桶表), 将数据灌入到临时表, 最后从临时表使用insert+select方式导入
创建临时表:
create  table us_yiqing_temp(
    count_date string,
    county string,
    state string,
    fips string,
    cases int,
    deaths int
) 
row format delimited fields terminated by ',';

通过load方式加载数据
load data inpath '/hivedata/us/us-covid19-counties.dat' into table us_yiqing_temp; 

通过insert + select 方式导入
insert into us_yiqing select * from us_yiqing_temp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

• 分桶表有什么作用

' 1.当查询的根据分桶字段来查询，此时hive会根据分桶字段对的值确定这个数据在哪一个桶中，直接从这个桶中获取数据，从而减少数据扫描量，依次提升效率 '
' 2.数据采样 '
' 3.提升多表的查询效率：减少大量的非必要join 情况 '
1
2
3

思考: hive进行划分多个桶的时候, 是如何划分的呢?

采用 hash取模方案

洲的值.hashcode() 得到一个int类型的值: ' 同一个数据得到int的值一定是相同的 '
	%  5   余数的范围: ' 0~4  余数得多少, 将这个数据划分到哪一个桶中 '
1
2
3
4