Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Didacticiel : Faire une requête de données imbriquées avec Amazon Redshift Spectrum

Ce didacticiel explique comment interroger des données imbriquées avec Redshift Spectrum. Les données imbriquées sont des données qui contiennent des champs imbriqués. Les champs imbriqués sont des champs assemblés en une seule entité, tels que des tableaux, des structures ou des objets.

Présentation

Amazon Redshift Spectrum prend en charge l'interrogation de données imbriquées dans les formats de fichier ParquetORC,JSON, et Ion. Redshift Spectrum accède aux données à l’aide de tableaux externes. Vous pouvez créer des tableaux externes qui utilisent les types de données complexes struct, array, et map.

Par exemple, supposons que votre fichier de données contienne les données suivantes dans Amazon S3 dans un fichier nommé customers. Bien qu'il n'y ait pas un seul élément racine, chaque JSON objet de cet exemple de données représente une ligne dans un tableau.

{"id": 1,
 "name": {"given": "John", "family": "Smith"},
 "phones": ["123-457789"],
 "orders": [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50},
            {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}]
}
{"id": 2,
 "name": {"given": "Jenny", "family": "Doe"},
 "phones": ["858-8675309", "415-9876543"],
 "orders": []
}
{"id": 3,
 "name": {"given": "Andy", "family": "Jones"},
 "phones": [],
 "orders": [{"shipdate": "2018-03-02T08:02:15.000Z", "price": 13.50}]
}  

Vous pouvez utiliser Amazon Redshift Spectrum pour soumettre une requête sur les données imbriquées dans des fichiers. Le didacticiel suivant vous montre comment effectuer cette opération avec les données Apache Parquet.

Prérequis

Si vous n’utilisez pas encore Redshift Spectrum, suivez les étapes détaillées dans Mise en route avec Amazon Redshift Spectrum avant de poursuivre.

Pour créer un schéma externe, remplacez le IAM rôle ARN dans la commande suivante par le rôle ARN que vous avez créé dans Créer un IAM rôle. Exécutez ensuite la commande dans votre SQL client.

create external schema spectrum 
from data catalog 
database 'myspectrum_db' 
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
create external database if not exists;

Étape 1 : Création d’un tableau externe contenant des données imbriquées

Vous pouvez afficher les données source en les téléchargeant depuis Amazon S3.

Exécutez la commande suivante pour créer un tableau externe pour ce didacticiel.

CREATE EXTERNAL TABLE spectrum.customers (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';     

Dans l’exemple précédent, le tableau externe spectrum.customers utilise les types de données struct et array pour définir les colonnes dotées de données imbriquées. Amazon Redshift Spectrum prend en charge l'interrogation de données imbriquées dans les formats de fichier ParquetORC,JSON, et Ion. Le paramètre STORED AS est PARQUET pour les fichiers Apache Parquet. Le paramètre LOCATION doit se référer au dossier Amazon S3 contenant les données ou fichiers imbriqués. Pour de plus amples informations, veuillez consulter CREATE EXTERNAL TABLE.

Vous pouvez imbriquer les types array et struct à n’importe quel niveau. Vous pouvez par exemple définir une colonne nommée toparray, comme l’illustre l’exemple suivant.

toparray array<struct<nestedarray:
         array<struct<morenestedarray: 
         array<string>>>>>

Vous pouvez également imbriquer les types struct comme l’illustre la colonne x dans l’exemple suivant.

x struct<a: string,
         b: struct<c: integer,
                   d: struct<e: string>
                  >
        >

Étape 2 : interrogez vos données imbriquées dans Amazon S3 avec des extensions SQL

Redshift Spectrum prend en charge les requêtes et array les map types struct complexes grâce à des extensions de la syntaxe Amazon Redshift. SQL

Extension 1 : Accès aux colonnes de structs

Vous pouvez extraire des données à partir des colonnes struct à l’aide d’une notation par points qui connecte les noms de champs en chemins. Par exemple, la requête suivante retourne les noms et prénoms de clients. Le prénom est obtenu via le long chemin c.name.given. Le nom de famille est obtenu via le long chemin c.name.family.

SELECT c.id, c.name.given, c.name.family
FROM   spectrum.customers c;

La requête précédente renvoie les données suivantes.

id | given | family
---|-------|-------
1  | John  | Smith
2  | Jenny | Doe
3  | Andy  | Jones
(3 rows)

Un struct peut être une colonne d’un autre struct, qui peut être une colonne d’un autre struct et ce à tout niveau. Les chemins qui accèdent aux colonnes dans des struct aussi profondément imbriqués peuvent être très longs. Par exemple, regardez la définition pour la colonne x dans l’illustration suivante.

x struct<a: string,
         b: struct<c: integer, 
                      d: struct<e: string>
                  >
        >

Vous pouvez accéder aux données dans e en tant que x.b.d.e.

Extension 2 : Répartition des tableaux dans une clause FROM

Vous pouvez extraire des données de colonnes array (et, par extension, des colonnes map) en précisant les colonnes array dans une clause FROM à la place des noms de tableaux. L’extension s’applique à la clause FROM de la requête principale, ainsi qu’aux clauses FROM des sous-requêtes.

Vous pouvez référencer les éléments array d’après leur position, comme c.orders[0] (version préliminaire).

En combinant le surplombage de arrays avec des raccords, vous pouvez réaliser différentes sortes de désimbrication, comme les cas d’utilisation suivants l’expliquent.

Désimbrication à l’aide de raccords internes

La requête suivante sélectionne les dates d'expédition IDs des clients et des commandes pour les clients qui ont des commandes. L'SQLextension de la FROM clause c.orders o dépend de l'aliasc.

SELECT c.id, o.shipdate
FROM   spectrum.customers c, c.orders o 

Pour chaque c de client disposant de commandes, la clause FROM renvoie une ligne pour chaque commande o du client c. Cette ligne combine la ligne du client c et la ligne de commande o. Ensuite, la clause SELECT garde uniquement le c.id et le o.shipdate. Le résultat est le suivant.

id|      shipdate
--|----------------------
1 |2018-03-01  11:59:59
1 |2018-03-01  09:10:00
3 |2018-03-02  08:02:15
(3 rows) 

L’alias c fournit un accès aux champs du client et l’alias o fournit un accès aux champs de commande.

La sémantique est similaire à celle de la norme. SQL Vous pouvez envisager la clause FROM comme exécutant la boucle imbriquée suivante qui est suivie par la sélection de champs pour la sortie par SELECT.

for each customer c in spectrum.customers
  for each order o in c.orders
     output c.id and o.shipdate 

Ainsi, si un client ne dispose pas de commande, le client n’apparaît pas dans le résultat.

Vous pouvez également envisager ceci comme une clause FROM qui exécute un JOIN avec le tableau customers et le pan orders. Dans les faits, vous pouvez également rédiger la requête comme l’illustre l’exemple suivant.

SELECT c.id, o.shipdate
FROM   spectrum.customers c INNER JOIN c.orders o ON true 

Désimbrication à l’aide de raccords gauches

La requête suivante sort tous les noms des clients et leurs commandes. Si un client n’a pas encore placé de commande, son nom sera tout de même renvoyé. Toutefois, dans ce cas, les colonnes de commande sont les suivantesNULL, comme indiqué dans l'exemple suivant pour Jenny Doe.

SELECT c.id, c.name.given, c.name.family, o.shipdate, o.price
FROM   spectrum.customers c LEFT JOIN c.orders o ON true

La requête précédente renvoie les données suivantes.

id  |  given  | family  |    shipdate          | price
----|---------|---------|----------------------|--------
 1  |  John   | Smith   | 2018-03-01  11:59:59 | 100.5
 1  |  John   | Smith   | 2018-03-01  09:10:00 |  99.12
 2  |  Jenny  | Doe     |                      |
 3  |  Andy   | Jones   | 2018-03-02  08:02:15 |  13.5
 (4 rows)

Extension 3 : Obtenir directement à un pan de scalaires en utilisant un alias

Lorsqu’un alias p dans une clause FROM surplombe tout un tableau de scalaires, la requête se réfère aux valeurs de p en tant que p. Par exemple, la requête suivante produit des paires de noms de clients et de numéros de téléphone.

SELECT c.name.given, c.name.family, p AS phone
FROM   spectrum.customers c LEFT JOIN c.phones p ON true  

La requête précédente renvoie les données suivantes.

given  |  family  |  phone
-------|----------|-----------
John   | Smith    | 123-4577891
Jenny  | Doe      | 858-8675309
Jenny  | Doe      | 415-9876543
Andy   | Jones    | 
(4 rows) 

Extension 4 : Accès aux éléments de cartes

Redshift Spectrum traite le type de donnée map comme un type de array contenant des types struct avec une colonne key et une colonne value. La key doit être scalar; la valeur peut être de n’importe quel type de donnée.

Par exemple, le code suivant crée un tableau externe avec une map pour stocker les numéros de téléphone.

CREATE EXTERNAL TABLE spectrum.customers2 (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones map<varchar(20), varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';

Parce qu’un type de map se comporte comme un type de array avec des colonnes key et value, vous pouvez penser aux schémas précédents comme s’ils étaient les suivants.

CREATE EXTERNAL TABLE spectrum.customers3 (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<struct<key:varchar(20), value:varchar(20)>>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';   

La requête suivante renvoie les noms de clients avec un numéro de téléphone mobile et renvoie le numéro pour chaque nom. La requête de carte est traitée comme l’équivalent d’une requête de array imbriquée de types struct. La requête suivante ne renvoie des données que si vous avez créé le tableau externe précédemment évoqué.

SELECT c.name.given, c.name.family, p.value 
FROM   spectrum.customers c, c.phones p 
WHERE  p.key = 'mobile';