- 概述
造數據在一些奇怪的場合會被用到。一般我們是先有數據才有基於數據的應用場合，但是反過來如果應用拿到另外一個場景，沒有數據功能是沒有方法演示的。
一般較為真實的數據，脫敏後就可以應用在功能測試和演示的場合。但是數據脫敏其實也滿複雜（脫敏過重數據就用不了了，過低數據又洩漏了），所以自己模擬一些數據，似乎更安全。
我個人一般遇到的造數據場景有兩個。第一，是有合作伙伴或者同事諮詢一個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/document_detail/73778.html?spm=a2c4g.11186623.6.732.7e477b57ZhLOGj
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；
如下表：
| 時間 | 類別 | 記錄數 | 用戶數 | 用戶記錄數最小 | 用戶記錄數最多 | 用戶記錄數中位 | 設備數 | 單行（KB） |
|---|---|---|---|---|---|---|---|---|
| 9:00 | 原始 | 1668486059 | 14297500 | 1 | 31973 | 51 | 231272 | 436 |
| 9:00 | 處理 | 174817694 | 13371656 | 1 | 2441 | 4 | 230860 | 157 |
根據上述要求，第一步是分析業務需求，原始數據有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. 總結
造數據場景大部分時候都比較簡單，但是，也會遇到上述這種特殊的複雜情況。但是複雜的業務主要還是考驗數據加工的能力，怎麼使用基礎表生成複雜表，還是關係數據庫的關係模型的構建的過程。
單個數據表的構建，首先需要先分析出業務中的維度和事實的部分，再構建維度，利用維度構建事實。