Comment supprimer des enregistrements en double dans la table de base de données de flocon de neige

Si vous avez une clé primaire en tant que telle:

ALTER TABLE table_name SWAP WITH new_table_name

comme alors

CREATE TABLE new_table_name AS
SELECT id, name FROM (
    SELECT id
        ,name
        ,ROW_NUMBER() OVER (PARTITION BY id, name) AS rn
    FROM table_name
)
WHERE rn > 1

Mais si vous n'avez pas de clé unique, vous ne pouvez pas supprimer de cette façon. À quel point un

DELETE FROM fruit
WHERE key in (
  SELECT key 
  FROM (
      SELECT key
          ,ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY key) AS rn
      FROM fruit
  )
  WHERE rn > 1
);

puis échangez- les

CREATE TABLE fruit (key number, id number, name text);

insert into fruit values (1,1, 'Apple'), (2,1,'Apple'),
      (3,2, 'Apple'), (4,3, 'Orange'), (5,3, 'Orange');

Snowflake ne possède pas de clés primaires efficaces, leur utilisation se fait principalement avec les outils ERD. Snowflake n'a pas non plus quelque chose comme un ROWID, il n'y a donc aucun moyen d'identifier les doublons à supprimer.

Il est possible d'ajouter temporairement une colonne "is_duplicate", par exemple. numéroter tous les doublons avec la fonction ROW_NUMBER (), puis supprimer tous les enregistrements avec "is_duplicate"> 1 et enfin supprimer la colonne utilitaire.

Une autre façon consiste à créer une table en double et à permuter, comme d'autres l'ont suggéré. Cependant, les contraintes et les subventions doivent être conservées. Une façon de procéder est:

CREATE TABLE new_table LIKE old_table COPY GRANTS;
INSERT INTO new_table SELECT DISTINCT * FROM old_table;
ALTER TABLE old_table SWAP WITH new_table;

Le code ci-dessus supprime les doublons exacts . Si vous voulez retrouver avec une ligne pour chaque « PK » , vous devez inclure une logique pour sélectionner la copie que vous souhaitez conserver.

Cela illustre l'importance d'ajouter des colonnes d'horodatage de mise à jour dans un entrepôt de données Snowflake.

Votre question se résume à: Comment puis-je supprimer l'une des deux lignes parfaitement identiques? . Vous ne pouvez pas. Vous ne pouvez faire un DELETE FROM fruit where ID = 1 and Name = 'Apple'; , les deux lignes disparaîtront. Ou vous ne le faites pas, et gardez les deux.

Pour certaines bases de données, il existe des solutions de contournement utilisant des lignes internes, mais il n'y en a pas dans snowflake, voir https://support.snowflake.net/s/question/0D50Z00008FQyGqSAL/is-there-an-internalmetadata-unique-rowid-in -snowflake-que-je-peux-référencer . Vous ne pouvez pas non plus limiter les suppressions, votre seule option est donc de créer une nouvelle table et de la permuter.

Note supplémentaire sur la remarque de Hans Henrik Eriksen sur l'importance des horodatages de mise à jour: Ceci est une réelle aide lorsque les doublons ont été ajoutés plus tard. Si, par exemple, vous souhaitez conserver les valeurs les plus récentes, vous pouvez alors faire ceci:

-- setup
create table fruit (ID Integer, Name VARCHAR(16777216), "UPDATED_AT" TIMESTAMP_NTZ);
insert into fruit values (1, 'Apple', CURRENT_TIMESTAMP::timestamp_ntz)
, (2, 'Apple', CURRENT_TIMESTAMP::timestamp_ntz)
, (3, 'Orange', CURRENT_TIMESTAMP::timestamp_ntz);
-- wait > 1 nanosecond
insert into fruit values (1, 'Apple', CURRENT_TIMESTAMP::timestamp_ntz)
, (3, 'Orange', CURRENT_TIMESTAMP::timestamp_ntz);

-- delete older duplicates (DESC)
DELETE FROM fruit
  WHERE (ID
  , UPDATED_AT) IN (
     SELECT ID
     , UPDATED_AT
     FROM (
         SELECT ID
         , UPDATED_AT
         , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY UPDATED_AT DESC) AS rn
         FROM fruit
     )
     WHERE rn > 1
  );

cela me dérange aussi depuis un certain temps. Comme snowflake a ajouté la prise en charge de la qualification, vous pouvez désormais créer une table dédupliquée avec une seule instruction sans sous-sélection:

CREATE TABLE fruit (id number, nam text);
insert into fruit values (1, 'Apple'), (1,'Apple'),
      (2, 'Apple'), (3, 'Orange'), (3, 'Orange');


CREATE OR REPLACE TABLE fruit AS 
SELECT * FROM 
fruit 
qualify row_number() OVER (PARTITION BY id, nam ORDER BY id, nam) = 1;
SELECT * FROM fruit;

Bien sûr, il vous reste une nouvelle table et un historique de table lâche, des clés primaires, des clés étrangères, etc.

Basé sur les idées ci-dessus ..... la requête suivante a parfaitement fonctionné dans mon cas.

CREATE OR REPLACE TABLE SCHEMA.table
 AS
SELECT
    DISTINCT * 
FROM
    SCHEMA.table
  ;