常用的hive sql
2024-10-20 09:33:31
细节：sql 中有涉及到正则匹配函数的，要注意转义符号
因为在不同语言下正则匹配规则是否需要加转义符号是不同的，举例，regexp_replace 函数，在hive sql的正则匹配规则的 \d+ 需要前面给它加上转义符号\，而在java中可能不用，在Presto sql 就是不用加转义符号\的。
思路：不用特意去记哪一种语言需要加转义符号，哪种语言不需要，只需要记住正则匹配规则，报错的一个原因，可能是转义符号的问题即可。
regexp_replace(`date`, '\\d+ 小时前', '${DateUtil.addDays(dt, 1)}')
1、拆解 json 字段/json 解析函数 get_json_object
(1) 语法：get_json_object(string json_string, string path)
- json_string：必填。STRING类型。标准的JSON格式对象，格式为
{Key:Value, Key:Value,...}。如果遇到英文双引号（"），需要用两个反斜杠（\）进行转义。如果遇到英文单引号（'），需要用一个反斜杠（\）进行转义。 - path：必填。STRING类型。以$开头。
$：表示根节点。.或['']：表示子节点。MaxCompute支持用这两种字符解析JSON对象，当JSON的Key本身包含.时，可以用['']来替代。[]：表示数组下标，从0开始。*：返回整个数组。*不支持转义。
(2) 例子：
-- json字符串数据如下：
json_string:
{
"store": {
"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"} },
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}
-- 获取owner字段信息，返回amy。
select get_json_object(json_string, '$.owner') from json_string;
-- 提取store.fruit字段第一个数组信息，返回{"weight":8,"type":"apple"}。
select get_json_object(json_string, '$.store.fruit[0]') from json_string;
2、截取字符串
(1) 符合正则表达式方式，截取字符串 regexp_extract
- 语法：regexp_extract(string subject, string pattern, int index)
- 抽取字符串subject 中符合正则表达式pattern 的第index 个部分的子字符串
index是返回结果取表达式的哪一部分
- 0 表示把整个正则表达式对应的结果全部返回
- 1 表示返回正则表达式中第一个()对应的结果，以此类推。
select regexp_extract('histry','(i)(.*?)(e)',0);
(2) 按字符位置截取字符串 substr
- 语法：substr(string|binary A, int start) substr(string|binary A, int start, int len)
substr(title,1,10)
3、替换字符串中的字符 regexp_replace
(1) 语法：
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
(2) 作用：
按照正则表达式pattern 将字符串INTIAL_STRING 中符合条件的部分替换成REPLACEMENT 指定的字符串
(3) 例子：
-- 举例：替换字符
regexp_replace(get_json_object(map_col,'$.title'), '\n|\t|\r', '') title,-- 去掉空格等特殊符号，以防存储是出现数据错行
regexp_replace(get_json_object(map_col,'$.date'),'\/ ', '') `date`, -- 去掉时间前面的/
4、拼接字符串 concat/concat_ws
(1) concat 拼接字符串
- 语法：concat_ws(字符串1,字符串2)
-- 举例：拼接字段
concat('https://developer.unity.cn/projects/',get_json_object(map_col,'$.id')) url
(2) concat_ws 带分割符的拼接字符串
- 语法：concat_ws('分隔符',字符串1,字符串2)
-- 举例：带分割符的拼接字段
concat_ws('/','https://t.bilibili.com',dynamic_id) note_url
5、时间格式相关
(1) 规定时间输出格式 date_format
-- 举例：规定时间输出格式，默认格式 yyyy-MM-dd HH:mm:ss
date_format(get_json_object(map_col,'$.create_time'),'yyyy-MM-dd HH:00:00')
细节：date_format 无法识别/的时间格式，例如：select date_format('2023/01/17', 'y'); 结果是null
解决：先通过字符串替换函数，将/ 替换成 -，然后再使用date_format 函数，获取指定格式的时间
select date_format(regexp_replace('2023/01/17', '/', '-'), 'yyyy-MM-dd');
(2) 和时间戳相关
unix_timestamp(string date) 获取当前时间戳
- unix_timestamp(string timestame) 输入的时间戳格式必须为'yyyy-MM-dd HH:mm:ss',如不符合则返回null
unix_timestamp(string date, string pattern) 指定格式将时间字符串转化成时间戳
- select unix timestamp('2023-1-6''yyyy-MM-dd');
from_unixtime(bigint unixtime[, string format]) 将时间戳转成国际协调时间
6、排序/排名/窗口函数 ROW_NUMBER
- 语法：ROW_NUMBER() OVER(PARTITION BY 分区的字段 ORDER BY 升序/降序字段 [DESC])
-- 举例：根据标题分区后根据创建时间降序展示自然数排名
ROW_NUMBER() OVER(PARTITION BY get_json_object(map_col,'$.title') ORDER BY get_json_object(map_col,'$.create_time') DESC)AS rn
7、炸裂函数explode + 侧视图函数LATERAL VIEW
(1) 作用：
炸裂函数，实现将一行转换成多列，然后侧视图函数进行聚合
(2) 例子：
原数据结果：
试试炸裂函数explode 效果：
SELECT
explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ",")) genre
FROM
ods_crawler_table
WHERE
dt = '2023-02-26'
AND get_json_object(map_col,'$.code') = 'xxx'
▷ explode(genre) 和其他字段一起查询
- 实际业务，字段game_name，genre 都要查询
SELECT
get_json_object(map_col,'$.game_name') game_name,
explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ",")) genre
FROM
ods_crawler_table
WHERE
dt = '2023-02-26'
AND get_json_object(map_col,'$.code') = 'xxx'
报错：UDTF's are not supported outside the SELECT clause, nor nested in expressions
分析：原因是因为这个字段genre，炸裂之后，转换成多列(3列)，而game_name 字段依然是1列，列数不匹配
解决：侧视图(表)的聚合
ods_crawler_table -- 原先的表
LATERAL VIEW -- 聚合（本质上就是笛卡尔乘积）
explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ",")) v -- 炸裂后作为一个表，两个表聚合之后成v表
as genre -- 是炸裂函数explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ","))的别名
------------------------------------------------------------------------------------------------------------------------
SELECT
get_json_object(map_col,'$.game_name') game_name,
genre
FROM
ods_crawler_table
LATERAL VIEW explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ",")) v as genre
WHERE
dt = '2023-02-26'
AND get_json_object(map_col,'$.code') = 'xxx'
聚合效果：
8、去掉json多余的字段 json2map + map_remove + map_values
- 先将json转换成map，然后使用方法map_remove删除，最后使用map_values取出来
-- 举例：
map_values(map_remove(json2map(map_col),'code','create_time')) AS datas
9、条件判断，判断是否为空 nvl、IF
- nvl(valueExp1, valueExp2)：根据第一个表达式的值是否为空，不为空则返回第一个表达式的值，若为空则返回第二个表达式的值
nvl(IF(gap>120, null, gap), 0) gap
10、提高查询性能，相当于临时表、视图 with...as
(1) 作用：
with as短语，也叫做子查询部分，是用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到。其中，SQL片段产生的结果集保存在内存中,
后续的sql均可以访问这个结果集，作用与视图或临时表类似。
(2) 语法：
with temp as (
select xx字段 from xx表
)
select xx字段 from temp;
(3) 本质:
with...as 子查询部分，和直接用子查询效率上没有什么区别，只是这种写法增加了sql可读性。
(4) 小细节：
- with...as 特点：是一次性的，例如下面的例子，定义的"临时表"temp1，在第一次查询name 之后，就不可以再查看id了。
11、类型强转 cast
(1) 语法：
cast(expr as <type>) 将表达式 expr 的结果转换为 <type>
Cast(字段名 as 转换的类型)
(2) 例子：
举例1：cast('1' as BIGINT) 将字符串 '1' 转换为其整数表示形式
举例2：表tableA 有一个时间字段 release_time：2018-11-03 15:31:26
select cast(release_time as date) as release_time from tableA;
- 结果：release_time：2018-11-03
如果本文对你有帮助的话记得给一乐点个赞哦，感谢！
最新文章
- Leetcode 94. Binary Tree Inorder Traversal
- lua table remove元素的问题
- Helloworld -SilverN
- Smart210学习记录------paltform总线
- Drupal8开发教程：认识.info.yml文件
- svn服务器搭建-SuSE Linux Enterprise Server 11 SP3
- git的安装和环境配置过程（学习笔记）
- zabbix自定义key监控mysql主从同步超简单！
- Linux（Ubuntu）换apt-get源
- appium工作原理
- Web前端JQuery面试题（三）
- Integer的NPE问题
- 从MATLAB到FPGA 视频和图像处理——讲座学习小结（视频地址https://ww2.mathworks.cn/videos/from-matlab-to-fpga-video-and-image-processing-102492.html）
- usbserials
- python 全栈开发，Day44(IO模型介绍,阻塞IO,非阻塞IO,多路复用IO,异步IO,IO模型比较分析,selectors模块,垃圾回收机制)
- 线程使用中常见的错误-“System.InvalidOperationException”线程间操作无效: 从不是创建控件“ ”的线程访问它。
- 分布式知识点总结（来自CS-Notes）
- PowerDesigner 连接oracle数据库
- 使用Java设计验证码生成程序
- D. Arpa and a list of numbers Codeforces Round #432 (Div. 2, based on IndiaHacks Final Round 2017)