Johannes Mayer created HIVE-17499:
-------------------------------------
Summary: Hive Cube Operator returns duplicate rows
Key: HIVE-17499
URL: https://issues.apache.org/jira/browse/HIVE-17499
Project: Hive
Issue Type: Bug
Affects Versions: 2.1.0
Environment: Hortonworks HDP 2.6.0.1
Reporter: Johannes Mayer
Priority: Critical
The cube Operator returns duplicate rows, when it shouldnt. I ran the same
query in Pig and got the correct result. (see the example below)
{code:sql}
insert overwrite table thesis.clickstream_export PARTITION (ds_year = '2016' ,
ds_month = '04' , ds_day = '01')
select year(ds), month(ds), day(ds), c8, c11, count(*)
from thesis.clickstream_landing
where ds = '2016-04-01'
group by year(ds), month(ds), day(ds), c8, c11
With Cube;
{code}
Then I check for duplicates:
{code:sql}
select year, month, day, country, city, count (*) from thesis.clickstream_export
where ds_year = '2016' and ds_month = '04' and ds_day = '01'
group by year, month, day, country, city
having count(*) > 1;
{code}
The result is:
year month day country city _c5
null null null null null 4
null null 1 null null 4
null 4 null null null 4
null 4 1 null null 4
2016 null null null null 4
2016 null 1 null null 4
2016 4 null null null 4
2016 4 1 null null 4
When i do the same thing in Pig, everything is fine:
{code:pig}
DATA = LOAD 'thesis.clickstream_landing' USING
org.apache.hive.hcatalog.pig.HCatLoader();
FILTERED = FOREACH DATA GENERATE GetYear(ToDate(ds, 'yyyy-MM-dd')) AS year,
GetMonth(ToDate(ds, 'yyyy-MM-dd')) AS month, GetDay(ToDate(ds, 'yyyy-MM-dd'))
AS day, c8 AS country, c11 AS city;
CUBED = CUBE FILTERED BY CUBE(year, month, day, country, city);
D = FOREACH CUBED GENERATE FLATTEN(group) AS (year, month, day, country, city),
COUNT_STAR(cube) As click_count;
STORE D INTO 'thesis.clickstream_export' USING
org.apache.hive.hcatalog.pig.HCatStorer('ds_year=2016, ds_month=04, ds_day=02');
{code}
Then again I check for duplicates:
{code:sql}
select year, month, day, country, city, count (*) from thesis.clickstream_export
where ds_year = '2016' and ds_month = '04' and ds_day = '02'
group by year, month, day, country, city
having count(*) > 1;
{code}
And the result is empty as it should be.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
