HQL DDL语句介绍:
它指的是 数据定义语言, 主要是用来 操作数据库, 数据表, 字段等的. 这里我们学习DDL语句, 主要就是学习 Hive如何建表. 它(Hive如何建表)非常重要, 是你学好Hive的第1课.
建表语法:
create [external] table [if not exists] 表名(
列名 数据类型 [ comment '描述信息'],
列名 数据类型 [ comment '描述信息'],
列名 数据类型 [ comment '描述信息'],
......
) comment '表的描述信息'
分区
分桶(桶内排序 + 分桶个数)
行格式切割符
存储格式(行存储, 列存储)
存储位置
表的属性信息;
常用的数据类型:
原生类型:
int 整数
double 小数
string 字符串
timestamp 时间戳
date 日期(年月日)
复杂类型:
array 数组类型
map 字段(映射)类型
struct 结构体
union 联合体
Hive的本质:
把HDFS文件映射成一张Hive表, 然后通过写 类SQL的语句来操作它(HDFS文件), 底层会被解析成MR程序, 交由Yarn来调度执行.
案例1: 默认切割符
-- ---------------------------- 案例1: 默认切割符 ----------------------------
-- 结论: Hive表默认切割符是'\001'符号, 在windows中显示为 SOH, 在HDFS中显示为 小口, 在Linux中显示为 ^A
-- 0. 建库.
create database day06;
use day06;
show tables;
-- 1. 建表, 表字段, 数据类型等要和 HDFS源文件保持一致.
drop table team_ace_player;
create table team_ace_player ( -- 表名无需和HDFS源文件保持一致, 见名知意即可.
id int comment '编号',
team_name string comment '队名',
player_name string comment '玩家名字'
) comment '战队ACE玩家信息';
-- 2. 把源文件上传到到Hive表所在的 HDFS路径下.
-- 3. 查询表数据.
select * from team_ace_player;
-- 4. 如何查看表结构信息.
desc team_ace_player; -- 发现注释信息乱码, 原因是: 我们的Hive表是UTF-8码表, Hive的元数据存在MySQL中, 用的GBK码表, 码表不一致导致的.
show create table team_ace_player;
desc formatted team_ace_player; -- 查看表结构详细信息(掌握), 其中: Table Type 表示表类型, MANAGED_TABLE(受管理表, 即: 内部表), EXTERNAL_TABLE(外部表)
案例2: 解决Hive注释中文乱码问题
-- ---------------------------- 案例2: 解决Hive注释中文乱码问题 ----------------------------
-- 产生原因: 我们的Hive表是UTF-8码表, Hive的元数据存在MySQL中, 用的GBK码表, 码表不一致导致的.
-- 解决方案: 保持码表一致即可, 我们去MySQL中修改Hive元数据信息的码表, 改为: UTF-8
-- 具体步骤:
-- Step1: 先通过DataGrip连接node1机器的MySQL, 账号: root, 密码: 123456 当然, 也可以直接在CRT中连接MySQL数据库.
-- Step2: 在MySQL中执行如下的内容即可, 详见MySQL的文件. 细节: 还要去hive-site.xml文件中修改下码表.
-- Step3: 重启metastore服务 和 hiveserver2服务.
-- Step4: 在Hive中把刚才的表删掉, 然后重建, 再次查询, 注释(描述信息)不乱码了.
案例3: 指定切割符
-- ---------------------------- 案例3: 指定切割符 ----------------------------
-- 1. 建表, 指定分隔符为 '\t'
create table t_archer (
id string comment 'ID',
name string comment '英雄',
hp_max string comment '最大生命',
mp_max string comment '最大法力',
attack_max string comment '最高物攻',
defense_max string comment '最大物防',
attack_range string comment '攻击范围',
role_main string comment '主要定位',
role_assist string comment '次要定位'
) comment '射手表'
row format delimited fields terminated by '\t'; -- 行格式切割 字段分隔符为 '\t'
-- 2. 上传源文件到该Hive表的HDFS路径下.
-- 3. 查询表数据.
select * from t_archer;
案例4: 其它的建表方式
-- ---------------------------- 案例4: 其它的建表方式 ----------------------------
-- 1. 查询所有数据表.
show tables;
-- 2. 原表
select * from t_archer; -- 10条数据.
-- 3. 备份 t_archer表, 要求: 字段名, 数据类型, 数据都都保持一致.
create table t1 as select * from t_archer; -- 底层会转成MR程序, 备份表和原表, 字段名, 数据类型, 数据都会保持一致. 用的是默认分隔符: '\001'
create table t2 row format delimited fields terminated by ','
as select * from t_archer; -- 底层会转成MR程序, 备份表和原表, 字段名, 数据类型, 数据都会保持一致. 用的是指定分隔符: ','
-- 4. 备份 t_archer表, 要求: 字段名, 数据类型, 保持一致, 没有数据, 即: 空表.
create table t3 like t_archer; -- 底层不会转MR.
-- 5. 备份完之后, 查询表数据.
select * from t1;
select * from t2;
select * from t3;案例5: 修改表 相关操作
-- ---------------------------- 案例5: 修改表 相关操作 ----------------------------
show tables;
-- 1. 修改表名, t1 => t_archer_backup1
alter table t1 rename to t_archer_backup1;
-- 2. 修改表 t_archer_backup1 的存储路径.
alter table t_archer_backup1 set location '/wordcount/aa';
select * from t_archer_backup1;
desc formatted t_archer_backup1;
-- 3. 修改表属性, 例如: 内部表 和 外部表的相互转换.
-- 3.1 把内部表修改为外部表.
alter table t_archer_backup1 set tblproperties('EXTERNAL'='TRUE'); -- 固定格式, 必须大写.
-- 3.2 把外部表修改为内部表.
alter table t_archer_backup1 set tblproperties('EXTERNAL'='FALSE'); -- 固定格式, 必须大写.
-- 3.3 查看表属性信息.
desc formatted t_archer_backup1; -- Table Type表示表的类型, managed_table(内部表), external_table(外部表)
-- 3.4 添加列
desc t_archer_backup1;
alter table t_archer_backup1 add columns (address string, hobby string); -- 新增两列.
-- 3.5 修改列名
alter table t_archer_backup1 change address addr string;
-- 3.6 删除表.
show tables; -- 查看所有数据表.
drop table t3; -- 删除表.
-- 3.7 (清空)删除表数据
select * from t_archer_backup1;
-- delete from t_archer_backup1; -- 报错, Hive不支持这种删除方式.
truncate table t_archer_backup1; -- 删除表数据.
drop table t3; -- 删除表
-- 3.8 尝试用 truncate 方式, 清空 外部表.
desc formatted team_ace_player;
alter table team_ace_player set tblproperties('EXTERNAL'='TRUE'); -- 将表改为 外部表.
select * from team_ace_player; -- 有数据.
-- 尝试清空外部表, 报错, truncate 只能情况 内部表.
truncate table team_ace_player;
案例6: 内部表 和 外部表的区别
-- ---------------------------- 案例6: 内部表 和 外部表的区别 ----------------------------
/*
问题: 内部表 和 外部表的区别是什么?
答案:
1. 格式不同.
直接创建Hive表默认就是内部表, 如果要创建外部表, 必须用 external 关键字.
2. 删除时是否会删除源文件.
删除内部表的时候, 除了会删除元数据, 还会删除源文件.
删除外部表的时候, 只会删除元数据, 但是源文件(HDFS上)还在.
3. 应用场景不同.
删除Hive的时候, 如果也要同步删除源文件, 就用内部表.
否则就用外部表, 例如: Hbase 和 Hive的映射.
*/
-- 1. 建表, 内部表.
create table t_archer_inner (
id string comment 'ID',
name string comment '英雄',
hp_max string comment '最大生命',
mp_max string comment '最大法力',
attack_max string comment '最高物攻',
defense_max string comment '最大物防',
attack_range string comment '攻击范围',
role_main string comment '主要定位',
role_assist string comment '次要定位'
) comment '射手表'
row format delimited fields terminated by '\t';
-- 2. 上传文件, 查询表数据.
select * from t_archer_inner; -- 10条.
-- 3. 建表, 外部表.
create external table t_archer_outer (
id string comment 'ID',
name string comment '英雄',
hp_max string comment '最大生命',
mp_max string comment '最大法力',
attack_max string comment '最高物攻',
defense_max string comment '最大物防',
attack_range string comment '攻击范围',
role_main string comment '主要定位',
role_assist string comment '次要定位'
) comment '射手表'
row format delimited fields terminated by '\t';
-- 4. 上传文件, 查询表数据.
select * from t_archer_outer; -- 10条
-- 查看所有数据表, 如果这里边没有, 说明Hive的元数据删除了.
show tables;
-- 5. 删除内部表 和 外部表, 观察: Hive是否还有表的信息(元数据), HDFS上源文件是否还存在.
drop table t_archer_inner;
drop table t_archer_outer;
-- 6. 思考: 如果删除了外部表, 又把外部表创建了出来, 里边有数据吗?
-- 答案: 如果表名, 数据类型, 字段数量等和原表(删除前的外部表)一致, 则重新创建表之后, 直接就有数据.
-- 7. 扩展: 内部表 和 外部表之间如何互相转换.
desc formatted t_archer_outer;
-- 外部表 转 内部表
alter table t_archer_outer set tblproperties('EXTERNAL'='FALSE');
-- 内部表 转 外部表
alter table t_archer_outer set tblproperties('EXTERNAL'='TRUE');
案例7: 分区表之 静态分区
-- ---------------------------- 案例7: 分区表之 静态分区 ----------------------------
/*
分区表详解:
概述/作用:
1. 分区 = 分文件夹, 即: 把1个整体 拆分成 n个文件夹, 避免全表扫描, 提高查询效率.
2. 例如: 公司成立10年所有的数据都在1个文件夹中放着, 查找数据, 每次都要全表扫描, 效率相对较低.
可以按照 年月分区, 把每年每月的数据都放一起, 这样查找时只要找指定的内容即可, 降低扫描次数, 提高效率.
格式:
create table 表名(
) partitioned by(分区字段1 数据类型, 分区字段2 数据类型...)
row format ......;
细节:
分区字段必须是表中没有的字段.
*/
-- 1. 建表 t_all_hero, 用来存储王者荣耀所有的英雄数据(射手, 法师, 刺客, 战士, 坦克, 辅助)
create table t_all_hero (
id string comment 'ID',
name string comment '英雄',
hp_max string comment '最大生命',
mp_max string comment '最大法力',
attack_max string comment '最高物攻',
defense_max string comment '最大物防',
attack_range string comment '攻击范围',
role_main string comment '主要定位',
role_assist string comment '次要定位'
) comment '王者荣耀英雄表'
row format delimited fields terminated by '\t'; -- 行格式切割 字段分隔符为 '\t'
-- 2. 把所有的英雄数据文件(例如: 射手, 法师, 刺客, 战士, 坦克, 辅助)都传入到该表所在的HDFS路径下.
-- 3. 查看表数据.
select * from t_all_hero;
-- 4. 新需求: 查询所有的 射手数据.
select * from t_all_hero where role_main='archer'; -- 10条
-- 5. 思考上述的查询方式, 效率高不高?
-- 答案: 不高, 因为需要全表扫描, 可以用分区表解决, 把每一类英雄数据放到一起, 这样查找某一类的时候, 可以精准查找, 避免全表扫描.
-- 6. 创建分区表, 按照 角色主要定位 进行分区(分文件夹)
create table t_all_hero_part (
id string comment 'ID',
name string comment '英雄',
hp_max string comment '最大生命',
mp_max string comment '最大法力',
attack_max string comment '最高物攻',
defense_max string comment '最大物防',
attack_range string comment '攻击范围',
role_main string comment '主要定位',
role_assist string comment '次要定位'
)
comment '王者荣耀英雄表'
partitioned by (role string) -- 按照角色主要定位分区, 分区字段必须是表中没有的字段
row format delimited fields terminated by '\t';
-- 7. 往分区表中添加数据, 静态分区, 即: 分区字段 = 值, 类似于 静态IP, 都是手动写死的.
load data local inpath '/export/data/hivedata/archer.txt' into table t_all_hero_part partition (role='sheshou');
load data local inpath '/export/data/hivedata/assassin.txt' into table t_all_hero_part partition (role='cike');
load data local inpath '/export/data/hivedata/mage.txt' into table t_all_hero_part partition (role='fashi');
load data local inpath '/export/data/hivedata/support.txt' into table t_all_hero_part partition (role='fuzhu');
load data local inpath '/export/data/hivedata/tank.txt' into table t_all_hero_part partition (role='tanke');
load data local inpath '/export/data/hivedata/warrior.txt' into table t_all_hero_part partition (role='zhanshi');
-- 8. 查询表数据.
select * from t_all_hero; -- 不分区
select * from t_all_hero where role_main='archer'; -- 全表扫描, 效率低.
select * from t_all_hero_part; -- 分区
select * from t_all_hero_part where role='sheshou'; -- 精准扫描, 效率相对较高.
案例8: 分区表之 动态分区
-- ---------------------------- 案例8: 分区表之 动态分区 ----------------------------
-- 细节: 动态分区时, 要关闭严格模式, 因为严格模式要求: 在动态分区的时候, 至少要有1个静态分区.
set hive.exec.dynamic.partition.mode=nonstrict; -- 关闭严格模式
-- 例如: partition(role) 动态分区, partition(role='sheshou') 静态分区, partition(role='sheshou', year,month) 1个静态分区, 2个动态分区
-- 1. 建表, 动态分区表.
create table t_all_hero_part_dynamic (
id string comment 'ID',
name string comment '英雄',
hp_max string comment '最大生命',
mp_max string comment '最大法力',
attack_max string comment '最高物攻',
defense_max string comment '最大物防',
attack_range string comment '攻击范围',
role_main string comment '主要定位',
role_assist string comment '次要定位'
)
comment '王者荣耀英雄表'
partitioned by (role string) -- 按照角色主要定位分区, 分区字段必须是表中没有的字段
row format delimited fields terminated by '\t';
-- 2. 往(动态)分区表中添加数据, insert + select 方式.
insert into t_all_hero_part_dynamic partition (role) -- 动态分区
select *, role_main from t_all_hero; -- role_main是分区列, 内容一样的数据会被放到一起.
-- 3. 查看结果.
select * from t_all_hero_part_dynamic;
案例9: 分区表之 总结
-- ---------------------------- 案例9: 分区表之 总结 ----------------------------
/*
分区总结:
1. 分区 = 分文件夹, 目的是: 避免全表扫描, 降低扫描的次数, 提高查询效率.
2. 分区字段必须是表中 没有的 字段.
3. Hive默认开启了严格模式, 要求: 动态分区时, 至少指定1个静态分区.
partition(role) 动态分区
partition(role='fashi') 静态分区
4. 实际开发中, 可以是单级分区, 也可以是多级分区, 多级分区一般不会超过3层, 且大多数是 年月日分区.
*/
-- 0. 创建学生分区表.
create table stu_part(
id int,
name string,
gender string,
age int,
sno string
)
partitioned by (year string, month string) -- 按照年, 月分区.
row format delimited fields terminated by ',';
-- 1. 往学生分区表中添加数据.
insert into stu_part partition (year='2023', month='05') -- 静态分区
select * from stu;
select * from stu_part;
-- 2. 查看表所有的分区.
show partitions stu_part;
-- 3. 手动添加分区
alter table stu_part add partition(year='2023', month='03'); -- 手动添加1个分区
alter table stu_part add
partition(year='2023', month='01')
partition(year='2022', month='01')
partition(year='2022', month='03')
partition(year='2021', month='05')
partition(year='2023', month='09'); -- 手动添加多个分区
-- 4. 修改分区
alter table stu_part partition (year='2023', month='09') rename to partition (year='2021', month='11');
-- 5. 删除分区.
alter table stu_part drop partition(month='01'); -- 删除(每年)所有的 1月分区
alter table stu_part drop partition(year='2021'); -- 删除2021年所有的分区
alter table stu_part drop partition(year='2023',month='03'); -- 删除2023年03月的分区
案例10: 分桶表
-- ---------------------------- 案例10: 分桶表 ----------------------------
/*
分桶表相关:
概述:
分桶 = 分文件.
目的/好处:
1. 把1个整体(大文件) 拆分成 n个小文件, 减少join的次数, 提高查询效率.
2. 方便采样, 抽样.
细节:
1. 分桶字段必须是表中已有的字段.
2. 分桶表不能通过load方式加载数据, 因为要把文件拆分成n份, 所以必须通过 insert + select方式实现, 它的底层会转MR.
3. 分桶的原理: 哈希取模法
分桶的公式: 分桶字段的哈希值 % 桶的个数 = 结果, 结果是啥, 就进那个桶
例如: 按照性别(男, 女)分桶, 分成2个桶, 执行流程如下:
select abs(hash('男')) % 2 => 0, 1
select abs(hash('男')) % 2 => 0, 1
4. 哈希值 就是 根据字段的内容计算出来的1个整数(可能是正数, 也可能是负数), 类似于: 每个学生都有自己的 学号一样.
5. 同一对象(内容)哈希值一定相同, 不同对象哈希值一般不同.
例如: 重地和通话, 儿女和农丰... 去Java中执行.
*/
-- 0. 分桶原理, 哈希取模法
select hash('男'); -- -27446
select hash('女'); -- -28845
select abs(hash('男')); -- 27446
select abs(hash('男')) % 2; -- 0
select abs(hash('女')) % 2; -- 1
-- 1. 创建普通表.
create database day07;
use day07;
show tables;
create table stu(
id int,
name string,
gender string,
age int,
sno string
) row format delimited fields terminated by ',';
-- 2. 添加数据到普通表, 查询表数据.
select * from stu;
-- 3. 创建分桶表(不排序), 指定: 按照性别分桶, 分成2个桶.
create table stu_bucket(
id int,
name string,
gender string,
age int,
sno string
) clustered by (gender) into 2 buckets -- 按照性别分桶, 分成2个桶, 分桶字段必须是表中已有的字段
row format delimited fields terminated by ',';
-- 4. 直接上传文件到分桶表中, 观察, 分桶表中是否有数据?
-- 结论: 有数据, 但是没有按照我们的要求分成2个桶, 思考: 为啥?
-- 答案: 分桶表必须通过 insert + select 方式添加数据, 底层会转MR, 把数据分成n个文件.
insert into stu_bucket select * from stu;
select * from stu_bucket; -- 22条数据, HDFS路径下 2个文件.
-- 6. 创建分桶(排序)表, 按照: 性别分, 桶内按照年龄降序排列.
create table stu_bucket_sort(
id int,
name string,
gender string,
age int,
sno string
) clustered by (gender) sorted by (age desc) into 2 buckets -- 按照性别分桶, 分成2个桶, 桶内数据按照 年龄降序排列.
row format delimited fields terminated by ',';
-- 7. 往分桶排序表中添加数据, 然后查看结果.
insert into stu_bucket_sort select * from stu; -- 普通表的数据 => 分桶排序表中.
select * from stu_bucket_sort;
案例11: 复杂类型 array
-- ---------------------------- 案例11: 复杂类型 array ----------------------------
-- 源文件中数据格式为: "zhangsan beijing,shanghai,tianjin,hangzhou" 建表存储.
-- 1. 建表, 用于存储上述格式的数据.
drop table test_array;
create table test_array(
name string,
addrs string
) row format delimited
fields terminated by '\t'; -- 切割后, 数据格式为: "zhangsan", "beijing,shanghai,tianjin,hangzhou"
-- 2. 上传源文件, 查看表数据.
select * from test_array;
-- 3. 查找包含 hangzhou 这个城市的 数据信息.
select * from test_array where addrs like '%hangzhou%';
-- 4. 查找每个人, 都去过几个城市, 第2个去的城市是哪里.
select * from test_array;
-- 5. 虽然能实现需求, 但是比较麻烦, 针对于这个特定的数据格式, 我们用复杂类型 array来表示.
create table test_array(
name string,
addrs array<string> -- 表示 所有去过的城市的数组. 数据格式为: [值1, 值2, 值3...]
) row format delimited
fields terminated by '\t' -- 切割后, 数据格式为: "zhangsan", "beijing,shanghai,tianjin,hangzhou"
collection items terminated by ','; -- 切割后, 数据格式为: "zhangsan", ["beijing", "shanghai", "tianjin", "hangzhou"]
-- 6. 上传文件, 查看表数据.
select * from test_array;
-- 7. 数组的相关操作.
-- 查询所有数据
select * from test_array;
-- 查询addrs数组中第一个元素, 索引是从 0 开始数的.
select *, addrs[0] from test_array;
-- 查询addrs数组中元素的个数
select * , size(addrs) from test_array;
-- 查询addrs数组中包含tianjin的信息
select *, array_contains(addrs, 'tianjin') from day07.test_array; -- 这样是看结果, 不是筛选.
select * from day07.test_array where array_contains(addrs, 'tianjin')=true;
-- 最终写法, 即: 上述代码的变形版, 省略true.
select * from day07.test_array where array_contains(addrs, 'tianjin');
案例12: 复杂类型 struct
-- ---------------------------- 案例12: 复杂类型 struct ----------------------------
-- 源文件中数据格式为: "1#周杰轮:11" 建表存储.
-- 流程: "1#周杰轮:11" => "1", "周杰轮:11" => "1", {"name":"周杰轮", "age":11}
-- 1. 建表, 用于存储上述格式的数据.
drop table test_struct;
create table test_struct(
name string,
info struct<name:string, age:int> -- 结构体, 即: 键值对形式, 可以有无数组键值对组合.
) row format delimited
fields terminated by '#' -- 切割后, 数据格式为: "1", "周杰轮:11"
collection items terminated by ':'; -- 切割后, 数据格式为: "1", {"name":"周杰轮","age":11}
-- 2. 上传表数据, 查看表信息.
select * from test_struct;
-- 3. 结构体的相关操作.
select *, info.name from test_struct; -- 通过 info.键名 的方式, 可以实现 根据键获取其对应的值.
-- ---------------------------- 案例13: 复杂类型 map ----------------------------
/*
源文件中数据格式为: "1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28"
流程为:
1. 原始数据为: "1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28"
2. 按照,切割: "1", "zhangsan", "father:xiaoming#mother:xiaohuang#brother:xiaoxu", "28"
3. 按照#切割: "1", "zhangsan", ["father:xiaoming", "mother:xiaohuang", "brother:xiaoxu"], "28"
4. 按照:切割: "1", "zhangsan", {"father":"xiaoming", "mother":"xiaohuang", "brother":"xiaoxu"}, "28"
*/
-- 1. 建表, 用于存储上述格式的数据.
create table test_map(
id int,
name string,
members map<string, string>, -- 键值都是string类型
age int
) row format delimited
fields terminated by ',' -- 切割后, 数据格式为: "1", "zhangsan", "father:xiaoming#mother:xiaohuang#brother:xiaoxu", "28"
collection items terminated by '#' -- 切割后, 数据格式为: "1", "zhangsan", ["father:xiaoming", "mother:xiaohuang", "brother:xiaoxu"], "28"
map keys terminated by ':'; -- 切割后, 数据格式为: "1", "zhangsan", {"father":"xiaoming", "mother":"xiaohuang", "brother":"xiaoxu"}, "28"
-- 2. 查询表数据.
select * from test_map;
-- 3. 完成如下查询.
-- 查询全部
select * from test_map;
-- 查询father、mother这两个map的key
select *, members['father'] father, members['mother'] mother from test_map; -- 字典通过 字段名['键名'] 的方式获取其对应的值.
-- 查询全部map的key，使用map_keys函数，结果是array类型
select *, map_keys(members) keys from test_map; -- 结果是array类型 ["father","mother","brother"]
-- 查询全部map的value，使用map_values函数，结果是array类型
select *, map_values(members) keys from test_map; -- 结果是array类型 ["王林","如花","潇潇"]
-- 查询map类型的KV对数量
select *, size(members) kv from test_map; -- 结果是array类型
-- 查询map的key中有brother的数据
-- 数组是否包含某个元素 ["father","mother","brother"] 'brother'
select * from test_map where array_contains( map_keys(members), 'brother');
-- 获取家庭成员中, 有叫如花的信息.
-- 数组是否包含某个元素 ["王林","如花","潇潇"] '如花'
select * from test_map where array_contains( map_values(members), '如花');
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
