1 つの SQL クエリで DynamoDB と OpenSearch を結合する方法

DynoTable の SQL Workbench は、DynamoDB テーブルと OpenSearch インデックスをまたぐ SQL の JOIN を 1 つの文で実行します。AI エージェント、MCP サーバー、エディター連携がこの結合を実行したとき、結果は正しかったものの、5 行を表示するのに 617 秒かかりました。その文は、226,783 件のドキュメントを持つ OpenSearch のインデックスを、712,699 件のアイテムを持つ DynamoDB テーブルとそのテーブル自身のパーティションキーで結合するものでした。5 行を返すために 939,482 件のレコードを読み取っていました。インデックスのすべてのドキュメントと、テーブルのすべてのアイテムです。

ストリームやゼロ ETL のパイプラインで DynamoDB から OpenSearch にデータを送っているチームは、同じレコードを 2 つのエンジンに持ち、その両方にまたがる問いを抱えることになります。この検索結果の顧客のうち、未払いの注文があるのは誰か。このテーブルのステータスに一致するドキュメントは国ごとに何件あるか。DynamoDB には JOIN がなく、OpenSearch の SQL プラグインの JOIN は、GROUP BY を加えると間違った数字を返しました。

SQL Workbench は、こうした問いに 1 つの文で答えます。

SELECT c.country, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM dynamodb.orders o
JOIN opensearch.customers c ON o.customerId = c.customerId
GROUP BY c.country

OpenSearch の SQL プラグインはエラーなしで間違った数字を返した

OpenSearch は SQL プラグインを通じて、結合も含めて SQL を実行できます。プラグインは DynamoDB テーブルには届かないので、試したのは 2 つのインデックスの結合を OpenSearch に任せられるかどうかだけです。OpenSearch 2.19.1 で、150 件のドキュメントを持つ orders インデックスと customers インデックスに対して、次の文を実行しました。

SELECT c.country, COUNT(*)
FROM orders o JOIN customers c ON o.customerId = c.customerId
GROUP BY c.country

応答は HTTP 200 で、エラーはありませんでした。国ごとに 1 行の 6 行であるべきところが、注文ごとに 1 行の 150 行で、件数はすべて null でした。2 列のクエリに対して、スキーマには o.COUNT(*)、c.country、c.COUNT(*) の 3 列が並んでいました。

それだけで候補から外れましたが、同じバージョンにはさらに 2 つの問題がありました。結合があると、プラグインは fetch_size を無視して結果全体を 1 回の応答で返すため、大きな結合をページ単位で読むためのカーソルがありません。また、解析済みの text フィールドでの結合は、これもエラーなしで 0 行を返します。OpenSearch 自身の制限事項のページも、結合を "best-effort"（ベストエフォート）と呼び、"not support aggregations on the joined result"（結合結果に対する集計をサポートしない）と書いています。

そのため、インデックスを含む結合はすべてアプリの中で実行します。

両側を丸ごと読むのは正しかったが、遅すぎた

AI エージェント、MCP サーバー、エディター連携が共有する経路は、最初は単純な方法を取っていました。文に含まれるすべてのテーブルとインデックスを丸ごとアプリ内の SQL エンジンにコピーし、そこで文を実行します。これはどんな文に対しても動き、そのコストは WHERE や LIMIT が何であれ、文に含まれるすべてのテーブルとインデックスの大きさです。

数字は eu-west-1 の OpenSearch 3.7 ドメインで測ったもので、そのインデックスには一日中書き込みがあるため、ドキュメント数は実行ごとに変わります。WHERE なしで 5 行を表示するのに、インデックス単体では 2.0 秒かかりました。599 件のアイテムを持つ DynamoDB テーブルと結合すると、結合によってインデックス全体を読むことになり、58.2 秒かかりました。712,699 件、981 MB のテーブルと結合すると 617.4 秒です。

結合されるテーブルやインデックスは、バッチごとにキーで読む

FROM の最初のテーブルまたはインデックスをベース、それ以外のテーブルやインデックスを結合の相手側と呼びます。Workbench のグリッドは、もともと結合の相手側をキーで読んでいました。修正では、AI エージェント、MCP サーバー、エディター連携が共有する経路を同じキーによる読み取りに移しました。ベースはそのエンジンから、OpenSearch では 1 回のリクエストで 5,000 件ずつ読まれ、100 行のバッチ単位で渡されます。アプリは各バッチについて結合値を集め、一致するレコードだけを取得します。

ローカルのテストデータでは、キーによる読み取りは丸ごと読んだ場合と同じ答えを返し、インデックス同士の結合は 6,002 件ではなく 302 件を読みました。

キーで読むには、読むためのキーが必要です。DynamoDB テーブルでは、結合先はソートキーを持たないテーブルのパーティションキーか、ソートキーを持たずすべての属性を射影する GSI のパーティションキーでなければなりません。ソートキーを持つ GSI にはソート属性のないアイテムが含まれないので、そこをキーで読むと行が失われます。ここでの読み取りはすべて結果整合性で、GSI はさらに遅れることがあるため、数秒前に書いたアイテムはまだ結合されないことがあります。テーブル自身のキーにソートキーがある場合は、そのテーブルをベースにして、もう一方のテーブルやインデックスをそこに結合できます。LEFT JOIN では、それによってどちら側の行が残るかが変わります。OpenSearch のインデックスでは、_id、または任意の keyword、数値、date、boolean、ip のフィールド、keyword のサブフィールドを持つ text フィールドで結合します。そこでは terms クエリが値全体で一致するからです。

Workbench のグリッドが受け付けるのは、こうした結合だけです。共有の経路では、それ以外の結合はこれまでどおり丸ごとの読み取りで実行されます。

そのエンジンに送られるのはベースのフィルタだけ

ベース自身のフィルタは、WHERE が残す行の上位集合に広げたうえで、今ではそのエンジンに送られます。結合の相手側はフィルタなしでキーによって読まれます。WHERE 全体は結合した行に対してあらためて実行されるので、一致しすぎるフィルタは読み取りを増やすだけで、答えは変わりません。ローカルのテストデータでは、WHERE o.sku > 18.5 は 6,000 件以上ではなく 604 件のドキュメントを読みました。広げたフィルタは sku 18 も持ち帰り、それは WHERE 全体があとで取り除きました。

キーで読むと間違える 3 つのケースと、取り除く値 1 つ

キーによる読み取りが安全なのは、丸ごと読んだ場合に見つかるすべてのレコードを見つけるときだけです。そうならないケースが 3 つ見つかりました。そのいずれでも、共有の経路は文全体を丸ごとの読み取りに切り替え、丸ごとの読み取りが返すものを、そのコストで返します。すでに行ったキーによる読み取りは再利用されません。MCP サーバーとエディター連携では、丸ごとの読み取りはスキャン予算にも計上されます。スキャン予算とは、1 つのクライアントのクエリが読める量の上限です。

ignore_above より長い値。 OpenSearch はドキュメントをインデックスしますが、フィールドの ignore_above より長い keyword の値は飛ばすため、terms クエリはそれに一致しません。一方、丸ごと読めば _source の中にその値が見えます。私たちは各結合値を、届いた時点でその上限と照らします。最初は、ignore_above を持つフィールドはすべて丸ごとの読み取りに回していました。私たちが測ったインデックスでは、ほとんどの keyword フィールドにそれが付いていて、あるインデックスでは 225 個中 200 個でした。そのため、ほぼすべての文字列の結合が切り替わるところでした。キーによる読み取りのままにするには、値がその上限に収まるフィールドで結合してください。ignore_above を引き上げる方法は、既存のドキュメントをすべてインデックスし直したあとでしか効きません。たとえば _update_by_query を使います。アプリは新しい上限をすぐにマッピングから読みますが、古い上限でインデックスされたドキュメントにはまだその語がありません。

複数のインデックスを指す 1 つの名前。 エイリアスは、同じ _id のドキュメントを持つ複数のインデックスを指すことがあります。ローカルのコピーはドキュメントを _id だけで識別するので、2 つ目のドキュメントが 1 つ目を上書きしてしまいます。共有の経路は読み取りの前に切り替え、Workbench のグリッドとエディターは、そのような結合の相手側をエラーとして示します。目的のドキュメントが 1 つのインデックスにすべて入っているなら、エイリアスではなくそのインデックスを指定してください。

あるバッチで失敗するキーによる読み取り。 あるバッチのキーによる読み取りが失敗すると、INNER JOIN では黙って行が失われ、LEFT JOIN では黙って列が空になるので、経路はどちらの場合も切り替えます。ここで切り替えが繰り返されるなら、たいていは結合の相手側の権限かスロットリングが原因です。まずそこを確認してください。丸ごとの読み取りは文に含まれるすべてのテーブルとインデックスをスキャンするので、Scan の権限が必要で、置き換えたキーによる読み取りよりはるかに多くの読み取りキャパシティを使います。

インデックスが解釈できない結合値。 これは切り替えではなく、取り除くことで直しました。OpenSearch は、long のフィールドに単語を送ったときのように、1 つの値がフィールドの型に合わないと terms クエリ全体を拒否します。以前は、1 つの悪い値がそのバッチのキーによる読み取り全体を失敗させ、文を丸ごとの読み取りへ送っていました。結合は型付きの値を比べるので、フィールドの型に合わない値は、ソースがマッピングと一致するどのドキュメントにも一致しません。経路は今ではその値をキーによる読み取りから取り除き、残りを使います。

早期停止は、間隔を空けるまで 2 乗で増えていた

読み取りを早めに止められるのは、単純な行の読み取りの場合だけです。つまり、DISTINCT なしで列か * を選び、あるのは WHERE と、OFFSET を伴わない LIMIT だけという文です。そのとき読み取りは LIMIT で止まり、LIMIT がなければ出力の上限より 1 行多いところ、つまり結果が切り詰められたことを示す行で止まります。上の GROUP BY の例は集計をするので、ベースを丸ごと読み、節約は結合の相手側だけです。止めて節約できるのは 5,000 件のリクエスト単位なので、それより小さいインデックスではベース側の節約はありません。

「行はもう足りたか」を確かめるには、それまでに読んだものすべてを数える必要があります。最初のバージョンは 100 行のバッチごとに確認し、その確認が実行中はアプリのメインプロセスを止めていました。LIMIT に達しない最悪の結合では、100,000 行のベースで 1,000 回の確認に 1,202 ミリ秒、226,000 行で 2,260 回の確認に 6,115 ミリ秒かかりました。行数が 2.25 倍になると、時間は 5 倍になったわけです。

今では、足りなかった確認のたびに、次の確認をすでに読んだ行の 4 分の 1 だけ遅らせます。同じ 2 つの実行で、確認は 27 回で 11 ミリ秒、31 回で 27 ミリ秒になりました。間隔を空けた分、止まるのが遅れます。ベースの 1,000 行に 1 行だけ一致する結合は、4,100 行目ではなく 4,800 行目で止まります。OpenSearch のベースなら、それはまだ最初の 5,000 件のリクエストの中なので、ベースの読み取りは増えず、増えるのはその 700 行分の結合の相手側のキーによる読み取りだけです。

MCP のスキャン予算は 5,000 件の OpenSearch ドキュメントに 101 件しか計上していなかった

最初のバージョンは、OpenSearch が送ってきたドキュメントではなく、経路が次に渡した行を予算に計上していました。OpenSearch のベースでは、その差は最大で 5,000 件のリクエスト 1 回分になります。ある実行では、5,000 件のドキュメントを転送したのに 101 件と報告していました。予算は今では、各ベースのリクエストで OpenSearch が返したものと、キーによる読み取りが返したすべてのレコードを数えます。

自分で作るなら応用できること

データベースの SQL 層の上に何かを作る前に、結合の上の集計で試してください。私たちの場合は HTTP 200 で間違った数字が返ってきました。
ベースのフィルタは上位集合として押し込み、WHERE 全体をローカルで再確認してください。そうすれば、押し込みのバグは読み取りを増やすことはあっても、答えを変えることはありません。
結合の相手側は、バッチごとにキーで読んでください。両側を丸ごと読む結合は両方の大きさだけコストがかかり、どんなフィルタも 5 行の表示を速くしません。
キーによる読み取りがレコードを取りこぼすケースをすべて挙げ、どれかに当てはまるときは文全体を切り替えてください。文の一部だけを切り替えると、テストすべき 3 つ目の読み取り方式が生まれます。
読んだものを再スキャンする「もう終わりか」の確認は、間隔を空けてください。

結合のルールとクエリの例は OpenSearch SQL に、DynamoDB 側のキーによる結合は JOIN のガイドにあります。無料の DynamoDB Expression Builder では、結合の相手側の GSI Query が使うキー条件を組み立てられます。

試すには、DynoTable をダウンロードし、プロファイルで OpenSearch をオンにして、Workbench のタブでインデックスとテーブルを結合してください。AI プロバイダーを設定していれば、SQL の結合でこの問いに答えるようエージェントに頼むこともできます。