Amazon Redshift Spectrum の開始方法

IAM コンソールを開きます。

ナビゲーションペインで [ロール] を選択します。

[Create role] を選択します。

AWS サービス を信頼されたエンティティとして選択し、次に Redshift をユースケースとして選択します。

[他の AWS のサービス のユースケース] で、[Redshift - カスタマイズ可能]、[次へ] の順に選択します。

[アクセス許可ポリシーをアタッチする] ページが表示されます。AmazonS3ReadOnlyAccess および AWSGlueConsoleFullAccess を選択します (AWS Glue データカタログを使用する場合)。または、AmazonAthenaFullAccessを選択します (Athena データカタログを使用する場合)。[Next] を選択します。

{
    "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 のデータファイルを参照します。

IAM コンソール (https://console.aws.amazon.com/iam/) を開きます。

ナビゲーションペインで、[ポリシー] を選択します。

[Policies] (ポリシー) を初めて選択する場合は、[Welcome to Managed Policies] (マネージドポリシーにようこそ) ページが表示されます。[Get Started] (今すぐ始める) を選択します。

[Create policy] を選択します。

[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] (ポリシーの作成) を選択して作業を保存します。

ポリシーを作成したら、ユーザーにアクセス権を付与できます。

Lake Formation コンソール (https://console.aws.amazon.com/lakeformation/) を開きます。

ナビゲーションで、[データレイクアクセス許可]、[付与] の順に選択します。

[AWS Lake Formation 開発者ガイド」の「名前付きリソースメソッドによるテーブルのアクセス許可の付与」の手順に従ってください。以下の情報を記述します。

[Grant] (付与) を選択します。

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 ロールをクラスターに関連付けます。これで、クラスターが変更され、変更が完了します。

外部スキーマを作成するには、次のコマンドの 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 コマンドを実行します。

aws s3 cp s3://redshift-downloads/tickit/spectrum/sales/ s3://bucket-name/tickit/spectrum/sales/ --copy-props none --recursive

copy: s3://redshift-downloads/tickit/spectrum/sales/sales_ts.000 to s3://bucket-name/tickit/spectrum/sales/sales_ts.000

create 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');

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)

[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 リソースが作成される場合があることを承認します] を選択します。

[スタックの作成] を選択します。