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. # Didacticiel : Utiliser les fonctions SQL spatiales avec Amazon Redshift Ce didacticiel explique comment utiliser certaines fonctions SQL spatiales avec Amazon Redshift Pour ce faire, vous interrogez deux tables à l’aide de fonctions SQL spatiales. Le didacticiel utilise des données provenant de jeux de données publics qui mettent en corrélation les données de localisation des logements locatifs avec des codes postaux à Berlin, en Allemagne. **Topics** + [Conditions préalables](#spatial-tutorial-prerequisites) + [Étape 1 : Créer des tables et charger des données de test](#spatial-tutorial-test-data) + [Étape 2 : Interroger les données spatiales](#spatial-tutorial-query) + [Étape 3 : Nettoyer vos ressources](#spatial-tutorial-clean-up) ## Conditions préalables Pour ce didacticiel, vous avez besoin des ressources suivantes : + Un cluster et une base de données Amazon Redshift existants auxquels vous pouvez accéder et que vous pouvez mettre à jour. Dans le cluster existant, vous créez des tables, chargez des exemples de données et exécutez des requêtes SQL pour démontrer les fonctions spatiales. Votre cluster doit disposer d’au moins deux nœuds. Pour savoir comment créer un cluster, suivez les étapes du [Guide de démarrage d’Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/gsg/). + Pour utiliser l’éditeur de requêtes Amazon Redshift, assurez-vous que votre cluster se trouve dans une région AWS qui prend en charge l’éditeur de requêtes. Pour plus d’informations, consultez [Interrogation d’une base de données à l’aide de l’éditeur de requêtes](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html) dans le *Guide de gestion Amazon Redshift*. + AWS informations d'identification pour votre cluster Amazon Redshift qui lui permettent de charger des données de test depuis Amazon S3. Pour plus d'informations sur la manière d'accéder à d'autres AWS services tels qu'Amazon S3, consultez [Autoriser Amazon Redshift à accéder aux AWS](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html) services. + Rôle Gestion des identités et des accès AWS (IAM) nommé`mySpatialDemoRole`, auquel est `AmazonS3ReadOnlyAccess` attachée la politique gérée pour lire les données Amazon S3. Pour créer un rôle avec l’autorisation de charger les données à partir d’un compartiment Amazon S3, consultez [Autorisation d’opérations COPY, UNLOAD, CREATE EXTERNAL FUNCTION et CREATE EXTERNAL SCHEMA à l’aide de rôles IAM](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html) dans le *Guide de gestion Amazon Redshift*. + Une fois que vous avez créé le rôle IAM `mySpatialDemoRole`, celui-ci nécessite une association avec votre cluster Amazon Redshift. Pour plus d’informations sur la création d’une association, consultez [Autorisation d’opérations COPY, UNLOAD, CREATE EXTERNAL FUNCTION et CREATE EXTERNAL SCHEMA à l’aide de rôles IAM](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html) dans le *Guide de gestion Amazon Redshift*. ## Étape 1 : Créer des tables et charger des données de test Les données source utilisées par ce didacticiel se trouvent dans des fichiers nommés `accommodations.csv` et `zipcodes.csv`. Le fichier `accommodations.csv` comprend des données open source provenant de insideairbnb.com. Le fichier `zipcodes.csv` fournit des codes postaux qui sont des données open source de l’institut national de statistique de Berlin-Brandebourg en Allemagne (Amt für Statistik Berlin-Brandebourg). Les deux sources de données sont fournies sous licence Creative Commons. Les données sont limitées à la région de Berlin, en Allemagne. Ces fichiers se trouvent dans un compartiment public Amazon S3 à utiliser avec ce didacticiel. Vous pouvez éventuellement télécharger les données source à partir des liens Amazon S3 suivants : + [ Données source pour la table `accommodations`](https://s3.amazonaws.com/redshift-downloads/spatial-data/accommodations.csv). + [ Données source pour la table `zipcode`](https://s3.amazonaws.com/redshift-downloads/spatial-data/zipcode.csv). Utilisez la procédure suivante pour créer des tables et charger les données de test. **Pour créer des tables et charger les données de test** 1. Ouvrez l’éditeur de requêtes Amazon Redshift. Pour plus d’informations sur l’utilisation de l’éditeur de requêtes, consultez [Interrogation d’une base de données à l’aide de l’éditeur de requêtes](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html) dans le *Guide de la gestion du cluster Amazon Redshift*. 1. Supprimez toutes les tables utilisées par ce didacticiel si elles existent déjà dans votre base de données. Pour plus d'informations, consultez [Étape 3 : Nettoyer vos ressources](#spatial-tutorial-clean-up). 1. Créez la table `accommodations` pour stocker l’emplacement géographique de chaque hébergement (longitude et latitude), le nom de la liste et d’autres données métier. Ce didacticiel parcourt les locations de chambres à Berlin, en Allemagne. La colonne `shape` stocke les points géographiques de l’emplacement des logements. Les autres colonnes contiennent des informations sur la location. Pour créer la table `accommodations`, exécutez l’instruction SQL suivante dans l’éditeur de requêtes Amazon Redshift. ``` CREATE TABLE public.accommodations ( id INTEGER PRIMARY KEY, shape GEOMETRY, name VARCHAR(100), host_name VARCHAR(100), neighbourhood_group VARCHAR(100), neighbourhood VARCHAR(100), room_type VARCHAR(100), price SMALLINT, minimum_nights SMALLINT, number_of_reviews SMALLINT, last_review DATE, reviews_per_month NUMERIC(8,2), calculated_host_listings_count SMALLINT, availability_365 SMALLINT ); ``` 1. Créez la table `zipcode` dans l’éditeur de requêtes pour stocker les codes postaux de Berlin. Un *code postal* est défini en tant que polygone dans la colonne `wkb_geometry`. Les autres colonnes décrivent les métadonnées spatiales supplémentaires sur le code postal. Pour créer la table `zipcode`, exécutez l’instruction SQL suivante dans l’éditeur de requêtes Amazon Redshift. ``` CREATE TABLE public.zipcode ( ogc_field INTEGER PRIMARY KEY NOT NULL, wkb_geometry GEOMETRY, gml_id VARCHAR(256), spatial_name VARCHAR(256), spatial_alias VARCHAR(256), spatial_type VARCHAR(256) ); ``` 1. Chargez les tables à l’aide des exemples de données. Les exemples de données de ce didacticiel sont fournis dans un compartiment Amazon S3 qui permet un accès en lecture à tous les AWS utilisateurs authentifiés. Assurez-vous que vous fournissez les informations d’identification AWS valides qui permettent d’accéder à Amazon S3. Pour charger les données de test dans vos tables, exécutez les commandes COPY suivantes. Remplacez *`account-number`* par votre propre numéro de compte AWS . Le segment de la chaîne d’informations d’identification entre guillemets simples ne peut pas comporter d’espaces ou de sauts de ligne. ``` COPY public.accommodations FROM 's3://redshift-downloads/spatial-data/accommodations.csv' DELIMITER ';' IGNOREHEADER 1 REGION 'us-east-1' CREDENTIALS 'aws_iam_role=arn:aws:iam::account-number:role/mySpatialDemoRole'; ``` ``` COPY public.zipcode FROM 's3://redshift-downloads/spatial-data/zipcode.csv' DELIMITER ';' IGNOREHEADER 1 REGION 'us-east-1' CREDENTIALS 'aws_iam_role=arn:aws:iam::account-number:role/mySpatialDemoRole'; ``` 1. Vérifiez que chaque table s’est chargée correctement en exécutant les commandes suivantes. ``` select count(*) from accommodations; ``` ``` select count(*) from zipcode; ``` Les résultats suivants indiquent le nombre de lignes dans chaque tableau de données de test. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/redshift/latest/dg/spatial-tutorial.html) ## Étape 2 : Interroger les données spatiales Une fois vos tables créées et chargées, vous pouvez les interroger à l’aide des instructions SQL SELECT. Les requêtes suivantes illustrent certaines des informations que vous pouvez récupérer. Vous pouvez écrire de nombreuses autres requêtes qui utilisent des fonctions spatiales pour répondre à vos besoins. **Pour interroger les données spatiales** 1. Interrogez les données pour obtenir le nombre total d’éléments stockés dans la table `accommodations`, comme illustré ci-dessous. Le système de référence spatiale est le système géodésique mondial (WGS) 84, qui possède l’identifiant de référence spatiale unique 4326. ``` SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326; ``` ``` count ------- 22248 ``` 1. Extrayez les objets géométriques au format WKT (Well-known text) avec quelques attributs en plus. En outre, vous pouvez vérifier si ces données de code postal sont également stockées dans le système géodésique mondial (WGS) 84, qui utilise l’ID de référence spatiale (SRID) 4326. Les données spatiales doivent être stockées dans le même système de référence spatiale pour être interopérables. ``` SELECT ogc_field, spatial_name, spatial_type, ST_SRID(wkb_geometry), ST_AsText(wkb_geometry) FROM public.zipcode ORDER BY spatial_name; ``` ``` ogc_field spatial_name spatial_type st_srid st_astext --------------------------------------------------------------- 0 10115 Polygon 4326 POLYGON((...)) 4 10117 Polygon 4326 POLYGON((...)) 8 10119 Polygon 4326 POLYGON((...)) ... (190 rows returned) ``` 1. Sélectionnez le polygone de Berlin Mitte (10117), un arrondissement de Berlin, au format GeoJSON, sa dimension et le nombre de points dans ce polygone. ``` SELECT ogc_field, spatial_name, ST_AsGeoJSON(wkb_geometry), ST_Dimension(wkb_geometry), ST_NPoints(wkb_geometry) FROM public.zipcode WHERE spatial_name='10117'; ``` ``` ogc_field spatial_name spatial_type st_dimension st_npoint ----------------------------------------------------------------------------------------------- 4 10117 {"type":"Polygon", "coordinates":[[[...]]]} 2 331 ``` 1. Exécutez la commande SQL suivante pour voir combien d’hébergements se trouvent à moins de 500 mètres de la porte de Brandebourg. ``` SELECT count(*) FROM public.accommodations WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500; ``` ``` count ------ 29 ``` 1. Obtenez l’emplacement approximatif de la porte de Brandebourg à partir des données stockées dans les hébergements répertoriés comme étant à proximité en exécutant la requête suivante. Cette requête nécessite une sous-sélection. Le nombre obtenu n’est pas le même parce que l’emplacement interrogé diffère de la requête précédente, car il est plus proche des hébergements. ``` WITH poi(loc) as ( SELECT st_astext(shape) FROM accommodations WHERE name LIKE '%brandenburg gate%' ) SELECT count(*) FROM accommodations a, poi p WHERE ST_DistanceSphere(a.shape, ST_GeomFromText(p.loc, 4326)) < 500; ``` ``` count ------ 60 ``` 1. Exécutez la requête suivante pour afficher les détails de tous les hébergements autour de la porte de Brandebourg, classés par prix dans l’ordre décroissant. ``` SELECT name, price, ST_AsText(shape) FROM public.accommodations WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500 ORDER BY price DESC; ``` ``` name price st_astext ------------------------------------------------------------------------------------------------------- DUPLEX APARTMENT/PENTHOUSE in 5* LOCATION! 7583 300 POINT(13.3826510209548 52.5159819722552) DUPLEX-PENTHOUSE IN FIRST LOCATION! 7582 300 POINT(13.3799997083855 52.5135918444834) ... (29 rows returned) ``` 1. Exécutez la requête suivante pour récupérer l’hébergement le plus cher avec son code postal. ``` SELECT a.price, a.name, ST_AsText(a.shape), z.spatial_name, ST_AsText(z.wkb_geometry) FROM accommodations a, zipcode z WHERE price = 9000 AND ST_Within(a.shape, z.wkb_geometry); ``` ``` price name st_astext spatial_name st_astext ------------------------------------------------------------------------------------------------------------------------------------------------- 9000 Ueber den Dächern Berlins Zentrum POINT(13.334436985013 52.4979779501538) 10777 POLYGON((13.3318284987227 52.4956021172799,... ``` 1. Calculez le prix maximum, minimum ou médian des hébergements à l’aide d’une sous-requête. La requête suivante répertorie le prix médian des logements par code postal. ``` SELECT a.price, a.name, ST_AsText(a.shape), z.spatial_name, ST_AsText(z.wkb_geometry) FROM accommodations a, zipcode z WHERE ST_Within(a.shape, z.wkb_geometry) AND price = (SELECT median(price) FROM accommodations) ORDER BY a.price; ``` ``` price name st_astext spatial_name st_astext --------------------------------------------------------------------------------------------------------------------------------------------- 45 "Cozy room Berlin-Mitte" POINT(13.3864349535358 52.5292016386514) 10115 POLYGON((13.3658598465795 52.535659581048,... ... (723 rows returned) ``` 1. Exécutez la requête suivante pour récupérer le nombre d’hébergements répertoriés à Berlin. Les points chauds sont regroupés par code postal et triés par quantité d’informations fournies. ``` SELECT z.spatial_name as zip, count(*) as numAccommodations FROM public.accommodations a, public.zipcode z WHERE ST_Within(a.shape, z.wkb_geometry) GROUP BY zip ORDER BY numAccommodations DESC; ``` ``` zip numaccommodations ---------------------------- 10245 872 10247 832 10437 733 10115 664 ... (187 rows returned) ``` ## Étape 3 : Nettoyer vos ressources Votre cluster continue d’accumuler les frais aussi longtemps qu’il est en cours d’exécution. Lorsque vous aurez terminé ce didacticiel, vous pourrez supprimer votre exemple de cluster. Si vous souhaitez conserver le cluster, mais récupérer le stockage utilisé par les tables de données de test, exécutez les commandes suivantes pour supprimer les tables. ``` drop table public.accommodations cascade; ``` ``` drop table public.zipcode cascade; ```