How do I escape a query string in Spark SQL?

johnmcauley — Wed, 12 Aug 2015 15:51:32 GMT

Hey all,

I am trying to filter on a string but the string has a single quote - how do I escape the string in Scala?

I have tried an old version of StringEscapeUtils but no luck.

Sorry if a silly question - new to Scala.

import org.apache.commons.lang.StringEscapeUtils

var sql = StringEscapeUtils.escapeSql("'Ulmus_minor_'Toledo'");

df.filter("topic = '" + sql + "'").map(.getValuesMap[Any](List("hits","date"))).collect().foreach(println);

RETURNS:

topics = 'Ulmus_minor'Toledo' ^

 at scala.sys.package$.error(package.scala:27)

 at org.apache.spark.sql.catalyst.SqlParser.parseExpression(SqlParser.scala:45)

 at org.apache.spark.sql.DataFrame.filter(DataFrame.scala:651)

 at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.&lt;init&gt;(&lt;console&gt;:26)

 at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.&lt;init&gt;(&lt;console&gt;:31)

 at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.&lt;init&gt;(&lt;console&gt;:33)

 at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC.&lt;init&gt;(&lt;console&gt;:35)

 at $iwC$$iwC$$iwC$$iwC$$iwC.&lt;init&gt;(&lt;console&gt;:37)

 at $iwC$$iwC$$iwC$$iwC.&lt;init&gt;(&lt;console&gt;:39)

 at $iwC$$iwC$$iwC.&lt;init&gt;(&lt;console&gt;:41)

 at $iwC$$iwC.&lt;init&gt;(&lt;console&gt;:43)

 at $iwC.&lt;init&gt;(&lt;console&gt;:45)

 at &lt;init&gt;(&lt;console&gt;:47)

 at .&lt;init&gt;(&lt;console&gt;:51)

 at .&lt;clinit&gt;(&lt;console&gt;)

 at .&lt;init&gt;(&lt;console&gt;:7)

 at .&lt;clinit&gt;(&lt;console&gt;)

 at $print(&lt;console&gt;)

 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

 at java.lang.reflect.Method.invoke(Method.java:497)

 at org.apache.spark.repl.SparkIMain$ReadEvalPrint.call(SparkIMain.scala:1065)

 at org.apache.spark.repl.SparkIMain$Request.loadAndRun(SparkIMain.scala:1338)

 at org.apache.spark.repl.SparkIMain.loadAndRunReq$1(SparkIMain.scala:840)

 at org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:871)

 at org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:819)

 at org.apache.spark.repl.SparkILoop.reallyInterpret$1(SparkILoop.scala:857)

 at org.apache.spark.repl.SparkILoop.interpretStartingWith(SparkILoop.scala:902)

 at org.apache.spark.repl.SparkILoop.command(SparkILoop.scala:814)

 at org.apache.spark.repl.SparkILoop.processLine$1(SparkILoop.scala:657)

 at org.apache.spark.repl.SparkILoop.innerLoop$1(SparkILoop.scala:665)

 at org.apache.spark.repl.SparkILoop.org$apache$spark$repl$SparkILoop$$loop(SparkILoop.scala:670)

 at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply$mcZ$sp(SparkILoop.scala:997)

 at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply(SparkILoop.scala:945)

 at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply(SparkILoop.scala:945)

 at scala.tools.nsc.util.ScalaClassLoader$.savingContextLoader(ScalaClassLoader.scala:135)

 at org.apache.spark.repl.SparkILoop.org$apache$spark$repl$SparkILoop$$process(SparkILoop.scala:945)

 at org.apache.spark.repl.SparkILoop.process(SparkILoop.scala:1059)

 at org.apache.spark.repl.Main$.main(Main.scala:31)

 at org.apache.spark.repl.Main.main(Main.scala)

 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

 at java.lang.reflect.Method.invoke(Method.java:497)

 at org.apache.spark.deploy.SparkSubmit$.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:665)

 at org.apache.spark.deploy.SparkSubmit$.doRunMain$1(SparkSubmit.scala:170)

 at org.apache.spark.deploy.SparkSubmit$.submit(SparkSubmit.scala:193)

 at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:112)

 at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)

Re: How do I escape a query string in Spark SQL?

johnmcauley — Wed, 12 Aug 2015 16:05:26 GMT

Thanks to ZERO323 on SO - http://stackoverflow.com/questions/31969047/spark-sql-escape-query-string/31970069#31970069

df.filter($"topic"<=> sql)

Laters,

Re: How do I escape a query string in Spark SQL?

antoniosarco — Wed, 04 May 2016 07:20:10 GMT

generally when u deal with apostrophe u replace the the single quote(') with ('').

More about....handling single quotes

Antonio

topic How do I escape a query string in Spark SQL? in Data Engineering

How do I escape a query string in Spark SQL?

Re: How do I escape a query string in Spark SQL?

Re: How do I escape a query string in Spark SQL?