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
SUMorCOUNTacross 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 statusValidationException: Unsupported clause: GROUP BYPartiQL と 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)。
- アプリが生のアイテム(チケット、ダウンロード、イベント)を書きます。集計ロジックはなし。
- DynamoDB Streams が書き込みをストリームレコードとして捕捉します。
- ストリームに接続された Lambda がグループ(ステータス、月、カテゴリ…)を導出し、対応する集計アイテムに
ADDします。 - 事前計算された集計を読み取ります。しばしば、ロールアップアイテムだけをインデックスするスパース GSI を通して読むので、「今月のトップ 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 BY | DynoTable 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 をライブテーブルに向けましょう。