Hive ETL Advertising Industry-user behavior Classification sql
-case2-case2-case2 Ad_101 2014-05-02 09 ad_104 2014-05-02 09 ad_104 2014-05-03 12 59 ad_104 12.7435.0177 ad_103 2014-05-03 18 04V 0199 ad_102 2014-05-04 0036 ad_102 39.713mm 0133 ad_101 2014-05-04 19ad_103 1012.343mm 0111 ad_101 2014-05-05-05 09Rd 07purl 12.344mm 0135 ad _ 102 2014-05-05 11 ad_105 07 ad_105 12.3394th 0122 ad_103 2014-05-05 1212 ad_105 2014-05-05 12 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 ordersfrom click_log--where ts > '2014-05-02'group by cookie_id;select cookie_id, group_concat (ad_id,' |') as ordersfrom click_log--where ts > '2014-05-02'group by cookie_id -- = ad_list =-/ * ad_101 http://abcn.net/ catalog8 | catalog1ad_102 http://www.abcn.net/ catalog6 | catalog3ad_103 http://fxlive.de/ catalog7ad_104 http://fxlive.fr/ catalog5 | catalog1 | catalog4 | catalog9ad_105 http://fxlive.eu/ * / CREATE EXTERNAL TABLE ad_list (ad_id STRING, url STRING Catalogs array) ROW FORMAT DELIMITEDFIELDS 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 ordersfrom (select cookie_id, ad_id, count (1) as amount from click_log group by cookie_id, ad_id) clickjoin ad_list_stringon (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 clickleft outer join (select ad_id, catalog from ad_list LATERAL VIEW OUTER explode (catalogs) t AS catalog) adon (click.ad_id = ad.ad_id) Create table cookie_cats asselect click.cookie_id, ad.catalog, count (1) as weight from click_log clickleft outer join (select ad_id, catalog from ad_list LATERAL VIEW OUTER explode (catalogs) t AS catalog) adon (click.ad_id = ad.ad_id) group by click.cookie_id, ad.catalogorder by cookie_id, weight desc;select cookie_id, collect_set (catalog) from cookie_cats group by cookie_id -- where catalog is not nullselect cookie_id, group_concat (catalog,'|) from cookie_cats group by cookie_id;-- impala group_concat
