Étape 4. Mise à jour des données

Jusqu’à présent dans ce tutoriel, vous avez chargé un lot de données d’événement JSON dans une table RAW_SOURCE, l’avez interrogé et créé une table EVENTS composée de valeurs clés d’événement. Dans le scénario précédemment décrit dans ce tutoriel, les données JSON proviennent de sources multiples et passent par un point de terminaison Web vers votre compartiment S3. Vous pouvez écrire un script pour extraire de nouveaux fichiers de données dans la table RAW_SOURCE. Comment insérer uniquement de nouvelles données d’événement dans la table EVENTS ?

Il existe de nombreuses façons de différencier vos données JSON dans la table RAW_SOURCE et les données aplaties dans la table EVENTS. Cette section propose deux options :

Comparaison de données de clé principales

  1. Examinez vos données JSON à la recherche de valeurs uniques et qui seraient de bonnes candidates pour une clé primaire. Dans notre exemple actuel, supposons que la combinaison de src:device_type et value:rv ferait une bonne clé primaire composite. 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 le schéma d’informations.

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

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

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

    select * from EVENTS;
    
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
    | 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 |
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
    

Comparaison de toutes les clés/colonnes

Si vos données JSON n’incluent pas de valeurs de clé qui sont des candidates de clé primaire naturelle, vous pouvez comparer toutes les clés JSON répétitives dans la table RAW_SOURCE avec les valeurs de colonne correspondantes dans 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
      }');
    
  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);
    

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

    select * from EVENTS;
    
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
    | 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 |
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
    

Suivant : Étape 5. Félicitations !