DynamoDB のための SQL と PartiQL の限界
DynamoDB は PartiQL を通じてキーの形をした SQL には答えますが、リレーショナルなものには答えません。
JOIN、GROUP BY、集計はありません。このページでは PartiQL がカバーする範囲と、PartiQL では表現できないクエリを実行する方法をまとめます。
DynamoDB を SQL でクエリできるのか?
部分的には。AWS は PartiQL を "a SQL-compatible query language, to select, insert, update, and delete data in Amazon DynamoDB" と説明し、"Amazon DynamoDB supports a subset of the PartiQL query language." とも述べています。
つまり SELECT * FROM "tickets" WHERE ticketId = 'T-1001' は動きますが、JOIN、GROUP BY、
COUNT(*) は対象外です。それらには上に載せる SQL エンジンが必要です。Athena、Redshift へのゼロ ETL、
EMR 上の Hive、または DynoTable の SQL Workbench です。
DynamoDB 自体は NoSQL のキーバリュー・ドキュメントストアで、プライマリキー以外のスキーマもクエリプランナも持ちません。機能ごとの比較は PartiQL vs SQL にあります。
PartiQL はネイティブ操作にコンパイルされる
PartiQL は SQL 風のステートメントを、SDK が公開するデータプレーン操作にマッピングします。パーティションキーの等価条件を持つ SELECT はキーによる読み取りにコンパイルされ、それがなければ Scan になります。
AWS の SELECT リファレンスによると:
WHERE 句でパーティションキーによる等価条件または IN 条件が指定されていない場合、
SELECTステートメントを使用するとテーブル全体のスキャンが発生する可能性があります。
Query と Scan を支配するアクセスパターンのルールは依然として適用されます。PartiQL はそれらをおなじみの構文の裏に隠しているだけです。ステートメントごとのマッピングは
PartiQL vs SQL にあります。
フルスキャンは読み取ったすべてのアイテムを計上します。オンデマンドでは、500 MB のテーブルを結果整合性でスキャンすると、WHERE が何を残すかに関係なく、約 64,000 読み取りリクエストユニット(512,000 KB ÷ 4 KB × 0.5)かかります。自分のケースは料金計算ツールで見積もりましょう。
PartiQL がカバーするもの
DynamoDB の PartiQL は 4 つの DML/クエリステートメントをサポートします。
- SELECT: アイテムを読む(キーによる読み取りまたは
Scan) - INSERT: アイテムを 1 つ追加する(
PutItem)。上書きはしません。既存のキーに対してはDuplicateItemExceptionで失敗します。 - UPDATE: 既存のアイテムを 1 つ変更する(
UpdateItem)。新規作成はしません。存在しないキーに対してはConditionalCheckFailedExceptionで失敗します。 - DELETE: アイテムを 1 つ削除する(
DeleteItem)
アップサートはないため、上書きまたは作成には、ネイティブの PutItem か UpdateItem を使います。
PartiQL はトランザクションとバッチ操作もサポートします。整った読み取りは、等価条件または IN でパーティションキーを指定します。
SELECT ticketId, minutesSpent
FROM "tickets"
WHERE ticketId IN ['T-1001', 'T-1002', 'T-1003'] ORDER BY ticketId DESCORDER BY は使えますが、AWS のリファレンスは並べ替えのキーを "a hash key
or a sort key" に制限しています。つまりパーティションキーまたはソートキーであって、任意の列ではありません。
それ以外の順序で並べたいなら、コード側でソートするか、その順序をソートキーまたは GSI のソートキーとしてモデリングします。コピー&ペーストで使えるステートメントは PartiQL の例 を参照してください。
PartiQL ができないこと
これらは開発者が「SQL」に最もよく期待するもので、PartiQL はそのどれもサポートしません。
JOINはありません。 PartiQL のSELECT構文は単一のFROM {{table}}[.{{index}}]、つまり 1 つのテーブルまたは 1 つのインデックスです。それぞれの側をキーでクエリしてコードでマージするか、1 回の読み取りで両方が返るように設計します(JOIN ガイド)。GROUP BYはありません。 文法にありません。 GROUP BY ガイドにあるように、グループは書き込み時に事前計算します。- 集計関数はありません。
PartiQL の関数リファレンスは "Aggregate functions" の下にちょうど 1 つの関数
SIZEを挙げています。これは単一のアイテムについて属性のサイズをバイト単位で返します。AWS はこう述べています。"Any SQL functions that are not included in this list are not currently supported in DynamoDB." カウントや合計については COUNT と SUM を参照してください。 LIKE、サブクエリ、UNION、ウィンドウ関数はありません。 パターンマッチングにはcontains/begins_withを使います。サブクエリの代わりには、内側のSELECTを先に実行し、そのキーをIN [...]リスト(パーティションキーの値は最大 50 個)で渡します。UNIONの代わりには、それぞれのSELECTを実行してコードでマージします。ウィンドウ関数には、後述の SQL エンジンのいずれかが必要です。
そのため、「先月のアカウント別サポート時間」は、ほかのデータベースなら 1 行の GROUP BY ですが、
PartiQL では書けません。ネイティブには、チケットが処理されるたびにアカウントごとの累計を保持するか、その月のチケットを読み出してコードで合計します。
DynamoDB で本物の SQL を実行する方法
JOIN、GROUP BY、集計を得るには、テーブルの上に SQL エンジンが必要です。AWS がドキュメント化しているものが 3 つあり、DynoTable がデスクトップの選択肢を加えます。
| 選択肢 | SQL が実行される場所 | 向いている用途 |
|---|---|---|
| Athena DynamoDB コネクタ | Athena。Lambda 経由で稼働中のテーブルを読む | アドホックな分析とダッシュボード。運用するクラスタなし |
| Amazon Redshift へのゼロ ETL | Redshift。15〜30 分ごとに更新されるコピー上 | 定期レポート。稼働中のテーブルに負荷をかけない |
| Amazon EMR 上の Hive | 自分で運用する EMR クラスタ上の HiveQL | 大規模なバッチジョブと S3 や HDFS へのコピー |
| DynoTable SQL Workbench | デスクトップ上。DynamoDB から読み取った行に対して実行 | テーブルを探索しながらの手早い結合とロールアップ |
Amazon Athena で DynamoDB を本物の SQL でクエリする方法
Amazon Athena DynamoDB コネクタは "enables Amazon Athena to communicate with DynamoDB so that you can query your tables with SQL." ものです。AWS のウォークスルーのタイトルは "Access, query, and join Amazon DynamoDB tables using Athena." です。セットアップの手順:
- Athena コンソールで Data sources を開き、Create data source、続いて
Amazon DynamoDB を選び、
ddbのような名前を付けます。 - 求められたらコネクタの Lambda 関数を作成します。そのテンプレートはスピルバケット、つまり Lambda の上限を超える結果を置く S3 バケットを求めます。
- クエリエディタで
ddbデータソースを選びます。テーブルはそのdefaultデータベースに表示されます。 - カタログ名、データベース名、テーブル名でクエリします。結合や
GROUP BYも使えます。
SELECT a.country, COUNT(*) AS tickets, SUM(t.minutesspent) AS minutes
FROM ddb.default.tickets t
JOIN ddb.default.accounts a ON t.accountid = a.accountid
GROUP BY a.country
ORDER BY minutes DESCAthena では小文字の名前を使います。大文字と小文字が混在する DynamoDB のテーブル名や属性名については、コネクタのドキュメントが AWS Glue で設定する sourceTable と columnMapping のプロパティを説明しています。
セットアップとコスト:
- これはアカウントにデプロイする Lambda ベースのフェデレーテッドコネクタです(Athena コンソールまたは Serverless Application Repository 経由)。設定には Glue 接続を使い(スキーマ用の Glue テーブルは任意)、スピル用に S3 バケットを使います。
- 読み取りは依然として DynamoDB の
QueryとScanを通ります。AWS は "queries that use scans can consume a large number of read capacity units (RCUs)." と警告しています。パーティションキーの述語はQuery呼び出しとしてプッシュダウンされ、それ以外はスキャンになるため、大きなテーブルを結合する前に料金計算ツールで確認しましょう。 - 読み取り専用です。
INSERT INTOはサポートされないため、書き込みは引き続き PartiQL か SDK で行います。
コネクタをデプロイしてしまえば、Athena はアドホックな分析や BI ダッシュボードに向いています。テーブルを閲覧しながら手早く結合したいだけなら、Lambda、Glue、スピルバケットを用意するのは大がかりなセットアップです。そのケースを扱うのが次のセクションです。
DynoTable の SQL Workbench: DynamoDB のアクセスパターンのルール内で動く SQL
DynoTable の SQL Workbench は、Lambda も Glue も S3 も用意せずに、デスクトップクライアントから稼働中のテーブルに対して JOIN、GROUP BY、COUNT/SUM/AVG を実行します。ベーステーブルを
Query または Scan で読み、結合される側をキーで取得してから、デスクトップ上でそれらの行に対して
1 つの SELECT を実行します。
-- Runs in the DynoTable Workbench (NOT in PartiQL).
-- accounts: partition key accountId, no sort key
SELECT a.country, COUNT(*) AS tickets, SUM(t.minutesSpent) AS minutes
FROM tickets t
INNER JOIN accounts a ON t.accountId = a.accountId
GROUP BY a.country
ORDER BY minutes DESCすべての読み取りが DynamoDB 自身の操作を通るため、エディタは実行前にベーステーブルの読み取りの見積もりを表示し、DynamoDB のアクセスモデルを強制します。
INNER JOINとLEFT JOINのみです。結合される側(to 側)はONの属性でキー付けされている必要があります。つまりソートキーを持たないテーブルのパーティションキーか、ちょうど 1 つの GSI のパーティションキーです。ソートキーを持つテーブルに結合するには、結合の向きを逆にするか、GSI のパーティションキーで結合します。RIGHT/FULL/CROSS/ カンマ結合はありません。- 自己結合(両側が同じテーブル)、サブクエリ、派生テーブル、CTE や
UNION、ウィンドウ関数はありません。 - 結合と射影はスカラー属性に対して動作します。
完全な SQL ステートメントではなく、生の API 向けの条件式とキー式だけが必要なら、
DynamoDB Expression Builder が PartiQL を一切使わずに
FilterExpression / KeyConditionExpression を生成します。
テーブルを探索、デバッグ、分析するための DynamoDB SQL クライアントが欲しいなら、Workbench は完全な DynamoDB GUI の中にあります。 DynoTable を試して、自分のテーブルに対して SQL を実行しましょう。
よくある質問
DynamoDB で SQL を実行できますか? PartiQL は実行できます。これは SQL 互換のサブセット(キーによる SELECT/INSERT/UPDATE/DELETE)です。 JOIN、GROUP BY、集計には上に載せる SQL エンジンが必要です。Amazon Athena DynamoDB コネクタ、 Amazon Redshift へのゼロ ETL、Amazon EMR 上の Hive、または DynoTable の SQL Workbench (INNER/LEFT JOIN を備えた単一 SELECT の方言で、CTE、UNION、サブクエリはなし)です。
DynamoDB の PartiQL は JOIN をサポートしますか?いいえ。PartiQL の SELECT 構文は単一の FROM テーブルまたはインデックスを持ち、結合の文法はありません。それぞれの側をキーでクエリしてコードでマージするか、Athena のような SQL エンジンで結合を実行します。
PartiQL は GROUP BY や COUNT、SUM のような集計をサポートしますか?いいえ。GROUP BY 句はなく、唯一の「集計」関数は SIZE(1 アイテムの属性のバイトサイズ)です。カウントには Query または Scan で Select: 'COUNT' を使います。合計は書き込み時に維持するか、前述の SQL エンジンのいずれかを使います。
DynamoDB は SQL ですか、NoSQL ですか? NoSQL です。キーバリュー・ドキュメントストアです。PartiQL はその上に SQL 互換のクエリ言語を加えますが、 DynamoDB にはリレーショナルエンジンも、結合も、集計もありません。
PartiQL はアドホッククエリに向いていますか?キーベースのルックアップには向いています。分析的なアドホッククエリ(カウント、ロールアップ、結合)には向いていません。PartiQL はそれらを表現できず、制約のない SELECT ステートメントは暗黙のうちにテーブル全体のスキャンになります。それらには Athena か Workbench を使いましょう。
JOIN と GROUP BY を扱える DynamoDB の SQL クライアントはありますか?はい。Amazon Athena は AWS アカウントにデプロイするフェデレーテッドコネクタ経由でそれを行い、
DynoTable の SQL Workbench はデスクトップから稼働中のテーブルに対して JOIN/GROUP BY/集計を実行します。