Hive10---explode拆分多行_hive sql split 多行

Intro

    hive explode操作

import pyspark
1

from pyspark.sql import SparkSession
# 创建SparkSession对象，调用.builder类
# .appName("testapp")方法给应用程序一个名字；.getOrCreate()方法创建或着获取一个已经创建的SparkSession
spark = SparkSession.builder.appName("pysaprk").getOrCreate()
import pyspark.sql.functions
1
2
3
4
5

df = spark.createDataFrame([(1, "A,B"),
                            (2, "C,D"),
                            (3, "E")],
                           ["id", "split_str"])
1
2
3
4

df.show(20,truncate=False)
1

+---+---------+
|id |split_str|
+---+---------+
|1  |A,B      |
|2  |C,D      |
|3  |E        |
+---+---------+
1
2
3
4
5
6
7

df.createOrReplaceTempView("temp")
1

split_str拆分多行

sql = """
select id,split_str,explode(split(split_str,',')) as letter
from temp
"""
1
2
3
4

spark.sql(sql).show()
1

+---+---------+---+
| id|split_str|col|
+---+---------+---+
|  1|      A,B|  A|
|  1|      A,B|  B|
|  2|      C,D|  C|
|  2|      C,D|  D|
|  3|        E|  E|
+---+---------+---+
1
2
3
4
5
6
7
8
9

带序号的有序explode

LATERAL VIEW posexplode(data) t2 as pos,j_column

                                2022-03-29 于南京市江宁区九龙湖