hive学习小结

hive学习小结

目录

• 一、函数学习
• • 1、concat拼接函数
  • 2、concat_ws()
  • 3、FROM_UNIXTIME函数
  • 4、UNIX_TIMESTAMP()函数
  • 5、NVL()函数
  • 6、group by分组
  • 7、collect_set函数
  • 8、size函数
  • 9、sumif函数的语法格式
• 二、SQL正则表达式
• • 1、元字符
  • 2、正则表达函数
  • • 2.1 `regexp_like(x,pattern[,match_option])`
    • 2.2 regexp_instr(x,pattern[,start[,occurrence[,return_option[, match_option]]]])
    • 2.3 REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])
    • 2.4 REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])
• 三、hive数仓数据重复性检查
• • 1、如何快速确认某一字段是否有重复值
  • • 1.1 cnt 没有去重，order_cnt 去重， 如果得到结果相同，则表是没有重复
    • 1.2 根据待确认字段进行分组，订单号不能有大于1的情况，就代表没有重复
    • 1.3 如果数据有重复，如何查找出来重复的字段信息呢？
  • 2、如何去除重复值
  • • 2.1 hive表中某些数据是整行的重复，而且字段过多时：
    • 2.2 两行数据只有一个字段不一样：
    • 2.3 当表中字段不多，而且数据是某些字段重复，可通过row_number()：
    • 2.4 在某些情况中我们不是需要全部字段，而是仅仅过滤出某些不重复的字段：
  • 3、row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
• 四、常见Shell快捷操作

一、函数学习

1、concat拼接函数

将多个字符串连接成一个字符串。

concat(str1, str2,…) 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

但是输入sql语句麻烦了许多，三个字段需要输入两次逗号，如果10个字段，要输入九次逗号…麻烦死了啦，有没有什么简便方法呢？——于是可以指定参数之间的分隔符的concat_ws()来了！！！

2、concat_ws()

和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）

concat_ws(separator, str1, str2, …)
说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

3、FROM_UNIXTIME函数

FROM_UNIXTIME(unix_timestamp,format)

1. unix_timestamp为需要处理的时间戳(该参数是Unix 时间戳),可以是字段名,也可以直接是Unix 时间戳字符串。
2. format为需要转换的格式
   mysql官方手册的解释为：返回’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS 格式值的unix_timestamp参数表示，具体格式取决于该函数是否用在字符串中或是数字语境中。若format 已经给出，则结果的格式是根据format 字符串而定。format的格式和日常的编程语言的时间格式是保持一致的。

mysql> select FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S');
+-----------------------------------------------+
| FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S') |
+-----------------------------------------------+
| 2012-08-14 22:28:35                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

4、UNIX_TIMESTAMP()函数

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

• 参数调用，则返回一个 Unix timestamp (‘1970-01-01 00:00:00’ GMT 之后的秒数) 作为无符号整数。
• 若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以’1970-01-01 00:00:00’ GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。

mysql> select UNIX_TIMESTAMP('2012-09-04 18:17:23');
+---------------------------------------+
| UNIX_TIMESTAMP('2012-09-04 18:17:23') |
+---------------------------------------+
|                            1346753843 |
+---------------------------------------+
1 row in set (0.00 sec)

实际应用中，我们可以将格式转换为毫秒的与数据库中进行比较，也可以将数据库的Int格式转换为普通格式进行比较。

例如：查询所有创建日期大于2012-09-03 18:00:00之后的博客数据
方法一：将日期转换为int比较

select * from blog where createdTime > UNIX_TIMESTAMP('2012-09-03 18:00:00' );

方法二：将int转换为时间格式比较

select * from blog where FROM_UNIXTIME(createdTime, '%Y-%m-%d %H:%i:%S') > '2012-09-03 18:00:00';

5、NVL()函数

• hive中的nvl函数为判断是否为空值，nvl叫做空值转换函数。
• NVL函数的格式如下：NVL(expr1,expr2)

备注：
1、如果expr1为NULL，返回值为 expr2，否则返回expr1。
2、适用于数字型、字符型和日期型，但是 expr1和expr2的数据类型必须为同类型。

6、group by分组

• 在 Group by 子句中，Select 查询的列，要么需要是 Group by 中的列，要么得是用聚合函数（比如 sum、count等）加工过的列。不支持直接引用非 Group by 的列。这一点和 MySQL 有所区别；
• 对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组，使用数字下标，可以直接访问数组中的元素。

select collect_set(d.dname)[0],count(*) num from emp e 
join dept d on e.deptno=d.deptno 
where e.sal<=2500 
group by e.deptno 
order by num desc limit 1

7、collect_set函数

8、size函数

计算数组的大小

size(collect_set(compant_name))

9、sumif函数的语法格式

• =sumif(range，criteria，sum_range)
• Sumif（条件区域，求和条件，实际求和区域），第二个求和条件参数在第一个条件区域里。
  

二、SQL正则表达式

1、元字符

当我们要进行一些简单的糊涂查询时用百分号(%),通配符(_)就可以了.其中%表达任意长度的字符串,_表示任意的某一个字符。但如果在一些复杂的查询中关用这两个符号sql语句就会非常复杂,而且也不一定能实现.从Oracle 10g开始引入了在其他程序语言中普通使用的正则表达式。主要有regexp_like（匹配）,regexp_replace（替换）,regexp_substr（提取）,regexp_instr（包含）四个正则表达式函数。

2、正则表达函数

2.1 regexp_like(x,pattern[,match_option])

含义：查看x是否与pattern相匹配，该函数还可以提供一个可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下：

1. ‘c’ 说明在进行匹配时区分大小写（缺省值）；
2. ‘i’ 说明在进行匹配时不区分大小写；
3. ‘n’ (.)点号能表示所有单个字符；
4. ‘m’ 字符串存在换行的时候当作多行处理.这样 就 可 匹 配 每 行 的 结 尾 . 不 然 的 话 就可匹配每行的结尾.不然的话 就可匹配每行的结尾.不然的话只匹配字符串最后的位置。

select * from emp where regexp_like(ename,'^a[a-z]*n$')解析：
从emp表中匹配ename字段，要求以a开头，a-z任意多个字符（包括0个），n结尾。
例如ename为arwen或arwin或anden.但Arwen不能被匹配.因为默认是区分大小写。select * from emp where regexp_like(ename,'^a[a-z]*n$','i')可以查找ename为Arwen的行记录。

2.2 regexp_instr(x,pattern[,start[,occurrence[,return_option[, match_option]]]])

含义：用于在x中查找pattern。返回pattern在x中出现的位置。匹配位置从1开始。可以参考字符串函数 INSTR()，参数相关：

1. ‘start’ 开始查找的位置；
2. ‘occurrence’ 说明应该返回第几次出现pattern的位置；
3. ‘return_option’ 说明应该返回什么整数。若该参数为0，则说明要返回的整数是x中的一个字符的位置；若该参数为非0的整数，则说明要返回的整数为x中出现在pattern之后的字符的位置；
4. ‘match_option’ 修改默认的匹配设置。与regexp_like里面的相同。

SELECT REGEXP_INSTR('hello world','o',1,1,0);   
==》5 解析：
查找o在hello word中第一次出现的位置。
start=1，occurrence=1，return_option=0SELECT REGEXP_INSTR('hello world','o',1,1,1);
==》6查找o在hello word中第一次出现后的字符的位置，这里return_option=1也可以等于其他非0整数。SELECT REGEXP_INSTR('hello world','o',1,2,0);
==》8查找o在hello word中第二次出现的位置（空格也属于一个字符位置）。

2.3 REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])

**含义：**用于在x中查找pattern并返回。可以参考字符串函数 SUBSTR()，参数同REGEXP_INSTR函数。

SELECT REGEXP_SUBSTR('hello world','l{2}');            
==》ll解析：
查询到匹配的字符串才返回匹配的字符.没查到就返回空。

2.4 REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])

含义：用于在x中查找pattern，并将其替换为replae_string。可以参考字符串函数 REPLACE()，参数同REGEXP_INSTR函数。

REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str。注意：from_str不能是空字符串 ('')。

SELECT REGEXP_REPLACE('hello world','o','x',1,1);            
==》hellx world解析：
start=1，occurrence=1。SELECT REGEXP_REPLACE('hello world','o','x');            
==》hellx wxrldSELECT REGEXP_REPLACE('hello world','o','x',1,2);            
==》hello wxrld

三、hive数仓数据重复性检查

1、如何快速确认某一字段是否有重复值

因为Hive中没有主键，mysql严格要求主键，可以将待确认字段作为主键。

1.1 cnt 没有去重，order_cnt 去重， 如果得到结果相同，则表是没有重复

select
count(*) cnt ,count(distinct order_id) order_cnt
from orders
//结果：
cnt order_cnt
3421083 3421083 

1.2 根据待确认字段进行分组，订单号不能有大于1的情况，就代表没有重复

select
order_id,count(1) 
from orders
group by order_id
having count(1)>1
//结果：
order_id   count(1) # 表示没有重复值

1.3 如果数据有重复，如何查找出来重复的字段信息呢？

select
pripid,count(1) 
from orders
group by pripid
having count(1)>1

2、如何去除重复值

2.1 hive表中某些数据是整行的重复，而且字段过多时：

insert overwrite table stu_tab
select distinct * from stu_tab；

通过过滤不同的行，将原表进行覆盖。

2.2 两行数据只有一个字段不一样：

根据情况我们将含有2的那行数据进行删除；

insert overwrite table sys_cus_hz_20210127
select distinct * from sys_cus_hz_20210127 where consumlevel != 2;

2.3 当表中字段不多，而且数据是某些字段重复，可通过row_number()：

insert overwrite table emp_tab
select id,name,sal from (
select * ,
row_number() over(partition by id order by sal desc) rank
from emp_tab)t1
where t1.rank=1;

2.4 在某些情况中我们不是需要全部字段，而是仅仅过滤出某些不重复的字段：

select distinct id from stu_tab;
#单个字段去重
select distinct id，name from stu_tab;
#多个字段之间通过逗号去重

3、row_number() OVER (PARTITION BY COL1 ORDER BY COL2)

这行代码的意思是先对COL1列进行分组，然后按照COL2进行排序，row_number()函数是对分组后的每个组内记录按照COL2排序标号，我们最后取的时候就拿标号为1的一条记录，即达到我的需求。

SELECT 
*, 
Row_Number() OVER (partition by deptid ORDER BY salary desc) rank
FROM employee 

SELECT *
from( select
*,
Row_Number() OVER (partition by deptid ORDER BY salary desc) as rank
FROM employee) t1
where t1.rank=1

只保留rank=1的数据。

四、常见Shell快捷操作

快捷组合	含义
ｉ	vim状态下的插入
esc+wq	vim状态下保存退出
shift+z+z	vim状态下强制保存退出
ctrl+c	强制停止运行
ctrl+l	清空终端返回最上面
scp	拷贝
rsync	同步更新