snowflake×GoogleSpreadSheet ～OAuth認可なAPIを利用してみた

OAuthの解説記事は多数ありますが、本記事でも簡単な説明をいたします。
OAuthなんて知ってるよ！という方は本節は飛ばして下さい。

OAuthはWebサービスなどを連携して“権限や許可を与える”ためのプロトコルです。
皆さんも、Googleと何かしらのサービスを連携するみたいなことを実施したことがあると思いますが、そこで利用されています。

OAuthを利用するメリットとしては、以下が挙げられると思います。

1. 他サービスとの連携が容易になる

2. アクセストークンが悪用される危険が少ない

3. ユーザー毎に権限を制限できる

GoogleのAPIもいくつかの認証方法を用意していますが、Google翻訳等の一般データにアクセスする機能は、APIキーを利用しても問題ありません。一方で、GoogleドライブやSpreadSheetなどのユーザーデータにアクセスする場合は、OAuthの利用が推奨されています。

OAuthは、RFC 6749で定義されてます。RFC 6749では以下の４つの認可フローを定義しています。

• 認可コードフロー

• インプリシットフロー

• リソースオーナー・パスワード・クレデンシャルズフロー

• クライアントクレデンシャルズフロー

snowflakeでは、上記4つの認可フローのうち、2つをサポートしています。(※公式ドキュメント参照)

• 認可コードフローを使用したOAuth

• クライアントクレデンシャルズフローを使用したOAuth

実は、上記以外の２つは脆弱性が見つかっており、非推奨となっているため、snowflakeでは上記2つのみに対応していると思われます。

GoogleのOAuthでは、「認可コードフロー」に対応しているため、今回は「認可コードフロー」を用いて実装します。

※厳密には、アクセストークン取得の際に同時に発行されるリフレッシュトークンを用いた「リフレッシュトークンフロー」と思います。

OAuthの詳細な説明は、こちらのYoutubeが感動的にわかりやすいです。外部サイトで恐縮ですが、詳細に知りたい方はご覧ください。

1.  APIの有効化
   1. GCPでプロジェクトを作成する
   2. プロジェクトでAPIを有効化する

2. OAuthシークレットを作成する
   1. OAuth同意画面を登録する
      1. 今回は書き込みを検証するので、スコープには「https://www.googleapis.com/auth/spreadsheets」を指定する
   2. OAuth2.0クライアントを作成する

3. Refresh TokenからAccess Tokenを獲得できるようにする
   1. Authorizationコードを取得する
   2. Authorizationコードから、「Access Token」と「Refresh Token」を取得する
   3. 「Refresh Token」から、「Access Token」を得る

2の手順が完了した時点で、以下のようなJSONが得られるはずです。このうち、「client_id」、「client_secret」、「token_uri」はsnowflakeで利用します。

{
	"web": {
		"client_id": "<xxxxxxxx>",
		"project_id": "<xxxxxxxx>",
		"auth_uri": "https://accounts.google.com/o/oauth2/auth",
		"token_uri": "https://oauth2.googleapis.com/token",
		"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
		"client_secret": "<xxxxxxxx>",
		"redirect_uris": [
			"http://localhost:8080"
		]
	}
}

また、3.bの手順を実行した時点で、以下のJSONが得られるはずです。このうち、「refresh_token」をsnowflakeで利用します。

{
	"access_token": "xxxxxx",
	"expires_in": 3599,
	"refresh_token": "xxxxxx",
	"scope": "https://www.googleapis.com/auth/spreadsheets",
	"token_type": "Bearer"
}

OAuth以外の方法と同様に、ネットワークルールを作成します。

CREATE OR REPLACE NETWORK RULE google_sheets_apis_network_rule
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = ('sheets.googleapis.com')
;

ここが少しOAuth利用で特徴的な部分になります。OAuth利用に必要な認証情報をセキュリティ統合として作成します。

CREATE OR REPLACE SECURITY INTEGRATION google_sheets_oauth
TYPE = API_AUTHENTICATION
AUTH_TYPE = OAUTH2
OAUTH_GRANT = 'AUTHORIZATION_CODE'
OAUTH_CLIENT_AUTH_METHOD = CLIENT_SECRET_POST
OAUTH_CLIENT_ID = 'xxxxxx'
OAUTH_CLIENT_SECRET = 'xxxxxx'
OAUTH_TOKEN_ENDPOINT = 'https://oauth2.googleapis.com/token'
ENABLED = TRUE
COMMENT = ''
;

少しだけオプションの補足解説です。詳細は公式ドキュメントを参照ください

• AUTH_TYPE
  • OAuth 2.0を使用して外部サービスを認証　します

• OAUTH_GRANT
  • AUTHORIZATION_CODE
    • 認可フローに、「認可コードフロー」を利用することを明示します。今回はこちらを利用します
  • CLIENT_CREDENTIALS
    • 認可フローに「クライアント・クレデンシャルズ」を利用することを明示します。

• OAUTH_CLIENT_ID
  • 事前準備で取得した「client_id」を指定します

• OAUTH_CLIENT_SECRET
  • 事前準備で取得した「client_secret」を指定します

OAtuh向けのシークレットを作成します。

CREATE OR REPLACE SECRET oauth_token
TYPE = OAUTH2
API_AUTHENTICATION = google_sheets_oauth
OAUTH_REFRESH_TOKEN = 'xxxxx'
;

オプションの補足解説です。詳細は公式ドキュメントを参照ください

• TYPE
  • OAuth 2.0を使用して外部サービスを認証します

• API_AUTHENTICATION
  • 作成したセキュリティ統合を指定します

• OAUTH_REFRESH_TOKEN
  • 事前準備で取得した「refresh_token」を指定します

外部アクセス統合を作成して、ネットワークルールとシークレットを紐づけします。

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION google_sheets_api_integration
ALLOWED_NETWORK_RULES = (google_sheets_apis_network_rule)
ALLOWED_AUTHENTICATION_SECRETS = (oauth_token)
ENABLED = TRUE
;

• 利用するAPIの仕様・サンプル

  今回利用するAPIの仕様や、利用例はGoogleの公式ドキュメントを参考にさせてもらいました。処理の内容がわかりにくい場合は、ご参照ください。

  • APIの一覧・仕様
    • このページの「v4.spreadsheets.values」リソースのupdateメソッドを利用しました。
  • APIのサンプル
    • このページの「基本的な書式設定」を参考にしました

実際に、Googleのスプレッドシートに書き込みを実施するUDFを作成します。
（実際Pythonから書き込みする場合は、高度なパッケージを利用することが多いですが、ここではシンプルな動作確認のため、素のAPIをコールしています）

CREATE OR REPLACE FUNCTION write_google_sheets(sheetid STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.11
HANDLER = 'write_google_sheets'
EXTERNAL_ACCESS_INTEGRATIONS = (google_sheets_api_integration)
PACKAGES = ('snowflake-snowpark-python', 'requests')
SECRETS = ('cred' = oauth_token)
AS
$$
import _snowflake
import requests
import json
def write_google_sheets(sheetid):

    # snowflake 関数によるOauthトークン取得
    access_token = _snowflake.get_oauth_access_token('cred')
    headers = {
        'Authorization':f'Bearer {access_token}'
    }

    url = f'https://sheets.googleapis.com/v4/spreadsheets/{sheetid}/values/シート1!A1:D5?valueInputOption=USER_ENTERED'

    sample_data = {
    "range": "シート1!A1:D5",
    "majorDimension": "ROWS",
    "values": [
            ["Item", "Cost", "Stocked", "Ship Date"],
            ["Wheel", "20.50", "4", "3/1/2016"],
            ["Door", "15", "2", "3/15/2016"],
            ["Engine", "100", "1", "3/20/2016"],
            ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
        ]
    }

    response = requests.put(url, headers=headers, data=json.dumps(sample_data))
    return response.json()
$$
;

以下、補足です

• snowflakeの関数であるget_oauth_access_token()関数で、リフレッシュトークンを利用して、アクセストークンを新規発行してくれています。

• 引数にスプレッドシートのシートIDを指定します。