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}"
