Databricks 第11篇:Spark SQL 查询(行转列、列转行、Lateral View、排序)


本文分享在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

  • 分享:
评论
还没有评论
    发表评论 说点什么