how to group by data from hive with specific partition?

Question

I have the following:

hive>show partitions TABLENAME                                                           
pt=2012.07.28.08                                                                         
pt=2012.07.28.09                                                                         
pt=2012.07.28.10                                                                         
pt=2012.07.28.11                                                                         
hive> select pt,count(*) from TABLENAME group by pt;                                     
OK

Why can't the group by get the data?

what is the result of `select count(*) from TABLENAME`? are you sure you have data in the partitions? — dimamah, Oct 30 '13 at 18:53
I suppose there is data, otherwise partitions cant be created, am i correct? — Raja Reddy, Nov 04 '13 at 15:53

score 2 · Answer 1 · answered Nov 05 '13 at 09:52

2

Check if the hive.mapred.mode is set to "strict", if so it'll not allow all partitions to scan for the submitted query. You can set it to nonstrict as below:

hive>set hive.mapred.mode=nonstrict;

I'm not sure whether this caused NO results out of your query, but trying to address it. Do share the results.

Note: You can check the default value for this parameter in hive-default.xml

answered Nov 05 '13 at 09:52

Raja Reddy

772
8
19
37

Thank you.I set hive.mapred.mode =strict and then fire query (select pt,count(*) from Table group by pt) but i got error like AILED: SemanticException org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: Read timed out – user2935539 Nov 12 '13 at 10:44

score 1 · Answer 2 · edited Feb 08 '16 at 11:38

You can always achive the same using 2 select statements . For ex

Create table table1(
session_id string,
page_id string
)
partitioned by (metrics_date string);

Consider we are have loaded table for 2 partitions

hive>show partitions table1
metrics_date=2012.07.28.08
metrics_date=2012.07.28.09

select * from table1 ;

1212121212    google.com     2012.07.28.08 

1212121212    google.com     2012.07.28.09`

Getting number of rows per partition

select metrics_date,count(*) from (
select * from table1 ) temp
group by metrics_date;

score 0 · Answer 3 · answered Feb 08 '16 at 10:43

0

To get whole results along with group by ,You can use the below query.

SELECT pt,count(*) OVER (PARTITION BY pt) FROM TABLENAME;

This can be achiened through partition by.

answered Feb 08 '16 at 10:43

USB

6,019
15
62
93

Getting `HiveSQLException: Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.` – Omar Ali May 04 '18 at 09:54

how to group by data from hive with specific partition?

3 Answers3