You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by LinQili <li...@outlook.com> on 2015/04/02 05:18:29 UTC

Incorrect results of spark sql on hive select

Hi all:I was using spark sql on hive for doing some statistics. While using spark sql, I found that, sometimes, spark-sql got the incorrect results while select. And if I executed the same select statement again, it returned the different results which was correct. That is saying, sometime, with the same input and the same select logic, the select result is different. That is so weird!Is there any hints? Thanks.
val selectJoin =
      s"""
        | insert into table $tblLoginLogStatDay
        | select '$statDate' as date, '$USER_TYPE' as user_type, a.platform, a.channel, a.product_version,
        | coalesce(c.total_user_90,0), a.total_user, coalesce(b.one_time_startup, 0), a.total_startup, coalesce(f.dau,0), coalesce(d.wau,0), coalesce(e.mau,0)
        | from
        |     (select platform, channel, product_version, count(distinct $userType) as total_user, sum(login_cnt) as total_startup
        |         from $tblNewTable
        |         group by platform, channel, product_version) a
        |
        |     left outer join
        |
        |     (select platform, channel, product_version, count(distinct $userType) as one_time_startup
        |         from $tblNewTable
        |         where login_cnt = 1
        |         group by platform, channel, product_version) b
        |     on a. platform = b.platform and a.channel = b.channel and a.product_version = b.product_version
        |
        |     left outer join
        |
        |     (select platform, channel, product_version, count(distinct $userType) as total_user_90
        |         from $tblNewTable90
        |         group by platform, channel, product_version) c
        |     on a.platform = c.platform and a.channel = c.channel and a.product_version = c.product_version
        |
        |     left outer join
        |
        |     (select platform, channel, product_version, count(distinct $userType) as wau
        |         from $tblNewTable90
        |         where  from_unixtimestamp_to_date(last_time) >= date_sub('$statDate', 6)
        |         group by platform, channel, product_version) d
        |     on a.platform = d.platform and a.channel = d.channel and a.product_version = d.product_version
        |
        |     left outer join
        |
        |     (select platform, channel, product_version, count(distinct $userType) as mau
        |         from $tblNewTable90
        |         where  from_unixtimestamp_to_date(last_time) >= date_sub('$statDate', 29)
        |         group by platform, channel, product_version) e
        |     on a.platform = e.platform and a.channel = e.channel and a.product_version = e.product_version
        |
        |     left outer join
        |
        |     (select platform, channel, product_version, count(distinct $userType) as dau
        |        from $tblNewTable90
        |        where from_unixtimestamp_to_date(last_time) = '$statDate'
        |        group by platform, channel, product_version) f
        |     on a.platform = f.platform and a.channel = f.channel and a.product_version = f.product_version
      """.stripMargin

spark version: 1.2.0
hive version: 0.10.0-cdh4.2.1
​