一、 使用变量的方式:
和 hive 中使用方式一样,如 ${varName}
案例如下:
scala> spark.sql("set unit_len=8")
res3: org.apache.spark.sql.DataFrame = [key: string, value: string]
scala> spark.sql("set unit_flag=unit_day")
res4: org.apache.spark.sql.DataFrame = [key: string, value: string]
scala> val point_pv_site_day = spark.sql("select substr(access_date, 1, ${unit_len}) as ${unit_flag}, count(1) as pv_site from harli.point_pv group by substr(access_date, 1, ${unit_len})")
二、 说明
当前在 Spark 2.0 中可以正确使用 set key=value 之类定义的变量,但在CDH 5.7 自带的 Spark 1.6.0 中无法识别,执行时的报错信息如下所示:
NoViableAltException(16@[147:1: selectExpression : ( expression | tableAllColumns );])
at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
at org.antlr.runtime.DFA.predict(DFA.java:116)
........................
........................
org.apache.spark.sql.AnalysisException: cannot recognize input near '$' '{' 'unit_len' in select expression; line 1 pos 31
at org.apache.spark.sql.hive.HiveQl$.createPlan(HiveQl.scala:318)
at org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:41)
at org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:40)