AWS Redshiftを使ってデータをロードしてクエリを実行するまで

f:id:taigok:20190101112401p:plain

AWS Redshiftとは

完全マネージド型、ペタバイトスケールのAWSデータウェアハウスサービス。

AWS Athenaとの違い

「Amazon Athena よくある質問」には下記記載あり。大きな違いは、課金体系でRedshiftはクラスタの起動時間課金に対して、Athenaの場合はデータ使用量に対する課金になります。

Amazon Redshift のクエリエンジンは、非常に大規模なデータベーステーブルを多数結合する複雑なクエリを実行する必要がある場合のユースケースで、パフォーマンスを特に発揮するように最適化されています。非常に大規模な多数のテーブル間で多数の結合がある、高度に構造化されたデータに対してクエリを実行する必要がある場合は、Amazon Redshift をお勧めします。

これに対して、Amazon Athena のようなクエリサービスを使用すると、データの形式化やインフラストラクチャの管理について心配することなく、Amazon S3 のデータに対して直接インタラクティブにクエリを実行できます。例えば、Athena は、一部のウェブログですばやくクエリを実行し、サイトのパフォーマンス問題のトラブルシューティングのみが必要とされる場合に適しています。クエリサービスを使用すると、迅速に開始できます。データのテーブルを定義し、標準 SQL を使用してクエリを開始するのみです。

課金体系

Redshiftはオンデマンドの場合、クラスタの起動時間課金になります。Redshift Spectrumの場合、スキャンされたバイト数に対して課金されます。Athenaの場合、スキャンされたデータ 1TB あたり 5USDがかかります。

参考：Amazon Redshift の料金、Amazon Athena の料金

準備

入門ガイドである Amazon Redshift の使用開始を参考に進めます。　ガイド通りにSQL Workbench/JというSQL クライアントツールをインストールをしました。

Amazon S3 のサンプルデータをロードする

CREATE TABLE

CREATE TABLE を参考に、Workbench/Jから下記DDL(Data Definition Language)を実行して、新しいテーブルを作成します。

create table users(
    userid integer not null distkey sortkey,
    username char(8),
    firstname varchar(30),
    lastname varchar(30),
    city varchar(30),
    state char(2),
    email varchar(100),
    phone char(14),
    likesports boolean,
    liketheatre boolean,
    likeconcerts boolean,
    likejazz boolean,
    likeclassical boolean,
    likeopera boolean,
    likerock boolean,
    likevegas boolean,
    likebroadway boolean,
    likemusicals boolean);

create table venue(
    venueid smallint not null distkey sortkey,
    venuename varchar(100),
    venuecity varchar(30),
    venuestate char(2),
    venueseats integer);

create table category(
    catid smallint not null distkey sortkey,
    catgroup varchar(10),
    catname varchar(10),
    catdesc varchar(50));

create table date(
    dateid smallint not null distkey sortkey,
    caldate date not null,
    day character(3) not null,
    week smallint not null,
    month character(5) not null,
    qtr character(5) not null,
    year smallint not null,
    holiday boolean default('N'));

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

create table listing(
    listid integer not null distkey,
    sellerid integer not null,
    eventid integer not null,
    dateid smallint not null  sortkey,
    numtickets smallint not null,
    priceperticket decimal(8,2),
    totalprice decimal(8,2),
    listtime timestamp);

create table sales(
    salesid integer not null,
    listid integer not null distkey,
    sellerid integer not null,
    buyerid integer not null,
    eventid integer not null,
    dateid smallint not null sortkey,
    qtysold smallint not null,
    pricepaid decimal(8,2),
    commission decimal(8,2),
    saletime timestamp);

s３にあるサンプルデータをロード

copy users from 's3://awssampledbuswest2/tickit/allusers_pipe.txt' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter '|' region 'us-west-2';

copy venue from 's3://awssampledbuswest2/tickit/venue_pipe.txt' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter '|' region 'us-west-2';

copy category from 's3://awssampledbuswest2/tickit/category_pipe.txt' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter '|' region 'us-west-2';

copy date from 's3://awssampledbuswest2/tickit/date2008_pipe.txt' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter '|' region 'us-west-2';

copy event from 's3://awssampledbuswest2/tickit/allevents_pipe.txt' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-west-2';

copy listing from 's3://awssampledbuswest2/tickit/listings_pipe.txt' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter '|' region 'us-west-2';

copy sales from 's3://awssampledbuswest2/tickit/sales_tab.txt'
credentials 'aws_iam_role=<iam-role-arn>'
delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'us-west-2';

クエリ実行

SELECT *    
FROM pg_table_def    
WHERE tablename = 'sales';

public   sales   salesid integer    delta32k    false   0  true
public  sales   listid  integer    delta32k    true    0  true
public  sales   sellerid    integer    delta32k    false   0  true
public  sales   buyerid integer    delta32k    false   0  true
public  sales   eventid integer    delta32k    false   0  true
public  sales   dateid  smallint    lzo false   1  true
public  sales   qtysold smallint    lzo false   0  true
public  sales   pricepaid   numeric(8,2)  lzo false   0  false
public  sales   commission  numeric(8,2)  delta32k    false   0  false
public  sales   saletime    timestamp without time zone lzo false   0  false

クラスターのリセット

ステップ 7: 他のリソースの検索と環境のリセットを参考にクラスターを削除します。クラスターをするまで、そのクラスターについて Amazon Redshift サービスの使用料が継続して発生するので注意しましょう。