Google Cloud BigQuery を含む SQL Server SSRS、SSIS パッケージ
Google Cloud Japan Team
※この投稿は米国時間 2021 年 9 月 16 日に、Google Cloud blog に投稿されたものの抄訳です。
ETL およびビジネス インテリジェンスのデベロッパーは、データ ウェアハウスを Google Cloud BigQuery に移行した後で、データ パイプライン、レポート、ダッシュボードのアップグレードと改良を依頼されることがよくあります。SQL Server Integration Services（SSIS）と SQL Server Reporting Services（SSRS）を使い慣れているデータチームは、これらのツールを BigQuery で引き続き利用できます。そのため、初期のデータ移行を完了した後に ETL パイプラインと BI プラットフォームをモダナイズできます。以下のブログ投稿では、データチームが SSIS と SSRS を BigQuery で活用するパターンと例を示します。
BigQuery での SQL Server Integration Services（SSIS）の使用
SQL Server Integration Services（SSIS）は、ソースとターゲットのデータベース間でデータを移動するために使用されます。以下ではよくあるシンプルなパターンを紹介します。このパターンでは、データが OLTP システム（ソース）から抽出され、データ ウェアハウス（ターゲット）に書き込まれます。データの変換とデータ マッピングという 2 つの手順が間にある点に注意してください。この手順には、具体的なビジネスルール、ロジック、C# コードが含まれています。この時点では、こうしたものは BigQuery に移行するよりも SSIS に保持するほうが理にかなっています。
SSIS のデータフロー
以下の改良された SSIS データフローでは、マルチキャスト変換が、csv ファイルのフラット ファイル出力の宛先とともに追加されています。データは既存の EDW データベースに引き続き読み込まれるものの、フラット ファイル（csv 形式）でファイル システムにも書き込まれます。これはその後 GCP バケットにコピーされ BigQuery に読み込まれます。このパターンは、BigQuery の開発ワークストリームと並行して、現在の本番環境の EDW に対する改善をサポートします。
BigQuery の宛先が追加された状態の SSIS データフロー
csv ファイルが生成されると、プロセスの実行タスクを介して実行される PowerShell スクリプトを経由して、Google Cloud Storage バケット（GCS）にコピーされます。以下は、サンプルの SSIS コントロール フローのスクリーンショットです。
SSIS のコントロール フロー
出力: SSIS 実行プロセスのタスク
構成: SSIS 実行プロセスのタスク
上記のスクリーンショットからのサンプル構文
csv ファイルが GCS バケットにコピーされると、ファイルを参照するための外部テーブルが BigQuery に作成されます。この場合、外部テーブルはステージング テーブルとして機能し、直接クエリするかソースとして使用して、レポートとユーザー アクセス向けに最適化された内部テーブルへとデータを統合できます。以下は、外部テーブルのスキーマのサンプルです。
データの QA と検証をサポートするには、GCP プロフェッショナル サービスのデータ検証ツール（DVT）を使用して、テストとデータ検証を自動化できます。例えば、EDW と BigQuery データベース間のテーブルの行数と列のデータ分布の比較などが自動化可能です。
データ検証ツール（DVT）は、オープンソースの Python CLI ツールであり、異種のデータソース テーブルを比較する Ibis フレームワークに基づいており、マルチレベルの検証機能を備えています。
データ検証は、データ ウェアハウス、データベース、データレイクの移行プロジェクトにおいて重要なステップです。ここでは、ソースと宛先の両方のテーブルの構造化または半構造化されたデータが比較され、各移行ステップ（データとスキーマの移行、SQL スクリプト変換、ETL 移行など）の後に一致していて正しいことが確認されます。データ検証ツールは、このタスクの実行のために、自動化された繰り返し可能なソリューションを提供します。
Google BigQuery での SQL Server Reporting Services（SSRS）の使用
SQL Server Reporting Services（SSRS）はレポートとダッシュボード向けに使用され、BigQuery Simba ODBC ドライバ（32 ビットおよび 64 ビット）を使用して Google BigQuery から直接レポートを生成するために使用できます。
デベロッパー向けの重要な注意: Visual Studio 2019 は 32 ビットの IDE であり、SSRS Report Server（SQL 2019）は 64 ビットのアプリケーションです。32 ビットの ODBC DSN を使用して SSRS レポートを開発およびテストしてから、レポートを SQL Server Report Server にデプロイした後に、レポート データソースを編集して、64 ビット ODBC DSN を参照します。
Visual Studio - インストール手順
SQL Server Data Tools（SSDT）を含む Visual Studio 2019 Community Edition をインストールします
Visual Studio Marketplace の Visual Studio SSIS プロジェクト テンプレートから、または Chocolatey を使用して、Visual Studio SSIS プロジェクト テンプレートをインストールします。
Visual Studio Marketplace から Visual Studio SSRS プロジェクト テンプレートをインストールします。
BigQuery Simba ODBC ドライバ（32 ビットおよび 64 ビット）をダウンロードおよびインストールし、ODBC システム DSN を構成します。
BigQuery サービス アカウントにより、BigQuery Simba ODBC DSN が保存された認証情報を使用して BigQuery のテーブルとビューにアクセスできます。ODBC DSN の構成前に、BigQuery サービス アカウントを作成しキーファイル（.json）をダウンロードする必要があります。また、サービス アカウントに必要な BigQuery ユーザー権限を割り当てるようにしてください。
スクリーンショット: BigQuery サービス アカウント向けのサービス アカウントの権限。
ODBC システム DSN の構成（32 ビットおよび 64 ビット）
*UI のヒント: DSN の構成時は、[Catalog (Project)] フィールドに GCP ProjectID をコピーして貼り付け、データセットを選択し、[Catalog (Project)] プルダウンをクリックして [Catalog (Project)] を再選択します。
SQL Server Reporting Services の構成
レポートを実行し、データソースでユーザー名やパスワードを要求しないよう指定した場合、SSRS ではドメイン アカウントである実行アカウントが必要となります。この例では、テストドメインのユーザー アカウント GONTOSO\ssrs-demo が作成されています。このアカウントはシンプルなドメインのユーザー アカウントであり、SSRS レポート サーバー上の権限はありません。
以下のレポート サーバー構成マネージャーに実行アカウントを追加します。
SSRS レポートの開発とデプロイ
レポート サーバーには 64 ビットの DSN が必要であることは、重要な注意点です。32 ビットの DSN で開発 / テストし、その後 SSRS サーバーのデータソースを変更して、64 ビットの DSN を参照することをおすすめします。以下のスクリーンショットで、[Credentials] という小見出しの下の [Without Any Credentials] にチェックを入れると、先ほど構成したレポート サーバー実行アカウントが SSRS によって使用されます。また、先ほど作成した BigQuery サービス アカウントには、作成済みの BigQuery データセットへの読み取りアクセス権があるため、レポート サーバーのロールと権限を再確認してレポートが安全であることを確かめることをおすすめします。
レポートのデモ
要約すると、SSRS と SSIS を使い慣れているデベロッパーとアナリストはどちらも、慣れ親しんだツールセットを初期の移行後も BigQuery で使用できます。そのため、後続のプロジェクト フェーズでデータフロー、レポート機能、BI を Looker と併用して ETL/ELT をモダナイズする計画を立てることができます。
-Google Cloud プロフェッショナル サービス戦略クラウド エンジニア Matthew Smith
