hive 实战笔记case2

– case2 –

–========== click_log ==========–

/*
11  ad_101  2014-05-01 06:01:12.334+01
22  ad_102  2014-05-01 07:28:12.342+01
33  ad_103  2014-05-01 07:50:12.33+01
11  ad_104  2014-05-01 09:27:12.33+01
22  ad_103  2014-05-01 09:03:12.324+01
33  ad_102  2014-05-02 19:10:12.343+01
11  ad_101  2014-05-02 09:07:12.344+01
35  ad_105  2014-05-03 11:07:12.339+01
22  ad_104  2014-05-03 12:59:12.743+01
77  ad_103  2014-05-03 18:04:12.355+01
99  ad_102  2014-05-04 00:36:39.713+01
33  ad_101  2014-05-04 19:10:12.343+01
11  ad_101  2014-05-05 09:07:12.344+01
35  ad_102  2014-05-05 11:07:12.339+01
22  ad_103  2014-05-05 12:59:12.743+01
77  ad_104  2014-05-05 18:04:12.355+01
99  ad_105  2014-05-05 20:36:39.713+01
*/

CREATE EXTERNAL TABLE click_log (
    cookie_id  STRING
  , ad_id      STRING
  , ts         STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/tmp/db_case2/click_log';

select cookie_id, collect_set(ad_id) as orders
from click_log
--where ts > '2014-05-02'
group by cookie_id;

select cookie_id, group_concat(ad_id, '|') as orders
from click_log
--where ts > '2014-05-02'
group by cookie_id;

–========== ad_list ==========–

/*
ad_101  http://abcn.net/    catalog8|catalog1
ad_102  http://www.abcn.net/    catalog6|catalog3
ad_103  http://fxlive.de/   catalog7
ad_104  http://fxlive.fr/   catalog5|catalog1|catalog4|catalog9
ad_105  http://fxlive.eu/   
*/

CREATE EXTERNAL TABLE ad_list (
    ad_id    STRING
  , url      STRING
  , catalogs array<STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '|'
LOCATION '/tmp/db_case2/ad_list';

CREATE EXTERNAL TABLE ad_list_string (
   ad_id    STRING
 , url      STRING
 , catalogs STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/tmp/db_case2/ad_list';

select click.cookie_id, click.ad_id, click.amount, ad_list_string.catalogs as orders
from (
  select cookie_id, ad_id, count(1) as amount from click_log group by cookie_id, ad_id
) click
join ad_list_string
on (ad_list_string.ad_id = click.ad_id);


select ad_id, catalog from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog;
select ad_id, collect_set(catalog) from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog group by ad_id;


select click.cookie_id, ad.catalog from click_log click
left outer join (
  select ad_id, catalog from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog
) ad
on (click.ad_id = ad.ad_id);


create table cookie_cats as
select click.cookie_id, ad.catalog, count(1) as weight from click_log click
left outer join (
  select ad_id, catalog from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog
) ad
on (click.ad_id = ad.ad_id)
group by click.cookie_id, ad.catalog
order by cookie_id, weight desc;

select cookie_id, collect_set(catalog) from cookie_cats group by cookie_id; -- where catalog is not null
select cookie_id, group_concat(catalog, '|') from cookie_cats group by cookie_id; -- impala group_concat