Hive–DQL

正则匹配

正则表达式(Regexp)介绍:

概述:
正确的, 符合特定规则的字符串.

Regular Expression

细节:

正则表达式不独属于任意的一种语言, 市场上大多数的语言都支持正则, 例如: Java, Python, HiveSQL, JavaScript等…

要求: 能用我们讲的规则, 看懂别人写的正则表达式(式子)即可.
正则规则:

^           代表: 正则开头
$           代表: 正则结尾
a           代表: 1个字符a
.           代表: 任意的1个字符
\.          代表: 取消.的特殊含义, 即只把它当做1个普通的 点(.)
\\          代表: 一个 \
[abc]       代表: a,b,c中的任意1个字符
[^abc]      代表: 除了a,b,c外的的任意1个字符
\d          代表: 任意的1个整数, 等价于 [0-9]
\w          代表: 任意的1个单词字符, 即: 数字, 字母, 下划线, 等价于 [0-9a-zA-Z_]
\S          代表: 任意的1个非空字符

?           代表: 至少0次, 至多1次
*           代表: 至少0次, 至多n次(无所谓)
+           代表: 至少1次, 至多n次(无所谓)
x{n}        代表: x恰好出现 n次(多一次, 少一次都不行)
x{n,}       代表: x至少出现n次, 至多无所谓.
x{n,m}      代表: x至少出现n次, 至多m次, 包括n和m

Pay Attention Please

-- 查找手机号符合：188****0*** (四种写法)
select * from orders where userPhone rlike '^188\\S{4}0\\d{3}$';
select * from orders where userPhone rlike '^188\\*{4}0\\d{3}$';
select * from orders where userPhone rlike '^188\\*{4}0[0-9][0-9][0-9]$';       -- 47条
select * from orders where userPhone rlike '^188\\*{4}0[0-9]{3}$';

-- \\d 代表一个\d匹配任意数字, \\* 代表一个特殊的*

联合查询

联合查询解释:
概述:
联合查询指的是 union 查询, 目的: 达到类似于拼接表的操作, 把多张表拼接到一起.
格式:
select ... from …
union all / distinct
select ... from …
细节:
1. 如果直接写union, 后边啥都不写, 默认是: union distinct
2. union all是合并, 但是不去重.
union distinct是合并, 但是去重.

要进行合并的表, 字段个数, 对应`的数据类型必须保持一致.

select * from stu
union all
select * from stu_insert;  -- 联合查询，将两张表放在一块，不去重(注意字段类型一定要匹配，要不会报错

select * from stu
union
select * from stu_insert;  -- 联合查询，这里union 后面没有东西，但是相当于union distinct

select * from stu
union distinct
select * from stu_insert limit 9;  -- 如果在最后面写group by，order by，limit则是作用于整个语句（结果

(select * from stu limit 10)
union
select * from stu_insert;  -- 如果在某个语句后面写group by，order by，limit则是作用于单个语句

随机抽样

随机抽样解释:
概述:
它表示我们通过 tablesample()函数实现, 从大表中抽取出一定的样本数据.
格式:
tablesample(bucket x out of y on 列名或者 rand());
细节:
1. y表示分成几个桶, 即: 桶的个数.
2. x表示从桶内抽取第x份(条)--将表按照男女分成两个组，第一组必然是女生，第二组必定全部是男
3. 根据列名抽取, 相当于把该列当做了分桶字段抽取, 列名一致的情况下, 其它条件不变(桶的数据等...), 每次抽取到的数据都一样.
4. rand()表示随机数, 即随机抽取, 每次采集到的数据都不一样.
5. x 不能比 y 大.

1
2

select * from stu tablesample ( bucket 2 out of 2 on gender);  -- 按照gender将stu表分成两个桶，取第二份
select * from stu tablesample ( bucket 1 out of 2 on rand(2));  -- 随机(以2为种子)将stu表分成两个桶（份，取其中第一份，

虚拟列

虚拟列介绍:
概述:
属于Hive内置的, 数据本身的参数, 辅助我们进行查询的.
分类:
INPUT__FILE__NAME 显示数据行所在的数据文件
BLOCK__OFFSET__INSIDE__FILE 显示数据行所在的数据文件中的行偏移量(即: 起始索引)
95001,李勇,男,20,CS 行偏移量(即: 起始索引): 0
95002,刘晨,女,19,IS 行偏移量(即: 起始索引): 23
95003,王敏,女,22,MA 行偏移量(即: 起始索引): 46
ROW__OFFSET__INSIDE__BLOCK 显示数据所在的HDFS块的偏移量, 该虚拟列必须要设置才能用, 即: set hive.exec.rowoffset=true
显示数据行所在的 Block块的编号(从 0 开始)
细节:

       1. `1个中文, gbk码表占2个字节, utf-8码表占3个字节`
       2. `row__offset__inside__block`
       3. 就三个内置函数掌握就完事了

select id,INPUT__FILE__NAME from stu;  -- 查看数据行所在的数据文件(file)

select BLOCK__OFFSET__INSIDE__FILE,id from stu;  -- 显示数据行所在文件的偏移量(offset)

set hive.exec.rowoffset=true;  -- 设置开启虚拟列
select *,ROW__OFFSET__INSIDE__BLOCK from stu;  -- 显示数据行所在hdfs块的偏移量，必须设置，而且会报红

Hive函数

Hive函数介绍:

最初Hive函数分为内置函数和用户自定义函数两大类, 其中用户自定义函数又被分为3类, 分别是:
内置函数: 属于Hive自带的.
用户自定义函数:
UDF: 全称叫 User Defined Functions, 普通函数, 即: 一进一出.
例如: select * from stu;
UDAF: 全称叫 User Defined Aggregation Functions, 聚合函数, 即: 多进一出.
例如: select count(id) from stu;
UDTF: 全称叫 User Defined Table-Generating Functions, 表生成函数, 即: 一进多出.
例如: select explode(array(11, 22, 33));
后来发现用 UDF, UDAF, UDTF来划分Hive函数实在是太方便了, 于是提出了1个词, 叫: 函数标准扩大化, 即:
UDF, UDAF, UDTF本来是形容用户自定义函数的, 现在, hive中的函数没有内置函数和自定义函数之分了, 取而代之的是: UDF, UDAF, UDTF

Hive官网, 函数解释:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-MathematicalFunctions

Hive函数,必须要掌握的.
show functions;

– 查看hive所有内置函数, 289个, 其中, 符号也是Hive函数一种, 只不过函数名是符号而已.
describe function extended 函数名;

– 查看函数详细信息.
split() substr() concat_ws() date_add() datediff() year() round() rand() cast() coalesce() case...when... get_json_object() explode() collect_list() row_number() rank() dense_rank() ntile() lag()

函数忘了咋用？不存在的

show functions ;  -- 查看所有函数

describe function substr;  -- 简单描述函数的信息

describe function extended substr;  -- 详细描述函数的信息（还有例子

字符串相关函数

select substr('abs123',1,3);  -- 提取函数的子串，（字符串，起始位置，子串个数) 效果和substring
select split('12,34,45',',');  -- 将字符串以,进行切割
select concat('12','nihao');  -- 将字符串进行拼接（使用默认方式
select concat_ws('-','12','44');  -- 将字符串以自定义字符方式进行拼接(第一个参数为拼接符
select length('agfa');  -- 获取字符串的长度
select upper('aaaAA');  -- 将字符串转大写
select lower('HAGu');  -- 将字符串转小写
select trim('  hg  jhh   ');  -- 移除字符串的首尾空格（字符串中间的空格不用管
select regexp_replace('100-200','\\d+','你好');  -- 将字符串中的数字替换为指定字符
select parse_url('http://www.itcast.cn/path/p1.php?query=1','HOST');  -- 提取url的域名
select parse_url('http://www.itcast.cn/path/p1.php?query=1','QUERY');  -- 提取url的请求
select parse_url('http://www.itcast.cn/path/p1.php?query=1','PATH');  -- 提取url的路径
select parse_url('http://www.itcast.cn/path/p1.php?username=admin01&password=pwd111','QUERY','username')  --提取url中键username的值
select get_json_object('{"name":"小威","age":"18"}','$.name');  -- 获取json字符串中某个对象的值
select get_json_object('[{"name":"小威","age":"18"},{"name":"小李","age":"18"}]','$.[0].name');  -- 获取某个数组中字典某个键的值

日期相关函数

这里有个借助日期函数判断闰年的比较重要嗷

select current_date();  -- 获取当前的日期
select unix_timestamp();  -- 获取当前时间戳
select unix_timestamp('2023-01-23 18:09:23');  -- 获取给定日期的时间戳
select unix_timestamp('2022/01/12','YYYY/mm/dd');  -- 获取给定日期的时间戳,并且指定格式
select from_unixtime(1684500647);  -- 根据指定时间戳获取日期，注意是再加上8小时，因为是东八区时间
select date_add('2023-05-19',2);  -- 日期加2,实际上写-2就是日期减二，也就是说date_sub用不到了
select date_sub('2023-05-19',2);  -- 日期减掉2
select datediff('2023-05-19','2023-05-20');  -- 日期比较 前-后
-- 判断该年是否为闰年 （提示：如果该年是闰年的话，二月有29天，如果不是闰年2月有28天
-- 数学中判断该年是否为闰年的方式是 能被4整除但不能被100整除的年份是闰年，或者能被400整除也是闰年
select dayofmonth(date_add('2022-03-01',-1));  -- 2022年3月1日的前一天是28，所以2022年不是闰年

数字相关函数

select rand();  -- 产生一个0.0-1.0之间的随机数，如果不指定种子seed则随机数一直会不确定
select round('4.1');  -- 取整函数，四舍五入（四舍五入的原理是什么？加0.5取地板数
select floor(4.3+0.5);  -- 取地板数
select ceil(3.4);  -- 取天花板函数
select abs(-23);  -- 取绝对值

非空检验相关函数

select if(5>3,'郑州','信阳');  -- if条件判断（条件，如果满足，如果不满足
select isnull(null);  -- 非空校验
select isnull('nihao');  -- 判断不为空，结果为false
select isnotnull('nihao'); -- 跟isnull判断结果相反
select nvl(null,'nihao');  -- 判断第一个值是否为空，是空就用第二个，且只能传入2个参数
select coalesce(null,null,'nihao') ; -- coalesce 是合并的意思,从后到前找到第一个不为空的值
select
    case 3
        when 5 then '周五'
        when 3 then '周三'
    end as week;  -- case when 条件判断

杂项函数

-- 类型转换相关.
select cast(10.3 as int);       -- 10
select cast(10 as string);      -- '10'
select cast('12.3' as double);  -- 12.3
select cast('12.3a' as double);  -- null

-- 获取哈希值.
select hash('张三');          -- -838675700
select mask_hash('张三');     -- 1d841bc0ee98309cb7916670b7f0fdef5f4c35150711a41405ef3633b56322cf

-- mask()函数, 脱敏函数, 顺序是: 大写字母, 小写字母, 数字
select mask('abc123ABC');                   -- xxxnnnXXX, 默认: 大写字母X, 小写字母x, 数字n
select mask('abc123ABC', '大','小', '*');     -- xxxnnn***, 指定: 大写字母 大, 小写字母 小, 数字*

-- 对数组元素排序, 默认: 升序.
select sort_array(array(11, 33, 55, 22));       -- [11,22,33,55]

行列转换入门Expolde函数

select explode(champion_year) from the_nba_championship;  -- 爆炸函数，将数组元素炸开
select * from the_nba_championship;
select
       team_name,
       b1.cham_year
from the_nba_championship a1 lateral view explode(champion_year) b1 as cham_year; -- 爆炸函数和侧视图结合

行列转换之: 行转列

select * from row2col2;
select
       col1,
       col2,
       collect_list(col3)  -- collect_list 采集到的数据是数组形式
from row2col2 group by col1,col2;  -- collect_list()采集数据, 可重复, 有序collect_set()采集数据, 唯一, 无序.

select concat_ws('-',1,2,3); -- 这里会报错，因为concat_ws()函数只能拼接字符串
select concat_ws('-',cast(1 as string),cast(2 as string));  -- 可以拼接
select concat_ws('-',array('1','2'));  -- 将数据写成数组形式也可以拼接
select
    col2,
    col1,
    concat_ws('-',collect_list(cast(col3 as string)))
from row2col2 group by col2, col1;  -- 多敲多练，不会也得会

行列转换之: 列转行

create table col2row2(
    col1 string,
    col2 string,
    col3 string
)row format delimited fields terminated by '\t';
select * from col2row2;

select
       a1.col1,
       b1.col_,
       a1.col2
from col2row2 a1 lateral view explode(split(col3,',')) b1 as col_;  -- 注意explode函数只接受map或者array类型的输入

处理json字符串

select get_json_object('{"name":"杨过"}', '$.name');

create table test1_json(
    json string
);
select * from test1_json;
select
    get_json_object(json,'$.device') as device,
    get_json_object(json,'$.deviceType') as deviceType,
    get_json_object(json,'$.signal') as signal,
    get_json_object(json,'$.time') as `time`
from test1_json;  -- 处理json字符串，get_json_object只会处理单个json列

select
       json_tuple(json,'device','deviceType','signal','time')
           as (device, deviceType, signal, `time`)
from test1_json;  -- 写json_tuple 将json关键字扔进去，然后写需要的字段，json_tuple函数会直接提取

select
       device, deviceType, signal, `time`
from test1_json
    lateral view json_tuple(json,'device','deviceType','signal','time') lv
    as device, deviceType, signal, `time`;
-- 通过侧视图将json_tuple 得到的表临时存储为视图，然后定义各个字段的名称，并查询该字段，是上一种写法的变形

create table test2_json(
    device string,
    deviceType string,
    signal string,
    `time` string
)row format serde 'org.apache.hive.hcatalog.data.JsonSerDe' ;
-- 如果不写serde则默认为lazysampleserde处理方式，（row format delimited fields terminated by
select * from test2_json;

CTE表达式

CTE表达式介绍:

概述:
全称叫 Common Table Expression, 公共表表达式, 用来(临时)存储表结果的, 后续可以重复使用.
格式:
with CTE表达式的别名 as ( 被CTE所存储的内容, 即: SQL查询语句 ) select ... from cte表达式别名;

-- 1. CTE表达式入门.
with t1 as (
    select * from stu
)
select * from t1;

-- 2. from风格.
with t1 as (
    select * from stu
)
from t1 select name, age;

-- 3. 链式写法(链式编程)
with t1 as (select * from stu),
     t2 as (select * from t1 where id > 95010),
     t3 as (select id, name, gender,age from t2 where id > 95010)
select name, gender from t3;

-- 4. CTE表达式结合 union 使用.
with t1 as (
    select * from stu
)
select * from t1
union  all      -- 合并, 不去重.
select * from t1 limit 3;       -- 44条 => 3条

-- 5. 用表 把 CTE的结果 永久存储.
create table hg1 as
with t1 as (
    select * from stu
)
select id, name, age from t1;

select * from hg1;

-- 6. 用视图 把 CTE的结果 "永久"存储.
create view hg2 as
with t1 as (
    select * from stu
)
select id, name, age from t1;

select * from hg2;