学习目标
理解Hive SerDe机制、分隔符语法
掌握内外部表、分区表、分桶表创建使用
修改、删除
内容大纲
核心：建表语句 直接决定了表和文件之间能否映射成功
数据类型
SerDe序列化机制
分隔符语法
内部表、外部表
数据存储路径
分区表
分桶表
alter修改表
01_Apache Hive DDL 概念与语法树介绍
蓝色字体是建表语法的关键字，用于指定某些功能。
[ ]中括号的语法表示可选。
|表示使用的时候，左右语法二选一。
建表语句中的语法顺序要和语法树中顺序保持一致。
02_Apache Hive DDL 建表语句 表存在忽略异常
IF NOT EXISTS
-
建表的时候，如果表名已经存在，默认会报错，通过IF NOT EXISTS关键字可以忽略异常。
0: jdbc:hive2:
0: jdbc:hive2:
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. AlreadyExistsException(message:Table t_1 already exists) (state=08S01,code=1)
0: jdbc:hive2:
0: jdbc:hive2:
Error: Error while compiling statement: FAILED: ParseException line 1:0 cannot recognize input near 'creatf' 'table' 't_1' (state=42000,code=40000)
0: jdbc:hive2:
hivesql
03 Apache Hive DDL 建表语句 数据类型
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
-
Hive除了支持SQL类型之外，还支持java数据类型； -
Hive除了支持基础数据类型之外，还支持复合类型（array数组 map映射）；
- 针对复合类型的数据 要想直接从文件中解析成功 还必须配合分隔符指定的语法。
-
Hive中大小写不敏感； -
在建表的时候，最好表的字段类型要和文件中的类型保持一致，
- 如果不一致，Hive会尝试进行类型隐式转换，不保证转换成功，如果不成功，显示null;
-
栗子
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";
select * from t_archer;
desc formatted t_archer;
[root@node1 hivedata]
[root@node1 hiedata]
/root/hivedata
04_Apache Hive DDL 建表语句 SerDe机制、分隔符指定语法
05_Apache Hive DDL 建表语句 默认分隔符
-
默认分隔符
- Hive在建表的时候，如果没有row format语法，则该表使用\001默认分隔符进行字段分割；
- 如果此时文件中的数据字段之间的分隔符也是\001 ，那么就可以直接映射成功。
- 针对默认分隔符，其是一个不可见分隔符，在代码层面是\001表示
- 在vim编辑器中，连续输入ctrl+v 、ctrl+a；
- 在实际工作中，Hive最喜欢的就是\001分隔符，在清洗数据的时候，有意识的把数据之间的分隔符指定为\001;
-
栗子
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 ':';
hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/itheima.db/t_hot_hero_skin_price
select *
from t_hot_hero_skin_price;
create table t_hot_hero_skin_price_str(
id int,
name string,
win_rate int,
skin_price string
)
row format delimited
fields terminated by ',';
hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/itheima.db/t_hot_hero_skin_price_str
·1、不管使用map还是使用string来定义数据 都能解析映射成功
2、区别在于使用的过程中 一个是针对map类型数据处理 一个是针对string类型的数据处理
select skin_price from t_hot_hero_skin_price;
select skin_price from t_hot_hero_skin_price_str;
select skin_price["至尊宝"] from t_hot_hero_skin_price limit 1;
select skin_price["至尊宝"] from t_hot_hero_skin_price_str limit 1;
create table t_team_ace_player(
id int,
team_name string,
ace_player_name string
);
hadoop fs -put team_ace_player.txt /user/hive/warehouse/itheima.db/t_team_ace_player
select * from t_team_ace_player;
06_Apache Hive DDL 建表语句 内部表、外部表
create table student_inner(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';
create external table student_external(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';
hadoop fs -put students.txt /user/hive/warehouse/itheima.db/student_inner
hadoop fs -put students.txt /user/hive/warehouse/itheima.db/student_external
drop table student_inner;
drop table student_external;
07_Apache Hive DDL 建表语句 location存储位置
存储路径由hive.metastore.warehouse.dir 属性指定。默认值是：/user/hive/warehouset
-
不管是内部表，还是外部表，在HDFS上的路径如下： /user/hive/warehouse/itcast.db/t_array
/user/hive/warehouse/数据库名.db/表名
-
栗子 create table t_team_ace_player_location_default_location(
id int,
team_name string,
ace_player_name string);
/user/hive/warehouse/itheima.db/t_team_ace_player_location_default_location
create table t_team_ace_player_location(
id int,
team_name string,
ace_player_name string)
location '/aaa';
-
在实际开发中，最好集中维护管理Hive表数据，避免文件在HDFS随意存放。
08_Apache Hive DDL 建表语句 分区表创建、静态数据加载、分区裁剪
create table t_all_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";
hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/itheima.db/t_all_hero
select * from t_all_hero;
select count(*) from t_all_hero where role_main="archer" and hp_max >6000;
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_main string)
row format delimited
fields terminated by "\t";
Error: Error while compiling statement: FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns (state=42000,code=10035)
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 (juesedingwei string)
row format delimited
fields terminated by "\t";
select * from t_all_hero_part;
load data local inpath '/root/hivedata/archer.txt' into table t_all_hero_part partition(juesedingwei='sheshou');
load data local inpath '/root/hivedata/assassin.txt' into table t_all_hero_part partition(juesedingwei='cike');
load data local inpath '/root/hivedata/mage.txt' into table t_all_hero_part partition(juesedingwei='fashi');
load data local inpath '/root/hivedata/support.txt' into table t_all_hero_part partition(juesedingwei='fuzhu');
load data local inpath '/root/hivedata/tank.txt' into table t_all_hero_part partition(juesedingwei='tanke');
load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(juesedingwei='zhanshi');
select * from t_all_hero_part;
load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(juesedingwei='666');
思考：如果分区很多 一个一个加载，效率如何？
因为静态分区的时候 分区值是用户手动写死的 有写错的风险。
09_Apache Hive DDL 建表语句 动态分区插入数据
-
设置允许动态分区、设置动态分区模式
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
严格模式要求 分区字段中至少有一个分区是静态分区。
-
动态分区加载数据
insert + select
插入的数据来自于后面的查询语句返回的结果。
查询返回的内容，其字段类型、顺序、个数要和待插入的表保持一致。
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_dong string)
row format delimited
fields terminated by "\t";
insert into table t_all_hero_part_dynamic partition(role_dong)
select tmp.*,tmp.role_main from t_all_hero tmp;
select * from t_all_hero_part_dynamic;
Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict
在执行动态分区插入数据的时候，如果是严格模式strict，要求至少一个分区为静态分区？
partition(guojia=“zhongguo”,sheng) --第一个分区写死了（静态） 符合严格模式。
partition(guojia,sheng) --两个分区都是动态确定的 需要非严格模式
-
分区表的使用–分区裁剪
select count(*) from t_all_hero where role_main="archer" and hp_max >6000;
select count(*) from t_all_hero_part where role="sheshou" and hp_max >6000;
10_Apache Hive DDL 建表语句 多重分区及分区表注意事项
10.1 分区表注意事项
-
分区表的字段不能是表中已有的字段；分区的字段也会显示在查询结果上； -
分区的字段是虚拟的字段，出现在表所有字段的后面，其值来自于加载数据到表中的时候手动指定。 -
分区在底层的形式就是以文件夹管理不同的文件；不同文件夹就是表不同分区；文件夹的名字：
/user/hive/warehouse/数据库.db/表
/分区字段=分区值1
xxxx.txt
/分区字段=分区值2
zzzz.txt
10.2 多重分区表
create table t_user_double_p(id int,name string,country string) partitioned by(guojia string,sheng string) row format delimited fields terminated by ',';
load data local inpath '/root/hivedata/china_sh.txt' into table t_user_double_p partition(guojia="zhongguo",sheng="shanghai");
load data local inpath '/root/hivedata/china_sz.txt' into table t_user_double_p partition(guojia="zhongguo",sheng="shenzhen");
load data local inpath '/root/hivedata/usa_dezhou.txt' into table t_user_double_p partition(guojia="meiguo",sheng="dezhou");
select * from t_user_double_p where guojia="zhongguo"and sheng="shenzhen";
11_Apache Hive DDL 建表语句 分桶表语法、创建、加载
11.1 从语法层面解析分桶含义
CLUSTERED BY xxx INTO N BUCKETS
t_user(id int,name string);
CLUSTERED BY xxx ； xxx必须是表中的字段
N BUCKETS ；N的值就是分桶的个数
clustered by id into 3 bucket
hashfunc(分桶字段) % N bucket 余数相同的来到同一个桶中
1、如果分桶的字段是数字类型的字段，hashfunc(分桶字段)=分桶字段本身
2、如果分桶的字段是字符串或者其他字段，hashfunc(分桶字段) = 分桶字段.hashcode
11.2 分桶的创建
CREATE TABLE itheima.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 itheima.t_usa_covid19_bucket_sort(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
CLUSTERED BY(state)
sorted by (cases desc) INTO 5 BUCKETS;
11.3 分桶表的数据加载
set hive.enforce.bucketing=true;
CREATE TABLE itheima.t_usa_covid19(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
row format delimited fields terminated by ",";
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/itheima.db/t_usa_covid19
insert into t_usa_covid19_bucket select * from t_usa_covid19;
select * from t_usa_covid19_bucket;
11.4 分桶表的使用
select *
from t_usa_covid19_bucket where state="New York";
12_Apache Hive DDL 建表语句 分桶表的好处、注意事项
- 分桶表也是一种优化表，可以减少join查询时笛卡尔积的数量、提高抽样查询的效率。
- 分桶表的字段必须是表中已有的字段；
- 分桶表需要使用间接的方式才能把数据加载进入：insert+select
- 在join的时候，针对join的字段进行分桶，可以提高join的效率 减少笛卡尔积数量。
13_Apache Hive DDL 库、表、分区其他操作
因为Hive建表、加载数据及其方便高效；在实际的应用中，如果建表有问题，通常可以直接drop删除重新创建加载数据。时间成本极低。
如果表是外部表的话，更加完美了。
13.1 Database 数据库 DDL操作
create database if not exists itcast
comment "this is my first db"
with dbproperties ('createdBy'='Allen');
describe database itcast;
describe database extended itcast;
desc database extended itcast;
use default;
use itcast;
create table t_1(id int);
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
drop database itcast cascade ;
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;
13.2 Table 表 DDL操作
desc formatted itheima.t_all_hero_part;
show create table t_all_hero_part;
ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );
ALTER TABLE table_name SET FILEFORMAT file_format;
ALTER TABLE table_name SET LOCATION "new location";
CREATE TABLE test_change (a int, b int, c int);
ALTER TABLE test_change CHANGE a a1 INT;
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
ALTER TABLE test_change CHANGE c c1 INT FIRST;
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);
13.3 Partition分区 DDL操作
比较重要的是增加分区、删除分区操作
drop table if exists t_user_province;
create table t_user_province (
num int,
name string,
sex string,
age int,
dept string) partitioned by (province string);
load data local inpath '/root/hivedata/students.txt' into table t_user_province partition(province ="SH");
hadoop fs -mkdir /user/hive/warehouse/itheima.db/t_user_province/province=XM
hadoop fs -put students.txt /user/hive/warehouse/itheima.db/t_user_province/province=XM
ALTER TABLE t_user_province ADD PARTITION (province='XM') location
'/user/hive/warehouse/itheima.db/t_user_province/province=XM';
ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';
ALTER TABLE t_user_province PARTITION (province ="SH") RENAME TO PARTITION (province ="Shanghai");
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us');
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us') PURGE;
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET FILEFORMAT file_format;
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET LOCATION "new location";
扩展1：正则表达式
1）什么是正则表达式（规则表达式）
正则表达式(regular expression)描述了一种字符串匹配的模式（pattern）；
可以用来检查一个字符串中是否含有某种子串、将匹配的子串替换或者从某个串中取出符合某个条件的子串等。
runoo+b，可以匹配 runoob、runooob、runoooooob 等，+ 号代表前面的字符必须至少出现一次（1次或多次）。
runoo*b，可以匹配 runob、runoob、runoooooob 等，* 号代表前面的字符可以不出现，也可以出现一次或者多次（0次、或1次、或多次）。
colou?r 可以匹配 color 或者 colour，? 问号代表前面的字符最多只可以出现一次（0次、或1次）。
2）应用场景
（1）邮箱格式合法性校验
由于邮箱的基本格式为“名称@域名”，需要使用“^”匹配邮箱的开始部分，用“$”匹配邮箱结束部分以保证邮箱前后不能有其他字符，所以最终邮箱的正则表达式为：
??^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$
（2）手机号格式校验
（3）身份证格式校验
（4）多空白符识别
\\s+
3）重要的语法
^ 锚定行首，匹配输入字符串的开始位置
$ 锚定行尾，匹配输入字符串结束的位置
* 匹配前面的子表达式零次或多次。
. 匹配除换行符 \n 之外的任何单字符
+ 匹配前面的子表达式一次或多次。
? 匹配前面的子表达式零次或一次
一些有特殊含义的字符，如上面说的 runoo*b 中的 *，简单的说就是表示任何字符串的意思。
如果要查找字符串中的 * 符号，则需要对 * 进行转义，即在其前加一个 \，runo\*ob 匹配字符串 runo*ob。
许多特殊字符要求在试图匹配它们时特别对待。
若要匹配这些特殊字符，必须首先使字符"转义"，即，将反斜杠字符\ 放在它们前面。
\r 匹配一个回车符
\n 匹配一个换行符
\t 匹配一个制表符
\s 匹配一个空白符
扩展2：Hive多字节分隔符
（1）LazySimpleSerDe
01||zhangsan
02||lisi
03||wangwu
create table t_test1(id string,name string)
row format delimited fields terminated by '||';
+
| t_test1.id | t_test1.name |
+
| 01 | |
| 02 | |
| 03 | |
+
https:
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
（2）MultiDelimitSerDe
create table t_test2(id string,name string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="||");
Cannot validate serde:org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe
0: jdbc:hive2:
create table t_test2(id string,name string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="||");
（3）RegexSerDe
create table t_test3(id string,name string)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
'input.regex'='(.*)\\|\\|(.*)',
'output.format.string'='%1$s %2$s'
);
+
| t_test3.id | t_test3.name |
+
| 01 | zhangsan |
| 02 | lisi |
| 03 | wangwu |
+
表示 || 左右两边任意字符被抽取为一个字段
%1$s %2$s则分别表示表中的第一个字段、第二个地段
a、使用RegexSerDe类时，所有的字段必须为string
b、input.regex里面，以一个匹配组，表示一个字段
|