AWS Payments が Redash から Amazon Redshift クエリエディタ V2 に移行した話

この記事は How AWS Payments migrated from Redash to Amazon Redshift Query Editor v2 の翻訳記事です。

AWS Payments は、AWS 請求書の支払いというカスタマーエクスペリエンスを提供する AWS コマースプラットフォーム (CP) 組織の一部です。AWS のお客様が支払い方法と支払い設定を管理するのに役立ち、お客様が AWS にセルフサービスで支払いするのに役立ちます。
AWS Payments の機械学習、データ、分析 (MLDA) チームは、一連のスケーラブルなデータ、インサイト、ML 推論に関するサービス群を通じてデータ、ビジネスインサイト、因果推論と ML 推論を提供することで、支払いプロセスとパートナー全体でデータドリブンな意思決定を支援しています。

この投稿では、Amazon Redshift クエリエディタ V2 を使用して Amazon Redshift へのデータアクセスを民主化する方法について説明します。

背景

AWS Payments では、ユーザーが Amazon Redshift データウェアハウスに対して SQL クエリを作成して実行できるように Redash を使用していました。Redash は Web ベースの SQL クライアントアプリケーションで、クエリの作成と実行、クエリ結果の視覚化、チームとの共同作業に使用できます。

しかし、時間が経つにつれて Redash の運用と我々のニーズに乖離が生まれ始めました。
具体的には以下の要件がありました。

認証と認可
- データベースユーザーとパスワードを作成せずにデータにアクセスできる
- POSIX/LDAP 管理のグループ、ユーザーがツールにアクセスできる
- データベースへのユーザーアクセスを制限できる
ユーザーエクスペリエンス
- 選択したデータベースで SQL クエリを実行できる
- クエリを保存して後で再実行できる
- 動的 SQL クエリを記述し、入力パラメータに基づいてクエリを実行できる
- クエリ結果を CSV にエクスポートできる
- 保存したクエリを検索できる
- クエリを URL として他のユーザーと共有できる

上記観点で代替サービスを評価した結果、 Amazon Redshift クエリエディタ V2 を選択しました。

Amazon Redshift クエリエディタ V2

Amazon Redshift クエリエディタ V2 は次のような利点があります。

統合された Web ベースの分析用統合環境により、ユーザーは SQL を介してデータを探索、共有し、共同作業によるコラボレーションができるようになります
マネージドサービスのためインフラストラクチャの管理は不要です
ユーザーはシングルサインオン (SSO) を使用してクエリエディターにログインできます
ユーザーはフェデレーションアクセスを使用して Amazon Redshift に接続できます
保存したクエリをチームメンバーと安全に共有でき、共同作業が容易になります
Amazon Redshift クエリエディタチームがリリースする新機能をすぐに利用できます
クエリ履歴機能を使用して、保存したクエリに加えられた変更を追跡できます
パラメータ化された SQL クエリを記述でき、異なる値のクエリを再利用できます
チャート機能をオンにすると、クエリ結果をその場で視覚化できます
ノートブックを使用して、1 つのドキュメントで複数の SQL クエリを整理、注釈付けし、共有できます
各クエリを別々のタブで実行することで、複数のクエリを並行して実行できます

導入にあたってはいくつか課題がありましたが、以下の点で工夫をしています。

AWS アカウント内の他の AWS サービスへのユーザーアクセスを制限するために、 AWS Identity and Access Management (AWS IAM) ポリシー (付録を参照) を SAML IAM ロールにアタッチしています。ポリシーでは以下を考慮しています。
- クエリエディタ V2 にのみアクセスを限定する
- 特定のデータベース・グループに割り当てる
Amazon Redshift クエリエディタ V2 は現在、クロスアカウントの Amazon Redshift 接続をサポートしていません。ただし、Amazon Redshift のデータ共有を設定して、他の AWS アカウントから Amazon Redshift クラスターにアクセスできるようにしています。詳細については、「Amazon Redshift のクラスター間でのデータ共有」を参照してください。

アーキテクチャの概要

以下にアーキテクチャを示します。

以下のセクションでは、クエリエディターの設定と Redash クエリの移行手順を順を追って説明します。

前提条件

このソリューションを実装するには、ID プロバイダー (IdP) サービスを使用して Amazon Redshift クエリエディタ V2 へのフェデレーテッドアクセスを設定する必要があります。詳細については、以下の記事をご覧ください。

Amazon Redshift クエリエディタ V2 のセットアップ

クエリエディタを設定するには、次の手順を実行します。

読み取り専用アクセス権を持つ Amazon Redshift データベースグループを作成します。
AWS アカウントで Amazon Redshift クエリエディタ V2 にアクセスするための IAM ロールを作成し、ユースケースに基づいて必要な IAM ポリシーをアタッチします。詳細については、「AWS アカウントの設定」を参照してください。
IdP と AWS の間に信頼関係を構築します。
クエリを共有するには、プリンシパルタグ sqlworkbench-team を IAM ロールに追加します。詳細については、「クエリの共有」を参照してください。

Redash クエリを Amazon Redshift クエリエディタ V2 に移行する

このセクションでは、Redash クエリを Amazon Redshift クエリエディタ V2 に移行するさまざまな方法について説明します。

パラメータなしのクエリ

パラメータなしでクエリを実行するのは非常に簡単です。Redash からクエリをコピーしてクエリエディタに入力するだけです。

Redash で、保存したクエリに移動し、「ソースを編集」を選択します。
ソースクエリをコピーします。
Amazon RedShift クエリエディタ V2 では、クエリをエディタに入力し、[保存] アイコンを選択して、クエリにタイトルを付けます。

パラメータを使ったクエリ

Redash では、{{}} の間の文字列はパラメータとして扱われますが、Amazon Redshift クエリエディタ V2 は ${} を使用してパラメータを識別します。パラメータを含むクエリを移行するには {{ を ${ に、}} を } に置き換えます。

以下は、Redash のクエリの例を示しています。

以下は、Amazon RedShift クエリエディタ V2 での同じクエリを示しています。

Amazon Redshift クエリエディタ V2 ノートブックへのマルチパートクエリ

マルチパートクエリの場合は、Redash ダッシュボードの各セクションのクエリをコピーしてノートブックに追加します。Amazon Redshift クエリエディタ V2 のノートブックは、クエリを連続して実行します。クエリの説明を追加することもできます。

以下は、Redash ダッシュボードのクエリの例を示しています。

以下は、Amazon Redshift クエリエディタ V2 ノートブックのクエリを示しています。

まとめ

この投稿では、SSO と Amazon Redshift フェデレーテッドアクセスを備えた Amazon Redshift クエリエディタ V2 をセットアップし、Redash から Amazon Redshift クエリエディタ V2 に移行した方法について説明しました。このソリューションにより、サードパーティのアプリケーションとそのインフラストラクチャを維持するための運用コストが削減されました。

同様のユースケースがあり、Amazon Redshift クラスターのデータを探索するためのウェブベースのツールが必要な場合は、Amazon Redshift クエリエディタ V2 の使用を検討してください。

付録: カスタマー IAM ポリシー

このセクションでは、 SAML IAM ロールにアタッチした IAM ポリシーを示します。このポリシーでは、AWS アカウント内の他の AWS サービスへのアクセスを制限します。

query-editor-credentials-policy — 以下ポリシーでは、リージョン、アカウント、クラスターパラメータを指定して次のアクションを許可します。1) Amazon Redshift へのアクセス、2) クラスター認証情報の取得、3) ユーザー作成、4) データベースグループへの参加

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": "redshift:GetClusterCredentials",
            "Resource": [
                "arn:aws:redshift:<region>:<account>:cluster:<cluster>",
                "arn:aws:redshift:<region>:<account>:dbname:<cluster>/payments_beta",
                "arn:aws:redshift:<region>:<account>:dbuser:<cluster>/${redshift:DbUser}"
            ],
            "Effect": "Allow"
        },
        {
            "Action": "redshift:JoinGroup",
            "Resource": "arn:aws:redshift:<region>:<account>:dbgroup:<cluster>/payments_ro_users",
            "Effect": "Allow"
        },
        {
            "Action": "redshift:DescribeClusters",
            "Resource": "arn:aws:redshift:<region>:<account>:cluster:<cluster>",
            "Effect": "Allow"
        },
        {
            "Action": "redshift:CreateClusterUser",
            "Resource": "arn:aws:redshift:<region>:<account>:dbuser:<cluster>/${redshift:DbUser}",
            "Effect": "Allow"
        }
    ]
}

query-editor-access-policy — 以下ポリシーを参照

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": "redshift:DescribeClusters",
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "RedshiftPermissions"
        },
        {
            "Condition": {
                "StringEquals": {
                    "secretsmanager:ResourceTag/sqlworkbench-resource-owner": "${aws:userid}"
                }
            },
            "Action": [
                "secretsmanager:CreateSecret",
                "secretsmanager:GetSecretValue",
                "secretsmanager:DeleteSecret",
                "secretsmanager:TagResource"
            ],
            "Resource": "arn:aws:secretsmanager:::sqlworkbench!",
            "Effect": "Allow",
            "Sid": "SecretsManagerPermissions"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:CalledViaLast": "sqlworkbench.amazonaws.com"
                }
            },
            "Action": "tag:GetResources",
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "ResourceGroupsTaggingPermissions"
        },
        {
            "Action": [
                "sqlworkbench:CreateFolder",
                "sqlworkbench:PutTab",
                "sqlworkbench:BatchDeleteFolder",
                "sqlworkbench:DeleteTab",
                "sqlworkbench:GenerateSession",
                "sqlworkbench:GetAccountInfo",
                "sqlworkbench:GetAccountSettings",
                "sqlworkbench:GetUserInfo",
                "sqlworkbench:GetUserWorkspaceSettings",
                "sqlworkbench:PutUserWorkspaceSettings",
                "sqlworkbench:ListConnections",
                "sqlworkbench:ListFiles",
                "sqlworkbench:ListTabs",
                "sqlworkbench:UpdateFolder",
                "sqlworkbench:ListRedshiftClusters",
                "sqlworkbench:DriverExecute",
                "sqlworkbench:ListTaggedResources"
            ],
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "AmazonRedshiftQueryEditorV2NonResourceLevelPermissions"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:RequestTag/sqlworkbench-resource-owner": "${aws:userid}"
                }
            },
            "Action": [
                "sqlworkbench:CreateConnection",
                "sqlworkbench:CreateSavedQuery",
                "sqlworkbench:CreateChart"
            ],
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "AmazonRedshiftQueryEditorV2CreateOwnedResourcePermissions"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:ResourceTag/sqlworkbench-resource-owner": "${aws:userid}"
                }
            },
            "Action": [
                "sqlworkbench:DeleteChart",
                "sqlworkbench:DeleteConnection",
                "sqlworkbench:DeleteSavedQuery",
                "sqlworkbench:GetChart",
                "sqlworkbench:GetConnection",
                "sqlworkbench:GetSavedQuery",
                "sqlworkbench:ListSavedQueryVersions",
                "sqlworkbench:UpdateChart",
                "sqlworkbench:UpdateConnection",
                "sqlworkbench:UpdateSavedQuery",
                "sqlworkbench:AssociateConnectionWithTab",
                "sqlworkbench:AssociateQueryWithTab",
                "sqlworkbench:AssociateConnectionWithChart",
                "sqlworkbench:UpdateFileFolder",
                "sqlworkbench:ListTagsForResource"
            ],
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "AmazonRedshiftQueryEditorV2OwnerSpecificPermissions"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:ResourceTag/sqlworkbench-resource-owner": "${aws:userid}",
                    "aws:RequestTag/sqlworkbench-resource-owner": "${aws:userid}"
                },
                "ForAllValues:StringEquals": {
                    "aws:TagKeys": "sqlworkbench-resource-owner"
                }
            },
            "Action": "sqlworkbench:TagResource",
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "AmazonRedshiftQueryEditorV2TagOnlyUserIdPermissions"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:ResourceTag/sqlworkbench-team": "${aws:PrincipalTag/sqlworkbench-team}"
                }
            },
            "Action": [
                "sqlworkbench:GetChart",
                "sqlworkbench:GetConnection",
                "sqlworkbench:GetSavedQuery",
                "sqlworkbench:ListSavedQueryVersions",
                "sqlworkbench:ListTagsForResource",
                "sqlworkbench:AssociateQueryWithTab"
            ],
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "AmazonRedshiftQueryEditorV2TeamReadAccessPermissions"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:ResourceTag/sqlworkbench-resource-owner": "${aws:userid}",
                    "aws:RequestTag/sqlworkbench-team": "${aws:PrincipalTag/sqlworkbench-team}"
                }
            },
            "Action": "sqlworkbench:TagResource",
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "AmazonRedshiftQueryEditorV2TagOnlyTeamPermissions"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:ResourceTag/sqlworkbench-resource-owner": "${aws:userid}"
                },
                "ForAllValues:StringEquals": {
                    "aws:TagKeys": "sqlworkbench-team"
                }
            },
            "Action": "sqlworkbench:UntagResource",
            "Resource": "*",
            "Effect": "Allow",
            "Sid": "AmazonRedshiftQueryEditorV2UntagOnlyTeamPermissions"
        }
    ]
}

query-editor-notebook-policy — 以下ポリシーを参照

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "sqlworkbench:ListNotebooks",
                "sqlworkbench:ListNotebookVersions",
                "sqlworkbench:ListQueryExecutionHistory"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:RequestTag/sqlworkbench-resource-owner": "${aws:userid}"
                }
            },
            "Action": [
                "sqlworkbench:CreateNotebook",
                "sqlworkbench:ImportNotebook",
                "sqlworkbench:DuplicateNotebook"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:ResourceTag/sqlworkbench-resource-owner": "${aws:userid}"
                }
            },
            "Action": [
                "sqlworkbench:GetNotebook",
                "sqlworkbench:UpdateNotebook",
                "sqlworkbench:DeleteNotebook",
                "sqlworkbench:CreateNotebookCell",
                "sqlworkbench:DeleteNotebookCell",
                "sqlworkbench:UpdateNotebookCellContent",
                "sqlworkbench:UpdateNotebookCellLayout",
                "sqlworkbench:BatchGetNotebookCell",
                "sqlworkbench:AssociateNotebookWithTab",
                "sqlworkbench:ExportNotebook",
                "sqlworkbench:CreateNotebookVersion",
                "sqlworkbench:GetNotebookVersion",
                "sqlworkbench:CreateNotebookFromVersion",
                "sqlworkbench:DeleteNotebookVersion",
                "sqlworkbench:RestoreNotebookVersion"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Condition": {
                "StringEquals": {
                    "aws:ResourceTag/sqlworkbench-team": "${aws:PrincipalTag/sqlworkbench-team}"
                }
            },
            "Action": [
                "sqlworkbench:GetNotebook",
                "sqlworkbench:BatchGetNotebookCell",
                "sqlworkbench:AssociateNotebookWithTab"
            ],
            "Resource": "*",
            "Effect": "Allow"
        }
    ]
}

Amazon Web Services ブログ