Can the CosmosDB Analytical store be used for realtime querying?

gsharp 0
Viewed 66

For an IoT project with our customer we work with multiple factories sending data from multiple devices to the Azure IoT hub. We extract the last package every minute for each factory and device and store this data in a CosmosDb. Goal is to build reporting on the various parameters for every device over time.

The document we store in CosmosDb is around 1Kb. If we take 100 factories into account, each containing 100 devices, and every minute 1 package is stored for these devices, then after one year we will have collected around 5Tb of data.

This is quite expensive storage. The data that is stored is readonly and will not be updated. So I was exploring the analytical store to offload the data. The storage of the analytical store is 10x cheaper.

I have enabled the synapse link on my CosmosDb and container. I created a synapse workspace and connected to the CosmosDb as a connected service.

Now I tried to query the data using the SQL Serverless pool. But even a simple query showing only 1 or 2 columns of only a few records is taking several seconds to return.

So I am wondering if this is the normal behaviour? If this is the case, then the analytical store doesn't seem to be a good candidate for doing live reporing in a webportal.

azure-cosmosdb azure-synapse
2 Answers

For SQL Serverless endpoint my experience would be that it is definitely not suitable for this.

I don't have any collections as large as 5TB but I do have collections of 2TB in analytical store.

I ran the following query to get an indicative timing for you

SELECT [_id]
FROM   OPENROWSET(PROVIDER = 'CosmosDB' ...) 
WITH ( _id VARCHAR(150) COLLATE Latin1_General_100_BIN2_UTF8 '$._id.string' ) AS rows
WHERE  _id = 'xxx'

I had to wait maybe 30 seconds for the SQL Serverless pool to warm up before I could run the query - the actual execution time to return the single row was 1m 18s (so extrapolating out from that would be over 3 minutes on a collection 2.5x bigger)

I have found running the same query back to back can have hugely different execution times in SQL Serverless and no visibility into why so it isn't suitable for meeting any strict SLA.

How SQL Serverless scales itself to the workload is a black box and there are no execution plans or any metrics to show things like successful file elimination. Even queries with a very recent predicate on the _ts field (that one would have thought could skip a lot of files without reading them) still seem to take an unexpectedly long time to execute in my experience.

Possibly you would have better luck with the Spark endpoint (especially in conjunction with custom partitioning) if you leave a cluster permanently running but I haven't used custom partitioning at all so have no experience with that.

userG4j0

For our particular use case we ended up with using Azure Data Explorer. This resource is perfect for ingesting timebased IoT telemetry data. Querying 25 million records, performing grouping (on timespans) and aggregation on these groups is done in less than 0.3 seconds.

gsharp0
Related