2017.05.23

Redshiftアダプタの使用方法とユースケース

  • このエントリーをはてなブックマークに追加
  • follow us in feedly
9月に入り肌寒くなってきましたね。
この夏に久しぶりにサッカーをしてバテバテだった製品開発部の大澤です。

サッカーの試合やサッカーアンテナサイトが好きで常にチェックしているのですが、
選手のプレイゾーンやホットゾーンをよく見かけることがあります。
これは選手が試合中フィールドのどこで最もプレーしているかを色の濃さで
表し濃いと多くその場所でプレーし、薄いとそこではあまりプレーしない
というデータを可視化し戦略に活かしているものです。
(パスやシュートの精度、スピードや走行距離なども記録しているようです!
 いわゆるデータスポーツですね!)

どうやってデータをとっているのかと疑問になり調べたところ、
主に3つのデータの取り方があるようです。

.人間の目で確認しデータを打ち込みデータ化する。
  (大変そう!!)
.専用のカメラで選手の動きを分析しデータ化する。
  (カメラが何台もあるみたいです!)
.選手に小さなセンサーチップを装着してデータ化する。
    (チップはレガースや首のあたりにいれるようです。逐一サーバにデータが送られる!IoT!)

上記のような方法で取得したデータをデータベースへ保存後
BIツールでデータを分析し、可視化しているようです。

上記ケースのように3通りのデータの取り方で行っていて
さらに保存されたデータのフォーマットや保存先が違う場合に
同じグラフ上で可視化をしたい場合、ひと手間必要です。

もう少し業務よりですと、各支店のデータベースやExcelファイルやCSVファイルを
同じグラフでみたい!でもデータが細かいとこでバラバラ。。。
このままだとグラフの精度が落ちてしまう!!
そんなときEAIツールを使用して、コーディングなしでそれぞれの保存先からデータを吸い上げ、
コーディングなしでフォーマットを整えてひとつのデータベースにまとめることできるのはEAIツールの強みです。

前置きが長くなってしましたが今回はデータ分析に適するDWH(データウェアハウス)サービス Amazon RedshiftへSkyOnDemandからのデータロードを行なうことができるAmazon Redshiftアダプタを紹介します。
このアダプタはAmazon RedshiftへのCOPYコマンドを自由に実行することが可能です。

自由って具体的にどういうコマンド!?
ということで今回はデータロード時に基本の構文とよく使うオプションをご紹介します。


COPYコマンド紹介
 (データソースはS3を前提)

1.基本構文

基本的な構文は以下になります。

S3のCSVファイルをRedshiftにCOPYを行なうコマンドとなります。

COPY table_name
FROM 's3://aws-bucket/testdata.csv'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
CSV;

■行ごとに解説します。
COPYコマンドを使用しますので COPYとその後ろに格納するテーブル名を指定します。

FROM 行では対象のデータソースを指定します。例ではS3のCSVファイルを指定しています。
  データソースのCSVファイルの順序は指定したテーブルの定義と列を合わせる必要があります。
FROM の後はオプションを指定します。
 オプションパラメータの中で必須なCREDENTIALSを指定します。
  FROMで指定したデータソースを読み取るためのクレデンシャル情報を入力します。

 ・アクセスキー
 ・シークレットアクセスキー
 ・トークン(オプション)

データソースがCSVファイルのため、CSVオプションを指定することで
  CSV形式で読み取ります。


2.よく使うオプション

コマンド
説明
記述例
MANIFEST
FROMにマニフェストファイルを指定します。
データファイルやソースを直接指定するとは別にマニフェストファイルを指定し、
マニフェストファイルに記載されている複数のデータソースを指定することが可能です。

マニフェストファイル例

{
	"entries": [
		{"url":"s3://bucket-usa/datas/data.1","mandatory":true},
		{"url":"s3://bucket-japan/datas/data.1","mandatory":true},
		{"url":"s3://bucket-japan/datas/data.2","mandatory":false}
	]
}


JSON形式でデータソースのURLを複数指定します。
データソースが見つからない場合処理を終了させたい場合オプションのmandatoryフラグをtrueに指定します。
デフォルトはfalseです。

COPY table_name
FROM 's3://aws-bucket/manifest/copy_from_s3_manifest_file'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
MANIFEST;
JSON
データソースにJSONファイルの場合指定します。
また JSONPathファイルを指定します。
これはテーブルとデータソースの紐付け定義ファイルとなります。

JSONデータファイル例
{
	"id": 0001,
	"name": [ "テラ", "スカオ"],
	"age": 25
}
{
	"id": 0002,
	"name": [ "鈴木", "花子"],
	"age": 26
}
{
	"id": 0003,
	"name": [ "田中", "太郎"],
	"age": 27
}

JSONPathファイル例
{
	"jsonpaths": [
		$['id'],
		$['name'][0],
		$['name'][1],
		$['age']
	]
}

COPY table_name
FROM 's3://aws-bucket/file.json'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
JSON 's3://aws-bucket/mapping/jasonpath.json';
FIXEDWIDTH
データソースが固定長ファイルの場合の固定長の定義を指定します。
名:長さ
列A:10、列B:100
COPY table_name
FROM 's3://aws-bucket/file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
FIXEDWIDTH 'id:3,name:25,city:12,state:2,seats:6';
DELIMITER
データソースのデリミタ文字(区切り文字)を指定します。

COPY table_name
FROM 's3://aws-bucket/file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
DELIMITER '|';

REGION データソースがRedshiftと別のリージョンにある場合リージョンを指定します。
COPY table_name
FROM 's3://aws-bucket/file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
REGION 'us-east-1';
ENCRYPTED
データソースが暗号化されている場合に指定します。
暗号化のマスターキーはクレデンシャルで指定します。
COPY table_name
FROM 's3://aws-bucket/symmetricdata.csv'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY;master_symmetric_key=ZZZ'
ENCRYPTED;
ENCODING
データソースのエンコードを指定します。
デフォルトはUTF-8です。
COPY table_name
FROM 's3://aws-bucket/utf-16_file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
ENCODING 'utf-16';
GZIP またはLZOP
データソースがgzipファイルまたはlzopファイルをロード時に解凍します。
COPY table_name
FROM 's3://aws-bucket/file.gzip'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
GZIP;
MAXERROR
データソースからロード時エラーとなる件数を指定します。
エラーデータがあっても指定した数よりエラーレコード数が少ない場合は正常終了となります。
デフォルトは0です。
※記述例の場合は9件以下の場合は正常終了となります。
COPY table_name
FROM 's3://aws-bucket/file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
MAXERROR 10;
DATEFORMAT またはTIMEFORMAT
データソースのDATE型、TIMESTAMP 型データのフォーマットを指定します。
COPY table_name
FROM 's3://aws-bucket/date.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
DATEFORMAT 'YYYY-MM-DD';
IGNOREHEADER 指定された行数をヘッダーとして扱い、読み取り対象外とします。
COPY table_name
FROM 's3://aws-bucket/file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
IGNOREHEADER 1;
IGNOREBLANKLINES データソースの空行は読み取りません。
COPY table_name
FROM 's3://aws-bucket/file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
IGNOREBLANKLINES;
NOLOAD データソースのロードは行わずに、データソースの検証を行ないます。
※データを検証したい場合にMAXERRORオプションと組み合わせて使うことで
 効率よくデータチェックが行えます。
 すべてデータが正常になってからロードが行うことができます。
COPY table_name
FROM 's3://aws-bucket/file.txt'
NOLOAD
MAXERROR 100;
COMPUPDATE 自動圧縮エンコードを自動的に適用するかどうかを制御します。
デフォルトはONです。
COPY table_name
FROM 's3://aws-bucket/file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
COMPUPDATE ON;
STATUPDATE
テーブルの統計を最新のものにします。
データロードごとに統計を最新のものにすることが推奨されています。
COPY table_name
FROM 's3://aws-bucket/file.txt'
CREDENTIALS 'aws_access_key_id=XXX;aws_secret_access_key=YYY'
STATUPDATE ON;

COPYコマンドの詳細は公式リファレンス を参照ください。
Amazon Redshift ドキュメント-開発者ガイド(日本語)- COPY


3.エラー時の対応について

Amazon Redshiftへのデータロード時の桁数あふれまたはデータ型エラーとなった場合は通常であれば、
専用のテーブルにエラーが出力されていないかいちいち確認しないといけません。

Amazon Redshiftアダプタなら処理後エラーデータを取得しますので、
エラー時の対応も簡潔に作成可能です。

※例 エラーデータがある場合メールにて原因をお知らせ


まとめ

それぞれの拠点にありフォーマットが整っていないひと手間必要なデータをレポーティングしたい場合
SkyOnDemandで多方向(接続先)のデータ、多フォーマットなデータを収集、フォーマットを整える機能と
今回ご紹介したCOPYコマンドを組み合わせてつかうことでレポーティング業務が大幅に捗ることが見込めます。

レポーティングといえば、Salesforceのデータ分析プラットフォーム「Salesforce Wave Analytics」が話題ですよね!
日本での正式サポートも待ち遠しいですね。

次回もご期待ください!!

1 件

関連する記事