当您需要研究某类型数据的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造数据详解。
文档内容是否对您有帮助？