Looker StudioからSnowflakeのデータを取得するアドオン開発について

こんにちは！バクラク事業部機械学習・データ部データチームの @TrsNium です。

昨年、Google SheetsからSnowflakeに接続するためのアドオンをGoogle Apps Script（GAS）で自作し、ブログ記事として公開しました。今回はLooker StudioからSnowflakeへ直接接続し、任意のクエリを安全に実行・可視化できるコミュニティコネクタを開発しました。本記事では、開発背景、システム構成、認証設計、実装の工夫、そして運用面での知見について詳しくご紹介します！

1. 背景とコミュニティコネクタ開発のモチベーション

バクラク事業部では、以前は Google BigQuery を主なデータ基盤として活用していました。しかし、サービスの成長と共に増大するデータ量や複雑化する分析ニーズに対応するために、Snowflake への移行を進めています。Snowflakeへの詳しい移行背景は、以下の記事を参照してください。

findy-tools.io

Looker Studioは、Google Cloudが提供するBIツールであり、誰でも無料で利用できる「Looker Studio（無償版）」に加えて、Google Cloudサポートやチーム単位での管理機能などを備えた有償プラン「Looker Studio Pro」も提供されています。バクラク事業部では主に無償版を活用し、コストを抑えながら社内データの可視化を行っています。今後、より高機能なツールへの移行も検討の余地がありますが、現在はSnowflakeへの移行を最優先事項として進めているため、BI環境は当面Looker Studioを使い続ける判断をしています。

この状況において、Snowflakeに対応したBIツールを整えるため、Looker Studioとの安全かつシームレスな接続方法を検討する必要がありました。Snowflake社からは、Looker Studio向けの公式コミュニティコネクタが提供されています。これを使えばSnowflakeのデータを直接可視化することができますが、以下のような課題がありました。

1. 認証方式がパスワードのみ

公式コミュニティコネクタは username / password 認証しかサポートしておらず、発行したパスワードを Looker Studio 側に入力する必要があります。バクラク事業部ではセキュリティポリシー上、Snowflake へのパスワード認証を禁止し、Key‑Pair / OAuth / SAML に限定しています。詳細は社内ブログ “Don’t Use Passwords in Your Snowflake Account” を参照ください。

2. 接続・クエリ入力までのUXが煩雑

Looker Studioの仕様上、新たにデータソースを追加するたびに接続情報を再入力する必要があるうえ、クエリを入力できる画面にたどり着くまでに複数回「次へ」をクリックしなければならないなど、UXに課題があります。特に、パスワードなどの認証情報を何度も再入力する操作は、実運用では非現実的でした。

これらの課題に対応するため、バクラク事業部では独自のコミュニティコネクタを開発し、Snowflakeのデータを直接Looker Studioで可視化できる環境を構築しました。

2. 解決策としての Key-Pair 認証

上記の課題を解決するため、Key-Pair JWTを活用したユーザー操作を極小化する認証方式を実装した独自のコミュニティコネクタを開発しました。

この認証方式では、秘密鍵（Private Key）を用いて署名付き JWT（JSON Web Token）を生成し、Snowflake API に送信することで認証を完了します。不要なユーザー操作をなくし、スクリプト側で全て自動化されている点が特徴です。

なぜ OAuth ではなく Key‑Pair 認証？

前提として、Google Apps Script には OAuth 認証を手軽に実装できるapps-script-oauth2 ライブラリが公式で提供されています。このライブラリを使えば、数行の設定で認証設定や Authorization Code Flow（PKCE 対応）を組み込むことができます。技術的には OAuth でもコミュニティコネクタを実装することは可能ですが、運用面で課題がありました。特に認証の有効期限が切れるたびにユーザーが再認証する必要が生じ、多数のデータソースを含むレポートでは現実的ではありません。

OAuth ライブラリを使用すると、認証の有効期限切れ時に Looker Studio の各データソースに対して個別に再認証が必要になります。特にレポート内のグラフ数が多い場合、この再認証作業は大きな負担となります。そのため、再認証作業を発生させずに安全に Looker Studio から Snowflake にアクセスできる手法を検討した結果、Key-Pair 認証を採用しました。

Key-Pair JWT 採用には主に2つのメリットがあります。

まず、完全ノーインタラクションという点です。署名付き JWT をスクリプト側で自動生成できるため、人の再認証を一切必要とせずデータ取得が継続可能です。これにより、定期実行の Looker Studio レポートが途中で止まることがありません。

次に、シンプルなコネクタ UXという点です。接続設定は管理者が一度秘密鍵を登録するだけで済みます。レポート作成者は SQL を書いて保存するだけで OK なので、データソース追加時に接続情報を再入力する煩雑さがありません。

OAuth 認証はセキュリティリスクが小さいですが、「ユーザー体験」と「セキュリティ」のバランスを考慮した結果、Key-Pair 認証が最適解と判断しました。。

3. アーキテクチャ全体像

sequenceDiagram
  participant ユーザー
  participant Looker as Looker Studio
  participant コネクタ as コミュニティコネクタ (GAS)
  participant Snowflake as Snowflake SQL API

  ユーザー->>Looker: レポートを開く
  Looker->>コネクタ: getConfig を呼び出す（設定UI表示）
  コネクタ->>Snowflake: SHOW GRANTS TO USER を実行（ロール一覧取得）
  Snowflake-->>コネクタ: 利用可能なロール一覧を返却
  コネクタ-->>Looker: クエリ・ロール・Warehouse選択UIを構成

  ユーザー->>Looker: クエリ・ロールなどを選択
  Looker->>コネクタ: 選択された設定とクエリを送信
  コネクタ->>Snowflake: JWT付きで SQL 発行リクエスト
  Snowflake-->>コネクタ: 実行ステータス or 結果URLを返却
  コネクタ->>Snowflake: 結果チャンクをポーリング取得
  Snowflake-->>コネクタ: 実行結果データ返却
  コネクタ-->>Looker: スキーマと結果を整形して返却
  Looker-->>ユーザー: グラフやテーブルとして可視化

コミュニティコネクタ (GAS): ユーザーが入力したSQLと設定に基づいて、Snowflakeへクエリを発行し、その結果をLooker Studioが解釈できる形式に変換する。
Snowflake SQL API: Snowflake SQL APIはHTTPエンドポイントを介してSnowflakeのクエリを実行できるインターフェースで、REST APIとして提供されています。このAPIを使用することで、SnowflakeのSQLクエリを実行し、結果を取得することが可能です。

4. Snowflake 側の事前準備と認証設定

秘密鍵（Key-Pair）の生成

openssl genrsa -out rsa_private_key.pem 2048
openssl rsa -in rsa_private_key.pem -pubout -out rsa_public_key.pem

キーペア認証では少なくとも2048ビットのRSAキーペアが必要です。会社のルールに応じてビット数は変更してください。詳しくは以下のドキュメントを参考にしてください。

キーペア認証とキーペアローテーション | Snowflake Documentation

サービスユーザーの作成

CREATE USER LOOKER_STUDIO_CONNECTOR
  RSA_PUBLIC_KEY='<公開鍵>'
  DEFAULT_ROLE = EXAMPLE_ROLE
  DEFAULT_WAREHOUSE = PLAYGROUND;

必要なロール／権限の付与

バクラク事業部のデータ基盤では、Snowflake上のロール設計を「ユーザー → Functional Role → Access Role」という多段構造で管理しています。サービスユーザーにも分析で使用するFunctional Roleが利用できるようにロール割当を行っています。

GRANT ROLE ANALYST TO USER LOOKER_STUDIO_CONNECTOR;
GRANT USAGE ON WAREHOUSE PLAYGROUND TO ROLE EXAMPLE_ROLE;

この準備により、Looker Studio からの接続に対して Snowflake 側で正しく JWT を認識・検証できるようになります。

5. コード全体像とポイント

コードが長いため、折りたたみ形式で表示しています。クリックで詳細表示

main.js

    var cc = DataStudioApp.createCommunityConnector();
    var USER_AGENT = "LookerStudioAppsScript/1.0"
    
    const DEFAULT_SNOWFLAKE_USER_ROLE = "EXAMPLE_ROLE";
    const QUERY_TAG = "looker_studio_query";
    
    /*
      Configure OAuth settings
    */
    function getAuthType() {
      return cc.newAuthTypeResponse()
          .setAuthType(cc.AuthType.NONE)
          .build();
    }
    function getConfig() {
      var config = cc.getConfig();
    
      config.newTextArea()
          .setId('query')
          .setName('query')
          .setHelpText('クエリを入力してください');
        
      var option1 = config.newOptionBuilder()
        .setLabel("全てのデータを取得する")
        .setValue("fetch_all");
      var option2 = config.newOptionBuilder()
        .setLabel("データ行数を制限し、データの取得を高速化する")
        .setValue("fetch_limit");
      config.newSelectSingle()
        .setId("fetchType")
        .setName("データ取得タイプ")
        .setHelpText("取得するデータのタイプを選択してください。'データ行数を制限し、データの取得を高速化する'を選択すると、制限付きでデータを取得します。")
        .addOption(option1)
        .addOption(option2);
      // Warehouse Select
      config.newSelectSingle()
        .setId("warehouse")
        .setName("Warehouse")
        .setHelpText("データを取得する環境を選択してください。")
        .addOption(config.newOptionBuilder().setLabel("PLAYGROUND").setValue("PLAYGROUND"))
        .addOption(config.newOptionBuilder().setLabel("PLAYGROUND_S").setValue("PLAYGROUND_S"))
        .addOption(config.newOptionBuilder().setLabel("PLAYGROUND_M").setValue("PLAYGROUND_M"))
        .addOption(config.newOptionBuilder().setLabel("PLAYGROUND_L").setValue("PLAYGROUND_L"))
        .addOption(config.newOptionBuilder().setLabel("PLAYGROUND_XL").setValue("PLAYGROUND_XL"));
    
      // Role Select
      var roles = [];
      try {
        const userEmail = Session.getActiveUser().getEmail(); 
        var snowflakeConnector = new SnowflakeConnector(DEFAULT_SNOWFLAKE_USER_ROLE);
        const props = PropertiesService.getScriptProperties();
        const env = props.getProperty("ENV");
        
        try {
          roles = snowflakeConnector.getUserRoles(userEmail);
          
          if (roles && roles.length > 0) {
            var roleSelect = config.newSelectSingle()
              .setId("role")
              .setName("ロール")
              .setHelpText("実行するロールを選択してください。");
    
            roles.forEach(function(role) {
                roleSelect.addOption(
                  config.newOptionBuilder()
                    .setLabel(role)
                    .setValue(role)
                );
            });
          } else {
            console.log("No roles found for user: " + userEmail);
            throw new Error("ユーザーロールが見つかりませんでした。管理者に連絡してください。");
          }
        } catch (roleError) {
          console.error("Error getting user roles: " + roleError);
          throw new Error("ロールの取得中にエラーが発生しました: " + roleError.message);
        }
      } catch (e) {
        console.error("Critical error in getConfig: " + e);
        throw new Error("設定の取得中にエラーが発生しました: " + e.message);
      }
      return config.build();
    }
    /*
      1. Sends user-configured SQL Statements to Snowflake to retrieve fields.
      2. Resends user-configured SQL Statements to Snowflake, retrieves the results of the SQL Statements execution and maps them to LookerStudio types.
    */
    function convertRowTypeToFields(rowType) {
      var fields = cc.getFields();
      var types = cc.FieldType;
    
      // map Snowflake data types to LookerStudio data types
      // ref. https://docs.snowflake.com/ja/sql-reference/intro-summary-data-types
      // ref. https://developers.google.com/apps-script/reference/data-studio/field-type?hl=ja
      rowType.forEach(function(column) {
        var field;
        switch (column.type) {
          case 'varchar':
          case 'char':
          case 'character':
          case 'string':
          case 'text':
          case 'time': // HH:MM:SS.nnnnnnnnn
          case 'binary':
          case 'varbinary':
            field = fields.newDimension().setId(column.name).setName(column.name).setType(types.TEXT);
            break;
          case 'fixed':
          case 'number':
          case 'decimal':
          case 'numeric':
          case 'int':
          case 'integer':
          case 'bigint':
          case 'smallint':
          case 'tinyint':
          case 'byteint':
          case 'float':
          case 'float4':
          case 'float8':
          case 'double':
          case 'double precision':
          case 'real':
            field = fields.newMetric().setId(column.name).setName(column.name).setType(types.NUMBER);
            break;
          case 'boolean':
            field = fields.newDimension().setId(column.name).setName(column.name).setType(types.BOOLEAN);
            break;
          case 'date':
            field = fields.newDimension().setId(column.name).setName(column.name).setType(types.YEAR_MONTH_DAY);
            break;
          case 'datetime':
          case 'timestamp':
          case 'timestamp_ltz':
          case 'timestamp_ntz':
          case 'timestamp_tz':
            field = fields.newDimension().setId(column.name).setName(column.name).setType(types.YEAR_MONTH_DAY_SECOND);
            break;
          case 'variant':
          case 'object':
          case 'array':
            field = fields.newDimension().setId(column.name).setName(column.name).setType(types.TEXT);
            break;
          case 'geography':
          case 'geometry':
            field = fields.newDimension().setId(column.name).setName(column.name).setType(types.TEXT);
            break;
          default:
            throw new Error('Unsupported data type: ' + column.type);
        }
      });
      return fields;
    }
    function getFields(request) {
      var savedQuery = request.configParams.query;
      var warehouse = request.configParams.warehouse;
      var snowflakeConnector = new SnowflakeConnector(request.configParams.role);
      var requestStatementResult = snowflakeConnector.requestStatement({query: savedQuery, warehouse: warehouse}, QUERY_TAG);
      return convertRowTypeToFields(requestStatementResult.rowType);
    }
    function getSchema(request) {
      var fields = getFields(request).build();
      return { schema: fields };
    }
    function getData(request) {
      var fetchType = request.configParams.fetchType;
      var limit = fetchType == 'fetch_limit';
      var query = request.configParams.query;
      var warehouse = request.configParams.warehouse;
      var snowflakeConnector = new SnowflakeConnector(request.configParams.role);
      var statementResult = snowflakeConnector.executeQuery({query: query, warehouse: warehouse}, QUERY_TAG, limit);
    
      var fields = convertRowTypeToFields(statementResult.rowType);
      var requestedFieldIds = request.fields.map(function(field) {
        return field.name;
      });
      var requestedFields = fields.forIds(
        request.fields.map(function(field) {
          return field.name;
        })
      );
      // Create a mapping of field names to their indices for quick access
      var fieldNameToIndex = {};
      requestedFieldIds.forEach(function(fieldId, index) {
        fieldNameToIndex[fieldId] = index;
      });
    
      // Rearrange columns to make them in the same order as in Schema
      console.log('Rearranging columns to match schema...');
      var rows = statementResult.data.map(function(rowArray) {
        var rowValues = new Array(requestedFieldIds.length);
        rowArray.forEach(function(value, index) {
          var fieldName = statementResult.rowType[index].name;
          if (fieldNameToIndex.hasOwnProperty(fieldName)) {
            rowValues[fieldNameToIndex[fieldName]] = value;
          }
        });
        return { values: rowValues };
      });
      console.log('Rearranging columns completed.');
      return {
        schema: requestedFields.build(),
        rows: rows
      };
    }
    
    /*
      DEBUG MODE
    */
    function isAdminUser() {
      return true;
    }

snowflake_jwt.js

    // ---------------------------------------
    // Load jsrsasign Library. Used to generate jwt token.
    // ---------------------------------------
    var window = {}; 
    var navigator = { userAgent: USER_AGENT };
    const cdnjs =
    "https://cdnjs.cloudflare.com/ajax/libs/jsrsasign/11.1.0/jsrsasign-all-min.js";
    const expectedSha384 = "SH7pjrbd3mK22fzapFxO/BenIdDWyTBCpFmFS7eWWCS54rs6zuKWVYJ+PmmIDEkZ";
    function verifyScript() {
      const response = UrlFetchApp.fetch(cdnjs);
      const content = response.getContentText();
    
      const actualHash = Utilities.computeDigest(
        Utilities.DigestAlgorithm.SHA_384,
        content
      );
      const actualSha384 = Utilities.base64Encode(actualHash);
    
      if (actualSha384 !== expectedSha384) {
        throw new Error('Script integrity check failed. Expected: ' + expectedSha384 + ' but got: ' + actualSha384);
      }
    
      return true;
    }
    // nosemgrep
    if (verifyScript()) eval(UrlFetchApp.fetch(cdnjs).getContentText());
    
    class SnowflakeConnector {
      constructor(role) {
        const props = PropertiesService.getScriptProperties();
    
        this.account = props.getProperty("SNOWFLAKE_ACCOUNT");
        this.encryptedPrivateKeyPEM = props.getProperty("SNOWFLAKE_PRIVATE_KEY").replace(/\\n/g, "\n").trim();
        this.passphrase = props.getProperty("SNOWFLAKE_PASSPHRASE");
    
        this.user = "LOOKER_STUDIO_CONNECTOR";
        this.role = role;
        this.baseUrl = props.getProperty("SNOWFLAKE_BASE_URL");
      }
      
      generateJWT() {
        var prvKeyObj = KEYUTIL.getKey(this.encryptedPrivateKeyPEM, this.passphrase);
        var unencryptedPrivateKeyPEM = KEYUTIL.getPEM(prvKeyObj, "PKCS8PRV");
        
        prvKeyObj.isPublic = true;
        var pubPEM = KEYUTIL.getPEM(prvKeyObj, "PKCS8PUB");
        var pubContent = pubPEM.replace(/-----.*-----/g, "").replace(/\s+/g, "");
        var derBytes = Utilities.base64Decode(pubContent);
        var digestBytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, derBytes);
        var fingerp = Utilities.base64Encode(digestBytes);
    
        var header = { alg: "RS256", typ: "JWT" };
        var qualifiedUsername = this.account + '.' + this.user;
        var payload = {
          iss: qualifiedUsername + ".SHA256:" + fingerp,
          sub: qualifiedUsername,
          iat: Math.floor(new Date().getTime() / 1000),
          exp: Math.floor(new Date().getTime() / 1000) + 3600
        };
    
        var token = KJUR.jws.JWS.sign("RS256", header, payload, unencryptedPrivateKeyPEM);
        return token;
      }
      
      executeQuery(queryInfo, queryTag, limit=false) {
        try {
          var statementResult = this.requestStatement(queryInfo, queryTag, limit);
          var statementHandle = statementResult.statementHandle;
          var rowType = statementResult.rowType;
          var partitions = statementResult.partitions;
          var data = this.getPartitionedStatement(statementHandle, partitions, limit);
          return { data: data, rowType: rowType };
        } catch (e) {
          Logger.log('executeQuery error: ' + e);
          return null;
        }
      }
      
      requestStatement(queryInfo, queryTag, limit=false) {
        var token = this.generateJWT();
        var SNOWFLAKE_BASE_URL = PropertiesService.getScriptProperties().getProperty("SNOWFLAKE_BASE_URL");
        var warehouse = queryInfo.warehouse || 'PLAYGROUND';
        
        var requestBody = {
          statement: queryInfo.query,
          role: this.role,
          warehouse: warehouse,
          parameters: {
            // looker studioのデータ型とsnowflakeのデータ型の対応
            // ref. https://developers.google.com/apps-script/reference/data-studio/field-type?hl=ja#properties
            DATE_OUTPUT_FORMAT: "YYYYMMDD",
            TIMESTAMP_OUTPUT_FORMAT: "YYYYMMDDHH24MISS",
            CLIENT_RESULT_CHUNK_SIZE: 50,
            USE_CACHED_RESULT: true,
            QUERY_TAG: queryTag
          }
        };
        if (limit) {
          requestBody.parameters.CLIENT_RESULT_CHUNK_SIZE = 1;
        }
        
        var requestOptions = {
          method: 'post',
          contentType: 'application/json',
          headers: {
            Authorization: "Bearer " + token,
            'Content-Type': 'application/json',
            Accept: 'application/json',
            'User-Agent': USER_AGENT,
            "X-Snowflake-Authorization-Token-Type": "KEYPAIR_JWT"
          },
          payload: JSON.stringify(requestBody),
          muteHttpExceptions: true
        };
        
        var response = UrlFetchApp.fetch(SNOWFLAKE_BASE_URL + "/api/v2/statements", requestOptions);
        var responseCode = response.getResponseCode();
        var responseBody = response.getContentText();
        var resultObj = JSON.parse(responseBody);
        
        if (responseCode === 200) {
          return {
            statementHandle: resultObj.statementHandle,
            rowType: resultObj.resultSetMetaData.rowType,
            partitions: resultObj.resultSetMetaData.partitionInfo
          };
        } else if (responseCode === 202) {
          var statementHandle = resultObj.statementHandle;
          var statusUrl = resultObj.statementStatusUrl;
          while (true) {
            Utilities.sleep(3000);
            var checkOptions = {
              method: 'get',
              headers: {
                Authorization: "Bearer " + token,
                Accept: 'application/json',
                'User-Agent': USER_AGENT,
                "X-Snowflake-Authorization-Token-Type": "KEYPAIR_JWT"
              },
              muteHttpExceptions: true
            };
            var checkResponse = UrlFetchApp.fetch(SNOWFLAKE_BASE_URL + statusUrl, checkOptions);
            var checkResponseCode = checkResponse.getResponseCode();
            var checkResponseBody = checkResponse.getContentText();
            var checkResult = JSON.parse(checkResponseBody);
            if (checkResponseCode === 200) {
              return {
                statementHandle: statementHandle,
                rowType: checkResult.resultSetMetaData.rowType,
                partitions: checkResult.resultSetMetaData.partitionInfo
              };
            } else if (checkResponseCode === 202) {
              continue;
            } else {
              throw new Error('failed to check query status: ' + checkResponseBody);
            }
          }
        } else {
          throw new Error('failed to request query: ' + responseBody);
        }
      }
      
      getPartitionedStatement(statementHandle, partitions, limit=false) {
        var token = this.generateJWT();
        var SNOWFLAKE_BASE_URL = PropertiesService.getScriptProperties().getProperty("SNOWFLAKE_BASE_URL");
        var data = [];
        var requests = partitions.map(function(partition, index) {
          return {
            url: SNOWFLAKE_BASE_URL + "/api/v2/statements/" + statementHandle + "?partition=" + index,
            method: "get",
            headers: {
              Authorization: "Bearer " + token,
              "Content-Type": "application/json",
              Accept: "application/json",
              "User-Agent": USER_AGENT,
              "X-Snowflake-Authorization-Token-Type": "KEYPAIR_JWT"
            },
            muteHttpExceptions: true,
          };
        });
        var responses = UrlFetchApp.fetchAll(requests);
        if (limit) {
          responses = responses.slice(0, 1);
        }
        responses.forEach(function(resp, idx) {
          var txt = resp.getContentText();
          if (resp.getResponseCode() !== 200) {
            Logger.log("Failed to fetch partition data: " + resp.getResponseCode() + " " + txt);
            return;
          }
          try {
            var obj = JSON.parse(txt);
            data = data.concat(obj.data);
          } catch (e) {
            Logger.log("Failed to parse JSON for partition " + idx + ": " + e.message);
            throw e;
          }
        });
        return data;
      }
    
      getUserRoles(email) {
        var query = 'show grants to user "' + email.toUpperCase() + '";';
        var queryInfo = {
          query: query,
          warehouse: "PLAYGROUND",
          role: this.role
        };
        var queryTag = "getUserRoles";
        var result = this.executeQuery(queryInfo, queryTag);
        if (result && result.data && result.rowType) {
          var roleIndex = -1;
          for (var i = 0; i < result.rowType.length; i++) {
            var colName = result.rowType[i].name.replace(/"/g, "").toLowerCase();
            if (colName === "role") {
              roleIndex = i;
              break;
            }
          }
          if (roleIndex === -1) return [];
          var roles = [];
          for (var j = 0; j < result.data.length; j++) {
            roles.push(result.data[j][roleIndex].replace(/"/g, ""));
          }
          return roles;
        } else {
          return [];
        }
      }
    }

JWT 生成のロジック

// ---------------------------------------
// Load jsrsasign Library. Used to generate jwt token.
// ---------------------------------------
var window = {}; 
var navigator = { userAgent: USER_AGENT };
const cdnjs =
"https://cdnjs.cloudflare.com/ajax/libs/jsrsasign/11.1.0/jsrsasign-all-min.js";
const expectedSha384 = "SH7pjrbd3mK22fzapFxO/BenIdDWyTBCpFmFS7eWWCS54rs6zuKWVYJ+PmmIDEkZ";
function verifyScript() {
  const response = UrlFetchApp.fetch(cdnjs);
  const content = response.getContentText();

  const actualHash = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_384,
    content
  );
  const actualSha384 = Utilities.base64Encode(actualHash);

  if (actualSha384 !== expectedSha384) {
    throw new Error('Script integrity check failed. Expected: ' + expectedSha384 + ' but got: ' + actualSha384);
  }

  return true;
}
if (verifyScript()) eval(UrlFetchApp.fetch(cdnjs).getContentText()); // jsrsasignの読み込み

class SnowflakeConnector {
  ..省略
  generateJWT() {
    var prvKeyObj = KEYUTIL.getKey(this.encryptedPrivateKeyPEM, this.passphrase);
    var unencryptedPrivateKeyPEM = KEYUTIL.getPEM(prvKeyObj, "PKCS8PRV");
    
    prvKeyObj.isPublic = true;
    var pubPEM = KEYUTIL.getPEM(prvKeyObj, "PKCS8PUB");
    var pubContent = pubPEM.replace(/-----.*-----/g, "").replace(/\s+/g, "");
    var derBytes = Utilities.base64Decode(pubContent);
    var digestBytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, derBytes);
    var fingerp = Utilities.base64Encode(digestBytes);

    var header = { alg: "RS256", typ: "JWT" };
    var qualifiedUsername = this.account + '.' + this.user;
    var payload = {
      iss: qualifiedUsername + ".SHA256:" + fingerp,
      sub: qualifiedUsername,
      iat: Math.floor(new Date().getTime() / 1000),
      exp: Math.floor(new Date().getTime() / 1000) + 3600
    };

    var token = KJUR.jws.JWS.sign("RS256", header, payload, unencryptedPrivateKeyPEM);
    return token;
  }
}

前述の通り、Snowflake の Snowflake SQL API は OAuth認証や Key-Pair認証などの認証方式に対応していますが、ユーザー操作を極小化するため、Key-Pair認証を採用しました。

この JWT は snowflake.js 内の generateJWT() 関数で生成されます。

jsrsasign ライブラリを CDN 経由で読み込み
非公開鍵を密言解析して PEM として使用
SHA-256 でフィンガープリントを設定
署名に RS256 を用い、1時間有効なトークンを生成

これにより、この JWT を Authorization ヘッダに含めることで、Snowflake SQL API に安全にアクセスできるようになります。

クエリ実行フロー

ユーザーから SQL とパラメータを受け取ると、SnowflakeConnector.executeQuery() を通じて次のようなフローで実行されます：

function getData(request) {
  var fetchType = request.configParams.fetchType;
  var limit = fetchType == 'fetch_limit';
  var query = request.configParams.query;
  var warehouse = request.configParams.warehouse;
  var snowflakeConnector = new SnowflakeConnector(request.configParams.role);

  var statementResult = snowflakeConnector.executeQuery({
    query: query,
    warehouse: warehouse
  }, QUERY_TAG, limit);

ユーザーが入力したクエリとロールの情報を取得し、SnowflakeConnector のインスタンスを初期化します。
executeQuery() メソッド内で requestStatement() を呼び出し、JWT認証を付与してSnowflake SQL API（/api/v2/statements）にPOSTリクエストを送信します。
レスポンスコードが200の場合は即座に結果を返却し、202の場合は statementStatusUrl に対してポーリングを行います。

while (true) {
  Utilities.sleep(3000);
  var checkResponse = UrlFetchApp.fetch(SNOWFLAKE_BASE_URL + statusUrl, checkOptions);
  if (checkResponse.getResponseCode() === 200) {
    return 結果;
  }
}

結果の statementHandle と partitionInfo を取得後、getPartitionedStatement() を使用して複数のチャンクデータを取得します。
データはLooker Studioが指定したフィールド順に整列して返却します。

これにより、ステートレスで段階的なデータ取得を実現し、大規模データでも安定したレスポンスを提供できる設計となっています。

requestStatement() でSQL API /api/v2/statements にPOSTリクエストを送信
レスポンスが202の場合、status URLに対してポーリングを実行
実行が完了したら、statement handleとpartition情報を返却
getPartitionedStatement() で各チャンクを一括取得

この設計により、大規模データにも対応しながら、preview mode（fetch_limit）では最初の1MBのみを取得することも可能です。

Looker Studio 側の処理

このコミュニティコネクタには、ユーザーが実行可能なロールやクエリを安全かつ動的に判断する仕組みを導入しています。Session.getActiveUser().getEmail()で現在ログイン中のユーザーのメールアドレスを取得し、Snowflake側でSHOW GRANTS TO USERを実行することで、そのユーザーに割り当てられたロールを取得します。

const userEmail = Session.getActiveUser().getEmail();
const roles = snowflakeConnector.getUserRoles(userEmail);

得られたロールのうち、組織が許可した Functional Role のみをセレクトボックスに表示します。
許可されたロールでのみクエリ実行が可能であり、不正アクセスや誤操作の防止につながります。

このように、ユーザーごとに実行可能なクエリとロールを厳密にコントロールすることで、柔軟性とセキュリティのバランスを保ったデータ可視化基盤を構築しています。

コミュニティコネクタの主要な機能は以下の3つの関数で構成されています

getConfig()
- SQL 入力エリア
- FetchType 選択
- Warehouse 選択
- Snowflake の show grants to user 結果に基づくロール UI の動的生成
getFields() / getSchema()
- SQL からスキーマ情報の取得
- Snowflake の data type から Looker Studio の FieldType へのマッピング
getData()
- 必要なフィールドの抽出
- schema に従ったレコードの並び替えと返却

これらの機能により、ユーザーはSQLを記述するだけでデータの可視化が可能になります。権限は多段階構造で設計されており、このコミュニティコネクタではユーザーに付与されたFunctional Roleから選択できます。機密情報にアクセスできる特定のロールは限られたユーザーにのみ付与され、閲覧可能なデータ範囲が制限されています。この仕組みにより、データ取得からスキーマ抽出、BIでのビジュアライゼーションまでをシームレスに実行することが可能です。