Amazon Redshift は、パッチ 198 以降、新しい Python UDF の作成をサポートしなくなります。既存の Python UDF は、2026 年 6 月 30 日まで引き続き機能します。詳細については、[ブログ記事](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)を参照してください。 # チュートリアル: Amazon Redshift での空間 SQL 関数の使用このチュートリアルでは、Amazon Redshift でいくつかの空間 SQL 関数を使用する方法を説明します。これを行うには、空間 SQL 関数を使用して 2 つのテーブルをクエリします。このチュートリアルでは、ドイツのベルリンに位置する賃貸宿泊施設の位置データと郵便番号を関連付ける公開データセットのデータを使用します。 **Topics** + [前提条件](#spatial-tutorial-prerequisites) + [ステップ 1: テーブルを作成し、テストデータをロードする](#spatial-tutorial-test-data) + [ステップ 2: 空間データのクエリを実行する](#spatial-tutorial-query) + [ステップ 3: リソースをクリーンアップする](#spatial-tutorial-clean-up) ## 前提条件このチュートリアルでは、以下のリソースが必要になります。 + アクセスおよび更新できる既存の Amazon Redshift クラスターおよびデータベース。既存のクラスターでは、テーブルを作成し、サンプルデータをロードし、SQL クエリを実行して空間関数を実証します。クラスターには 2 つ以上のノードが必要です。クラスターの作成方法を学ぶには、[Amazon Redshift 入門ガイド](https://docs.aws.amazon.com/redshift/latest/gsg/)の手順に従ってください。 + Amazon Redshift クエリエディタを使用するには、クエリエディタをサポートする AWS リージョンにあるクラスターを使用する必要があります。詳細については、「*Amazon Redshift 管理ガイド*」の「[クエリエディタを使用してデータベースのクエリを実行する](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html)」を参照してください。 + AWSAmazon S3 からのテストデータのロードを Amazon Redshift クラスターに許可する認証情報。AWS の他のサービス (Amazon S3 など) へのアクセス方法については、「[Amazon Redshift が AWS サービスにアクセスすることを許可する](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html)」を参照してください。 + `mySpatialDemoRole` という名前の AWS Identity and Access Management (IAM) ロール。Amazon S3 データを読み取るためのマネージドポリシー `AmazonS3ReadOnlyAccess` がアタッチ済み。Amazon S3 バケットからデータをロードするアクセス許可を持つロールを作成するには、「*Amazon Redshift 管理ガイド*」の「[IAM ロールを使用して COPY、UNLOAD、CREATE EXTERNAL SCHEMA オペレーションを認可する](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html)」を参照してください。 + IAM ロール `mySpatialDemoRole` を作成した後、そのロールには Amazon Redshift クラスターとの関連付けが必要です。その関連付けを作成する方法の詳細については、「*Amazon Redshift 管理ガイド*」の「[IAM ロールを使用して COPY、UNLOAD、CREATE EXTERNAL SCHEMA オペレーションを認可する](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html)」を参照してください。 ## ステップ 1: テーブルを作成し、テストデータをロードするこのチュートリアルで使用されているソースデータは、`accommodations.csv` および `zipcodes.csv` という名前のファイルです。 `accommodations.csv` ファイルは、insideairbnb.com からのオープンソースデータです。`zipcodes.csv` ファイルは、ドイツのベルリンブランデンブルク国立統計研究所 (Amt für Statistik Berlin-Brandenburg) からのオープンソースデータである郵便番号を提供します。どちらのデータソースも、クリエイティブ・コモンズ・ライセンスで提供されています。データは、ドイツ、ベルリンリージョンに限定されています。これらのファイルは、このチュートリアルで使用する Amazon S3 公開バケットにあります。必要に応じて、次の Amazon S3 リンクからソースデータをダウンロードできます。 + [`accommodations` テーブルのソースデータ](https://s3.amazonaws.com/redshift-downloads/spatial-data/accommodations.csv)。 + [`zipcode` テーブルのソースデータ](https://s3.amazonaws.com/redshift-downloads/spatial-data/zipcode.csv)。次の手順を使用して、テーブルを作成し、テストデータをロードします。 **テーブルを作成してテストデータをロードするには** 1. Amazon Redshift クエリエディタを開きます。クエリエディタ操作の詳細については、「*Amazon Redshift 管理ガイド*」の「[クエリエディタを使用するデータベースのクエリ](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html)」を参照してください。 1. このチュートリアルで使用するテーブルがデータベースにすでに存在する場合は、削除してください。詳細については、「[ステップ 3: リソースをクリーンアップする](#spatial-tutorial-clean-up)」を参照してください。 1. `accommodations` テーブルを作成して、各宿泊施設の地理的位置 (経度と緯度)、リストの名前、およびその他のビジネスデータを保存します。このチュートリアルでは、ドイツ、ベルリンの部屋レンタルについて説明します。`shape` 列には、宿泊施設の場所に関する地理的ポイントが保存されます。その他の列には、レンタルに関する情報が含まれています。 `accommodations` テーブルを作成するには、Amazon Redshift クエリエディタで次の SQL ステートメントを実行します。 ``` 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. ベルリンの郵便番号を保存するために、クエリエディタで `zipcode` テーブルを作成します。 *郵便番号*は、`wkb_geometry` 列のポリゴンとして定義されています。残りの列では、郵便番号に関する追加の空間メタデータについて説明しています。 `zipcode` テーブルを作成するには、Amazon Redshift クエリエディタで次の SQL ステートメントを実行します。 ``` 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. サンプルデータをテーブルにロードします。このチュートリアルのサンプルデータは、認証済みのすべての AWS ユーザーに読み取りアクセスを許可する、Amazon S3 バケット内に用意されています。Amazon S3 へのアクセスを許可する、有効な AWS 認証情報を使用する必要があります。テストデータをテーブルにロードするには、次の COPY コマンドを実行します。*`account-number`* は自分の AWS アカウント番号に置き換えます。一重引用符で囲まれた認証情報文字列に、スペースまたは改行を含めることはできません。 ``` 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. 以下のコマンドを実行して、各テーブルが正しくロードされていることを確認します。 ``` select count(*) from accommodations; ``` ``` select count(*) from zipcode; ``` 以下の結果の表に示しているのは、テストテーブルの各テーブルでの行数です。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/spatial-tutorial.html) ## ステップ 2: 空間データのクエリを実行するテーブルを作成してロードした後、SQL SELECT ステートメントを使用してテーブルにクエリを実行できます。次のクエリは、取得できる情報の一部を示しています。ニーズを満たすために空間関数を使用する他の多くのクエリを書き込むことができます。 **空間データをクエリするには** 1. 次のように、クエリを実行して、`accommodations` テーブルに保存されているリストの総数を取得します。空間参照系は世界測地系 (WGS) 84 であり、一意の空間参照識別子 4326 を持ちます。 ``` SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326; ``` ``` count ------- 22248 ``` 1. いくつかの追加属性を使用して、ジオメトリオブジェクトを well-known text (WKT) 形式で取得します。さらに、この郵便番号データが空間参照 ID (SRID) 4326 を使用する世界測地システム (WGS) 84 にも保存されているかどうかを検証できます。空間データを相互運用するには、空間データを同じ空間参照系に保存する必要があります。 ``` 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. ベルリンの自治区であるベルリンミッテ (10117) のポリゴンを GeoJSON 形式で選択し、そのディメンション、およびこのポリゴンのポイント数を選択します。 ``` 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. 次の SQL コマンドを実行して、ブランデンブルク門から 500 メートル以内にある宿泊施設の数を確認します。 ``` SELECT count(*) FROM public.accommodations WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500; ``` ``` count ------ 29 ``` 1. 次のクエリを実行して、近いと表示されている宿泊施設に保存されているデータから、ブランデンブルク門の大まかな位置を取得します。このクエリには、複数の選択が必要です。リクエストされた場所が宿泊施設に近いため、以前のクエリと同じではないため、カウントが異なります。 ``` 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. 次のクエリを実行して、ブランデンブルク門周辺のすべての宿泊施設の詳細を料金の降順に表示します。 ``` 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. 次のクエリを実行して、最も高価な宿泊施設を郵便番号で取得します。 ``` 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. サブクエリを使用して、宿泊施設の最大料金、最小料金、または中央値を計算します。次のクエリは、宿泊施設の料金の中央値を郵便番号別に示しています。 ``` 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. 次のクエリを実行して、ベルリンにリストされている宿泊施設の数を取得します。ホットスポットを見つけるために、これらは郵便番号でグループ化され、供給量によってソートされます。 ``` 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) ``` ## ステップ 3: リソースをクリーンアップするクラスターが実行されている限り料金が発生し続けます。このチュートリアルを完了したら、サンプルクラスターを削除できます。クラスターを維持したいが、テストデータテーブルで使用されるストレージを復元するには、以下のコマンドを実行してテーブルを削除します。 ``` drop table public.accommodations cascade; ``` ``` drop table public.zipcode cascade; ```