Amazon Redshiftではじめるビッグデータ処理入門

第4回Amazon Redshiftにデータをロードしてみよう

前回は、Amazon Redshiftの起動から、ローカルマシンでAmazon Redshiftクラスタへ接続するまでの説明を行いました。今回は、Amazon Redshiftを実際に運用する上で重要となるデータのロードを中心に、例を交えて説明していきます。なお、チュートリアルの中で利用するデータはAmazon Redshift Getting Started Guideで紹介されているデータを利用しています。

本連載のチュートリアルは、AWSアカウント、起動中のAmazon Redshiftクラスタ、また接続するクライアントツールがすでに準備されていることを前提としています⁠。実際に試される方は、前回の記事を参考に環境準備をしておいてください。

※)
Hapyrusでは、RedshiftとFlyDataをお試しいただけるSandbox環境を無料で提供しています。また、ブラウザ上からRedshiftに対してSQLが実行できる機能もありますので、無料のRedshift環境にご興味のある方はぜひこちらからのご登録をおすすめします。

テーブルの作成

データのロードの前に、まずテーブルを作成する必要があります。Amazon Redshift Getting Started Guide - Step5に記載されているCREATE文(users, venue, category, date)全てをコピーし、SQLをRedshiftクラスタ上で実行してみましょう図1⁠。

図1 テーブルの作成
図1 テーブルの作成

これらのCREATE文は、MySQLやPostgreSQL上での実行するものと特段変わりませんが、distkey, sortkeyといった見慣れないキーワードがあることに気づくでしょう。これらは、設定したカラムのデータをどのようにRedshiftクラスタ上に配置するか、その配置方法について示すもので、データをロードした後のSELECTクエリを発行する際のパフォーマンスに大きく影響します。

デフォルトでは、Redshiftはクラスタの全ノードに均等にデータが分散されるようにデータを保存しますが、distkeyを指定したカラムは同じ値の場合に同じノードに保存されるようになり、そのカラムを含むテーブルをJOINで結合するSQLの高速化が期待できます。sortkeyは、指定したカラムの値がソートされてノードに保存されるようになり、たとえばタイムスタンプをsortkeyと指定することで、タイムスタンプをWHERE句で範囲指定したSELECTクエリのパフォーマンスの向上が期待できます。このためPostgreSQLやOracleなどのテーブルのパーティショニングは、Redshiftでは必要ありません。

その他、PostgreSQLと異なり、以下のような点に注意する必要があります。

ALTER COLUMNは利用不可
テーブルのカラムを変更する場合は、カラムの追加と削除で対応します。
Constraintsによる制約は設定できない
primary keyのようなキー制約はクエリのオプティマイズにのみ利用され、一意制約としてデータロード時にエラーを返すといったようなことは無く、重複データをチェックする機構を持ち合わせていません。このような制御はアプリ側で行う必要があります。
サポートされているデータ型がPostgreSQLに比べて限られている
INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、BOOLEAN、CHAR、VARCHAR、DATE、TIMESTAMPをサポート。文字コードはUTF-8をサポートしています。データタイプの詳細はAmazon Redshift Developer Guide(Data Type)を参照してください。

テーブル設計に関しては、クラスタ型というアーキテクチャのため、上記以外にも通常のPostgreSQLとは異なる点があります。ここでは、これ以上深くは掘り下げませんが、テーブルをデザインやクエリの速度チューニングを行う際にはAmazon Redshift Developer Guide(Designing Table)を見直すと良いでしょう。

Redshiftへのデータのロード

Amazon Redshiftへのデータのロード方法は、以下の3通りがサポートされています。

  • COPYコマンドによるAmazon S3バケットからの一括ロード
  • COPYコマンドによるAmazon DynamoDBのテーブルからのインポート
  • INSERTクエリによるレコード単位の追加

Amazon RedshiftのAmazon Redshift Developer Guide(Loading Data)によると、大容量のデータをロードする場合には、この中でもCOPYコマンドによるS3、DynamoDBからの一括ロードを推奨しています。これは、AWS上でデータを並列に処理してRedshiftクラスタに取り込め、ロードの効率が良いためです。これに比べ、INSERTクエリによるレコードの追加はパフォーマンスが非常に悪く、限定的な使い方になるでしょう。なお、今回はS3バケットからのロード方法について説明します。

データの作成

S3バケットからデータをロードする場合、まずCSVやTSVといったように特定文字をセパレータとしたフォーマットでファイルを作成し、S3のバケットにアップロードします。Amazonが提供しているサンプルデータでは、以下のようにパイプを区切り文字としたデータとなっています。

categoryテーブルのサンプルデータ(例)
1|Sports|MLB|Major League Baseball
2|Sports|NHL|National Hockey League
3|Sports|NFL|National Football League

今回はAmazon提供のデータをそのまま利用するため、データの作成およびアップロードの必要はありません。

データのロード

S3にアップロードされたデータをロードするには、Redshiftクラスタへ接続したクライアントツールからCOPYコマンドを発行します。このCOPYコマンドはRedshift用に拡張されたもので、以下の通りS3のファイルのパスやAWSのアクセスキーを指定するようになっています。AWSのアクセスキーはS3バケットへアクセスするために使われます。

COPYコマンドのフォーマット
COPY <Redshiftクラスタ上のテーブル名> FROM 's3://<バケット名>/<ファイルパス>' CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID(Access-Key-ID)>;aws_secret_access_key=<あなたのAWSシークレットキー(Secret-Access-Key)>' <オプション>;

さっそく以下を修正し、Redshiftに接続したクライアントツールから実行してみましょう。今回利用するS3はパブリックに公開されているものですので、AWSキーは任意のもので構いません。

サンプルデータロード用のCOPYコマンドAmazon Redshift Getting Start Guideより)
copy users from 's3://awssampledb/tickit/allusers_pipe.txt' CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID>;aws_secret_access_key=<あなたのAWSシークレットキー>' delimiter '|';
copy venue from 's3://awssampledb/tickit/venue_pipe.txt' CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID>;aws_secret_access_key=<あなたのAWSシークレットキー>' delimiter '|';
copy category from 's3://awssampledb/tickit/category_pipe.txt' CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID>;aws_secret_access_key=<あなたのAWSシークレットキー>' delimiter '|';
copy date from 's3://awssampledb/tickit/date2008_pipe.txt' CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID>;aws_secret_access_key=<あなたのAWSシークレットキー>' delimiter '|';
copy event from 's3://awssampledb/tickit/allevents_pipe.txt' CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID>;aws_secret_access_key=<あなたのAWSシークレットキー>' delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS';
copy listing from 's3://awssampledb/tickit/listings_pipe.txt' CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID>;aws_secret_access_key=<あなたのAWSシークレットキー>' delimiter '|';
copy sales from 's3://awssampledb/tickit/sales_tab.txt'CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID>;aws_secret_access_key=<あなたのAWSシークレットキー>' delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS';

注意点として、S3バケットはコピー先のRedshiftクラスタと同じリージョンである必要があります。上記はUS East(Northern Virginia)のリージョンを想定したもので、RedshiftクラスタのリージョンがUS West(Oregon)リージョンの場合はawssampledbuswest2に、EU(Ireland)リージョンの場合はawssampledbeuwest1と、それぞれCOPYコマンドの中のバケット名を指定してください。

クエリの発行

データのインポートが完了したら、さっそくSELECTクエリを試してみましょう。以下の通り、SELECTクエリは既存のRDBMSとほぼ同様に実行することができます図2⁠。

SELECTクエリAmazon Redshift Getting Start Guideより)
-- 2008年1月5日の売り上げ
SELECT sum(qtysold) 
FROM   sales, date 
WHERE  sales.dateid = date.dateid 
AND    caldate = '2008-01-05';

-- 販売個数が上位10位までの顧客
SELECT firstname, lastname, total_quantity 
FROM   (SELECT buyerid, sum(qtysold) total_quantity
        FROM  sales
        GROUP BY buyerid
        ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;

-- 売り上げ順に並べたイベントの上位0.1パーセント
SELECT eventname, total_price 
FROM  (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile 
       FROM (SELECT eventid, sum(pricepaid) total_price
             FROM   sales
             GROUP BY eventid)) Q, event E
       WHERE Q.eventid = E.eventid
       AND percentile = 1
ORDER BY total_price desc;
図2 SELECTクエリの実行
図2 SELECTクエリの実行

データのロードのコツ

最後にデータのロードについて、実際の運用時に気をつけるべきポイントを紹介します。

1回のCOPYコマンドで複数ファイルをロード

COPYコマンドでS3バケット上のファイルを指定する場合、ファイルが含まれるディレクトリを指定すると、そのディレクトリ以下のファイルを一括で取り込むことができます。たとえば、筆者がRedshiftのベンチマークを取得した際は、以下のように日付ごとにパスを区切ってファイルを配置し、1回のCOPYコマンドで全てのファイルをロードしました。Redshiftは並列でデータをロードすることから、ファイル単位でCOPYコマンドを実行するのに比べて速べてロードの速度の向上が期待できます。また単一のファイルをロードするよりもファイルを分割した方が効率良く取り込むことができます。

S3上のファイル
s3://バケット名/テーブル名/2012/01/2013-01-01.tsv
s3://バケット名/テーブル名/2012/01/2013-01-02.tsv
...
COPYコマンドで指定するパス
s3://バケット名/テーブル名

GZIPによりファイルを圧縮

S3バケット上のファイルのロードはGZIP形式に対応しており、COPYコマンドのオプションに"GZIP"と指定するだけでGZIPファイルのロードを行うことができます。実運用を想定した場合、S3へのアップロードやファイルのバックアップのコストの点でも必須のオプションと言えるでしょう。

初回ロード時に自動で圧縮方法を設定

Redshift内部では、カラム単位でデータを圧縮(エンコーディング)して保存する仕組みとなっており、テーブル作成時にカラムごとにエンコーディングの種類を指定することができます。

また、テーブルが空の状態でCOPYコマンドによりデータをロードすると、それぞれのカラムの最適なエンコーディングを決定し、自動でそのエンコーディングの設定を行います。ただし、これには十分なデータ量が必要であり、XLタイプでシングルノード構成の場合は最低でも20万レコード以上が必要とされています。手元にあるデータのレコード数が十分にある場合は、一括で取り込むことで自動的に最適なエンコーディングが初回ロード時に設定されます。

なお、このエンコーディング方法は、データを格納した後は変更できません。手元のデータが少なく、これから蓄積されていくような場合には、データが蓄積したタイミングで"ANALYZE COMPRESSION"コマンドにより最適なエンコーディングを知ることができますので、そのタイミングでエンコーディングを指定したテーブルを別途作成しデータを移行することで対応できます。

今回はRedshift上でのテーブルの作成とデータのロードについて説明を行いました。テーブルのデザインにあたってはdistkey、sortkeyや圧縮の方法(エンコーディング)についてなどといった、Redshiftならではのトピックがあります。実際にRedshiftを利用して運用する場合には、これまでも紹介した通りAmazon Redshift Developer GuideDesigning TablesLoading Dataの項目を確認すると良いでしょう。


筆者が所属する Hapyrusでは、Amazon Redshift を使いはじめるための継続データインテグレーションサービスFlyData for Amazon Redshiftを提供しています。また、Amazon Redshift の導入コンサルティングも無料で行なっていますので、興味がある方はぜひお気軽に info@hapyrus.com にお問い合わせください。

おすすめ記事

記事・ニュース一覧