レガシーシステム Db2 for i の大規模データをGoogle BigQuery に連携したケースにおけるELT処理ポイント解説 #dbts2024

最近弊社にはレガシーな環境下のデータをGoogle BigQuery などのクラウドDWH に連携し、よりスピーディかつアドホックなデータ分析・ビジネス上の意思決定につなげたい、という相談がよく寄せられます。
しかしながら、単純にデータを持っていく、というアプローチにおいても大規模データの場合、アーキテクチャやそれぞれのAPI の性質を理解し考慮しなければパフォーマンスや運用面などで課題が発生してしまいます。
今回のセッションでは実際にDb2 for i に格納された大規模データをクラウドDWH・BigQuery に連携したケースを元に、データレプリケーションの勘所、それぞれのインターフェースやアーキテクチャの考慮ポイントをDb2 for i のプロフェッショナルであるベルデータ様と一緒に解説します。

CData Sync
https://www.cdata.com/jp/sync/

Transcript

1. The real-time data connectivity company. レガシーシステム Db2 for i の大規模データを Google BigQuery に連携したケースにおけるELT処理ポイント解説 db tech showcase 2024 A5
2. 2 About Speaker 杉本 和也 CData Software Japan 合同会社 パートナーサクセスチーム リードエンジニア 2017年からCData Japan のリードエンジニアとしてテ クニカルサポートをメインに国内SaaS API 向けの Driver 開発・プリセールスを担当し、現在はパート ナーサクセスチームでパートナー企業との協業体制の 構築および各サービスのビジネスデベロップメントを リード。 コミュニティ活動・Blog 執筆にも力を入れており、 Microsoft MVP for Business Applications を2017年～ 2021年まで受賞。
3. 3 CData Syncとは © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE SaaSなどのアプリケーション, データベース, ファイルなどにあるデータをノーコードで別 のデータストアに複製（レプリケーション）出来るツール SaaSやオンプレミスアプリケーション, データ ベース, ファイルなど400(API Profile含む)を超 えるデータソース 主要なデータレイク・データ ベース・データウェアハウス データソース 同期先 BigQuery Cloud Storage
4. 4 About Speaker 安達 美知政 ベル・データ株式会社 アプリケーションビジネス本部 セールスプロフェッショナル 創業メンバーとしてITのスタートアップ企業の 立ち上げを経験後、ベル・データに参画。お客 様担当営業、新規事業部門に従事し、現在は、 業務効率改善のためのデジタル化やデータ利活 用をキーワードとしたソリューション企画・開 発、お客様へのプロモーションを担当。 提案活動やディスカッションを通じ、現場に根 付いた業務課題解決に日々奮闘中。
5. 5 ベル・データ株式会社 会社紹介 ベル・データは、蓄積してきた テクノロジーをもって中堅・中 小企業のビジネス上の課題に対 するソリューションを提供しま す。3000社を超えるお客様との お取引を通じて培われた技術力 やサポート力を基盤に、最高のI Tソリューションインテグレー ターが課題を解決し、企業の成 長を支援していきます。BELLグ ループのパーパスおよびプロミ スにもとづいた誠実な姿勢で事 業活動を展開していきます。 取扱実績
6. 本日のセッションのゴール
7. 7 レガシーシステム（Db2 for i）の大規模データをDWH に連携 分析基盤として活用したいという相談が増加中 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE
8. 8 ユースケース © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE • 業種：小売業 • 売上規模：1000億超 • 100店舗以上運営 • IBM i (AS/400) IBM i ベースのMD（マーチャンダイジング）システムを利用中 • レコード数は商品マスタ：10万件規模、売上：億オーダー （※特定のお客様ではなく、類似ケースとしてまとめています）
9. 9 データ分析基盤採用の背景 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE Db2 for i 用分析ツールを利 用していたが、データ数 増加に伴うパフォーマン スの悪化により、デイ リーレベルの分析レベル でも課題 経営やビジネスサイドが 情報システム部門にデー タをリクエストしてから 結果が得られるまでのス ピード感が遅く、意思決 定に寄与できていない Db2 for i 側のノウハウを 持っているメンバーが少 なくなり、保守・運用面 でも課題 BigQuery データ分析基盤としてBigQuery・セルフサービスBIツールを導入し アドホックかつスピーディな分析環境を実現
10. 10 レガシー → DWH 連携時にお客様からよく聞かれる質問 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE いろんなデータロード方法 があるけど、それぞれのメ リデメがわからない。どれ を選択するべき？ アーキテクチャはどうしよう？ 処理の主体はオンプレ側？ クラウド側？ もともと汎用機（COBOL）だった から、連携しにくい所があるのだ けど、どうしたらいい？
11. 11 データパイプラインのアプローチを間違えると 適切なパフォーマンス、分析結果が得られない © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE
12. 12 本日のセッションでお伝えしたいこと © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE レガシーからクラウドDWH 連携シナリオの勘所をおさえよう！ 以下の3点を意識して、データパイプラインを構築することがポイント レガシー側の インターフェース チューニング ネットワーク含めた アーキテクチャ DWH側の インターフェース チューニング
13. レガシー側の インターフェース・チューニングポイント
14. 14 インターフェースのサポートについて © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE ELTツールを選定する際に、「直接Db2 for iに接続できない」がたびたび問題に なる。 Db2 for iをサポートしていないツールだ と、別DBに同期して連携するか、フェデ レーションサーバーなどを通して透過的 にDb2 for i のデータを利用しないといけ なくなり、リアルタイム性が損なわれた り、高額な投資が必要に。
15. 15 日付データの連携チューニング（差分抽出） © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE # 売上データ連携の課題 売上データ件数が多く、毎日全てを持っていくのは厳しい（容 量＋処理時間）。前日分の売上のみを抽出し、BigQueryにロード することを検討 しかしDB2 for iの日付は数値型（10進数）で持っていることが多 い。多くのDBは日付型(yyyymmdd)。 そのため標準機能で差分抽出を行うことが困難に。数値型を日 付型に変換するビューを作成して実行してみたが、時間がかか り過ぎて実用性がなかった。 # 解決策 DB2のユーザー定義関数を作成し対応。 日付型は日付計算（today -1）が出来るので簡単。それにより、 カスタムクエリ上で設定が可能となり、前日分の売上データが 高パフォーマンスで抽出できるようになった。
16. 16 文字列カラムにおける固定長の圧縮 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE • 文字列の右側に空白の文字列がある。その ため、空白をデータとしてELT処理をして しまうと、無駄なデータを送信し、処理時 間やDWH側の容量が無駄に必要となって しまう。 • カスタムクエリでトリムすることで転送容 量を圧縮することができる。
17. DWH側の インターフェース・チューニングポイント
18. 18 BigQuery へのAPI連携・データ読み込みパターン © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE Category Approach Reference ジョブ型 ローカルファイルアップロード https://cloud.google.com/bigquery/docs/batc h-loading- data?hl=ja#loading_data_from_local_files Google Cloud Storage ロード https://cloud.google.com/bigquery/docs/batc h-loading-data?hl=ja#permissions-load- data-from-cloud-storage LOAD DATA クエリ https://cloud.google.com/bigquery/docs/refe rence/standard-sql/other- statements#load_data_statement クエリ型 INSERT・UPDATE・DELETE クエリ（DML） https://cloud.google.com/bigquery/docs/refe rence/standard-sql/dml-syntax ストリーミング 型 Streaming API https://cloud.google.com/bigquery/docs/stre aming-data-into-bigquery?hl=ja Storage Write API https://cloud.google.com/blog/ja/topics/dev elopers-practitioners/bigquery-write-api- explained-overview-write-api?hl=ja
19. 19 各連携方式の比較 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE 連携方式 ユースケース 再利用までの時間 コスト リクエスト ボリューム ジョブ型 大量データ連携向 け 同期ジョブが完了す るまで ロードには課金 なし JSONの場合 最大 100 MB ファイルなら 4GB クエリ型 1日数回のバッチ連 携 直接BigQueryの データを操作した いケース 時間はデータ量によ るが、連携リクエス トのレスポンスが 返ってきたら即OK 1TB まで無料 リクエストサ イズは最大 10 MB ストリーミング型 リアルタイム・ニ アリアルタイム連 携向け 最長90分 $0.050 per 1GB or $0.025 per 1GB 5万行まで
20. 20 ジョブ型 / 制約抜粋 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE 上限 デフォルト 注 1 日のテーブルあたりの読み込み ジョブ数 1,500 個のジョブ 読み込みジョブ（失敗した読み込みジョブを含む）は、 宛先テーブルでの 1 日あたりのテーブル オペレーショ ンの上限にカウントされます。標準テーブルとパー ティション分割テーブルの 1 日あたりのテーブル オペ レーション数の上限については、テーブルをご覧くだ さい。 1 日あたりの読み込みジョブ 100,000 ジョブ プロジェクトで 1 日あたり最大 100,000 個のコピー ジョブを実行できます。読み込みに失敗したジョブは、 この上限にカウントされます。 読み込みジョブあたりの最大サイズ 15 TB CSV、JSON、Avro、Parquet、ORC の各入力ファイルの 合計サイズは 15 TB 以下にする必要があります。 読み込みジョブの実行時間の上限 6 時間 6 時間を超えて実行されると、読み込みジョブは失敗 します。 CSV: 最大セルサイズ 100 MB CSV セルの最大サイズは 100 MB です。 CSV: 行の最大サイズ 100 MB CSV 行の最大サイズは 100 MB です。 CSV: 最大ファイルサイズ - 圧縮 4 GB 圧縮 CSV ファイルのサイズの上限は 4 GB です。 CSV: 最大ファイルサイズ - 非圧縮 5 TB 非圧縮 CSV ファイルのサイズの上限は 5 TB です。 1日のテーブルあたりの読み込み ジョブは1500が上限らしい 1分に1件ジョブを走らせるような 使い方をするとNG 1500 / 24 / 60 = 1.041666… https://cloud.google.com/bigquery/quotas?hl=ja#table_limits
21. 21 クエリ型 / 制約事項 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE INSERT DML 同時実行性 24 時間のうち、最初の 1500 個INSERTのス テートメントは、送信された直後に実行されま す。この制限に達すると、INSERTテーブルに 書き込むステートメントの同時実行数は 10 に 制限されます。追加の INSERTステートメント はPENDINGキューに追加されます。INSERTは いつでも、最大 100 個のステートメントをテー ブルに対してキューに入れることができます。 ステートメントが完了するとINSERT 、次の INSERTステートメントがキューから削除され て実行されます。 https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language#insert_dml_concurrency
22. 22 ストリーミング型 / 制約事項 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language#limitations （日本語訳抜粋） ストリーミング データがコピー 操作に使用できるようになるま でに最大 90 分かかる場合があり ます。 ストリーミングバッ ファのレコードに対 して更新をかけよう とするとエラーにな る
23. 23 主な採用アプローチ © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE Streaming API によるレコード追 加で対応。 データの更新は原則行わない DMLを個別に発行するよりも高 速・かつ大量データで対応可能に。 ジョブ型のUpload API を利用した ローカルファイルアップロード方 式を採用。 Db2 for i からは日次差分でデータ を取得。 ただし、データの種類によっては、 更新対象データも存在するため、 一時テーブルにデータを登録の上、 クエリ型を使ってマージステート メントで正式テーブルに反映。 日次バッチの大量データ 追加のみの ニアリアルタイム分析データ
24. アーキテクチャ
25. 25 オンプレミスデータ連携における 主なアーキテクチャ・アプローチ © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE ①GCP側主体 ELT BigQuery オンプレミス：IBM i (AS/400) ELT BigQuery オンプレミス：IBM i (AS/400) ELT BigQuery オンプレミス：IBM i (AS/400) Agent ③SaaS＆エージェント型 ②ホスティング型
26. 26 各アーキテクチャの比較 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE アーキテクチャ メリット デメリット GCP側主体 アーキテクチャ 既存のIBM i (AS/400) 側に手をい れる必要が最小限で済む オンプレミスへの接続のため に、 VPN環境を整える必要あり ネットワーク周りのコストが 別途必要 ホスティング型 アーキテクチャ 既存のIBM i (AS/400) サーバー資 産を活用でき、ネットワーク環境 は整備する必要がない IBM i (AS/400) 側の管理・運 用コストが追加で必要 SaaS＆エージェント型 アーキテクチャ ELTサーバーの運用コストが最小 限で済む エージェントとクラウド間、 クラウド間とDWH間でパ フォーマンスの劣化・エー ジェントの運用管理が必要
27. Db2 for i 連携におけるCData Sync 採用ポイント
28. 28 Db2 for i を標準サポート © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE • Db2 for i およびDb2 for LUW に対応 • DRDA ゲートウェイプロトコルバー ジョン5以降と互換性あり • 認証は、USRIDPWD、EUSERIDPWD、 IBMIAMAuth に対応 • SQL 92 標準のCRUD 対応 • データソース・同期先両方に対応可能
29. 29 BigQuery への柔軟な連携をサポート © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE 連携するデータの種類に応じて、 Streaming・DML・Upload・ GCSStaging から選択可能。 差分更新を行う場合は、自動的 に一時テーブルにアップロード した上で、MERGE ステートメン トでデータを反映させる処理に。
30. 30 GCP・オンプレミス等様々な環境下での利用が可能 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE CData Sync CData Sync オンプレミス：IBM i (AS/400) CData Sync はサーバーインストール型の提供のクラウド側に配置してVPN経由で オンプレミスに接続することも、オンプレミス側に配置して、基幹システムのデー タをBigQuery にレプリケーションするような環境構成も可能。 ホスティング先はWindows Server やLinux だけでなく、IBM i (AS/400) などにイ ンストールして利用することも可能。 BigQuery BigQuery GCE オンプレミス：IBM i (AS/400)
31. ベル・データが考えるDb2 連携のポイント
32. 32 ベル・データが考えるDb2 連携のポイント お客様に評価頂いた点 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE # ハード面 IBM i（PowerSystems）はDb2 for i とOSレベルで統合されているため、IO性能が 非常に高く、優秀。そのため読み取りのスループットが速く、データ分析の元とな るDBとしては有用性が高い。 # ソフト面 ベル・データが IBM i と分析基盤の両方に精通したベンダーでもあり、ベル・デー タでは、24時間365日の運用保守体制を有しており、万が一、同期エラー発生時の 再起動 実行など含めた運用のアウトソースで対応できる。 基幹システムであるIBM i は、日次バッチ処理後の早朝に最新データをDWHに連携 する形を想定。そのため、同期にエラーが発生した場合の対応が出勤移動時間帯に 重なり自社メンバーでの対応が難しく、分析業務が止まってしまうようなケースで もサポート可能。
33. 33 ベル・データが提供するIBM i サービスとは？ © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE • データの収集・蓄積、Web API活用 を含めたデータ連携基盤の構築、AI 型BIツールによるデータ活用などの DX推進支援 • お客様の運用業務の負荷を軽減する 24h365dの運用支援・アウトソーシ ングサービスの提供 • 基幹、周辺業務システムのアプリ ケーション開発や他社開発のシステ ムを含めたアプリケーション保守 サービスの提供 【データ連携支援サービスの一例】
34. まとめ
35. 35 © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE 企業のデータ収集及び活用の状況 DX時代のデータマネジメント大全 DX、データドリブン経営、データ利活用から理解する https://www.amazon.co.jp/gp/product/B0BWR4PTQP/ref=ppx_yo_dt_b_d_asin_title_351_o06?ie=UTF8&psc=1 日本がデータ収集、 データ蓄積、データ処 理、どの分野でも圧倒 的に進んでいない
36. 36 本日のセッションでお伝えしたいこと © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE レガシーからクラウドDWH 連携シナリオの勘所をおさえよう！ 以下の3点を意識して、データパイプラインを構築することがポイント レガシー側の インターフェース チューニング ネットワーク含めた アーキテクチャ DWH側の インターフェース チューニング
37. 37 セッションアンケートにご協力お願いします！ © 2024 CData Software Inc. — CONFIDENTIAL – DO NOT DUPLICATE OR DISTRIBUTE https://bit.ly/dbtech2024CData CData ブースではデータ分析アンケートも実施中！ ぜひお立ち寄りください。