CosmosDB のマルチパーティションのコレクションで、集計関数を使おうとして結構はまったので、自分のために記録しておく。
パーティションを持ったコレクションのクエリ方法
パーティションを持ったクエリの方法は、複数ある。一つは、パーティションキーを含んだ Where 句を持つこと。
IQueryable<DeviceReading> query = client.CreateDocumentQuery<DeviceReading>(
UriFactory.CreateDocumentCollectionUri("db", "coll"))
.Where(m => m.MetricType == "Temperature" && m.DeviceId == "XMS-0001");
もう一つが EnableCrossPartitionQuery を True に設定すること
IQueryable<DeviceReading> crossPartitionQuery = client.CreateDocumentQuery<DeviceReading>(
UriFactory.CreateDocumentCollectionUri("db", "coll"),
new FeedOptions { EnableCrossPartitionQuery = true })
.Where(m => m.MetricType == "Temperature" && m.MetricValue > 100);
また、クエリの方法として、単純に SQL を指定する方法と、Linq を使う方法があるので、それぞれ試してみよう。
SQL を指定する方法
次のようなコレクションがある。この形を持ったコレクションをマルチパーティションで作る。TeamId がパーティションキーになる。
public class DowntimeRecords
{
public string TeamId { get; set; }
public DateTime Time { get; set; }
public int Count { get; set; }
public string id { get; set; }
}
この場合、SQL べた書きの場合は次のように書ける。
var teamId = "Team01";
var sql = $"SELECT VALUE Sum(c.Count) from DowntimeRecords as c Where c.TeamId = \"{teamId}\"";
var query = client.CreateDocumentQuery(
UriFactory.CreateDocumentCollectionUri(databaseId, "DowntimeRecords"), sql);
var result = query.ToList<dynamic>();
通常だと、client.CreateDocumentQuery<T> を使いたくなるところだが、残念ながら、このSum() を使うに当たってはいくつの制約があった。
VALUE 句が Sum には必要
クエリの先頭に VALUE 句を指定しないと Sum が動作しなかった。指定しないと次のようなエラーに遭遇する。VALUE を指定しろと言われる。
SELECT Sum(c.Count) as Downtime from DowntimeRecords as c Where c.TeamId = \"{teamId}\"
[Error] System.Private.CoreLib: Exception while executing function: DowntimeBatch. System.Private.CoreLib: One or more errors occurred. (Message: {"Errors":["Cross partition query only supports 'VALUE <AggreateFunc>' for aggregates."]}
ActivityId: f794fa44-3566-46c3-96d4-1b7cb4735170, Microsoft.Azure.Documents.Common/2.0.0.0). Microsoft.Azure.DocumentDB.Core: Message: {"Errors":["Cross partition query only supports 'VALUE <AggreateFunc>' for aggregates."]}
ActivityId: f794fa44-3566-46c3-96d4-1b7cb4735170, Microsoft.Azure.Documents.Common/2.0.0.0.
VALUE を指定すると戻りは、「値」になるので、Json にならない。だから、<T> を指定するということができない。ちなみに
SELECT VALUE Sum(c.Count) as Downtime ...
にするとこれはこれでエラーになる。as Downtime の指定がだめ。ほかにも
SELECT c.TeamId, Sum(c.Count) as Downtime ...
みたいに返すのもNG だった。メモメモ。
Linq を使う
上記のような生SQLはかっこ悪いし、SQLインジェクション出来そうであまりやりたくない。Linq で書こう。でもどうやって書くのだろう？試してみるととても簡単だった。ただし、マニュアルにはサンプルなども書いていないので、VS のIntellisense を見ながら書くのが良いだろう。
var sum = client.CreateDocumentQuery<DowntimeRecords>(
UriFactory.CreateDocumentCollectionUri(databaseId, "DowntimeRecords"))
.Where<DowntimeRecords>(r => r.TeamId == teamId)
.Sum<DowntimeRecords>(r => r.Count);
パフォーマンスの比較
クロスパーティションクエリーになると、パフォーマンスがどうなるんだろうか？とかはとても心配になるポイントなので、実際に１万パーティションにそれぞれ２つのドキュメントを持つようなデータを作成して、クエリしてみた。私の理解がただしければ、PartitionKey を指定すれば、一発で検索に行くので、コストは高くなくて、リクエストをパーティションに投げることもないはずだ。
結果として両者のパフォーマンスは同じ程度だった。次の結果を見てみると、一瞬 Linq のほうが優秀に見えるが、そうではなく、先に実行したほうがあのスコアになるので、キャッシュの関係と思われる。
Plain SQL query ------
[5.0]
------- Elapse time (ms): 4035
LINQ Query -----
Sum: 5
------- Elapse time (ms): 411
リクエスト
Fiddler でどのようなリクエストが送られているか見てみた。実際に発行されているSQLはそれぞれ２つづづ。
SQL
{"query":"SELECT VALUE [{\"item\": Sum(c.Count)}]\r\nFROM DowntimeRecords AS c\r\nWHERE (c.TeamId = \"Team01\")"}
Linq
{"query":"SELECT VALUE [{\"item\": Sum(root[\"Count\"])}]\r\nFROM root\r\nWHERE (root[\"TeamId\"] = \"Team01\")"}
最初のリクエスト
/ にリクストを投げています。データベースの基本設定をとってきている雰囲気
GET https://xxxxxxxxxxxx.documents.azure.com/ HTTP/1.1
Connection: Keep-Alive
Authorization: xxxxxxxxxxxx
User-Agent: Windows/10.0.17134 documentdb-netcore-sdk/1.5.1
x-ms-version: 2017-02-22
x-ms-date: Wed, 27 Jun 2018 09:20:35 GMT
Host: xxxxxxxxxxx.documents.azure.com
HTTP/1.1 200 Ok
Cache-Control: no-store, no-cache
Pragma: no-cache
Transfer-Encoding: chunked
Content-Type: application/json
Content-Location: https://xxxxxxxxxx.documents.azure.com/
Server: Microsoft-HTTPAPI/2.0
x-ms-max-media-storage-usage-mb: 2048
x-ms-media-storage-usage-mb: 0
x-ms-databaseaccount-consumed-mb: 0
x-ms-databaseaccount-reserved-mb: 0
x-ms-databaseaccount-provisioned-mb: 0
Strict-Transport-Security: max-age=31536000
x-ms-gatewayversion: version=2.0.0.0
Date: Wed, 27 Jun 2018 09:20:37 GMT
```
レスポンス
```
51B
{"_self":"","id":"xxxxxxxx","_rid":"xxxxxxxxx.documents.azure.com","media":"//media/","addresses":"//addresses/","_dbs":"//dbs/","writableLocations":[{"name":"East US","databaseAccountEndpoint":"https://xxxxxxxx-eastus.documents.azure.com:443/"}],"readableLocations":[{"name":"East US","databaseAccountEndpoint":"https://xxxxxxxxx-eastus.documents.azure.com:443/"}],"userReplicationPolicy":{"asyncReplication":false,"minReplicaSetSize":3,"maxReplicasetSize":4},"userConsistencyPolicy":{"defaultConsistencyLevel":"Session"},"systemReplicationPolicy":{"minReplicaSetSize":3,"maxReplicasetSize":4},"readPolicy":{"primaryReadCoefficient":1,"secondaryReadCoefficient":1},"queryEngineConfiguration":"{\"maxSqlQueryInputLength\":30720,\"maxJoinsPerSqlQuery\":5,\"maxLogicalAndPerSqlQuery\":500,\"maxLogicalOrPerSqlQuery\":500,\"maxUdfRefPerSqlQuery\":10,\"maxInExpressionItemsCount\":16000,\"queryMaxInMemorySortDocumentCount\":500,\"maxQueryRequestTimeoutFraction\":0.9,\"sqlAllowNonFiniteNumbers\":false,\"sqlAllowAggregateFunctions\":true,\"sqlAllowSubQuery\":true,\"sqlAllowScalarSubQuery\":true,\"allowNewKeywords\":true,\"sqlAllowLike\":false,\"maxSpatialQueryCells\":12,\"spatialMaxGeometryPointCount\":256,\"sqlAllowTop\":true,\"enableSpatialIndexing\":true}"}
0
```
## /dbs/{databasename}/colls/{collection name} へのクエリ
こちらは、具体的なコレクションへのクエリで設定をとってきています。Cosomos にデータベースや、Collectionの名前を指定していますが、こういうフローになるのですね。
```
GET https://XXXXXXX-eastus.documents.azure.com/dbs/leaderboard/colls/DowntimeRecords HTTP/1.1
Cache-Control: no-cache
Connection: Keep-Alive
Accept: application/json
Authorization: XXXXXXXXXXXXXXXXXXXXXX
User-Agent: Windows/10.0.17134 documentdb-netcore-sdk/1.5.1
x-ms-date: Wed, 27 Jun 2018 09:20:38 GMT
x-ms-consistency-level: Session
x-ms-version: 2017-02-22
Host: XXXXXXXXX-eastus.documents.azure.com
```
```
HTTP/1.1 200 Ok
Cache-Control: no-store, no-cache
Pragma: no-cache
Transfer-Encoding: chunked
Content-Type: application/json
Content-Location: https://XXXXXXXXXXXX-eastus.documents.azure.com/dbs/leaderboard/colls/DowntimeRecords
Server: Microsoft-HTTPAPI/2.0
Strict-Transport-Security: max-age=31536000
x-ms-last-state-change-utc: Wed, 27 Jun 2018 06:32:42.036 GMT
etag: "00004f07-0000-0000-0000-5b333a7b0000"
collection-partition-index: 0
collection-service-index: 0
lsn: 3959
x-ms-schemaversion: 1.6
x-ms-alt-content-path: dbs/leaderboard
x-ms-content-path: LhhOAA==
x-ms-xp-role: 2
x-ms-global-Committed-lsn: 3958
x-ms-number-of-read-regions: 0
x-ms-item-lsn: 4
x-ms-transport-request-id: 178
x-ms-request-charge: 1
x-ms-serviceversion: version=2.0.0.0
x-ms-activity-id: 7a8af223-3c1c-44ec-9846-c9fcd7fcb695
x-ms-session-token: 4:3959
x-ms-documentdb-collection-index-transformation-progress: -1
x-ms-gatewayversion: version=2.0.0.0
Date: Wed, 27 Jun 2018 09:20:39 GMT
226
{"id":"DowntimeRecords","indexingPolicy":{"indexingMode":"consistent","automatic":true,"includedPaths":[{"path":"\/*","indexes":[{"kind":"Range","dataType":"Number","precision":-1},{"kind":"Hash","dataType":"String","precision":3}]}],"excludedPaths":[]},"partitionKey":{"paths":["\/TeamId"],"kind":"Hash"},"_rid":"LhhOAL5KcQ4=","_ts":1530083963,"_self":"dbs\/LhhOAA==\/colls\/LhhOAL5KcQ4=\/","_etag":"\"00004f07-0000-0000-0000-5b333a7b0000\"","_docs":"docs\/","_sprocs":"sprocs\/","_triggers":"triggers\/","_udfs":"udfs\/","_conflicts":"conflicts\/"}
0
```
## パーティションに対するクエリ
次のクエリはパーティションキーのハッシュに対してクエリがされています。パーティションのレンジなどパーティション情報が返ってくる様子。
```
GET https://xxxxxxxx-eastus.documents.azure.com/dbs/LhhOAA==/colls/LhhOAL5KcQ4=/pkranges HTTP/1.1
Cache-Control: no-cache
Connection: Keep-Alive
Accept: application/json
Authorization: xxxxxxxxxxxxxxxxxxxxx
User-Agent: Windows/10.0.17134 documentdb-netcore-sdk/1.5.1
x-ms-max-item-count: -1
A-IM: Incremental Feed
x-ms-date: Wed, 27 Jun 2018 09:20:40 GMT
x-ms-consistency-level: Session
x-ms-version: 2017-02-22
Host: xxxxxxxx-eastus.documents.azure.com
```
```
HTTP/1.1 200 Ok
Cache-Control: no-store, no-cache
Pragma: no-cache
Transfer-Encoding: chunked
Content-Type: application/json
Content-Location: https://xxxxxxxxxx-eastus.documents.azure.com/dbs/LhhOAA==/colls/LhhOAL5KcQ4=/pkranges
Server: Microsoft-HTTPAPI/2.0
Strict-Transport-Security: max-age=31536000
x-ms-last-state-change-utc: Tue, 26 Jun 2018 19:57:56.303 GMT
etag: "116"
lsn: 121
x-ms-item-count: 5
x-ms-schemaversion: 1.6
x-ms-alt-content-path: dbs/leaderboard/colls/DowntimeRecords
x-ms-xp-role: 2
x-ms-global-Committed-lsn: 121
x-ms-number-of-read-regions: 0
x-ms-transport-request-id: 49285
x-ms-serviceversion: version=2.0.0.0
x-ms-activity-id: 1c7544cc-3754-4dd5-9f61-4a6f83c9b02b
x-ms-session-token: 0:121
x-ms-gatewayversion: version=2.0.0.0
Date: Wed, 27 Jun 2018 09:20:39 GMT
6C8
{"_rid":"LhhOAL5KcQ4=","PartitionKeyRanges":[{"_rid":"LhhOAL5KcQ4CAAAAAAAAUA==","id":"0","_etag":"\"00005107-0000-0000-0000-5b333a7b0000\"","minInclusive":"","maxExclusive":"05C1C9CD673398","ridPrefix":0,"_self":"dbs\/LhhOAA==\/colls\/LhhOAL5KcQ4=\/pkranges\/LhhOAL5KcQ4CAAAAAAAAUA==\/","throughputFraction":0.2,"status":"online","parents":[],"_ts":1530083963,"_lsn":112},{"_rid":"LhhOAL5KcQ4DAAAAAAAAUA==","id":"1","_etag":"\"00005207-0000-0000-0000-5b333a7b0000\"","minInclusive":"05C1C9CD673398","maxExclusive":"05C1D9CD673398","ridPrefix":1,"_self":"dbs\/LhhOAA==\/colls\/LhhOAL5KcQ4=\/pkranges\/LhhOAL5KcQ4DAAAAAAAAUA==\/","throughputFraction":0.2,"status":"online","parents":[],"_ts":1530083963,"_lsn":113},{"_rid":"LhhOAL5KcQ4EAAAAAAAAUA==","id":"2","_etag":"\"00005307-0000-0000-0000-5b333a7b0000\"","minInclusive":"05C1D9CD673398","maxExclusive":"05C1E399CD6732","ridPrefix":2,"_self":"dbs\/LhhOAA==\/colls\/LhhOAL5KcQ4=\/pkranges\/LhhOAL5KcQ4EAAAAAAAAUA==\/","throughputFraction":0.2,"status":"online","parents":[],"_ts":1530083963,"_lsn":114},{"_rid":"LhhOAL5KcQ4FAAAAAAAAUA==","id":"3","_etag":"\"00005407-0000-0000-0000-5b333a7b0000\"","minInclusive":"05C1E399CD6732","maxExclusive":"05C1E9CD673398","ridPrefix":3,"_self":"dbs\/LhhOAA==\/colls\/LhhOAL5KcQ4=\/pkranges\/LhhOAL5KcQ4FAAAAAAAAUA==\/","throughputFraction":0.2,"status":"online","parents":[],"_ts":1530083963,"_lsn":115},{"_rid":"LhhOAL5KcQ4GAAAAAAAAUA==","id":"4","_etag":"\"00005507-0000-0000-0000-5b333a7b0000\"","minInclusive":"05C1E9CD673398","maxExclusive":"FF","ridPrefix":4,"_self":"dbs\/LhhOAA==\/colls\/LhhOAL5KcQ4=\/pkranges\/LhhOAL5KcQ4GAAAAAAAAUA==\/","throughputFraction":0.2,"status":"online","parents":[],"_ts":1530083963,"_lsn":116}],"_count":5}
0
```
## SQL のクエリ
最後に、SQL もしくは、Linq のクエリです。上記のクエリは、SQL と Linq の２回クエリをかけていますが、１回のみでした。つまり必要なときのみクエリされるということでしょう。だから最初の１回のクエリは遅いのですね。Linq のクエリはほぼ同じなので省略します。
```
POST https://xxxxxxxx-eastus.documents.azure.com/dbs/leaderboard/colls/DowntimeRecords/docs HTTP/1.1
Cache-Control: no-cache
Connection: Keep-Alive
Content-Type: application/query+json
Accept: application/json
Authorization: xxxxxxxxxxxxxx
User-Agent: Windows/10.0.17134 documentdb-netcore-sdk/1.5.1
x-ms-continuation:
x-ms-documentdb-isquery: True
x-ms-documentdb-query-enablecrosspartition: False
x-ms-max-item-count: 100
x-ms-documentdb-populatequerymetrics: False
x-ms-documentdb-partitionkeyrangeid: LhhOAL5KcQ4=,1
x-ms-date: Wed, 27 Jun 2018 09:20:40 GMT
x-ms-consistency-level: Session
x-ms-version: 2017-02-22
Content-Length: 113
Host: xxxxxxxx-eastus.documents.azure.com
{"query":"SELECT VALUE [{\"item\": Sum(c.Count)}]\r\nFROM DowntimeRecords AS c\r\nWHERE (c.TeamId = \"Team01\")"}
```
```
HTTP/1.1 200 Ok
Cache-Control: no-store, no-cache
Pragma: no-cache
Transfer-Encoding: chunked
Content-Type: application/json
Server: Microsoft-HTTPAPI/2.0
Strict-Transport-Security: max-age=31536000
x-ms-last-state-change-utc: Wed, 27 Jun 2018 07:00:18.547 GMT
x-ms-resource-quota: documentSize=10240;documentsSize=10485760;documentsCount=-1;collectionSize=10485760;
x-ms-resource-usage: documentSize=2;documentsSize=1818;documentsCount=3908;collectionSize=2848;
lsn: 3915
x-ms-item-count: 1
x-ms-schemaversion: 1.6
x-ms-alt-content-path: dbs/leaderboard/colls/DowntimeRecords
x-ms-content-path: LhhOAL5KcQ4=
x-ms-xp-role: 2
x-ms-global-Committed-lsn: 3914
x-ms-number-of-read-regions: 0
x-ms-transport-request-id: 776
x-ms-request-charge: 3
x-ms-serviceversion: version=2.0.0.0
x-ms-activity-id: 4a32ce54-2ced-4dd0-937a-89bcbbd13b56
x-ms-session-token: 1:3915
x-ms-gatewayversion: version=2.0.0.0
Date: Wed, 27 Jun 2018 09:20:40 GMT
3D
{"_rid":"LhhOAL5KcQ4=","Documents":[[{"item":5}]],"_count":1}
0
```
# まとめ
パーティションに対する集計のクエリをかいて、１００００パーティションに分散させてみましたが、クエリ自体は一発で終了しているようで快適でした。パフォーマンスチューニングなどは一切していませんが、今回のユースケースに関しては問題なさそうです。(CosmosDBトリガーを使ったバッチ処理)
# リファレンス
サンプルを GitHub に置いておきました。
* [Cosmos DB Client Spike](https://github.com/TsuyoshiUshio/CosmosDBClientQuerySpike/blob/master/CosmosDBClientSpike/Program.cs)
* [SQL queries for Azure Cosmos DB](https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-sql-query)
* [Querying partitioned containers](https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-partition-data#querying-partitioned-containers)
* [Bug: Unexpected behaviour of aggregates in Query Explorer](https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/18963151-bug-unexpected-behaviour-of-aggregates-in-query-e) VALUE が必要になることについて