Construct test data

There are two main business scenarios for constructing test data:

• Single business scenario:

  For example, to test or verify a SQL data processing method, you can create a single table and insert a small amount of data by using INSERT … VALUES, values table, SELECT ... FROM VALUES, or UNION ALL. For more information, see Constructing Small Tables.

• Complex business scenario:

  This scenario involves building a business system with multiple, interrelated tables. Data construction in this scenario is divided into two stages:

  1. Construct a dimension table: Use the Cartesian product to construct a large table. For more information, see Construct a large table.

  2. Construct a fact table: Construct a fact table based on the dimension table. This stage is complex and is not covered in detail in this topic. To learn more, see Request link.

After you create a table in MaxCompute, you can use the following methods to construct test data:

• Method 1

  Use the INSERT … VALUES statement to insert data into a table. The following code provides an example.

  -- Create a partitioned table named srcp.
  CREATE TABLE IF NOT EXISTS srcp (key STRING, value BIGINT) PARTITIONED BY (p STRING);
  
  -- Add a partition to the srcp table.
  ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');
  
  -- Insert data into the 'abc' partition of the srcp table.
  INSERT INTO TABLE srcp PARTITION (p='abc') VALUES ('a',1),('b',2),('c',3);
  
  -- Query the srcp table.
  SELECT * FROM srcp WHERE p='abc';
  
  -- The following result is returned.
  +------------+------------+------------+
  | key        | value      | p          |
  +------------+------------+------------+
  | a          | 1          | abc        |
  | b          | 2          | abc        |
  | c          | 3          | abc        |
  +------------+------------+------------+

• Method 2:

  Use the values table statement to insert data into a table. The following code provides an example.

  -- Create a partitioned table named srcp.
  CREATE TABLE IF NOT EXISTS srcp (key STRING, value BIGINT) PARTITIONED BY (p STRING);
  
  -- Insert data into the srcp table. values (...), (...) t(a, b) defines a table named t with columns a and b of the STRING and BIGINT types, respectively. The column types are derived from the values list.
  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);
  
  -- Query the srcp table.
  SELECT * FROM srcp WHERE p='20170102';
  
  -- The following result is returned.
  +------------+------------+------------+
  | key        | value      | p          |
  +------------+------------+------------+
  | d4         | 2          | 20170102   |
  | e5         | 2          | 20170102   |
  | f6         | 2          | 20170102   |
  +------------+------------+------------+

• Method 3

  Use the FROM VALUES or UNION ALL statement to construct a table and insert data. The following code provides an example.

  WITH t AS (SELECT 1 c UNION ALL SELECT 2 c) SELECT * FROM t;
  -- The preceding statement is equivalent to the following statement.
  SELECT * FROM VALUES (1), (2) t(c);
  
  -- The following result is returned.
  +------------+
  | c          |
  +------------+
  | 1          |
  | 2          |
  +------------+

The data can be categorized into two types:

• Sequence value: An ordered sequence of numbers generated using the ROW_NUMBER function. This data can be used as a primary key.

• Enumeration value: A small set of code values, such as numbers or amounts, that are typically generated using the RAND function.

Sample commands:

-- Construct a small table and insert sequence values.
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);
-- View the constructed data in the za1 table.
SELECT * FROM za1;
+------+
| c0   |
+------+
| 1    |
| 2    |
| 3    |
| ...  |
| 61   |
| 62   |
| 63   |
+------+
-- You can also use SELECT TRANSFORM to insert sequence values.
CREATE TABLE za1 AS SELECT TRANSFORM('for i in `seq 1 63`; do echo $i; done') USING 'sh' AS (data);

-- Use the ROW_NUMBER function to generate ordered values to construct a large table. The command generates an ordered sequence from 1000000 to 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;
-- View the constructed data in the zb1 table.
SELECT * FROM zb1;
+------+
| c0   |
+------+
| 1000000    |
| 1000001    |
| 1000002    |
| 1000003    |
|   ...      |
| 1039688    |
| 1039689    |
+------+
-- Use the RAND function to generate random values to construct a large table. The values generated in the c2 column are relatively evenly distributed from 1 to 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;
-- View the constructed data in the zb2 table.
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         |
+------------+------+------------+

If you have special data requirements, such as text-based enumeration values or date values, you can use the following commands to construct data.

-- Construct enumeration values (text).
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;

-- The following result is returned.
+------------+---+------+------------+
| k          | a | b    | c          |
+------------+---+------+------------+
| 101        | lisi | 5    | 5       |
| 102        | wangmazi | 6    | 6   |
| 104        | lisi | 5    | 5       |
+------------+---+------+------------+
-- Construct date and time values.
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;
  
-- The following result is returned.
+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+