Automatisation de la gestion des données JSON semi-structurées dans Snowflake

Automatisation de la gestion des données JSON semi-structurées dans Snowflake


Cet article de blog présente une technique permettant de créer automatiquement des vues de base de données en fonction de la structure des données JSON stockées dans les tables Snowflake. C’est un vrai gain de temps, et vous trouverez le code complet ainsi qu’un exemple d’utilisation à la fin du deuxième partie de cet article de blog. Ou, lisez la suite si vous souhaitez creuser un peu plus, vous trouverez ici des détails sur le fonctionnement exact de tout!

Aperçu

L’une des caractéristiques les plus convaincantes de Snowflake est sa prise en charge native des données semi-structurées. De la formats de fichiers pris en charge, JSON est l’un des plus largement utilisés en raison de son format d’échange de données relativement léger et de la facilité avec laquelle il peut être écrit et lu par les humains et les machines. Données JSON peut être chargé directement dans les colonnes de table de type UNE VARIANTE, puis interrogé à l’aide d’instructions SQL SELECT qui référencent des éléments de document JSON par leurs chemins hiérarchiques. Cela nécessite cependant des compétences techniques, ainsi que des connaissances sur le JSON structure interrogée. Ainsi, de nombreuses organisations déploient des vues qui masquent essentiellement cette complexité, rendant ainsi les données plus facilement accessibles aux utilisateurs finaux. Cependant, la création et la maintenance de ces vues est un processus manuel. Ces articles de blog décrivent une technique qui automatise la création de ces vues, ce qui peut aider à réduire les erreurs et à gagner du temps.

Arrière plan

Prenons l’exemple d’ensemble de données JSON suivant :

{
  "ID": 1,
  "color": "black",
  "category": "hue",
  "type": "primary",
  "code": {
    "rgb": "255,255,255",
    "hex": "#000"
  }
},{
  "ID": 2,
  "color": "white",
  "category": "value",
  "code": {
    "rgb": "0,0,0",
    "hex": "#FFF"
  }
},{
  "ID": 3,
  "color": "red",
  "category": "hue",
  "type": "primary",
  "code": {
    "rgb": "255,0,0",
    "hex": "#FF0"
  }
},{
  "ID": 4,
  "color": "blue",
  "category": "hue",
  "type": "primary",
  "code": {
    "rgb": "0,0,255",
    "hex": "#00F"
  }
},{
  "ID": 5,
  "color": "yellow",
  "category": "hue",
  "type": "primary",
  "code": {
    "rgb": "255,255,0",
    "hex": "#FF0"
  }
},{
  "ID": 6,
  "color": "green",
  "category": "hue",
  "type": "secondary",
  "code": {
    "rgb": "0,255,0",
    "hex": "#0F0"
  }
}   

Bien que les données JSON soient généralement chargées en bloc dans une table de destination à l’aide d’un Opération COPIER DANSl’ensemble de données ci-dessus est suffisamment petit pour pouvoir être chargé dans une table Snowflake à l’aide d’une commande CREATE TABLE AS (CTAS), comme ceci :

CREATE OR REPLACE TABLE colors AS
SELECT 
   parse_json(column1) AS json_data
FROM VALUES
   ('{
      "ID": 1,
      "color": "black",
      "category": "hue",
      "type": "primary",
      "code": {
        "rgb": "255,255,255",
        "hex": "#000"
      }
    }'),
   ('{
      "ID": 2,
      "color": "white",
      "category": "value",
      "code": {
        "rgb": "0,0,0",
        "hex": "#FFF"
      }
    }'),
   ('{
      "ID": 3,
      "color": "red",
      "category": "hue",
      "type": "primary",
      "code": {
        "rgb": "255,0,0",
        "hex": "#FF0"
      }
    }'),
   ('{
      "ID": 4,
      "color": "blue",
      "category": "hue",
      "type": "primary",
      "code": {
        "rgb": "0,0,255",
        "hex": "#00F"
      }
    }'),
   ('{
      "ID": 5,
      "color": "yellow",
      "category": "hue",
      "type": "primary",
      "code": {
        "rgb": "255,255,0",
        "hex": "#FF0"
      }
    }'),
   ('{
      "ID": 6,
      "color": "green",
      "category": "hue",
      "type": "secondary",
      "code": {
        "rgb": "0,255,0",
        "hex": "#0F0"
      }
    }')
    as raw_json;

Voici comment ces données peuvent être interrogées :

SELECT 
    json_data:ID::INTEGER as ID,
    json_data:color::STRING as color, 
    json_data:category::STRING as category, 
    json_data:type::STRING as type,
    json_data:code.rgb::STRING as code_rgb, 
    json_data:code.hex::STRING as code_hex 
FROM 
    colors;
+----+--------+----------+-----------+-------------+----------+                 
| ID | COLOR  | CATEGORY | TYPE      | CODE_RGB    | CODE_HEX |
|----+--------+----------+-----------+-------------+----------|
|  1 | black  | hue      | primary   | 255,255,255 | #000     |
|  2 | white  | value    | NULL      | 0,0,0       | #FFF     |
|  3 | red    | hue      | primary   | 255,0,0     | #FF0     |
|  4 | blue   | hue      | primary   | 0,0,255     | #00F     |
|  5 | yellow | hue      | primary   | 255,255,0   | #FF0     |
|  6 | green  | hue      | secondary | 0,255,0     | #0F0     |
+----+--------+----------+-----------+-------------+----------+

Notez les références aux éléments de document JSON par leurs chemins hiérarchiques, par exemple :

json_data:code.rgb::STRING as code_rgb

Où:

  • json_data : spécifie la colonne VARIANT dans laquelle résident les données JSON
  • code.rgb : est le chemin d’accès à l’élément RVB dans la structure du document JSON
  • STRING : convertit les données dans le type approprié
  • code_rgb : est l’alias de la colonne correspondante dans le jeu de résultats

Une vue peut être créée manuellement qui masque essentiellement cette complexité à la communauté des utilisateurs finaux, telle que :

CREATE OR REPLACE VIEW
    colors_vw
AS SELECT 
    json_data:ID::INTEGER as ID,
    json_data:color::STRING as color, 
    json_data:category::STRING as category, 
    json_data:type::STRING as type,
    json_data:code.rgb::STRING as code_rgb, 
    json_data:code.hex::STRING as code_hex 
FROM 
    colors;

Le problème ici est que quelqu’un doit créer manuellement cette vue. Et cette vue devrait être mise à jour si des éléments supplémentaires sont ajoutés au JSON sous-jacent, d’où la volonté d’automatiser ce processus !

Afficher les étapes de création

Pour automatiser la création d’une telle vue, nous aurons besoin de connaître deux informations clés sur chaque élément de la structure du document JSON :

  • Les chemins vers chaque élément
  • Le type de données de chaque élément

Il s’avère que nous pouvons tirer parti d’un Jointure LATERAL à une sous-requête FLATTEN à obtenir des informations sur les éléments individuels de la structure du document JSON, comme ceci :

SELECT DISTINCT
   f.path,
   typeof(f.value)
FROM 
  colors,
  LATERAL FLATTEN(json_data, RECURSIVE=>true) f
WHERE
  TYPEOF(f.value) != 'OBJECT';
+----------+-----------------+                                                  
| PATH     | TYPEOF(F.VALUE) |
|----------+-----------------|
| ID       | INTEGER         |
| category | VARCHAR         |
| code.hex | VARCHAR         |
| code.rgb | VARCHAR         |
| type     | VARCHAR         |
| color    | VARCHAR         |
+----------+-----------------+

Maintenant que nous savons comment obtenir ces informations, nous aurons besoin d’un mécanisme de programmation nous permettant d’automatiser réellement le processus de création de vues. Voici ce qu’il devra faire :

  1. Construire la requête qui renvoie les éléments et leurs types de données
  2. Exécutez la requête
  3. Boucle à travers les éléments retournés
  4. Construire la liste des colonnes de la vue
  5. Construisez la vue Langage de définition de données (DDL) :
    CRÉER UNE VUE … COMME SÉLECTIONNER …
  6. Exécutez le DDL pour créer la vue

Il se trouve que Procédures stockées JavaScript de Snowflake sont parfaits pour ce cas d’utilisation.

Composants de procédure stockée

La procédure stockée que nous allons construire acceptera trois paramètres :

  • TABLE_NAME : nom de la table qui contient les données semi-structurées.
  • COL_NAME : le nom de la colonne VARIANT dans la table susmentionnée.
  • VIEW_NAME : nom de la vue à créer par la procédure stockée.

Voici à quoi ressemblera le shell de notre procédure stockée :

create or replace procedure create_view_over_json 
   (TABLE_NAME varchar, COL_NAME varchar, VIEW_NAME varchar)
returns varchar
language javascript
as
$
Build the view… 
$;

Examinons chacune des étapes décrites précédemment, ainsi que le code de procédure stockée correspondant.

1. Construire la requête qui renvoie les éléments et leurs tTypes de données

La variable requête_élément contiendra le texte de la requête :

var element_query = "SELECT DISTINCT n" +
                    path_name + " AS path_name, n" +
                    attribute_type + " AS attribute_type, n" +
                    alias_name + " AS alias_name n" +
                    "FROM n" + 
                    TABLE_NAME + ", n" +
                    "LATERAL FLATTEN(" + COL_NAME + ", RECURSIVE=>true) f n" +
                    "WHERE TYPEOF(f.value) != 'OBJECT' n";

Notez les références aux NOM DE LA TABLE et COL_NAME paramètres.

Les définitions du nom de chemin, du type d’attribut et du nom d’alias ont été séparées de cette définition de requête pour en faciliter la lecture. Voici comment chacun d’entre eux est défini :

  • Nom du chemin: Cela génère des chemins avec des niveaux entourés de guillemets doubles (exemple : « path ». »to ». »element ») :
    var path_name = « regexp_replace(f.path,'(\\w+)’,' »\\1″’) » ;
    Notez les instances doublées du ” caractère d’échappement ; – cela doit être fait car puisqu’il y a deux interpréteurs impliqués dans l’exécution de l’instruction (JavasScript et SQL).
  • Type d’attribut: Cela génère uniquement les types de données de colonne ARRAY, BOOLEAN, FLOAT et STRING :
    var type_attribut = « DECODE (substr(typeof(f.value),1,1),’A’,’ARRAY’,’B’,’BOOLEAN’,’I’,’FLOAT’,’D’,’FLOAT ‘,’CHAÎNE DE CARACTÈRES’) »;
    Cela permet de s’assurer que la clause DISTINCT du SELECT fonctionne correctement, ce qui évite les cas où le même élément est renvoyé avec plusieurs types de données (par exemple, INTEGER et FLOAT, par exemple). Nous en discuterons plus en détail dans le deuxième article de blog.
  • Alias: Cela génère des alias de colonne basés sur le chemin :
    var alias_name = « REGEXP_REPLACE(REGEXP_REPLACE(f.path, ‘\\[(.+)\\]’),'[^a-zA-Z0-9]’,’_’) » ;

Il est important de noter que la requête renverra ces trois valeurs pour chaque élément de la structure du document JSON.

2. Exécutez la requête

C’est assez simple. Tout d’abord, le texte de la requête contenu dans by requête_élément est utilisé pour créer un objet d’instruction exécutable à l’aide de la méthode createStatement():

var element_stmt = snowflake.createStatement({sqlText:element_query});

Ensuite, l’objet d’instruction est exécuté à l’aide de la méthode execute():

var element_res = element_stmt.execute();

Notez que le jeu de résultats de la requête est retourné comme le élément_res objet.

3. Boucle à travers les éléments retournés

Le but ici est de créer des expressions de colonne qui ressemblent à ceci : nom_col : « nom » . »first »::STRING as name_first

Cela se fait en bouclant à travers le jeu de résultats de la requête en utilisant le méthode suivante() et construire l’expression de colonne souhaitée en utilisant le chemin :

while (element_res.next()) {
      col_list += COL_NAME + ":" + element_res.getColumnValue(1);   // Path name
      col_list += "::" + element_res.getColumnValue(2);             // Datatype
      col_list += " as " + element_res.getColumnValue(3);           // Alias
     }

Notez que le COL_NAME Le paramètre est à nouveau utilisé ici dans le cadre de la construction de l’expression de colonne.

4. Construire la liste des colonnes de vue

Il s’avère que la liste des colonnes était en cours de construction alors que nous parcourions le jeu de résultats de la requête. Nous l’avons fait par le biais d’une série d’annexes au col_list chaîne (le += l’opérateur le fait dans le code indiqué dans la section ci-dessus).

5. Construire la vue DDL

Maintenant que nous avons la liste des colonnes, nous pouvons simplement la « coller » dans la chaîne DDL de la vue, comme ceci :

var view_ddl = "CREATE OR REPLACE VIEW " + VIEW_NAME + " AS n" +
               "SELECT n" + col_list + "n" +
               "FROM " + TABLE_NAME;

6. Exécutez le DDL pour créer la vue

Même s’il s’agit d’une instruction DDL, nous utiliserons la même createStatement() et exécuter() méthodes que nous avons utilisées lorsque nous avons exécuté la requête de liste d’éléments (étape 2, ci-dessus):

var view_stmt = snowflake.createStatement({sqlText:view_ddl});
var view_res = view_stmt.execute();

Mettre tous ensemble

Maintenant que nous avons tous les éléments dont nous avons besoin, nous sommes prêts à créer la procédure stockée. Voici le code complet :

create or replace procedure create_view_over_json (TABLE_NAME varchar, COL_NAME varchar, VIEW_NAME varchar)
returns varchar
language javascript
as
$
// CREATE_VIEW_OVER_JSON - Craig Warman, Snowflake Computing, DEC 2019
//
// This stored procedure creates a view on a table that contains JSON data in a column.
// of type VARIANT.  It can be used for easily generating views that enable access to 
// this data for BI tools without the need for manual view creation based on the underlying 
// JSON document structure.  
//
// Parameters:
// TABLE_NAME    - Name of table that contains the semi-structured data.
// COL_NAME      - Name of VARIANT column in the aforementioned table.
// VIEW_NAME     - Name of view to be created by this stored procedure.
//
// Usage Example:
// call create_view_over_json('db.schema.semistruct_data', 'variant_col', 'db.schema.semistruct_data_vw');
//
// Important notes:
//   - This is the "basic" version of a more sophisticated procedure. Its primary purpose
//     is to illustrate the view generation concept.
//   - This version of the procedure does not support:
//         - Column case preservation (all view column names will be case-insensitive).
//         - JSON document attributes that are SQL reserved words (like TYPE or NUMBER).
//         - "Exploding" arrays into separate view columns - instead, arrays are simply
//           materialized as view columns of type ARRAY.
//   - Execution of this procedure may take an extended period of time for very 
//     large datasets, or for datasets with a wide variety of document attributes
//     (since the view will have a large number of columns).
//
// Attribution:
// I leveraged code developed by Alan Eldridge as the basis for this stored procedure.

var path_name = "regexp_replace(regexp_replace(f.path,'\\\\[(.+)\\\\]'),'(\\\\w+)','"\\\\1\"')"                           // This generates paths with levels enclosed by double quotes (ex: "path"."to"."element").  It also strips any bracket-enclosed array element references (like "[0]")
var attribute_type = "DECODE (substr(typeof(f.value),1,1),'A','ARRAY','B','BOOLEAN','I','FLOAT','D','FLOAT','STRING')";    // This generates column datatypes of ARRAY, BOOLEAN, FLOAT, and STRING only
var alias_name = "REGEXP_REPLACE(REGEXP_REPLACE(f.path, '\\\\[(.+)\\\\]'),'[^a-zA-Z0-9]','_')" ;                           // This generates column aliases based on the path
var col_list = "";

// Build a query that returns a list of elements which will be used to build the column list for the CREATE VIEW statement
var element_query = "SELECT DISTINCT \n" +
                    path_name + " AS path_name, \n" +
                    attribute_type + " AS attribute_type, \n" +
                    alias_name + " AS alias_name \n" +
                    "FROM \n" + 
                    TABLE_NAME + ", \n" +
                    "LATERAL FLATTEN(" + COL_NAME + ", RECURSIVE=>true) f \n" +
                    "WHERE TYPEOF(f.value) != 'OBJECT' \n" +
                    "AND NOT contains(f.path,'[') ";      // This prevents traversal down into arrays;

// Run the query...
var element_stmt = snowflake.createStatement({sqlText:element_query});
var element_res = element_stmt.execute();

// ...And loop through the list that was returned
while (element_res.next()) {

// Add elements and datatypes to the column list
// They will look something like this when added: 
//    col_name:"name"."first"::STRING as name_first, 
//    col_name:"name"."last"::STRING as name_last   

   if (col_list != "") {
      col_list += ", \n";}
   col_list += COL_NAME + ":" + element_res.getColumnValue(1);   // Start with the element path name
   col_list += "::" + element_res.getColumnValue(2);             // Add the datatype
   col_list += " as " + element_res.getColumnValue(3);           // And finally the element alias 
}

// Now build the CREATE VIEW statement
var view_ddl = "CREATE OR REPLACE VIEW " + VIEW_NAME + " AS \n" +
               "SELECT \n" + col_list + "n\" +
               "FROM " + TABLE_NAME;

// Now run the CREATE VIEW statement
var view_stmt = snowflake.createStatement({sqlText:view_ddl});
var view_res = view_stmt.execute();
return view_res.next();
$;

Voici comment il serait exécuté sur la table COLORS créée précédemment :

call create_view_over_json('mydatabase.public.colors', 'json_data', 'mydatabase.public.colors_vw');

Notez les trois paramètres mentionnés précédemment :

  • TABLE_NAME : le nom de la table qui contient les données semi-structurées (mydatabase.public.colors).
  • COL_NAME : le nom de la colonne VARIANT dans la table susmentionnée (json_data).
  • VIEW_NAME : nom de la vue à créer par la procédure stockée (mydatabase.public.colors_vw).

Si vous exécutez le code ci-dessus, puis regardez l’onglet Historique, vous verrez trois instructions SQL en cours d’exécution. Tout d’abord, vous verrez l’appel de procédure stockée, puis en dessous, vous verrez où il a exécuté la requête qui renvoie les éléments et leurs types de données ; puis vous verrez où il a exécuté le DDL de création de vue :

Procedure call

Cliquez sur le DDL généré par la procédure stockée. Ça devrait ressembler a quelque chose comme ca:

CREATE OR REPLACE VIEW mydatabase.public.colors_vw AS 
SELECT 
json_data:"ID"::FLOAT as ID, 
json_data:"category"::STRING as category, 
json_data:"code"."hex"::STRING as code_hex, 
json_data:"code"."rgb"::STRING as code_rgb, 
json_data:"color"::STRING as color, 
json_data:"type"::STRING as type
FROM mydatabase.public.colors

Voici la description de la vue :

desc view colors_vw;
+----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name     | type              | kind   | null? | default | primary key | unique key | check | expression | comment |
|----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| ID       | FLOAT             | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| CATEGORY | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| CODE_HEX | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| CODE_RGB | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| COLOR    | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| TYPE     | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Et, enfin, voici à quoi ressemble l’ensemble de données résultant :

select * from colors_vw;
+----+----------+----------+-------------+--------+-----------+                 
| ID | CATEGORY | CODE_HEX | CODE_RGB    | COLOR  | TYPE      |
|----+----------+----------+-------------+--------+-----------|
|  1 | hue      | #000     | 255,255,255 | black  | primary   |
|  2 | value    | #FFF     | 0,0,0       | white  | NULL      |
|  3 | hue      | #FF0     | 255,0,0     | red    | primary   |
|  4 | hue      | #00F     | 0,0,255     | blue   | primary   |
|  5 | hue      | #FF0     | 255,255,0   | yellow | primary   |
|  6 | hue      | #0F0     | 0,255,0     | green  | secondary |
+----+----------+----------+-------------+--------+-----------+

Il est beaucoup plus facile d’exécuter cet appel de procédure stockée que de créer manuellement la vue !

Et après?

Beaucoup, en fait. Cette procédure stockée était en fait assez basique dans sa construction ; – son objectif principal est d’illustrer le concept de la façon dont le processus global fonctionne essentiellement. Avant d’envisager d’utiliser cette procédure stockée de manière régulière, certains éléments doivent être pris en compte :

  • Cas de colonne : La casse des colonnes de la vue générée doit-elle correspondre à celle des éléments JSON, ou toutes les colonnes doivent-elles simplement être en majuscule ? À ce stade, la procédure stockée crée simplement toutes les colonnes de la vue en majuscules, ce qui est le comportement par défaut de Snowflake.
  • Type de colonne : En ce moment, nous définissons le type de données de chaque colonne de vue afin qu’il corresponde à celui des données sous-jacentes. Mais, il arrive parfois que plusieurs documents JSON aient des attributs portant le même nom mais contiennent en réalité des données avec des types de données différents, ce qui peut entraîner des problèmes.
  • Tableaux : Les documents JSON peuvent contenir à la fois des tableaux simples et des tableaux d’objets, et la procédure stockée actuelle les renvoie simplement sous forme de colonnes de vue de type de données ARRAY. Il serait souhaitable que le contenu de ces tableaux soit exposé sous forme de colonnes de vue séparées.

Nous aborderons chacun d’entre eux dans la deuxième partie de cet article de blog !

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.