IICSでGoogle スプレッドシートに接続してみる
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
本エントリはクラスメソッド インフォマティカ Advent Calendar 2020のエントリです。
- クラスメソッド インフォマティカ Advent Calendar 2020 - Qiita
- クラスメソッド インフォマティカ Advent Calendar 2020 ｜ 特集カテゴリー ｜ Developers.IO
データアナリティクス事業本部、池田です。
アドベントカレンダー11日目です！
Informatica Intelligent Cloud Services（以下IICS）には
いろいろなコネクタ
がありますが、
今回はGoogle スプレッドシート（Google Sheets、以下スプレッドシート）に接続してみます。
【 Google Sheets Connector Overview 】
補足！！！
スプレッドシートのコネクタでの接続が目的なので、 処理としては単純に Amazon S3 に吐き出すだけにします。
本当にスプレッドシートをS3に吐き出したいだけなら、もちろんIICSを使うまでもなく、 例えば↓のような Google Apps Script（GAS） のライブラリを使うなどの方がたぶん楽です。
また、スプレッドシート→ BigQuery の 連携ができるので、IICSで既に使っているような場合は、 BigQueryを経由するのも可能かもしれません。（気が向いたら試してブログにします。）
↑一度きりのインポートのようなものではなく、スプレッドシートを変更するとBigQuery側も変更されました。（すごい！）
っていう前置きを踏まえて次章から、 スプレッドシート側の準備、IICSでの接続、マッピング機能でのS3出力、と進めていきます。
スプレッドシート（GCP）側の準備（リフレッシュトークンの取得）
スプレッドシートの接続の作成に必要になるリフレッシュトークンを作成します。
スプレッドシートのコネクタは
Google Sheets API を使用しており、
このAPIを使うために必要になるのがリフレッシュトークンです。
けっこうしんどい作業です。以下、ガイドを基に私がやった作業をざっと掲載します。
作業としては、コネクタのガイドの、 Administration of Google Sheets Connector と Generating OAuth 2.0 access tokens を行います。
クライアントIDとクライアントシークレット
Administration of Google Sheets Connector では、GoogleのクライアントIDとクライアントシークレットの取得までを行います。
GCPのコンソールから、Google Sheets APIを有効化します。
OAuth クライアント IDを作成します。
ただし、初めて作成する場合はOAuth 同意画面の設定が求められます。
（今回の用途の範囲に限って言えば、自分が許可する時の画面への影響だけなので、
あまり気にしなくて良い部分ではないかと思います。たぶん。）
設定後にOAuth クライアント IDが作成できるようになります。
↑IICSのガイドでは特に触れていないようでしたが、
私の場合はリダイレクトURIが後の手順で必要になりました。
http://localhost を設定しました。
作成が完了すると、目当てのクライアントIDとクライアントシークレットを
含むJSONファイルがダウンロードできるようになります。
リフレッシュトークン
Generating OAuth 2.0 access tokens では、Googleのリフレッシュトークンを取得します。
Chromeで↓のURLにつなぎます。前節のクライアントIDとリダイレクトURIを使います。
https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=auto&client_id={クライアントID}&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets&redirect_uri={リダイレクトURI}
（このURLの scope により、スプレッドシートのAPI使用の許可が求められます。）
表示される画面（←OAuth 同意画面の設定がここで効いてきます）で許可をしていくと、リダイレクトされます。
そのURLのパラメータの code が認可コードです。
http://localhost/?code={認可コード}&scope=https://www.googleapis.com/auth/spreadsheets
その認可コードとクライアントID、クライアントシークレットを使ってPOSTリクエストを発行し、
リフレッシュトークンを取得します。
↓私はChromeのデベロッパーツール（F12）でJavaScript動かしてリクエストしました。
fetch('https://accounts.google.com/o/oauth2/token', {
method: 'POST',
body: JSON.stringify({
"grant_type": "authorization_code",
"code":"{認可コード}",
"client_id":"{クライアントID}",
"client_secret":"{クライアントシークレット}",
"redirect_uri":"http://localhost"
})
})
.then(response => response.json())
.then(data => {
console.log('Success:', data);
});
このレスポンスの中に含まれる refresh_token が、念願のリフレッシュトークンです！！！
※
ここまででリフレッシュトークンが取得できたので、
IICSの ガイド
の『Step 3. Refreshing an access token』（アクセストークン）は、IICSで接続するだけであれば不要だと思います。
また、執筆時点のガイドではgrant_typeにまた authorization_code を指定してリクエストしていますが、
正しくは refresh_token （ガイドだと画像の方が正しい）だと思われます。
使用するスプレッドシート
今回使うスプレッドシートは↓こんな感じです。
IMPORTXML 関数を使って、
弊社ブログのページ をスクレイピングしています。
接続してみる
前章長かったですね…ここからが本題です。
コネクタの有効化
「アドオンコネクタ」からコネクタを有効化します。
接続の作成
Google Sheets connection properties
を参考に接続を作成します。
ランタイム環境 は準備しておいたSecure Agentを指定。
Hosted Agent
ではダメでした…
ClientId と ClientSecret は前章でさんざん出てきたやつです。
RefreshTokenForSheet は 前章の最後に取得できたリフレッシュトークン。
SpreadSheetId は接続したいスプレッドシートのURLから取得できます。
今回のスプレッドシートは1行目がヘッダーなので、 HeaderPresent をチェック。
（ InitialColumnRange で読込みの開始範囲が指定できるのかと思ったのですが、
いろいろな値を試しましたが、私では効かせられませんでした……）
動かしてみる
接続はできたと言えばできたのですが、 せっかくなのでマッピングを使って処理をしてみます。
Informatica Cloud Data Integrationの最重要ETL機能「Mapping」のチュートリアルをやってみた
出力先（ターゲット）はS3なので、こちらも接続だけ作っておきます。
マッピングの作成＆実行
マッピング作っていきます！
マッピングの名称をつけます。
ソース側の設定をします。
前章で作ったスプレッドシートの接続をここで使います。
オブジェクト でシート名を指定します。
「データのプレビュー」をすると、
スプレッドシートの内容を表示してくれます。
これができれば、リフレッシュトークンやら設定やらはもう大丈夫でしょう！
苦労が報われます。
（さすがに画像の列（img）は空になっていますね。エラーにならないだけでもすごい！）
ターゲット（S3）側の設定も良い感じに仕上げます。
保存後に試しに実行してみます。
（ここの ランタイム環境 もSecure Agentじゃないとダメでした。）
「マイジョブ」から結果を確認できます。
成功！
無事S3にファイルが出力されていました！！！
おわりに
リフレッシュトークンを扱ったことが無い場合には、ちょっとハードルが高い印象を受けました。 IICS上での操作は簡単でしたね。
コネクタはまだまだ たくさんある ので、おもしろそうなものは触ってみたいと思います。たぶん。
