Roche が Google スプレッドシートと Amazon Redshift Data API でデータへのアクセスを民主化した方法

この記事は Roche のグローバルプラットフォームおよび ML エンジニアリングチームから Dr. Yannick Misteli、João Antunes、Krzysztof Wisniewski を筆頭著者として共同執筆されました。

Roche は世界中で事業を展開するスイスの多国籍ヘルスケア企業です。Roche は世界最大の製薬会社であり、がん治療の世界的なリーディングプロバイダーです。

この記事では、Roche のグローバルプラットフォームおよび機械学習 (ML) エンジニアリングチームが、Amazon Redshift Data API を使用して Google スプレッドシート (gSheet) で Amazon Redshift データウェアハウス内のデータへのアクセスを民主化した方法について説明します。

ビジネスニーズ

Go-To-Market (GTM) は、Roche が顧客を理解し、顧客のニーズを満たす価値あるサービスを創り出し、提供するための戦略を指します。GTM により、医療エコシステムの理解を深め、患者、医師、病院により良いサービスを提供できるようになります。GTM は医療従事者（Health Care Professionals: HCP）だけでなく、患者、コミュニティ、保健当局、支払人、医療提供者、学界、競合他社などで構成される大規模なヘルスケアエコシステムにも及びます。データと分析は、実用的なインサイトにより社内外のステークホルダーの意思決定プロセスをサポートするために不可欠です。

このミッションのために、Roche は最新のデータスタックを採用し、クラウドにスケーラブルなソリューションを構築しました。

データの民主化を推進するには、ビジネスリーダーへ洗練されたダッシュボードを提供したり、データサイエンティストに SQL アクセスを提供するだけでなく、データを必要とするビジネスユーザーのニーズにも対応する必要があります。この目的で、ほとんどのビジネスユーザー（アナリストなど）は Excel （Roche の場合は gSheet）をデータ分析に活用しています。

これらの gSheet ユーザーに Amazon Redshift のデータへのアクセスを提供することは、難しい問題です。データ利用者がセルフサービスで分析できる強力で柔軟なツールがなければ、ほとんどの組織は最新のデータスタックのメリットを享受できないでしょう。この問題を解決するために、SQL スキルセットを持たないすべてのデータアナリストに、最も使い慣れたアプリケーション内のデータに簡単にアクセスして操作できる手段を提供したいと考えています。

Roche の GTM 組織は、Redshift Data API を使用して gSheet と Amazon Redshift をシンプルに統合して、分析とクエリに対するビジネスユーザーのデータニーズを促進しています。Amazon Redshift Data API を使用すると、既存の方法、クラウドネイティブ、コンテナ化されたサーバーレスなウェブサービスベースのアプリケーション、およびイベント駆動型アプリケーションといったあらゆる方法で、Amazon Redshift のデータに簡単にアクセスできます。Data API により、Python、Go、Java、Node.js、PHP、Ruby、C++ などの AWS SDK でサポートされている言語からのデータアクセス、取り込み、および出力がシンプルになるため、インフラストラクチャの管理ではなくアプリケーションの構築に集中できます。Amazon Redshift Data API を使用して開発したプロセスは、データウェアハウスの経験を必要とせずに、新規ユーザーの参入障壁を大幅に引き下げました。

ユースケース

この記事では、Amazon Redshift と gSheet を統合してデータセットを gSheet に直接取り込む方法について説明します。これらのメカニズムは、 Amazon Redshift Data API と Google Apps Script を使用することで容易になります。 Google Apps Script は、gSheet とそれに含まれるデータをプログラムから操作および拡張する方法です。

アーキテクチャ

Apps Script がネイティブにクラウドベースの JavaScript プラットフォームであるため、 jQuery-Builder などの公開されている JS ライブラリを含めることができます。

jQuery Builder ライブラリは、使いやすいグラフィカルユーザーインターフェイスを介して、標準 SQL クエリの作成を容易にします。Redshift Data API を使用すると、クエリを実行してデータを直接 gSheet に取得できます。次の図は、技術的な視点でのプロセス全体を示しています:

AppsScript は JavaScript プラットフォームですが、AWS が提供するブラウザ用の SDK（NodeJS および React）は、基盤となるインフラストラクチャに固有の特定のプロパティを必要とするため、Google プラットフォームでは使用できません。利用可能な API 呼び出しを通じて、AWS リソースの認証とアクセスが可能です。それを達成する方法の例がこちらにあります。

上記のリンク例のコードを使用して、アクセスキー ID とシークレットアクセスキーを使用して AWS へのリクエストを認証できます。このプログラムにアクセス権を付与する場合、または一時的な認証情報を持つロールを引き受ける場合は、最小権限の原則に従うことをお勧めします。Redshift オブジェクト (データベース、スキーマ、テーブル) に対するパーミッションセットはユーザーごとに異なるため、各ユーザーには独自の認証情報があります。これらの認証情報は AWS Secrets Manager サービスでセキュアに保管されます。そのため、プログラムには、AWS Secrets Manager からシークレットを取得し、Redshift Data API に対してクエリを実行できる一連のアクセス許可が必要です。

AppScript で Data API を使用するコード例

このセクションでは、既存のデータを新しい gSheet ドキュメントに取得する方法を説明します。このセクションでは、jQuery-Builder ライブラリのデータを解析する方法については説明しません。これは、この記事の主なスコープに含まれていないためです。

<script src="https://cdn.jsdelivr.net/npm/jQuery-QueryBuilder/dist/js/query-builder.standalone.min.js"></script>

AWS コンソールで、Secrets Manager に移動し、Redshift Cluster にアクセスするためのデータベースの認証情報（ユーザー名とパスワード）を保存する新しいシークレットを作成します。これらは、gSheet ユーザーに Redshift アクセスを許可するために使用されます。

AWS コンソールで、プログラムから利用する新しい IAM ユーザーを作成し、対応するアクセスキー認証情報を生成します。このユーザーに必要なポリシーセットは、前のステップで作成されたシークレットを AWS Secrets Manager サービスから読み取り、Redshift Data API にクエリを実行できることだけです。

以下はポリシードキュメントです:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": [
        "secretsmanager:GetSecretValue",
        "secretsmanager:DescribeSecret"
      ],
      "Resource": "arn:aws:secretsmanager:*::secret:*"
    },
    {
      "Sid": "VisualEditor1",
      "Effect": "Allow",
      "Action": "secretsmanager:ListSecrets",
      "Resource": "*"
    },
    {
      "Sid": "VisualEditor2",
      "Effect": "Allow",
      "Action": "redshift-data:*",
      "Resource": "arn:aws:redshift:*::cluster:*"
    }
  ]
}

Google Apps Script コンソールにアクセスします。こちらで利用可能なコードで aws.gs ファイルを作成します。これにより、アクセスキーとシークレットアクセスキーを指定して、AWS サービスに対する認証済みリクエストを実行できます。
ステップ 3 で作成したアクセスキーとシークレットアクセスキーを AWS 変数に指定します。
```
AWS.init("<ACCESS_KEY>", "<SECRET_KEY>");
```
Bash

AWS Secrets Manager から Redshift のユーザー名とパスワードを要求します:

function runGetSecretValue_(secretId) {
 
  var resultJson = AWS.request(
    	getSecretsManagerTypeAWS_(),
    	getLocationAWS_(),
    	'secretsmanager.GetSecretValue',
    	{"Version": getVersionAWS_()},
    	method='POST',
    	payload={          
      	"SecretId" : secretId
    	},
    	headers={
      	"X-Amz-Target": "secretsmanager.GetSecretValue",
      	"Content-Type": "application/x-amz-json-1.1"
    	}
  );
 
  Logger.log("Execute Statement result: " + resultJson);
  return JSON.parse(resultJson);
 
}

Amazon Redshift Data API を使用してテーブルにクエリを実行します:

function runExecuteStatement_(sql) {
 
  var resultJson = AWS.request(
    	getTypeAWS_(),
    	getLocationAWS_(),
    	'RedshiftData.ExecuteStatement',
    	{"Version": getVersionAWS_()},
    	method='POST',
    	payload={
      	"ClusterIdentifier": getClusterIdentifierReshift_(),
      	"Database": getDataBaseRedshift_(),
      	"DbUser": getDbUserRedshift_(),
      	"Sql": sql
    	},
    	headers={
      	"X-Amz-Target": "RedshiftData.ExecuteStatement",
      	"Content-Type": "application/x-amz-json-1.1"
    	}
  ); 
 
  Logger.log("Execute Statement result: " + resultJson);

結果を gSheet で表として表示できます:

function fillGsheet_(recordArray) { 
 
  adjustRowsCount_(recordArray);
 
  var rowIndex = 1;
  for (var i = 0; i < recordArray.length; i++) {  
       
	var rows = recordArray[i];
	for (var j = 0; j < rows.length; j++) {
  	var columns = rows[j];
  	rowIndex++;
  	var columnIndex = 'A';
     
  	for (var k = 0; k < columns.length; k++) {
       
    	var field = columns[k];       
    	var value = getFieldValue_(field);
    	var range = columnIndex + rowIndex;
    	addToCell_(range, value);
 
    	columnIndex = nextChar_(columnIndex);
 
  	}
 
	}
 
  }
 
}

完了したら、Apps Script をアドオンとしてデプロイして、組織全体のエンドユーザーが Amazon Redshift からスプレッドシートに直接データを取得する機能を活用できるようにします。Apps Script コードをアドオンとしてデプロイする方法の詳細については、こちらをご覧ください。

ユーザーが Google スプレッドシートにアクセスする方法

gSheet を開き、アドオンの管理 -> アドオンのインストールに進みます:
アドオンが正常にインストールされたら、「Addon」メニューを選択し、「Redshift Synchronization」を選択します。ダイアログが表示され、データのロード元となるデータベース、スキーマ、テーブルの組み合わせを選択するよう求められます。
目的のテーブルを選択すると、画面の右側に新しいパネルが表示されます。次に、テーブルから取得する列の選択、フィルタリング操作の適用、データへの集計の適用を求めるプロンプトが表示されます。
クエリを送信すると、アプリケーションスクリプトはユーザーの選択内容を Amazon Redshift Data API に送信されるクエリに変換します。その後、返されたデータは変換され、通常の gSheet テーブルとして表示されます:

セキュリティとアクセス管理

上記のスクリプトでは、AWS Secrets Manager と Google Apps Script が直接統合されています。上記のスクリプトは、現在認証されているユーザーの Google メールアドレスを抽出できます。この値と一連の注釈付きタグを使用して、Amazon Redshift クラスターに対して行われたリクエストを認証するために、スクリプトはユーザーの認証情報を適切に安全に取得できます。既存の Amazon Redshift クラスターに新しいユーザーをセットアップするには、こちらの手順に従います。ユーザーを作成したら、こちらの手順に従って、クラスター用の新しい AWS Secrets Manager シークレットを作成します。適切なタグが、対応するユーザーの Google メールアドレスとともに「メール」のキーで適用されていることを確認します。Redshift Data API を介して Redshift グループ、ユーザー、およびデータ共有を作成するために使用される設定例を次に示します:

connection:
 redshift_super_user_database: dev
 redshift_secret_name: dev_
 redshift_cluster_identifier: dev-cluster
 redshift_secrets_stack_name: dev-cluster-secrets
 environment: dev
 aws_region: eu-west-1
 tags:
   - key: "Environment"
 	value: "dev"
users:
 - name: user1
   email: user1@roche.com
 data_shares:
 - name: test_data_share
   schemas:
 	- schema1
   redshift_namespaces:
 	- USDFJIL234234WE
group:
 - name: readonly
   users:
 	- user1
   databases:
 	- database: database1
   	exclude-schemas:
     	- public
     	- pg_toast
     	- catalog_history
   	include-schemas:
     	- schema1
   	grant:
     	- select

運用メトリクスと改善

Redshift でホストされているライブデータへの直接アクセスをビジネスユーザーに提供し、真のセルフサービスにすることで、プラットフォームチームがデータ抽出やその他のメカニズムを提供して最新の情報を配信する負担を軽減できます。さらに、異なるファイルやバージョンのデータを循環させないことで、異なる主要数値や KPI を報告するビジネスリスクを軽減し、全体的なプロセスを効率化できます。

私たちは GTM でのこのアドオンの最初の成功を受けて，より多くの利用者向けに拡大してく予定です。将来的には数百人のユーザーにすべての内部データと公開データを提供したいと考えています。

まとめ

この記事では、新しい Amazon Redshift テーブルを作成し、既存の Redshift テーブルを Google スプレッドシートに取り込み、ビジネスユーザーがデータを簡単に統合して操作できるようにする方法を学びました。この統合はシームレスで、Amazon Redshift Data API が Google スプレッドシートと Amazon Redshift などの外部アプリケーションとの統合がいかに簡単に行えるかを実証しました。上記のユースケースの概要は、Amazon Redshift Data API を適用してユーザーと Amazon Redshift クラスター間のやり取りを簡素化する方法のほんの一例です。

原文はこちらです。
本ブログは Solutions Architect の宮田が翻訳しました。

Amazon Web Services ブログ

Roche が Google スプレッドシートと Amazon Redshift Data API で データへのアクセスを民主化した方法