Étape 4. Mise à jour des données¶

Jusqu’à présent, dans ce tutoriel, vous avez fait ce qui suit :

Copie de données d’événement JSON d’exemple d’un compartiment S3 dans la table RAW_SOURCE et exploration de requêtes simples.
Vous avez également exploré la fonction FLATTEN pour aplatir les données JSON et obtenir une représentation relationnelle des données. Par exemple, vous avez extrait des clés d’événements et stocké les clés dans des colonnes distinctes dans une autre table EVENTS.

Au début, le tutoriel explique le scénario d’application où plusieurs sources génèrent des événements et un point de terminaison Web les enregistre dans votre compartiment S3. À mesure que de nouveaux événements sont ajoutés au compartiment S3, vous pouvez utiliser un script pour copier continuellement les nouvelles données dans la table RAW_SOURCE. Mais comment insérer uniquement de nouvelles données d’événement dans la table EVENTS ?

Il existe de nombreuses façons de maintenir la cohérence des données. Cette section présente deux options.

Utiliser les colonnes de clé primaire pour la comparaison¶

Dans cette section, vous ajoutez une clé primaire à la table EVENTS. La clé primaire garantit alors l’unicité.

Examinez vos données JSON à la recherche de valeurs uniques et qui seraient de bonnes candidates pour une clé primaire. Par exemple, supposons que la combinaison de src:device_type et value:rv puisse être une clé primaire. Ces deux clés JSON correspondent aux colonnes DEVICE_TYPE et RV de la table EVENTS.

Note

Snowflake n’applique pas la contrainte de clé primaire. La contrainte sert plutôt de métadonnées identifiant la clé naturelle dans Information Schema.

Ajoutez la contrainte de clé primaire à la table EVENTS :

ALTER TABLE events ADD CONSTRAINT pk_DeviceType PRIMARY KEY (device_type, rv);

Copy

Insérez un nouvel enregistrement d’événement JSON dans la table RAW_SOURCE :

insert into raw_source
  select
  PARSE_JSON ('{
    "device_type": "cell_phone",
    "events": [
      {
        "f": 79,
        "rv": "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22",
        "t": 5769784730576,
        "v": {
          "ACHZ": 75846,
          "ACV": 098355,
          "DCA": 789,
          "DCV": 62287,
          "ENJR": 2234,
          "ERRS": 578,
          "MXEC": 999,
          "TMPI": 9
        },
        "vd": 54,
        "z": 1437644222811
      }
    ],
    "version": 3.2
  }');

Copy

Insérez le nouvel enregistrement que vous avez ajouté à la table RAW_SOURCE dans la table EVENTS en se basant sur une comparaison des valeurs de clés primaires :

insert into events
select
      src:device_type::string
    , src:version::string
    , value:f::number
    , value:rv::variant
    , value:t::number
    , value:v.ACHZ::number
    , value:v.ACV::number
    , value:v.DCA::number
    , value:v.DCV::number
    , value:v.ENJR::number
    , value:v.ERRS::number
    , value:v.MXEC::number
    , value:v.TMPI::number
    , value:vd::number
    , value:z::number
    from
      raw_source
    , lateral flatten( input => src:events )
    where not exists
    (select 'x'
      from events
      where events.device_type = src:device_type
      and events.rv = value:rv);

Copy

L’interrogation de la table EVENTS affiche la ligne ajoutée :

select * from EVENTS;

Copy

La requête renvoie le résultat suivant :

+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
| DEVICE_TYPE | VERSION |       F | RV                                                                   |             T |  ACHZ |    ACV | DCA |   DCV | ENJR | ERRS | MXEC | TMPI |  VD |             Z |
|-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------|
| server      | 2.6     |      83 | "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19"   | 1437560931139 | 42869 | 709489 | 232 | 62287 | 2599 |  205 |  487 |    9 |  54 | 1437644222811 |
| server      | 2.6     | 1000083 | "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22" | 1437036965027 |  6953 | 346795 | 250 | 46066 | 9033 |  615 |    0 |  112 | 626 | 1437660796958 |
| cell_phone  | 3.2     |      79 | "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22"           | 5769784730576 | 75846 |  98355 | 789 | 62287 | 2234 |  578 |  999 |    9 |  54 | 1437644222811 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+

Copy

Utiliser toutes les colonnes pour la comparaison¶

Si les données JSON ne comportent pas de champs susceptibles d’être des candidats à la clé primaire, vous pourriez comparer toutes les clés JSON répétitives de la table RAW_SOURCE avec les valeurs des colonnes correspondantes de la table EVENTS.

Aucune modification de votre table EVENTS existante n’est requise.

Insérez un nouvel enregistrement d’événement JSON dans la table RAW_SOURCE :

insert into raw_source
  select
  parse_json ('{
    "device_type": "web_browser",
    "events": [
      {
        "f": 79,
        "rv": "122375.99,744.89,386.99,12.45,78.08,43.7,9.22,8765.43",
        "t": 5769784730576,
        "v": {
          "ACHZ": 768436,
          "ACV": 9475,
          "DCA": 94835,
          "DCV": 88845,
          "ENJR": 8754,
          "ERRS": 567,
          "MXEC": 823,
          "TMPI": 0
        },
        "vd": 55,
        "z": 8745598047355
      }
    ],
    "version": 8.7
  }');

Copy

Insérer le nouvel enregistrement de la table RAW_SOURCE dans la table EVENTS en se basant sur une comparaison des valeurs de clés répétitives :

insert into events
select
      src:device_type::string
    , src:version::string
    , value:f::number
    , value:rv::variant
    , value:t::number
    , value:v.ACHZ::number
    , value:v.ACV::number
    , value:v.DCA::number
    , value:v.DCV::number
    , value:v.ENJR::number
    , value:v.ERRS::number
    , value:v.MXEC::number
    , value:v.TMPI::number
    , value:vd::number
    , value:z::number
    from
      raw_source
    , lateral flatten( input => src:events )
    where not exists
    (select 'x'
      from events
      where events.device_type = src:device_type
      and events.version = src:version
      and events.f = value:f
      and events.rv = value:rv
      and events.t = value:t
      and events.achz = value:v.ACHZ
      and events.acv = value:v.ACV
      and events.dca = value:v.DCA
      and events.dcv = value:v.DCV
      and events.enjr = value:v.ENJR
      and events.errs = value:v.ERRS
      and events.mxec = value:v.MXEC
      and events.tmpi = value:v.TMPI
      and events.vd = value:vd
      and events.z = value:z);

Copy

L’interrogation de la table EVENTS affiche la ligne ajoutée :

select * from EVENTS;

Copy

La requête renvoie le résultat suivant :

+-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
| DEVICE_TYPE | VERSION |       F | RV                                                                   |             T |   ACHZ |    ACV |   DCA |   DCV | ENJR | ERRS | MXEC | TMPI |  VD |             Z |
|-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------|
| server      | 2.6     |      83 | "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19"   | 1437560931139 |  42869 | 709489 |   232 | 62287 | 2599 |  205 |  487 |    9 |  54 | 1437644222811 |
| server      | 2.6     | 1000083 | "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22" | 1437036965027 |   6953 | 346795 |   250 | 46066 | 9033 |  615 |    0 |  112 | 626 | 1437660796958 |
| cell_phone  | 3.2     |      79 | "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22"           | 5769784730576 |  75846 |  98355 |   789 | 62287 | 2234 |  578 |  999 |    9 |  54 | 1437644222811 |
| web_browser | 8.7     |      79 | "122375.99,744.89,386.99,12.45,78.08,43.7,9.22,8765.43"              | 5769784730576 | 768436 |   9475 | 94835 | 88845 | 8754 |  567 |  823 |    0 |  55 | 8745598047355 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+

Copy

Suivant : Étape 5. Nettoyage