常用的hive sql

妖狐艹你老母 2024-03-26 16:42 53阅读 0赞

> #### 细节：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) 例子： ####

*  原数据结果：

![923d134ec42d025148da9cd34116a745.png][]

*  试试炸裂函数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'

###### 聚合效果： ######

![2cdb4da64f237b8f9441c595c85c89bd.png][]

### 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了。

![d22e532788343b84c5444da86fd2bc22.png][]

### 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

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

[923d134ec42d025148da9cd34116a745.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/040323494dcf4c07be4c3400ee163630.png
[2cdb4da64f237b8f9441c595c85c89bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/236b68f1a42b4e0295e1422372785d2c.png
[d22e532788343b84c5444da86fd2bc22.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/277dbb2cc2534af2b7ae7ce08b761e96.png