PostgreSQL 如何快速构建海量逼真测试数据

3 minute read

背景

为了测试或验证需要，通常需要快速的构建测试数据。

PostgreSQL提供了一些非常有用的功能，可以帮助用户快速的构建测试数据。

有趣的功能

1、SRF

返回多条记录的函数。例如

                                                                 List of functions  
   Schema   |        Name         |         Result data type          |                        Argument data types                         |  Type    
------------+---------------------+-----------------------------------+--------------------------------------------------------------------+--------  
 pg_catalog | generate_series     | SETOF bigint                      | bigint, bigint                                                     | normal  
 pg_catalog | generate_series     | SETOF bigint                      | bigint, bigint, bigint                                             | normal  
 pg_catalog | generate_series     | SETOF integer                     | integer, integer                                                   | normal  
 pg_catalog | generate_series     | SETOF integer                     | integer, integer, integer                                          | normal  
 pg_catalog | generate_series     | SETOF numeric                     | numeric, numeric                                                   | normal  
 pg_catalog | generate_series     | SETOF numeric                     | numeric, numeric, numeric                                          | normal  
 pg_catalog | generate_series     | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, interval | normal  
 pg_catalog | generate_series     | SETOF timestamp with time zone    | timestamp with time zone, timestamp with time zone, interval       | normal  
 pg_catalog | generate_subscripts | SETOF integer                     | anyarray, integer                                                  | normal  
 pg_catalog | generate_subscripts | SETOF integer                     | anyarray, integer, boolean                                         | normal  
(10 rows)  

返回一批数值、时间戳、或者数组的下标。

例子，生成一批顺序值。

postgres=# select id from generate_series(1,10) t(id);  
 id   
----  










(10 rows)  

2、随机数

random()

例子，生成一批随机整型

postgres=# select (random()*100)::int from generate_series(1,10);  
 int4   
------  










(10 rows)  

3、随机字符串

md5(random()::text)

例子，生成一批随机字符串

postgres=# select md5(random()::text) from generate_series(1,10);  
               md5                  
----------------------------------  
 ba1f4f4b0073f61145a821c14437230d  
 a76b09292c1449ebdccad39bcb5864c0  
 d58f5ebe43f631e7b5b82e070a05e929  
 0c0d3971205dc6bd355e9a60b29a4c6d  
 bd437e87fd904ed6ecc80ed782abac7d  
 71aea571d8c0cd536de53fd2be8dd461  
 e32e105db58f9d39245e3e2b27680812  
 174f491a2ec7a3498cab45d3ce8a4277  
 563a7c389722f746378987b9c4d9bede  
 6e8231c4b7d9a5cfaae2a3e0cef22f24  
(10 rows)  

4、重复字符串

repeat('abc', 10)

例子，生成重复2次的随机字符串

postgres=# select repeat(md5(random()::text),2) from generate_series(1,10);  
                              repeat                                
------------------------------------------------------------------  
 616d0a07a2b61cd923a14cb3bef06252616d0a07a2b61cd923a14cb3bef06252  
 73bc0d516a46182b484530f5e153085e73bc0d516a46182b484530f5e153085e  
 e745a65dbe0b4ef0d2a063487bbbe3d6e745a65dbe0b4ef0d2a063487bbbe3d6  
 90f9b8b18b3eb095f412e3651f0a946c90f9b8b18b3eb095f412e3651f0a946c  
 b300f78b20ac9a9534a46e9dfd488761b300f78b20ac9a9534a46e9dfd488761  
 a3d55c275f1e0f828c4e6863d4751d06a3d55c275f1e0f828c4e6863d4751d06  
 40e609dbe208fc66372b1c829018097140e609dbe208fc66372b1c8290180971  
 f661298e28403bc3005ac3aebae49e16f661298e28403bc3005ac3aebae49e16  
 10d0641e40164a238224d2e16a28764710d0641e40164a238224d2e16a287647  
 450e599890935df576e20c457691c421450e599890935df576e20c457691c421  
(10 rows)  

5、随机中文

create or replace function gen_hanzi(int) returns text as $$    
declare    
  res text;    
begin    
  if $1 >=1 then    
    select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);    
    return res;    
  end if;    
  return null;    
end;    
$$ language plpgsql strict;   

postgres=# select gen_hanzi(10) from generate_series(1,10);  
      gen_hanzi         
----------------------  
 騾歵癮崪圚祯骤氾準赔  
 縬寱癱办戾薶窍爉充環  
 鷊赶輪肸蒹焷尮禀漽湯  
 庰槖诤蜞礀链惧珿憗腽  
 憭釃轮訞陡切瀰煈瘐獵  
 韸琵慆蝾啈響夐捶燚積  
 菥芉阣瀤樂潾敾糩镽礕  
 廂垅欳事鎤懯劑搯蔷窡  
 覤綊伱鳪散噹镄灳毯杸  
 鳀倯鰂錾牓晟挗觑镈壯  
(10 rows)  

6、随机数组

create or replace function gen_rand_arr(int,int) returns int[] as $$    
  select array_agg((random()*$1)::int) from generate_series(1,$2);    
$$ language sql strict;    

postgres=# select gen_rand_arr(100,10) from generate_series(1,10);  
          gen_rand_arr             
---------------------------------  
 {69,11,12,70,7,41,81,95,83,17}  
 {26,79,20,21,64,64,51,90,38,38}  
 {3,64,46,28,26,55,39,12,69,76}  
 {66,38,87,78,8,94,18,88,89,1}  
 {6,14,81,26,36,45,90,87,35,28}  
 {25,38,91,71,67,17,26,5,29,95}  
 {82,94,32,69,72,40,63,90,29,51}  
 {91,34,66,72,60,1,17,50,88,51}  
 {77,13,89,69,84,56,86,10,61,14}  
 {5,43,8,38,11,80,78,74,70,6}  
(10 rows)  

7、连接符

postgres=# select concat('a', ' ', 'b');  
 concat   
--------  
 a b  
(1 row)  

8、自定义函数

通过自定义函数，可以生成很多有趣的数据。

随机身份证号

create or replace function gen_id(    
  a date,    
  b date    
)     
returns text as $$    
select lpad((random()*99)::int::text, 2, '0') ||     
       lpad((random()*99)::int::text, 2, '0') ||     
       lpad((random()*99)::int::text, 2, '0') ||     
       to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||     
       lpad((random()*99)::int::text, 2, '0') ||     
       random()::int ||     
       (case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ;    
$$ language sql strict;    

postgres=# select gen_id('1900-01-01', '2017-10-16') from generate_series(1,10);  
       gen_id         
--------------------  
 25614020061108330X  
 49507919010403271X  
 96764619970119860X  
 915005193407306113  
 551360192005045415  
 430005192611170108  
 299138191310237806  
 95149919670723980X  
 542053198501097403  
 482334198309182411  
(10 rows)  

建模

建模是指根据业务需求，设计表、函数、视图等。

建模完成后，需要创建对象。

构建测试数据

根据业务提供的数据限定条件，构建测试数据。

那么用户需要提供什么呢？

用户除了提供结构，还需要提供数据的layout，这些LAYOUT用于帮助构建真实的测试数据。

那么layout包含什么呢？实际上就包含了统计信息中要的东西。

《PostgreSQL 统计信息pg_statistic格式及导入导出dump_stat - 兼容Oracle》

postgres=# \d pg_stats       
                     View "pg_catalog.pg_stats"      
         Column         |   Type   | Collation | Nullable | Default       
------------------------+----------+-----------+----------+---------      
 schemaname             | name     |           |          |   -- 对象所属的schema    
 tablename              | name     |           |          |   -- 对象名    
 attname                | name     |           |          |   -- 列名    
 inherited              | boolean  |           |          |   -- 是否为继承表的统计信息（false时表示当前表的统计信息，true时表示包含所有继承表的统计信息）    
 null_frac              | real     |           |          |   -- 该列空值比例    
 avg_width              | integer  |           |          |   -- 该列平均长度    
 n_distinct             | real     |           |          |   -- 该列唯一值个数（-1表示唯一，小于1表示占比，大于等于1表示实际的唯一值个数）    
 most_common_vals       | anyarray |           |          |   -- 该列高频词    
 most_common_freqs      | real[]   |           |          |   -- 该列高频词对应的出现频率    
 histogram_bounds       | anyarray |           |          |   -- 该列柱状图（表示隔出的每个BUCKET的记录数均等）    
 correlation            | real     |           |          |   -- 该列存储相关性（-1到1的区间），绝对值越小，存储越离散。小于0表示反向相关，大于0表示正向相关    
 most_common_elems      | anyarray |           |          |   -- 该列为多值类型（数组）时，多值元素的高频词    
 most_common_elem_freqs | real[]   |           |          |   -- 多值元素高频词的出现频率    
 elem_count_histogram   | real[]   |           |          |   -- 多值元素的柱状图中，每个区间的非空唯一元素个数    

例子

create table a   -- 总共N条记录  
(  
  id int primary key,  -- 唯一值  
  c1 int,              -- 取值范围，有多少唯一值，有多少空值，相关性，有哪些高频词  
  c2 text,             -- 取值范围，有多少唯一值，有多少空值，相关性，平均长度，有哪些高频词  
  c3 timestamp         -- .......  
);  