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.
Interrogation de données semi-structurées
Avec Amazon Redshift, vous pouvez interroger et analyser des données semi-structurées, telles qu'Avro ou IonJSON, 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 SQL compatible 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. Cela permet également aux éléments de FROM clause d'itérer sur des tableaux et de les utiliser pour des opérations de désimbrication. Vous trouverez ci-dessous des descriptions des différents modèles de requête qui combinent l'utilisation du type de SUPER données avec la navigation par chemin et par tableau, la dénidification, le dépivotement et les jointures.
Pour en savoir plus sur les tables utilisées dans l’exemple suivant, consultez SUPERexemple de jeu de données.
Rubriques
Navigation
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 c_orders SUPER données est un tableau doté d'une structure et d'un attribut nomméo_orderkey.
Pour intégrer des données dans la table customer_orders_lineitem, exécutez la commande suivante. Remplacez le IAM rôle 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 SELECT instruction 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 navigation entre crochets et points dans les clauses GROUP ORDER BY et 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 les requêtes, Amazon Redshift utilise la syntaxe partiQL pour itérer sur des tableaux. SUPER Pour ce faire, il parcourt le tableau à l'aide de la FROM clause 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 FROM clause. En standardSQL, la FROM clause x (AS) y signifie qu'yelle itère sur chaque tuple en 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 qui consiste à annuler l'imbrication à l'aide de l'élément de FROM clause x (AS) y signifie qu'yelle itère sur chaque valeur (SUPER) dans l'expression de tableau (SUPER) x. Dans ce cas, x est une SUPER expression et y un alias pourx.
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 FROM clause, consultezClause FROM.
Dépivotement d’objet
Pour effectuer le dépivotement des objets, Amazon Redshift utilise la syntaxe partiQL pour itérer sur les objets. SUPER Pour ce faire, il utilise la FROM clause d'une requête avec le UNPIVOT mot clé. 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 dans le cas de la dénidification, la syntaxe non pivotante est également une extension de la clause. FROM La différence réside dans le fait que la syntaxe non pivotante utilise le UNPIVOT mot clé pour indiquer qu'il s'agit d'une itération sur un objet plutôt que sur 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, comme suit : FROM
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 FROM clause, consultezClause FROM. Pour des exemples illustrant comment interroger des données structurées, avec PIVOT etUNPIVOT, consultezExemples 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 la saisie dynamique pour traiter les SUPER données sans schéma sans qu'il soit nécessaire de déclarer les types de données avant de les utiliser dans votre requête. La saisie dynamique utilise les résultats de la navigation dans les colonnes de SUPER données sans avoir à les convertir explicitement en types Amazon Redshift. La saisie dynamique est particulièrement utile dans les jointures et GROUP les clauses BY. L'exemple suivant utilise une SELECT instruction qui ne nécessite aucune conversion explicite des expressions entre points et crochets selon les types Amazon Redshift habituels. 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 uniquement déterminer que c_orders [0] .o_orderstatus est un type de SUPER données, 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 Classiquement, un type est implicitement un type 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.
Lorsque vous utilisez un SQL opérateur ou une fonction avec des expressions à points et crochets de type dynamique, Amazon Redshift produit des résultats similaires à l'utilisation d'un SQL opérateur ou d'une fonction 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 deux types scalaires CHAR et les types scalaires VARCHAR Amazon Redshift en. SUPER Elles sont comparables à des chaînes, notamment en ignorant les espaces blancs de fin similaires à Amazon CHAR VARCHAR Redshift et aux types. De même, les nombres entiers, les nombres décimaux et les valeurs à 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, la saisie dynamique fait automatiquement correspondre les valeurs aux différents types dynamiques sans effectuer une longue CASE WHEN analyse pour déterminer 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 SUPER les valeurs renvoient la valeur null au lieu de renvoyer une erreur lorsque la navigation n'est pas valide. La navigation par objet n'est pas valide si la SUPER valeur n'est pas un objet ou si la SUPER valeur 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 SUPER données cdata :
SELECT c.c_orders.something FROM customer_orders_lineitem c;
La navigation dans le tableau renvoie la SUPER valeur nulle si la valeur 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 lors de l'utilisation de la saisie dynamique pour convertir une valeur. SUPER La conversion d'une SUPER valeur dans le mauvais type renvoie une valeur nulle au lieu d'une erreur si la conversion n'est pas valide. Par exemple, la requête suivante renvoie la valeur 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 VARCHAR INTEGER diffusion, mais pas pour une SUPER diffusion.
SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;
Types d’introspection
SUPERles colonnes de données prennent en charge les fonctions d'inspection qui renvoient le type dynamique et d'autres informations de type concernant la SUPER valeur. L'exemple le plus courant est la fonction JSON _ TYPEOF scalar qui renvoie un VARCHAR avec des valeurs booléennes, numériques, chaînes, objets, tableaux ou nulles, 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
ES_ ARRAY
ES_ BIGINT
ES_ CHAR
ES_ DECIMAL
ES_ FLOAT
ES_ INTEGER
ES_ OBJECT
ES_ SCALAR
ES_ SMALLINT
ES_ VARCHAR
JSON_TYPEOF
Toutes ces fonctions renvoient false si la valeur d’entrée est nulle. IS_SCALAR, IS_ et IS_ OBJECT s'ARRAYexcluent mutuellement et couvrent toutes les valeurs possibles à l'exception de null.
Pour déduire les types correspondant aux données, Amazon Redshift utilise JSON la fonction TYPEOF _ qui renvoie le type (le niveau supérieur de) la valeur, comme indiqué dans SUPER 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 considère qu'il s'agit d'une chaîne longue unique, comme si vous insériez cette valeur dans une VARCHAR colonne au lieu d'une. SUPER Puisque la colonne estSUPER, la chaîne unique est toujours une SUPER valeur 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 SUPER comparaisons entre des valeurs de types dynamiques différents. Une SUPER valeur qui est une chaîne n'est ni plus petite ni plus grande qu'une SUPER valeur qui est un nombre. Pour utiliser des clauses ORDER BY avec des SUPER colonnes, Amazon Redshift définit un ordre total entre différents types à respecter lorsqu'Amazon Redshift SUPER classe des valeurs ORDER à l'aide de clauses 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, consultezClause ORDER BY.
