内容大纲:
1. 掌握HQL DDL 数据定义语言 //针对表的
数据类型
SerDe序列化机制
分隔符语法
内部表、外部表
数据存储路径
分区表
分桶表
alter修改表
2. 掌握HQL DML 数据操作语言 //针对表数据的
load加载数据
insert插入数据
多重插入
动态分区插入
数据导出
********************************* 具体内容 *********************************
-
HQL DDL语句_建表语法详解
概述:
数据定义语言, 全称是 Data Definition Language, 主要是操作数据库, 数据表, 字段的.
关键字:
create, alter, drop
HQL DDL建表语法完整格式解释:
//详见图片. -
HQL DDL语句_数据类型详解
概述:
Hive中的数据类型指的是Hive表中的列字段类型。Hive数据类型整体分为两个类别：
原生数据类型包括： //primitive data type
数值类型: int, double,float
时间类型: date
字符串类型: string
杂项数据类型； miscellaneous
复杂数据类型包括： //complex data type
数组: array //类似于Python中的列表
映射: map //类似于Python中的字典
结构: struct
联合体: union注意事项:
1. Hive不区分大小写.
2. int 和 string 是使用的最多的数据类型.
3. 复杂数据类型的使用通常需要 和 分隔符指定语法配合使用.4. 如果定义的数据类型和文件不一致, hive通常会尝试隐式转换, 但是不保证成功.名词解释:
隐式转换: //也叫: 自动类型转换, 隐转
概述:
指的是 原生类型 从窄类型 到 宽类型的转换.
例如:
int => double //即: 10 => 10.0显式转换: //也叫: 强制类型转换, 强转 格式: cast(数值 as 目标数据类型) //使用cast()函数实现. 例如: cast("10.3" as double) //结果是: 10.3 cast(10.3 as int) //结果是: 10 cast("false" as double) //结果是: NULL, 即: 如果强制转换失败, 则函数返回NULL更多Hive中的数据类型描述, 请参考:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types -
HQL DDL语句_SerDe, 原生数据类型案例
SerDe解释:
SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。
序列化是对象转化为字节码的过程, 而反序列化是字节码转换为对象的过程。
Hive使用SerDe（和FileFormat）读取和写入行对象。
//简单记忆: 写 是 序列化, 读 是 反序列化.SerDe相关语法(多用,记清楚):
在Hive的建表语句中，和SerDe相关的语法为:
row format delimited/serde
delimited: //使用默认的LazySimpleSerDe类来处理数据, 这个是我们用的最多的.fields terminated by char //字段分隔符 collection items terminated by char //列表分隔符 map keys terminated by char //映射分隔符 lines terminated by char //行分隔符 serde: //如果要用其它的SerDe类, 甚至是用户自定义的SerDe类规则, 则使用这种方式. with serdeproperties //允许用户自定义SerDe规则, 目前用不到, 后期NoSQL阶段会用到.原生数据类型案例(入门demo,可不看):
需求:
文件archer.txt中记录了手游《王者荣耀》射手的相关信息，内容如下所示，其中字段之间分隔符为制表符\t,要求在Hive中建表映射成功该文件。
//文件在 day08_hive\资料\sql脚本、数据集\hive练习数据集\honor of kings\hero\archer.txt步骤: 1. 创建Hive表: create database if not exists itheima; use itheima; create table t_archer( id int comment "ID", name string comment "英雄名称", hp_max int comment "最大生命", mp_max int comment "最大法力", attack_max int comment "最高物攻", defense_max int comment "最大物防", attack_range string comment "攻击范围", role_main string comment "主要定位", role_assist string comment "次要定位" ) comment "王者荣耀射手信息" row format delimited fields terminated by "\t"; desc t_archer; //查看表结构信息, 即: 字段名, 数据类型, 描述信息 desc formatted t_archer; //查看表结构信息, 详细信息. 2. 把 archer.txt 文件上传到 HDFS的 t_archer 表下. HDFS路径: /user/hive/warehouse/itheima.db/t_archer 3. 在Hvie中查询 t_archer 表的信息, 有数据即可. select * from t_archer; -
HQL DDL语句_复杂数据类型案例(map案例,也可以不看,前提是不需要练习了)
需求:
文件hot_hero_skin_price.txt中记录了手游《王者荣耀》热门英雄的相关皮肤价格信息，内容如下,要求在Hive中建表映射成功该文件
//文件在 day09_hive\资料\sql脚本、数据集\hive练习数据集\honor of kings\hot_hero_skin_price.txt步骤:
1. 创建Hive表.
create table t_hot_hero_skin_price(
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 ':';2. 把 archer.txt 文件上传到 HDFS的 t_hot_hero_skin_price 表下. HDFS路径: /user/hive/warehouse/itheima.db/t_hot_hero_skin_price 3. 在Hvie中查询 t_hot_hero_skin_price 表的信息, 有数据即可. select * from t_hot_hero_skin_price; 4. 新的需求: 查询 孙悟空 大圣娶亲 这款皮肤的价格. select name, skin_price['大圣娶亲'] from t_hot_hero_skin_price limit 1; -
HQL DDL语句_默认分隔符案例
需求:
//文件在 day09_hive\资料\sql脚本、数据集\hive练习数据集\honor of kings\team_ace_player.txt步骤:
1. 创建Hive表.
create table t_team_ace_player(
id int,
team_name string,
ace_player_name string
);2. 把 team_ace_player.txt 文件上传到 HDFS的 t_team_ace_player 表下. HDFS路径: /user/hive/warehouse/itheima.db/t_team_ace_player 3. 在Hvie中查询 t_team_ace_player 表的信息, 有数据即可. select * from t_team_ace_player;结论:
1. hive的默认分隔符是 \001, 在Linux中它是一个不可见字符,就是它在编码表中没有对应的字符. 在linux中显示为^A,在Notepad++中显示的是 SOH.
2. \001 如果想在Linux中打印出来, 可以按下 Ctrl V + Ctrl A 实现. -
HQL DDL语句_关于Hive文件的存储路径的问题
-
存储路径由hive.metastore.warehouse.dir 属性指定。默认值是：/user/hive/warehouse
-
不管是内部表，还是外部表，在HDFS上的路径如下：
/user/hive/warehouse/数据库名.db/表名 -
在建表的时候 可以使用location关键字指定表的路径在HDFS任意位置, 例如:
create table t_team_ace_player_location(
id int,
team_name string,
ace_player_name string
) location '/data/aaa'; //使用location关键字指定本张表数据在hdfs上的存储路径!!!/ location '/data' 的本质是,把我们的表建到/data/目录下,表对应的文件名更名为aaa,即:/aaa就表示我们这张表.(重点)
-
在实际开发中，最好集中维护管理Hive表数据，避免文件在HDFS随意存放。
-
-
HQL DDL语句_内部表和外部表详解
内部表:正常创建的表
外部表:用external关键字创建的表,删除表的时候不会删除源文件,只删除元数据
区别:
1.删除表的时候,内部表的元数据连同源文件都会被删除.
2.创建内部表直接写,默认的都是内部表,创建外部表需要加external关键字.外部表的好处:
防止误操作删除表的时候 把表的数据一起删除。案例:
//创建内部表
create table student_inner(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';//创建外部表 关键字external create external table student_external(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ','; //上传文件到内部表、外部表中, 文件在: day08_hive\资料\sql脚本、数据集\hive练习数据集\honor of kings\students.txt hadoop fs -put students.txt /user/hive/warehouse/itheima.db/student_inner hadoop fs -put students.txt /user/hive/warehouse/itheima.db/student_external //查询表数据, 好像没啥区别 都能映射成功 数据也都在HDFS上 select * from student_inner; select * from student_external;针对内部表、外部表 进行drop删除操作
drop table student_inner; //内部表在删除的时候 元数据和数据都会被删除
drop table student_external; //外部表在删除的时候 只删除元数据 而HDFS上的数据文件不会动结论:外部表只删除hive中的元数据,不会删除源文件,内部表则会把元数据和源文件都删除了.
如何知道表是 内部表 还是 外部表呢?
desc formatted 表名; 显示出的信息,找table_type字段就知道了
//MANAGED_TABLE 内部表、受控表
//EXTERNAL_TABLE 外部表 -
HQL DDL语句_分区表的由来 分区的本质:分文件夹(分目录)
具体见图片中图3.
所有的数据源文件都集中在一起，每次查询数据的时候,都需要把所有的文件
检索一下，即:满查询(全查询).效率相对较低.如何才能够减少全表扫描 而结果又正确。
将文件分区(分文件夹),这样查询具体信息时可以只查单个文件夹中的文件,提高查询效率.分区表的目的: //详见图片
分区字段必须是表中不存在的字段, 目的是: 把 整表 变成=> n个表 管理, 提高Hive的查询效率. -
HQL DDL语句_分区表的创建及添加数据
-
根据角色主定位字段进行分区 字段是role_main
-
分区表建表
create table t_all_hero_part(
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";
3, 查询分区表 发现分区字段也显示出来了,说明分区后它会给我们补充分区字段.
select * from t_all_hero_part;-
分区表的数据加载 之 静态分区加载
4.1 静态加载分区表数据
load data local inpath '/export/data/hivedata/archer.txt' into table t_all_hero_part partition(role='sheshou');/role是分区字段, 它的值 就是分区后, 文件夹的名字.
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');
!!! /role是分区字段, 它的值 就是分区后, 文件夹的名字.4.2 查询一下验证是否加载成功
select * from t_all_hero_part;4.3 思考：如果分区很多 一个一个加载，效率如何？
因为静态分区的时候 分区值是用户手动写死的 有写错的风险,所以一般使用动态分区。
静态分区就是插入数据的时候分区值是写死的,动态是根据插入的对应字段的值自动分区. -
分区表的数据加载 之 动态分区加载
5.1 设置允许动态分区、设置动态分区模式
//动态分区
set hive.exec.dynamic.partition=true; //注意hive3已经默认开启了set hive.exec.dynamic.partition.mode=nonstrict; //关闭严格模式 //模式分为strict严格模式 nonstrict非严格模式, 严格模式要求 分区字段中至少有一个分区是静态分区。 //例如: partition(province='河南省', city) 这里的province因为有固定值, 所以是静态分区, city没有值, 所以是动态分区.5.2 通过 insert + select 实现动态分区
插入的数据来自于后面的查询语句返回的结果。查询返回的内容，其字段类型、顺序、个数要和待插入的表保持一致。5.3 案例
5.3.1 创建一张新的分区表 t_all_hero_part_dynamic
create table t_all_hero_part_dynamic(
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";5.3.2 执行动态分区插入 //注意 分区值并没有手动写死指定 insert into table t_all_hero_part_dynamic partition(role) select tmp.*, tmp.role_main from t_all_hero tmp; 5.3.3 查询验证结果 select * from t_all_hero_part_dynamic;
-
-
HQL DDL语句_多级分区表的用法
记忆:
实际开发中, 多分区一般最多只有2层分区, 且大多数都是按照日期划分的.相关操作:
1. 查询指定表的分区.
//创建多分区表.
create external table student_part(
Sno int,Sname string,Sex string,Sage int,Sdept string
) partitioned by(year string, month string)
row format delimited fields terminated by ',';2. 查看该表的分区, 因为刚造的表, 所以没有分区. show partitions student_part; 3. 手动添加分区. load data local inpath '/export/data/hivedata/students.txt' into table student_part partition(year="2020",month='01'); alter table student_part add partition(year="2020",month='03'); alter table student_part add partition(year="2021",month='01'); alter table student_part add partition(year="2021",month='05'); 4. 查看分区 show partitions student_part; 5. 删除分区 alter table student_part drop partition(month='01'); -- 所有标记为1月的分区全部删除.总结:多级分区表就相当于树形结构罢了,目录下还有目录,没啥
-
HQL DDL语句_分桶操作详解
细节:
分桶字段必须是表中存在的.
分桶公式:
分桶字段的哈希值 % 分桶个数 = ...
分桶的目的是:
1. 检查表的join操作, 提高查询效率.
2. 方便我们进行数据抽样.案例:
//源数据格式: 2021-01-28,Washburn,Wisconsin,55129,1288,19 统计时间,县,洲,县的编码,确诊人数,死亡人数
1. 创建分桶表
CREATE TABLE t_usa_covid19_bucket(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
clustered by(state) INTO 5 BUCKETS; //分桶的字段一定要是表中已经存在的字段CREATE TABLE t_usa_covid19_bucket( count_date string, county string, state string, fips int, cases int, deaths int) clustered by(state) sorted by(cases desc) INTO 5 BUCKETS; //按照洲分桶, 且分桶之后桶内按照确诊人数排序. 2. 创建和该分桶表字段对应的 普通表. CREATE TABLE t_usa_covid19( count_date string, county string, state string, fips int, cases int, deaths int) row format delimited fields terminated by ','; 3. 往普通表中添加数据. hadoop fs -put ..... 4. 查看普通表中的数据 select * from t_usa_covid19 limit 10; 5. 把普通表的数据添加到分桶表中. insert into t_usa_covid19_bucket select * from t_usa_covid19;分桶图解:
见图片图4 -
HQL DDL语句_其他语句
//查询类的:掌握, 其它操作大多理解或者了解, 因为Hive基本上查询操作居多, 更改操作较少.-
创建数据库, 指定创建者的名字. //了解
create database bigdata52 with dbproperties('createBy'='hangge'); -
查看数据库的信息.
show databases; -- 查看所有数据库.
show create database bigdata52; -- 查看某个数据库的创建信息. -
删除数据库 //理解
drop database bigdata52; //如果数据库是空库, 可以直接删.
drop database itcast cascade; //如果数据库有数据表, 需要额外加关键字 cascade -
查看表的信息.
desc student_inner;
desc formatted student_inner; -- 查看表的详细信息.
show create table student_inner; -- 查看某个数据表的创建信息. -
修改表名 //了解
create table temp_my(id int, name string); //造表.
alter table temp_my rename to tmp; -
添加字段 //了解
alter table tmp add columns(gender string); -
关于分区的操作 //重点关注一下.
show partitions student_part; //查看某表所有分区
alter table student_part add partition(year='2020',month='10'); //添加分区.
alter table student_part drop partition(year='2020',month='03'); //删除分区.
-
-
HQL DML语句_load方式加载数据
案例:
演示DML相关操作: 数据操作语言, 主要是对表数据进行 更新操作(增, 删, 改).具体的HQL语句:
show functions; //查看hive支持的所有函数.准备动作:
//创建数据表, 3张表. 源数据格式: 95001,李勇,男,20,CS
//建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';//建表student_HDFS 用于演示从HDFS加载数据到非分区表 create table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ','; //建表student_HDFS_p 用于演示从HDFS加载数据到分区表 create table student_HDFS_p(num int,name string,sex string,age int,dept string) partitioned by(country string) row format delimited fields terminated by ',';load data 四种情况:
-- 方式1: 从本地(Linux)加载数据到 Hive的 内部表/外部表/分桶表中. load data local inpath '/export/data/hivedata/students.txt' into table student_load_linux; -- 方式2: 从本地(Linux)加载数据到 Hive的 分区表中. load data local inpath '/export/data/hivedata/students.txt' into table student_load_linux_partition partition(year='2022',month='06'); -- 方式3: 从HDFS文件系统中, 加载数据到Hive 内部表/外部表/分桶表中. -- 导入数据, 不带local就是HDFS文件系统. load data inpath '/hg/students.txt' into table student_load_hdfs; -- 类似于 hadoop fs -mv.. 的操作, 就是: 剪切. -- 方式4: 从HDFS文件系统中, 加载数据到Hive 分区表. -- 导入数据. load data inpath '/hg/students.txt' into table student_load_hdfs_partition partition(year='2022',month='06');