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

  1. 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 );
    
    Copy

    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                                                          |
    |                         |                     | }                                                                             |
    +-------------------------+---------------------+-------------------------------------------------------------------------------+
    
  2. 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 );
    
    Copy

    Interrogez la table résultante.

    SELECT * FROM flattened_source;
    
    Copy

    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 );
Copy

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 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
Copy

Suivant : Étape 4. Mise à jour des données