68. [Spark]SparkSQL 列转行的一种方法

68.1. 需求场景

有这样一种场景，数据如下：

+---+---+---+---+---+---+
| id|  A|  B|  C|  D|  E|
+---+---+---+---+---+---+
|  a|  2|  3|  4|  5|  6|
|  b|  4|  2| 10|  3|  4|
|  c|  2|  1|  3|  2|  0|
+---+---+---+---+---+---+

现在要求按照id将id后的A、B、C、D、E字段打开，得到一个只有两列的结果集，样式如下：

+---+---+
| id| ev|
+---+---+
|  a|  2|
|  a|  3|
|  a|  4|
|  a|  5|
|  a|  6|
|  b|  4|
|  b|  2|
|  b| 10|
|  b|  3|
|  b|  4|
|  c|  2|
|  c|  1|
|  c|  3|
|  c|  2|
|  c|  0|
+---+---+

68.2. 实际操作

我们使用jupyterLab来运行PySpark创建数据并进行分析。

创建数据集，并且结果注册成为临时视图source：

from pyspark.sql import SparkSession
spark = SparkSession.builder.master("local").getOrCreate()

df = spark.createDataFrame([('a',2,3,4,5,6), ('b',4,2,10,3,4), ('c',2,1,3,2,0)
                           ], ['id','A','B','C','D','E']).orderBy('id')
df.createOrReplaceTempView("source")

df.show()

结果如下：

+---+---+---+---+---+---+
| id|  A|  B|  C|  D|  E|
+---+---+---+---+---+---+
|  a|  2|  3|  4|  5|  6|
|  b|  4|  2| 10|  3|  4|
|  c|  2|  1|  3|  2|  0|
+---+---+---+---+---+---+

68.3. 解决办法

使用侧视图

先将id后面的字段A、B、C、D、E做成一个Array，再侧视图中配合explode函数将行explode成rows。大致实现如下：

sql = """
select id, k
from (
select id,array(A,B,C,D,E) as `ev`
from source
) a 
lateral view explode(a.ev) as k
"""

spark.sql(sql).createOrReplaceTempView("later")
spark.sql("select * from later").show()

结果输出：

+---+---+
| id|  k|
+---+---+
|  a|  2|
|  a|  3|
|  a|  4|
|  a|  5|
|  a|  6|
|  b|  4|
|  b|  2|
|  b| 10|
|  b|  3|
|  b|  4|
|  c|  2|
|  c|  1|
|  c|  3|
|  c|  2|
|  c|  0|
+---+---+

68.4. 隆重介绍

或许应该有别的方法(如上)得到我们想要的结果，但是下面隆重介绍一个sparkSQL中的函数，一步即可得到我们想要的结果集。

该函数即为：STACK(n, expr1, ..., exprk)

stack
stack(n, expr1, ..., exprk) - Separates expr1, ..., exprk into n rows. Uses column names col0, col1, etc. by default unless specified otherwise.

Examples:

> SELECT stack(2, 1, 2, 3);
 1  2
 3  NULL

该函数将一行以n指定的行数分割成n行

使用STACK即可像下面这样即可实现我们的需求：

sql = """
select id,stack(5,A,B,C,D,E) as (`ev`) 
from source
"""

spark.sql(sql).createOrReplaceTempView("ecplode")
spark.sql("select * from ecplode").show()

结果如下：

+---+---+
| id| ev|
+---+---+
|  a|  2|
|  a|  3|
|  a|  4|
|  a|  5|
|  a|  6|
|  b|  4|
|  b|  2|
|  b| 10|
|  b|  3|
|  b|  4|
|  c|  2|
|  c|  1|
|  c|  3|
|  c|  2|
|  c|  0|
+---+---+