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:对于对表 设置相关参数信息
注意:
上面的是建表语法的关键字 用于指定某些功能。
[] 中括号的语法表示可以选
| 表示使用的时候左右语法二选一
建表语句中的语法顺序要和上述语法规则保持一致
2、hive的数据类型介绍
在hive中 主要是有两种数据类型:第一种是 原生的数据类型 第二种是复杂的数据类型
- 常用的原生数据类型
数值类型:
int:整型 就是整数
double:小数
decimal(总位数据,小数位数量):小数
eg: decimal(10,2):整个小数的位数总共10位,其中小数位位2位 整数位是8位。
时间类型:
timestamp:时间戳
date: 日期
字符串类型:
string:相当于mysql中的text(表示一个大大的文本)
布尔类型:
boolean:只有true 和false
- 复杂的数据类型
array类型:表示数组
格式:array<原生类型>
map类型:表示键值对
格式:map<key原生类型,value原生类型> 就是Python中的键值对那种形式
关于Hive的数据类型,需要注意:
- 英文字母的大小写不敏感:
- 除了SQL数据类型外,还支持Java数据类型,比如string;
- int和string是使用最多的,大多数函数都支持;
- 复杂数据类型的使用通常需要和分隔符指定语法配合使用
- 如果定义的数据类型和文件不一致,hive会尝试隐式转换,但是不保证成功。
如何实现类型转换呢?
- hive所支持的隐式转换 : 小的可以自动转换大的, 但是大的不能自动转换为小的
- 显示的类型转换操作: cast
- 例如,CAST('100’as INT)会将100字符串转换为100整数值。
hive是如何将hdfs的结构化文件映射称为一个表的呢?
默认的存储路径:
/user/hive/warehouse
会在此目录下构建一个同名的库或者表的目录
指定存储路径:
在建表的时候,通过location '指定加载数据路径'
用于设置分隔符的类
- 默认的类: LazySimpleSerDe : 此类 是用于设置各种分隔符号的, 同时还需要对数据进行序列化和反序列化操作
上图非常的重要,上面标识了所有hive默认支持设置分割符号:
组合方式:row format delimited
设置字段与字段之间分隔符号:fields terminated by '分隔符号'
集合中元素与元素之间分隔符号:collection items terminated by '分隔符号'
map中 kv键值对之间分隔符号:map keys terminated by '分隔符号'
行数据之间符号:lines terminated by '分隔符号'
默认的字段与字段之间的分隔符号为:\001
默认行分隔符号为:回车符号
- 读取数据的类: 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
#假设我们在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;
- 第二种 采用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/';
直接将数据加载出来, 好处: '不会改变原有数据的路径'
- 案例二:复杂数据类型
文件名也叫'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
# 第一步: 将hero_1.txt文件上传到HDFS中
hdfs dfs -mkdir -p /hivedata/hero_1/
hdfs dfs -put hero_1.txt /hivedata/hero_1/
-- 第二步:在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 ':';
# 第三步: 将 /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
-- 第四步: 查看数据
select * from hero_1;
select map_keys(skin_price) from hero_1;
select map_values(skin_price) from hero_1;
3、DDL语句建表高阶
3.1 内部表和外部表
- 什么是内部表: 在hive中, 默认创建的表其实就是内部表, 当然内部表也被称为管理表, hive对内部表的元数据以及数据是有绝对的控制权, 当删除表的时候, 对应元数据和数据都会被删除
可以使用DESCRIBE FORMATTED 数据库名.表名来获取表的描述信息,从中可以看出表的类型
查看上面创建的hero_1表
describe formatted hero_1.hero_1;
接下来, 将这个表进行删除操作:
drop table hero_1.hero_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';
删除这个外部表:
drop table sheshou_2;
3.2内部表和外部表的区别:
在实际生产中,我们什么时候使用内部表 什么时候使用外部表呢??
' 内部表和外部表都用 具体使用那种表, 取决于是否表数据有绝对的控制权 '
另一种说明:
如果这个数据, 需要被多方人共享使用, 那么构建的一定是一张外部表, 如果这个表仅被自己使用, 构建为内部表
如果其他的情况, 无所谓
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;
查询的需求: 查询role_main主要定位是射手并且hp_max最大生命大于6000的有几个
select
count(1)
from hero_1 where role_main = 'archer' and hp_max >6000;
思考:
执行以上SQL的时候, MR会读取这个表下所有的6个文件中的数据, 从所有的数据中筛选出符合条件的数据, 然后进行统计计数操作
但是: 实际上这个数据, 只需要查询其中表中其中的射手文件的数据即可
如何进行优化呢? 需要使用hive的分区表实现
分区表的基本操作:
格式:
create [external] table table_name(
column1 data_type,
column2 data_type,
)partitioned by(partition1 data_type, partition2 data_type,….);
说明: ' 分区表不是独立的表结构,需要结合内部表和外部表,可以有内部分区表和外部分区表 '
根据上面描述进行建表
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';
加载数据到分区表中 : ' 静态加载数据方式 '
-- 加载射手文件数据到射手分区下
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');
查看 hdfs
查询数据:
select
count(1)
from hero_parti_1 where role = 'sheshou' and hp_max >6000;
总结一下:
分区表,' 本质上就是用来划分文件夹的,根据分区字段的值,将各个文件划分在不同的区域下,当查询数据的时候,根据分区字段,查询对应区域下的数据即可,实现不需要读取全表数据,只需要读取某几个区域下的数据即可,从而起到减少数据扫描情况,以此来提升效率。 '
' **分区表可以认为是hive的一种优化手段** '
注意事项:
' 一、分区表不是建表的必要语法规则,是一种优化手段表,可选 '
' 二、分区字段不能是表中已有的字段,不能重复 '
' 三、分区字段是虚拟字段,其数据并不能存储在底层的文件中 '
' 四、分区字段的值确定来自于用户价值数据手动指定(静态分区)或者根据查询结果位置自动推断(动态分区) '
' 五、Hive支持多重分区,也就是说在分区的基础上继续分区,划分更加细粒度 '
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的语句的最后的字段一定是分区字段的值
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);
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;
现有美国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
根据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 ',';
导入数据
方式一: 通过 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.当查询的根据分桶字段来查询,此时hive会根据分桶字段对的值确定这个数据在哪一个桶中,直接从这个桶中获取数据,从而减少数据扫描量,依次提升效率 '
' 2.数据采样 '
' 3.提升多表的查询效率:减少大量的非必要join 情况 '
思考: hive进行划分多个桶的时候, 是如何划分的呢?
采用 hash取模方案
洲的值.hashcode() 得到一个int类型的值: ' 同一个数据得到int的值一定是相同的 '
% 5 余数的范围: ' 0~4 余数得多少, 将这个数据划分到哪一个桶中 '