前回は、
本連載のチュートリアルは、
- ※）
- Hapyrusでは、
RedshiftとFlyDataをお試しいただけるSandbox環境を無料で提供しています。また、 ブラウザ上からRedshiftに対してSQLが実行できる機能もありますので、 無料のRedshift環境にご興味のある方はぜひこちらからのご登録をおすすめします。
テーブルの作成
データのロードの前に、
これらのCREATE文は、
デフォルトでは、
その他、
- 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)を参照してください。
テーブル設計に関しては、
Redshiftへのデータのロード
Amazon Redshiftへのデータのロード方法は、
- COPYコマンドによるAmazon S3バケットからの一括ロード
- COPYコマンドによるAmazon DynamoDBのテーブルからのインポート
- INSERTクエリによるレコード単位の追加
Amazon RedshiftのAmazon Redshift Developer Guide(Loading Data)によると、
データの作成
S3バケットからデータをロードする場合、
1|Sports|MLB|Major League Baseball
2|Sports|NHL|National Hockey League
3|Sports|NFL|National Football League今回はAmazon提供のデータをそのまま利用するため、
データのロード
S3にアップロードされたデータをロードするには、
COPY <Redshiftクラスタ上のテーブル名> FROM 's3://<バケット名>/<ファイルパス>' CREDENTIALS 'aws_access_key_id=<あなたのAWSのアクセスキーID(Access-Key-ID)>;aws_secret_access_key=<あなたのAWSシークレットキー(Secret-Access-Key)>' <オプション>;さっそく以下を修正し、
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';注意点として、
クエリの発行
データのインポートが完了したら、
-- 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;
データのロードのコツ
最後にデータのロードについて、
1回のCOPYコマンドで複数ファイルをロード
COPYコマンドでS3バケット上のファイルを指定する場合、
s3://バケット名/テーブル名/2012/01/2013-01-01.tsv
s3://バケット名/テーブル名/2012/01/2013-01-02.tsv
...s3://バケット名/テーブル名GZIPによりファイルを圧縮
S3バケット上のファイルのロードはGZIP形式に対応しており、
初回ロード時に自動で圧縮方法を設定
Redshift内部では、
また、
なお、
今回はRedshift上でのテーブルの作成とデータのロードについて説明を行いました。テーブルのデザインにあたってはdistkey、
筆者が所属する Hapyrusでは、