用Scala将SQL查询的结果编写结果在数据映中失败

Question

只是在databricks中运行此Spark-SQL查询正常工作：

%sql
select CONCAT(`tsArr[1]`,"-", `tsArr[0]`,"-", `tsArr[2]`," ", `tsArr[3]`) as time,
  cast (context._function as string) as funct, 
  cast (context._param as string) as param, 
  cast(context._value as string) as value from clickstreamDF
  lateral view explode(Context) as context

此输出：

time                funct   param           value
11-27-2017 08:20:33 Open    location        3424
11-27-2017 08:20:33 Open    Company Id      testinc
11-27-2017 08:20:33 Open    Channel Info    1
11-27-2017 08:20:33 Open    UserAgent       jack
11-27-2017 08:20:33 Open    Language        english

但是，当我想将查询放入像这样的数据框中

时

%scala    
val df_header = spark.sql(s"select CONCAT(`tsArr[1]`,"-", `tsArr[0]`,"-", `tsArr[2]`," ", `tsArr[3]`) as time,
  cast (context._function as string) as funct,
  cast (context._param as string) as param,
  cast(context._value as string) as value
  from clickstreamDF lateral view explode(Context) as context")
df_header.createOrReplaceTempView("clickstreamDF")

然后失败。它说：

错误：'）'预期，但字符串字面发现。

我猜这与" - "one_answers"有关。我尝试用"''和``或``或离开''''完全替换或延伸，但没有结果。我在做什么错？

问：

d。

Answer 1

避免引号（即 "）之间的模棱两可，以封闭整个Spark SQL字符串与您的SQL语句中使用的Squl SQL字符串，请使用Triple-Quotes（ """）作为封闭报价。您还需要删除包含tsArr[] s的backticks，如下所示：

：

import org.apache.spark.sql.functions._
import spark.implicits._
case class CT(_function: String, _param: String, _value: String)
val clickstreamDF = Seq(
  (Seq("27", "11", "2017", "08:20:33"), Seq(CT("f1", "p1", "v1"), CT("f2", "p2", "v2"))),
  (Seq("28", "12", "2017", "09:30:44"), Seq(CT("f3", "p3", "v3")))
).toDF("tsArr", "contexts")
clickstreamDF.createOrReplaceTempView("clickstreamTable")
val df_header = spark.sql("""
  select
    concat(tsArr[1], "-", tsArr[0], "-", tsArr[2], " ", tsArr[3]) as time,
    cast(context._function as string) as funct,
    cast(context._param as string) as param,
    cast(context._value as string) as value
  from
    clickstreamTable lateral view explode(contexts) as context
""")
df_header.show
// +-------------------+-----+-----+-----+
// |               time|funct|param|value|
// +-------------------+-----+-----+-----+
// |11-27-2017 08:20:33|   f1|   p1|   v1|
// |11-27-2017 08:20:33|   f2|   p2|   v2|
// |12-28-2017 09:30:44|   f3|   p3|   v3|
// +-------------------+-----+-----+-----+

顺便说一句，您可能需要考虑使用DataFrame API，因为您已经在dataFrame中拥有数据：

val df_header = clickstreamDF.
  withColumn("time",
    concat($"tsArr"(1), lit("-"), $"tsArr"(0), lit("-"), $"tsArr"(2), lit(" "), $"tsArr"(3))
  ).
  withColumn("context", explode($"contexts")).
  select($"time",
    $"context._function".cast("String").as("funct"),
    $"context._param".cast("String").as("param"),
    $"context._value".cast("String").as("value")
  )