初級読了 4 分

DynamoDB のための SQL と PartiQL の限界

DynamoDB は PartiQL を通じてキーの形をした SQL には答えますが、リレーショナルなものには答えません。 JOIN、GROUP BY、集計はありません。このページでは PartiQL がカバーする範囲と、PartiQL では表現できないクエリを実行する方法をまとめます。

DynamoDB を SQL でクエリできるのか?

部分的には。AWS は を "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 DESC

ORDER 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 へのゼロ ETLRedshift。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." です。セットアップの手順:

  1. Athena コンソールで Data sources を開き、Create data source、続いて Amazon DynamoDB を選び、ddb のような名前を付けます。
  2. 求められたらコネクタの Lambda 関数を作成します。そのテンプレートはスピルバケット、つまり Lambda の上限を超える結果を置く S3 バケットを求めます。
  3. クエリエディタで ddb データソースを選びます。テーブルはその default データベースに表示されます。
  4. カタログ名、データベース名、テーブル名でクエリします。結合や 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 DESC

Athena では小文字の名前を使います。大文字と小文字が混在する 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/集計を実行します。

著者: Dmitriy · 更新日