Cas d’utilisation de données imbriquées

Cette rubrique décrit les cas d'utilisation des données imbriquées. 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.

Vous pouvez combiner les extensions décrites précédemment avec les SQL fonctionnalités habituelles. Les cas d’utilisation suivants illustrent certaines combinaisons courantes. Ces exemples aident à illustrer comment utiliser des données imbriquées. Elles ne font pas partie du didacticiel.

Ingérer des données imbriquées

Vous pouvez utiliser une déclaration CREATE TABLE AS pour ingérer des données à partir d’un tableau externe contenant des types de données complexes. La requête suivante extrait tous les clients et leurs numéros de téléphone à partir du tableau externe, à l’aide de LEFT JOIN et les stocke dans le tableau Amazon Redshift CustomerPhones.

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

Agréger des données imbriquées avec des sous-requêtes

Vous pouvez utiliser une sous-requête pour agréger des données imbriquées. L’exemple suivant illustre cette approche.

SELECT c.name.given, c.name.family, (SELECT COUNT(*) FROM c.orders o) AS ordercount 
FROM   spectrum.customers c;

Les données suivantes sont renvoyées.

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

Note

La manière la plus efficace d’agréger des données imbriquées en les groupant par ligne parent est illustrée dans l’exemple précédent. Dans cet exemple, les lignes imbriquées de c.orders sont groupées par leur ligne parent c. Autrement, si vous savez que l’id est unique pour chaque customer et que o.shipdate n’a jamais une valeur nulle, vous pouvez agréger de la manière illustrée dans l’exemple suivant. Cependant, cette approche n’est généralement pas aussi efficace que l’exemple précédent.

SELECT    c.name.given, c.name.family, COUNT(o.shipdate) AS ordercount 
FROM      spectrum.customers c LEFT JOIN c.orders o ON true 
GROUP BY  c.id, c.name.given, c.name.family;

Vous pouvez également rédiger la requête en utilisant une sous-requête dans la clause FROM qui se réfère à un alias (c) de la requête d’ancêtre et extrait les données de tableau. L’exemple suivant illustre cette approche.

SELECT c.name.given, c.name.family, s.count AS ordercount
FROM   spectrum.customers c, (SELECT count(*) AS count FROM c.orders o) s;    

Joindre Amazon Redshift et des données imbriquées

Vous pouvez également raccorder les données Amazon Redshift aux données imbriquées dans un tableau externe. Par exemple, supposons que vous avez les données imbriquées suivantes dans Amazon S3.

CREATE EXTERNAL TABLE spectrum.customers2 (
  id      int,
  name    struct<given:varchar(20), family:varchar(20)>,
  phones  array<varchar(20)>,
  orders  array<struct<shipdate:timestamp, item:int>>
);

Supposons également que vous disposez du tableau suivant dans Amazon Redshift.

CREATE TABLE prices (
  id int,
  price double precision
);

La requête suivante trouve le nombre total et le montant de chaque achat du client selon ce qui précède. L’illustration suivante n’est qu’un exemple. Elle ne renvoie des données que si vous avez créé les tableaux précédemment évoqués.

SELECT   c.name.given, c.name.family, COUNT(o.date) AS ordercount, SUM(p.price) AS ordersum 
FROM     spectrum.customers2 c, c.orders o, prices p ON o.item = p.id  
GROUP BY c.id, c.name.given, c.name.family;