hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ricardo Birmele <Ricardo.Birm...@microsoft.com>
Subject RE: Help with query
Date Fri, 06 Jun 2014 18:04:46 GMT
One quick suggestion…if you modify the query to this:

SELECT COUNT(DISTINCT ip) AS IP_count, IP, query_string
FROM myTable
GROUP BY IP, query_string
ORDER BY count DESC;

…then you should see each IP along with its count.
FWIW,
__Birm

Ricardo Birmele, CISSP
Senior Security Technologist
Microsoft IT Security Operations
• | •••• | •

[Microsoft Logo]

From: Petter von Dolwitz (Hem) [mailto:petter.von.dolwitz@gmail.com]
Sent: Thursday, 5 June, 2014 01:33
To: user@hive.apache.org
Subject: Re: Help with query


I tried to answer this earlier. Maybe the mail got suck somewhere. I'll try again.

SELECT COUNT(DISTINCT ip) AS count, query_string
FROM myTable
GROUP BY query_string
ORDER BY count DESC;

This should give you one row per asked query. Rows are ordered by number of unique IPs that
asked the query. Most popular first.

Br,
Petter

2014-05-30 1:29 GMT+02:00 Software Dev <static.void.dev@gmail.com<mailto:static.void.dev@gmail.com>>:
We have a table with user entered queries, their IP. How could we
write a query that will count and order queries by their count having
a unique IP count > X. For example if we had the same IP enter the
same query Y times we wouldnlt want to include this in the final
result unless there have been X-Y other IP's that searched for that
query.

Is this perhaps better suited fro Pig?

Thanks

Mime
View raw message