Hive-DDL
Hive的本质
把HDFS文件映射成一张Hive表, 然后通过写类SQL的语句来操作它(HDFS文件), 底层会被解析成MR程序, 交由Yarn来调度执行.
DDL(Data Define Language)是什么？
见名知意，
数据定义语言==Data Define Language。
对数据库的操作：包含创建(Create)数据库，数据表、修改数据库 (Alter table)
对数据表的操作：内部表(Managed Table)及外部表(External)，分区表(Partition)和分桶表
数据库Operation
create database if not exists 数据库名; -- 创建数据库
show databases; -- 查看系统中所有数据库
use 数据库名; -- 切换数据库
drop database 数据库名 casecade; -- 如果数据库下面有表，使用casecade进行级联删除数据库
切割符
Hive表默认切割符是'\001'符号, 在windows中显示为 SOH, 在HDFS中显示为 小口, 在Linux中显示为 ^A
数据类型
常用的数据类型:
原生类型:
int 整数
double 小数
string 字符串
timestamp 时间戳
date 日期(年月日)
复杂类型:
array 数组类型
map 字段(映射)类型
struct 结构体
union 联合体
Comment乱码
原因是: 我们的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中把刚才的表删掉, 然后重建, 再次查询, 注释(描述信息)不乱码了.
具体步骤如下：
show databases ;
use hive3;
-- 下面修改是在MySQL中修改，因为MySQL记录维护着元数据
# （1）修改表字段注解和表注解
use hive3;
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
# （2）修改分区字段注解
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
# （3）修改索引注解
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
# ---下面这个在Hive的配置文件中修改
-- cd /export/server/hive/conf
-- 修改 hive-site.xml 文件
<!-- 存储元数据mysql相关配置 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://node1:3306/hive3?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value>
</property>
建表语法
建表语法: create [external] table [if not exists]表名( 列名 数据类型 [ comment '描述信息'], 列名 数据类型 [ comment '描述信息'], 列名 数据类型 [ comment '描述信息'], ...... ) comment '表的描述信息' 分区 分桶(桶内排序 + 分桶个数) 行格式切割符 存储格式(行存储, 列存储) 存储位置 表的属性信息;
建表, 表字段, 数据类型等要和 HDFS源文件保持一致
建表并制定分隔符为\t
create table archers(
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';
上传源文件(5种方式)
上传源文件到Hive表所在的 HDFS路径下
load data local inpath '/root/archer.txt' overwrite into table archers;
-- 从linux写入(有local，覆盖写入(有overwrite
load data inpath '/wordcount/archer.txt' into table archers;
-- 从hdfs中写入到hdfs中，追加写入
哪里来的5种方式，细数一下
上传Linux文件到hdfs中，有两种方式，追加写入和覆盖写入
上传hdfs文件到hdfs中，有两种方式，追加写入和覆盖写入
通过hdfs端口8979上传文件，这是Hadoop3.x的特性，2.x好像不行
查看表结构信息
三种方式
desc archers;
desc formatted archers;
show create table archers; -- 一般比较常用
其它的建表方式
create table archer1 as select * from archers; -- 会转mr执行，且是默认分隔符
create table archer2 row format delimited fields terminated by ',' as select * from archers; -- 会转mr执行
create table archer3 like archers; -- 创建一个空表，但是字段名称跟archer一样
修改表 相关操作
alter table archer1 rename to archers1; -- 修改表名
alter table archers1 set location '/root/aa'; -- 这只会修改表的映射路径，实际文件并没有在/root/aa下，而还在默认的地方。
alter table archers1 set tblproperties('EXTERNAL'='TRUE'); -- 修改表为外部表
alter table archers1 set tblproperties('EXTERNAL'='FALSE'); -- 修改表为内部表
alter table archer2 add columns (address string, tel string); -- 向表中添加地址和电话字段
alter table archer2 change address addr string; -- 修改表中的字段address为addr
drop table archer2; -- 删除表的元数据（支持删除内部表和外部表的元数据
truncate table archer2; -- 清空表（只能清空内部表，不能清空外部表，因为没有权限
-- 注意hive中没有delte from archer2 这种操作方式
面试题: 内部表 和 外部表的区别是什么?
-
格式不同.直接创建Hive表默认就是内部表, 如果要创建外部表, 必须用 external 关键字. -
删除时是否会删除源文件.删除内部表的时候, 除了会删除元数据, 还会删除源文件.删除外部表的时候, 只会删除元数据, 但是源文件(HDFS上)还在. -
应用场景不同.删除Hive的时候, 如果也要同步删除源文件, 就用内部表. 否则就用外部表, 例如: Hbase 和 Hive的映射.
分区表之 静态分区
分区表详解: 概述/作用: 1. 分区 = 分文件夹, 即: 把1个整体 拆分成 n个文件夹, 避免全表扫描, 提高查询效率. 2. 例如: 公司成立10年所有的数据都在1个文件夹中放着, 查找数据, 每次都要全表扫描, 效率相对较低. 可以按照 年月分区, 把每年每月的数据都放一起, 这样查找时只要找指定的内容即可, 降低扫描次数, 提高效率. 格式: create table 表名() partitioned by(分区字段1 数据类型, 分区字段2 数据类型…) row format ......;
细节: 分区字段必须是表中没有的字段.
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';
load data local inpath '/root/archer.txt' into table t_all_hero_part partition (role='sheshou');
-- 自定义分区为sheshou，（射手，也就是在数据库下面新建一个文件夹名为sheshou
分区表之 动态分区
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';
-- 细节: 动态分区时, 要关闭严格模式, 因为严格模式要求: 在动态分区的时候, 至少要有1个静态分区.
set hive.exec.dynamic.partition.mode; -- 查询是否是严格模式，查询结果为strict
set hive.exec.dynamic.partition.mode=nonstrict; -- 关闭严格模式
insert into t_all_hero_part_dynamic partition (role)
select *,role_main 3 t_all_hero; -- 通过一个insert + select将一个已经存在的表转换为动态分区表
分区表二级分区
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 ',';
insert into stu_part partition (year='2023',month='03')
select * from stu; -- 指定往二级分区中插入数据
show partitions stu_part; -- 查看数据表中所有分区
alter table stu_part add partition (year='2022',month='02'); -- 想分区表中添加新的分区
alter table stu_part partition(year='2022',month='02') rename to partition (year='2022',month='03'); -- 修改分区
alter table stu_part drop partition (year='2022',month='03'); -- 删除分区
