hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gourav Sengupta <gourav.had...@gmail.com>
Subject Fwd: GROUP BY Issue
Date Wed, 12 Jun 2013 12:51:14 GMT
Hi,

I had initially forwarded this request to the user group but am yet to
receive any response.

I will be grateful if someone can help me out in resolving the issue or
pointing out any mistakes that I may be doing.

It took me around 5 to 6 hours to generate the test data of around 20 GB
(or more) and there must be a better alternative.

Regards,
Gourav

---------- Forwarded message ----------
From: Gourav Sengupta <gourav.hadoop@gmail.com>
Date: Mon, Jun 10, 2013 at 4:10 PM
Subject: GROUP BY Issue
To: user@hive.apache.org


Hi,

On running the following query I am getting multiple records with same
value of F1

SELECT F1, COUNT(*)
FROM
(
SELECT F1, F2, COUNT(*)
FROM TABLE1
GROUP BY F1, F2
) a
GROUP BY F1;

As per what I understand there are multiple number of records based on
number of reducers.

Replicating the test scenario:
STEP1: get the dataset as available in
http://snap.stanford.edu/data/amazon0302.html

STEP2: Open the file and delete the heading

STEP3: hadoop fs -mkdir /test

STEP4: hadoop fs -put amazon0302.txt /test

STEP5: create external table test (f1 int, f2 int) row format delimited
fields terminated by '\t' lines terminated by '\n' stored as textfile
location '/test';

STEP6: create table test1 location '/test1' as select left_table.* from
(select * from test where f1<10000) left_table join (select * from test
where f1 < 10000) right_table;

STEP7: hadoop fs -mkdir /test2

STEP8: create table test2 location '/test2' as select f1, count(*) from
(select f1, f2, count(*) from test1 group by f1, f2) a group by f1;

STEP9: select * from test2 where f1 = 9887;

ENVIRONMENT:
HADOOP 2.0.4
HIVE 0.11

Please do let me know whether I am doing anything wrong.


Thanks and Regards,
Gourav Sengupta

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message