hive ETL之电商零售行业-推荐系统sql

-- case3 ----========== f_orders ==========--/*11    2014-05-01 06:01:12.334+01    10703007267488    item8:2|item1:122    2014-05-01 07:28:12.342+01    10101043505096    item6:3|item3:233    2014-05-01 07:50:12.33+01    10103043509747    item7:711    2014-05-01 09:27:12.33+01    10103043501575    item5:5|item1:1|item4:1|item9:122    2014-05-01 09:03:12.324+01    10104043514061    item1:333    2014-05-02 19:10:12.343+01    11003002067594    item4:2|item1:111    2014-05-02 09:07:12.344+01    10101043497459    item9:135    2014-05-03 11:07:12.339+01    10203019269975    item5:1|item1:1789    2014-05-03 12:59:12.743+01    10401003346256    item7:3|item8:2|item9:177    2014-05-03 18:04:12.355+01    10203019262235    item5:2|item1:199    2014-05-04 00:36:39.713+01    10103044681799    item9:3|item1:133    2014-05-04 19:10:12.343+01    12345678901234    item5:1|item1:111    2014-05-05 09:07:12.344+01    12345678901235    item6:1|item1:135    2014-05-05 11:07:12.339+01    12345678901236    item5:2|item1:122    2014-05-05 12:59:12.743+01    12345678901237    item9:3|item1:177    2014-05-05 18:04:12.355+01    12345678901238    item8:3|item1:199    2014-05-05 20:36:39.713+01    12345678901239    item9:3|item1:1*/CREATE EXTERNAL TABLE f_orders (    user_id   STRING  , ts        STRING  , order_id  STRING  , items     map)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '\t'COLLECTION ITEMS TERMINATED BY '|'MAP KEYS TERMINATED BY ':'LOCATION '/tmp/db_case3/f_orders';select * from f_orders where array_contains(map_keys(items), 'item8');select user_id, order_id, item, amount from f_orders LATERAL VIEW explode(items) t AS item, amount;--========== d_items ==========--/*item1    100.2    catalogA|catalogD|catalogXitem2    200.3    catalogAitem3    300.4    catalogA|catalogXitem4    400.5    catalogBitem5    500.6    catalogB|catalogXitem6    600.7    catalogBitem7    700.8    catalogCitem8    800.9    catalogC|catalogDitem9    899.99    catalogC|catalogA*/CREATE EXTERNAL TABLE d_items (  item_sku  STRING,  price     DOUBLE,  catalogs  array)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '\t'COLLECTION ITEMS TERMINATED BY '|'LOCATION '/tmp/db_case3/d_items';select orders.user_id, orders.order_id, round(sum(d.price*orders.amount), 2) as order_pricefrom (  select user_id, order_id, item, amount from f_orders LATERAL VIEW explode(items) t AS item, amount) ordersjoin d_items don (orders.item = d.item_sku)group by orders.user_id, orders.order_id;select orders.user_id, orders.item, orders.amount, catalogs.catalogfrom (  select user_id, item, amount from f_orders LATERAL VIEW explode(items) t AS item, amount) ordersjoin (  select item_sku, catalog from d_items LATERAL VIEW explode(catalogs) t AS catalog) catalogson (orders.item = catalogs.item_sku);select user_id, catalog, weight, row_number() OVER (PARTITION BY user_id ORDER BY weight DESC) as row_num FROM usr_cat_weight where user_id < '33';select user_id, catalog, weight, rank() OVER (PARTITION BY user_id ORDER BY weight DESC) as rnk FROM usr_cat_weight where user_id < '33';select user_id, catalog, weight, dense_rank() OVER (PARTITION BY user_id ORDER BY weight DESC) as drnk FROM usr_cat_weight where user_id < '33';CREATE TABLE usr_cat ASselect user_id, catalog, row_number() OVER (PARTITION BY user_id ORDER BY weight DESC) as row_numFROM (select orders.user_id, catalogs.catalog, sum(orders.amount) as weightfrom (  select user_id, item, amount from f_orders LATERAL VIEW explode(items) t AS item, amount) ordersjoin (  select item_sku, catalog from d_items LATERAL VIEW explode(catalogs) t AS catalog) catalogson (orders.item = catalogs.item_sku)group by orders.user_id, catalogs.catalogorder by user_id, weight) xORDER BY user_id, row_num;select user_id, group_concat(catalog, '|') from usr_cat where row_num < 3 group by user_id;--========== d_users ==========--/*11;m;1981-01-01;user11@gmail.com;2014-04-2122;w;1982-01-01;user22@abcn.net;2014-04-2233;m;1983-01-01;user33@fxlive.de;2014-04-2377;w;1977-01-01;user77@fxlive.fr;2014-05-0188;m;1988-01-01;user88@fxlive.eu;2014-05-0299;w;1999-01-01;user99@abcn.net;2014-05-03789;m;2008-01-01;admin@abcn.net;2014-05-03*/CREATE EXTERNAL TABLE d_users (    user_id  STRING  , gender   STRING  , birthday STRING  , email    STRING  , regday   STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\073'LOCATION '/tmp/db_case3/d_users';select user_id, birthday, translate(birthday, '0123456789', '1234567890'), email, translate(email, 'userfxgmail1234567890', '1234567890userfxgmail') from d_users;CREATE TABLE user_segment ASselect c.user_id, u.gender, u.age, c.catalogsfrom (  select user_id, group_concat(catalog, '|') as catalogs from usr_cat where row_num < 3 group by user_id) cleft outer join (  select user_id, gender, year(now()) - cast(substr(birthday, 1, 4) as int) as age from d_users) uon (c.user_id = u.user_id);-- 也可以用impala做一些准备工作CREATE EXTERNAL TABLE f_orders_string (    user_id   STRING  , ts        STRING  , order_id  STRING  , items     STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'LOCATION '/tmp/db_case3/f_orders';select u.user_id     , u.gender     , o.ordersfrom d_users uleft outer join (  select user_id, group_concat(order_id, '|') as orders  from f_orders_string  where ts > '2014-05-02'  group by user_id) oon (u.user_id = o.user_id);select o.user_id     , u.gender     , o.ordersfrom (  select user_id, group_concat(order_id, '|') as orders  from f_orders_string  where ts > '2014-05-02'  group by user_id) oleft outer join d_users uon (o.user_id = u.user_id);-- Hive / Impala JDBC 及中文支持问题beeline -u "jdbc:hive2://itr-hbasetest01:10000/"sudo vi /opt/cloudera/parcels/CDH/lib/hive/bin/hivebeeline -u "jdbc:hive2://itr-hbasetest02:21050/;auth=noSasl"