Update where rownum

Version imprimable

Voir 40 message(s) de cette discussion en une page

25/06/2018, 10h55
Le-DOC

Update where rownum

Bonjour tous le monde,

Je teste une méthode d'anonymisation, et j'aurai besoin d'un coup de main pour écrire ma requete:

J'ai une table CLIENT:
NOM | Prenom | etc...

et une table ANON
NOM | Prenom | etc...

J'aimerai mettre a jour le champ CLIENT.NOM avec les données de ANON.NOM en faisant correspondre les rownum de CLIENT et de ANON.

J'ai trouvé une méthode, mais en passant par une table de travail, ce que j'aimerais éviter.

J'ai vu plusieurs scripts avec MERGE, mais il y a toujours le problème d’existence du rownum lors du ON.

Auriez vous une idée ?
25/06/2018, 11h00
Waldar

rownum est une pseudo colonne évaluée à chaque requête, ce n'est absolument pas déterministe.
25/06/2018, 11h03
Le-DOC

C'est justement le but.
je comptais mettre de coté les 2 requêtes avec le rownum dans un WITH, et les lier pour l'UPDATE.
25/06/2018, 11h08
Ikebukuro

Idem que Waldar : évite rownum pour des UPDATE ou DELETE car ce n'est pas un identifiant de la base mais une pseudo-colonne.

Quelles sont les PK des deux tables? Pourquoi ne pas les utiliser?
Et quelle était ta solution passant par une table intermédiaire?

Dans ce cas, il faut aliaser la column ROWNUM

Code:

1
2
3
4
5
6
7
8
9
10
11
12
WITH t1 AS (SELECT ROWNUM num, CHR(LEVEL + 64) b FROM dual connect BY LEVEL < 5),
t2 AS (SELECT ROWNUM num, CHR(LEVEL + 96) b FROM dual connect BY LEVEL < 5)
SELECT *
FROM t1, t2
WHERE t1.num = t2.num
 
 
NUM	B	NUM_1	B_1
1	"A"	1	"a"
2	"B"	2	"b"
3	"C"	3	"c"
4	"D"	4	"d"

mon objectif est d'anonymiser des données, donc pas besoin d'identifier les colonnes, je veux simplement qu'elle soient différentes en se basant sur ma table ANON.
Ma table client peut avoir différent type de PK (plusieurs databases), c'est pourquoi je ne peux pas me baser dessus.

ma méthode en utilisant la table de travail (sur le shéma SCOTT) est:
Code:

1 2 3 4 5 6 7 8 9 10 CREATE TABLE ANON.TEST_EMP AS WITH A AS (SELECT ROWNUM ID, ENAME FROM SCOTT.EMP), B AS (SELECT ROWNUM ID, NOM FROM ANON.ANON) SELECT A.ID , A.ENAME, B.NOM FROM A, B WHERE A.ID = B.ID UPDATE SCOTT.EMP A SET A.ENAME = (SELECT B.NOM FROM ANON.TEST_EMP B WHERE A.ENAME = B.ENAME) WHERE EXISTS (SELECT 1 FROM ANON.TEST_EMP B WHERE A.ENAME = B.ENAME);

Quel est l'intérêt de votre solution par rapport à :
Code:

1 2 3 update Matable set Nom = null , Prenom = null;
Ou encore :
Code:

1 2 3 update Matable set Nom = dbms_random.string('a', length(Nom)) , Prenom = dbms_random.string('a', length(Prenom));
Il y a quelque chose qui m'échappe dans votre besoin.

25/06/2018, 15h09
McM

Je pense que quand tu anonymises des données, tu souhaites que les données restent cohérentes et lisibles par un humain.. et que les contraintes restent respectées (exemple une ville, un code postal, etc..)
25/06/2018, 15h19
Le-DOC

En fait, je souhaite que les données restent lisible.
C'est toujours mieux de voir un user "John Smith" que un "adflhjl nklhfdmq"

Apres, concernant la cohérence ville, CP, ça serait une prochaine étape, mais je doute qu'elle ai son importance dans la plupart des applications que je gère.
L'objectif serait aussi de gérer des matricules, ou identifiants basé sur ma table d'anonymisation.

Toutes les données de ma table ANON sont formatées au besoin.

Enfin, ça ne me choque pas qu'un Francis Cabrel devienne un John Smith après une anonymisation, puis devienne un Jane Jackson après un autre coup d'anonymisation. tout dépend s'il y a un contrôle du sexe au niveau de l'application.
27/06/2018, 11h21
Le-DOC

Vous avez une idée alors, de comment je pourrais mettre à jour ces données ?
27/06/2018, 12h14
Ikebukuro

As-tu testé la solution de McM?
27/06/2018, 16h14
Le-DOC

La solution de McM est un peu prés la même que la mienne dans le CREATE TABLE.
Mais mon objectif final est de faire un UPDATE, pas un SELECT :)

Pour ne pas être obligé d'avoir le # de données d'anonymisation == # de données réelles et éviter d'avoir les mêmes couples nom-prénom qui se répètent,
voici un petit "proof of concept" :

Code:

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
 
-- anonymisation data, id de 1 à N sans "trou"
create table a_names(
  id number(19,0) primary key,
  last_name varchar2(256 char),
  first_name varchar2(256 char)
);
 
create table t_data(
    nsq number(19,0) primary key,
    last_name varchar2(256 char),
    first_name varchar2(256 char)
);
 
insert into a_names(id, last_name, first_name) values(1,'a', 'I');
insert into a_names(id, last_name, first_name) values(2,'b', 'II');
insert into a_names(id, last_name, first_name) values(3,'c', 'III');
insert into a_names(id, last_name, first_name) values(4,'d', 'IV');
insert into a_names(id, last_name, first_name) values(5,'e', 'V');
insert into a_names(id, last_name, first_name) values(6,'f', 'VI');
 
insert into t_data(nsq, last_name, first_name) values(1,'A','1');
insert into t_data(nsq, last_name, first_name) values(2,'B','2');
insert into t_data(nsq, last_name, first_name) values(3,'C','3');
insert into t_data(nsq, last_name, first_name) values(4,'D','4');
insert into t_data(nsq, last_name, first_name) values(5,'E','5');
insert into t_data(nsq, last_name, first_name) values(6,'F','6');
insert into t_data(nsq, last_name, first_name) values(7,'G','7');
insert into t_data(nsq, last_name, first_name) values(8,'H','8');
insert into t_data(nsq, last_name, first_name) values(9,'I','9');
insert into t_data(nsq, last_name, first_name) values(10,'J','10');
insert into t_data(nsq, last_name, first_name) values(11,'K','11');
 
 
update t_data d set d.last_name = (
  with rnd_ids as (
    select nsq, round(dbms_random.value(1,(select count(id) from a_names))) as rid from t_data
  ) 
  select a.last_name from rnd_ids r
    join a_names a on a.id = r.rid
  where r.nsq = d.nsq
),
 d.first_name = (
  with rnd_ids as (
    select nsq, round(dbms_random.value(1,(select count(id) from a_names))) as rid from t_data
  ) 
  select a.first_name from rnd_ids r
    join a_names a on a.id = r.rid
  where r.nsq = d.nsq
)
 
;

NSQ	LAST_NAME	FIRST_NAME
1	b	II
2	e	V
3	d	V
4	e	III
5	c	III
6	e	II
7	d	I
8	d	II
9	b	IV
10	d	III
11	a	I

28/06/2018, 17h09
mnitu

Regardez DBMS_REDACT dans la documentation d'Oracle.
29/06/2018, 14h01
JeitEmgie

Cela fonctionne sur base de l'utilisateur ORACLE... ce qui limite les cas d'utilisation...
29/06/2018, 14h28
skuatamad

En même temps, c'est le forum Oracle, normal de proposer des solutions Oracle

Autre solution utilisation l'instruction MERGE et respectant le gender d'origine

Code:

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
create table a_lastnames(
  last_name varchar2(256 char) primary key
);
 
 
 
create table a_firstnames(
  first_name varchar2(256 char),
  gender char(1),
  constraint pk_firstnames  primary key(first_name, gender) 
);
 
create table t_data(
    nsq number(19,0) primary key,
    last_name varchar2(256 char),
    first_name varchar2(256 char),
    gender char(1)
);
 
 
insert into a_lastnames(last_name) values('a');
insert into a_lastnames(last_name) values('b');
insert into a_lastnames(last_name) values('c');
insert into a_lastnames(last_name) values('d');
insert into a_lastnames(last_name) values('e');
insert into a_lastnames(last_name) values('f');
 
 
 
insert into a_firstnames(first_name, gender) values('I', 'm');
insert into a_firstnames(first_name, gender) values('II', 'f');
insert into a_firstnames(first_name, gender) values('III', 'm');
insert into a_firstnames(first_name, gender) values('IV', 'f');
insert into a_firstnames(first_name, gender) values('V', 'm');
insert into a_firstnames(first_name, gender) values('VI', 'f');
 
 
 
insert into t_data(nsq, last_name, first_name, gender) values(1,'A','1', 'm');
insert into t_data(nsq, last_name, first_name, gender) values(2,'B','2', 'f');
insert into t_data(nsq, last_name, first_name, gender) values(3,'C','3', 'm');
insert into t_data(nsq, last_name, first_name, gender) values(4,'D','4', 'm');
insert into t_data(nsq, last_name, first_name, gender) values(5,'E','5', 'f');
insert into t_data(nsq, last_name, first_name, gender) values(6,'F','6', 'm');
insert into t_data(nsq, last_name, first_name, gender) values(7,'G','7', 'f');
insert into t_data(nsq, last_name, first_name, gender) values(8,'H','8', 'f');
insert into t_data(nsq, last_name, first_name, gender) values(9,'I','9', 'm');
insert into t_data(nsq, last_name, first_name, gender) values(10,'J','10', 'f');
insert into t_data(nsq, last_name, first_name, gender) values(11,'K','11', 'm');
 
 
merge into t_data d
using (
with x_male_names as (
  select rownum as id, ln.last_name, fn.first_name, fn.gender
    from a_lastnames ln, a_firstnames fn
  where fn.gender = 'm'
),
x_female_names as (
  select rownum as id, ln.last_name, fn.first_name, fn.gender
    from a_lastnames ln, a_firstnames fn
  where fn.gender = 'f'
),
male_random_ids as (
  select nsq, round(dbms_random.value(1,(select count(id) from x_male_names))) as rid
    from t_data where gender = 'm'
),
female_random_ids as (
  select nsq, round(dbms_random.value(1,(select count(id) from x_female_names))) as rid
    from t_data where gender = 'f'
)
select d.nsq, nvl(m.last_name, f.last_name) as last_name, nvl(m.first_name, f.first_name) as first_name, d.gender
  from t_data d
  left join male_random_ids mr on mr.nsq = d.nsq and d.gender = 'm'
    left join x_male_names m on m.id = mr.rid
  left join female_random_ids fr on fr.nsq = d.nsq and d.gender = 'f'
    left join x_female_names f on f.id = fr.rid
)
nd on (nd.nsq = d.nsq)
when matched then 
update set d.last_name = nd.last_name, d.first_name = nd.first_name
;
 
 
select * from t_data ;

NSQ	LAST_NAME	FIRST_NAME	GENDER
1	f	V	m
2	e	VI	f
3	d	V	m
4	c	I	m
5	b	II	f
6	b	I	m
7	c	IV	f
8	a	VI	f
9	d	V	m
10	b	IV	f
11	e	III	m

04/07/2018, 09h45
Le-DOC

Je viens de regarder vos com.

Le poc de JeitEmgie a l'air interessant pour d'autres cas, je vais le mettre de coté :)

Concernant la fonction Oracle, je la garde au cas ou, pour plus tard, mais vu le nombre de versions de bases que j'ai encore en 9 et 10, je cherchais une méthode plus conventionnelle.

Le merge de JeitEmgie me parait interessant. Au début je me disais que je ne pouvais pas utiliser les clé (champ nsq dans ton exemple) car pas d'unicité avec la table d'anonymisation, mais dans ce cas, seul la clé de la table cible est utilisé, donc ca pourrait coller peu importe le cas.

Je teste tout ca a mon retour de congé, et vous donne mes résultats.

Merci

Autre POC basé sur MERGE mais dans le cas où non seulement il faut respecter le GENDER du prénom mais (hypothèse) la LANG du nom de famille :
(attention celui-ci a besoin de LATERAL donc 12c ou il faut l'activer manuellement en 11g)

Code:

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
 
create table a_lastnames(
  last_name varchar2(256 char),
  lang varchar2(2 char),
  constraint pk_lastnames  primary key(last_name, lang) 
)
organization index;
 
create table a_firstnames(
  first_name varchar2(256 char),
  gender char(1),
  constraint pk_firstnames  primary key(first_name, gender) 
)
organization index;
 
 
create table t_data(
    nsq number(19,0) primary key,
    last_name varchar2(256 char),
    lang varchar2(2 char),
    first_name varchar2(256 char),
    gender char(1)
);
 
 
insert into a_lastnames(last_name, lang) values('a', 'en');
insert into a_lastnames(last_name, lang) values('b', 'fr');
insert into a_lastnames(last_name, lang) values('c', 'de');
insert into a_lastnames(last_name, lang) values('d', 'it');
insert into a_lastnames(last_name, lang) values('e', 'en');
insert into a_lastnames(last_name, lang) values('f', 'fr');
 
 
insert into a_firstnames(first_name, gender) values('I', 'm');
insert into a_firstnames(first_name, gender) values('II', 'f');
insert into a_firstnames(first_name, gender) values('III', 'm');
insert into a_firstnames(first_name, gender) values('IV', 'f');
insert into a_firstnames(first_name, gender) values('V', 'm');
insert into a_firstnames(first_name, gender) values('VI', 'f');
 
 
insert into t_data(nsq, last_name, lang, first_name, gender) values(1,'A', 'fr','1', 'm');
insert into t_data(nsq, last_name, lang, first_name, gender) values(2,'B', 'it','2', 'f');
insert into t_data(nsq, last_name, lang, first_name, gender) values(3,'C', 'de','3', 'm');
insert into t_data(nsq, last_name, lang, first_name, gender) values(4,'D', 'en','4', 'm');
insert into t_data(nsq, last_name, lang, first_name, gender) values(5,'E', 'en','5', 'f');
insert into t_data(nsq, last_name, lang, first_name, gender) values(6,'F', 'fr','6', 'm');
insert into t_data(nsq, last_name, lang, first_name, gender) values(7,'G', 'fr','7', 'f');
insert into t_data(nsq, last_name, lang, first_name, gender) values(8,'H', 'fr','8', 'f');
insert into t_data(nsq, last_name, lang, first_name, gender) values(9,'I', 'en','9', 'm');
insert into t_data(nsq, last_name, lang, first_name, gender) values(10,'J', 'de','10', 'f');
insert into t_data(nsq, last_name, lang, first_name, gender) values(11,'K', 'it','11', 'm');
 
merge INTO t_data d USING
(
    WITH x_names AS (
        SELECT
            row_number() over(partition BY ln.lang, fn.gender order by ln.last_name) AS
            id, ln.last_name, ln.lang, fn.first_name, fn.gender
        FROM
            a_lastnames ln, a_firstnames fn
    ) ,
    x_lang_gender AS (
        SELECT DISTINCT
            lang, gender
        FROM
            a_lastnames ln, a_firstnames fn
    ) ,
    all_random_ids AS (
        SELECT
            nsq,
            ROUND(dbms_random.value(1,
                ( SELECT COUNT(id) FROM x_names x WHERE x.lang   = j.lang AND x.gender = j.gender )
            ) ) AS rid, d.last_name, d.first_name, d.lang, d.gender
        FROM
            t_data d,
            lateral ( SELECT x.lang, x.gender FROM x_lang_gender x WHERE x.lang   = d.lang AND x.gender = d.gender ) j
        WHERE
            d.lang   = j.lang AND d.gender = j.gender
    )
    SELECT
        d.nsq, mr.last_name AS last_name, mr.first_name AS first_name, d.gender, d.lang, m.id, m.last_name as new_last_name, m.first_name as new_first_name
    FROM
        t_data d
    JOIN all_random_ids mr ON mr.nsq   = d.nsq 
    LEFT JOIN  ( SELECT * FROM x_names  ) m ON m.id = mr.rid and m.gender = mr.gender and m.lang = mr.lang
)
nd on (nd.nsq = d.nsq) 
WHEN matched THEN
    UPDATE
    SET
        d.last_name  = nd.new_last_name,
        d.first_name = nd.new_first_name ;
 
select * from t_data ;

Rebonjour a tous,

Je me replonge sur les méthodes d'anonymisations que vous m'avez proposés, et surtout celle de JeitEmgie m’intéresse beaucoup.
En revanche, la ou ça coince, c'est qu'elle se base sur une clé unique (nsq) sur la table t_data de ton exemple.

Dans mon cas, j'aurai des tables qui auront des clés non unique, voir meme pas de clé du tout (je sais c'est pas bien, mais c'est pas moi qui développe les applications :D)

J'ai repris ton MERGE, en supprimant les contrôles de sexe (inutile dans mon cas), mais je vois pas comment je peux me débarasser de cette clé.
Sachant que je peux très bien avoir plusieurs personnes avec le même nom dans ma base.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 merge into t_data2 d using ( with x_names as ( select rownum as id, ln.nom data_anon from anon.anon ln ), random_ids as ( select id nsq, round(dbms_random.value(1,(select count(id) from x_names))) as rid from t_data2 ) select d.id nsq , xn.data_anon from t_data2 d left join random_ids mr on mr.nsq = d.id left join x_names xn on xn.id = mr.rid ) nd on (nd.nsq = d.id) when matched then update set d.first_name = nd.data_anon ;

Voir 40 message(s) de cette discussion en une page