Amazon Redshift Spectrum の開始方法
このチュートリアルでは、Amazon Redshift Spectrum を使用して Amazon S3 上のファイルのデータに直接クエリを実行する方法を説明します。すでにクラスターと SQL クライアントがある場合、このチュートリアルは最小限のセットアップで完了することができます。
注記
Redshift Spectrum クエリには追加料金が発生します。このチュートリアルのサンプルクエリは通常料金で実行できます。料金の詳細については、「Amazon Redshift Spectrum 料金表
前提条件
Redshift Spectrum を使用するには、SQL コマンドを実行するために、クラスターに接続された Amazon Redshift クラスターと SQL クライアントが必要です。クラスターと Amazon S3 内のデータファイルは同じ AWS リージョン に存在する必要があります。
Amazon Redshift クラスターの作成方法の詳細については、「Amazon Redshift 入門ガイド」の「Amazon Redshift でプロビジョニングされたデータウェアハウス」を参照してください。クラスターに接続する方法については、「Amazon Redshift 入門ガイド」の「Amazon Redshift データウェアハウスに接続する」を参照してください。
次の例では、サンプルデータは米国東部 (バージニア北部)リージョン (us-east-1) にあるため、us-east-1 にあるクラスターも必要です。または、Amazon S3 を使用して、次のバケットとフォルダのデータオブジェクトをクラスターがある AWS リージョン のバケットにコピーできます。
s3://redshift-downloads/tickit/spectrum/customers/*s3://redshift-downloads/tickit/spectrum/sales_partition/*s3://redshift-downloads/tickit/spectrum/sales/*s3://redshift-downloads/tickit/spectrum/salesevent/*
次のような Amazon S3 コマンドを実行して、米国東部 (バージニア北部) にあるサンプルデータを AWS リージョン にコピーします。コマンドを実行する前に、Amazon S3 のコピーコマンドに合ったバケットとフォルダをバケットに作成します。Amazon S3 のコピーコマンドの出力により、ファイルが希望する AWS リージョン の bucket-name にコピーされたことが確認されます。
aws s3 cp s3://redshift-downloads/tickit/spectrum/ s3://bucket-name/tickit/spectrum/ --copy-props none --recursive
AWS CloudFormation での Amazon Redshift Spectrum の開始方法
以下に示す手順の代わりに、Redshift Spectrum DataLake の AWS CloudFormation テンプレートにアクセスし、クエリの実行が可能な Amazon S3 バケットを含むスタックを作成することもできます。詳細については、「AWS CloudFormation スタックを起動して Amazon S3 内のデータにクエリを実行する」を参照してください。
ステップバイステップによる Redshift Spectrum の使用開始
Amazon Redshift Spectrum の使用を開始するには、次のステップに従います。
ステップ 1. Amazon Redshift 用の IAM ロールを作成する
クラスターには、AWS Glue または Amazon Athena に置かれている外部データカタログ、および Amazon S3 内のデータファイルにアクセスするための承認が必要です。この承認を提供するには、クラスターにアタッチされた AWS Identity and Access Management (IAM) ロールを参照します。ロールと Amazon Redshift の詳細な使用方法については、IAM ロールを使用して COPY および UNLOAD オペレーションを認可するを参照してください。
注記
特定のケースでは、Athena データカタログを AWS Glue データカタログに移行することができます。これを実行するには、クラスターが AWS Glue 対応の AWS リージョンに存在していること、および Athena データカタログ内に Redshift Spectrum 外部テーブルを持っていることが条件となります。Redshift Spectrum で AWS Glue データカタログを使用するには、IAM ポリシーの変更が必要になる場合があります。詳細については、Athena ユーザーガイドの「AWS Glue データカタログへのアップグレード」を参照してください。
Amazon Redshift のロールを作成する場合は、次のいずれかのアプローチを選択します。
Athena データカタログまたは AWS Glue データカタログで Redshift Spectrum を使用する場合は、Amazon Redshift 用の IAM ロールを作成するにはに示されている手順に従います。
AWS Lake Formation に対し有効になっている AWS Glue Data Catalog で Redshift Spectrum を使用する場合は、以下に示されている手順に従います。
Amazon Redshift 用の IAM ロールを作成するには
-
IAM コンソール
を開きます。 -
ナビゲーションペインで [ロール] を選択します。
-
[Create role] を選択します。
-
AWS サービス を信頼されたエンティティとして選択し、次に Redshift をユースケースとして選択します。
-
[他の AWS のサービス のユースケース] で、[Redshift - カスタマイズ可能]、[次へ] の順に選択します。
-
[アクセス許可ポリシーをアタッチする] ページが表示されます。
AmazonS3ReadOnlyAccessおよびAWSGlueConsoleFullAccessを選択します (AWS Glue データカタログを使用する場合)。または、AmazonAthenaFullAccessを選択します (Athena データカタログを使用する場合)。[Next] を選択します。注記
AmazonS3ReadOnlyAccessポリシーは、すべての Amazon S3 バケットに対する読み込み専用アクセス権をクラスターに付与します。AWS サンプルデータバケットへのアクセスのみを許可するには、新しいポリシーを作成して以下のアクセス許可を追加します。{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:Get*", "s3:List*" ], "Resource": "arn:aws:s3:::redshift-downloads/*" } ] } -
[ロール名] に、
myspectrum_roleなどのロール名を入力します。 -
情報を確認してから、[ロールの作成] を選択します。
-
ナビゲーションペインで [Roles (ロール) ] を選択します。新しいロールの名前を選択して概要を確認し、次にロール ARN をクリップボードにコピーします。この値は、作成したロールの Amazon リソースネーム (ARN) です。この値を使用して、外部テーブルの作成時に Amazon S3 のデータファイルを参照します。
AWS Lake Formation に対し有効化されている AWS Glue Data Catalog を使用して、Amazon Redshift の IAM ロールを作成するには
-
IAM コンソール (https://console.aws.amazon.com/iam/
) を開きます。 -
ナビゲーションペインで、[ポリシー] を選択します。
[Policies] (ポリシー) を初めて選択する場合は、[Welcome to Managed Policies] (マネージドポリシーにようこそ) ページが表示されます。[Get Started] (今すぐ始める) を選択します。
-
[ポリシーを作成] を選択します。
-
[JSON] タブでポリシーの作成を選択します。
-
次の JSON ポリシードキュメントに貼り付けます。これにより、データカタログへのアクセスは付与されますが、Lake Formation の管理者権限は拒否されます。
{ "Version": "2012-10-17", "Statement": [ { "Sid": "RedshiftPolicyForLF", "Effect": "Allow", "Action": [ "glue:*", "lakeformation:GetDataAccess" ], "Resource": "*" } ] } -
完了したら、[確認] を選択してポリシーを確認します。構文エラーがある場合は、Policy Validator によってレポートされます。
-
[ポリシーの確認] ページの [名前] に、
myspectrum_policyと入力して、作成するポリシーに名前を付けます。[説明] (オプション) を入力します。ポリシーの [Summary] (概要) を参照して、ポリシーによって付与された許可を確認します。次に、[Create policy] (ポリシーの作成) を選択して作業を保存します。ポリシーを作成したら、ユーザーにアクセス権を付与できます。
アクセス権限を付与するには、ユーザー、グループ、またはロールにアクセス許可を追加します。
-
AWS IAM Identity Center のユーザーとグループ:
アクセス許可セットを作成します。「AWS IAM Identity Center ユーザーガイド」の「権限設定を作成する」の手順に従ってください。
-
IAM 内で、ID プロバイダーによって管理されているユーザー:
ID フェデレーションのロールを作成します。詳細については、「IAM ユーザーガイド」の「サードパーティー ID プロバイダー (フェデレーション) 用のロールの作成」を参照してください。
-
IAM ユーザー:
-
ユーザーが担当できるロールを作成します。手順については、「IAM ユーザーガイド」の「IAM ユーザー用ロールの作成」を参照してください。
-
(お奨めできない方法) ポリシーをユーザーに直接アタッチするか、ユーザーをユーザーグループに追加する。詳細については、「IAM ユーザーガイド」の「ユーザー (コンソール) へのアクセス権限の追加」を参照してください。
-
Lake Formation データベースでクエリを実行するテーブルの SELECT 権限を付与するには
-
Lake Formation コンソール (https://console.aws.amazon.com/lakeformation/
) を開きます。 -
ナビゲーションで、[データレイクアクセス許可]、[付与] の順に選択します。
-
[AWS Lake Formation 開発者ガイド」の「名前付きリソースメソッドによるテーブルのアクセス許可の付与」の手順に従ってください。以下の情報を記述します。
-
[IAM role (IAM ロール)] で、作成した IAM ロール (
myspectrum_role) を選択します。Amazon Redshift クエリエディタを実行すると、IAM ロールを使用して、データに対するアクセス許可が付与されます。注記
Lake Formation が有効なデータカタログでテーブルに対する SELECT 許可を付与してクエリを実行するには、次を実行します。
Lake Formation にデータのパスを登録します。
Lake Formation でそのパスへのアクセス許可をユーザーに付与します。
作成したテーブルは、Lake Formation で登録したパスで確認できます。
-
-
[Grant] (付与) を選択します。
重要
ベストプラクティスとして、Lake Formation 許可で基本の Amazon S3 オブジェクトにのみアクセスを許可することをお勧めします。承認されていないアクセスを防止するために、Lake Formation 外の Amazon S3 オブジェクトに許可が付与されている場合はそれらをすべて削除します。以前に Amazon S3 オブジェクトにアクセスしたことがあり、Lake Formation を設定する場合は、以前に設定した IAM ポリシーやバケットのアクセス許可をすべて削除します。詳細については、「AWS Lake Formation モデルへの AWS Glue データのアクセス許可のアップグレード」および「Lake Formation のアクセス許可」を参照してください。
ステップ 2: IAM ロールをクラスターと関連付ける
これで、Amazon Redshift が外部データカタログや Amazon S3 にアクセスすることを許可する IAM ロールが作成されました。ここで、そのロールと Amazon Redshift クラスターを関連付ける必要があります。
IAM ロールをクラスターに関連付けるには
-
AWS Management Consoleにサインインして、https://console.aws.amazon.com/redshiftv2/
で Amazon Redshift コンソールを開きます。 -
ナビゲーションメニューで [Clusters] (クラスター) を選択し、更新するクラスター名を選択します。
-
[アクション] で、[IAM ロールの管理] を選択します。[IAM ロール] のページが表示されます。
-
[Enter ARN ] (ARN の入力) を選択し、ARN または IAM ロール を入力するか、リストから IAM ロールを選択します。その後、[Add IAM role (IAM ロールの追加)] を選択して、[Attached IAM roles (アタッチされている IAM ロール)] のリストに追加します。
-
[完了] を選択し、IAM ロールをクラスターに関連付けます。これで、クラスターが変更され、変更が完了します。
ステップ 3: 外部スキーマと外部テーブルを作成する
外部スキーマに外部テーブルを作成します。外部スキーマは、外部データカタログのデータベースを参照し、ユーザーに代わってクラスターの Amazon S3 へのアクセスを許可する IAM ロール ARN を提供します。外部データベースは、Amazon Athena データカタログ、AWS Glue Data Catalog、または Amazon EMR などの Apache Hive メタストアに作成できます。この例では、外部スキーマ Amazon Redshift 作成時に Amazon Athena データカタログに外部データベースを作成します。詳細については、「Amazon Redshift Spectrum 用の外部スキーマ」を参照してください。
外部スキーマと外部テーブルを作成するには
-
外部スキーマを作成するには、次のコマンドの IAM ロール ARN を、ステップ 1 で作成したロール ARN で置き換えます。次に、SQL クライアントでコマンドを実行します。
create external schema myspectrum_schema from data catalog database 'myspectrum_db' iam_role 'arn:aws:iam::123456789012:role/myspectrum_role' create external database if not exists; -
外部テーブルを作成するには、次の CREATE EXTERNAL TABLE コマンドを実行します。
注記
クラスターと Amazon S3 バケットは、同じ AWS リージョン に存在する必要があります。この CREATE EXTERNAL TABLE コマンドの例では、サンプルデータのある Amazon S3 バケットは米国東部 (バージニア北部) AWS リージョン にあります。ソースデータを表示するには、
sales_ts.000ファイルをダウンロードします。 この例を変更して、別の AWS リージョン で実行します。目的の AWS リージョン で Amazon S3 バケットを作成します。Amazon S3 コピーコマンドで販売データをコピーします。次に、この例の
CREATE EXTERNAL TABLEコマンドにあるロケーションオプションをバケットに更新します。aws s3 cp s3://redshift-downloads/tickit/spectrum/sales/ s3://bucket-name/tickit/spectrum/sales/ --copy-props none --recursiveAmazon S3 のコピーコマンドの出力により、ファイルが希望する AWS リージョン の
bucket-nameにコピーされたことが確認されます。copy: s3://redshift-downloads/tickit/spectrum/sales/sales_ts.000 to s3://bucket-name/tickit/spectrum/sales/sales_ts.000create external table myspectrum_schema.sales( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, dateid smallint, qtysold smallint, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp) row format delimited fields terminated by '\t' stored as textfile location 's3://redshift-downloads/tickit/spectrum/sales/' table properties ('numRows'='172000');
ステップ 4: Amazon S3 のデータにクエリを実行する
外部テーブルを作成すると、他の Amazon Redshift テーブルにクエリを実行する際に使用するものと同じ SELECT ステートメントでこれらのテーブルにクエリを実行できます。これらの SELECT ステートメントクエリには、テーブルの結合、データの集計、および述語のフィルタリングが含まれます。
Amazon S3 でデータをクエリするには
-
MYSPECTRUM_SCHEMA.SALES テーブルの行数を取得します。
select count(*) from myspectrum_schema.sales;count ------ 172462
-
Amazon S3 に大きなファクトテーブルを、Amazon Redshift に小さなディメンションテーブルを保持することがベストプラクティスとなります。「データをロードする」でサンプルデータをロードした場合は、EVENT という名前のテーブルがデータベースに作成されます。テーブルが見つからない場合は、次のコマンドを使用して EVENT テーブルを作成します。
create table event( eventid integer not null distkey, venueid smallint not null, catid smallint not null, dateid smallint not null sortkey, eventname varchar(200), starttime timestamp); -
次の COPY コマンドの IAM ロール ARN を「ステップ 1. Amazon Redshift 用の IAM ロールを作成する」で作成したロール ARN と置き換え、[EVENT] テーブルをロードします。オプションで、AWS リージョン
us-east-1の Amazon S3 バケットからallevents_pipe.txtのソースデータをダウンロードして表示できます。 copy event from 's3://redshift-downloads/tickit/allevents_pipe.txt' iam_role 'arn:aws:iam::123456789012:role/myspectrum_role' delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-east-1';次の例では、外部 Amazon S3 テーブル MYSPECTRUM_SCHEMA.SALES をローカル Amazon Redshift テーブルである EVENT と結合し、トップ 10 イベントの合計セールスを検出します。
select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) from myspectrum_schema.sales, event where myspectrum_schema.sales.eventid = event.eventid and myspectrum_schema.sales.pricepaid > 30 group by myspectrum_schema.sales.eventid order by 2 desc;eventid | sum --------+--------- 289 | 51846.00 7895 | 51049.00 1602 | 50301.00 851 | 49956.00 7315 | 49823.00 6471 | 47997.00 2118 | 47863.00 984 | 46780.00 7851 | 46661.00 5638 | 46280.00 -
前のクエリのクエリプランを表示します。Amazon S3 のデータに対して実行された
S3 Seq Scan、S3 HashAggregate、およびS3 Query Scanのステップに注意してください。explain select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) from myspectrum_schema.sales, event where myspectrum_schema.sales.eventid = event.eventid and myspectrum_schema.sales.pricepaid > 30 group by myspectrum_schema.sales.eventid order by 2 desc;QUERY PLAN ----------------------------------------------------------------------------- XN Limit (cost=1001055770628.63..1001055770628.65 rows=10 width=31) -> XN Merge (cost=1001055770628.63..1001055770629.13 rows=200 width=31) Merge Key: sum(sales.derived_col2) -> XN Network (cost=1001055770628.63..1001055770629.13 rows=200 width=31) Send to leader -> XN Sort (cost=1001055770628.63..1001055770629.13 rows=200 width=31) Sort Key: sum(sales.derived_col2) -> XN HashAggregate (cost=1055770620.49..1055770620.99 rows=200 width=31) -> XN Hash Join DS_BCAST_INNER (cost=3119.97..1055769620.49 rows=200000 width=31) Hash Cond: ("outer".derived_col1 = "inner".eventid) -> XN S3 Query Scan sales (cost=3010.00..5010.50 rows=200000 width=31) -> S3 HashAggregate (cost=3010.00..3010.50 rows=200000 width=16) -> S3 Seq Scan myspectrum_schema.sales location:"s3://redshift-downloads/tickit/spectrum/sales" format:TEXT (cost=0.00..2150.00 rows=172000 width=16) Filter: (pricepaid > 30.00) -> XN Hash (cost=87.98..87.98 rows=8798 width=4) -> XN Seq Scan on event (cost=0.00..87.98 rows=8798 width=4)
AWS CloudFormation スタックを起動して Amazon S3 内のデータにクエリを実行する
Amazon Redshift クラスターを作成してクラスターに接続したら、Redshift Spectrum DataLake の AWS CloudFormation テンプレートをインストールし、データをクエリできるようになります。
CloudFormation は、Redshift Spectrum Getting Started DataLake テンプレートをインストールし、以下を含むスタックを作成します。
Redshift クラスターに関連付けられている、
myspectrum_roleという名前のロールmyspectrum_schemaという名前の外部スキーマAmazon S3 バケット内の、
salesという名前の外部テーブルデータがロードされた、
eventという名前の Redshift テーブル
Redshift Spectrum Getting Started DataLake で CloudFormation スタックを起動するには
[Launch CFN stack] (CFN スタックを起動する)
をクリックします。DataLake.yml テンプレートが選択された状態で、CloudFormation コンソールが開きます。 また、Redshift Spectrum Getting Started DataLake CloudFormation の CFN テンプレート
をダウンロードしてカスタマイズし、CloudFormation コンソール (https://console.aws.amazon.com/cloudformation ) を開いて、カスタマイズしたテンプレートでスタックを作成することもできます。 [Next] を選択します。
[Parameters] (パラメータ)で、Amazon Redshift クラスター名、データベース名、およびデータベースのユーザー名を入力します。
[Next] を選択します。
スタックに関するオプションが表示されます。
[Next] (次へ) をクリックして、デフォルト設定を受け入れます。
情報を確認し、[機能] で、[AWS CloudFormation によって IAM リソースが作成される場合があることを承認します] を選択します。
[スタックの作成] を選択します。
スタックの作成中にエラーが発生した場合は、以下の情報をご確認ください。
エラーの解決に役立つ情報については、CloudFormation の [Events] (イベント) タブを開きます。
オペレーションを再試行する前に DataLake の CloudFormation スタックを削除します。
Amazon Redshift データベースに接続されていることを確認します。
Amazon Redshift クラスター名、データベース名、およびデータベースのユーザー名に関する情報を、正確に入力しているか確認します。
Amazon S3 でデータをクエリする
他の Amazon Redshift テーブルにクエリを実行する際に使用するものと同じ SELECT ステートメントで、外部テーブルでもクエリを実行できます。これらの SELECT ステートメントクエリには、テーブルの結合、データの集計、および述語のフィルタリングが含まれます。
次のクエリは、外部テーブル myspectrum_schema.sales 内の行数を返します。
select count(*) from myspectrum_schema.sales;
count ------ 172462
外部テーブルとローカルテーブルを結合する
次の例では、外部テーブル myspectrum_schema.sales をローカルテーブルである event と結合し、トップ 10 イベントの合計セールスを検出しています。
select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) from myspectrum_schema.sales, event where myspectrum_schema.sales.eventid = event.eventid and myspectrum_schema.sales.pricepaid > 30 group by myspectrum_schema.sales.eventid order by 2 desc;
eventid | sum
--------+---------
289 | 51846.00
7895 | 51049.00
1602 | 50301.00
851 | 49956.00
7315 | 49823.00
6471 | 47997.00
2118 | 47863.00
984 | 46780.00
7851 | 46661.00
5638 | 46280.00
クエリプランを表示する
前のクエリのクエリプランを表示します。Amazon S3 のデータに対して実行された S3 Seq Scan、S3 HashAggregate、および S3 Query Scan のステップをメモします。
explain select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) from myspectrum_schema.sales, event where myspectrum_schema.sales.eventid = event.eventid and myspectrum_schema.sales.pricepaid > 30 group by myspectrum_schema.sales.eventid order by 2 desc;
QUERY PLAN ----------------------------------------------------------------------------- XN Limit (cost=1001055770628.63..1001055770628.65 rows=10 width=31) -> XN Merge (cost=1001055770628.63..1001055770629.13 rows=200 width=31) Merge Key: sum(sales.derived_col2) -> XN Network (cost=1001055770628.63..1001055770629.13 rows=200 width=31) Send to leader -> XN Sort (cost=1001055770628.63..1001055770629.13 rows=200 width=31) Sort Key: sum(sales.derived_col2) -> XN HashAggregate (cost=1055770620.49..1055770620.99 rows=200 width=31) -> XN Hash Join DS_BCAST_INNER (cost=3119.97..1055769620.49 rows=200000 width=31) Hash Cond: ("outer".derived_col1 = "inner".eventid) -> XN S3 Query Scan sales (cost=3010.00..5010.50 rows=200000 width=31) -> S3 HashAggregate (cost=3010.00..3010.50 rows=200000 width=16) -> S3 Seq Scan spectrum.sales location:"s3://redshift-downloads/tickit/spectrum/sales" format:TEXT (cost=0.00..2150.00 rows=172000 width=16) Filter: (pricepaid > 30.00) -> XN Hash (cost=87.98..87.98 rows=8798 width=4) -> XN Seq Scan on event (cost=0.00..87.98 rows=8798 width=4)
