On Fri, Aug 20, 2010 at 8:37 AM, Ronak Bhatt <ronakb...@gmail.com> wrote:
> Hi - I've a following SQL query. What is the way to convert it into HIVE
> runnable format -
> Select a.id, count(b.url)
> from a, b
> where a.id = b.id
> and (b.url like '%google.com%' or b.url like '%h.google.com%' or b.url like
> '%bing%')
> and a.exec_date = date(now()) ; ----- get today's data
> group by a.id
>
> thanks, ronak
>
I think something like:
SELECT a.id, COUNT(distinct b.url)
FROM a
JOIN b on a.id = b.id
WHERE
(b.url LIKE '%google.com%' OR b.url LIKE '%h.google.com%' OR
b.url LIKE '%bing%')
AND a.exec_date = from_unixtime(unix_timestamp())
GROUP BY a.id;
should work, but I didn't test it. You will probably need to format
the data string to match your data - hive doesn't really have a date
type as such - but instead has date manipulation functions for
strings.
Take a look at
http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF#Date_Functions
Jacob Rideout
