如何在SparkSQL中实现Like条件? [英] How to implement Like-condition in SparkSQL?

问题描述

如何编写SQL语句以达到如下所示的目标:

How do I write SQL statement to reach the goal like the following statement:

SELECT * FROM table t WHERE t.a LIKE '%'||t.b||'%';

谢谢.

推荐答案

spark.sql.Column提供了like方法，但到目前为止(Spark 1.6.0/2.0.0)它仅适用于字符串文字.仍然可以使用原始SQL:

spark.sql.Column provides like method but as for now (Spark 1.6.0 / 2.0.0) it works only with string literals. Still you can use raw SQL:

import org.apache.spark.sql.hive.HiveContext
val sqlContext = new HiveContext(sc) // Make sure you use HiveContext
import sqlContext.implicits._ // Optional, just to be able to use toDF

val df = Seq(("foo", "bar"), ("foobar", "foo"), ("foobar", "bar")).toDF("a", "b")

df.registerTempTable("df")
sqlContext.sql("SELECT * FROM df  WHERE a LIKE CONCAT('%', b, '%')")

// +------+---+
// |     a|  b|
// +------+---+
// |foobar|foo|
// |foobar|bar|
// +------+---+

或expr/selectExpr:

df.selectExpr("a like CONCAT('%', b, '%')")

在Spark 1.5中将需要HiveContext.如果由于某些原因无法使用Hive上下文，则可以使用自定义udf:

In Spark 1.5 it will requireHiveContext. If for some reason Hive context is not an option you can use custom udf:

import org.apache.spark.sql.functions.udf

val simple_like = udf((s: String, p: String) => s.contains(p))
df.where(simple_like($"a", $"b"))

val regex_like = udf((s: String, p: String) =>
  new scala.util.matching.Regex(p).findFirstIn(s).nonEmpty)
df.where(regex_like($"a", $"b"))

这篇关于如何在SparkSQL中实现Like条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！