--- title: snowflake×GoogleSpreadSheet ～OAuth認可なAPIを利用してみた description: snonwflakeの外部API認証（OAuth認可）を用いて、GoogleのSpreadSheetと連携した検証です --- [Snowflake Knowledge - INSIGHT LAB](https://knowledge.insight-lab.co.jp/snowflake) # [snowflake×GoogleSpreadSheet ～OAuth認可なAPIを利用してみた](https://knowledge.insight-lab.co.jp/snowflake/snowflakegooglespreadsheet-oauthを利用したapi認証を利用してみた) 作成者: [Osyou](https://knowledge.insight-lab.co.jp/snowflake/author/osyou)｜2024年1月22日 ## 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を用いた例](https://docs.snowflake.com/ja/developer-guide/external-network-access/external-network-access-examples)がありますので、そちもご参照ください。 ## 1.OAuthとは 概要 OAuthの解説記事は多数ありますが、本記事でも簡単な説明をいたします。 OAuthなんて知ってるよ！という方は本節は飛ばして下さい。 OAuthはWebサービスなどを連携して“権限や許可を与える”ためのプロトコルです。 皆さんも、Googleと何かしらのサービスを連携するみたいなことを実施したことがあると思いますが、そこで利用されています。 OAuthのメリット OAuthを利用するメリットとしては、以下が挙げられると思います。 1. 他サービスとの連携が容易になる 1. アクセストークンが悪用される危険が少ない 1. ユーザー毎に権限を制限できる GoogleのAPIもいくつかの認証方法を用意していますが、Google翻訳等の一般データにアクセスする機能は、APIキーを利用しても問題ありません。一方で、GoogleドライブやSpreadSheetなどのユーザーデータにアクセスする場合は、OAuthの利用が推奨されています。 snowflakeで対応しているフロー OAuthは、[RFC 6749](https://tex2e.github.io/rfc-translater/html/rfc6749.html)で定義されてます。RFC 6749では以下の４つの認可フローを定義しています。 - 認可コードフロー - インプリシットフロー - リソースオーナー・パスワード・クレデンシャルズフロー - クライアントクレデンシャルズフロー snowflakeでは、上記4つの認可フローのうち、2つをサポートしています。(※[公式ドキュメント](https://docs.snowflake.com/ja/user-guide/api-authentication)参照) - 認可コードフローを使用したOAuth - クライアントクレデンシャルズフローを使用したOAuth 実は、上記以外の２つは脆弱性が見つかっており、非推奨となっているため、snowflakeでは上記2つのみに対応していると思われます。 GoogleのOAuthでは、「認可コードフロー」に対応しているため、今回は「`認可コードフロー`」を用いて実装します。 ※厳密には、アクセストークン取得の際に同時に発行されるリフレッシュトークンを用いた「リフレッシュトークンフロー」と思います。 OAuthの詳細な説明は、[こちらのYoutube](https://www.youtube.com/watch?v=PKPj_MmLq5E)が感動的にわかりやすいです。外部サイトで恐縮ですが、詳細に知りたい方はご覧ください。 ## 2.事前準備（Google Sheet API側の準備） APIを利用するための事前準備を実施します。事前準備の方法は色々ありますが、私は、[こちらのブログ](https://qiita.com/7032/items/50fc96b6e8c9ac90c32f)を参考にしました。 詳細手順 以下の手順を実行します。 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](https://www.notion.so/snowflake-GoogleSpreadSheet-OAuth-API-bc61ab5d57bf4a4c965f35fec48c241b?pvs=21)の手順が完了した時点で、以下のようなJSONが得られるはずです。このうち、「`client_id`」、「`client_secret`」、「`token_uri`」はsnowflakeで利用します。 ``` { "web": { "client_id": "", "project_id": "", "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": "", "redirect_uris": [ "http://localhost:8080" ] } } ``` また、[3.bの手順](https://www.notion.so/snowflake-GoogleSpreadSheet-OAuth-API-bc61ab5d57bf4a4c965f35fec48c241b?pvs=21)を実行した時点で、以下の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 = '' ; ``` 少しだけオプションの補足解説です。詳細は[公式ドキュメント](https://docs.snowflake.com/en/sql-reference/sql/create-security-integration-api-auth)を参照ください - AUTH\_TYPE - OAuth 2.0を使用して外部サービスを認証　します - OAUTH\_GRANT - `AUTHORIZATION_CODE` - 認可フローに、「認可コードフロー」を利用することを明示します。今回はこちらを利用します - `CLIENT_CREDENTIALS` - 認可フローに「クライアント・クレデンシャルズ」を利用することを明示します。 - OAUTH\_CLIENT\_ID - [事前準備](https://www.notion.so/snowflake-GoogleSpreadSheet-OAuth-API-bc61ab5d57bf4a4c965f35fec48c241b?pvs=21)で取得した「`client_id`」を指定します - OAUTH\_CLIENT\_SECRET - [事前準備](https://www.notion.so/snowflake-GoogleSpreadSheet-OAuth-API-bc61ab5d57bf4a4c965f35fec48c241b?pvs=21)で取得した「`client_secret`」を指定します シークレットの作成 OAtuh向けのシークレットを作成します。 ``` CREATE OR REPLACE SECRET oauth_token TYPE = OAUTH2 API_AUTHENTICATION = google_sheets_oauth OAUTH_REFRESH_TOKEN = 'xxxxx' ; ``` オプションの補足解説です。詳細は[公式ドキュメント](https://docs.snowflake.com/en/sql-reference/sql/create-secret)を参照ください - TYPE - OAuth 2.0を使用して外部サービスを認証します - API\_AUTHENTICATION - 作成したセキュリティ統合を指定します - OAUTH\_REFRESH\_TOKEN - [事前準備](https://www.notion.so/snowflake-GoogleSpreadSheet-OAuth-API-bc61ab5d57bf4a4c965f35fec48c241b?pvs=21)で取得した「`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の一覧・仕様 - [このページ](https://developers.google.com/sheets/api/reference/rest?hl=ja)の「`v4.spreadsheets.values`**」**リソースの`update`メソッドを利用しました。 - APIのサンプル - [このページ](https://developers.google.com/sheets/api/samples?hl=ja)の「基本的な書式設定」を参考にしました 実際に、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.動作確認 動作確認をしてみます。事前に、スプレッドシートは空の状態にしておきます。 UDFにスプレッドシートのIDを指定して実行します ``` SELECT write_google_sheets('xxxxxxxxxxxxxxxx'); ``` スプレッドシートに値が書き込みされました！これで、snowflakeのUDFから、スプレッドシートへとデータが連携できたことになります！ ## 5.おわりに 今回は認可コード付与フローに準じたOAuthを実装してみました。対応した認可サーバーが見つかれば、クライアントクレデンシャルズフローの方も試してみたいと思います。 [完全な記事を表示](https://knowledge.insight-lab.co.jp/snowflake/snowflakegooglespreadsheet-oauthを利用したapi認証を利用してみた) ```json { "@context" : "http://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Osyou" }, "dateModified" : "2024-01-22T00:14:21.409Z", "datePublished" : "2024-01-22T00:14:20Z", "headline" : "snowflake×GoogleSpreadSheet ～OAuth認可なAPIを利用してみた", "image" : { "@type" : "ImageObject", "height" : 892, "url" : "https://download.insight-lab.co.jp/hubfs/Untitled%202-4.png", "width" : 592 }, "mainEntityOfPage" : "https://knowledge.insight-lab.co.jp/snowflake/snowflakegooglespreadsheet-oauthを利用したapi認証を利用してみた", "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "height" : 60, "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png", "width" : 60 }, "name" : "snowflakeナレッジ" } } ```