中級読了 6 分

DynamoDB GROUP BY:代わりに集計する方法

DynamoDB に GROUP BY はありません。SUM、AVG、MIN、MAX もありません。組み込みの集計はマッチしたアイテムのカウント Select=COUNT ひとつだけで、それも評価するすべてのアイテムを読み取ります。PartiQL はどれも追加しません。

DynamoDB で GROUP BY はできるのか?

いいえ。API も PartiQL もグループ化や合計はできないので、集計をどこで計算するかを自分で決めます。書き込み時か、下流(Streams のロールアップや S3 エクスポート)か、読み取り後かです。

  • 書き込み時に事前計算する。 DynamoDB は "doesn't natively support aggregation operations like SUM or COUNT across items," なので、AWS はデータが変わるにつれて事前計算するよう勧めています(AWS: materialized aggregation)。
  • エクスポートからレポートする。 テーブルを S3 にエクスポートし、Athena で GROUP BY を実行します。エクスポートはテーブルのキャパシティを読み取りません。
  • 読み取りが 1 つのパーティションなら、読み取り後にグループ化する。 フルの Scan に対して行うと、実行のたびにテーブル全体の対価を支払います。
  • DynoTable の SQL Workbench で探索する。 ライブテーブルから読み取った行に対して、クライアント側で GROUP BY を実行します。

なぜ DynamoDB で集計が難しいのか

Query と Scan はアイテムを返します。それらを数値に畳み込みはしません。Scan はテーブルを 1 ページ 1 MB ずつ読みます。

"DynamoDB considers the size of the items that are evaluated, not the size of the items returned" (AWS)なので、FilterExpression は課金を下げずに結果を絞るだけです。

PartiQL は同じエンジンを継承しています。その SELECT が受け付けるのは WHERE と ORDER BY だけなので(文法と、拒否の再現)、グループ化した文は何も読まないうちに失敗します。

SELECT COUNT(*) FROM "tickets" GROUP BY status
ValidationException: Unsupported clause: GROUP BY

PartiQL と SQL の残りの差については、PartiQL vs SQL を参照してください。

パターン 1:書き込み時に集計する(アトミックカウンター)

グループを前もって知っているなら(ステータスごとのカウント、アカウントごとの作業時間、月ごとのダウンロード)、カウンターアイテムを保持し、書き込みごとにそれを更新します。

ADD を使います。数値とセットで機能し、 "avoids read-modify-write race conditions," なので、同じカウンターをインクリメントする 2 つの書き手が互いを上書きすることはありません(AWS)。

UpdateItem
Key                         { pk: "STATS#tickets", sk: "status#closed" }
UpdateExpression            "ADD ticketCount :one"
ExpressionAttributeValues   { ":one": 1 }

これがあなたの SELECT COUNT(*) … GROUP BY status で、すでに計算済みで 1 回の GetItem で読めます。代償として、書き込み時にグルーピングキーを知っている必要があり、カウンターの更新が書き込みパスに乗ります。

書き込みとカウンターをまとめておく。 2 つの別々の呼び出しは、その間でアプリがクラッシュするとずれます。両方を 1 つの TransactWriteItems に入れましょう。"either all of them succeed, or all of them fail" (AWS)。

TransactWriteItems   ClientRequestToken: ticket-1001
  Put     Key { pk: "TICKET#1001", sk: "TICKET" }, status: "OPEN", minutesSpent: 120
    ConditionExpression  attribute_not_exists(pk)
  Update  Key { pk: "STATS#tickets", sk: "status#open" }
    UpdateExpression     ADD ticketCount :one
    Values               :one = 1

リトライを安全にする。 ADD はべき等ではありません。ネットワークエラー後のリトライは 2 回カウントしうります。ClientRequestToken は同一のリトライを 10 分間ノーオペレーションにし、 attribute_not_exists はそのチケットのそれ以降の再実行をキャンセルします。

グループが変わったらカウントを移す。 そのガードがカバーするのはチケットの作成だけです。チケットが OPEN から CLOSED に移るときは、チケットと両方のカウンターを、古いステータスを条件に 1 つのトランザクションで更新します。

TransactWriteItems   ClientRequestToken: ticket-1001-close
  Update  Key { pk: "TICKET#1001", sk: "TICKET" }
    UpdateExpression     SET #s = :new
    ConditionExpression  #s = :old
    Names                #s = status
    Values               :new = "CLOSED", :old = "OPEN"
  Update  Key { pk: "STATS#tickets", sk: "status#open" }
    UpdateExpression     ADD ticketCount :minusOne
    Values               :minusOne = -1
  Update  Key { pk: "STATS#tickets", sk: "status#closed" }
    UpdateExpression     ADD ticketCount :one
    Values               :one = 1

この条件は再実行もキャンセルします。チケットが CLOSED になれば、同じ移動は ConditionalCheckFailed で失敗し、どちらのカウンターも変わりません。削除も同じ形です。 #s = :old を条件にしてチケットを Delete し、そのステータスのカウンターに ADD ticketCount :minusOne を加えます。どちらかのパスを省くと合計がずれます。

トランザクション書き込みは、アイテムあたり 1 ではなく 2 書き込みユニットかかります。トランザクションを使わない条件付き SET については、アトミックカウンターを参照してください。

1 つのカウンターに書き込みの大半が集中するなら、それを N 個のアイテムにシャーディングし、読み取り時に合計します(方法はホットパーティションを参照)。

パターン 2:DynamoDB Streams + Lambda のロールアップ

集計ロジックを書き込みパスに置きたくないとき、または書き込みがラップできない素の PutItem のときは、それを下流に移します。AWS はこれを materialized aggregation と呼んでいます(AWS: Using GSIs for materialized aggregation queries)。

  1. アプリが生のアイテム(チケット、ダウンロード、イベント)を書きます。集計ロジックはなし。
  2. が書き込みをストリームレコードとして捕捉します。
  3. ストリームに接続された Lambda がグループ(ステータス、月、カテゴリ…)を導出し、対応する集計アイテムに ADD します。
  4. 事前計算された集計を読み取ります。しばしば、ロールアップアイテムだけをインデックスする を通して読むので、「今月のトップ 10」は Limit 10 を伴う 1 回の Query です。

集計アイテムだけがインデックス対象の属性(例:Month)を持つので、生の行はインデックスに入らず、インデックスは "a small fraction of the total items in the table." になります。

3 種類のイベントすべてを処理する。 AWS のサンプルは INSERT だけを数えており、これは変更されないダウンロードには合います。OPEN から CLOSED に移るチケットは MODIFY として届き、削除されたチケットは REMOVE として届きます。

ストリームの StreamViewType を NEW_AND_OLD_IMAGES にして、各レコードが両方のイメージを持つようにしてから、カウントを移します。

import time
import boto3

ddb = boto3.client("dynamodb")
STATS_TABLE = "TicketStats"

def handler(event, context):
    for r in event["Records"]:
        old = r["dynamodb"].get("OldImage", {}).get("status", {}).get("S")
        new = r["dynamodb"].get("NewImage", {}).get("status", {}).get("S")
        if old == new:
            continue  # MODIFY that didn't move the ticket between groups
        ops = [{"Put": {  # marker: apply each stream record once
            "TableName": STATS_TABLE,
            "Item": {"pk": {"S": "APPLIED#" + r["eventID"]}, "sk": {"S": "-"},
                     "expiresAt": {"N": str(int(time.time()) + 2 * 86400)}},
            "ConditionExpression": "attribute_not_exists(pk)"}}]
        for status, delta in ((old, "-1"), (new, "1")):
            if status:
                ops.append({"Update": {
                    "TableName": STATS_TABLE,
                    "Key": {"pk": {"S": "STATS#tickets"}, "sk": {"S": "status#" + status}},
                    "UpdateExpression": "ADD ticketCount :d",
                    "ExpressionAttributeValues": {":d": {"N": delta}}}})
        try:
            ddb.transact_write_items(TransactItems=ops)
        except ddb.exceptions.TransactionCanceledException as e:
            if e.response["CancellationReasons"][0]["Code"] != "ConditionalCheckFailed":
                raise  # a real failure: let Lambda retry the batch
            # the marker exists: this record was already counted

マーカーがロールアップを正確にします。素の ADD だと "a retry would increment the count more than once," で、Lambda はエラー時にバッチ全体をリトライします。ここでは再実行されたレコードはマーカーの条件で失敗し、そのトランザクションはキャンセルされます。

ロールアップは専用のテーブルに置くので、Lambda が自分自身の書き込みでトリガーされることはありません。 expiresAt で TTL を有効にして古いマーカーを消します。ストリームレコードの寿命は 24 時間だけなので、 2 日分のマーカーがあればあらゆる再実行をカバーできます。

不正なレコードでストリームを止めない。 デフォルトでは、"a bad record can block processing on the affected shard for up to one day" (AWS Lambda)。リトライに上限を設け、失敗したバッチを分割し、失敗したものを保持しましょう。

aws lambda update-event-source-mapping \
  --uuid <mapping-uuid> \
  --maximum-retry-attempts 5 \
  --maximum-record-age-in-seconds 3600 \
  --bisect-batch-on-function-error \
  --destination-config '{"OnFailure":{"Destination":"arn:aws:s3:::my-failed-batches"}}'

S3 の送信先は呼び出しレコード全体を受け取ります。SQS と SNS が受け取るのはメタデータだけです。そこに届いたものを再実行するか、ずれた合計を再カウント(パターン 3 または 4)で作り直します。

変更履歴そのものを 24 時間を超えて保持するには、Streams ガイドの永続アーカイブを参照してください。

ロールアップは結果整合性です。AWS は "a delay of a few seconds between a download being recorded and the aggregation being updated." と記しています。ダッシュボード、リーダーボード、トレンドカウンターにはそれで問題ありません。

パターン 3:Scan/Query 後のアプリ側グルーピング

あるいは、アイテムを読み、自分のコードでグループ化します。

groups = {}
resp = table.scan()                        # or query() for one partition
while True:
    for item in resp["Items"]:
        key = item.get("status")           # None for items without one
        groups[key] = groups.get(key, 0) + 1
    if "LastEvaluatedKey" not in resp:
        break
    resp = table.scan(ExclusiveStartKey=resp["LastEvaluatedKey"])

フルの Scan に対して行うと、実行のたびにテーブル内のすべてのアイテムを読み、レイテンシはテーブルとともに増大します。AWS は "scan and count at read time" を "only suitable for very small datasets where latency isn't a concern" と挙げています(AWS)。

Query で 1 つのパーティションに絞れば(1 つのアカウントのチケット)、アプリ側グルーピングは妥当です。読むのは 1 つのアイテムコレクションだけです。両者の差については Query と Scan を参照してください。

1 GB のテーブルのフル Scan は、結果整合性で約 131,000 読み取りリクエストユニット(1,048,576 KB ÷ 4 KB × 0.5)です。us-east-1 のオンデマンド定価で約 $0.016、強い整合性ならその倍です。

アイテムサイズ計算ツールでアイテムのサイズを測り、料金計算ツールでテーブルの料金を見積もりましょう。

パターン 4:S3 にエクスポートしてから Athena で GROUP BY

定期的なレポートには、SQL をコピーに対して実行します。エクスポートに必要なポイントインタイムリカバリを有効にしてから、エクスポートします。

aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:123456789012:table/tickets \
  --s3-bucket my-export-bucket \
  --export-format DYNAMODB_JSON

エクスポートは "don't consume read capacity units (RCUs)." です。フルエクスポートはテーブルサイズの GB 単位で、増分エクスポートは選んだ期間の変更データの GB 単位で課金されます(AWS)。

Athena のテーブルをエクスポートの data/ フォルダに向けます。各行は DynamoDB JSON の 1 アイテムなので、すべての属性はその型をキーとする struct になります(AWS の例)。

CREATE EXTERNAL TABLE tickets_export (
  Item struct<status: struct<S: string>, minutesSpent: struct<N: string>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://my-export-bucket/AWSDynamoDB/<export-id>/data/';

SELECT Item.status.S AS status,
       COUNT(*) AS tickets,
       SUM(CAST(Item.minutesSpent.N AS DECIMAL(18, 2))) AS minutes
FROM tickets_export
GROUP BY Item.status.S;

エクスポートはそれぞれ新しい ID の下に置かれるので、次のエクスポートの後はテーブルの参照先を変えます。

ALTER TABLE tickets_export
SET LOCATION 's3://my-export-bucket/AWSDynamoDB/<new-export-id>/data/';

数値の鮮度はエクスポートの時点のものです。

ライブテーブルへのアドホックなクエリや、別のデータソースへの JOIN には、 Amazon Athena DynamoDB コネクタ がテーブルそのものに対して SQL を実行します(SQL for DynamoDB でのセットアップ)。

コネクタは進みながらスキャンし、AWS はそのスキャンが "can consume a large number of read capacity units" と警告しています(AWS)。

どのパターンを使うか?

必要なもの…使うもの
ホットな読み取りパス上の既知のグループ合計パターン 1:アトミックカウンター(ADD)
書き込みパスに触れない集計パターン 2:Streams + Lambda のロールアップ
正確な合計、ずれなしすべての書き込みパスでパターン 1、またはマーカー付きのパターン 2
1 つのパーティションに絞ったカウントパターン 3:Query してからアプリでグループ化
SQL による定期的な BI / レポーティングパターン 4:S3 へのエクスポート + Athena
ライブデータに対する単発の GROUP BYDynoTable Workbench または Athena コネクタ

DynoTable の SQL Workbench で GROUP BY を直接実行する

上記のパターンは、本番で集計を提供するためのものです。テーブルを探索しているとき(「今この瞬間、ステータスごとにチケットはいくつ?」)、Lambda やエクスポートをデプロイしたくはありません。クエリをタイプしたいのです。

DynoTable の SQL Workbench は GROUP BY、COUNT、SUM、AVG、HAVING、 JOIN をライブテーブルに対して、読み取った行の上でクライアント側で実行します。DynamoDB の PartiQL エンドポイントが拒否する SQL です。

SELECT status, COUNT(*) AS tickets, SUM(minutesSpent) AS minutes
FROM "tickets"
GROUP BY status
HAVING SUM(minutesSpent) > 1000
ORDER BY minutes DESC

アイテムは API が許す方法で読み取り(WHERE がパーティションキーまたは GSI のパーティションキーを = で指定していれば Query、そうでなければ Scan)、それからグループ化します。ループのないパターン 3 で、DynamoDB のアクセスパターンのルールの中で 動きます。ステータスバーは実行前に読み取りを見積もります。

Execute は最初のページを取得し、ページが残っている間は集計列に 一部 バッジが付きます。最後まで実行(⌘⇧↩)はすべてのページを読み取って正確な合計を出します。ホットな読み取りパスには事前計算しましょう(パターン 1 または 2)。

PartiQL がやはり実行できないクロステーブルの結合については、DynamoDB JOIN を参照してください。この能力で GUI クライアントを比較していますか? DynamoDB GUI 比較 を参照してください。

よくある質問

DynamoDB PartiQL は GROUP BY をサポートしますか?いいえ。DynamoDB の PartiQL SELECT は WHERE と ORDER BY のみをサポートします。GROUP BY、 HAVING、集計関数、JOIN はありません。文法は SELECT … FROM … [WHERE …] [ORDER BY …] と文書化されています。

パーティションキーで GROUP BY できますか? DynamoDB でも PartiQL でもできません。「パーティションキーごと」が既知のアクセスパターンなら、キーごとに 1 つの集計アイテムをアトミックな ADD で維持する(パターン 1)か、Streams + Lambda でロールアップします(パターン 2)。

グループごとに SUM や AVG をするには? SUM:グループごとに実行中の合計を保持し、書き込み時に ADD します。AVG:合計とカウントを保存し、読み取り時に割ります。単発の AVG には、DynoTable の SQL Workbench か Athena DynamoDB コネクタを使いましょう。

partiql group by の回避策はありますか? PartiQL 側のものはありません。集計を事前計算(カウンターか Streams)してロールアップアイテムを SELECT するか、GROUP BY を別の場所で実行しましょう。アドホックな疑問には DynoTable の Workbench、定期的なレポートには S3 エクスポートに対する Athena です。


Lambda を書かずに自分のテーブルに対して GROUP BY を実行したいですか? DynoTable を試して SQL Workbench をライブテーブルに向けましょう。

著者: Dmitriy · 更新日