É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é.

  1. 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.

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

    ALTER TABLE events ADD CONSTRAINT pk_DeviceType PRIMARY KEY (device_type, rv);
    
    Copy
  3. 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
  4. 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.

  1. 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
  2. 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