みてわかる クラウド マガジン Vol.3の特集4をHiveQLで一部書いてみた

レコメンデーションで似ている人を出すHiveQL

select
 *
from
(
  select
    dis.user as user,
    dis.sim as sim
   from
   (
     select
       rec.user as user,
       sqrt(sum(rec.diff*rec.diff)) as sim
     from
     (
       select
         o_table.user as user,
         o_table.menu as menu,
         if(a_table.menu is null, "NONE", o_table.ratio - a_table.ratio) as diff
       from
       (
         select
           *
         from
         (
           select
             food_cnt.user as user,
             food_cnt.menu as menu,
             food_cnt.cnt/user_total.total as ratio
           from
           (
             select
               user as user,
               count(*) as total
             from
               cafeteriahistory201007
             group by
               user
           )user_total

           join

           (
             select
               user as user,
               menu as menu,
               count(1) as cnt
             from
               cafeteriahistory201007
             group by
               user,
               menu
           )food_cnt

           on
             user_total.user=food_cnt.user
         )tab1
         where
           tab1.user != 'A'
       )o_table

       left outer join

       (
         select
           *
         from
         (
           select
             food_cnt.user as user,
             food_cnt.menu as menu,
             food_cnt.cnt/user_total.total as ratio
           from
           (
             select
               user as user,
               count(*) as total
             from
               cafeteriahistory201007
             group by
               user
           )user_total

           join

           (
             select
               user as user,
               menu as menu,
               count(1) as cnt
             from
               cafeteriahistory201007
             group by
               user,
               menu
           )food_cnt

         on
           user_total.user=food_cnt.user
       )tab2
       where
         tab2.user == 'A'
     )a_table
   on
     a_table.menu = o_table.menu
   )rec
      where
        rec.diff != 'NONE'
      group by
        rec.user
  )dis
    where
      dis.sim < 0.1
)sim
;

実行するとこんな感じ

C 0.045549532874995705
E 0.047382490568478285

みてわかるクラウドマガジン Vol.3
http://itpro.nikkeibp.co.jp/article/MAG/20110707/362179/

サンプルコード
http://itpro.nikkeibp.co.jp/article/MAG/20110613/361307/hadoop_sample.tar.gz