Hive离线选品推荐

Hive离线选品推荐

1.我们先展示一条源数据,以逗号分割
字段意思:商品页数,一级分类,二级分类,三级分类,商品图片,商品名称,商品价格,商品价格范围,商品单位,公司名称,公司网址,地址,"几年老店",标签 字段名：productId,productPage,categoryOne,categoryTwo,categoryThr,productImg,productName,productPrice,productPrices,productUnit,companyName,companyUrl,address,year,tags

1,1,Apparel_Textiles & Accessories,Apparel,Apparel Design Services,//s.alicdn.com/@sc01/kf/H50cfdfc8a6c4445ab6f3a926a2542c00t.jpg_300x300.jpg,embroidery 
digitizing 24h on line,1,$1.00-$2.00,Unit,Shen Zhen Happitoo Textile Co._ Ltd.,//happitoo.en.alibaba.com/company_profile.html#top-nav-bar,China,1,Apparel 
Design Apparel_Textessories Services；

首先在hive创建表名称为"data"

create table data(
    productId string,
    productPage int,
    categoryOne string,
    categoryTwo string,
    categoryThr string,
    productImg string,	
    productName string,
    productPrice string,
    productPrices string,
    productUnit string,
    companyName string,
    companyUrl string,
    address string,
    year int,
    tags string)
    row format delimited fields terminated by ',';

然后我们将本地数据导入到hive中:

load data local inpath '/root/sj.txt' into table data;

查看导入的数据：

select * from data;

创建一个Grade表，用于等级分类调用:

create table Grade()
insert into Grade values("low","middlw","high");

接下来写shell脚本对数据价格进行分类：

#!/usr/bin/env bash
#统计data表中数据总数
#test1=`hive -S -e "select count(*) from data;"`
#echo ${test1}
#对数据进行排名从低到高并且生成到新表aaa_data
test3=`hive -S -e "create table aaa_data as select *,rank() over(order by productPrice asc) as pm from data;"`
echo ${test3}
#计算总数中前百分之30和百分之60的数
test4=`hive -S -e "select count(*)*0.3 from aaa_data;"`
test5=`hive -S -e "select count(*)*0.6 from aaa_data;"`
#将价格分类名称输出到变量
testlow=`hive -S -e "select low from Grade;"`
testmiddlw=`hive -S -e "select middlw from Grade;"`
testhigh=`hive -S -e "select high from Grade;"`
#打印输出
echo ${testlow}
echo ${testmiddlw}
echo ${testhigh}
echo ${test1}
echo ${test4}
echo ${test5}
#test6=`hive -S -e "select a,b,h,case
#    when pm<=${test4} then ${testlow}
#    when pm>=${test4} and pm<${test5} then ${testmiddlw}
#    when pm>=${test5} then ${testhigh}
#    else null end as Praaa from aaa_info,Grade;"`
#echo ${test6}
#创建新表data_output,判断前百分之30为low，百分之30到百分之60为middiw，百分之60以上为high

output=`hive -S -e "create table data_output as select 
productId,productPage,categoryOne,categoryTwo,categoryThr,productImg,productName,productPrice,productPrices,productUnit,companyName,companyUrl,address,year,tags,case
   when pm<=${test4} then ${testlow}
   when pm>=${test4} and pm<${test5} then ${testmiddlw}
   when pm>=${test5} then ${testhigh}
   else null end as Praaa from aaa_data,Grade;"`
echo ${output}