hive 字符串模糊匹配多个字段

转载

footballboy 2024-09-04 08:04:37

文章标签 hive 字符串模糊匹配多个字段 hive sql mapreduce json 文章分类 Hive 大数据

1、内置运算符

查看函数

show functions;
desc functions extended +;

关系运算符

# 大小比较
select 1>1;
select 1=1;
select 1>=1;


# 空值判断
select 'asd' is null;
select 'asd' is not null;


# 模糊查询
select 'itcast' like 'it____'; # _代表匹配一个字符
select 'itcast' like 'it%'; # _代表匹配任意个数字符


# 正则查询
select 'itcast' rlike 'it.';
select 'itcast' regexp 'it.';

算数运算符

# 加减乘除
select 1+1;
select 1/2;
select 1/0;


# 取整取余
select 3 div 2;
select 4 % 2;


# 位运算
0 0000 
1 0001
2 0010
3 0011
4 0100  
5 0101
6 0110
select 4 & 6; 0100 * 0110     0100
select 4 | 6  0100 | 0110  只要有一个位数是1 该位置上的数据就是1

逻辑运算符

# 与或非
select 1>1 and 1=1;  and 所有条件成立
select 1>1 or 1=1;  or 一个条件成则返回true
select  !1>1; 取反


# 在范围内
select 1 in (2,3);
select 1 not in  (2,3);

2、内置函数

字符串函数

-- 字符串长度函数
select length('asd');
-- 字符串反转函数
select reverse('asd');
-- 字符串连接函数
select concat('asd','zxc');
-- 分隔符字符串连接函数
select concat_ws('-','asd','zxc','aaa')
-- 字符串截取函数
select substr('itcast',2,2); -- 第一个截取字符的位置 第二个截取字符的个数
select substr('itcast',-2)
-- 字符串转大写函数
select upper('asd');
select lower('ASD');
-- 去空格函数
select trim(' aaa ');
select ltrim('  aaaa   ');
select rtrim('  aaaa   ');
-- 正则表达式替换函数
select regexp_replace('asd123','(\\w+)\d','aaa')
-- URL解析函数
select parse_url('http://www.baidu.com','HOST');
-- 割字符串函数
select split('asd,aaa',',')

时间日期函数

-- 获取当前日期
select `current_date`();
-- 获取当前时间戳
select `current_timestamp`();
-- UNIX时间戳转日期函数
select from_unixtime(1634528834);
-- 获取当前UNIX时间戳函数
select unix_timestamp();
-- 日期转UNIX时间戳函数
select unix_timestamp('2021-10-10 10:10:10');
-- 抽取日期函数
select to_date('2021-10-10 10:10:10');
-- 日期转年函数
select year('2021-10-10 10:10:10');
-- 日期比较函数
select datediff('2021-10-10 10:10:10','2021-10-12 10:10:10');
-- 日期增加函数
select date_add('2021-10-10 10:10:10',5);
select date_sub('2021-10-10 10:10:10',5);

数学函数

-- 取整函数
select round(3.1415);
-- 指定精度取整函数
select round(3.1415，2);
-- 向下取整函数
select `floor`(-1.1);
-- 向上取整函数
select ceil(1.1);
-- 进制转换函数
select conv()
-- 取随机数函数
select round(rand(3)*100);
-- 绝对值函数
select abs(-10)

集合函数

-- 集合元素数
select size(`array`('a','n','b'));
-- 取map集合keys函数
select map_keys(`map`('name','python','age',19));
-- 取map集合values函数
select map_values(`map`('name','python','age',19));
-- 判断数组是否包含指定元素
select array_contains(`array`(1,2,3),4);
select 1 in (1,2,3);
-- 数组排序函数
select sort_array(`array`(1,5,3,2));
select sort_array(`array`('g','a','f','c'));

条件函数

-- if条件判断
if(boolean testCondition, T valueTrue, T valueFalseOrNull)
-- 空判断函数
isnull( a )
-- 非空判断函数
isnotnull ( a )
-- 空值转换函数
nvl(T value, T default_value)
-- 非空查找函数
select coalesce(null,null,2);
-- 条件转换函数
 CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'man' else 'women' end from student limit 3;

--nullif( a, b ):
-- 果a = b，则返回NULL；否则返回NULL。否则返回一个
select nullif(11,11);
select nullif(11,12);

--assert_true(condition)
--如果'condition'不为真，则引发异常，否则返回null
SELECT assert_true(11 >= 0);
SELECT assert_true(-1 >= 0);

类型转换

select cast('12' as int);

数据脱敏

select mask('asd123ZXC');
select mask('asd123ZXC','*','@','#');
-- 对前n个进行脱敏替换
select mask_first_n('asd123',3);
select mask_last_n('itcastpython',3);
-- 除了前n个字符，其余进行掩码处理
select mask_show_first_n('asdzxc',2);
select mask_show_last_n('qwertyu',2);
-- 返回字符串的hash编码。xxxxxxxxxx select mask('asd123ZXC');select mask('asd123ZXC','*','@','#');--对前n个进行脱敏替换select mask_first_n('asd123',3);select mask_last_n('itcastpython',3);--除了前n个字符，其余进行掩码处理select mask_show_first_n('asdzxc',2);select mask_show_last_n('qwertyu',2);--返回字符串的hash编码。-- 脱敏--对前n个进行脱敏替换--除了前n个字符，其余进行掩码处理--返回字符串的hash编码。

其他

-- 反射函数
select reflect("java.lang.Math","max",11,22);
-- 取哈希值函数
select hash("allen");
-- SHA-1加密
select sha1("allen");
--SHA-2 家族算法加密
select sha2("allen",224);
select sha2("allen",512);
-- MD5加密
select md5("allen");
--crc32加密:
select crc32("allen");

3、自定义函数（UDF）

UDF 普通函数输入一行输出一行
UDAF 聚合函数输入多行输出一行
UDTF 输入一行输出多行
explode（UDTF

-- NBA总冠军球队
create table the_nba_championship(
    team_name string,
    champion_year array<string>
) row format delimited
fields terminated by ','
collection items terminated by '|';

-- 使用explode函数对champion_year进行拆分

lateral View
行列转换 concat
列比较

select count(case when gender='男' then id end) as '男'，count(case when gender='女' then id end) as '女' from students

行比较

select gender,count(id) as count from student group by gender

行转列

create table row2col2(
   col1 string,
   col2 string,
   col3 int
)row format delimited fields terminated by '\t';

列转行

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

json数据处理

-- 单个字段
create table tb_json_test1 (
  json string
);

get_json_object(json,'$.deviceType')

-- 多个字段
create table tb_json_test2 (
   device string,
   deviceType string,
   signal double,
   `time` string
 )
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;

 json_tuple(json,'device','deviceType')


--serder
create table tb_json_test2 (
   device string,
   deviceType string,
   signal double,
   `time` string
 )
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;


-- 拓展--xml数据处理
desc function extended xpath;
SELECT xpath('<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>', 'a/b/text()')

4、窗口函数

聚合函数、分析函数、排序函数 over(partition by 字段1... order by 字段1... rows 范围)

建表

---建表并且加载数据
create table website_pv_info(
   cookieid string,
   createtime string,   --day
   pv int
) row format delimited
fields terminated by ',';

create table website_url_info (
    cookieid string,
    createtime string,  --访问时间
    url string       --访问页面
) row format delimited
fields terminated by ','

求出每个用户总pv数sum+group by普通常规聚合操作

select cookieid,sum(pv) from website_pv_info group by cookieid;

求出网站总的pv数所有用户所有访问加起来
求出每个用户总pv数

select cookieid,sum(pv) over(partition by cookieid) from website_pv_info;

求出每个用户截止到当天，累积的总pv数

-- 求出每个用户截止到当天，累积的总pv数  between unbounded preceding and current row 等价  unbounded preceding
-- order by 默认有一个计算范围 unbounded preceding
select cookieid,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row ) from website_pv_info;
select cookieid,sum(pv) over(partition by cookieid order by createtime rows unbounded preceding) from website_pv_info;
select cookieid,sum(pv) over(partition by cookieid order by createtime ) from website_pv_info;

自定义窗口范围

unbounded preceding

找出每个用户访问pv最多的Top3重复并列的不考虑

-- 排序  rank 遇到重复会生成不连续的序号  dense_rank连续序号 row_number生成行号
select *,rank() over (partition by cookieid order by pv desc ) as rank_num from website_pv_info;
select *,dense_rank()  over (partition by cookieid order by pv desc ) as rank_num from website_pv_info;
select *,row_number()  over (partition by cookieid order by pv desc ) as rank_num from website_pv_info;
select *,ntile(3)  over (partition by cookieid order by pv desc ) as rank_num from website_pv_info;


with tb1 as ( select *,dense_rank()  over (partition by cookieid order by pv desc ) as rank_num from website_pv_info )
from tb1
select *
 where rank_num <= 3;

统计每个用户pv数最多的前3分之1天

with tb1 as (select *,ntile(3)  over (partition by cookieid order by pv desc ) as rank_num from website_pv_info)
from tb1
select *
 where rank_num = 1;

分析函数

select *,lag(pv)  over (partition by cookieid order by pv desc ) as rank_num from website_pv_info;
-- 向下取值
select *,lead(pv)  over (partition by cookieid order by pv desc ) as rank_num from website_pv_info;
-- 取第一个值
select *,first_value(pv)  over (partition by cookieid order by pv desc ) as rank_num from website_pv_info;
-- 去最后一个值 注意取值范围 因为orderby 所用取值范围需要增加向下的范围
select *,last_value(pv)  over (partition by cookieid order by pv desc rows between unbounded preceding and unbounded following) as rank_num from website_pv_info;


select sum(pv) sum(sum(pv)) over() from website_pv_info

5、数据压缩

压缩算法
map压缩

1）开启hive中间传输数据压缩功能
set hive.exec.compress.intermediate=true;
2）开启mapreduce中map输出压缩功能
set mapreduce.map.output.compress=true;
3）设置mapreduce中map输出数据的压缩方式
Set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;

reduce压缩

1）开启hive最终输出数据压缩功能
set hive.exec.compress.output=true;
2）开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
3）设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
4）设置mapreduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type=BLOCK;

6、数据存储格式

行存储和列存储
TextFILE
ORC
PAROUET
格式对比

TEXTfile

create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;

orc

create table log_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc ;

parquet

create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS PARQUET ;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。