SparkSQL电商用户画像(五)之用户画像开发(客户基本属性表)

7.1用户画像–数据开发的步骤
u 数据开发前置依赖

-需求确定 pv uv topn

-建模确定表结构 create table t1(pv int,uv int,topn string)

-实现方案确定

u 数据开发过程

-表落地

-写sql语句实现业务逻辑

-部署代码

-数据测试

-试运行与上线

在接下来的客户基本属性表开发中演示开发的流程。

7.2 用户画像开发–客户基本属性表
复制代码
–用户画像-客户基本属性模型表
create database if not exists gdm;
create table if not exists gdm.itcast_gdm_user_basic(
user_id string ,–用户ID
user_name string ,–用户登陆名
user_sex string ,–用户性别
user_birthday string ,–用户生日
user_age bigint ,–用户年龄
constellation string ,–用户星座
province string ,–省份
city string ,–城市
city_level string ,–城市等级
hex_mail string ,–邮箱
op_mail string ,–邮箱运营商
hex_phone string ,–手机号
fore_phone string ,–手机前3位
op_phone string ,–手机运营商
add_time timestamp ,–注册时间
login_ip string ,–登陆ip地址
login_source string ,–登陆来源
request_user string ,–邀请人
total_mark bigint ,–会员积分
used_mark bigint ,–已使用积分
level_name string ,–会员等级名称
blacklist bigint ,–用户黑名单
is_married bigint ,–婚姻状况
education string ,–学历
monthly_money double ,–收入
profession string ,–职业
sex_model bigint ,–性别模型
is_pregnant_woman bigint ,–是否孕妇
is_have_children bigint ,–是否有小孩
children_sex_rate double ,–孩子性别概率
children_age_rate double ,–孩子年龄概率
is_have_car bigint ,–是否有车
potential_car_user_rate double ,–潜在汽车用户概率
phone_brand string ,–使用手机品牌
phone_brand_level string ,–使用手机品牌档次
phone_cnt bigint ,–使用多少种不同的手机
change_phone_rate bigint ,–更换手机频率
majia_flag string ,–马甲标志
majie_account_cnt bigint ,–马甲账号数量
loyal_model bigint ,–用户忠诚度
shopping_type_model bigint ,–用户购物类型
figure_model bigint ,–身材
stature_model bigint ,–身高
dw_date timestamp
) partitioned by (dt string);
复制代码
该模型表其基本信息主要来源于用户表、用户调查表。有静态信息和动态信息、后面的一些是数据挖掘模型（数据挖掘模型比较多，逻辑比较复杂，在机器学习课程中给大家介绍）。

复制代码
#***************************
–客户基本属性模型表BDM层
create database if not exists bdm;
create external table if not exists bdm.itcast_bdm_user(
user_id string ,–用户ID
user_name string ,–用户登陆名
user_sex string ,–用户性别
user_birthday string ,–用户生日
user_age bigint ,–用户年龄
constellation string ,–用户星座
province string ,–省份
city string ,–城市
city_level string ,–城市等级
hex_mail string ,–邮箱
op_mail string ,–邮箱运营商
hex_phone string ,–手机号
fore_phone string ,–手机前3位
op_phone string ,–手机运营商
add_time string ,–注册时间
login_ip string ,–登陆ip地址
login_source string ,–登陆来源
request_user string ,–邀请人
total_mark bigint ,–会员积分
used_mark bigint ,–已使用积分
level_name string ,–会员等级名称
blacklist bigint ,–用户黑名单
is_married bigint ,–婚姻状况
education string ,–学历
monthly_money double ,–收入
profession string --职业
) partitioned by (dt string)
row format delimited fields terminated by ‘,’;
alter table itcast_bdm_user add partition (dt=‘2017-01-01’) location ‘/business/itcast_bdm_user/2017-01-01’;
–客户基本属性表FDM层
create database if not exists fdm;
create table if not exists fdm.itcast_fdm_user_wide(
user_id string ,–用户ID
user_name string ,–用户登陆名
user_sex string ,–用户性别
user_birthday string ,–用户生日
user_age bigint ,–用户年龄
constellation string ,–用户星座
province string ,–省份
city string ,–城市
city_level string ,–城市等级
hex_mail string ,–邮箱
op_mail string ,–邮箱运营商
hex_phone string ,–手机号
fore_phone string ,–手机前3位
op_phone string ,–手机运营商
add_time string ,–注册时间
login_ip string ,–登陆ip地址
login_source string ,–登陆来源
request_user string ,–邀请人
total_mark bigint ,–会员积分
used_mark bigint ,–已使用积分
level_name string ,–会员等级名称
blacklist bigint ,–用户黑名单
is_married bigint ,–婚姻状况
education string ,–学历
monthly_money double ,–收入
profession string ,–职业
dw_date timestamp
) partitioned by (dt string);
–加载数据
insert overwrite table fdm.itcast_fdm_user_wide partition(dt=‘2017-01-01’)
select
t.user_id,
t.user_name,
t.user_sex,
t.user_birthday,
t.user_age,
t.constellation,
t.province,
t.city,
t.city_level,
t.hex_mail,
t.op_mail,
t.hex_phone,
t.fore_phone,
t.op_phone,
t.add_time,
t.login_ip,
t.login_source,
t.request_user,
t.total_mark,
t.used_mark,
t.level_name,
t.blacklist,
t.is_married,
t.education,
t.monthly_money,
t.profession,
from_unixtime(unix_timestamp()) dw_date
from bdm.itcast_bdm_user t where dt=‘2017-01-01’;
–用户画像-客户基本属性模型表GDM层
create database if not exists gdm;
create table if not exists gdm.itcast_gdm_user_basic(
user_id string ,–用户ID
user_name string ,–用户登陆名
user_sex string ,–用户性别
user_birthday string ,–用户生日
user_age bigint ,–用户年龄
constellation string ,–用户星座
province string ,–省份
city string ,–城市
city_level string ,–城市等级
hex_mail string ,–邮箱
op_mail string ,–邮箱运营商
hex_phone string ,–手机号
fore_phone string ,–手机前3位
op_phone string ,–手机运营商
add_time string ,–注册时间
login_ip string ,–登陆ip地址
login_source string ,–登陆来源
request_user string ,–邀请人
total_mark bigint ,–会员积分
used_mark bigint ,–已使用积分
level_name string ,–会员等级名称
blacklist bigint ,–用户黑名单
is_married bigint ,–婚姻状况
education string ,–学历
monthly_money double ,–收入
profession string ,–职业
sex_model bigint ,–性别模型
is_pregnant_woman bigint ,–是否孕妇
is_have_children bigint ,–是否有小孩
children_sex_rate double ,–孩子性别概率
children_age_rate double ,–孩子年龄概率
is_have_car bigint ,–是否有车
potential_car_user_rate double ,–潜在汽车用户概率
phone_brand string ,–使用手机品牌
phone_brand_level string ,–使用手机品牌档次
phone_cnt bigint ,–使用多少种不同的手机
change_phone_rate bigint ,–更换手机频率
majia_flag string ,–马甲标志
majie_account_cnt bigint ,–马甲账号数量
loyal_model bigint ,–用户忠诚度
shopping_type_model bigint ,–用户购物类型
figure_model bigint ,–身材
stature_model bigint ,–身高
dw_date timestamp
) partitioned by (dt string);
–加载数据
insert overwrite table gdm.itcast_gdm_user_basic partition(dt=‘2017-01-01’)
select
t.user_id,
t.user_name,
t.user_sex,
t.user_birthday,
t.user_age,
t.constellation,
t.province,
t.city,
t.city_level,
t.hex_mail,
t.op_mail,
t.hex_phone,
t.fore_phone,
t.op_phone,
t.add_time,
t.login_ip,
t.login_source,
t.request_user,
t.total_mark,
t.used_mark,
t.level_name,
t.blacklist,
t.is_married,
t.education,
t.monthly_money,
t.profession,
null sex_model,–数据挖掘模型-开始
null is_pregnant_woman,
null is_have_children,
null children_sex_rate,
null children_age_rate,
null is_have_car,
null potential_car_user_rate,
null phone_brand,
null phone_brand_level,
null phone_cnt,
null change_phone_rate,
null majia_flag,
null majie_account_cnt,
null loyal_model,
null shopping_type_model,
null figure_model,
null stature_model,–数据挖掘模型-结束
from_unixtime(unix_timestamp()) dw_date
from (select * from fdm.itcast_fdm_user_wide where dt=‘2017-01-01’) t;
复制代码
itcast_gdm_user_basic.sh

复制代码
演示模型表开发脚本：
######################
#名称：客户基本属性模型表

itcast_gdm_user_basic.sh

######################
#!/bin/sh
yesterday=date -d '-1 day' "+%Y-%m-%d"
if [ $1 ];then
yesterday=$1fiSPAR{K}_{S}UBMI{T}_{I}NFO="/export/servers/spark/bin/spark-sql--masterspark://node1:7077--executor-memory1g--total-executor-cores2--confspark.sql.warehouse.dir=hdfs://node1:9000/user/hive/warehouse"SOURC{E}_{D}ATA="/root/sourc{e}_{d}ata"SQ{L}_{B}DM="createdatabaseifnotexistsbdm;createexternaltableifnotexistsbdm.itcas{t}_{b}d{m}_{u}ser(use{r}_{i}dstring,--用户IDuse{r}_{n}amestring,--用户登陆名use{r}_{s}exstring,--用户性别use{r}_{b}irthdaystring,--用户生日use{r}_{a}gebigint,--用户年龄constellationstring,--用户星座provincestring,--省份citystring,--城市cit{y}_{l}evelstring,--城市等级he{x}_{m}ailstring,--邮箱o{p}_{m}ailstring,--邮箱运营商he{x}_{p}honestring,--手机号for{e}_{p}honestring,--手机前3位o{p}_{p}honestring,--手机运营商ad{d}_{t}imestring,--注册时间logi{n}_{i}pstring,--登陆ip地址logi{n}_{s}ourcestring,--登陆来源reques{t}_{u}serstring,--邀请人tota{l}_{m}arkbigint,--会员积分use{d}_{m}arkbigint,--已使用积分leve{l}_{n}amestring,--会员等级名称blacklistbigint,--用户黑名单i{s}_{m}arriedbigint,--婚姻状况educationstring,--学历monthl{y}_{m}oneydouble,--收入professionstring--职业)partitionedby(dtstring)rowformatdelimitedfieldsterminatedb{y}^{\prime }{,}^{\prime }locatio{n}^{\prime }/business/bdm/itcas{t}_{b}d{m}_{u}se{r}^{\prime };altertablebdm.itcas{t}_{b}d{m}_{u}seraddpartition(dt{=}^{\prime }$yesterday’);"
SQL_FDM=“create database if not exists fdm;
create table if not exists fdm.itcast_fdm_user_wide(
user_id string ,–用户ID
user_name string ,–用户登陆名
user_sex string ,–用户性别
user_birthday string ,–用户生日
user_age bigint ,–用户年龄
constellation string ,–用户星座
province string ,–省份
city string ,–城市
city_level string ,–城市等级
hex_mail string ,–邮箱
op_mail string ,–邮箱运营商
hex_phone string ,–手机号
fore_phone string ,–手机前3位
op_phone string ,–手机运营商
add_time string ,–注册时间
login_ip string ,–登陆ip地址
login_source string ,–登陆来源
request_user string ,–邀请人
total_mark bigint ,–会员积分
used_mark bigint ,–已使用积分
level_name string ,–会员等级名称
blacklist bigint ,–用户黑名单
is_married bigint ,–婚姻状况
education string ,–学历
monthly_money double ,–收入
profession string ,–职业
dw_date timestamp
) partitioned by (dt string);”
##加载数据
LOAD_FDM="
insert overwrite table fdm.itcast_fdm_user_wide partition(dt=‘$yesterda{y}^{\prime })selectt.use{r}_{i}d,t.use{r}_{n}ame,t.use{r}_{s}ex,t.use{r}_{b}irthday,t.use{r}_{a}ge,t.constellation,t.province,t.city,t.cit{y}_{l}evel,t.he{x}_{m}ail,t.o{p}_{m}ail,t.he{x}_{p}hone,t.for{e}_{p}hone,t.o{p}_{p}hone,t.ad{d}_{t}ime,t.logi{n}_{i}p,t.logi{n}_{s}ource,t.reques{t}_{u}ser,t.tota{l}_{m}ark,t.use{d}_{m}ark,t.leve{l}_{n}ame,t.blacklist,t.i{s}_{m}arried,t.education,t.monthl{y}_{m}oney,t.profession,fro{m}_{u}nixtime(uni{x}_{t}imestamp())d{w}_{d}atefrombdm.itcas{t}_{b}d{m}_{u}sertwheredt{=}^{\prime }$yesterday’;"
SQL_GDM=“create database if not exists gdm;
create table if not exists gdm.itcast_gdm_user_basic(
user_id string ,–用户ID
user_name string ,–用户登陆名
user_sex string ,–用户性别
user_birthday string ,–用户生日
user_age bigint ,–用户年龄
constellation string ,–用户星座
province string ,–省份
city string ,–城市
city_level string ,–城市等级
hex_mail string ,–邮箱
op_mail string ,–邮箱运营商
hex_phone string ,–手机号
fore_phone string ,–手机前3位
op_phone string ,–手机运营商
add_time string ,–注册时间
login_ip string ,–登陆ip地址
login_source string ,–登陆来源
request_user string ,–邀请人
total_mark bigint ,–会员积分
used_mark bigint ,–已使用积分
level_name string ,–会员等级名称
blacklist bigint ,–用户黑名单
is_married bigint ,–婚姻状况
education string ,–学历
monthly_money double ,–收入
profession string ,–职业
sex_model bigint ,–性别模型
is_pregnant_woman bigint ,–是否孕妇
is_have_children bigint ,–是否有小孩
children_sex_rate double ,–孩子性别概率
children_age_rate double ,–孩子年龄概率
is_have_car bigint ,–是否有车
potential_car_user_rate double,–潜在汽车用户概率
phone_brand string ,–使用手机品牌
phone_brand_level string ,–使用手机品牌档次
phone_cnt bigint ,–使用多少种不同的手机
change_phone_rate bigint ,–更换手机频率
majia_flag string ,–马甲标志
majie_account_cnt bigint ,–马甲账号数量
loyal_model bigint ,–用户忠诚度
shopping_type_model bigint ,–用户购物类型
figure_model bigint ,–身材
stature_model bigint ,–身高
dw_date timestamp
) partitioned by (dt string);”
##加载数据到GDM
LOAD_GDM=“insert overwrite table gdm.itcast_gdm_user_basic partition(dt=‘$yesterda{y}^{\prime })selectt.use{r}_{i}d,t.use{r}_{n}ame,t.use{r}_{s}ex,t.use{r}_{b}irthday,t.use{r}_{a}ge,t.constellation,t.province,t.city,t.cit{y}_{l}evel,t.he{x}_{m}ail,t.o{p}_{m}ail,t.he{x}_{p}hone,t.for{e}_{p}hone,t.o{p}_{p}hone,t.ad{d}_{t}ime,t.logi{n}_{i}p,t.logi{n}_{s}ource,t.reques{t}_{u}ser,t.tota{l}_{m}ark,t.use{d}_{m}ark,t.leve{l}_{n}ame,t.blacklist,t.i{s}_{m}arried,t.education,t.monthl{y}_{m}oney,t.profession,nullse{x}_{m}odel,--数据挖掘模型-开始nulli{s}_{p}regnan{t}_{w}oman,nulli{s}_{h}av{e}_{c}hildren,nullchildre{n}_{s}e{x}_{r}ate,nullchildre{n}_{a}g{e}_{r}ate,nulli{s}_{h}av{e}_{c}ar,nullpotentia{l}_{c}a{r}_{u}se{r}_{r}ate,nullphon{e}_{b}rand,nullphon{e}_{b}ran{d}_{l}evel,nullphon{e}_{c}nt,nullchang{e}_{p}hon{e}_{r}ate,nullmaji{a}_{f}lag,nullmaji{e}_{a}ccoun{t}_{c}nt,nullloya{l}_{m}odel,nullshoppin{g}_{t}yp{e}_{m}odel,nullfigur{e}_{m}odel,nullstatur{e}_{m}odel,--数据挖掘模型-结束fro{m}_{u}nixtime(uni{x}_{t}imestamp())d{w}_{d}atefrom(select\ast fromfdm.itcas{t}_{f}d{m}_{u}se{r}_{w}idewheredt{=}^{\prime }$yesterday’) t;”
##创建BDM层表
echo “${SQL_BDM}”
$SPAR{K}_{S}UBMI{T}_{I}NFO-e"${SQL_BDM}"
##添加数据到BDM
hdfs dfs -put $SOURC{E}_{D}ATA/itcas{t}_{b}d{m}_{u}ser.txt/business/bdm/itcas{t}_{b}d{m}_{u}ser/"dt=$yesterday"
##创建FDM层表
echo “${SQL_FDM}”
$SPAR{K}_{S}UBMI{T}_{I}NFO-e"${SQL_FDM}"
##导入数据到FDM
echo “${LOAD_FDM}”
$SPAR{K}_{S}UBMI{T}_{I}NFO-e"${LOAD_FDM}"
##创建GDM层表
echo “${SQL_GDM}”
$SPAR{K}_{S}UBMI{T}_{I}NFO-e"${SQL_GDM}"
##导入GDM数据
echo “${LOAD_GDM}”
$SPAR{K}_{S}UBMI{T}_{I}NFO-e"${LOAD_GDM}"
USB Microphone https://www.soft-voice.com/
Wooden Speakers https://www.zeshuiplatform.com/
亚马逊测评 www.yisuping.cn
深圳网站建设www.sz886.com