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

 7、电商用户画像开发
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=$1
fi
SPARK_SUBMIT_INFO="/export/servers/spark/bin/spark-sql --master spark://node1:7077 --executor-memory 1g --total-executor-cores 2 --conf spark.sql.warehouse.dir=hdfs://node1:9000/user/hive/warehouse"
SOURCE_DATA="/root/source_data"
SQL_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 ','
location '/business/bdm/itcast_bdm_user' ;
alter table bdm.itcast_bdm_user add partition (dt='$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='$yesterday')
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='$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='$yesterday')
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='$yesterday') t;"
##创建BDM层表
echo "${SQL_BDM}"
$SPARK_SUBMIT_INFO -e "${SQL_BDM}"
##添加数据到BDM
hdfs dfs -put $SOURCE_DATA/itcast_bdm_user.txt business/bdm/itcast_bdm_user/"dt=$yesterday"
##创建FDM层表
echo "${SQL_FDM}"
$SPARK_SUBMIT_INFO -e "${SQL_FDM}"
##导入数据到FDM
echo "${LOAD_FDM}"
$SPARK_SUBMIT_INFO -e "${LOAD_FDM}"
##创建GDM层表
echo "${SQL_GDM}"
$SPARK_SUBMIT_INFO -e "${SQL_GDM}"
##导入GDM数据
echo "${LOAD_GDM}"
$SPARK_SUBMIT_INFO -e "${LOAD_GDM}"`