当您需要研究某类型数据的SQL处理方法，或验证功能实现逻辑是否符合预期，或需要在某些场合演示功能时，可以通过构造测试数据支撑功能验证及演示。本文为您介绍构造数据的方法，仅供参考。
背景信息
通常，先有数据才会有基于数据的应用，但当需要将应用运用到其他场景时，没有数据无法进行功能验证或演示。
企业的真实数据经过脱敏后也可以应用于功能验证及演示，但是数据脱敏操作很复杂，脱敏过重会导致数据无法使用，脱敏过轻会出现数据泄露风险，最安全的方式就是构造测试数据。在构造测试数据前，您需要梳理清楚业务场景及对数据的特殊要求，例如表的记录数、列字段类型、列字段的枚举值等。
构造测试数据的业务场景包含如下两种：
构造小表
在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 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 | +------------+
构造大表
您需要先构造小表，然后通过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造数据详解。
反馈
- 本页导读 （1）
文档反馈