2022-02-05大数据学习日志——Hadoop离线阶段——Hive SQL DDL

学习目标

#掌握HQL DDL建表语句
	理解Hive SerDe机制、分隔符语法
	掌握内外部表、分区表、分桶表创建使用
#理解HQL DDL其他语句
	修改、删除

内容大纲

#1、HQL DDL 数据定义语言 针对表的
 核心：建表语句   直接决定了表和文件之间能否映射成功
 	数据类型
 	SerDe序列化机制
 	分隔符语法
 	内部表、外部表
 	数据存储路径
 	分区表
 	分桶表
  alter修改表

01_Apache Hive DDL 概念与语法树介绍

蓝色字体是建表语法的关键字，用于指定某些功能。
[ ]中括号的语法表示可选。
|表示使用的时候，左右语法二选一。
建表语句中的语法顺序要和语法树中顺序保持一致。

02_Apache Hive DDL 建表语句表存在忽略异常

IF NOT EXISTS

建表的时候，如果表名已经存在，默认会报错，通过IF NOT EXISTS关键字可以忽略异常。

--第一次创建表
0: jdbc:hive2://node1:10000> create table t_1(id int,name string,age int);
--再次执行
0: jdbc:hive2://node1:10000> create table t_1(id int,name string,age int);
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)
--Error while processing statement 执行期间的错误 往往就是逻辑错误

--加上if not exists忽略异常
0: jdbc:hive2://node1:10000> create table if not exists t_1(id int,name string,age int);

0: jdbc:hive2://node1:10000> creatf table t_1(id int,name string,age int);
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://node1:10000> 
--Error while compiling statement 编译期间的错误  SQL语法问题

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;

--上传文件到表对应的HDFS目录下
[root@node1 hivedata]# hadoop fs -put archer.txt /user/hive/warehouse/itheima.db/t_archer

[root@node1 hiedata]# pwd
/root/hivedata

04_Apache Hive DDL 建表语句 SerDe机制、分隔符指定语法

机制：SerDe（Serializer and Deserializer）序列化机制

Hive使用SerDe机制读写HDFS上文件

读文件

HDFS files --> InputFileFormat --> --> Deserializer --> Row object

#1、使用InputFileFormat（默认实现TextInputFormat）读取hdfs上文件
    一行一行读取数据，返回键值对类型
#2、返回，其中数据存储在value中

#3、使用Deserializer反序列化动作读取value  解析成为对象（Row object）
    默认的序列化类LazysimpleSerDe

写文件

Row object --> Serializer --> --> OutputFileFormat --> HDFS files

分隔符指定语法

语法格式

ROW FORMAT DELIMITED | SERDE

ROW FORMAT DELIMITED  表示使用LazySimpleSerDe类进行序列化解析数据
 
ROW FORMAT SERDE      表示使用其他SerDe类进行序列化解析数据

ROW FORMAT DELIMITED具体的子语法

row format delimited
[fields terminated by char]  #指定字段之间的分隔符
[collection items terminated by char]  #指定集合元素之间的分隔符
[map keys terminated by char]         #指定map类型kv之间的分隔符
[lines terminated by char]            #指定换行符

课堂练习sql

create table t_hot_skin_1(
id int,
name string,
win_rate int,
skin_price string
)
row format delimited
fields terminated by ',';


create table t_hot_skin_2(
id int,
name string,
win_rate int,
skin_price map
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

05_Apache Hive DDL 建表语句默认分隔符

默认分隔符

Hive在建表的时候，如果没有row format语法，则该表使用01默认分隔符进行字段分割；如果此时文件中的数据字段之间的分隔符也是01 ，那么就可以直接映射成功。针对默认分隔符，其是一个不可见分隔符，在代码层面是01表示在vim编辑器中，连续输入ctrl+v 、ctrl+a；在实际工作中，Hive最喜欢的就是01分隔符，在清洗数据的时候，有意识的把数据之间的分隔符指定为01;

栗子

--建表
create table t_hot_hero_skin_price(
      id int,
      name string,
      win_rate int,
      skin_price map
)
row format delimited
fields terminated by ',' --字段之间分隔符
collection items terminated by '-'  --集合元素之间分隔符
map keys terminated by ':'; --集合元素kv之间分隔符;

--上传数据
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;


--有点想法： 就把map数据类型当成字符串映射如何？
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
); --没有指定row format语句 此时采用的是默认的01作为字段的分隔符

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 ',';

--创建外部表 关键字external
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

--好像没啥区别 都能映射成功 数据也都在HDFS上

--针对内部表、外部表 进行drop删除操作
drop table student_inner;  --内部表在删除的时候 元数据和数据都会被删除
drop table student_external; --外部表在删除的时候 只删除元数据  而HDFS上的数据文件不会动

外部表有什么好处

最大的好处是防止误操作删除表的时候 把表的数据一起删除。

可以通过命令去查询表的元数据信息获取表的类型

desc formatted table_name;

MANAGED_TABLE     内部表、受控表   inner_table 
EXTERNAL_TABLE    外部表

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

--在建表的时候 可以使用location关键字指定表的路径在HDFS任意位置
create table t_team_ace_player_location(
 id int,
 team_name string,
 ace_player_name string)
 location '/aaa'; --使用location关键字指定本张表数据在hdfs上的存储路径
 
--此时再上传数据 就必须上传到指定的目录下 否则就解析映射失败了

在实际开发中，最好集中维护管理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;


--查询role_main主要定位是射手并且hp_max最大生命大于6000的有几个

select count(*) from t_all_hero where role_main="archer" and hp_max >6000;

--思考：上述查询sql底层应该如何去查询数据？ 要不要全表扫描？

--问题：要进行过滤 就需要全表扫描 不全表扫描呢 又得不出正确结果？性能如何优化呢？
--优化要求  如何才能够减少全表扫描 而结果又正确。

分区表的创建

--经过大脑分析 我们认为应该根据角色主定位进行分区 所以分区的字段就是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_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; --注意hive3已经默认开启了
set hive.exec.dynamic.partition.mode=nonstrict;

--模式分为strict严格模式  nonstrict非严格模式
严格模式要求 分区字段中至少有一个分区是静态分区。

动态分区加载数据

insert + select

插入的数据来自于后面的查询语句返回的结果。

查询返回的内容，其字段类型、顺序、个数要和待插入的表保持一致。

--创建一张新的分区表 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_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

分区表是一种优化表，建表的时候可以不使用，但是，当创建分区表之后，使用分区字段查询可以减少全表扫描，提高查询的效率。

企业中常用的分区字段

地域维度：省，市时间维度：day,month,year 10.2 多重分区表

分区表支持基于多个字段进行分区

partitioned by(字段1 ，字段2....)

多个分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区；从底层来说就是文件夹下面继续划分子文件夹；

4常见的多分区就是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
--根据xxx字段把数据分成N桶
--根据表中的字段把数据文件成为N个部分

t_user(id int,name string);
--1、根据谁分？
 CLUSTERED BY xxx ；  xxx必须是表中的字段
--2、分成几桶？
 N BUCKETS   ；N的值就是分桶的个数
--3、分桶的规则？
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; --分桶的字段一定要是表中已经存在的字段

--根据state州分为5桶 每个桶内根据cases确诊病例数倒序排序
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;--指定每个分桶内部根据 cases倒序排序

11.3 分桶表的数据加载

--step1:开启分桶的功能 从Hive2.0开始不再需要设置
set hive.enforce.bucketing=true;

--step2:把源数据加载到普通hive表中
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 ",";

--将源数据上传到HDFS，t_usa_covid19表对应的路径下
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/itheima.db/t_usa_covid19

--step3:使用insert+select语法将数据加载到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19;

select * from t_usa_covid19_bucket;

11.4 分桶表的使用

--基于分桶字段state查询来自于New York州的数据
--不再需要进行全表扫描过滤
--根据分桶的规则hash_function(New York) mod 5计算出分桶编号
--查询指定分桶里面的数据 就可以找出结果  此时是分桶扫描而不是全表扫描
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);

--删除数据库
--注意 CASCADE关键字慎重使用
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;

--1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;
--2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
--更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
--3、更改SerDe属性
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' = ',');
--移除SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

--4、更改表的文件存储格式 该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进行。
ALTER TABLE table_name  SET FILEFORMAT file_format;
--5、更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";

--6、更改列名称/类型/位置/注释
CREATE TABLE test_change (a int, b int, c int);
// First change column a's name to a1.
ALTER TABLE test_change CHANGE a a1 INT;
// Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table's structure is:  b int, a2 string, c int.
// Then change column c's name to c1, and put it as the first column.
ALTER TABLE test_change CHANGE c c1 INT FIRST;
// The new table's structure is:  c1 int, b int, a2 string.
// Add a comment to column a1
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

--7、添加/替换列
--使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。
--REPLACE COLUMNS 将删除所有现有列，并添加新的列集。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);

13.3 Partition分区 DDL操作

比较重要的是增加分区、删除分区操作

--1、增加分区
--step1: 创建表 手动加载分区数据
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");


--step2:手动创建分区的文件夹 且手动上传文件到分区中 绕开了hive操作  发现hive无法识别新分区
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


--step3：修改hive的分区，添加一个分区元数据
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';


--2、重命名分区
ALTER TABLE t_user_province PARTITION (province ="SH") RENAME TO PARTITION (province ="Shanghai");

--3、删除分区
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; --直接删除数据 不进垃圾桶 有点像skipTrash

--4、修复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];
--详细使用见课件资料

--5、修改分区
--更改分区文件存储格式
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）重要的语法

#1、基础语法
	^ 锚定行首，匹配输入字符串的开始位置
	$ 锚定行尾，匹配输入字符串结束的位置
	
	* 匹配前面的子表达式零次或多次。
	. 匹配除换行符 n 之外的任何单字符
	+ 匹配前面的子表达式一次或多次。
	? 匹配前面的子表达式零次或一次
	

#2、转义语法
	一些有特殊含义的字符，如上面说的 runoo*b 中的 *，简单的说就是表示任何字符串的意思。
	如果要查找字符串中的 * 符号，则需要对 * 进行转义，即在其前加一个 ，runo*ob 匹配字符串 runo*ob。

	许多特殊字符要求在试图匹配它们时特别对待。
	若要匹配这些特殊字符，必须首先使字符"转义"，即，将反斜杠字符 放在它们前面。
	
#3、非打印字符
	r 匹配一个回车符
	n 匹配一个换行符
	t 匹配一个制表符
	s 匹配一个空白符

扩展2：Hive多字节分隔符

（1）LazySimpleSerDe

--结构化数据如下
01||zhangsan
02||lisi
03||wangwu

--在hive中建表映射成功该数据
create table t_test1(id string,name string)
row format delimited fields terminated by '||';

--上传文件到表对应的HDFS目录下
--select查询验证
+-------------+---------------+
| t_test1.id  | t_test1.name  |
+-------------+---------------+
| 01          |               |
| 02          |               |
| 03          |               |
+-------------+---------------+

--原因
https://issues.apache.org/jira/browse/HIVE-237

--官方语法树中  char  只支持单字节分隔符
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]   -- (Note: Available in Hive 0.13 and later)
  | 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

---下面的是正确的操作

--需要在hive客户端中添加MultiDelimitSerDe的jar包
0: jdbc:hive2://node1:10000> add jar /export/server/apache-hive-3.1.2-bin/lib/hive-contrib-3.1.2.jar;

--再次执行建表语句  注意类的路径  官方也是会挖坑
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        |
+-------------+---------------+


--input.regex：输入的正则表达式 
	表示 || 左右两边任意字符被抽取为一个字段
--output.format.string：输出的正则表达式
	%1$s  %2$s则分别表示表中的第一个字段、第二个地段
--注意事项：
	a、使用RegexSerDe类时，所有的字段必须为string
	b、input.regex里面，以一个匹配组，表示一个字段

2022-02-05大数据学习日志——Hadoop离线阶段——Hive SQL DDL

大数据系统相关栏目本月热门文章