Databricks 第11篇：Spark SQL 查詢（行轉列、列轉行、Lateral View、排序）

2021 年 2 月 9 日
筆記
Databricks

本文分享在Azure Databricks中如何實現行轉列和列轉行，並介紹對查詢的結果進行排序的各種方式。

一，行轉列

在分組中，把每個分組中的某一列的數據連接在一起：

collect_list：把一個分組中的列合成為數組，數據不去重，格式是[‘a’,’a’,’b’]
collect_set：把一個分組中的列合成為集合，數據去重，格式是[‘a’,’b’]

用於連接文本數組的函數，通過sep把數組中的item分割開，連接成一個字元串：

concat_ws(sep, [str | array(str)]+)

舉個例子，把每個用戶的game，通過逗號連接起來：

select uid
 ,concat_ws(",",collect_list(game)) as game_list 
from user_game

二，列轉行

Explode(expr) 用於處理array和map結構的數據，把一行的列值轉換成多行，該函數產生一個虛擬表，包含一行或多行數據，也就是說，Explode(expr)函數把array類型expr中的元素分成多行，或者將map類型的expr中的元素分成多行和多列。

舉個例子，把game_list中的每個item轉換為一行數據：

with cte_game as
(
  select uid
    ,collect_list(game) as game_list 
  from user_game
  group by uid
)
select uid
  ,explode(game_list) as game    
from cte_game

三，Lateral View子句

Lateral View子句用於連接表值函數（UDTF），比如explode、split 。Lateral View通過UDTF函數把數據拆分成多行，再把多行結果組合成一個虛擬表。

該子句主要解決的問題是：在select使用UDTF做查詢的過程中，該查詢只能包含單個UDTF，不能包含其它欄位以及多個UDTF的情況。

LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)

使用LATERAL VIEW + explode 函數進行查詢，語句如下：

select movie,category_name 
from movie_info
LATERAL VIEW explode(category) tmpTable as category_name;
-- category_name 是給 explode(category) 列起的別名

四，排序

在SELECT子句中，ORDER BY子句用於保證最終數據的順序，除此之外，還可以按照分區來排序。

[ WITH with_query [ , ... ] ]
select_statement[ ORDER BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] } ]
  [ SORT BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] } ]
  [ CLUSTER BY { expression [ , ... ] } ]
  [ DISTRIBUTE BY { expression [, ... ] } ]
  [ WINDOW { named_window [ , WINDOW named_window, ... ] } ]

1，DISTRIBUTE BY子句

根據輸入表達式對錶進行重新分區，

DISTRIBUTE BY { expression [ , ... ] }

舉個例子，把age相同的person作為同一個cluster（或partition）。

SELECT age, name FROM person DISTRIBUTE BY age;

2，SORT BY子句

在每個分區中，按照指定的順序對每個分區內的數據進行排序，如果分區不止一個，那麼SORT BY可能會返回部分排序的結果。

SORT BY { expression [ sort_direction | nulls_sort_order ] [ , ... ] }

參數注釋：

nulls_sort_order 的有效值是 NULLS { FIRST | LAST }，可選，用於指定在非NULL值之前/之後是否返回NULL值。

如果未指定null_sort_order，則如果排序順序為ASC，則NULL排在最前面，如果排序順序為DESC，則NULL排在最後面。

如果指定了NULLS FIRST，則無論排序順序如何，都將首先返回NULL值。
如果指定了NULLS LAST，則無論排序順序如何，最後都會返回NULL值。

舉個例子，按照name對每個分區中的數據進行排序：

SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person
    SORT BY name ASC, age DESC;

3，CLUSTER BY子句

根據輸入表達式對數據進行重新分區，然後對每個分區內的數據進行排序。從語義上講，這等效於先執行DISTRIBUTE BY，再執行SORT BY。該子句僅確保結果行在每個分區內排序，並且不保證輸出的總順序。

CLUSTER BY { expression [ , ... ] }

舉個例子，按照age把person分區，age相同的person位於同一個分區，然後按照age對每個分區中的person進行排序。

SELECT age, name FROM person CLUSTER BY age;

參考文檔：

Spark SELECT

掌握這個SQL技巧超越80%的人——行轉列/列轉行

SQL reference for Databricks Runtime 7.x

Tags: Databricks