我有一个火花数据框,如下所示,带有一个struct
字段。
val arrayStructData = Seq(
Row("James",Row("Java","XX",120)),
Row("Michael",Row("Java","",200)),
Row("Robert",Row("Java","XZ",null)),
Row("Washington",Row("","XX",120))
)
val arrayStructSchema = new StructType().add("name",StringType).add("my_struct", new StructType().add("name",StringType).add("author",StringType).add("pages",IntegerType))
val df = spark.createDataFrame(spark.sparkContext.parallelize(arrayStructData),arrayStructSchema)
df.printSchema()
root
|-- name: string (nullable = true)
|-- my_struct: struct (nullable = true)
| |-- name: string (nullable = true)
| |-- author: string (nullable = true)
| |-- pages: integer (nullable = true)
df.show(false)
+----------+---------------+
|name |my_struct |
+----------+---------------+
|James |[Java, XX, 120]|
|Michael |[Java, , 200] |
|Robert |[Java, XZ,] |
|Washington|[, XX, 120] |
+----------+---------------+
我想构造一个输出列final_list
,该列显示结构中是否存在元素。问题是,在此示例中,结构元素仅限于3个,但是在实际数据中,结构中有1,000个元素,每个记录可能包含也可能不包含每个元素中的值。
这是我要构造列的方式-
val cleaned_df = spark.sql(s"""select name, case when my_struct.name = "" then "" else "name" end as name_present
, case when my_struct.author = "" then "" else "author" end as author_present
, case when my_struct.pages = "" then "" else "pages" end as pages_present
from df""")
cleaned_df.createOrReplaceTempView("cleaned_df")
cleaned_df.show(false)
+----------+------------+--------------+-------------+
|name |name_present|author_present|pages_present|
+----------+------------+--------------+-------------+
|James |name |author |pages |
|Michael |name | |pages |
|Robert |name |author |pages |
|Washington| |author |pages |
+----------+------------+--------------+-------------+
因此,我case
为每个列编写了一个语句以捕获其存在或不存在。然后我像下面这样进行concat以获得最终输出-
val final_df = spark.sql(s"""
select name, concat_ws("," , name_present, author_present, pages_present) as final_list
from cleaned_df
""")
final_df.show(false)
+----------+-----------------+
|name |final_list |
+----------+-----------------+
|James |name,author,pages|
|Michael |name,,pages |
|Robert |name,author,pages|
|Washington|,author,pages |
+----------+-----------------+
我不能写一个巨大的case语句来捕获1000个元素的结构。有更聪明的方法吗?也许是UDF?
我正在使用Spark 2.4.3。我不知道是否有任何支持此功能的高阶函数。但是我的真实数据框的架构如下所示-
|-- name: string (nullable = true)
|-- my_struct: struct (nullable = true)
| |-- name: string (nullable = true)
| |-- author: string (nullable = true)
| |-- element3: integer (nullable = true)
| |-- element4: string (nullable = true)
| |-- element5: double (nullable = true)
.....
.....
| |-- element1000: string (nullable = true)
您已经提到过UDF。使用UDF,您可以遍历my_struct的所有字段并收集标志:
def availableFields = (in:Row) => {
val ret = scala.collection.mutable.ListBuffer.empty[String]
for( i <- Range(0, in.size)) {
if( !in.isNullAt(i) && in.get(i) != "") {
ret += in.schema.fields(i).name
}
}
ret.mkString(",")
}
val availableFieldsUdf = udf(availableFields)
df.withColumn("final_list", availableFieldsUdf(col("my_struct")) ).show(false)
版画
+----------+---------------+-----------------+
|name |my_struct |final_list |
+----------+---------------+-----------------+
|James |[Java, XX, 120]|name,author,pages|
|Michael |[Java, , 200] |name,pages |
|Robert |[Java, XZ,] |name,author |
|Washington|[, XX, 120] |author,pages |
+----------+---------------+-----------------+
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句