0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@zumax

Redshift ServerlessのデータをQuickSightのSPICEにインポートしてビジュアル化するまでの手順を整理した

0
Posted at

背景・目的

前、Redshift ServerlessをCDKで構築し、テストデータのロードまで試しました。
今回は、そのRedshiftをデータソースとしてQuickSightに接続し、SPICEへのインポートからビジュアル作成・データ更新の反映までを一通り試します。

まとめ

下記に特徴をまとめます

特徴 説明
正式名称 SPICE (Super-fast, Parallel, In-memory Calculation Engine)
役割 QuickSightのインメモリエンジン。データソースからインポートしたデータを保持し、分析クエリを高速処理
モード選択 データセット作成時に「SPICE」か「直接クエリ」を選択
メリット クエリ高速化、データソースへの都度アクセス不要、インポート済みデータは追加コストなしで再利用可能
暗号化 Enterprise Editionでは保管時暗号化に対応
容量 リージョンごとに割り当て。同一アカウント・リージョン内の全ユーザーで共有。管理者が追加購入・解放可能
データ更新 手動更新、スケジュール更新、増分更新に対応
サイズ計算 論理サイズで算出。数値・日付: 12B/セル、文字列: 24B + UTF-8長/セル

概要

SPICEへのデータのインポート

下記を基に整理します

  • SPICE (Super-fast, Parallel, In-memory Calculation Engine) は、QuickSightのインメモリエンジン

  • データソースからデータをインポート（取り込み）して保持し、分析クエリを高速に処理します。直接クエリと異なり、データソースへの都度アクセスが不要なため、速度とコストの両面でメリットがある

  • Enterprise Editionでは保管時暗号化にも対応

  • データの更新は手動またはスケジュールで行う

  • データセット作成時に「SPICE」か「直接クエリ」を選択できる。SPICEを選ぶメリットは下記の3つ

    • 分析クエリの処理が高速
    • 直接クエリの待ち時間が不要
    • インポート済みデータは追加コストなしで何度でも再利用可能（直接クエリはクエリごとに課金）

  • SPICE容量はリージョンごとに割り当てられ、同一アカウント・リージョン内の全ユーザーで共有される

  • デフォルトではホームリージョンにのみ容量が割り当てられ、他リージョンは別途購入が必要

  • 管理者は容量の確認・追加購入・解放が可能

SPICE データセットのサイズの推定

  • SPICEのデータセットサイズは「論理サイズ」で計算され、ソーステーブルのサイズとは異なる
  • データ型変換・計算列の定義後にマテリアライズされた結果が対象
  • サイズの計算式は以下の通り
    • 数値・日付: 12バイト/セル
    • 文字列: 24バイト + UTF-8テキスト長/セル（インデックス作成のため数値より大きい）

SPICE専用機能

下記を基に整理します

Amazon Quick Sight の SPICE (Super-fast, Parallel, In-memory Calculation Engine) では、計算負荷の高い特定のデータ準備機能を使用できます。これらの変換は、クエリ時に実行されるのではなく、最適なパフォーマンスのために SPICE でマテリアライズされます。
一部の加工機能（Append, Aggregate, Pivot, Unpivot, Divergence）も持っています。

実践

Redshift環境の整備

  1. 下記のSQLを作成します
-- スキーマ作成
CREATE SCHEMA IF NOT EXISTS dm;

-- 月別カテゴリ売上サマリ
CREATE TABLE IF NOT EXISTS dm.monthly_sales_summary (
  sales_month   INTEGER      NOT NULL,
  category      VARCHAR(50)  NOT NULL,
  region        VARCHAR(50)  NOT NULL,
  quantity      INTEGER      NOT NULL,
  revenue       NUMERIC(12,2) NOT NULL
)
DISTSTYLE AUTO SORTKEY AUTO;

-- サンプルデータ
INSERT INTO dm.monthly_sales_summary VALUES
(202601, 'Electronics', 'Tokyo',    120, 1800000.00),
(202601, 'Electronics', 'Osaka',     85, 1275000.00),
(202601, 'Clothing',    'Tokyo',    200,  600000.00),
(202601, 'Clothing',    'Osaka',    150,  450000.00),
(202601, 'Food',        'Tokyo',    500,  250000.00),
(202601, 'Food',        'Osaka',    420,  210000.00),
(202602, 'Electronics', 'Tokyo',    130, 1950000.00),
(202602, 'Electronics', 'Osaka',     90, 1350000.00),
(202602, 'Clothing',    'Tokyo',    180,  540000.00),
(202602, 'Clothing',    'Osaka',    160,  480000.00),
(202602, 'Food',        'Tokyo',    480,  240000.00),
(202602, 'Food',        'Osaka',    450,  225000.00),
(202603, 'Electronics', 'Tokyo',    140, 2100000.00),
(202603, 'Electronics', 'Osaka',     95, 1425000.00),
(202603, 'Clothing',    'Tokyo',    250,  750000.00),
(202603, 'Clothing',    'Osaka',    170,  510000.00),
(202603, 'Food',        'Tokyo',    520,  260000.00),
(202603, 'Food',        'Osaka',    400,  200000.00);

テーブルを作成

1.実行前のクエリを確認します。DMスキーマは0件です

  aws redshift-data execute-statement \
  --workgroup-name <workgroup-name> \
  --database <database-name> \
  --secret-arn <secret-arn> \
  --sql "SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'dm' ORDER BY tablename;" \
  --region ap-northeast-1


TotalNumRows: 0

2.テーブルを作成し、データを登録します

aws redshift-data execute-statement \
  --workgroup-name <workgroup-name> \
  --database <database-name> \
  --secret-arn <secret-arn> \
  --sql "$(cat 01_setup.sql)" \
  --region ap-northeast-1

Status: FINISHED

3.テーブル作成後の確認をします

 schemaname | tablename
 dm         | monthly_sales_summary
TotalNumRows: 1

4.データの確認をします

 sales_month | category    | region | quantity | revenue
 202601      | Clothing    | Osaka  |      150 |   450000.00
 202601      | Clothing    | Tokyo  |      200 |   600000.00
 202601      | Electronics | Osaka  |       85 |  1275000.00
 202601      | Electronics | Tokyo  |      120 |  1800000.00
 202601      | Food        | Osaka  |      420 |   210000.00
 202601      | Food        | Tokyo  |      500 |   250000.00
 202602      | Clothing    | Osaka  |      160 |   480000.00
 202602      | Clothing    | Tokyo  |      180 |   540000.00
 202602      | Electronics | Osaka  |       90 |  1350000.00
 202602      | Electronics | Tokyo  |      130 |  1950000.00
 202602      | Food        | Osaka  |      450 |   225000.00
 202602      | Food        | Tokyo  |      480 |   240000.00
 202603      | Clothing    | Osaka  |      170 |   510000.00
 202603      | Clothing    | Tokyo  |      250 |   750000.00
 202603      | Electronics | Osaka  |       95 |  1425000.00
 202603      | Electronics | Tokyo  |      140 |  2100000.00
 202603      | Food        | Osaka  |      400 |   200000.00
 202603      | Food        | Tokyo  |      520 |   260000.00
TotalNumRows: 18

Quick環境の整備

サインアップ

1.Quickに遷移します
2.「Amazon Quickにサインアップ」をクリックします
image.png
3.下記の内容を入力し、「アカウントを作成」をクリックします

  • アカウント名：任意
  • アカウント通知用メールアドレス：自分のアドレス
  • リージョン：任意
  • 認証方法：パスワードベースまたはシングルサインオン
  • 暗号化：AWS管理のKMS
    image.png
    4.「Amazon Quickに移動」をクリックします
    image.png
    5.ホーム画面が表示されました
    image.png

VPC接続環境を構築

初期化

1.cdk initで初期化します

npx cdk init app --language typescript

実装とテスト

1.VPCConnectionのオブジェクトを作ります

import * as cdk from 'aws-cdk-lib';
import * as ec2 from 'aws-cdk-lib/aws-ec2';
import * as iam from 'aws-cdk-lib/aws-iam';
import * as quicksight from 'aws-cdk-lib/aws-quicksight';
import { NagSuppressions } from 'cdk-nag';
import { Construct } from 'constructs';

export class QsVpcConnectionStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props?: cdk.StackProps) {
    super(scope, id, props);

    const vpcId = this.node.getContext('vpcId');
    const redshiftSgId = this.node.getContext('redshiftSgId');

    // 既存VPC・SGをインポート
    const vpc = ec2.Vpc.fromLookup(this, 'Vpc', { vpcId });
    const redshiftSg = ec2.SecurityGroup.fromSecurityGroupId(this, 'RedshiftSg', redshiftSgId);

    // QS VPC接続用SG
    const qsSg = new ec2.SecurityGroup(this, 'QsSg', {
      vpc,
      securityGroupName: 'qs-vpc-connection-sg',
      description: 'SG for QuickSight VPC Connection to Redshift',
    });

    // Redshift SGにQS SGからのインバウンドを追加
    redshiftSg.addIngressRule(qsSg, ec2.Port.tcp(5439), 'From QuickSight VPC Connection');

    // QSサービスロール（service-role/パス付きARN）
    const qsServiceRoleArn = `arn:aws:iam::${this.account}:role/service-role/aws-quicksight-service-role-v0`;
    const qsServiceRole = iam.Role.fromRoleArn(this, 'QsServiceRole', qsServiceRoleArn);
    qsServiceRole.addToPrincipalPolicy(new iam.PolicyStatement({
      actions: [
        'ec2:CreateNetworkInterface',
        'ec2:ModifyNetworkInterfaceAttribute',
        'ec2:DeleteNetworkInterface',
        'ec2:DescribeSubnets',
        'ec2:DescribeSecurityGroups',
      ],
      resources: ['*'],
    }));

    // QuickSight VPC接続
    const vpcConnection = new quicksight.CfnVPCConnection(this, 'QsVpcConnection', {
      awsAccountId: this.account,
      vpcConnectionId: 'qs-redshift-vpc-conn',
      name: 'QS to Redshift VPC Connection',
      subnetIds: vpc.isolatedSubnets.map(s => s.subnetId),
      securityGroupIds: [qsSg.securityGroupId],
      roleArn: qsServiceRoleArn,
    });

    // cdk-nag suppressions
    NagSuppressions.addResourceSuppressions(this, [
      {
        id: 'AwsSolutions-IAM5',
        reason: 'EC2 ENI operations do not support resource-level permissions. Wildcard is required for QuickSight VPC Connection.',
      },
    ], true);
  }
}

2.テストを書きます

import { Match, Template } from 'aws-cdk-lib/assertions';
import * as cdk from 'aws-cdk-lib';
import { QsVpcConnectionStack } from '../lib/qs-vpc-connection-stack';

const app = new cdk.App({
  context: {
    vpcId: 'vpc-dummy',
    redshiftSgId: 'sg-dummy',
  },
});
const stack = new QsVpcConnectionStack(app, 'TestStack', {
  env: { account: '123456789012', region: 'ap-northeast-1' },
});
const template = Template.fromStack(stack);

test('QS用SGが作成される', () => {
  template.hasResourceProperties('AWS::EC2::SecurityGroup', {
    GroupName: 'qs-vpc-connection-sg',
  });
});

test('Redshift SGにQS SGからの5439インバウンドが追加される', () => {
  template.hasResourceProperties('AWS::EC2::SecurityGroupIngress', {
    GroupId: 'sg-dummy',
    IpProtocol: 'tcp',
    FromPort: 5439,
    ToPort: 5439,
  });
});

test('QSサービスロールにENI権限が追加される', () => {
  template.hasResourceProperties('AWS::IAM::Policy', {
    PolicyDocument: Match.objectLike({
      Statement: Match.arrayWith([
        Match.objectLike({
          Action: Match.arrayWith(['ec2:CreateNetworkInterface']),
          Effect: 'Allow',
        }),
      ]),
    }),
  });
});

test('QuickSight VPC接続がservice-role付きARNで作成される', () => {
  template.hasResourceProperties('AWS::QuickSight::VPCConnection', {
    VPCConnectionId: 'qs-redshift-vpc-conn',
    RoleArn: 'arn:aws:iam::123456789012:role/service-role/aws-quicksight-service-role-v0',
  });
});

3.テストを実行します

npx jest
・・・
Test Suites: 1 passed, 1 total
Tests:       4 passed, 4 total
Snapshots:   0 total
Time:        6.991 s, estimated 7 s
Ran all test suites.

デプロイ

1.synthを実行し、テンプレートを生成します

  npx cdk synth \
    -c vpcId=vpc-XXXXXXXXXX \
    -c redshiftSgId=sg-XXXXXXXXXX \

2.deployします

CDK_DEFAULT_ACCOUNT=XXXXX CDK_DEFAULT_REGION=ap-northeast-1 \
  npx cdk deploy \
    -c vpcId=vpc-XXXXX \
    -c redshiftSgId=sg-XXXXX \
    --require-approval broadening

リソースの確認

  1. 上記で作成した、VPC接続の管理をみます
  2. Quickでプロファイルを開き自分の名前をクリックします
  3. 左ナビゲーションペインで、「VPC接続を管理」をクリックします
  4. VPC接続名などが確認できます

データソースの作成

セキュリティとアクセス許可

  1. Quickでプロファイルを開き自分の名前をクリックします
  2. 左のナビゲーションペインで「AWSリソース」をクリックします
    image.png
  3. シークレットを選択し、保存をクリックします
    image.png

CLIで作成

aws quicksight create-data-source \
  --aws-account-id XXXXXXXX \
  --data-source-id redshift-dev \
  --name "Redshift Dev" \
  --type REDSHIFT \
  --data-source-parameters '{"RedshiftParameters":{"Database":"<database-name>","Host":"<workgroup-endpoint>","Port":5439}}' \
  --credentials '{"SecretArn":"<secret-arn>"}' \
  --vpc-connection-properties '{"VpcConnectionArn":"arn:aws:quicksight:<region>:<account-id>:vpcConnection/qs-redshift-vpc-conn"}' \
  --region ap-northeast-1 \
  --output json

データソースを確認

  1. 左のナビゲーションペインで「データセット」をクリックします
  2. 「データソース」タブをクリックします
  3. みえました
    image.png

データセット作成 + SPICEインポート

データセットの作成

1.下記のコマンドでデータセットを作成します

aws quicksight create-data-set \
  --aws-account-id XXXXXXX \
  --data-set-id monthly-sales-summary \
  --name "Monthly Sales Summary" \
  --import-mode SPICE \
  --physical-table-map '{
    "salesTable": {
      "RelationalTable": {
        "DataSourceArn": "arn:aws:quicksight:ap-northeast-1:XXXXXXX:datasource/redshift-dev",
        "Schema": "dm",
        "Name": "monthly_sales_summary",
        "InputColumns": [
          {"Name": "sales_month", "Type": "INTEGER"},
          {"Name": "category", "Type": "STRING"},
          {"Name": "region", "Type": "STRING"},
          {"Name": "quantity", "Type": "INTEGER"},
          {"Name": "revenue", "Type": "DECIMAL"}
        ]
      }
    }
  }' \
  --permissions '[{
    "Principal": "arn:aws:quicksight:ap-northeast-1:XXXXXXX:user/default/admin/XXXXXXX",
    "Actions": [
      "quicksight:DescribeDataSet",
      "quicksight:DescribeDataSetPermissions",
      "quicksight:PassDataSet",
      "quicksight:DescribeIngestion",
      "quicksight:ListIngestions",
      "quicksight:UpdateDataSet",
      "quicksight:DeleteDataSet",
      "quicksight:CreateIngestion",
      "quicksight:CancelIngestion",
      "quicksight:UpdateDataSetPermissions"
    ]
  }]' \
  --region ap-northeast-1 \
  --output json

{
    "Status": 201,
    "Arn": "arn:aws:quicksight:ap-northeast-1:XXXXX:dataset/monthly-sales-summary",
    "DataSetId": "monthly-sales-summary",
    "IngestionArn": "arn:aws:quicksight:ap-northeast-1:XXXXX:dataset/monthly-sales-summary/ingestion/XXXXX",
    "IngestionId": "XXXXX",
    "RequestId": "XXXXX"
}

2.SPICEへのインジェスションも開始されています。確認します。18行が取り込まれました

aws quicksight describe-ingestion \
  --aws-account-id XXXXXX \
  --data-set-id monthly-sales-summary \
  --ingestion-id  XXXXXX  \
  --region ap-northeast-1 \
  --query 'Ingestion.{Status:IngestionStatus,RowsIngested:RowInfo.RowsIngested}' \
  --output json 2>&1 
{
    "Status": "COMPLETED",
    "RowsIngested": 18
}

確認

  1. データセットを確認します。見えました
    image.png

ビジュアル作成

  1. ナビゲーションペインで①データセットをクリック、②名前をクリックします
    image.png

  2. 右上の「分析で使用」をクリックします
    image.png

  3. インタラクティブシートでデフォルトのまま「作成」をクリックします
    image.png

  4. 下記を選択します。できました

  • Y軸：category
  • 値：revenue(合計)
  • グループ/色：sales_month

image.png

  1. 分析一覧にも表示されました
    image.png

Redshiftのデータを更新し、分析画面までの反映

データマートを更新し、Quickの画面が変化するまでを確認します

データマートの更新

1.データマートにレコードを追加します。202604のElectronicsのデータが追加されました

# 202604（4月）のデータを追加。既存は202601-03なので明確に区別できる
aws redshift-data execute-statement \
  --workgroup-name <workgroup-name> \
  --database dev-redshift-db \
  --secret-arn "arn:aws:secretsmanager:ap-northeast-1:XXXXXX:secret:redshift!XXXXXX" \
  --sql "INSERT INTO dm.monthly_sales_summary VALUES (202604, 'Electronics', 'Tokyo', 999, 9999999.00), (202604, 'Electronics', 'Osaka', 888, 8888888.00);" \
  --region ap-northeast-1 \
  --query 'Id' --output text

2.確認します

aws redshift-data describe-statement --id XXXXXX --region ap-northeast-1 --query 'Status' --output text

SPICEを更新

1.SPICEを更新します

aws quicksight create-ingestion \
  --aws-account-id XXXXXX \
  --data-set-id monthly-sales-summary \
  --ingestion-id refresh-$(date +%Y%m%d%H%M%S) \
  --region ap-northeast-1 \
  --output json

2.確認します。20行になりました

aws quicksight describe-ingestion \
  --aws-account-id XXXXXX \
  --data-set-id monthly-sales-summary \
  --ingestion-id refresh-XXXXXX \
  --region ap-northeast-1 \
  --query 'Ingestion.{Status:IngestionStatus,RowsIngested:RowInfo.RowsIngested}' \
  --output json


{
    "Status": "COMPLETED",
    "RowsIngested": 20
}

分析を確認

  1. オレンジの202604が追加されました。（更新されました）
    image.png

考察

今回、Redshiftにデータマートを作成し、QuickのSPICEへロードし、画面に反映するまでを試しました。

SPICEの更新タイミングについては、SPICEはスナップショット方式のため、Redshiftのデータを更新してもQSの表示は自動では変わらない。本番運用では日次バッチ後にスケジュール更新を設定するか、Step Functionsから create-ingestion APIを呼んでSPICE更新をトリガーする設計が必要になります。

aws quicksight create-ingestion \
  --data-set-id monthly-sales-summary \
  --ingestion-id refresh-$(date +%Y%m%d%H%M%S) \

今後も継続して試してみます。

参考

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?