DWH（データウェアハウス）つくってみた、くらべてみた株式会社ビデオマーケット 瀬上 祐匡2019/1/26PHPカンファレンス仙台2019
View Slide
瀬上 祐匡（せのうえ ひろまさ）株式会社ビデオマーケットサーバサイドエンジニア現在、GAE/Go で、サービス開発をおこなってます。直近は、AppleTVのサーバサイド、バックオフィスシステムの構築・改修（Laravel）仙台市出身ビデオマーケット入社3年目。前職は、受託開発＋自社サービスの開発・運営（LAPP+cakePHP）好きなサービスは、Cloud Function茂庭台中学校のPTA会長やってますモノノフです。ハリネズミ飼ってます Twitter @senoue Facebook senoue.hiromasa自己紹介
株式会社ビデオマーケット2005年6月 フィーチャーフォン向け、Video On Demand（VOD）事業を開始2011年8月 スマートフォン向け開始2014年6月 PC向け配信開始2016年3月 仙台オフィス開設2017年8月 配信作品本数 20万本達成海外・国内ドラマ、アニメ、バラエティ番組など2０万本以上を配信最新作品ほぼあります。自社サービス以外にも、music.jp、dmm.com とも業務連携中沿革
DHW構築にあたってつくってみたくらべてみた
DHWってなに？データウェアハウスとは、直訳すれば「データの倉庫」である。利用者により定義範囲は異なるが、一般に時系列に整理された大量の統合業務データ、もしくはその管理システムを指す。参考：Wikipedia自分の理解BIを利用するのに、分析情報（顧客情報、購買履歴）などを適切な形で、運用に影響の出ないようにデータを貯めておくもので、取り出しやすいように、データの整理をしておけば、どんな形でもOKデータレイク、データマートなるものもある？単純にコピーするだけでなく、データの整形なんかも必要そう
データベースの把握会員情報：数百万件・・・ログインID 利用履歴：数千万件・・・操作ログ購入情報：数千万件作品情報：数十万件データベースは、負荷分散のため、3つに分かれています。毎日DWHに差分を、保存する必要がある
くらべるものGCPAWS
なにで作る？1.世の中的にPythonがいいらしいから、Pythonを使おう2.管理画面とか作りたくないから、Jupyter Notebook でごまかそう3.GCPとかいろいろ使ってみたいな4.あとでAIとかつかってごにょごにょしたいなさて、方向性は決まったので、どうやって実現するかを、メンバーと考えました。
最初に考えたアーキテクチャ
まず検証環境構築コンテナ環境：mariadb-columnstore-docker/columnstore-jupyter$docker-compose up -dhttp://localhost:88885分で完成しました。コンテナ素晴らしいですね でしょ
作って動かしてみたpandas,matplotlib, google.cloud, boto3のパッケージを利用。Jupyter なので、簡単に作成できます。テーブルは、３つに分散されているので、CSVは３ファイルづつ作成されます。
データレイクの確認データレイク（S3 or GCP）にCSVファイルで保存されました。これを毎日、動かせば、差分のデータ作成は終了です。Pandasで、CSVの作成もラクラクです。※3行で出来ちゃいます。でしょパンダ最高
ETLの検証Extract/Transform/Load（略称：ETL）とは、データウェアハウスにおける以下のような工程を指す。Extract - 外部の情報源からデータを抽出Transform - 抽出したデータをビジネスでの必要に応じて変換・加工Load - 最終的ターゲット（すなわちデータウェアハウス）に変換・加工済みのデータをロード参考：WekipediaAWS・・・Athena、GlueGCP・・・Dataflowこちらも早速使ってみましたが。高い・・・あっという間に、無料枠を使っちゃいました。しかも、使い勝手よくない！ということで・・・
ETLの設計というわけで、作りました。AWS・・・LambdaGCP・・・CloudFunctionこちらも、Pythonで作りましたS3もGCPも、イベント駆動が簡単にできます特定のディレクトリにファイルが入ったら、データをマージするように設計します※サンプルは、Lambdaです
定時バッチの仕様データの差分取り込み- upsert,margeを実行- 作品データなどは小さいので洗い替え- 毎日5:00に可動
フロー図
実際の計測結果CSV・・・自社DBから、CSVの作成StagingDB・・・TempDBへのCSVインサートUPSERT・・・TempDBから、MasterDBへのDELETE＆INSERT、もしくはマージデータの送信は、GCSが早い、マージはRedshift速いですね、GCPの3倍近く早い。AWS GCP AWS GCP AWS GCPCSV StagingDB UPSERT会員情報 14.00 4.22 5.87 35.41 6.30 18.09操作ログ 50.50 9.07 8.95 56.31 9.65 29.98購入履歴 30.24 7.80 8.47 46.18 8.98 22.18単位：秒
金額でくらべてみました【AWS】- Redshift・・・時間課金（ 0.314USD/時間）- Lambda・・・実行数課金（無料枠：1,000,000件リクエスト有- S3・・・データ量課金 （当初3年分保管予定）
金額でくらべてみました【GCP】- BigQuery・・・クエリからデータ取得料で金額が変わる、＊だと無駄になる。同じクエリならキャッシュされるので、比較的安価（ストレージ＋クエリ数）- Cloud Function・・・実行数課金（無料枠：200万回）- GCS・・・データ量＋データのアクセス頻度で課金（Nearline Storage：$0.01/GB）
金額でくらべてみましたService DWH ETL ストレージ合計条件 21GB/月 300万回 30GB/月AWS 25,094.0 22.2 2.8 25,119円GCP 1,509.0 44.4 33.3 1,586円GCP圧勝
最終的に現在の構成はこうなった最終的に、、、、AWSでリザーブドの環境を準備することになりました。- 定額である＋年払いするとやすかった- Tableauとの相性がBigQueryが悪かった- 他社実績なども考慮ということで、現在運用中です
できたこと□負荷低減[Load reduction] 単純作業なので、サーバへの負荷０kcal□コスト削減[Cost reduction] バッチを作りつつ、管理画面も作れた。 その上でベンチマークも取れる。現在も、４ヶ月になりますが、順調に動作しています。
できなかったこと■Notebook→pyへは、変換作業が必要 直接実行ファイルにすることはできなかった。■バッチのトリガーは結果、Shellにしてしまった cronで実行するにあたって、エラー処理も含め、結果shellになったこの辺は、今後の課題です。
結果的に何がよかった？仙台出身のしがないエンジニアですが、今回のようなデータ分析などの業務に携わることができています。自由な言語で開発をしてみましょう。趣味でもいいです、とりあえず、触ってみましょう。PHPを否定しているわけではないです。他の言語にふれることで、PHPの新たな発見もあります。
時代はデータドリブンである- データを見ることでわかることがたくさんある- 実際にやってみることで、新しいキヅキがある- 最初は小さなデータでもいいので、集めてみよう- 継続的に確認できるサービスにすべきである
ビデオマーケット宣伝本日、ビデオマーケットブースにて、VODサービスの利用状況の調査を行っています。お答え頂いた方にステッカーをプレゼントしていますので、ぜひお立ち寄りください。当社のVP of Engineering も会場におります「Googleアシスタントアプリ開発入門」という本もスタンプラリーでプレゼントしています。
ご清聴ありがとうございました。ビデオマーケット仙台オフィスでは、エンジニアの採用を行っております。新しいことにも積極的にチャレンジしています。ジョブボードも、掲載していますので、ぜひ、一度オフィスに遊びにいらっしゃってください。・コーポレートサイトhttps://www.videomarket.co.jp/recruit-form/・Greenhttps://www.green-japan.com/job/76539?case=login
senoue
cybozuinsideout
takesection
i35_267
hygradme
masasuzu
chiroito
toshi0383
bigwheel
joker1007
cimadai
wakamatsu_takumu
coconala_engineer
jcasabona
csswizardry
philhawksworth
keavy
davidbonilla
pauljervisheath
sferik
myddelton
kevinliebholz
tanoku
jonyablonski
paigeccino