CData SSIS Components を使用して Gumroad のデータを Google BigQuery にマイグレーション

Google BigQuery は、サーバーレスで高いスケーラビリティとコスト効率を備えたデータウェアハウスであり、組織がビッグデータを実用的なインサイトに変換できるよう設計されています。

CData SSIS Components は、SQL Server Integration Services を拡張し、さまざまなソースやデスティネーションからデータを簡単にインポート・エクスポートできるようにします。

この記事では、BigQuery へのエクスポート時のデータ型マッピングの考慮事項を確認し、CData SSIS Components for Gumroad と BigQuery を使用してGumroad のデータを Google BigQuery にマイグレーションする方法を説明します。

データ型マッピング

STRUCT 型と ARRAY 型

Google BigQuery は、1 つの行に複合値を格納するための STRUCT と ARRAY という 2 種類の型をサポートしています。Google BigQuery の一部では、これらは RECORD 型および REPEATED 型としても知られています。

STRUCT は、名前でアクセスでき、異なる型を持つことができる固定サイズの値のグループです。コンポーネントは struct をフラット化し、ドット表記の名前でフィールドにアクセスできるようにします。これらのドット表記の名前は引用符で囲む必要があることに注意してください。

ARRAY は、同じ型の値で任意のサイズを持つことができるグループです。コンポーネントは配列を単一の複合値として扱い、JSON 集約として報告します。これらの型は組み合わせることができ、STRUCT 型が ARRAY フィールドを含んだり、ARRAY フィールドが STRUCT 値のリストになったりする場合があります。

特別な考慮事項

• Google BigQuery には、DATETIME（タイムゾーンなし）と TIMESTAMP（タイムゾーンあり）の両方のデータ型があり、CData SSIS Components はローカルマシンのタイムゾーンに基づいて datetime にマッピングします。
• Google BigQuery では、NUMERIC 型は 38 桁の精度と小数点以下最大 9 桁をサポートし、BIGNUMERIC 型は 76 桁の精度と小数点以下最大 38 桁をサポートします。CData SSIS Components for Google BigQuery は精度/スケールを自動検出しますが、Destination コンポーネントでは高精度カラムを手動でマッピングできます。
• INTERVAL データ型：
  • コンポーネントは INTERVAL 型を文字列として表現します。クエリで INTERVAL 型が必要な場合は、BigQuery SQL の INTERVAL フォーマットを使用して INTERVAL を指定する必要があります：

    YEAR-MONTH DAY HOUR:MINUTE:SECOND.FRACTION

  • 例えば、「5 年と 11 ヶ月、マイナス 10 日と 3 時間と 2.5 秒」という値は正しいフォーマットでは以下のようになります：

    5-11 -10 -3:0:0.2.5

前提条件

• Visual Studio 2022
• Visual Studio 2022 用 SQL Server Integration Services Projects 拡張機能
• CData SSIS Components for Google BigQuery
• CData SSIS Components for Gumroad

プロジェクトの作成とコンポーネントの追加

1. Visual Studio を開き、新しい Integration Services プロジェクトを作成します。
2. Control Flow 画面に新しい Data Flow Task を追加し、Data Flow Task を開きます。
3. Data Flow Task に CData Gumroad Source コントロールと CData GoogleBigQuery Destination コントロールを追加します。

Gumroad ソースの設定

以下の手順に従って、Gumroad への接続に必要なプロパティを指定します。

1. CData Gumroad Source をダブルクリックしてソースコンポーネントエディタを開き、新しい接続を追加します。
2. CData Gumroad Connection Manager で接続プロパティを設定し、接続をテストして保存します。

   OAuth 認証の設定

   Gumroad への認証では、OAuth 2.0 標準を使って自分自身のデータに接続する場合はもちろん、他のユーザーが各自のデータに接続できるようにする場合にも利用できます。これが推奨される認証方式です。

   まず、Gumroad に OAuth アプリケーションを登録する必要があります。https://app.gumroad.com/settings/advanced の Gumroad アカウント設定にアクセスし、Applications セクションに移動して OAuth アプリケーションを作成できます。

   以下の接続プロパティを設定して接続を確立してください：

   • AuthScheme：OAuth に設定します。
   • InitiateOAuth：GETANDREFRESH に設定します。CData API Profile for Gumroad が OAuth プロセスを自動的に進め、アクセストークンを取得します。
   • OAuthClientID：アプリ設定で指定された client_id に設定します。
   • OAuthClientSecret：アプリ設定で指定された client_secret に設定します。
   • CallbackURL：アプリ設定で指定したリダイレクト URI に設定します。

   接続文字列の例

   Profile=C:\profiles\Gumroad.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;CallbackUrl=your_callback_url;
   

   
3. 接続を保存後、「Table or view」を選択し、Google BigQuery にエクスポートするテーブルまたはビューを選択して、CData Gumroad Source Editor を閉じます。

Google BigQuery デスティネーションの設定

Gumroad Source を設定したら、Google BigQuery 接続を設定してカラムをマッピングします。

1. CData Google BigQuery Destination をダブルクリックしてデスティネーションコンポーネントエディタを開き、新しい接続を追加します。
2. CData GoogleBigQuery Connection Manager で接続プロパティを設定し、接続をテストして保存します。
   • Google は OAuth 認証標準を使用しています。個々のユーザーに代わって Google API にアクセスするには、埋め込み資格情報を使用するか、独自の OAuth アプリを登録できます。 OAuth を使用すると、サービスアカウントを使用して Google Apps ドメイン内のユーザーに代わって接続することもできます。サービスアカウントで認証するには、アプリケーションを登録して OAuth JWT 値を取得します。 OAuth 値に加えて、DatasetId と ProjectId を指定します。OAuth の使用ガイドについては、ヘルプドキュメントの「Getting Started」章を参照してください。

   便利な接続プロパティ

   • QueryPassthrough: True に設定すると、クエリは Google BigQuery に直接渡されます。
   • ConvertDateTimetoGMT: True に設定すると、コンポーネントはローカルマシンの時刻ではなく、日時値を GMT に変換します。
   • FlattenObjects: デフォルトでは、コンポーネントは STRUCT カラムの各フィールドを独自のカラムとして報告し、STRUCT カラム自体は非表示にします。False に設定すると、トップレベルの STRUCT は展開されず、独自のカラムとして残ります。このカラムの値は JSON 集約として報告されます。
   • SupportCaseSensitiveTables: このプロパティを true に設定すると、同じ名前で大文字小文字が異なるテーブルは、すべてメタデータで報告されるように名前が変更されます。デフォルトでは、プロバイダーはテーブル名を大文字小文字を区別しないものとして扱うため、複数のテーブルが同じ名前で大文字小文字が異なる場合、メタデータでは 1 つだけが報告されます。
   
3. 接続を保存後、Use a Table メニューでテーブルを選択し、Action メニューで Insert を選択します。
4. Column Mappings タブで、入力カラムからデスティネーションカラムへのマッピングを設定します。

プロジェクトの実行

これでプロジェクトを実行できます。SSIS Task の実行が完了すると、SQL テーブルのデータが選択したテーブルにエクスポートされます。