Hive SQL 常用零碎知识

1. 去换行符

去除某列里面的换行符转义符等等

regexp_replace(col_name, '\n|\t|\r', '') AS new_col_name

2. 日期函数

获取当前时间戳

unix_timestamp()

时间戳转成日期

from_unixtime(CAST(timestamp AS INT),'yyyyMMdd')
from_unixtime(CAST(timestamp AS INT),'yyyy-MM-dd HH:dd:ss')

日期转时间戳

to_nuix_timestamp('2023-07-21 13:07:22','yyyy-MM-dd HH:dd:ss')

日期加减

-- 昨天日期
date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'), -1)

-- 明天日期
date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'), 1)

日期相差天数

-- 等于date1-date2
datediff(date1,date2)

3. 读取json

比如event_value是一个json格式的字段，然后想获取里面的id作为单独一列

select get_json_object(event_value,"$.id") AS id

4. 行专列、列转行

行转列：

变成：

SELECT feature
FROM (
    select feature_list
    from table
)tmp1
LATERAL VIEW explode(split(feature_list,",")) tmp2 AS feature

列转行：

将上面操作反过来，注意这里有set和list两种用法，set会去重，并且使用默认排序(不保序)。list不会去重并且保持原有的排序。

CONCAT_WS(',',COLLECT_SET(feature))
CONCAT_WS(',',COLLECT_LIST(feature))

5. CONCAT和CONCAT_WS

上面提到了CONCAT_WS，这里说一下和CONCAT区别。在 Hive SQL 中，CONCAT_WS 和 CONCAT 函数都用于连接字符串，但它们在如何处理分隔符方面存在差异。以下是这两个函数的主要区别：

CONCAT_WS（With Separator）：用于在连接字符串时添加分隔符。您需要提供一个分隔符，并将分隔符应用在一组要连接的字符串之间。CONCAT_WS 的语法如下：

CONCAT_WS(separator, str1, str2, ...)

-- 示例：
SELECT CONCAT_WS(',', 'apple', 'banana', 'cherry')

输出：'apple,banana,cherry'

CONCAT：将提供的字符串按顺序连接起来，但不包括任何分隔符。其语法如下：

CONCAT(str1, str2, ...)

-- 示例
SELECT CONCAT('apple', 'banana', 'cherry')

输出：'applebananacherry'

总之，CONCAT_WS 在连接字符串时会自动添加分隔符，以简化构建逗号分隔值、路径等的过程。而 CONCAT 仅按顺序连接字符串，而不考虑分隔符。根据所需的输出格式，选择合适的函数以方便地连接字符串。

6. NVL()函数

NVL()函数是空值判断函数，空值为NULL的空值。其表达式的值可以是数字型、字符型和日期型。

• 用法一：NVL（表达式A，表达式B）

 -- 例：如果id为空，则返回0；否则返回id的值
NVL(id,0)

• 用法二：NVL2(表达式A，表达式B，表达式C）

-- 例: 如果sex为空，则返回1；否则返回 0
NVL(id, 0, 1)

通常在数据处理的时候可以使用NVL()来筛选出NULL和字符串为空的情况：

WHERE NVL(id, '')=''

7. 排序并CONCAT_WS

• 例：按照clk_time从小到大，把feature_val变成一行，并以逗号隔开

SELECT owner
     , primary_key
     , CONCAT_WS(',', COLLECT_LIST(feature_val)) AS merged_feature_val
FROM (
         SELECT owner
              , primary_key
              , feature_val
              , clk_time
         FROM your_table_name
         DISTRIBUTE BY owner, primary_key
         SORT BY clk_time
     ) subquery
GROUP BY owner, primary_key

第一反应是order by，但这里不能使用。因为ORDER BY子句对整个结果集进行全局排序，而不是对每个owner和primary_key组内的数据进行排序。当您将数据按owner和primary_key分组后，由于ORDER BY作用于整个结果集，无法保证每个分组内的clk_time顺序。以上代码仅限于Hive，如果在Presto中使用：

SELECT owner,
       primary_key,
       ARRAY_JOIN(ARRAY_AGG(feature_val) 
                  OVER (PARTITION BY owner, primary_key
                        ORDER BY clk_time
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
                  ',') AS merged_feature_val
FROM your_table_name
GROUP BY owner, primary_key, clk_time

在这个查询中，我们使用ARRAY_AGG窗口函数来收集每个分组内的feature_val，并按clk_time排序。然后我们用ARRAY_JOIN函数将列表中的元素连接成一个字符串，并用逗号隔开。这样，可以在Presto上按clk_time从小到大将feature_val变成一行并用逗号隔开。

总结：在此概括一下ORDER BY与DISTRIBUTE BY和SORT BY的区别：

1. ORDER BY：
   • ORDER BY子句用于对整个结果集进行全局排序。
   • 通常用于对查询结果的最终展示格式进行排序。
   • 它对整个结果集进行排序，因此对于分组内部的局部排序不是很理想，尤其是当输入数据的分布和假设不同时。
2. DISTRIBUTE BY 和 SORT BY：
   • DISTRIBUTE BY和SORT BY组合使用可以在Hive中实现局部排序。
   • DISTRIBUTE BY子句用于确保具有相同特征的数据行（如owner和primary_key）发送到同一个reducer。
   • 在每个reducer上，SORT BY对数据进行排序。
   • 这种组合方法更适合在执行聚合和分组操作之前，针对每个分组实现局部排序。

需要注意的是，DISTRIBUTE BY和SORT BY是Hive中特定的子句，不适用于Presto或Spark SQL。为了在Presto或Spark SQL中实现类似的局部排序需求，请使用窗口函数（如使用OVER和PARTITION BY子句）。这可以确保每个分组内部都保留了正确的顺序，从而在执行聚合、连接等操作时顺序不会丢失。

8. UNION和UNION ALL

1. UNION：UNION操作符将两个或多个查询结果集合并为一个结果集，并去除其中的重复行。UNION操作符会对结果进行去重，即如果两个结果集存在相同的行，则只保留一份。
2. UNION ALL：UNION ALL操作符也将两个或多个查询结果集合并为一个结果集，但不进行去重。UNION ALL会保留所有结果中的重复行，并将其全部加入到最终的结果集中。

注意：由于UNION需要进行去重操作，所以它比UNION ALL的执行速度稍慢。如果你确定结果集不会有重复的行，可以使用UNION ALL来提高查询性能。

select 1
union all
select 2
union all
select 1

-- output
1
2
1

select 1
union
select 2
union
select 1

-- output
1
2