You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by "mark.jenkins4@baesystems.com" <ma...@baesystems.com> on 2017/06/15 16:05:56 UTC
[SparkSQL] Escaping a query for a dataframe query
Hi,
I have a query sqlContext.sql("SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND (myudfsearchfor(\"start\"end\"))"
How should I escape the double quote so that it successfully parses?
I know I can use single quotes but I do not want to since I may need to search for a single and double quote.
The exception I get is
[Thread-18] ERROR QueryService$ - Failed to complete query, will mark job as failed java.lang.RuntimeException: [1.117] failure: ``)'' expected but "end" found
SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND (myudfsearchfor(\"start\"end\"))
^
at scala.sys.package$.error(package.scala:27)
at org.apache.spark.sql.catalyst.AbstractSparkSQLParser.parse(AbstractSparkSQLParser.scala:36)
at org.apache.spark.sql.catalyst.DefaultParserDialect.parse(ParserDialect.scala:67)
at org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)
at org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)
Thankyou
Please consider the environment before printing this email. This message should be regarded as confidential. If you have received this email in error please notify the sender and destroy it immediately. Statements of intent shall only become binding when confirmed in hard copy by an authorised signatory. The contents of this email may relate to dealings with other companies under the control of BAE Systems Applied Intelligence Limited, details of which can be found at http://www.baesystems.com/Businesses/index.htm.
RE: [SparkSQL] Escaping a query for a dataframe query
Posted by "mark.jenkins4@baesystems.com" <ma...@baesystems.com>.
Thanks both!
FYI the suggestion to escape the quote does not seem to work. I should have mentioned I am using spark 1.6.2 and have tried to escape the double quote with \\ and \\\\.
My gut feel is that escape chars are not considered for UDF parameters for this version of spark – I would like to be wrong
[Thread-18] ERROR QueryService$ - Failed to complete query, will mark job as failed
java.lang.RuntimeException: [1.118] failure: ``)'' expected but `end' found
SELECT * FROM mytable WHERE mycolumn BETWEEN 1 AND 2 AND (myudfsearchfor(index2, "**", "*start\"end*"))
^
at scala.sys.package$.error(package.scala:27)
at org.apache.spark.sql.catalyst.AbstractSparkSQLParser.parse(AbstractSparkSQLParser.scala:36)
From: Gourav Sengupta [mailto:gourav.sengupta@gmail.com]
Sent: 15 June 2017 19:35
To: Michael Mior
Cc: Jenkins, Mark (UK Guildford); user@spark.apache.org
Subject: Re: [SparkSQL] Escaping a query for a dataframe query
ATTENTION. This message originates from outside BAE Systems.
It might be something that I am saying wrong but sometimes it may just make sense to see the difference between ” and "
<”> 8221, Hex 201d, Octal 20035
<"> 34, Hex 22, Octal 042
Regards,
Gourav
On Thu, Jun 15, 2017 at 6:45 PM, Michael Mior <mm...@apache.org>> wrote:
Assuming the parameter to your UDF should be start"end (with a quote in the middle) then you need to insert a backslash into the query (which must also be escaped in your code). So just add two extra backslashes before the quote inside the string.
sqlContext.sql("SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND (myudfsearchfor(\"start\\\"end\"))"
--
Michael Mior
mmior@apache.org<ma...@apache.org>
2017-06-15 12:05 GMT-04:00 mark.jenkins4@baesystems.com<ma...@baesystems.com> <ma...@baesystems.com>>:
Hi,
I have a query sqlContext.sql(“SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND (myudfsearchfor(\“start\"end\”))”
How should I escape the double quote so that it successfully parses?
I know I can use single quotes but I do not want to since I may need to search for a single and double quote.
The exception I get is
[Thread-18] ERROR QueryService$ - Failed to complete query, will mark job as failed java.lang.RuntimeException: [1.117] failure: ``)'' expected but "end" found
SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND (myudfsearchfor(\“start\"end\”))
^
at scala.sys.package$.error(package.scala:27)
at org.apache.spark.sql.catalyst.AbstractSparkSQLParser.parse(AbstractSparkSQLParser.scala:36)
at org.apache.spark.sql.catalyst.DefaultParserDialect.parse(ParserDialect.scala:67)
at org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)
at org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)
Thankyou
Please consider the environment before printing this email. This message should be regarded as confidential. If you have received this email in error please notify the sender and destroy it immediately. Statements of intent shall only become binding when confirmed in hard copy by an authorised signatory. The contents of this email may relate to dealings with other companies under the control of BAE Systems Applied Intelligence Limited, details of which can be found at http://www.baesystems.com/Businesses/index.htm.
Please consider the environment before printing this email. This message should be regarded as confidential. If you have received this email in error please notify the sender and destroy it immediately. Statements of intent shall only become binding when confirmed in hard copy by an authorised signatory. The contents of this email may relate to dealings with other companies under the control of BAE Systems Applied Intelligence Limited, details of which can be found at http://www.baesystems.com/Businesses/index.htm.
Re: [SparkSQL] Escaping a query for a dataframe query
Posted by Gourav Sengupta <go...@gmail.com>.
It might be something that I am saying wrong but sometimes it may just make
sense to see the difference between *” *and "
<”> 8221, Hex 201d, Octal 20035
<"> 34, Hex 22, Octal 042
Regards,
Gourav
On Thu, Jun 15, 2017 at 6:45 PM, Michael Mior <mm...@apache.org> wrote:
> Assuming the parameter to your UDF should be start"end (with a quote in
> the middle) then you need to insert a backslash into the query (which must
> also be escaped in your code). So just add two extra backslashes before the
> quote inside the string.
>
> sqlContext.sql("SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND
> (myudfsearchfor(\"start\\\"end\"))"
>
> --
> Michael Mior
> mmior@apache.org
>
> 2017-06-15 12:05 GMT-04:00 mark.jenkins4@baesystems.com <
> mark.jenkins4@baesystems.com>:
>
>> *Hi,*
>>
>>
>>
>> *I have a query **sqlContext.sql(“**SELECT * FROM mytable WHERE
>> (mycolumn BETWEEN 1 AND 2) AND (myudfsearchfor(\“start\"end\”))”*
>>
>>
>>
>> *How should I escape the double quote so that it successfully parses? *
>>
>>
>>
>> *I know I can use single quotes but I do not want to since I may need to search for a single and double quote.*
>>
>>
>>
>> *The exception I get is*
>>
>>
>>
>> *[Thread-18] ERROR QueryService$ - Failed to complete query, will mark
>> job as failed java.lang.RuntimeException: [1.117] failure: ``)'' expected
>> but "end" found*
>>
>>
>>
>> *SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND
>> (myudfsearchfor(\“start\"end\”))*
>>
>> *
>> ^*
>>
>> * at scala.sys.package$.error(package.scala:27)*
>>
>> * at
>> org.apache.spark.sql.catalyst.AbstractSparkSQLParser.parse(AbstractSparkSQLParser.scala:36)*
>>
>> * at
>> org.apache.spark.sql.catalyst.DefaultParserDialect.parse(ParserDialect.scala:67)*
>>
>> * at
>> org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)*
>>
>> * at
>> org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)*
>>
>>
>>
>> *Thankyou*
>> Please consider the environment before printing this email. This message
>> should be regarded as confidential. If you have received this email in
>> error please notify the sender and destroy it immediately. Statements of
>> intent shall only become binding when confirmed in hard copy by an
>> authorised signatory. The contents of this email may relate to dealings
>> with other companies under the control of BAE Systems Applied Intelligence
>> Limited, details of which can be found at http://www.baesystems.com/Busi
>> nesses/index.htm.
>>
>
>
Re: [SparkSQL] Escaping a query for a dataframe query
Posted by Michael Mior <mm...@apache.org>.
Assuming the parameter to your UDF should be start"end (with a quote in the
middle) then you need to insert a backslash into the query (which must also
be escaped in your code). So just add two extra backslashes before the
quote inside the string.
sqlContext.sql("SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND
(myudfsearchfor(\"start\\\"end\"))"
--
Michael Mior
mmior@apache.org
2017-06-15 12:05 GMT-04:00 mark.jenkins4@baesystems.com <
mark.jenkins4@baesystems.com>:
> *Hi,*
>
>
>
> *I have a query **sqlContext.sql(“**SELECT * FROM mytable WHERE
> (mycolumn BETWEEN 1 AND 2) AND (myudfsearchfor(\“start\"end\”))”*
>
>
>
> *How should I escape the double quote so that it successfully parses? *
>
>
>
> *I know I can use single quotes but I do not want to since I may need to search for a single and double quote.*
>
>
>
> *The exception I get is*
>
>
>
> *[Thread-18] ERROR QueryService$ - Failed to complete query, will mark job
> as failed java.lang.RuntimeException: [1.117] failure: ``)'' expected but
> "end" found*
>
>
>
> *SELECT * FROM mytable WHERE (mycolumn BETWEEN 1 AND 2) AND
> (myudfsearchfor(\“start\"end\”))*
>
> *
> ^*
>
> * at scala.sys.package$.error(package.scala:27)*
>
> * at
> org.apache.spark.sql.catalyst.AbstractSparkSQLParser.parse(AbstractSparkSQLParser.scala:36)*
>
> * at
> org.apache.spark.sql.catalyst.DefaultParserDialect.parse(ParserDialect.scala:67)*
>
> * at
> org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)*
>
> * at
> org.apache.spark.sql.SQLContext$$anonfun$2.apply(SQLContext.scala:211)*
>
>
>
> *Thankyou*
> Please consider the environment before printing this email. This message
> should be regarded as confidential. If you have received this email in
> error please notify the sender and destroy it immediately. Statements of
> intent shall only become binding when confirmed in hard copy by an
> authorised signatory. The contents of this email may relate to dealings
> with other companies under the control of BAE Systems Applied Intelligence
> Limited, details of which can be found at http://www.baesystems.com/
> Businesses/index.htm.
>