dwh アプライアンスから...2019/09/24 · query5...
TRANSCRIPT
© Copyright(c) ORBIS, Inc. All rights reserved.
DWH アプライアンスからAmazon Redshift へ~PoCでわかったこと~
2019年9月24日
オルビス株式会社
ICT戦略部
佐々木 哲哉
© Copyright(c) ORBIS, Inc. All rights reserved.2
0-0. 目次
1.自己紹介2.会社紹介
~本編~3.オルビスの情報系システムの歴史4.PoC事例の紹介5.今後の展望
© Copyright(c) ORBIS, Inc. All rights reserved.3
1.自己紹介
© Copyright(c) ORBIS, Inc. All rights reserved.4
1-1. 自己紹介
名前: 佐々木哲哉
所属: オルビス株式会社
ICT戦略部ICTシステム開発グループグループマネージャー
キャリア:開発ベンダーから転職して10年システム開発・インフラ構築セキュリティ対応、内部統制など企業のIT部門の役割を一通り
© Copyright(c) ORBIS, Inc. All rights reserved.5
2.会社紹介
© Copyright(c) ORBIS, Inc. All rights reserved.6
2-1. 会社概要
設立 1984年6月
従業員数 1,302人 (2018年12月末時点)
事業内容 化粧品、栄養補助食品、ボディウェアの企画・開発および通信販売・店舗販売
連結売上高 510億円
© Copyright(c) ORBIS, Inc. All rights reserved.7
2-2. 会社概要
「オルビスユー」
2018/10/23 発売
主力商品はスキンケア
© Copyright(c) ORBIS, Inc. All rights reserved.8
2-3. 会社概要
主力商品はスキンケア
© Copyright(c) ORBIS, Inc. All rights reserved.9
2-4. 会社概要
売上の半分以上がECで、
約3分の1がスマホ。
スマホと親和性が
高い事業体。PC
その他
通販
店舗
チャネル構成比
スマホ
(EC)
スマホ
(アプリ)
販売チャネルの構成比
© Copyright(c) ORBIS, Inc. All rights reserved.10
2-5. 会社概要
リアルの店舗も展開
このような世界観の店舗を運営。
全国115店舗。
© Copyright(c) ORBIS, Inc. All rights reserved.11
2-6. 会社概要
オルビスブランドの特徴~ECサイト~
業界に先駆けて1999年よりECサイトを開始
© Copyright(c) ORBIS, Inc. All rights reserved.12
2-7. 会社概要
オルビスブランドの特徴~スマフォアプリ~
© Copyright(c) ORBIS, Inc. All rights reserved.13
3.オルビスの情報系システムの歴史
© Copyright(c) ORBIS, Inc. All rights reserved.14
3-1. オルビスの情報系システムの歴史
2009年~2012年
カタログやDMのセグメントの効率性を重視
セグメント抽出に特化した仕組み
情報系・DWHの役割
抽出用、RFM用の数個のデータマートのみ
非定形分析も、一部の部門で対応
© Copyright(c) ORBIS, Inc. All rights reserved.15
3-2. オルビスの情報系システムの歴史
2012年~2018年
1to1マーケティングの加速化
顧客コミュニケーションの発射台の役割
情報系・DWHの役割
分析力の向上 ⇛ SASの導入(全社に分析を開放)
性能(スピード向上) ⇛ DWHアプライアンス参考)https://www.niandc.co.jp/news/20121030/
© Copyright(c) ORBIS, Inc. All rights reserved.16
3-3. オルビスの情報系システムの歴史
2019年~
データドリブンマーケティングを強化
過去分析から未来予測へ。よりパーソナライズ
情報系・DWHに求められること
増加するデータ量 & 圧倒的なスピード
社外のデータ・サービスとの連携の容易さ
背景) 既存の情報系システムのEOSL
© Copyright(c) ORBIS, Inc. All rights reserved.17
3-4. PoC事例の紹介
その要件を満たすのが、クラウド型DWH
Amazon Redshift
"PoC"を実施!
© Copyright(c) ORBIS, Inc. All rights reserved.18
4.PoC事例の紹介
© Copyright(c) ORBIS, Inc. All rights reserved.19
4-1. PoC実施観点
"PoC"を実施!
【PoC実施観点】
①性能
評価軸
②移行性
③業務継続性
オンプレと同等以上のパフォーマンスが出ることを確認・データベースとして基本性能を評価(select/insertなど)・並行処理性能を評価
様々な移行ツールがある。もっと容易にできるのでは?・業務アプリケーションの移行方式の確認と精緻化
分析資産の継続活用は必須。業務影響度をはかる・SAS製品の基本動作の担保・クラウド型DBの制約に伴う業務制約の発見と対処
検証内容・考慮点
弊社環境や利用方法の依存度が高いので割愛
© Copyright(c) ORBIS, Inc. All rights reserved.20
4-2. PoC実施の対象
"PoC"を実施!
【評価対象DB】
PureData
A.現行DWH B.Redshift
IIAS
C.他社DWH
オンプレ AWS 他社クラウド
参考
© Copyright(c) ORBIS, Inc. All rights reserved.21
4-3. PoC実施の環境
"PoC"を実施!
【検証環境】
ORBIS環境オフィス AWS
既存DWH (nzsql)
Postgres CLI
(psql)
Redshift
既存DWH
Universal One
EC2 SASサーバ
EC2踏み台Srv
EC2 SASクライアント
EG/DI/CI
SAS
RDP
インスタンスタイプ:dc2.8xlargeクラスター数:2
© Copyright(c) ORBIS, Inc. All rights reserved.22
4-4. PoC:①性能検証
性能検証 ・・・ オンプレ同等以上の性能が出るのか?1
Ⅰ.基本性能検証
データサイズが大、中、小のそれぞれのテーブルに対し、クエリを発行したレスポンス
タイムを取得した結果を検証
Ⅱ.平行処理性能多重分のDBプロセスを起動し、直積SQLなど高負荷クエリを実行した状態で測
定対象のSQLを実行し、その処理時間を取得しています。
Ⅲ.バッチ処理性能現行のバッチ処理のから特定のバッチ処理を選定し、処理時間の差を測定
© Copyright(c) ORBIS, Inc. All rights reserved.23
4-4. PoC結果:①性能検証
性能検証 ・・・ オンプレ同等以上の性能が出るのか?1
Ⅰ.基本性能検証クエリ①:select count(*) from D_T_I_0003_ORD_DM_M; 約 5億件の集計(1GByte以上)クエリ②:select count(*) from D_M_I_0007_LATEST_CST; 約 900万件の集計(100Mbyte)クエリ③:select count(*) from D_M_F_0011_PRD_BASIC; 約 3万件の集計(極小テーブル)
単位:秒
パターン DBMS 参考)初回実行 実行1回目 実行2回目 実行3回目 平均
クエリ① A.既存DWH 3.5730 2.8970 3.3830 3.2610 3.1803
B.Redshift 2.2657 0.1105 0.1115 0.0974 0.1065
C.他社DWH 0.0380 0.0270 0.0210 0.0287
クエリ② A.既存DWH 0.1030 0.0790 0.1980 0.1140 0.1303
B.Redshift 1.2395 0.0095 0.0089 0.0088 0.0090
C.他社DWH 0.0210 0.0200 0.0190 0.0200
クエリ③ A.既存DWH 0.1620 0.0530 0.0960 0.0290 0.0593
B.Redshift 1.2438 0.0084 0.0083 0.0083 0.0083
C.他社DWH 0.0200 0.0190 0.0190 0.0193
© Copyright(c) ORBIS, Inc. All rights reserved.24
4-4. PoC結果:①性能検証
性能検証 ・・・ オンプレ同等以上の性能が出るのか?1
Ⅰ.基本性能検証 結果①
現行DWHと比較して大幅スループットの向上が見込める。
データサイズが小さい場合は、体感の性能差はほぼない。
0.0 0.5 1.0 1.5 2.0 2.5 3.0 3.5
クエリ①
クエリ②
クエリ③ C.他社DWH B.Redshift A.既存DWH
単位:秒
© Copyright(c) ORBIS, Inc. All rights reserved.25
4-4. PoC結果:①性能検証
性能検証 ・・・ オンプレ同等以上の性能が出るのか?1
Ⅰ.基本性能検証 結果①
処理時間比率でみると処理対象のデータ容量が増加するにつれて
スループットが向上する傾向もある。
既存DWHを100秒とした場合のRedshift処理時間 (%=秒)
0.00%
2.00%
4.00%
6.00%
8.00%
10.00%
12.00%
14.00%
16.00%
クエリ① クエリ② クエリ③
既存DWHとの速度比
Redshift(未チューニング)
© Copyright(c) ORBIS, Inc. All rights reserved.26
4-4. PoC結果:①性能検証
性能検証 ・・・ オンプレ同等以上の性能が出るのか?1
Ⅱ.並行処理性能検証 結果①
Redshiftは、大容量データかつ多重度の高い処理は他社DWHに優位性はある
0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
単一 2多重 5多重 単一 2多重 5多重 単一 2多重 5多重
B.Redshift C.他社DWH
クエリ① クエリ② クエリ③
単位:秒
© Copyright(c) ORBIS, Inc. All rights reserved.27
4-4. PoC結果:①性能検証
性能検証 ・・・ オンプレ同等以上の性能が出るのか?1
Ⅲ.バッチ処理性能検証
既存DWHより処理時間が、約1/3の処理時間に。高速化が見込める。
データ量の多いデータロード処理は大幅な改善が見込まれる。単位:秒
Job Job-Node ノード名 Output件数 A.既存DWH B.Redshift C.他社DWH
Job① バッチ計 01:36.3 00:29.4 00:44.2
Query1 媒体名漢字取得処理 22,547 00:00.7 00:00.6 00:00.2
Query2 プロモコード、媒体分類区分取得処理 8,087,835 00:54.2 00:02.3 00:06.9
Query3 統合処理 8,087,374 00:13.6 00:07.5 00:14.3
Query4 顧客状態トランの時点抽出・DM不可能フラグ付与 356,187 00:01.3 00:00.4 00:00.4
Query5 顧客状態トランの時点抽出・督促ランク付与 133,355 00:00.9 00:00.4 00:00.2
Query6 EC会員マスタの時点抽出・ネット顧客フラグ付与 4,765,180 00:01.0 00:06.0 00:01.3
Query7 年齢、DM可能、ネット顧客区分処理 8,087,374 00:07.1 00:08.5 00:14.2
Query8 テーブルローダー 8,087,374 00:11.6 00:09.1 00:06.6
Job② バッチ計 25:12.0 03:37.8 05:46.2
Query1 商品、分類の購入金額・数量・単価取得処理 491,744,570 04:58.8 02:19.6 03:22.1
Query2 テーブルローダー 491,744,570 20:11.0 01:18.2 02:24.1
Job③ バッチ計 00:07.9 00:06.8 00:16.3
Bulkload バルクロード 25,203 00:06.9 00:06.8 00:16.3
© Copyright(c) ORBIS, Inc. All rights reserved.28
4-4. PoC結果:①性能検証
性能検証 ・・・ オンプレ同等以上の性能が出るのか?1
Ⅳ.データロード処理性能検証
データの書き出し(バルクアンロード)の性能差は少ない。
データの読み込み(バルクロード)は性能改善は大きく、データ量が大きいほど効果大。
検証データ:
No
1
2 およそ5.5GByte
およそ1.0GByteD_M_F_0042_CST_STS
D_M_I_0007_LATEST_CST
データ サイズ(非圧縮サイズ) 件数
3,370,000
8,125,360
■バルクアンロード(DB -> SAS)
■バルクロード(SAS -> DB)
単位:秒
単位:秒
No データ A.既存DWH B.Redshift C.他社DWH
1 D_M_F_0042_CST_STS 00:17.6 00:21.0 00:27.5
2 D_M_I_0007_LATEST_CST 01:27.8 01:52.8 04:03.5
No データ A.既存DWH B.Redshift C.他社DWH
1 D_M_F_0042_CST_STS 02:37.7 00:28.5 01:31.6
2 D_M_I_0007_LATEST_CST 19:08.4 01:47.0 08:51.2
© Copyright(c) ORBIS, Inc. All rights reserved.29
4-5. PoC結果:②移行性検証
Ⅰ.PoC以前で想定していたPG修正
PoC以前から想定したいPG修正について、影響範囲や移行方法を検証
Ⅱ.PoCで検知したPG修正PoCの中で新たに剣士したPG修正が必要な観点を整理
Ⅲ.移行ツール検証AWS社提供の移行ツールの移行の有用性を検証
移行性検証 ・・・ 移行費用は妥当か、もっと容易にできる方法は?2
© Copyright(c) ORBIS, Inc. All rights reserved.30
4-5. PoC結果:②移行性検証
移行性検証 ・・・ 移行費用は妥当か、もっと容易にできる方法は?2
Ⅰ.PoC以前で想定していたPG修正
PoC以前より検知していた修正箇所は、改修規模が縮小(見積もり精度が向上)
概要 修正内容プログラム修正本数
当初想定 B.Redshift C.他社DWH
(当初想定)
Bulkloadオプションの変更Bulkloadのオプション設定を既存DWHから
Redshiftに変更。 117 183(+66)
183(+66)
(当初想定)中間テーブル分散キー指定オプションの変更
中間Redshiftテーブル作成時に指定する分散キーの指定方法の変更。 341 178
(-163)178
(-163)
(当初想定)既存DWH関数のRedshift関数への置換
既存DWH関数をRedshift文法への置き換え、もしくはUDFへの置き換え。例)substr⇒substring
341 116(-225)
509(+168)
© Copyright(c) ORBIS, Inc. All rights reserved.31
4-5. PoC結果:②移行性検証
移行性検証 ・・・ 移行費用は妥当か、もっと容易にできる方法は?2
概要 修正内容プログラム修正本数
当初想定 B.Redshift C.他社DWH
Time型⇒TimeStamp型への変更
RedshiftではTime型の概念が存在しない。Time型を扱うデータを日付と組合わせてTimeStamp型へ変換する処理が必要
0 23 0
中間テーブル名の変更 Redshiftでは大文字テーブルの作成ができず、DI処理にてエラーとなるケースの回避。 0 201 0
UDFの作成 Encryptなど既存DHW関数でRedshiftで保持していない関数をUDFとして作成し置き換えを実施。 0 14 0
Timezoneの指定
RedshiftではTimezoneの指定が不可。
DB側で現在処理日を指定し、抽出、加工処理を行っている場所について日本標準時間への置き換えの対応。
0 7 7
Ⅱ.PoCで検知したPG修正
当初想定にはなかった影響範囲が見つかっている。
© Copyright(c) ORBIS, Inc. All rights reserved.32
4-5. PoC結果:②移行性検証
移行性検証 ・・・ 移行費用は妥当か、もっと容易にできる方法は?2
Ⅲ.移行ツール:Amazon SCT(Schema Conversion Tool)レポート
テーブルの移行は100%移行が可能。
ただし、前述の通りTime型などのカラム単位では対応が必要との結果。
100%
100%
© Copyright(c) ORBIS, Inc. All rights reserved.33
4-5. PoC結果:②移行性検証
移行性検証 ・・・ 移行費用は妥当か、もっと容易にできる方法は?2
Ⅲ.移行ツール:Amazon SCT(Schema Conversion Tool)レポート
ビューオブジェクトは、7割で修正が必要。関数はすべて「中程度の改修」が必要。
77%
© Copyright(c) ORBIS, Inc. All rights reserved.34
4-5. PoC結果:②移行性検証
移行性検証 Appendix Snowball利用レポート2
DC
既存DWH
受け取り・設置初期設定ラック退避
発送手配・発送
ベンダー オフィス
AWS
Amazon Snowball
NFS
データ移行(リモート)
受取/集荷待機
③現地入館(Day1)④受取
①DC事前作業・IP払い出し・L2スイッチのVLAN設定・DC手続き(入館、持込/持出し)
②Snowballリクエスト⑤設置⑥初期設定/NFSマウント/接続確認⑦データ移行
⑦データ移行よりリモート操作⑧筐体退避
⑨集荷手配
⑩現地入館(Day2)
⑪データ移行Buffer⑫筐体集荷準備
一日目作業
二日目作業
事前作業
⑪データ移行Buffer
⑬集荷立会⑭集荷
本番移行時の作業イメージ実手順の確認と想定時間を計測。
© Copyright(c) ORBIS, Inc. All rights reserved.35
4-6. PoC結果のまとめ
①性能
評価軸(課題)
②移行性
性能面での懸念はないこと確認。Redshift/他社DWHも疑うくらいの性能向上が見込める!
DBの差は大きくない。移行ツールは期待できない。システムの作りの依存度が大きい。PoCで移行難易度・範囲を確認⇒計画を精緻化
PoC結果評価
RedShiftの導入を決定(移行コストは微減)
③業務継続性
※他社DWHは各観点でも期待以上であったが、コスト面でかなわず・・・
© Copyright(c) ORBIS, Inc. All rights reserved.36
5.今後の展望
© Copyright(c) ORBIS, Inc. All rights reserved.37
5-1. 今後の展望
Data Source DataLake DWH MA
Rawデータ配置領域
統計分析・機械学習
AWS Glue
・・・
分析用加工データ
配置領域
AmazonRedshift Spectrum
ETL/EAI
AmazonQuickSight
AmazonMachineLearning
Data Spider
Asteria
AmazonSageMaker
クエリSASEnterpriseMiner
GCP/Azure Looker
データベース
ジオマーケ
メディア
SNSテキスト
過去分析 から 未来予測へ ➡ パーソナライズをより強化
SAS高速化
スコアリング
自社以外のデータ活用と顧客のスコアリングを内製化していく
© Copyright(c) ORBIS, Inc. All rights reserved.38
E.O.F