Étape 3. Aplatissement de données¶
FLATTEN est une fonction de table qui produit une vue latérale d’une colonne VARIANT, OBJECT ou ARRAY. Dans cette étape, vous utilisez cette fonction pour explorer différents niveaux d’aplatissement.
Aplatir les objets du tableau dans une colonne de variantes¶
Vous pouvez aplatir les objets d’événement dans le tableau events dans des lignes distinctes à l’aide de la fonction FLATTEN. La sortie de la fonction comprend une colonne VALUE qui stocke ces événements individuels.
Vous pouvez ensuite utiliser le modificateur LATERAL pour joindre la sortie de la fonction FLATTEN à toute information extérieure à l’objet. Dans cet exemple, les device_type et version.
Interrogez les données de chaque événement :
SELECT src:device_type::string, src:version::String, VALUE FROM raw_source, LATERAL FLATTEN( INPUT => SRC:events );
La requête renvoie le résultat suivant :
+-------------------------+---------------------+-------------------------------------------------------------------------------+ | SRC:DEVICE_TYPE::STRING | SRC:VERSION::STRING | VALUE | |-------------------------+---------------------+-------------------------------------------------------------------------------| | server | 2.6 | { | | | | "f": 83, | | | | "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19", | | | | "t": 1437560931139, | | | | "v": { | | | | "ACHZ": 42869, | | | | "ACV": 709489, | | | | "DCA": 232, | | | | "DCV": 62287, | | | | "ENJR": 2599, | | | | "ERRS": 205, | | | | "MXEC": 487, | | | | "TMPI": 9 | | | | }, | | | | "vd": 54, | | | | "z": 1437644222811 | | | | } | | server | 2.6 | { | | | | "f": 1000083, | | | | "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22", | | | | "t": 1437036965027, | | | | "v": { | | | | "ACHZ": 6953, | | | | "ACV": 346795, | | | | "DCA": 250, | | | | "DCV": 46066, | | | | "ENJR": 9033, | | | | "ERRS": 615, | | | | "MXEC": 0, | | | | "TMPI": 112 | | | | }, | | | | "vd": 626, | | | | "z": 1437660796958 | | | | } | +-------------------------+---------------------+-------------------------------------------------------------------------------+
Utilisez une instruction CREATE TABLE AS SELECT pour stocker les résultats de la requête précédente dans une table :
CREATE OR REPLACE TABLE flattened_source AS SELECT src:device_type::string AS device_type, src:version::string AS version, VALUE AS src FROM raw_source, LATERAL FLATTEN( INPUT => SRC:events );
Interrogez la table résultante.
SELECT * FROM flattened_source;
La requête renvoie le résultat suivant :
+-------------+---------+-------------------------------------------------------------------------------+ | DEVICE_TYPE | VERSION | SRC | |-------------+---------+-------------------------------------------------------------------------------| | server | 2.6 | { | | | | "f": 83, | | | | "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19", | | | | "t": 1437560931139, | | | | "v": { | | | | "ACHZ": 42869, | | | | "ACV": 709489, | | | | "DCA": 232, | | | | "DCV": 62287, | | | | "ENJR": 2599, | | | | "ERRS": 205, | | | | "MXEC": 487, | | | | "TMPI": 9 | | | | }, | | | | "vd": 54, | | | | "z": 1437644222811 | | | | } | | server | 2.6 | { | | | | "f": 1000083, | | | | "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22", | | | | "t": 1437036965027, | | | | "v": { | | | | "ACHZ": 6953, | | | | "ACV": 346795, | | | | "DCA": 250, | | | | "DCV": 46066, | | | | "ENJR": 9033, | | | | "ERRS": 615, | | | | "MXEC": 0, | | | | "TMPI": 112 | | | | }, | | | | "vd": 626, | | | | "z": 1437660796958 | | | | } | +-------------+---------+-------------------------------------------------------------------------------+
Aplatir les clés des objets dans des colonnes distinctes¶
Dans l’exemple précédent, vous avez aplati les objets événements du tableau events dans des lignes distinctes. La table flattened_source résultante a conservé la structure de l’événement dans la colonne src du type VARIANT.
L’un des avantages de conserver les objets d’événements dans la colonne src du type VARIANT est que, lorsque le format de l’événement change, il n’est pas nécessaire de recréer et de repeupler ces tables. Mais vous avez également la possibilité de copier des clés individuelles dans l’objet d’événement dans des colonnes typées distinctes, comme le montre la requête suivante.
L’instruction CREATE TABLE AS SELECT suivante crée une nouvelle table nommée events avec les clés de l’objet événement stockées dans des colonnes distinctes. Chaque valeur est convertie vers le type de données correspondant à la valeur à l’aide de deux signes deux-points (::) suivis du type. Si vous ne faites pas de conversion, la colonne choisit le type de données VARIANT par défaut pouvant contenir toute valeur :
create or replace table events as
select
src:device_type::string as device_type
, src:version::string as version
, value:f::number as f
, value:rv::variant as rv
, value:t::number as t
, value:v.ACHZ::number as achz
, value:v.ACV::number as acv
, value:v.DCA::number as dca
, value:v.DCV::number as dcv
, value:v.ENJR::number as enjr
, value:v.ERRS::number as errs
, value:v.MXEC::number as mxec
, value:v.TMPI::number as tmpi
, value:vd::number as vd
, value:z::number as z
from
raw_source
, lateral flatten ( input => SRC:events );
L’instruction aplatit les données imbriquées dans la clé EVENTS.SRC:V en ajoutant une colonne distincte pour chaque valeur. L’instruction crée une ligne pour chaque paire clé/valeur. La sortie suivante montre les deux premiers enregistrements de la nouvelle table events :
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 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
Suivant : Étape 4. Mise à jour des données