É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
etvalue:rv
puisse être une clé primaire. Ces deux clés JSON correspondent aux colonnesDEVICE_TYPE
etRV
de la tableEVENTS
.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);
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 }');
Insérez le nouvel enregistrement que vous avez ajouté à la table
RAW_SOURCE
dans la tableEVENTS
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;
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 | +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
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 }');
Insérer le nouvel enregistrement de la table
RAW_SOURCE
dans la tableEVENTS
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;
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 | +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
Suivant : Étape 5. Nettoyage