0


常用的hive sql

细节: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(PARTITIONBY get_json_object(map_col,'$.title')ORDERBY 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) 语法:

withtempas(select xx字段 from xx表
)select xx字段 fromtemp;

(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 asdate)as release_time from tableA;
  • 结果:release_time:2018-11-03

如果本文对你有帮助的话记得给一乐点个赞哦,感谢!

标签: hive sql hive sql

本文转载自: https://blog.csdn.net/weixin_45630258/article/details/129305532
版权归原作者 一 乐 所有, 如有侵权,请联系我们删除。

“常用的hive sql”的评论:

还没有评论