LoginSignup
4
1

More than 5 years have passed since last update.

@TsuyoshiUshio@github(Ushio Tsuyoshi)

CosmosDB の集計関数をマルチパーティションのコレクションで使ってみる

Last updated at Posted at 2018-06-27

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 に置いておきました。

4
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
1