お問い合わせ
9 分で読むことができます

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

執筆者 Osyou 更新日時 2024年1月22日

Topics: Python snowflake

目次

0. はじめに



ナレッジ執筆をしばらくさぼっており、2023年のAdeventCalenderに参加し損ねましたOsyouです。さて、snowflakeでは2023年もたくさんの新機能がリリースされましたが、その中でも、External Network Accessは個人的に好きな機能でした。

既にたくさんの検証記事がでておりますが、多くの記事はAPI認証方法がAPIキー認証であり、SECRETオブジェクトのタイプにGENERIC_STRINGを指定したものだと認識しております。

一方で、snowflakeでは外部APIを利用する際の認証方法として、OAuthをサポートしています。
そこで、この記事ではOAuthを用いた外部APIの利用を検証してみます。

具体的には、「Google Sheets APIを用いて、snowflakeで構築したUDF から、Google Spread Sheetへの書き込みを行ってみます。

公式ドキュメントにGoogle翻訳APIを用いた例がありますので、そちもご参照ください。

1.OAuthとは


概要

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

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

OAuthのメリット

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

  1. 他サービスとの連携が容易になる
  1. アクセストークンが悪用される危険が少ない
  1. ユーザー毎に権限を制限できる

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

snowflakeで対応しているフロー

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

  • 認可コードフロー
  • インプリシットフロー
  • リソースオーナー・パスワード・クレデンシャルズフロー
  • クライアントクレデンシャルズフロー

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

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

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

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

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

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

2.事前準備(Google Sheet API側の準備)


APIを利用するための事前準備を実施します。事前準備の方法は色々ありますが、私は、こちらのブログを参考にしました。

詳細手順
以下の手順を実行します。
  1.  APIの有効化
    1. GCPでプロジェクトを作成する
    1. プロジェクトでAPIを有効化する
  1. OAuthシークレットを作成する
    1. OAuth同意画面を登録する
      1. 今回は書き込みを検証するので、スコープには「https://www.googleapis.com/auth/spreadsheets」を指定する
    1. OAuth2.0クライアントを作成する
  1. Refresh TokenからAccess Tokenを獲得できるようにする
    1. Authorizationコードを取得する
    1. Authorizationコードから、「Access Token」と「Refresh Token」を取得する
    1. 「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"
}

3.snowflake実装


やっと本題です。OAuth認可なAPIを利用するUDFを作成していきます。

ネットワークルールの作成

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

CREATE OR REPLACE NETWORK RULE google_sheets_apis_network_rule
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = ('sheets.googleapis.com')
;
SECURITY統合の作成

ここが少し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
;
UDFの作成
  • 利用する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を指定します。

4.動作確認


動作確認をしてみます。事前に、スプレッドシートは空の状態にしておきます。

Untitled-Jan-16-2024-11-32-45-3205-PM

UDFにスプレッドシートのIDを指定して実行します

SELECT write_google_sheets('xxxxxxxxxxxxxxxx');

スプレッドシートに値が書き込みされました!これで、snowflakeのUDFから、スプレッドシートへとデータが連携できたことになります!

Untitled 2-4

Untitled 1-4

5.おわりに


今回は認可コード付与フローに準じたOAuthを実装してみました。対応した認可サーバーが見つかれば、クライアントクレデンシャルズフローの方も試してみたいと思います。

Snowflakeを体験してみませんか?

INSIGHT LABではSnowflake紹介セミナーを定期開催しています。Snowflakeの製品紹介だけでなく、デモンストレーションを通してSnowflakeのシンプルなUI操作や処理パフォーマンスの高さを体感いただけます。

詳細はこちら

Osyou

執筆者 Osyou

4 分で読むことができます。
Snowflakeの料金体系|クレジットと費用最適化のポイントをご紹介
5 分で読むことができます。
【禁断の比較?】SnowflakeとTreasure Dataを比べてみました
1 分で読むことができます。
誤ってupdateしてしまったレコードをtime travelで復元する
3 分で読むことができます。
AWS Lambdaを使ってSnowflakeとSFTPサーバーを連携してみた
6 分で読むことができます。
【Snowflake】初めてStreamを使ったデータパイプライン構築をした話