2. 软件开发
    3. PostgreSQL 数据采样与脱敏

    PostgreSQL 数据采样与脱敏

    xiaoxiao2024-07-14  93

    标签

    PostgreSQL , 采样 , 脱敏

    背景

    测试数据

    postgres=# create table test(id int primary key, username text, phonenum text, addr text, pwd text, crt_time timestamp); CREATE TABLE postgres=# insert into test select id, 'test_'||id, 13900000000+(random()*90000000)::int, '中国杭州xxxxxxxxxxxxxxxxxx'||random(), md5(random()::text), clock_timestamp() from generate_series(1,10000000) t(id); INSERT 0 10000000 postgres=# select * from test limit 10; id | username | phonenum | addr | pwd | crt_time ----+----------+-------------+---------------------------------------------+----------------------------------+---------------------------- 1 | test_1 | 13950521974 | 中国杭州xxxxxxxxxxxxxxxxxx0.953363882377744 | 885723a5f4938808235c5debaab473ec | 2017-06-02 15:05:55.465132 2 | test_2 | 13975998000 | 中国杭州xxxxxxxxxxxxxxxxxx0.91321265604347 | 7ea01dc02c0fbc965f38d1bf12b303eb | 2017-06-02 15:05:55.46534 3 | test_3 | 13922255548 | 中国杭州xxxxxxxxxxxxxxxxxx0.846756176557392 | 7c2992bdc69312cbb3bb135dd2b98491 | 2017-06-02 15:05:55.46535 4 | test_4 | 13985121895 | 中国杭州xxxxxxxxxxxxxxxxxx0.639280265197158 | 202e32f0f0e3fe669c00678f7acd2485 | 2017-06-02 15:05:55.465355 5 | test_5 | 13982757650 | 中国杭州xxxxxxxxxxxxxxxxxx0.501174578908831 | b6a42fc1ebe9326ad81a81a5896a5c6c | 2017-06-02 15:05:55.465359 6 | test_6 | 13903699864 | 中国杭州xxxxxxxxxxxxxxxxxx0.193029860965908 | f6bc06e5cda459d09141a2c93f317cf2 | 2017-06-02 15:05:55.465363 7 | test_7 | 13929797532 | 中国杭州xxxxxxxxxxxxxxxxxx0.192601112183183 | 75c12a3f14c7ef3e558cef79d84a7e8e | 2017-06-02 15:05:55.465368 8 | test_8 | 13961108182 | 中国杭州xxxxxxxxxxxxxxxxxx0.900682372972369 | 5df33d15cf7726f2fb57df3ed913b306 | 2017-06-02 15:05:55.465371 9 | test_9 | 13978455210 | 中国杭州xxxxxxxxxxxxxxxxxx0.87795089604333 | cbe233f00cdd3c61c67415c1f8691846 | 2017-06-02 15:05:55.465375 10 | test_10 | 13957044022 | 中国杭州xxxxxxxxxxxxxxxxxx0.410478914622217 | cdf2f98b0ff5a973efaca6a82625e283 | 2017-06-02 15:05:55.465379 (10 rows)

    采样

    9.5以前的版本，高效采样请参考

    《PostgreSQL 巧妙的数据采样方法》

    9.5以及以后的版本，可以使用tablesample语法进行采样（注意，采样过滤器在where条件过滤器的前面）。

    语法如下

    https://www.postgresql.org/docs/9.6/static/sql-select.html

    TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] sampling_method指采样方法 argument指参数，例如采样比例。 REPEATABLE(seed) 指采样随机种子，如果种子一样，那么多次采样请求得到的结果是一样的。如果忽略REPEATABLE则每次都是使用新的seed值，得到不同的结果。

    例子1，BERNOULLI(百分比)采样，使用全表扫描的采样方法，按采样参数百分比返回。

    postgres=# select * from test TABLESAMPLE bernoulli (1); id | username | phonenum | addr | pwd | crt_time ---------+--------------+-------------+------------------------------------------------+----------------------------------+---------------------------- 110 | test_110 | 13967004360 | 中国杭州xxxxxxxxxxxxxxxxxx0.417577873915434 | 437e5c29e12cbafa0563332909436d68 | 2017-06-02 15:05:55.46585 128 | test_128 | 13901119801 | 中国杭州xxxxxxxxxxxxxxxxxx0.63212554808706 | 973dba4b35057d44997eb4744eea691b | 2017-06-02 15:05:55.465938 251 | test_251 | 13916668924 | 中国杭州xxxxxxxxxxxxxxxxxx0.0558807463385165 | 71217eedce421bd0f475c0e4e6eb32a9 | 2017-06-02 15:05:55.466423 252 | test_252 | 13981440056 | 中国杭州xxxxxxxxxxxxxxxxxx0.457073447294533 | 6649c37c0f0287637a4cb80d84b6bde0 | 2017-06-02 15:05:55.466426 423 | test_423 | 13982447202 | 中国杭州xxxxxxxxxxxxxxxxxx0.816960731055588 | 11a8d6d1374cf7565877def6a147f544 | 2017-06-02 15:05:55.46717 ......

    例子2，SYSTEM(百分比)采样，使用块级采样方法，按采样参数百分比返回（被采样到的数据块，内的所有记录都将被返回）。因此离散度不如BERNOULLI，但是效率高很多。

    postgres=# select * from test TABLESAMPLE system (1); id | username | phonenum | addr | pwd | crt_time ---------+--------------+-------------+------------------------------------------------+----------------------------------+---------------------------- 6986 | test_6986 | 13921391589 | 中国杭州xxxxxxxxxxxxxxxxxx0.874497607816011 | e6a5d695aca17de0f6489d740750c758 | 2017-06-02 15:05:55.495697 6987 | test_6987 | 13954425190 | 中国杭州xxxxxxxxxxxxxxxxxx0.374216149561107 | 813fffbf1ee7157c459839987aa7f4b0 | 2017-06-02 15:05:55.495721 6988 | test_6988 | 13901878095 | 中国杭州xxxxxxxxxxxxxxxxxx0.624850326217711 | 5056caaad5e076f82b8caec9d02169f6 | 2017-06-02 15:05:55.495725 6989 | test_6989 | 13940504557 | 中国杭州xxxxxxxxxxxxxxxxxx0.705925882328302 | a5b4062086a3261740c82774616e64ee | 2017-06-02 15:05:55.495729 6990 | test_6990 | 13987358496 | 中国杭州xxxxxxxxxxxxxxxxxx0.981084300205112 | 6ba0b6c9d484e6fb90181dc86cb6598f | 2017-06-02 15:05:55.495734 6991 | test_6991 | 13948658183 | 中国杭州xxxxxxxxxxxxxxxxxx0.6592857837677 | 9a0eadd056eeb6e3c1e2b984777cdf6b | 2017-06-02 15:05:55.495738 6992 | test_6992 | 13934074866 | 中国杭州xxxxxxxxxxxxxxxxxx0.232706854119897 | 84f6649beac3b78a3a1afeb9c3aabccd | 2017-06-02 15:05:55.495741 ......

    用户还可以通过以下接口自定义采样方法

    https://www.postgresql.org/docs/9.6/static/tablesample-method.html

    脱敏

    脱敏的手段很多，用户对脱敏的需求也可能很多。

    常见的例如

    1. 隐藏字符串中间的内容，使用*表示，同时保持原始长度

    2. 隐藏字符串中间的内容，使用*表示，不保持原始长度

    3. 返回加密值

    不管什么需求，实际上就是数据的转换，从原始值，转换为目标值。在PostgreSQL中可以通过function实现这样的转换，对不同的需求，编写不同的转换逻辑即可。

    例子，将字符串中间部分模糊化，只显示字符串头2个，末尾1个。

    select id, substring(username,1,2)||'******'||substring(username,length(username),1), substring(phonenum,1,2)||'******'||substring(phonenum, length(phonenum),1), substring(addr,1,2)||'******'||substring(addr, length(addr),1), substring(pwd,1,2)||'******'||substring(pwd, length(pwd),1), crt_time from test TABLESAMPLE bernoulli (1); id | ?column? | ?column? | ?column? | ?column? | crt_time ---------+-----------+-----------+-------------+-----------+---------------------------- 69 | te******9 | 13******5 | 中国******9 | c0******2 | 2017-06-02 15:32:26.261624 297 | te******7 | 13******2 | 中国******1 | d9******6 | 2017-06-02 15:32:26.262558 330 | te******0 | 13******5 | 中国******3 | bd******0 | 2017-06-02 15:32:26.262677 335 | te******5 | 13******5 | 中国******6 | 08******f | 2017-06-02 15:32:26.262721 416 | te******6 | 13******6 | 中国******2 | b3******d | 2017-06-02 15:32:26.26312 460 | te******0 | 13******4 | 中国******8 | e5******f | 2017-06-02 15:32:26.26332 479 | te******9 | 13******1 | 中国******1 | 1d******4 | 2017-06-02 15:32:26.263393 485 | te******5 | 13******0 | 中国******3 | a3******8 | 2017-06-02 15:32:26.263418 692 | te******2 | 13******9 | 中国******4 | 69******8 | 2017-06-02 15:32:26.264326 1087 | te******7 | 13******9 | 中国******3 | 8e******5 | 2017-06-02 15:32:26.266091 1088 | te******8 | 13******8 | 中国******7 | 37******e | 2017-06-02 15:32:26.266095 1116 | te******6 | 13******8 | 中国******2 | 4c******3 | 2017-06-02 15:32:26.266235 1210 | te******0 | 13******4 | 中国******8 | 49******c | 2017-06-02 15:32:26.266671 ......

    如果需要更复杂的转换，写PostgreSQL的UDF对字段值进行转换即可。

    将采样结果抽取到其他平台的方法也很多，例如copy到stdout，或者ETL工具等。

    例子

    psql test -c "copy (select id, substring(username,1,2)||'******'||substring(username,length(username),1), substring(phonenum,1,2)||'******'||substring(phonenum, length(phonenum),1), substring(addr,1,2)||'******'||substring(addr, length(addr),1), substring(pwd,1,2)||'******'||substring(pwd, length(pwd),1), crt_time from test TABLESAMPLE bernoulli (1) ) to stdout" > ./sample_test.log less sample_test.log 54 te******4 13******4 中国******3 52******b 2017-06-02 15:32:26.261451 58 te******8 13******6 中国******3 23******a 2017-06-02 15:32:26.261584 305 te******5 13******6 中国******9 c0******4 2017-06-02 15:32:26.262587 399 te******9 13******5 中国******4 71******7 2017-06-02 15:32:26.26298 421 te******1 13******0 中国******4 21******3 2017-06-02 15:32:26.263139 677 te******7 13******5 中国******5 e2******7 2017-06-02 15:32:26.264269 874 te******4 13******9 中国******2 a6******9 2017-06-02 15:32:26.265159

    参考

    《PostgreSQL 巧妙的数据采样方法》

    https://www.postgresql.org/docs/9.6/static/tablesample-method.html

    https://www.postgresql.org/docs/9.6/static/sql-select.html

    最新回复(0)