Hive-DQL(续)及函数
Hive--DQL
正则匹配
概述: 正确的, 符合特定规则的字符串.
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 大.
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;
