{"id":3623,"date":"2021-11-02T08:00:51","date_gmt":"2021-11-02T15:00:51","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=3623"},"modified":"2021-11-02T07:33:19","modified_gmt":"2021-11-02T14:33:19","slug":"custom-partitioning-azure-synapse-link","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/custom-partitioning-azure-synapse-link\/","title":{"rendered":"Now in preview \u2013 Support for custom partitioning in Azure Synapse Link for Azure Cosmos DB"},"content":{"rendered":"

This blog post was co-authored by Revin Chalil, Rodrigo Souza and Anitha Adusumilli from the Azure Cosmos DB Team.<\/em><\/p>\n

Today, we are\u00a0excited\u00a0to announce\u00a0that\u00a0custom partitioning is in preview for\u00a0<\/span>Azure Synapse Link for Azure Cosmos DB<\/span><\/a>. This capability improves query performance by <\/span>enabling you to partition Azure Cosmos DB analytical store data using keys that are <\/span>frequently used<\/span> as query filters.<\/span>\u00a0<\/span><\/p>\n

In addition to custom partitioning,\u00a0this blog post also covers two\u00a0Azure Synapse Link\u00a0capabilities that\u00a0have\u00a0been\u00a0enabled in the last few months:\u00a0serverless compatibility and full fidelity schema.\u00a0<\/span>\u00a0<\/span><\/p>\n

Custom\u00a0partitioning\u00a0for analytical\u00a0workloads<\/span>\u00a0<\/span><\/h3>\n

You can now use custom partitioning to improve\u00a0analytical\u00a0query performance. By default, data in Azure Cosmos DB analytical store is not partitioned.\u00a0<\/span>If your analytical queries have frequently used filters, you\u202fnow have the option to partition based on these fields.<\/span>\u00a0<\/span><\/p>\n

Partitioned data from the Azure Cosmos DB analytical store is written to the partitioned store, which points to the primary storage account linked to your Azure Synapse workspace.<\/span>\u00a0<\/span>Analytical queries with filters on the partition columns can benefit from partition pruning and avoid scanning any partition that does not satisfy the filters. The pruned data from the partitioned store is then automatically merged with the most recent non-partitioned data from the analytical store, returning near real-time results for your queries. <\/span>\u00a0<\/span><\/p>\n

By compacting\u00a0and optimizing the\u00a0analytical data\u00a0written to\u00a0the\u00a0partitioned store, custom partitioning al<\/span>so helps\u00a0to\u00a0improve the query performance when there is a\u00a0<\/span>high volume of update or delete operations on the underlying Azure Cosmos DB container<\/span>.\u00a0<\/span>Instead of keeping track of multiple versions of records in the analytical store and loading them during each query execution, the partitioned store only contains the latest version of the data.<\/span>\u00a0<\/span><\/p>\n

\"Architecture<\/a>
Diagram of Azure Synapse Link custom partitioning architecture<\/figcaption><\/figure><\/p>\n

Triggering a\u00a0custom partitioning job<\/span>\u00a0<\/span><\/h3>\n

Partitioning can be triggered from an Azure Synapse Spark notebook using Azure Synapse Link. You can schedule it to run as a background job, once or twice a day, or it can be executed more often if needed. <\/span>\u00a0<\/span>You can also choose one or more fields from the dataset as the analytical store partition key. <\/span>\u00a0<\/span><\/p>\n

In the below sample, the\u202f”readDate”\u202fis chosen as the partition key. Each time you trigger the partitioning job, the incremental analytical store data will be partitioned and written to the partitioned store, which is indicated by the “basePath” config. <\/span>Please see\u00a0<\/span>configure custom partitioning<\/span><\/a> to understand the different configurations, which can be used with custom partitioning.<\/span>\u00a0<\/span><\/p>\n

\"Azure<\/a>
Azure Synapse Link custom partitioning job definition code snippet<\/figcaption><\/figure><\/p>\n

Analytical query execution\u00a0on\u00a0partitioned store\u00a0<\/span>\u00a0<\/span><\/h3>\n

While querying, specify\u202fthe\u202fsame\u202f”partition.keys” and\u202f”basePath”\u202fconfigs, as defined in the partitioning job,\u202fto make use of the partitioned store\u202fin the query path.\u202f<\/span>\u00a0<\/span>\u00a0<\/span><\/p>\n

Below filter will avoid scanning partitions other than “readDate = ‘2021-11-01’\u201d\u202fpartition: <\/span>\u00a0<\/span><\/p>\n

\"Azure<\/a>
Azure Synapse Link custom partitioning query<\/figcaption><\/figure><\/p>\n

In summary,\u00a0custom partitioning\u00a0can help\u00a0improve\u00a0analytical\u00a0query performance\u00a0when partition column(s) are used\u00a0as query filters.\u00a0To learn more,\u00a0refer to\u00a0our\u00a0<\/span>documentation<\/span><\/a>.<\/span>\u00a0<\/span><\/p>\n

What customers say about Azure Synapse Link for Azure Cosmos DB<\/h3>\n

“AEMO leverages Azure and Azure Cosmos DB to ingest terabytes of critical metering data. With the significant amounts of data that our system brings in, the capability to do custom partitioning in Azure Cosmos DB’s HTAP functionality was essential for us to provide performant insights to our internal stakeholders.”<\/p>\n

– Australian\u00a0Energy\u00a0Market\u00a0Operator<\/p><\/blockquote>\n

\u00a0<\/span>Azure Synapse Link compatibility with Azure Cosmos DB serverless<\/span>\u00a0<\/span><\/h3>\n

You can now use Azure Synapse Link with Azure Cosmos DB serverless<\/a> accounts. Serverless is ideal for developing, testing, and running production applications where the traffic pattern is unknown. These accounts are billed on<\/span> consumption, meaning that you are charged only for the Request Units consumed by database operations and the GBs of transactional and analytical storage consumed by your data.\u00a0<\/span> Visit our pricing page<\/a> for details on Azure Cosmos DB options<\/span>.<\/span>\u00a0<\/span><\/p>\n

This feature is generally available in all Azure regions, using Azure Powershell or CLI.\u00a0<\/span><\/p>\n

\u00a0<\/span>Full fidelity schema for Azure Cosmos DB Core (SQL) API accounts<\/span>\u00a0<\/span><\/h3>\n

Now you can use\u00a0<\/span>full fidelity schema for Azure Cosmos DB Core (SQL) API containers<\/span><\/a>. This provides you with more flexibility to run analytical queries against Core (SQL) API containers with mixed data types for properties or nested structures. Here is some important information on this capability:<\/span>\u00a0<\/span><\/p>\n