sql - 如何更改spark sql中的列值

Question

在 Sql 中，我可以使用 UPDATE 轻松更新某些列值，例如:
我有一张 table (学生)，如:

student_id, grade, new_student_id
123             B      234
555             A      null

UPDATE Student
SET student_id = new_student_id
WHERE new_student_id isNotNull

如何使用 SparkSql(PySpark) 在 Spark 中执行此操作？

Answer 1

您可以使用 withColumn覆盖现有 new_student_id栏目与原文new_student_id值如果不为空，否则为来自 student_id 的值使用列:

from pyspark.sql.functions import col,when

#Create sample data
students = sc.parallelize([(123,'B',234),(555,'A',None)]).toDF(['student_id','grade','new_student_id'])

#Use withColumn to use student_id when new_student_id is not populated
cleaned = students.withColumn("new_student_id", 
          when(col("new_student_id").isNull(), col("student_id")).
          otherwise(col("new_student_id")))
cleaned.show()

使用您的样本数据作为输入:

+----------+-----+--------------+
|student_id|grade|new_student_id|
+----------+-----+--------------+
|       123|    B|           234|
|       555|    A|          null|
+----------+-----+--------------+

输出数据如下所示:

+----------+-----+--------------+
|student_id|grade|new_student_id|
+----------+-----+--------------+
|       123|    B|           234|
|       555|    A|           555|
+----------+-----+--------------+