共计 7461 个字符，预计需要花费 19 分钟才能阅读完成。
简介：造一点模仿数据的办法
概述
造数据在一些奇怪的场合会被用到。个别咱们是先有数据才有基于数据的利用场合，然而反过来如果利用拿到另外一个场景，没有数据性能是没有办法演示的。
个别较为实在的数据，脱敏后就能够利用在功能测试和演示的场合。然而数据脱敏其实也满简单（脱敏过重数据就用不了了，过低数据又透露了），所以本人模仿一些数据，仿佛更平安。
我集体个别遇到的造数据场景有两个。第一，是有合作伙伴或者共事征询一个 SQL 解决数据的办法，没有数据。第二，就是有时候会有 POC 的一些场景，没有提供实在模仿数据，须要本人模仿。
分类
如果是繁多的业务场景的数据模仿，很多时候单表就能够满足了。然而要是模仿某个业务场景，或者 POC 测试场景则要模仿一个业务零碎中的互相关联的多张表。
造数据，个别会都会有些用户需要，会有明确的业务场景的形容。也会有一些其余要求，例如：表的记录数、行的存储、字段的生成规定、字段的值域、字段的枚举值，还可能会给大量实在的数据。
2.1. 一个表
独自造一张表的数据可能非常简单，比方咱们日常测试一个函数，测试一段 SQL 的 JOIN 逻辑。也可能非常复杂，结构一个表，也就相当于结构一个业务零碎。
2.2. 一个业务零碎
业务零碎绝对于单表来说只是表的数量减少了。而且，因为业务零碎的表间是存在主外键关系的，所以，须要先造代码表（维度表），而后再造业务表（事实表）。
办法
造模仿数据的办法分为两个阶段，第一阶段是结构一个小表，产生代码表（维度表），而后第二阶段利用笛卡尔积疾速乘出须要的数据量。在这其中，列的数据值填充能够应用随机函数生成。
3.1. 结构一个常量小表
Maxcompute 最简略的造数据的办法是 insert into values 语句，这个别也是我最罕用的。在不反对这个语句之前的更早的版本，应用的是 union all 的办法。如果不想理论写入数据到，则能够应用 from values 和 with 表达式。
示例 1：通过 insert … values 操作向特定分区内插入数据。
命令示例如下：
-- 创立分区表 srcp。create table if not exists srcp (key string,value bigint) partitioned by (p string);
-- 向分区表 srcp 增加分区。alter table srcp add if not exists partition (p='abc');
-- 向表 srcp 的指定分区 abc 中插入数据。insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);
-- 查问表 srcp。select * from srcp where p='abc';
-- 返回后果。+------------+------------+------------+
| key | value | p |
+------------+------------+------------+
| a | 1 | abc |
| b | 2 | abc |
| c | 3 | abc |
+------------+------------+------------+示例 2：通过 values table 操作插入数据。
命令示例如下：
-- 创立分区表 srcp。create table if not exists srcp (key string,value bigint) partitioned by (p string);
-- 向表 srcp 中插入数据。insert into table srcp partition (p) select concat(a,b), length(a)+length(b),'20170102' from values ('d',4),('e',5),('f',6) t(a,b);
-- 查问表 srcp。select * from srcp where p='20170102';
-- 返回后果。+------------+------------+------------+
| key | value | p |
+------------+------------+------------+
| d4 | 2 | 20170102 |
| e5 | 2 | 20170102 |
| f6 | 2 | 20170102 |
+------------+------------+------------+values (…), (…) t(a, b) 相当于定义了一个名为 t，列为 a 和 b，数据类型别离为 STRING 和 BIGINT 的表。列的类型须要从 values 列表中推导。
示例 3：from values 或者 union all 组合的形式，结构常量表。
命令示例如下：
with t as (select 1 c union all select 2 c) select * from t;
-- 等价于如下语句。select * from values (1), (2) t(c);
-- 返回后果。+------------+
| c |
+------------+
| 1 |
| 2 |
+------------+以上例子来源于：
https://help.aliyun.com/docum…
3.2. 利用笛卡尔积结构大表
家喻户晓，笛卡尔积的写法只能用在 MAPJOIN 提醒的状况下。所以，第一步结构进去的常量小表是能够应用 MAPJOIN 的。
命令示例如下：
— 1 结构一个常量表（我这里用的有序数字，方便使用 where 去取制订数量的记录数去乘笛卡尔积）
create table za1 as
select c0 from values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
,(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
,(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45)
,(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60)
,(61),(62),(63)
t(c0);
-- 2 应用常量表屡次关联，结构出须要的记录数 [大家应用计算器大略算一下 N 的多少次方够用]
create table zb1 as
select *
from(
-- 10*63*63=39690
select /*+mapjoin(t2,t3)*/
1000000 + row_number() over(partition by 1)-1 as c0
from za1 t1 -- 63
join za1 t2 -- 63
join(select c0 from za1 limit 10)t3 -- 10
)t
;
--3 第 2 步结构的表曾经达到万级，用这个表再结构的表记录数就能够轻松达到亿级 3.3. 利用随机值有序值填充列
数据品种从实质上能够分为 2 种，序列值和枚举值。序列值，就是有序的一个数列，应用 row_number() 函数来实现，在这个场景里次要定义为主键。枚举值就是多数的一些代码值（数值、金额、代码），散布在记录中，这些枚举值次要应用随机函数来填充。其余状况，目前集体还未遇到，就不形容了。
命令示例如下：
-- 1 有序值，在这个例子中，生成的数据是一个有序的从 1000000-1036689 的序列，能够作为业务主外键应用
select /*+mapjoin(t2,t3)*/
1000000 + row_number() over(partition by 1)-1 as c0
from za1 t1 -- 63
join za1 t2 -- 63
join(select c0 from za1 limit 10)t3 -- 10
;
-- 2 随机值 / 固定值，在这个例子中 c2 列会生成一个绝对平均的 1 -1000 的值
-- 随机函数生成的随机数是浮点值，必须要转为 bigint
select /*+mapjoin(t2,t3)*/
1000000 + row_number() over(partition by 1)-1 as c0
,1617120000 as c1
,cast(round(rand()*999,0) as bigint)+1 as c2
from za1 t1 -- 63
join za1 t2 -- 63
join(select c0 from za1 limit 10)t3 -- 10
;
3.4. 不同的数据类型的结构
个别数据类型能够分为 4 种，主键惟一值、字符串代表的枚举值、数值、日期工夫。方才的例子外面结构的都是数值，惟一区别的是枚举值是数字而不是文本，而且没有结构日期工夫。那么如果的确须要，该怎么实现。工夫能够结构成 unixtime，就能够转化为数值。文本类型的枚举值，能够先结构代码表，再构建好业务表后再关联进去（个别业务零碎存储的也是代码值，而不是一个长字符串）。命令示例如下：-- 利用代码表转文本
with za as (
select * from values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
,(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
,(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45)
,(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60)
,(61),(62),(63)
t(c0)
)
,ta as (select * from values ('zhangsan',4),('lisi',5),('wangmazi',6) t(a,b))
select k,a,b,c
from(select 100 + row_number() over(partition by 1)-1 as k
,cast(round(rand()*3,0) as bigint)+3 as c
from za -- 63
limit 3
)tb join ta on ta.b=tb.c
;
返回：k a b c
101 lisi 5 5
102 wangmazi 6 6
103 zhangsan 4 4
-- 利用 unixtimetamp 转日期工夫
with za as (
select * from values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
,(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
,(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45)
,(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60)
,(61),(62),(63)
t(c0)
)
select k
,from_unixtime(1617120000) as t
,from_unixtime(1617120000
+3600000 * c ) -- 小时
as b
,c
from(select 100 + row_number() over(partition by 1)-1 as k
,cast(round(rand()*3,0) as bigint)+3 as c
from za -- 63
limit 3
)tb
;
返回：k t b c
100 2021-03-31 00:00:00 2021-03-31 03:00:00 3
101 2021-03-31 00:00:00 2021-03-31 05:00:00 5
102 2021-03-31 00:00:00 2021-03-31 06:00:00 6实际
4.1. 实际案例
在前段时间经验的一个电信行业的 POC 我的项目，客户最开始给了 80 行实在数据，要求造十几亿左右的数据，并给了一些十分非凡的数据要求。
原始数据和依据客户要求处理过程解决完后的数据特色的要求
记录数：单表的记录数，原始 16 亿，解决后 1.7 亿；
用户数：1 千 4 百万；
设施数：23 万；
单行记录大小：原始数据行记录 436KB，解决完后是 157KB；
单用户记录数（最小、最多、中位）：最小值是 1；最大值原始未 3 万，解决后是 2 千 4 百；
中位数原始值是 51，解决后是 4；
如下表：
根据上述要求，第一步是剖析业务需要，原始数据有 61 列，然而实在参加数据计算的列只有 10 列。所以，结构原始表只须要把这 10 列结构进去，再把原始给的 61 列的记录的列选取 1 行关联下来即可。
剖析原始数据构造，选区参加计算的数据列：
create table if not exists t_log10 (
imei_tac int comment '用户设施 ID1'
,phone7 int comment '用户设施 ID2'
,imsi string comment '用户设施 ID3'
,msisdn string comment '用户设施 ID4'
,tac int comment '电信设施 ID1'
,cell_id int comment '电信设施 ID2'
,Procedure_Type int comment '业务类型'
,Procedure_Start_Time bigint comment '业务开始工夫，unixtimestamp'
,Procedure_status int comment '业务状态，固定值 1'
,country_code int comment '国家码，固定值 -406' )
partitioned by (hh string);电信业务中，这个业务场景形容的是用户手机设施在电信运营商基站设备上注册的状况。这个业务计算应用的字段 10 个。有 5 个是用户设施维度相干，别离是用户设施 ID（1-4）和国家码；有 2 个是电信设施维度相干，别离是电信设施 ID（1-2）。还有 3 个是用户设施与电信设施业务产生相干的，别离是业务类型、业务状态、业务开始工夫。
所以，在做了需要剖析后，我认为我须要先构建一个用户设施维度表和电信基站设备维度表，再依据这些维度表构建电信业务事实表（业务表）。
第一步，构建电信基站维度（代码）表：
drop table if exists t_tac_lacid;
create table if not exists t_tac_lacid (id bigint,tac bigint,lacid bigint);
insert overwrite table t_tac_lacid
select /*+mapjoin(t2)*/
row_number() over(partition by 1)+100000 as rn
,t1.c0+6001 as tac
,t2.c0+1201 as lacid
from (select row_number() over(partition by 1)-1 as c0 from zb1 limit 2300)t1
join (select row_number() over(partition by 1)-1 as c0 from zb1 limit 100)t2
;
-- 230000在这个例子，通过构建的 zb1 选区特定的记录数，通过笛卡尔积乘出指定的记录数的后果集。因为两个 ID 要构建出惟一主键，所以，这里应用了 row_number 窗口函数。在构建主键的时候，应用了 100000+ 这种形式来构建固定长度的 ID。
第二步，构建用户设施维度（代码）表。
drop table if exists t_user;
create table t_user (imei_tac bigint,phone7 bigint,imsi string ,msisdn string);
insert overwrite table t_user
select
rn as imei_tac
,cast(substr(to_char(rn),2,7) as bigint)+1000000 as phone7
,substr(MD5(rn), 1,10) as imsi
,substr(MD5(rn),11,10) as msisdn
from(select /*+mapjoin(t2,t3,t4)*/
row_number() over(partition by 1)+10000000 as rn
from za1 t1
join za1 t2
join za1 t3
join (select c0 from za1 limit 58) t4
-- limit 100
)t;
-- 14502726
-- 63*63*63*58 = 14502726在这个例子，通过 4 次应用 za1 这个表构建了一个看起来很实在的记录数（实际上造数据差几条没区别，这里有点无聊）。应用 row_number 窗口函数构建了业务主键，并转化了几种模式（MD5 截取）构建了不同的主键的款式。而后应用了随机函数构建了基站信息。这外面实际上把基站信息也做了计算，这些非凡解决次要是为了构建最初的后果表。
最初一步就是构建后果表了，因为后面咱们还没有思考中位数、极值和解决后后果的问题，所以，实际上最初的实现比较复杂（太长了，就不粘进去了，有须要独自找我要吧）。
满足特殊要求的办法是用户分段：
1) 极值，十分小的用户记录数满足用户极值 [例如选 500 个用户]
2) 中位数，中位数肯定是超过了一半以上的用户的记录数
3) 补充数，除去极值与中位数剩下的用户
须要应用提醒来改善性能，因为造数据的原始表都十分小，map 阶段个别只有 1 个 worker。
所以，必须要把 map 阶段的数据块输出切小，把 map 和 reduce 的资源给大了。
set odps.sql.mapper.cpu=200;
set odps.sql.mapper.memory=8192;
set odps.sql.mapper.split.size=4;
set odps.sql.reducer.cpu=200;
set odps.sql.reducer.memory=8192;4.2. 总结
造数据场景大部分时候都比较简单，然而，也会遇到上述这种非凡的简单状况。然而简单的业务次要还是考验数据加工的能力，怎么应用根底表生成简单表，还是关系数据库的关系模型的构建的过程。
单个数据表的构建，首先须要先剖析出业务中的维度和事实的局部，再构建维度，利用维度构建事实。
原文链接
本文为阿里云原创内容，未经容许不得转载。
