当您需要研究某类型数据的SQL处理方法,或验证功能实现逻辑是否符合预期,或需要在某些场合演示功能时,可以通过构造测试数据支撑功能验证及演示。本文为您介绍构造数据的方法,仅供参考。

背景信息

通常,先有数据才会有基于数据的应用,但当需要将应用运用到其他场景时,没有数据无法进行功能验证或演示。

企业的真实数据经过脱敏后也可以应用于功能验证及演示,但是数据脱敏操作很复杂,脱敏过重会导致数据无法使用,脱敏过轻会出现数据泄露风险,最安全的方式就是构造测试数据。在构造测试数据前,您需要梳理清楚业务场景及对数据的特殊要求,例如表的记录数、列字段类型、列字段的枚举值等。

构造测试数据的业务场景包含如下两种:
  • 单一业务场景:例如咨询或验证SQL处理数据的方法,只需要构造单张表并通过insert … valuesvalues tableselect ... from valuesunion all操作插入少量数据即可。更多构造小表信息,请参见构造小表。更多语法信息,请参见VALUES
  • 复杂业务场景:需要构造一个业务系统,相对于单一业务场景,存在多个表,且表之间存在关联关系。该场景下构造数据分为两个阶段:
    1. 构造维度表:通过笛卡尔积构造大表。更多构造大表信息,请参见构造大表
    2. 构造事实表:基于维度表构造事实表,此阶段实现复杂,本文不展开赘述,如果您想深入了解,请填写钉钉群申请表单加入钉钉群进行咨询。

构造小表

在MaxCompute上创建表后,构造少量测试数据的方法如下:
  • (推荐)方法一:通过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        |
    +------------+------------+------------+
  • (推荐)方法二:通过values table操作向表中插入数据。命令示例如下。
    -创建分区表srcp。
    create table if not exists srcp (key string,value bigint) partitioned by (p string);
    
    --向表srcp中插入数据。values (...), (...) t(a, b)相当于定义了一个名为t,列为a和b,数据类型分别为STRING和BIGINT的表。列的类型需要从values列表中推导。
    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   |
    +------------+------------+------------+
  • 方法三:通过from valuesunion 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          |
    +------------+

构造大表

您需要先构造小表,然后通过mapJoin对小表使用笛卡尔积方式基于随机值或有序值构造大量数据。

数据种类从本质上可以分为如下两种:
  • 序列值:有序的数列,使用ROW_NUMBER函数构造,该类型数据可以定义为主键。
  • 枚举值:少数的一些代码值,例如数值、金额,这些枚举值主要使用随机函数RAND构造。
命令示例如下。
--构造一张小表,插入序列值。
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);
--查看za1表中构造的数据。
select * from za1;
+------+
| c0   |
+------+
| 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   |
+------+
--您也可以通过SELECT TRANSFORM插入序列值。
create table za1 as select transform('for i in `seq 1 63`; do echo $i; done') using 'sh' as (data);

--使用ROW_NUMBER函数生成有序值构造大表。生成的数据是一个有序的从1000000-1039689的序列。
create table zb1 as
select *
  from(
select /*+mapjoin(t2,t3)*/
       1000000 + row_number() over(partition by 1)-1 as c0
  from za1 t1
  join za1 t2
  join(select c0 from za1 limit 10)t3
)t;
--查看zb1表中构造的数据。
select * from zb1;
+------+
| c0   |
+------+
| 1000000    |
| 1000001    |
| 1000002    |
| 1000003    |
|   ...      |
| 1039688    |
| 1039689    |
+------+
--使用RAND函数生成随机值构造大表。c2列生成的数据是相对均匀的1~1000的值。
create table zb2 as
select *
  from(
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
  join za1 t2
  join(select c0 from za1 limit 10)t3
  )t;
--查看zb2表中构造的数据。
select * from zb2;
+------------+------+------------+
| c0         | c1   | c2         |
+------------+------+------------+
| 1000000    | 1617120000 | 1          |
| 1000001    | 1617120000 | 800        |
| 1000002    | 1617120000 | 835        |
| 1000003    | 1617120000 | 108        |
| ...        |  ...       |  ...       | 
| 1039687    | 1617120000 | 4          |
| 1039688    | 1617120000 | 577        |
| 1039689    | 1617120000 | 33         |
+------------+------+------------+
当您有特殊数据要求,例如文本型枚举值或日期值时,可参考如下命令构造数据。
--构造枚举值(文本)。
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
 limit 5
)tb join ta on ta.b=tb.c;
--返回结果如下。
+------------+---+------+------------+
| k          | a | b    | c          |
+------------+---+------+------------+
| 101        | lisi | 5    | 5       |
| 102        | wangmazi | 6    | 6   |
| 104        | lisi | 5    | 5       |
+------------+---+------+------------+
--构造日期时间。
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
 limit 3
)tb;
--返回结果如下。
+------------+------------+------------+------------+
| k          | t          | b          | c          |
+------------+------------+------------+------------+
| 100        | 2021-03-31 00:00:00 | 2021-08-03 00:00:00 | 3          |
| 101        | 2021-03-31 00:00:00 | 2021-10-25 08:00:00 | 5          |
| 102        | 2021-03-31 00:00:00 | 2021-12-06 00:00:00 | 6          |
+------------+------------+------------+------------+

参考文档

更多关于构造数据的详细信息,请参见MaxCompute造数据详解