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=‘-01-01’) location ‘/business/itcast_bdm_user/-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=‘-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=‘-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=‘-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=‘-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=1fiSPARKSUBMITINFO="/export/servers/spark/bin/spark−sql−−masterspark://node1:7077−−executor−memory1g−−total−executor−cores2−−confspark.sql.warehouse.dir=hdfs://node1:9000/user/hive/warehouse"SOURCEDATA="/root/sourcedata"SQLBDM="createdatabaseifnotexistsbdm;createexternaltableifnotexistsbdm.itcastbdmuser(useridstring,−−用户IDusernamestring,−−用户登陆名usersexstring,−−用户性别userbirthdaystring,−−用户生日useragebigint,−−用户年龄constellationstring,−−用户星座provincestring,−−省份citystring,−−城市citylevelstring,−−城市等级hexmailstring,−−邮箱opmailstring,−−邮箱运营商hexphonestring,−−手机号forephonestring,−−手机前3位opphonestring,−−手机运营商addtimestring,−−注册时间loginipstring,−−登陆ip地址loginsourcestring,−−登陆来源requestuserstring,−−邀请人totalmarkbigint,−−会员积分usedmarkbigint,−−已使用积分levelnamestring,−−会员等级名称blacklistbigint,−−用户黑名单ismarriedbigint,−−婚姻状况educationstring,−−学历monthlymoneydouble,−−收入professionstring−−职业)partitionedby(dtstring)rowformatdelimitedfieldsterminatedby′,′location′/business/bdm/itcastbdmuser′;altertablebdm.itcastbdmuseraddpartition(dt=′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='1fiSPARKS​UBMITI​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"SOURCED​ATA="/root/sourced​ata"SQLB​DM="createdatabaseifnotexistsbdm;createexternaltableifnotexistsbdm.itcastb​dmu​ser(useri​dstring,−−用户IDusern​amestring,−−用户登陆名users​exstring,−−用户性别userb​irthdaystring,−−用户生日usera​gebigint,−−用户年龄constellationstring,−−用户星座provincestring,−−省份citystring,−−城市cityl​evelstring,−−城市等级hexm​ailstring,−−邮箱opm​ailstring,−−邮箱运营商hexp​honestring,−−手机号forep​honestring,−−手机前3位opp​honestring,−−手机运营商addt​imestring,−−注册时间logini​pstring,−−登陆ip地址logins​ourcestring,−−登陆来源requestu​serstring,−−邀请人totalm​arkbigint,−−会员积分usedm​arkbigint,−−已使用积分leveln​amestring,−−会员等级名称blacklistbigint,−−用户黑名单ism​arriedbigint,−−婚姻状况educationstring,−−学历monthlym​oneydouble,−−收入professionstring−−职业)partitionedby(dtstring)rowformatdelimitedfieldsterminatedby′,′location′/business/bdm/itcastb​dmu​ser′;altertablebdm.itcastb​dmu​seraddpartition(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′)selectt.userid,t.username,t.usersex,t.userbirthday,t.userage,t.constellation,t.province,t.city,t.citylevel,t.hexmail,t.opmail,t.hexphone,t.forephone,t.opphone,t.addtime,t.loginip,t.loginsource,t.requestuser,t.totalmark,t.usedmark,t.levelname,t.blacklist,t.ismarried,t.education,t.monthlymoney,t.profession,fromunixtime(unixtimestamp())dwdatefrombdm.itcastbdmusertwheredt=′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′)selectt.useri​d,t.usern​ame,t.users​ex,t.userb​irthday,t.usera​ge,t.constellation,t.province,t.city,t.cityl​evel,t.hexm​ail,t.opm​ail,t.hexp​hone,t.forep​hone,t.opp​hone,t.addt​ime,t.logini​p,t.logins​ource,t.requestu​ser,t.totalm​ark,t.usedm​ark,t.leveln​ame,t.blacklist,t.ism​arried,t.education,t.monthlym​oney,t.profession,fromu​nixtime(unixt​imestamp())dwd​atefrombdm.itcastb​dmu​sertwheredt=′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′)selectt.userid,t.username,t.usersex,t.userbirthday,t.userage,t.constellation,t.province,t.city,t.citylevel,t.hexmail,t.opmail,t.hexphone,t.forephone,t.opphone,t.addtime,t.loginip,t.loginsource,t.requestuser,t.totalmark,t.usedmark,t.levelname,t.blacklist,t.ismarried,t.education,t.monthlymoney,t.profession,nullsexmodel,−−数据挖掘模型−开始nullispregnantwoman,nullishavechildren,nullchildrensexrate,nullchildrenagerate,nullishavecar,nullpotentialcaruserrate,nullphonebrand,nullphonebrandlevel,nullphonecnt,nullchangephonerate,nullmajiaflag,nullmajieaccountcnt,nullloyalmodel,nullshoppingtypemodel,nullfiguremodel,nullstaturemodel,−−数据挖掘模型−结束fromunixtime(unixtimestamp())dwdatefrom(select∗fromfdm.itcastfdmuserwidewheredt=′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′)selectt.useri​d,t.usern​ame,t.users​ex,t.userb​irthday,t.usera​ge,t.constellation,t.province,t.city,t.cityl​evel,t.hexm​ail,t.opm​ail,t.hexp​hone,t.forep​hone,t.opp​hone,t.addt​ime,t.logini​p,t.logins​ource,t.requestu​ser,t.totalm​ark,t.usedm​ark,t.leveln​ame,t.blacklist,t.ism​arried,t.education,t.monthlym​oney,t.profession,nullsexm​odel,−−数据挖掘模型−开始nullisp​regnantw​oman,nullish​avec​hildren,nullchildrens​exr​ate,nullchildrena​ger​ate,nullish​avec​ar,nullpotentialc​aru​serr​ate,nullphoneb​rand,nullphoneb​randl​evel,nullphonec​nt,nullchangep​honer​ate,nullmajiaf​lag,nullmajiea​ccountc​nt,nullloyalm​odel,nullshoppingt​ypem​odel,nullfigurem​odel,nullstaturem​odel,−−数据挖掘模型−结束fromu​nixtime(unixt​imestamp())dwd​atefrom(select∗fromfdm.itcastf​dmu​serw​idewheredt=′yesterday’) t;”

##创建BDM层表

echo “${SQL_BDM}”

SPARKSUBMITINFO−e"SPARK_SUBMIT_INFO -e "SPARKS​UBMITI​NFO−e"{SQL_BDM}"

##添加数据到BDM

hdfs dfs -put SOURCEDATA/itcastbdmuser.txt/business/bdm/itcastbdmuser/"dt=SOURCE_DATA/itcast_bdm_user.txt /business/bdm/itcast_bdm_user/"dt=SOURCED​ATA/itcastb​dmu​ser.txt/business/bdm/itcastb​dmu​ser/"dt=yesterday"

##创建FDM层表

echo “${SQL_FDM}”

SPARKSUBMITINFO−e"SPARK_SUBMIT_INFO -e "SPARKS​UBMITI​NFO−e"{SQL_FDM}"

##导入数据到FDM

echo “${LOAD_FDM}”

SPARKSUBMITINFO−e"SPARK_SUBMIT_INFO -e "SPARKS​UBMITI​NFO−e"{LOAD_FDM}"

##创建GDM层表

echo “${SQL_GDM}”

SPARKSUBMITINFO−e"SPARK_SUBMIT_INFO -e "SPARKS​UBMITI​NFO−e"{SQL_GDM}"

##导入GDM数据

echo “${LOAD_GDM}”

SPARKSUBMITINFO−e"SPARK_SUBMIT_INFO -e "SPARKS​UBMITI​NFO−e"{LOAD_GDM}"

USB Microphone https://www.soft-/

Wooden Speakers /

亚马逊测评

深圳网站建设