You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Radha krishna <gr...@gmail.com> on 2016/07/10 14:19:48 UTC
IS NOT NULL is not working in programmatic SQL in spark
Hi All,IS NOT NULL is not working in programmatic sql. check below for
input output and code.
Input
====
10,IN
11,PK
12,US
13,UK
14,US
15,IN
16,
17,AS
18,AS
19,IR
20,As
val cntdat = sc.textFile("/user/poc_hortonworks/radha/gsd/sample.txt");
case class CNT (id:Int , code : String)
val cntdf = cntdat.map((f) => { val ff=f.split(",");new
CNT(ff(0).toInt,ff(1))}).toDF
cntdf.registerTempTable("cntids");
sqlContext.sql("SELECT MAX(id),code FROM cntids WHERE code is not null
GROUP BY code").show()
Output
=====
+---+----+
|_c0|code|
+---+----+
| 18| AS|
| 16| |
| 13| UK|
| 14| US|
| 20| As|
| 15| IN|
| 19| IR|
| 11| PK|
+---+----+
i am expecting the below one any idea, how to apply IS NOT NULL ?
+---+----+
|_c0|code|
+---+----+
| 18| AS|
| 13| UK|
| 14| US|
| 20| As|
| 15| IN|
| 19| IR|
| 11| PK|
+---+----+
Thanks & Regards
Radha krishna
Re: IS NOT NULL is not working in programmatic SQL in spark
Posted by Takeshi Yamamuro <li...@gmail.com>.
Hi,
One of solutions to use `spark-csv` (See:
https://github.com/databricks/spark-csv#features).
To load NULL, you can use `nullValue` there.
// maropu
On Mon, Jul 11, 2016 at 1:14 AM, Radha krishna <gr...@gmail.com> wrote:
> I want to apply null comparison to a column in sqlcontext.sql, is there
> any way to achieve this?
> On Jul 10, 2016 8:55 PM, "Radha krishna" <gr...@gmail.com> wrote:
>
>> Ok thank you, how to achieve the requirement.
>>
>> On Sun, Jul 10, 2016 at 8:44 PM, Sean Owen <so...@cloudera.com> wrote:
>>
>>> It doesn't look like you have a NULL field, You have a string-value
>>> field with an empty string.
>>>
>>> On Sun, Jul 10, 2016 at 3:19 PM, Radha krishna <gr...@gmail.com>
>>> wrote:
>>> > Hi All,IS NOT NULL is not working in programmatic sql. check below for
>>> input
>>> > output and code.
>>> >
>>> > Input
>>> > ====
>>> > 10,IN
>>> > 11,PK
>>> > 12,US
>>> > 13,UK
>>> > 14,US
>>> > 15,IN
>>> > 16,
>>> > 17,AS
>>> > 18,AS
>>> > 19,IR
>>> > 20,As
>>> >
>>> > val cntdat = sc.textFile("/user/poc_hortonworks/radha/gsd/sample.txt");
>>> > case class CNT (id:Int , code : String)
>>> > val cntdf = cntdat.map((f) => { val ff=f.split(",");new
>>> > CNT(ff(0).toInt,ff(1))}).toDF
>>> > cntdf.registerTempTable("cntids");
>>> > sqlContext.sql("SELECT MAX(id),code FROM cntids WHERE code is not null
>>> GROUP
>>> > BY code").show()
>>> >
>>> > Output
>>> > =====
>>> > +---+----+
>>> > |_c0|code|
>>> > +---+----+
>>> > | 18| AS|
>>> > | 16| |
>>> > | 13| UK|
>>> > | 14| US|
>>> > | 20| As|
>>> > | 15| IN|
>>> > | 19| IR|
>>> > | 11| PK|
>>> > +---+----+
>>> >
>>> > i am expecting the below one any idea, how to apply IS NOT NULL ?
>>> >
>>> > +---+----+
>>> > |_c0|code|
>>> > +---+----+
>>> > | 18| AS|
>>> > | 13| UK|
>>> > | 14| US|
>>> > | 20| As|
>>> > | 15| IN|
>>> > | 19| IR|
>>> > | 11| PK|
>>> > +---+----+
>>> >
>>> >
>>> >
>>> > Thanks & Regards
>>> > Radha krishna
>>> >
>>> >
>>>
>>
>>
>>
>> --
>>
>>
>>
>>
>>
>>
>>
>>
>> Thanks & Regards
>> Radha krishna
>>
>>
>>
--
---
Takeshi Yamamuro
Re: IS NOT NULL is not working in programmatic SQL in spark
Posted by Radha krishna <gr...@gmail.com>.
I want to apply null comparison to a column in sqlcontext.sql, is there any
way to achieve this?
On Jul 10, 2016 8:55 PM, "Radha krishna" <gr...@gmail.com> wrote:
> Ok thank you, how to achieve the requirement.
>
> On Sun, Jul 10, 2016 at 8:44 PM, Sean Owen <so...@cloudera.com> wrote:
>
>> It doesn't look like you have a NULL field, You have a string-value
>> field with an empty string.
>>
>> On Sun, Jul 10, 2016 at 3:19 PM, Radha krishna <gr...@gmail.com>
>> wrote:
>> > Hi All,IS NOT NULL is not working in programmatic sql. check below for
>> input
>> > output and code.
>> >
>> > Input
>> > ====
>> > 10,IN
>> > 11,PK
>> > 12,US
>> > 13,UK
>> > 14,US
>> > 15,IN
>> > 16,
>> > 17,AS
>> > 18,AS
>> > 19,IR
>> > 20,As
>> >
>> > val cntdat = sc.textFile("/user/poc_hortonworks/radha/gsd/sample.txt");
>> > case class CNT (id:Int , code : String)
>> > val cntdf = cntdat.map((f) => { val ff=f.split(",");new
>> > CNT(ff(0).toInt,ff(1))}).toDF
>> > cntdf.registerTempTable("cntids");
>> > sqlContext.sql("SELECT MAX(id),code FROM cntids WHERE code is not null
>> GROUP
>> > BY code").show()
>> >
>> > Output
>> > =====
>> > +---+----+
>> > |_c0|code|
>> > +---+----+
>> > | 18| AS|
>> > | 16| |
>> > | 13| UK|
>> > | 14| US|
>> > | 20| As|
>> > | 15| IN|
>> > | 19| IR|
>> > | 11| PK|
>> > +---+----+
>> >
>> > i am expecting the below one any idea, how to apply IS NOT NULL ?
>> >
>> > +---+----+
>> > |_c0|code|
>> > +---+----+
>> > | 18| AS|
>> > | 13| UK|
>> > | 14| US|
>> > | 20| As|
>> > | 15| IN|
>> > | 19| IR|
>> > | 11| PK|
>> > +---+----+
>> >
>> >
>> >
>> > Thanks & Regards
>> > Radha krishna
>> >
>> >
>>
>
>
>
> --
>
>
>
>
>
>
>
>
> Thanks & Regards
> Radha krishna
>
>
>
Re: IS NOT NULL is not working in programmatic SQL in spark
Posted by Radha krishna <gr...@gmail.com>.
Ok thank you, how to achieve the requirement.
On Sun, Jul 10, 2016 at 8:44 PM, Sean Owen <so...@cloudera.com> wrote:
> It doesn't look like you have a NULL field, You have a string-value
> field with an empty string.
>
> On Sun, Jul 10, 2016 at 3:19 PM, Radha krishna <gr...@gmail.com> wrote:
> > Hi All,IS NOT NULL is not working in programmatic sql. check below for
> input
> > output and code.
> >
> > Input
> > ====
> > 10,IN
> > 11,PK
> > 12,US
> > 13,UK
> > 14,US
> > 15,IN
> > 16,
> > 17,AS
> > 18,AS
> > 19,IR
> > 20,As
> >
> > val cntdat = sc.textFile("/user/poc_hortonworks/radha/gsd/sample.txt");
> > case class CNT (id:Int , code : String)
> > val cntdf = cntdat.map((f) => { val ff=f.split(",");new
> > CNT(ff(0).toInt,ff(1))}).toDF
> > cntdf.registerTempTable("cntids");
> > sqlContext.sql("SELECT MAX(id),code FROM cntids WHERE code is not null
> GROUP
> > BY code").show()
> >
> > Output
> > =====
> > +---+----+
> > |_c0|code|
> > +---+----+
> > | 18| AS|
> > | 16| |
> > | 13| UK|
> > | 14| US|
> > | 20| As|
> > | 15| IN|
> > | 19| IR|
> > | 11| PK|
> > +---+----+
> >
> > i am expecting the below one any idea, how to apply IS NOT NULL ?
> >
> > +---+----+
> > |_c0|code|
> > +---+----+
> > | 18| AS|
> > | 13| UK|
> > | 14| US|
> > | 20| As|
> > | 15| IN|
> > | 19| IR|
> > | 11| PK|
> > +---+----+
> >
> >
> >
> > Thanks & Regards
> > Radha krishna
> >
> >
>
--
Thanks & Regards
Radha krishna
Re: IS NOT NULL is not working in programmatic SQL in spark
Posted by Sean Owen <so...@cloudera.com>.
It doesn't look like you have a NULL field, You have a string-value
field with an empty string.
On Sun, Jul 10, 2016 at 3:19 PM, Radha krishna <gr...@gmail.com> wrote:
> Hi All,IS NOT NULL is not working in programmatic sql. check below for input
> output and code.
>
> Input
> ====
> 10,IN
> 11,PK
> 12,US
> 13,UK
> 14,US
> 15,IN
> 16,
> 17,AS
> 18,AS
> 19,IR
> 20,As
>
> val cntdat = sc.textFile("/user/poc_hortonworks/radha/gsd/sample.txt");
> case class CNT (id:Int , code : String)
> val cntdf = cntdat.map((f) => { val ff=f.split(",");new
> CNT(ff(0).toInt,ff(1))}).toDF
> cntdf.registerTempTable("cntids");
> sqlContext.sql("SELECT MAX(id),code FROM cntids WHERE code is not null GROUP
> BY code").show()
>
> Output
> =====
> +---+----+
> |_c0|code|
> +---+----+
> | 18| AS|
> | 16| |
> | 13| UK|
> | 14| US|
> | 20| As|
> | 15| IN|
> | 19| IR|
> | 11| PK|
> +---+----+
>
> i am expecting the below one any idea, how to apply IS NOT NULL ?
>
> +---+----+
> |_c0|code|
> +---+----+
> | 18| AS|
> | 13| UK|
> | 14| US|
> | 20| As|
> | 15| IN|
> | 19| IR|
> | 11| PK|
> +---+----+
>
>
>
> Thanks & Regards
> Radha krishna
>
>
---------------------------------------------------------------------
To unsubscribe e-mail: user-unsubscribe@spark.apache.org