- 建表，多设置一个字段id_tmp
create table if not exists mall_data.dtw_mall2_adm_customer_d_tmp(
id_tmp string comment '临时创建的id',
mac_addr string comment 'Mac地址',
customer_id string comment '客户ID',
name string comment '姓名',
gender string comment '性别',
age string comment '年龄',
phone string comment '手机',
email string comment '邮箱',
birthday string comment '生日',
city string comment '省份城市',
has_car string comment '是否有车',
member_id string comment '会员号',
member_point string comment '会员积分',
member_status string comment '会员状态',
member_activate_time string comment '激活时间',
customer_type string comment '客户类型',
customer_life_cycle string comment '生命周期',
customer_new_old string comment '新老客户',
customer_level string comment '客户等级',
worth_consume_level string comment '消费层级',
shopping_feature string comment '购物特征',
character_all string comment '性格特征',
food_style string comment '偏好菜系',
food_price string comment '偏好用餐价位',
food_taste string comment '偏好口味',
food_time string comment '偏好用餐时间',
food_type string comment '偏好餐饮类型',
mall_visit_times_all string comment '到访次数',
mall_stay_time_all string comment '总停留时长',
mall_stay_time_avg string comment '平均停留时长',
shop_visit_num string comment '到访店铺数',
shop_stay_time_avg string comment '到店平均停留时长',
mall_last_visit_time string comment '上次到访时间',
pay_mall_all string comment '交易总额',
pay_num_mall_all string comment '交易笔数',
pay_per_deal_avg string comment '平均每笔交易金额',
pay_shop_num_all string comment '成交店铺数',
pay_per_shop_avg string comment '成交店铺平均单价',
pay_commodity_num_all string comment '成交商品数',
pay_commodity_per_unit_avg string comment '成交商品平均单价',
pay_last_time string comment '上次交易时间'
)
comment '用户表'
partitioned by (ds string comment '分区', tenantId int comment '租户id' , mallName string comment '商场名称')
stored as parquet; - 创建临时表 tmp_count_control,用于辅助数据的插入
create table tmp_count_control(id int);insert into table tmp_count_control values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16);
select * from tmp_count_control;
- 插入数据到
insert into mall_data.dtw_mall2_adm_customer_d_tmp partition(ds='20180615', tenantid=1, mallname='big')
select
id,
cast(rand()*1000000 as int)+1000000,
cast(rand()*1000000 as int)+1000000,
concat(
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)],
split('a b c d e f g h i g k l m n o p q r s t u v w x y z',' ')[cast(rand()*26 as int)]
),
split('m f notclear',' ')[cast((rand()*3) as int)],
cast((rand()*40) as int)+10,
13344455666,
'dtw123@dtw-inc.com',
concat(concat(199,split('0 1 2 3 4 5 6 7 8 9',' ')[cast(rand()*10 as int)]),
split('01 02 03 04 05 06 07 08 09 10 11 12',' ')[cast(rand()*12 as int)],
concat(split('0 1 2',' ')[cast(rand()*3 as int)],split('1 2 3 4 5 6 7 8',' ')[cast(rand()*8 as int)])
),
split('北京 上海 广州 深圳 杭州 厦门 福州 济南 青岛 烟台 济宁 临沂 莱芜 曲阜 平壤 太原 阜阳 合肥 嵊州 平度 拉萨 昆明 沈阳 长春 哈尔滨',' ')[cast(rand()*25 as int)],
split('有 无 不确定',' ')[cast((rand()*3) as int)],
cast(rand()*1000000 as int)+1000000,
cast(rand()*100000 as int),
split('未激活 已激活',' ')[cast(rand()*2 as int)],
concat(concat(201,split('0 1 2 3 4 5 6 7',' ')[cast(rand()*8 as int)]),
split('01 02 03 04 05 06 07 08 09 10 11 12',' ')[cast(rand()*12 as int)],
concat(split('0 1 2',' ')[cast(rand()*3 as int)],split('1 2 3 4 5 6 7 8',' ')[cast(rand()*8 as int)])
),
split('到访客户 成交客户 会员客户',' ')[cast(rand()*3 as int)],
split('新客 活跃客户 忠诚客户 流失客户 回头客',' ')[cast(rand()*5 as int)],
split('新客户 老客户',' ')[cast(rand()*2 as int)],
split('初级访客 中级访客 高级访客 初级会员 中级会员 高级会员',' ')[cast(rand()*6 as int)],
split('低 中 高',' ')[cast(rand()*3 as int)],
concat(
'{',
split('花卉一族 摄影一族 健美一族 果粉 电影派 阅读者 舞林人士 乐器迷 爱听音乐 童心未泯',' ')[cast(rand()*10 as int)],
',',
split('御宅族 白富美 高帅富 呼朋唤友 休闲大咖 装修一族 甜蜜新人 户外一族 网络一族 酒品人生',' ')[cast(rand()*10 as int)],
',',
split('有型潮男 拜金主义 数码达人 家有儿女 养生专家 运动一族 车友派 家有宠物 游戏人生 爱家人士',' ')[cast(rand()*10 as int)],
'}'
),
concat(
'{',
split('明确果断 犹豫再三 喜新厌旧 货比三家',' ')[cast(rand()*4 as int)],
',',
split('只逛不买 虚荣型 忠诚型',' ')[cast(rand()*3 as int)],
',',
split('挑剔型 将就型 跟风型',' ')[cast(rand()*3 as int)],
'}'
),
split('川菜 粤菜 淮扬菜 浙菜 湘菜 徽菜 闽菜 日本菜 *菜 韩国菜',' ')[cast(rand()*10 as int)],
split('低 中 高',' ')[cast(rand()*3 as int)],
split('油 清淡 辣 麻 咸',' ')[cast(rand()*5 as int)],
split('上午 中午 下午 黄昏 傍晚 晚上',' ')[cast(rand()*6 as int)],
split('中餐 西餐 自助餐 小吃快餐 饮品店 面包甜点 火锅 特色菜 烧烤',' ')[cast(rand()*9 as int)],
cast(rand()*50 as int),
round(rand()*50,1)+5,
round(rand()*5,1),
cast(rand()*10 as int),
round(rand()*5,1)+1,
concat(concat(201,split('6 7 8',' ')[cast(rand()*3 as int)]),
split('01 02 03 04 05 06 07 08 09 10 11 12',' ')[cast(rand()*12 as int)],
concat(split('0 1 2',' ')[cast(rand()*3 as int)],split('1 2 3 4 5 6 7 8',' ')[cast(rand()*8 as int)])
),
round(rand()*5000,1)+5000,
cast(rand()*50 as int),
round(rand()*1000,1)+500,
cast(rand()*20 as int),
round(rand()*500,1)+50,
cast(rand()*20 as int),
round(rand()*100,1)+50,
concat(concat(201,split('6 7 8',' ')[cast(rand()*3 as int)]),
split('01 02 03 04 05 06 07 08 09 10 11 12',' ')[cast(rand()*12 as int)],
concat(split('0 1 2',' ')[cast(rand()*3 as int)],split('1 2 3 4 5 6 7 8',' ')[cast(rand()*8 as int)])
)from tmp_count_control [limit 5000];
- OK，完成数据的插入！！！
- 通过新建临时表来辅助实现插入数据条数的控制，否则就得通过insert into tablename values (******),(******)......形式，写多次上面蓝色的sql（上面80行sql仅仅插入1条数据）