sparksql 大小写_Spark SQL不区分大小写的列条件过滤器

How to use Spark SQL filter as a case insensitive filter.

For example:

dataFrame.filter(dataFrame.col("vendor").equalTo("fortinet"));

just return rows that 'vendor' column is equal to 'fortinet' but i want rows that 'vendor' column equal to 'fortinet' or 'Fortinet' or 'foRtinet' or ...

解决方案

You can either use case-insensitive regex:

val df = sc.parallelize(Seq(

(1L, "Fortinet"), (2L, "foRtinet"), (3L, "foo")

)).toDF("k", "v")

df.where($"v".rlike("(?i)^fortinet$")).show

// +---+--------+

// | k| v|

// +---+--------+

// | 1|Fortinet|

// | 2|foRtinet|

// +---+--------+

or simple equality with lower / upper:

import org.apache.spark.sql.functions.{lower, upper}

df.where(lower($"v") === "fortinet")

// +---+--------+

// | k| v|

// +---+--------+

// | 1|Fortinet|

// | 2|foRtinet|

// +---+--------+

df.where(upper($"v") === "FORTINET")

// +---+--------+

// | k| v|

// +---+--------+

// | 1|Fortinet|

// | 2|foRtinet|

// +---+--------+

For simple filters I would prefer rlike although performance should be similar, for join conditions equality is a much better choice. See How can we JOIN two Spark SQL dataframes using a SQL-esque "LIKE" criterion? for details.