Amazon Redshift ne prendra plus en charge la création de nouveaux Python à UDFs partir du patch 198. UDFs Le Python existant continuera de fonctionner jusqu'au 30 juin 2026. Pour plus d’informations, consultez le [ billet de blog ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
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.
# Exemples d’utilisation de données semi-structurées dans Amazon Redshift
Les exemples suivants montrent comment travailler avec des données semi-structurées dans Amazon Redshift à l’aide de la syntaxe PartiQL. Vous allez créer un exemple de table pour charger un ensemble d’exemples de données semi-structurées, puis interroger des objets de données semi-structurées dans divers cas d’utilisation.
**Note**
Nous vous recommandons de définir les options de configuration `enable_case_sensitive_identifier` et `enable_case_sensitive_super_attribute` avant de travailler avec le type de données SUPER. Pour plus d’informations, consultez [enable\$1case\$1sensitive\$1identifier](r_enable_case_sensitive_identifier.md) et [enable\$1case\$1sensitive\$1super\$1attribute](r_enable_case_sensitive_super_attribute.md).
## Chargement de données semi-structurées
Les instructions suivantes créent un exemple de table et chargent un exemple d’objet JSON dans la colonne SUPER `all_data`.
```
DROP TABLE IF EXISTS test_json;
SET enable_case_sensitive_super_attribute TO true;
SET enable_case_sensitive_identifier TO true;
CREATE TABLE test_json (all_data SUPER);
INSERT INTO test_json VALUES (JSON_PARSE('
{
"data":{
"pnr":{
"type":"pnr",
"pnrid":"123PQRS-2024-09-20",
"bookingIdentifier":"123PQRS",
"version":"5",
"triggerType":"",
"events":[
{
"eventType":"UPDATED",
"type":"PART",
"id":"123PQRS-2024-09-20-HO-1"
},
{
"eventType":"CREATED",
"type":"ABC",
"id":"123PQRS-2024-09-20-OT-38"
}
],
"create":{
"pnrCreateDate":"2024-09-20T16:56:00Z",
"officeID":"OFFCID1234",
"officeIDCategory":"Email"
},
"lastModification":{
"dateTime":"2024-09-20T17:09:00Z"
},
"PARTDetails":[
{
"path":"1",
"TrainPARTs":[
{
"PARTID":"123PQRS-2024-09-20-HO-1",
"departure":{
"departureStation":"XYZ",
"departureTimeLocal":"2024-10-03T06:30:00",
"departureTimeGMT":"2024-10-03T10:30:00Z"
},
"arrival":{
"arrivalStation":"ABC",
"arrivalTimeLocal":"2024-10-03T08:20:00",
"arrivalTimeGMT":"2024-10-03T15:20:00Z"
},
"marketing":{
"carrierCode":"XX",
"TrainNumber":"100"
},
"operating":{
"carrierCode":"YY",
"TrainNumber":"100-A"
},
"status":"ON",
"aircraft":{
"code":"222"
},
"class":"WC",
"first":"Y",
"seating":[
]
}
]
}
],
"commuterInformation":[
{
"commuterID":"2",
"commuterPNR":"123PQRS-2024-09-20-RO-2",
"commuterTypeCode":"DOM",
"firstName":"JOHN",
"lastName":"MILLER"
}
],
"contactDetail":[
{
"emailContacts":[
{
"id":"123PQRS-2024-09-20-OT-4",
"contact":"JOHNMILLER@EXAMPLE.COM",
"purpose":[
"BUSINESS"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
],
"language":"EN"
},
{
"id":"123PQRS-2024-09-20-OT-5",
"contact":"HARVEYCORMIER@EXAMPLE.COM",
"purpose":[
"NOTIFICATION"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
],
"language":"EN"
}
]
},
{
"phoneContacts":[
{
"id":"123PQRS-2024-09-20-OT-3",
"contact":"1234567890",
"purpose":[
"NOTIFICATION"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
],
"language":""
}
]
},
{
"addressInfo":[
{
"id":"123PQRS-2024-09-20-OT-6",
"addressline":[
"112 PORT STREET"
],
"provinceState":"CA",
"cityName":"SAN JOSE",
"postalCode":"12345",
"countryCode":"USA",
"purpose":[
"MAILING"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
}
]
}
],
"PendingService":[
{
"id":"123PQRS-2024-09-20-OT-26",
"code":"MONO",
"status":"",
"text":"Broken Seat at Coach-No XYZ123 Seat-No 567",
"trainCode":"WC-1",
"TrainsArray":[
"123PQRS-2024-09-20-HO-1"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
},
{
"id":"123PQRS-2024-09-20-OT-27",
"code":"OTHS",
"status":"",
"text":"Broken Seat at Coach-No XYZ567 Seat-No 111",
"trainCode":"WC-1",
"TrainsArray":[
"123PQRS-2024-09-20-HO-1"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
},
{
"id":"123PQRS-2024-09-20-OT-28",
"code":"OTHS",
"status":"",
"text":"Broken Seat at Coach-No XYZ890 Seat-No 123",
"trainCode":"WC-1",
"TrainsArray":[
"123PQRS-2024-09-20-HO-1"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
},
{
"id":"123PQRS-2024-09-20-OT-29",
"code":"OTHS",
"status":"",
"text":"Broken Seat at Coach-No XYZ111 Seat-No 333",
"trainCode":"WC-1",
"TrainsArray":[
"123PQRS-2024-09-20-HO-1"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
}
],
"parts": [
{
"partname": "prop",
"manufacturer": "local parts co",
"quality": 2,
"price": 10.00
},
{
"partname": "prop",
"manufacturer": "big parts co",
"quality": null,
"price": 9.00
},
{
"partname": "prop",
"manufacturer": "small parts co",
"quality": 1,
"price": 12.00
},
{
"partname": "rudder",
"manufacturer": "local parts co",
"quality": 1,
"price": 2.50
},
{
"partname": "rudder",
"manufacturer": "big parts co",
"quality": 2,
"price": 3.75
},
{
"partname": "rudder",
"manufacturer": "small parts co",
"quality": null,
"price": 1.90
},
{
"partname": "wing",
"manufacturer": "local parts co",
"quality": null,
"price": 7.50
},
{
"partname": "wing",
"manufacturer": "big parts co",
"quality": 1,
"price": 15.20
},
{
"partname": "wing",
"manufacturer": "small parts co",
"quality": null,
"price": 11.80
}
],
"count_by_color": [
{
"quality": "high",
"red": 15,
"green": 20,
"blue": 7
},
{
"quality": "normal",
"red": 35,
"green": null,
"blue": 40
},
{
"quality": "low",
"red": 10,
"green": 23,
"blue": null
}
]
}
},
"id":"abcdefgh-ijklmnop-qrstuvwxyz123",
"mainIds":[
{
"ID":"pqrstuvwxyz-aabbcc123",
"Source":"NYC"
}
]
}
'));
```
## Interrogation des données semi-structurées imbriquées
L’instruction suivante utilise la notation par points de PartiQL pour extraire le champ `pnrid`, qui est imbriqué à trois niveaux suivants dans l’objet de niveau supérieur `all_data`.
```
select all_data.data.pnr.pnrid::varchar from test_json;
pnrid
--------------------
123PQRS-2024-09-20
```
L’instruction suivante utilise la notation entre crochets de PartiQL pour spécifier et extraire uniquement le premier élément du tableau `events` imbriqué dans l’objet de niveau supérieur.
```
SELECT
all_data.data.pnr.events[0]
FROM test_json;
events
---------------------------------
{
"eventType":"UPDATED",
"type":"PART",
"id":"123PQRS-2024-09-20-HO-1"
}
```
L’instruction suivante extrait uniquement la propriété `eventType` de l’élément spécifié du tableau `events`.
```
SELECT
all_data.data.pnr.events[0].eventType
FROM test_json;
eventtype
-----------
"UPDATED"
```
Les instructions suivantes :
## Utilisation de `enable_case_sensitive_identifier` et `enable_case_sensitive_super_attribute` avec des données semi-structurées
Les exemples suivants montrent en quoi les options de configuration [enable\$1case\$1sensitive\$1identifier](r_enable_case_sensitive_identifier.md) et [enable\$1case\$1sensitive\$1super\$1attribute](r_enable_case_sensitive_super_attribute.md) diffèrent lorsqu’elles sont utilisées pour interroger des données semi-structurées. Pour plus d’informations sur ces options de configuration, consultez [Accès aux champs JSON avec des noms de champs ou des attributs en majuscules et à casse mixte](super-configurations.md#upper-mixed-case).
Dans l’instruction suivante, la réinitialisation des deux options de configuration à leur valeur par défaut false entraîne le renvoi de la valeur NULL par la requête.
```
RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;
SELECT
all_data.data.pnr.events[0].eventType
FROM test_json;
eventtype
-----------
NULL
```
Dans l’exemple suivant, l’exemple de requête renvoie le résultat souhaité une fois que vous avez placé les attributs sensibles à la casse entre guillemets doubles et que vous avez défini `enable_case_sensitive_identifier` sur true.
```
RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;
SELECT
all_data.data.pnr.events[0]."eventType"
FROM test_json;
eventtype
-----------
NULL
SET enable_case_sensitive_identifier TO true;
SELECT
all_data.data.pnr.events[0]."eventType"
FROM test_json;
eventtype
-----------
"UPDATED"
```
Dans l’exemple suivant, l’exemple de requête renvoie le résultat souhaité une fois que vous avez défini `enable_case_sensitive_super_attribute` sur true sans placer les attributs sensibles à la casse entre guillemets doubles.
```
RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;
SELECT
all_data.data.pnr.events[0].eventType
FROM test_json;
eventtype
-----------
NULL
SET enable_case_sensitive_super_attribute TO true;
SELECT
all_data.data.pnr.events[0].eventType
FROM test_json;
eventtype
-----------
"UPDATED"
```
## Filtrage des données semi-structurées
L’instruction suivante utilise la syntaxe PartiQL dans la clause WHERE d’une instruction qui compte les événements du type `UPDATED` pour récupérer les données d’un certain attribut à l’intérieur d’un tableau. Vous pouvez utiliser cette syntaxe dans n’importe quelle partie de la requête où vous feriez normalement référence à des colonnes.
```
SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0].eventType = 'UPDATED';
count
------
1
```
L’exemple suivant utilise la syntaxe par points et crochets de PartiQL dans les clauses GROUP BY et ORDER BY.
```
SELECT all_data.data.pnr.events[0].eventType::varchar,
COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0].eventType IS NOT NULL
GROUP BY all_data.data.pnr.events[0].eventType
ORDER BY all_data.data.pnr.events[0].eventType;
eventtype | count
-----------+-------
"UPDATED" | 1
```
## Désimbrication des données semi-structurées
L’instruction suivante utilise des jointures partiQL pour désimbriquer le tableau `events`. Notez que cette jointure fonctionne même lorsque le nombre d’index du tableau n’est pas statique.
Pour des exemples de désimbrication de données semi-structurées à l’aide de UNNEST dans la clause FROM, consultez [Exemples de UNNEST](r_FROM_clause-unnest-examples.md).
```
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
b.eventType::varchar event_type,
b.id::varchar event_id
FROM test_json a,
a.all_data.data.pnr.events b;
type_info | pnr_id | booking_id | version_info | event_type | event_id
-----------+---------------------+------------+--------------+------------+-------------------------
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | UPDATED | 123PQRS-2024-09-20-HO-1
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | CREATED | 123PQRS-2024-09-20-OT-38
```
## Désimbrication de tableaux imbriqués
L’instruction suivante utilise des jointures PartiQL pour désimbriquer un tableau imbriqué dans un autre tableau.
Pour des exemples de désimbrication de données semi-structurées à l’aide de UNNEST dans la clause FROM, consultez [Exemples de UNNEST](r_FROM_clause-unnest-examples.md).
```
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
d.id::varchar email_record_id,
d.contact::varchar email_contact,
e::varchar email_purpose,
f::varchar email_commuter
FROM test_json a,
a.all_data.data.pnr.contactDetail c,
c."emailContacts" d,
d.purpose e,
d.commuter f;
type_info | pnr_id | booking_id | version_info | email_record_id | email_contact | email_purpose | email_commuter
-----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+-------------------------
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM | BUSINESS | 123PQRS-2024-09-20-RO-2
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-5 | HARVEYCORMIER@EXAMPLE.COM | NOTIFICATION | 123PQRS-2024-09-20-RO-2
```
## Utilisation de données semi-structurées dans des sous-requêtes
L’instruction suivante utilise une sous-requête dans la clause WHERE pour renvoyer uniquement une sous-section des résultats de l’exemple précédent.
```
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
d.id::varchar email_record_id,
d.contact::varchar email_contact
FROM test_json a,
a.all_data.data.pnr.contactDetail c,
c."emailContacts" d
WHERE (SELECT COUNT(*) FROM d.purpose e WHERE e = 'BUSINESS') > 0;
type_info | pnr_id | booking_id | version_info | email_record_id | email_contact | email_purpose | email_commuter
-----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+-------------------------
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM | BUSINESS | 123PQRS-2024-09-20-RO-2
```
## Agrégation de requêtes utilisant des données semi-structurées
L’instruction suivante utilise la fonction COUNT pour agréger le nombre d’éléments du tableau `PendingService`.
```
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
COUNT(*) AS total_pending_service
FROM test_json a,
a.all_data.data.pnr.PendingService c
GROUP BY 1,2,3,4;
type_info | pnr_id | booking_id | version_info | total_pending_service
-----------+--------------------+------------+--------------+-----------------------
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 4
```
## Utilisation de données semi-structurées dans des vues matérialisées
L’instruction suivante utilise l’instruction de l’exemple précédent pour créer une vue matérialisée. La vue matérialisée actualise automatiquement le nombre de services en attente lorsque la table de base reçoit de nouvelles données.
```
CREATE MATERIALIZED VIEW mv_total_pending_service
AUTO REFRESH YES
AS
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
COUNT(*) AS total_pending_service
FROM test_json a,
a.all_data.data.pnr.PendingService c
GROUP BY 1,2,3,4;
```
## Utilisation de PIVOT et UNPIVOT avec des données semi-structurées
L’instruction suivante utilise PIVOT dans la colonne `partname` pour renvoyer le prix moyen de chaque pièce.
```
SELECT *
FROM
(
SELECT
c.partname::varchar, c.price
FROM test_json a,
a.all_data.data.pnr.parts c)
PIVOT (AVG(price) for partname IN ('prop', 'rudder', 'wing'));
prop | rudder | wing
------------+--------------------+--------
10.33 | 2.71 | 11.50
```
Dans l’exemple précédent, les résultats sont transformés en colonnes. L’exemple suivant montre une requête GROUP BY qui renvoie les prix moyens sous forme de lignes plutôt que de colonnes.
```
SELECT partname, avg(price)
FROM (
SELECT
c.partname::varchar, c.price
FROM test_json a,
a.all_data.data.pnr.parts c)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;
partname | avg
----------+-------
prop | 10.33
rudder | 2.71
wing | 11.50
```
Vous trouverez ci-dessous un exemple de PIVOT avec `manufacturer` en tant que colonne implicite.
```
SELECT *
FROM (
SELECT
c.quality, c.manufacturer::varchar
FROM test_json a,
a.all_data.data.pnr.parts c) PIVOT (
count(*) FOR quality IN (1, 2, NULL)
);
manufacturer | 1 | 2 | null
-------------------+----+----+------
local parts co | 1 | 1 | 1
big parts co | 1 | 1 | 1
small parts co | 1 | 0 | 2
```
Voici un exemple d’UNPIVOT sur la colonne `quality`.
```
SELECT *
FROM
(
SELECT
c.quality as quality
FROM test_json a,
a.all_data.data.pnr.parts c)
UNPIVOT (cnt FOR column_header IN (quality));
column_header | cnt
-----------------+----
quality | 2
quality | 1
quality | 1
quality | 2
quality | 1
```