Hive ETL's e-commerce retail industry-recommendation system sql

-- case3-= f_orders =-/ * 11 2014-05-01 06item8:2 01item8:2 12.3341901 10703007267488 item8:2 | item1:122 2014-05-01 07V 28item8:2 12.342Z01 10101043505096 item6:3 | item3:233 2014-05-01 07V 5050 item1 12.3301 103043509747 2014-05-01 09Switzerland 2712.33mm 0103043501575 item5:5 | item1:1 | item4:1 | item9:122 2014-05-01 09RU 0312.324 item1 : 333 2014-05-02 19item9:135 2014-05-02 19item9:135 2014-05-02 11003002067594 item7:3 | item9:177 2014-05-02 09 item9:135 2014-05-02 09 item9:135 2014-05-03 1111 item9:135 2014-05-03 1933-05 012 item1:1789 2014-05 012 item1:1789 2014-05-03 12 Swiss item9:135 5912.743001 10401003346256 item7:3 | item8:2 | item9:177 2014-05-03 1814 0418 04UR 12.3551401 10203019262235 item5:2 | item1:199 2014-05-04 0036 39.71301 10103044681799 item9:3 | item1:133 2014-05-04 19item5:1 10item5:1 12345678901234 item5:1 | item1:111 2014-05-05 09item5:1 07item5:1 12.3440112345678901235 item6:1 | item1:135 2014-05 111414511433 0112345678901236 item5:2 | item1:122 2014-05 1214140514345678901237 item9:3 | item1:177 2014-05-05 1814 0414 12.35501 12345678901238 | item1:199 2014-05-05 : 36 user_id STRING 39.7130112345678901239 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, catalogfrom 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 ordersfrom 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 and Chinese support questions 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"