2017.05.23

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

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 件
     
  • banner
  • banner

関連する記事