CodeZine(コードジン)

特集ページ一覧

Amazon Redshiftへのデータ投入からBIツールによる可視化までの手順

Amazon Redshiftによるビッグデータ分析環境の構築(4)

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2015/02/19 14:00
目次

Amazon S3からのデータ投入(COPY)

 アップロードするファイルとそのファイルが格納されるテーブルができましたので、いよいよ実際の投入作業に入りたいと思います。Amazon RedshiftのCOPYコマンドを用いてテーブルにデータをロードします。

 まずはこれまでの手順で作成したCSVファイルを、gz圧縮しておきます。Amazon Redshiftにロードする際はCSVの場合、圧縮なしでも圧縮しても取り込むことができますが、圧縮しておくとCOPY処理時のI/O転送量が圧縮分削減できますので、可能な限りgz圧縮は行っておいた方が良いでしょう。今回のケースであれば、ファイルを新しく追加し、新しく作成したテーブルへのデータのCOPY確認時は無圧縮でトライアンドエラーしながらファイルベースで対応すべき箇所を洗い出し、エラーの洗い出しが大方完了したら以降はgz圧縮状態で処理を行う、というような進め方が良いかと思います。

 圧縮処理が完了したら、S3バケットへファイルをアップロードします。

 Amazon RedshiftのDBへアクセスし、COPY処理を実行します。なお、COPY処理実行時にはアクセスキー(aws_access_key_id)とシークレットアクセスキー(aws_secret_access_key)の情報が必要です。Amazon RedshiftおよびS3のアクセス権限を有したユーザーのAPIキーが必要ですので、ない場合は改めて情報を入手しておいてください。

 以下COPY文が実際に環境を構築した際に用いたものです。前述のようにアクセスキー(aws_access_key_id)とシークレットアクセスキー(aws_secret_access_key)についてはユーザー固有の情報を設定して置き換えてください。またバケット名についても同じ名前のバケットは作成できませんので、適宜別名に置き換えてください。

 また、COPY処理では内容に基いて適宜オプション指定を行っています。各種指定内容については以下公式ドキュメントをご参照ください。

/** ----------------------*/
/** for codezine.orders */
/** ----------------------*/
DELETE FROM codezine.orders;
COMMIT;
VACUUM codezine.orders;
SELECT COUNT(*) FROM codezine.orders;

COPY codezine.orders
FROM 's3://codezine-redshift-tokyo/codezine_orders-utf8.csv.gz'
CREDENTIALS 'aws_access_key_id=(アクセスキーID);aws_secret_access_key=(シークレットアクセスキー)'
DELIMITER ','
CSV GZIP QUOTE AS '"'
DATEFORMAT 'YYYY/MM/DD'
IGNOREHEADER 1;

/** -------------------------*/
/** for codezine.inventory */
/** -------------------------*/
DELETE FROM codezine.inventory;
COMMIT;
VACUUM codezine.inventory;
SELECT COUNT(*) FROM codezine.inventory;

COPY codezine.inventory
FROM 's3://codezine-redshift-tokyo/codezine_inventory-utf8.csv.gz'
CREDENTIALS 'aws_access_key_id=(アクセスキーID);aws_secret_access_key=(シークレットアクセスキー)'
DELIMITER ','
CSV GZIP QUOTE AS '"'
IGNOREHEADER 1;

/** -------------------------*/
/** for codezine.customer */
/** -------------------------*/
DELETE FROM codezine.customer;
COMMIT;
VACUUM codezine.customer;
SELECT COUNT(*) FROM codezine.customer;

COPY codezine.customer
FROM 's3://codezine-redshift-tokyo/codezine_customer-utf8.csv.gz'
CREDENTIALS 'aws_access_key_id=(アクセスキーID);aws_secret_access_key=(シークレットアクセスキー)'
DELIMITER ','
CSV GZIP QUOTE AS '"'
IGNOREHEADER 1;

/** ---------------------------*/
/** for codezine.prefecture */
/** ---------------------------*/
DELETE FROM codezine.prefecture;
COMMIT;
VACUUM codezine.prefecture;
SELECT COUNT(*) FROM codezine.prefecture;

COPY codezine.prefecture
FROM 's3://codezine-redshift-tokyo/codezine_prefecture-utf8.csv.gz'
CREDENTIALS 'aws_access_key_id=(アクセスキーID);aws_secret_access_key=(シークレットアクセスキー)'
DELIMITER ','
CSV GZIP QUOTE AS '"'
IGNOREHEADER 1;

/** -----------------------*/
/** for codezine.targets */
/** -----------------------*/
DELETE FROM codezine.targets;
COMMIT;
VACUUM codezine.targets;
SELECT COUNT(*) FROM codezine.targets;

COPY codezine.targets
FROM 's3://codezine-redshift-tokyo/codezine_targets-utf8.csv.gz'
CREDENTIALS 'aws_access_key_id=(アクセスキーID);aws_secret_access_key=(シークレットアクセスキー)'
DELIMITER ','
CSV GZIP QUOTE AS '"'
IGNOREHEADER 1;

 ちなみに、COPY時に何らかの異常によりエラーが発生し、以下のようなエラーメッセージが出る場合があります。

YYYY-MM-DD HH:MM:SS] [XX000] ERROR: Load into table 'targets' failed.  Check 'stl_load_errors' system table for details.

 この場合は、メッセージにあるように『stl_load_errors』テーブルの内容を確認してみましょう。直近のレコードにそのエラー内容を確認することができます。

SELECT * FROM stl_load_errors ORDER BY starttime DESC LIMIT 10;

 今回の作業手順を解説する上で実際発生した内容をサンプルとして上げてみます。このような形でCOPY処理時には意図しない・予期してしないデータが入ってくることが(取り込みの初期段階では)往々にしてありえます。ファイルごとの(この項目はこうすべき、という)ノウハウが蓄積されるまでは、しばらくトライアンドエラーで情報収集・問題解消を行っていくことは必要です。

ケース(1)
filename: s3://codezine-redshift-tokyo/codezine_orders-utf8.csv
line_number: 2
colname: sales
raw_field_name: 15,261.00
err_code:1207
err_reason:Invalid digit, Value ',', Pos 3, Type: Long
→ 『,』が無効な文字として認識された。
→ データの中に含まれているのはNGなのでファイル作成時点でカンマが入らない様に修正。
ケース(2)
filename: s3://codezine-redshift-tokyo/codezine_targets-utf8.csv
line_number: 7
colname:product_sub_category
err_reason: String length exceeds DDL length
→ データの文字列長がテーブル定義の項目列長を超えてしまっている。
→ こちらは逆にデータが正のパターン。データが入るサイズにテーブルの所定項目のサイズを拡大して対応。

 データのCOPY処理が5テーブル分、完了しました。

# SELECT COUNT(*) FROM codezine.orders;
 count 
-------
  8369
(1 row)

# SELECT COUNT(*) FROM codezine.customer;
 count 
-------
   795
(1 row)

# SELECT COUNT(*) FROM codezine.inventory;
 count 
-------
  8199
(1 row)

# SELECT COUNT(*) FROM codezine.prefecture;
 count 
-------
    47
(1 row)

# SELECT COUNT(*) FROM codezine.targets;
 count 
-------
    17
(1 row)

 作成したスキーマおよびテーブルについて、所定のグループやユーザーからのアクセス制御を定める必要があります。あるグループやユーザーに対してはフルアクセス権限・フル操作権限を与えたいが、別のグループやユーザーに対してはアクセスさせたくない、または参照(SELECT)権限のみ与えたい、というようなケースです。環境に応じてさまざまな状況が考えられますので、詳細については以下のブログエントリを参照してください。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:Amazon Redshiftによるビッグデータ分析環境の構築

著者プロフィール

  • しんや(シンヤ)

    2010年末~2013年前半位までの期間で興味のある勉強会に頻繁に参加。参加してきた勉強会のレポートブログとTogetterをひたすらまとめ続け、まとめ職人(自称/他称含む)として暫く過ごしておりました。色々な縁あってDevelopers Summit 2013では『公募レポーター』も務めました。...

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5