You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Praseetha <pr...@gmail.com> on 2016/09/13 12:54:48 UTC
Unable to compare SparkSQL Date columns
Hi All,
I have a case class in scala case class TestDate (id: String, loginTime:
java.sql.Date)
I created 2 RDD's of type TestDate
I wanted to do an inner join on two rdd's where the values of loginTime
column is equal. Please find the code snippet below,
firstRDD.toDF.registerTempTable("firstTable")
secondRDD.toDF.registerTempTable("secondTable")
val res = sqlContext.sql("select * from firstTable INNER JOIN
secondTable on to_date(firstTable.loginTime) =
to_date(secondTable.loginTime)")
I'm not getting any exception. But i'm not getting correct answer too. It
does a cartesian and some random dates are generated in the result.
Regds,
--Praseetha
Re: Unable to compare SparkSQL Date columns
Posted by Praseetha <pr...@gmail.com>.
Hi Mich,
Even i'm getting similar output.
The dates that are passed as input are different from the one in the output.
Since its an inner join, the expected result is
[2015-12-31,2015-12-31,1,105]
[2016-01-27,2016-01-27,5,101]
Thanks & Regds,
--Praseetha
On Tue, Sep 13, 2016 at 11:21 PM, Mich Talebzadeh <mich.talebzadeh@gmail.com
> wrote:
> Hi Praseetha,
>
> This is how I have written this.
>
> case class TestDate (id: String, loginTime: java.sql.Date)
> val formate = new SimpleDateFormat("YYYY-MM-DD")
> val TestDateData = sc.parallelize(List(
> ("1", new java.sql.Date(formate.parse("2016-01-31").getTime)),
> ("2", new java.sql.Date(formate.parse("2106-01-30").getTime)),
> ("3", new java.sql.Date(formate.parse("2016-01-29").getTime)),
> ("4", new java.sql.Date(formate.parse("2016-01-28").getTime)),
> ("5", new java.sql.Date(formate.parse("2016-01-27").getTime))
> ))
> val firstPair = TestDateData.map(x => ( new TestDate(x._1, x._2)))
> val fp = firstPair.toDF
> case class TestDate2 (id2: String, loginTime2: java.sql.Date)
> val TestDateData1 = sc.parallelize(List(
> ("101", new java.sql.Date(formate.parse("2016-01-27").getTime)),
> ("102", new java.sql.Date(formate.parse("2016-01-26").getTime)),
> ("103", new java.sql.Date(formate.parse("2016-01-25").getTime)),
> ("104", new java.sql.Date(formate.parse("2016-01-24").getTime)),
> ("105", new java.sql.Date(formate.parse("2016-01-31").getTime))
> ))
> val secondPair = TestDateData1.map(x => ( new TestDate2(x._1, x._2)))
> val sp = secondPair.toDF
> val rs = fp.join(sp,fp("loginTime")===sp("loginTime2"),"inner").select('loginTime,'loginTime2,
> 'id,'id2).show
>
> This is what I get
>
> [2015-12-27,2015-12-27,1,101]
> [2015-12-27,2015-12-27,1,102]
> [2015-12-27,2015-12-27,1,103]
> [2015-12-27,2015-12-27,1,104]
> [2015-12-27,2015-12-27,1,105]
> [2015-12-27,2015-12-27,3,101]
> [2015-12-27,2015-12-27,3,102]
> [2015-12-27,2015-12-27,3,103]
> [2015-12-27,2015-12-27,3,104]
> [2015-12-27,2015-12-27,3,105]
> [2015-12-27,2015-12-27,4,101]
> [2015-12-27,2015-12-27,4,102]
> [2015-12-27,2015-12-27,4,103]
> [2015-12-27,2015-12-27,4,104]
> [2015-12-27,2015-12-27,4,105]
> [2015-12-27,2015-12-27,5,101]
> [2015-12-27,2015-12-27,5,102]
> [2015-12-27,2015-12-27,5,103]
> [2015-12-27,2015-12-27,5,104]
> [2015-12-27,2015-12-27,5,105]
> rs: Unit = ()
>
>
> Is this what you are expecting?
>
> HTH
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 13 September 2016 at 16:46, Praseetha <pr...@gmail.com> wrote:
>
>> Hi Mich,
>>
>> val formate = new SimpleDateFormat("YYYY-MM-DD")
>>
>> Thanks & Regds,
>> --Praseetha
>>
>> On Tue, Sep 13, 2016 at 8:50 PM, Mich Talebzadeh <
>> mich.talebzadeh@gmail.com> wrote:
>>
>>> Hi Praseetha.
>>>
>>> <console>:32: error: not found: value formate
>>> Error occurred in an application involving default arguments.
>>> ("1", new java.sql.Date(formate.parse("2
>>> 016-01-31").getTime)),
>>>
>>> What is that formate?
>>>
>>> Thanks
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>>
>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>
>>>
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>> any loss, damage or destruction of data or any other property which may
>>> arise from relying on this email's technical content is explicitly
>>> disclaimed. The author will in no case be liable for any monetary damages
>>> arising from such loss, damage or destruction.
>>>
>>>
>>>
>>> On 13 September 2016 at 16:12, Praseetha <pr...@gmail.com> wrote:
>>>
>>>> Hi Mich,
>>>>
>>>> Thanks a lot for your reply.
>>>>
>>>> Here is the sample
>>>>
>>>> case class TestDate (id: String, loginTime: java.sql.Date)
>>>>
>>>> val formate = new SimpleDateFormat("YYYY-MM-DD")
>>>>
>>>> val TestDateData = sc.parallelize(List(
>>>> ("1", new java.sql.Date(formate.parse("2016-01-31").getTime)),
>>>> ("2", new java.sql.Date(formate.parse("2106-01-30").getTime)),
>>>> ("3", new java.sql.Date(formate.parse("2016-01-29").getTime)),
>>>> ("4", new java.sql.Date(formate.parse("2016-01-28").getTime)),
>>>> ("5", new java.sql.Date(formate.parse("2016-01-27").getTime))
>>>> ))
>>>> val firstPair = TestDateData.map(x => ( new TestDate(x._1, x._2)))
>>>>
>>>> val TestDateData1 = sc.parallelize(List(
>>>> ("101", new java.sql.Date(formate.parse("2
>>>> 016-01-27").getTime)),
>>>> ("102", new java.sql.Date(formate.parse("2
>>>> 016-01-26").getTime)),
>>>> ("103", new java.sql.Date(formate.parse("2
>>>> 016-01-25").getTime)),
>>>> ("104", new java.sql.Date(formate.parse("2
>>>> 016-01-24").getTime)),
>>>> ("105", new java.sql.Date(formate.parse("2016-01-31").getTime))
>>>> ))
>>>> val secondPair = TestDateData1.map(x => ( new TestDate(x._1, x._2)))
>>>>
>>>> firstPair.toDF.registerTempTable("firstTable")
>>>> secondPair.toDF.registerTempTable("secondTable")
>>>>
>>>> val res = sqlContext.sql("select * from firstTable INNER JOIN
>>>> secondTable on firstTable.loginTime = secondTable.loginTime")
>>>>
>>>>
>>>> I tried the following query,
>>>> sqlContext.sql("select loginTime from firstTable")
>>>> Even this query gives the wrong dates.
>>>>
>>>> Regds,
>>>> --Praseetha
>>>>
>>>> On Tue, Sep 13, 2016 at 6:33 PM, Mich Talebzadeh <
>>>> mich.talebzadeh@gmail.com> wrote:
>>>>
>>>>> Can you send the rdds that just creates those two dates?
>>>>>
>>>>> HTH
>>>>>
>>>>> Dr Mich Talebzadeh
>>>>>
>>>>>
>>>>>
>>>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>>
>>>>>
>>>>>
>>>>> http://talebzadehmich.wordpress.com
>>>>>
>>>>>
>>>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>>>> any loss, damage or destruction of data or any other property which may
>>>>> arise from relying on this email's technical content is explicitly
>>>>> disclaimed. The author will in no case be liable for any monetary damages
>>>>> arising from such loss, damage or destruction.
>>>>>
>>>>>
>>>>>
>>>>> On 13 September 2016 at 13:54, Praseetha <pr...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>>
>>>>>> Hi All,
>>>>>>
>>>>>> I have a case class in scala case class TestDate (id: String,
>>>>>> loginTime: java.sql.Date)
>>>>>>
>>>>>> I created 2 RDD's of type TestDate
>>>>>>
>>>>>> I wanted to do an inner join on two rdd's where the values of
>>>>>> loginTime column is equal. Please find the code snippet below,
>>>>>>
>>>>>> firstRDD.toDF.registerTempTable("firstTable")
>>>>>> secondRDD.toDF.registerTempTable("secondTable")
>>>>>> val res = sqlContext.sql("select * from firstTable INNER JOIN secondTable on to_date(firstTable.loginTime) = to_date(secondTable.loginTime)")
>>>>>>
>>>>>> I'm not getting any exception. But i'm not getting correct answer
>>>>>> too. It does a cartesian and some random dates are generated in the result.
>>>>>>
>>>>>>
>>>>>> Regds,
>>>>>> --Praseetha
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
Re: Unable to compare SparkSQL Date columns
Posted by Mich Talebzadeh <mi...@gmail.com>.
Hi Praseetha,
This is how I have written this.
case class TestDate (id: String, loginTime: java.sql.Date)
val formate = new SimpleDateFormat("YYYY-MM-DD")
val TestDateData = sc.parallelize(List(
("1", new java.sql.Date(formate.parse("2016-01-31").getTime)),
("2", new java.sql.Date(formate.parse("2106-01-30").getTime)),
("3", new java.sql.Date(formate.parse("2016-01-29").getTime)),
("4", new java.sql.Date(formate.parse("2016-01-28").getTime)),
("5", new java.sql.Date(formate.parse("2016-01-27").getTime))
))
val firstPair = TestDateData.map(x => ( new TestDate(x._1, x._2)))
val fp = firstPair.toDF
case class TestDate2 (id2: String, loginTime2: java.sql.Date)
val TestDateData1 = sc.parallelize(List(
("101", new java.sql.Date(formate.parse("2016-01-27").getTime)),
("102", new java.sql.Date(formate.parse("2016-01-26").getTime)),
("103", new java.sql.Date(formate.parse("2016-01-25").getTime)),
("104", new java.sql.Date(formate.parse("2016-01-24").getTime)),
("105", new java.sql.Date(formate.parse("2016-01-31").getTime))
))
val secondPair = TestDateData1.map(x => ( new TestDate2(x._1, x._2)))
val sp = secondPair.toDF
val rs = fp.join(sp,fp("loginTime")===
sp("loginTime2"),"inner").select('loginTime,'loginTime2, 'id,'id2).show
This is what I get
[2015-12-27,2015-12-27,1,101]
[2015-12-27,2015-12-27,1,102]
[2015-12-27,2015-12-27,1,103]
[2015-12-27,2015-12-27,1,104]
[2015-12-27,2015-12-27,1,105]
[2015-12-27,2015-12-27,3,101]
[2015-12-27,2015-12-27,3,102]
[2015-12-27,2015-12-27,3,103]
[2015-12-27,2015-12-27,3,104]
[2015-12-27,2015-12-27,3,105]
[2015-12-27,2015-12-27,4,101]
[2015-12-27,2015-12-27,4,102]
[2015-12-27,2015-12-27,4,103]
[2015-12-27,2015-12-27,4,104]
[2015-12-27,2015-12-27,4,105]
[2015-12-27,2015-12-27,5,101]
[2015-12-27,2015-12-27,5,102]
[2015-12-27,2015-12-27,5,103]
[2015-12-27,2015-12-27,5,104]
[2015-12-27,2015-12-27,5,105]
rs: Unit = ()
Is this what you are expecting?
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 13 September 2016 at 16:46, Praseetha <pr...@gmail.com> wrote:
> Hi Mich,
>
> val formate = new SimpleDateFormat("YYYY-MM-DD")
>
> Thanks & Regds,
> --Praseetha
>
> On Tue, Sep 13, 2016 at 8:50 PM, Mich Talebzadeh <
> mich.talebzadeh@gmail.com> wrote:
>
>> Hi Praseetha.
>>
>> <console>:32: error: not found: value formate
>> Error occurred in an application involving default arguments.
>> ("1", new java.sql.Date(formate.parse("2
>> 016-01-31").getTime)),
>>
>> What is that formate?
>>
>> Thanks
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>> On 13 September 2016 at 16:12, Praseetha <pr...@gmail.com> wrote:
>>
>>> Hi Mich,
>>>
>>> Thanks a lot for your reply.
>>>
>>> Here is the sample
>>>
>>> case class TestDate (id: String, loginTime: java.sql.Date)
>>>
>>> val formate = new SimpleDateFormat("YYYY-MM-DD")
>>>
>>> val TestDateData = sc.parallelize(List(
>>> ("1", new java.sql.Date(formate.parse("2016-01-31").getTime)),
>>> ("2", new java.sql.Date(formate.parse("2106-01-30").getTime)),
>>> ("3", new java.sql.Date(formate.parse("2016-01-29").getTime)),
>>> ("4", new java.sql.Date(formate.parse("2016-01-28").getTime)),
>>> ("5", new java.sql.Date(formate.parse("2016-01-27").getTime))
>>> ))
>>> val firstPair = TestDateData.map(x => ( new TestDate(x._1, x._2)))
>>>
>>> val TestDateData1 = sc.parallelize(List(
>>> ("101", new java.sql.Date(formate.parse("2016-01-27").getTime)),
>>> ("102", new java.sql.Date(formate.parse("2016-01-26").getTime)),
>>> ("103", new java.sql.Date(formate.parse("2016-01-25").getTime)),
>>> ("104", new java.sql.Date(formate.parse("2016-01-24").getTime)),
>>> ("105", new java.sql.Date(formate.parse("2016-01-31").getTime))
>>> ))
>>> val secondPair = TestDateData1.map(x => ( new TestDate(x._1, x._2)))
>>>
>>> firstPair.toDF.registerTempTable("firstTable")
>>> secondPair.toDF.registerTempTable("secondTable")
>>>
>>> val res = sqlContext.sql("select * from firstTable INNER JOIN
>>> secondTable on firstTable.loginTime = secondTable.loginTime")
>>>
>>>
>>> I tried the following query,
>>> sqlContext.sql("select loginTime from firstTable")
>>> Even this query gives the wrong dates.
>>>
>>> Regds,
>>> --Praseetha
>>>
>>> On Tue, Sep 13, 2016 at 6:33 PM, Mich Talebzadeh <
>>> mich.talebzadeh@gmail.com> wrote:
>>>
>>>> Can you send the rdds that just creates those two dates?
>>>>
>>>> HTH
>>>>
>>>> Dr Mich Talebzadeh
>>>>
>>>>
>>>>
>>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>
>>>>
>>>>
>>>> http://talebzadehmich.wordpress.com
>>>>
>>>>
>>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>>> any loss, damage or destruction of data or any other property which may
>>>> arise from relying on this email's technical content is explicitly
>>>> disclaimed. The author will in no case be liable for any monetary damages
>>>> arising from such loss, damage or destruction.
>>>>
>>>>
>>>>
>>>> On 13 September 2016 at 13:54, Praseetha <pr...@gmail.com>
>>>> wrote:
>>>>
>>>>>
>>>>> Hi All,
>>>>>
>>>>> I have a case class in scala case class TestDate (id: String,
>>>>> loginTime: java.sql.Date)
>>>>>
>>>>> I created 2 RDD's of type TestDate
>>>>>
>>>>> I wanted to do an inner join on two rdd's where the values of
>>>>> loginTime column is equal. Please find the code snippet below,
>>>>>
>>>>> firstRDD.toDF.registerTempTable("firstTable")
>>>>> secondRDD.toDF.registerTempTable("secondTable")
>>>>> val res = sqlContext.sql("select * from firstTable INNER JOIN secondTable on to_date(firstTable.loginTime) = to_date(secondTable.loginTime)")
>>>>>
>>>>> I'm not getting any exception. But i'm not getting correct answer too.
>>>>> It does a cartesian and some random dates are generated in the result.
>>>>>
>>>>>
>>>>> Regds,
>>>>> --Praseetha
>>>>>
>>>>
>>>>
>>>
>>
>
Re: Unable to compare SparkSQL Date columns
Posted by Mich Talebzadeh <mi...@gmail.com>.
Hi Praseetha.
<console>:32: error: not found: value formate
Error occurred in an application involving default arguments.
("1", new
java.sql.Date(formate.parse("2016-01-31").getTime)),
What is that formate?
Thanks
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 13 September 2016 at 16:12, Praseetha <pr...@gmail.com> wrote:
> Hi Mich,
>
> Thanks a lot for your reply.
>
> Here is the sample
>
> case class TestDate (id: String, loginTime: java.sql.Date)
>
> val formate = new SimpleDateFormat("YYYY-MM-DD")
>
> val TestDateData = sc.parallelize(List(
> ("1", new java.sql.Date(formate.parse("2016-01-31").getTime)),
> ("2", new java.sql.Date(formate.parse("2106-01-30").getTime)),
> ("3", new java.sql.Date(formate.parse("2016-01-29").getTime)),
> ("4", new java.sql.Date(formate.parse("2016-01-28").getTime)),
> ("5", new java.sql.Date(formate.parse("2016-01-27").getTime))
> ))
> val firstPair = TestDateData.map(x => ( new TestDate(x._1, x._2)))
>
> val TestDateData1 = sc.parallelize(List(
> ("101", new java.sql.Date(formate.parse("2016-01-27").getTime)),
> ("102", new java.sql.Date(formate.parse("2016-01-26").getTime)),
> ("103", new java.sql.Date(formate.parse("2016-01-25").getTime)),
> ("104", new java.sql.Date(formate.parse("2016-01-24").getTime)),
> ("105", new java.sql.Date(formate.parse("2016-01-31").getTime))
> ))
> val secondPair = TestDateData1.map(x => ( new TestDate(x._1, x._2)))
>
> firstPair.toDF.registerTempTable("firstTable")
> secondPair.toDF.registerTempTable("secondTable")
>
> val res = sqlContext.sql("select * from firstTable INNER JOIN
> secondTable on firstTable.loginTime = secondTable.loginTime")
>
>
> I tried the following query,
> sqlContext.sql("select loginTime from firstTable")
> Even this query gives the wrong dates.
>
> Regds,
> --Praseetha
>
> On Tue, Sep 13, 2016 at 6:33 PM, Mich Talebzadeh <
> mich.talebzadeh@gmail.com> wrote:
>
>> Can you send the rdds that just creates those two dates?
>>
>> HTH
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>> On 13 September 2016 at 13:54, Praseetha <pr...@gmail.com> wrote:
>>
>>>
>>> Hi All,
>>>
>>> I have a case class in scala case class TestDate (id: String, loginTime:
>>> java.sql.Date)
>>>
>>> I created 2 RDD's of type TestDate
>>>
>>> I wanted to do an inner join on two rdd's where the values of loginTime
>>> column is equal. Please find the code snippet below,
>>>
>>> firstRDD.toDF.registerTempTable("firstTable")
>>> secondRDD.toDF.registerTempTable("secondTable")
>>> val res = sqlContext.sql("select * from firstTable INNER JOIN secondTable on to_date(firstTable.loginTime) = to_date(secondTable.loginTime)")
>>>
>>> I'm not getting any exception. But i'm not getting correct answer too.
>>> It does a cartesian and some random dates are generated in the result.
>>>
>>>
>>> Regds,
>>> --Praseetha
>>>
>>
>>
>
Re: Unable to compare SparkSQL Date columns
Posted by Praseetha <pr...@gmail.com>.
Hi Mich,
Thanks a lot for your reply.
Here is the sample
case class TestDate (id: String, loginTime: java.sql.Date)
val formate = new SimpleDateFormat("YYYY-MM-DD")
val TestDateData = sc.parallelize(List(
("1", new java.sql.Date(formate.parse("2016-01-31").getTime)),
("2", new java.sql.Date(formate.parse("2106-01-30").getTime)),
("3", new java.sql.Date(formate.parse("2016-01-29").getTime)),
("4", new java.sql.Date(formate.parse("2016-01-28").getTime)),
("5", new java.sql.Date(formate.parse("2016-01-27").getTime))
))
val firstPair = TestDateData.map(x => ( new TestDate(x._1, x._2)))
val TestDateData1 = sc.parallelize(List(
("101", new java.sql.Date(formate.parse("2016-01-27").getTime)),
("102", new java.sql.Date(formate.parse("2016-01-26").getTime)),
("103", new java.sql.Date(formate.parse("2016-01-25").getTime)),
("104", new java.sql.Date(formate.parse("2016-01-24").getTime)),
("105", new java.sql.Date(formate.parse("2016-01-31").getTime))
))
val secondPair = TestDateData1.map(x => ( new TestDate(x._1, x._2)))
firstPair.toDF.registerTempTable("firstTable")
secondPair.toDF.registerTempTable("secondTable")
val res = sqlContext.sql("select * from firstTable INNER JOIN
secondTable on firstTable.loginTime = secondTable.loginTime")
I tried the following query,
sqlContext.sql("select loginTime from firstTable")
Even this query gives the wrong dates.
Regds,
--Praseetha
On Tue, Sep 13, 2016 at 6:33 PM, Mich Talebzadeh <mi...@gmail.com>
wrote:
> Can you send the rdds that just creates those two dates?
>
> HTH
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 13 September 2016 at 13:54, Praseetha <pr...@gmail.com> wrote:
>
>>
>> Hi All,
>>
>> I have a case class in scala case class TestDate (id: String, loginTime:
>> java.sql.Date)
>>
>> I created 2 RDD's of type TestDate
>>
>> I wanted to do an inner join on two rdd's where the values of loginTime
>> column is equal. Please find the code snippet below,
>>
>> firstRDD.toDF.registerTempTable("firstTable")
>> secondRDD.toDF.registerTempTable("secondTable")
>> val res = sqlContext.sql("select * from firstTable INNER JOIN secondTable on to_date(firstTable.loginTime) = to_date(secondTable.loginTime)")
>>
>> I'm not getting any exception. But i'm not getting correct answer too. It
>> does a cartesian and some random dates are generated in the result.
>>
>>
>> Regds,
>> --Praseetha
>>
>
>
Re: Unable to compare SparkSQL Date columns
Posted by Mich Talebzadeh <mi...@gmail.com>.
Can you send the rdds that just creates those two dates?
HTH
Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 13 September 2016 at 13:54, Praseetha <pr...@gmail.com> wrote:
>
> Hi All,
>
> I have a case class in scala case class TestDate (id: String, loginTime:
> java.sql.Date)
>
> I created 2 RDD's of type TestDate
>
> I wanted to do an inner join on two rdd's where the values of loginTime
> column is equal. Please find the code snippet below,
>
> firstRDD.toDF.registerTempTable("firstTable")
> secondRDD.toDF.registerTempTable("secondTable")
> val res = sqlContext.sql("select * from firstTable INNER JOIN secondTable on to_date(firstTable.loginTime) = to_date(secondTable.loginTime)")
>
> I'm not getting any exception. But i'm not getting correct answer too. It
> does a cartesian and some random dates are generated in the result.
>
>
> Regds,
> --Praseetha
>