Interrogation de données semi-structurées

Mode de mise au point

Interrogation de données semi-structurées - Amazon Redshift

Navigation Désimbriquer des requêtes Dépivotement d’objet Typage dynamique Sémantique laxiste Types d’introspection Classer par

Avec Amazon Redshift, vous pouvez interroger et analyser des données semi-structurées, telles que JSON, Avro ou Ion, parallèlement à vos données structurées. Les données semi-structurées font référence à des données dotées d'un schéma flexible, permettant des structures hiérarchiques ou imbriquées. Les sections suivantes montrent comment interroger des données semi-structurées à l'aide de la prise en charge des formats de données ouverts par Amazon Redshift, ce qui vous permet de débloquer des informations précieuses à partir de structures de données complexes.

Amazon Redshift utilise le langage PartiQL pour offrir un accès compatible SQL aux données relationnelles, semi-structurées et imbriquées.

PartiQL fonctionne avec des types dynamiques. Cette approche permet un filtrage, une jonction et une agrégation intuitifs sur la combinaison de jeux de données structurés, semi-structurés et imbriqués. La syntaxe PartiQL utilise la notation par points et l’index de tableau pour la navigation dans les chemins lors de l’accès aux données imbriquées. Il permet également aux éléments de la clause FROM d’itérer sur des tableaux et de les utiliser pour des opérations de désimbrication. Ci-dessous, vous pouvez trouver des descriptions des différents modèles de requête qui combinent l’utilisation du type de données SUPER avec la navigation par chemin et par tableau, la désimbrication, le dépivotement ou les jointures.

Pour en savoir plus sur les tables utilisées dans l’exemple suivant, consultez Jeu de données échantillon SUPER.

Amazon Redshift utilise PartiQL pour permettre la navigation dans les tableaux et les structures en utilisant respectivement la notation entre crochets [...] et la notation par points. En outre, vous pouvez mélanger la navigation dans des structures en utilisant notation par points avec la navigation dans des tableaux en utilisant la notation entre crochets. Par exemple, l’exemple suivant suppose que la colonne de données c_orders SUPER est un tableau structuré et qu’un attribut est nommé o_orderkey.

Pour intégrer des données dans la table customer_orders_lineitem, exécutez la commande suivante. Remplacez le rôle IAM par vos propres informations d’identification.


COPY customer_orders_lineitem FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/customer_orders_lineitem'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;

Amazon Redshift utilise également un alias de table comme préfixe de la notation. L’exemple suivant est la même requête que celle de l’exemple précédent.


SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

Vous pouvez utiliser la notation par points et crochets dans tous les types de requêtes, comme le filtrage, la jointure et l’agrégation. Vous pouvez utiliser ces notations dans une requête dans laquelle il y a normalement des références de colonne. L’exemple suivant utilise une instruction SELECT qui filtre les résultats.


SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0]. o_orderkey IS NOT NULL;

L’exemple suivant utilise la notation par points et crochets dans les clauses GROUP BY et ORDER BY :


SELECT c_orders[0].o_orderdate,
       c_orders[0].o_orderstatus,
       count(*)
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderkey IS NOT NULL
GROUP BY c_orders[0].o_orderstatus,
         c_orders[0].o_orderdate
ORDER BY c_orders[0].o_orderdate;

Désimbriquer des requêtes

Pour désimbriquer des requêtes, Amazon Redshift utilise la syntaxe PartiQL pour effectuer une itération sur des baies SUPER. Pour ce faire, il navigue dans le tableau à l’aide de la clause FROM d’une requête. En utilisant l’exemple précédent, le suivant itère sur les valeurs de l’attribut pour c_orders.


SELECT c.*, o FROM customer_orders_lineitem c, c.c_orders o;

La syntaxe de désimbrication est une extension de la clause FROM. Dans SQL standard, la clause FROM x (AS) y signifie que y itère sur chaque tuple dans la relation x. Dans ce cas, x fait référence à une relation et y fait référence à un alias pour la relation x. De même, la syntaxe PartiQL de désimbrication à l’aide de l’élément de clause FROM x (AS) y signifie quey effectue une itération sur chaque valeur (SUPER) de l’expression x du tableau (SUPER). Dans ce cas, x est une expression SUPER et y est un alias pour x.

L’opérande de gauche peut également utiliser la notation par points et crochets pour la navigation régulière. Dans l’exemple précédent, customer_orders_lineitem c est l’itération sur la table de base customer_order_lineitem et c.c_orders o est l’itération sur le tableau c.c_orders. Pour itérer sur l’attribut o_lineitems, qui est un tableau dans un tableau, vous ajoutez plusieurs clauses.


SELECT c.*, o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

Amazon Redshift prend également en charge un index de tableau lors de l’itération sur le tableau à l’aide du mot clé AT. La clause x AS y AT z itère sur le tableau x et génère le champ z, qui est l’index du tableau. L’exemple suivant illustre le fonctionnement d’un index de tableau.


SELECT c_name,
       orders.o_orderkey AS orderkey,
       index AS orderkey_index
FROM customer_orders_lineitem c, c.c_orders AS orders AT index 
ORDER BY orderkey_index;

c_name             | orderkey | orderkey_index
-------------------+----------+----------------
Customer#000008251 | 3020007  |        0
Customer#000009452 | 4043971  |        0
  (2 rows)

L’exemple suivant itère sur un tableau scalaire


CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index;

 index | element
-------+----------
     0 | 1
     1 | 2.3
     2 | 45000000
(3 rows)

L’exemple suivant itère sur un tableau de plusieurs niveaux. L’exemple utilise plusieurs clauses de désimbrication (unnest) pour effectuer une itération dans les tableaux les plus intérieurs. Le tableau AS f.multi_level_array itère sur multi_level_array. L’élément AS du tableau est l’itération sur les tableaux dans multi_level_array.


CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array;

SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element;

   array   | element
-----------+---------
 [1.1,1.2] | 1.1
 [1.1,1.2] | 1.2
 [2.1,2.2] | 2.1
 [2.1,2.2] | 2.2
 [3.1,3.2] | 3.1
 [3.1,3.2] | 3.2
(6 rows)

Pour plus d’informations sur la clause FROM, consultez Clause FROM.

Dépivotement d’objet

Pour effectuer le dépivotement des objets, Amazon Redshift utilise la syntaxe PartiQL pour effectuer une itération sur des objets SUPER. Pour ce faire, il utilise la clause FROM d’une requête avec le mot clé UNPIVOT. Dans ce cas, l'expression est l'c.c_orders[0]objet. L'exemple de requête itère sur chaque attribut renvoyé par l'objet.


SELECT attr as attribute_name, json_typeof(val) as value_type 
FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr 
WHERE c_custkey = 9451;

 attribute_name  | value_type
-----------------+------------
 o_orderstatus   | string
 o_clerk         | string
 o_lineitems     | array
 o_orderdate     | string
 o_shippriority  | number
 o_totalprice    | number
 o_orderkey      | number
 o_comment       | string
 o_orderpriority | string
(9 rows)

Comme pour la désimbrication, la syntaxe de dépivotement est une extension de la clause FROM. La différence réside dans le fait que la syntaxe de dépivotement utilise le mot clé UNPIVOT pour indiquer qu’il effectue une itération sur un objet au lieu d’un tableau. Il utilise la value_alias AS pour l’itération sur toutes les valeurs à l’intérieur d’un objet et utilise l’attribute_alias AT pour effectuer une itération sur tous les attributs. Examinez le fragment de syntaxe suivant :


UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift prend en charge l'utilisation du dépivotement des objets et de la désimbrication des tableaux dans une seule clause FROM, comme suit :


SELECT attr as attribute_name, val as object_value
FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr 
WHERE c_custkey = 9451;

Lorsque vous utilisez le dépivotement d’objet, Amazon Redshift ne prend pas en charge le dépivotement corrélé. Concrètement, supposons que vous ayez un cas où il existe plusieurs exemples de dépivotement dans différents niveaux de requête et que le dépivotement interne fait référence à l’externe. Amazon Redshift ne prend pas en charge ce type de dépivotement multiple.

Pour plus d’informations sur la clause FROM, consultez Clause FROM. Pour bénéficier d’exemples montrant comment interroger des données structurées avec PIVOT et UNPIVOT, consultez Exemples PIVOT et UNPIVOT.

Typage dynamique

Le typage dynamique ne nécessite pas de moulage explicite des données qui sont extraites des chemins en notation points et crochets. Amazon Redshift utilise le typage dynamique pour traiter des données SUPER sans schéma sans avoir à déclarer les types de données avant de les utiliser dans votre requête. Le typage dynamique utilise les résultats de la navigation dans les colonnes de données SUPER sans devoir les convertir explicitement en types Amazon Redshift. Le typage dynamique est le plus utile dans les jointures et les clauses GROUP BY. L’exemple suivant utilise une instruction SELECT qui ne nécessite aucune conversion explicite des expressions points et crochets aux types habituels d’Amazon Redshift. Pour plus d’informations sur la compatibilité et la conversion des types, consultez Compatibilité et conversion de types.


SELECT c_orders[0].o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus = 'P';

Le signe d’égalité dans cette requête est évalué à true lorsque c_orders[0].o_orderstatus est la chaîne « P ». Dans tous les autres cas, le signe d’égalité est évalué à false, y compris lorsque les arguments de l’égalité sont de types différents.

Typage dynamique et statique

Sans utiliser le typage dynamique, vous ne pouvez pas déterminer si c_orders[0].o_orderstatus est une chaîne, un entier ou une structure. Vous pouvez seulement déterminer que c_orders[0].o_orderstatus est un type de données SUPER, qui peut être un scalaire Amazon Redshift, un tableau ou une structure. Le type statique de c_orders[0].o_orderstatus est un type de données SUPER. Conventionnellement, un type est implicitement statique dans SQL.

Amazon Redshift utilise le typage dynamique pour le traitement des données sans schéma. Lorsque la requête évalue les données, c_orders[0].o_orderstatus s’avère être un type spécifique. Par exemple, l’évaluation de c_orders[0].o_orderstatus sur le premier enregistrement de customer_orders_lineitem peut aboutir à un entier. L’évaluation sur le deuxième enregistrement peut résulter en une chaîne de caractères. Ce sont les types dynamiques de l’expression.

Lors de l’utilisation d’un opérateur ou d’une fonction SQL avec des expressions de type point et crochets ayant des types dynamiques, Amazon Redshift produit des résultats similaires à l’utilisation d’un opérateur ou d’une fonction SQL standard avec les types statiques respectifs. Dans cet exemple, lorsque le type dynamique de l’expression de chemin est une chaîne, la comparaison avec la chaîne « P » est significative. Si le type dynamique de c_orders[0].o_orderstatus est d’un autre type de données que celui de chaîne de caractères, l’égalité renvoie faux. Les autres fonctions renvoient null lorsque des arguments mal typés sont utilisés.

L’exemple suivant écrit la requête précédente avec un typage statique :


SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR = 'P'
           ELSE FALSE END;

Notez la distinction suivante entre les prédicats d’égalité et les prédicats de comparaison. Dans l'exemple précédent, si vous remplacez le prédicat d'égalité par un less-than-or-equal prédicat, la sémantique produit une valeur nulle au lieu de fausse.


SELECT c_orders[0]. o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus <= 'P';

Dans cet exemple, si c_orders[0].o_orderstatus est une chaîne, Amazon Redshift renvoie true si elle est alphabétiquement égale ou inférieure à « P ». Amazon Redshift renvoie false si elle est alphabétiquement supérieure à « P ». Toutefois, si c_orders[0].o_orderstatus n’est pas une chaîne, Amazon Redshift renvoie null car Amazon Redshift ne peut pas comparer des valeurs de différents types, comme indiqué dans la requête suivante :


SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR <= 'P'
           ELSE NULL END;

Le typage dynamique n’exclut pas des comparaisons des types qui sont minimalement comparables. Par exemple, vous pouvez convertir les types scalaires CHAR et VARCHAR Amazon Redshift en SUPER. Ils sont comparables à des chaînes de caractères, y compris en ignorant les caractères d’espacement de fin de chaîne, comme pour les types CHAR et VARCHAR d’Amazon Redshift. De même, les valeurs entières, décimales et à virgule flottante sont comparables en tant que valeurs SUPER. Spécifiquement pour les colonnes décimales, chaque valeur peut également avoir une graduation différente. Amazon Redshift les considère quand même comme des types dynamiques.

Amazon Redshift prend également en charge l’égalité sur les objets et les tableaux évalués comme étant profondément égaux, comme l’évaluation profonde des objets ou des tableaux et la comparaison de tous les attributs. Utilisez l’égalité profonde avec prudence, car le processus d’exécution de l’égalité profonde peut prendre du temps.

Utilisation du typage dynamique pour les jointures

Pour les jointures, le typage dynamique fait automatiquement correspondre des valeurs avec différents types dynamiques sans avoir à effectuer une longue analyse CASE WHEN pour savoir quels types de données peuvent apparaître. Supposons par exemple que votre organisation ait changé le format qu’elle utilisait pour les clés partielles (part keys) au fil du temps.

Les clés partielles de type entier initialement émises sont remplacées par des clés partielles de type chaîne de caractères, telles que ‘A55’, puis à nouveau par des clés partielles de type tableau, telles que [‘X’, 10] combinant une chaîne de caractères et un nombre. Amazon Redshift n’a pas besoin d’effectuer une longue analyse de cas sur les clés partielles et peut utiliser des jointures comme indiqué dans l’exemple suivant.


SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE l.l_partkey = ps.ps_partkey
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

L’exemple suivant montre à quel point la même requête peut être complexe et inefficace sans utiliser le typage dynamique :


SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey)
           THEN l.l_partkey::integer = ps.ps_partkey::integer
           WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey)
           THEN l.l_partkey::varchar = ps.ps_partkey::varchar
           WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey)
                AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0])
                AND IS_INTEGER(l.l_partkey[1]) AND IS_INTEGER(ps.ps_partkey[1])
           THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar
                AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer
           ELSE FALSE END
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

Sémantique laxiste

Par défaut, les opérations de navigation sur les valeurs SUPER renvoient null au lieu de renvoyer une erreur lorsque la navigation n’est pas valide. La navigation par objet est invalide si la valeur SUPER n’est pas un objet ou si la valeur SUPER est un objet mais ne contient pas le nom de l’attribut utilisé dans la requête. Par exemple, la requête suivante accède à un nom d’attribut non valide dans la colonne de données SUPER cdata :


SELECT c.c_orders.something FROM customer_orders_lineitem c;

La navigation de tableau renvoie null si la valeur SUPER n’est pas un tableau ou si l’index du tableau est hors limites. La requête suivante renvoie null car c_orders[1][1] est hors limites.


SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

La sémantique laxiste est particulièrement utile lorsqu’on utilise le typage dynamique pour convertir une valeur SUPER. Le transtypage d’une valeur SUPER en un type incorrect renvoie null au lieu d’une erreur si la conversion n’est pas valide. Par exemple, la requête suivante renvoie null car elle ne peut pas convertir la valeur de chaîne « Good » de l’attribut d’objet o_orderstatus en INTEGER. Amazon Redshift renvoie une erreur pour une conversion de VARCHAR en INTEGER mais pas pour une conversion en SUPER.


SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

Types d’introspection

Les colonnes de données SUPER prennent en charge les fonctions d’inspection qui renvoient le type dynamique et d’autres informations de type sur la valeur SUPER. L’exemple le plus courant est la fonction scalaire JSON_TYPEOF qui renvoie un VARCHAR avec les valeurs booléen (boolean), nombre (number), chaîne (string), objet (object), tableau (array) ou null, selon le type dynamique de la valeur SUPER. Amazon Redshift prend en charge les fonctions booléennes suivantes pour les colonnes de données SUPER :

DECIMAL_PRECISION
DECIMAL_SCALE
IS_ARRAY
IS_BIGINT
IS_CHAR
IS_DECIMAL
IS_FLOAT
IS_INTEGER
IS_OBJECT
IS_SCALAR
IS_SMALLINT
IS_VARCHAR
JSON_TYPEOF

Toutes ces fonctions renvoient false si la valeur d’entrée est nulle. IS_SCALAR, IS_OBJECT et IS_ARRAY s’excluent mutuellement et couvrent toutes les valeurs possibles à l’exception de null.

Pour déduire les types correspondant aux données, Amazon Redshift utilise la fonction JSON_TYPEOF qui renvoie le type de (premier niveau de) la valeur SUPER comme indiqué dans l’exemple suivant :


SELECT JSON_TYPEOF(r_nations) FROM region_nations;
 json_typeof
 -------------
 array
(1 row)


SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations;
 json_typeof
 -------------
 number

Amazon Redshift voit cela comme une longue chaîne unique, ce qui revient à insérer cette valeur dans une colonne VARCHAR au lieu d’un SUPER. Puisque la colonne est SUPER, la chaîne unique est toujours une valeur SUPER valide et la différence est notée dans JSON_TYPEOF :


SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations;
 is_varchar
-------------
 true
(1 row)


SELECT r_nations[4].n_name FROM region_nations
WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) 
           THEN r_nations[4].n_nationkey::INTEGER = 15 
           ELSE false END;

Classer par

Amazon Redshift ne définit pas de comparaisons SUPER entre des valeurs ayant des types dynamiques différents. Une valeur SUPER qui est une chaîne n’est ni plus petite ni plus grande qu’une valeur SUPER qui est un nombre. Pour utiliser les clauses ORDER BY avec les colonnes SUPER, Amazon Redshift définit un ordre total parmi les différents types à observer lorsque Amazon Redshift classe les valeurs SUPER à l’aide des clauses ORDER BY. L’ordre des types dynamiques est le suivant : booléen, nombre, chaîne, tableau, objet. L’exemple suivant montre les différents types d’ordres :


INSERT INTO region_nations VALUES 
(100,'name1','comment1','AWS'), 
(200,'name2','comment2',1),
(300,'name3','comment3',ARRAY(1, 'abc', null)),
(400,'name4','comment4',-2.5),
(500,'name5','comment5','Amazon');

SELECT r_nations FROM region_nations order by r_nations;

r_nations
----------------
 -2.5
 1
 "Amazon"
 "AWS"
 [1,"abc",null]
(5 rows)

Pour plus d’informations sur la clause ORDER BY, consultez Clause ORDER BY.

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.