检查 arraytype 列是否包含 null [英] Check if arraytype column contains null

问题描述

我有一个数据框，其中有一列可以包含整数值的数组类型.如果没有值，它将只包含一个，它将是空值

重要:注意该列不会为空，而是具有单个值的数组；空

<代码>>val df: DataFrame = Seq(("foo", Seq(Some(2), Some(3))), ("bar", Seq(None))).toDF("k", "v";)df: org.apache.spark.sql.DataFrame = [k: string, v: array]>df.show()+---+------+|k|v|+---+------+|foo|[2, 3]||条|[空]|

问题:我想获取具有空值的行.

---

到目前为止我尝试过的:

<代码>>df.filter(array_contains(df(v"), 2)).show()+---+------+|k|v|+---+------+|foo|[2, 3]|+---+------+

对于null，它似乎不起作用

<代码>>df.filter(array_contains(df(v"), null)).show()

<块引用>

org.apache.spark.sql.AnalysisException:无法解析'array_contains(v, NULL)' 由于数据类型不匹配:Null 类型值不能用作参数；

或

<代码>>df.filter(array_contains(df(v"), None)).show()

<块引用>

java.lang.RuntimeException: 不支持的文字类型类 scala.None$无

解决方案

在这种情况下不能使用 array_contains 因为 SQL NULL 不能进行相等性比较.

你可以像这样使用udf:

val contains_null = udf((xs: Seq[Integer]) => xs.contains(null))df.where(contains_null($"v")).show//+---+------+//|k|v|//+---+------+//|条|[空]|

I have a dataframe with a column of arraytype that can contain integer values. If no values it will contain only one and it will be the null value

Important: note the column will not be null but an array with a single value; null

> val df: DataFrame  = Seq(("foo", Seq(Some(2), Some(3))), ("bar", Seq(None))).toDF("k", "v")
df: org.apache.spark.sql.DataFrame = [k: string, v: array<int>]
> df.show()
+---+------+
|  k|     v|
+---+------+
|foo|[2, 3]|
|bar|[null]|

Question: I'd like to get the rows that have the null value.

---

What I have tried thus far:

> df.filter(array_contains(df("v"), 2)).show()
+---+------+
|  k|     v|
+---+------+
|foo|[2, 3]|
+---+------+

for null, it does not seem to work

> df.filter(array_contains(df("v"), null)).show()

org.apache.spark.sql.AnalysisException: cannot resolve 'array_contains(v, NULL)' due to data type mismatch: Null typed values cannot be used as arguments;

or

> df.filter(array_contains(df("v"), None)).show()

java.lang.RuntimeException: Unsupported literal type class scala.None$ None

解决方案

It is not possible to use array_contains in this case because SQL NULL cannot be compared for equality.

You can use udf like this:

val contains_null = udf((xs: Seq[Integer]) => xs.contains(null))

df.where(contains_null($"v")).show

// +---+------+
// |  k|     v|
// +---+------+
// |bar|[null]|

这篇关于检查 arraytype 列是否包含 null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！