Azure Cosmos DB: Develop with the Table API in .NET

Azure Cosmos DB is Microsoft’s globally distributed multi-model database service. You can quickly create and query document, key/value, and graph databases, all of which benefit from the global distribution and horizontal scale capabilities at the core of Azure Cosmos DB.

This tutorial covers the following tasks:

Tables in Azure Cosmos DB

Azure Cosmos DB provides the Table API (preview) for applications that need a key-value store with a schema-less design. Azure Table storage SDKs and REST APIs can be used to work with Azure Cosmos DB. You can use Azure Cosmos DB to create tables with high throughput requirements. Azure Cosmos DB supports throughput-optimized tables (informally called "premium tables"), currently in public preview.

You can continue to use Azure Table storage for tables with high storage and lower throughput requirements. Azure Cosmos DB will introduce support for storage-optimized tables in a future update, and existing and new Azure Table storage accounts will be seamlessly upgraded to Azure Cosmos DB.

If you currently use Azure Table storage, you gain the following benefits with the "premium table" preview:

• Turn-key global distribution with multi-homing and automatic and manual failovers
• Support for automatic schema-agnostic indexing against all properties ("secondary indexes"), and fast queries
• Support for independent scaling of storage and throughput, across any number of regions
• Support for dedicated throughput per table that can be scaled from hundreds to millions of requests per second
• Support for five tunable consistency levels to trade off availability, latency, and consistency based on your application needs
• 99.99% availability within a single region, and ability to add more regions for higher availability, and industry-leading comprehensive SLAs on general availability
• Work with the existing Azure storage .NET SDK, and no code changes to your application

During the preview, Azure Cosmos DB supports the Table API using the .NET SDK. You can download the Azure Storage Preview SDK from NuGet, that has the same classes and method signatures as the Azure Storage SDK, but also can connect to Azure Cosmos DB accounts using the Table API.

To learn more about complex Azure Table storage tasks, see:

• Introduction to Azure Cosmos DB: Table API
• The Table service reference documentation for complete details about available APIs Storage Client Library for .NET reference

About this tutorial

This tutorial is for developers who are familiar with the Azure Table storage SDK, and would like to use the premium features available using Azure Cosmos DB. It is based on Get Started with Azure Table storage using .NET and shows how to take advantage of additional capabilities like secondary indexes, provisioned throughput, and multi-homing. We cover how to use the Azure portal to create an Azure Cosmos DB account, and then build and deploy a Table application. We also walk through .NET examples for creating and deleting a table, and inserting, updating, deleting, and querying table data.

If you don't already have Visual Studio 2017 installed, you can download and use the free Visual Studio 2017 Community Edition. Make sure that you enable Azure development during the Visual Studio setup.

If you don't have an Azure subscription, create a free account before you begin.

Create a database account

Let's start by creating an Azure Cosmos DB account in the Azure portal.

1. In a new window, sign in to the Azure portal.

2. In the left menu, click New, click Databases, and then under Azure Cosmos DB, click Create.

   

3. In the New account blade, specify the desired configuration for the Azure Cosmos DB account.

   With Azure Cosmos DB, you can choose one of four programming models: Gremlin (graph), MongoDB, SQL (DocumentDB), and Table (key-value).

   In this quick start we'll be programming against the Table API so you'll choose Table (key-value) as you fill out the form. But if you have graph data for a social media app, document data from a catalog app, or data migrated from a MongoDB app, realize that Azure Cosmos DB can provide a highly available, globally-distributed database service platform for all your mission-critical applications.

   Fill out the New account blade using the information in the screenshot as a guide. You will choose unique values as you set up your account so your values will not match the screenshot exactly.

   

   Setting	Suggested value	Description
   ID	Unique value	A unique name you choose to identify the Azure Cosmos DB account. documents.azure.com is appended to the ID you provide to create your URI, so use a unique but identifiable ID. The ID may contain only lowercase letters, numbers, and the '-' character, and must be between 3 and 50 characters.
   API	Table (key-value)	We'll be programming against the Table API later in this article.
   Subscription	Your subscription	The Azure subscription that you want to use for the Azure Cosmos DB account.
   Resource Group	The same value as ID	The new resource group name for your account. For simplicity, you can use the same name as your ID.
   Location	The region closest to your users	The geographic location in which to host your Azure Cosmos DB account. Choose the location closest to your users to give them the fastest access to the data.

4. Click Create to create the account.

5. On the toolbar, click Notifications to monitor the deployment process.

   

6. When the deployment is complete, open the new account from the All Resources tile.

   

Clone the sample application

Now let's clone a Table app from github, set the connection string, and run it.

1. Open a git terminal window, such as git bash, and cd to a working directory.

2. Run the following command to clone the sample repository.

   git clone https://github.com/Azure-Samples/azure-cosmos-db-table-dotnet-getting-started
   

3. Then open the solution file in Visual Studio.

Update your connection string

Now go back to the Azure portal to get your connection string information and copy it into the app.

1. In the Azure portal, in your Azure Cosmos DB account, in the left navigation click Keys, and then click Read-write Keys. You'll use the copy buttons on the right side of the screen to copy the connection string into the app.config file in the next step.

2. In Visual Studio, open the app.config file.

3. Copy your URI value from the portal (using the copy button) and make it the value of the account-key in app.config. Use the account name created earlier for account-name in app.config.

<add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=account-name;AccountKey=account-key;TableEndpoint=https://account-name.documents.azure.com" />

Build and deploy the app

1. In Visual Studio, right-click on the project in Solution Explorer and then click Manage NuGet Packages.

2. In the NuGet Browse box, type WindowsAzure.Storage-PremiumTable. Check Include prerelease versions.

3. From the results, install the WindowsAzure.Storage-PremiumTable and choose the preview build 0.0.1-preview. This action installs the Azure Table storage package and all dependencies.

4. Click CTRL + F5 to run the application.

You can now go back to Data Explorer and see query, modify, and work with this table data.

Azure Cosmos DB capabilities

Azure Cosmos DB supports a number of capabilities that are not available in the Azure Table storage API. The new functionality can be enabled via the following appSettings configuration values. We did not introduce any new signatures or overloads to the preview Azure Storage SDK. This allows you to connect to both standard and premium tables, and work with other Azure Storage services like Blobs and Queues.

Key	Description
TableConnectionMode	Azure Cosmos DB supports two connectivity modes. In Gateway mode, requests are always made to the Azure Cosmos DB gateway, which forwards it to the corresponding data partitions. In Direct connectivity mode, the client fetches the mapping of tables to partitions, and requests are made directly against data partitions. We recommend Direct, the default.
TableConnectionProtocol	Azure Cosmos DB supports two connection protocols - Https and Tcp. Tcp is the default, and recommended because it is more lightweight.
TablePreferredLocations	Comma-separated list of preferred (multi-homing) locations for reads. Each Azure Cosmos DB account can be associated with 1-30+ regions. Each client instance can specify a subset of these regions in the preferred order for low latency reads. The regions must be named using their display names, for example, West US. Also see Multi-homing APIs.
TableConsistencyLevel	You can trade off between latency, consistency, and availability by choosing between five well-defined consistency levels: Strong, Session, Bounded-Staleness, ConsistentPrefix, and Eventual. Default is Session. The choice of consistency level makes a significant performance difference in multi-region setups. See Consistency levels for details.
TableThroughput	Reserved throughput for the table expressed in request units (RU) per second. Single tables can support 100s-millions of RU/s. See Request units. Default is 400
TableIndexingPolicy	Consistent and automatic secondary indexing of all columns within tables
TableQueryMaxItemCount	Configure the maximum number of items returned per table query in a single round trip. Default is -1, which lets Azure Cosmos DB dynamically determine the value at runtime.
TableQueryEnableScan	If the query cannot use the index for any filter, then run it anyway via a scan. Default is false.
TableQueryMaxDegreeOfParallelism	The degree of parallelism for execution of a cross-partition query. 0 is serial with no pre-fetching, 1 is serial with pre-fetching, and higher values increase the rate of parallelism. Default is -1, which lets Azure Cosmos DB dynamically determine the value at runtime.

To change the default value, open the app.config file from Solution Explorer in Visual Studio. Add the contents of the <appSettings> element shown below. Replace account-name with the name of your storage account, and account-key with your account access key.

<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
    <appSettings>
      <!-- Client options -->
      <add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=account-name;AccountKey=account-key; TableEndpoint=https://account-name.documents.azure.com" />
      <add key="TableConnectionMode" value="Direct"/>
      <add key="TableConnectionProtocol" value="Tcp"/>
      <add key="TablePreferredLocations" value="East US, West US, North Europe"/>
      <add key="TableConsistencyLevel" value="Eventual"/>

      <!--Table creation options -->
      <add key="TableThroughput" value="700"/>
      <add key="TableIndexingPolicy" value="{""indexingMode"": ""Consistent""}"/>

      <!-- Table query options -->
      <add key="TableQueryMaxItemCount" value="-1"/>
      <add key="TableQueryEnableScan" value="false"/>
      <add key="TableQueryMaxDegreeOfParallelism" value="-1"/>
      <add key="TableQueryContinuationTokenLimitInKb" value="16"/>

    </appSettings>
</configuration>

Let's make a quick review of what's happening in the app. Open the Program.cs file and you find that these lines of code create the Table resources.

Create the table client

You initialize a CloudTableClient to connect to the table account.

CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

This client is initialized using the TableConnectionMode, TableConnectionProtocol, TableConsistencyLevel, and TablePreferredLocations configuration values if specified in the app settings.

Create a table

Then, you create a table using CloudTable. Tables in Azure Cosmos DB can scale independently in terms of storage and throughput, and partitioning is handled automatically by the service. Azure Cosmos DB supports both fixed size and unlimited tables. See Partitioning in Azure Cosmos DB for details.

CloudTable table = tableClient.GetTableReference("people");

table.CreateIfNotExists();

There is an important difference in how tables are created. Azure Cosmos DB reserves throughput, unlike Azure storage's consumption-based model for transactions. The reservation model has two key benefits:

• Your throughput is dedicated/reserved, so you never get throttled if your request rate is at or below your provisioned throughput
• The reservation model is more cost effective for throughput-heavy workloads

You can configure the default throughput by configuring the setting for TableThroughput in terms of RU (request units) per second.

A read of a 1-KB entity is normalized as 1 RU, and other operations are normalized to a fixed RU value based on their CPU, memory, and IOPS consumption. Learn more about Request units in Azure Cosmos DB.

Next, we walk through the simple read and write (CRUD) operations using the Azure Table storage SDK. This tutorial demonstrates predictable low single-digit millisecond latencies and fast queries provided by Azure Cosmos DB.

Add an entity to a table

Entities in Azure Table storage extend from the TableEntity class and must have PartitionKey and RowKey properties. Here's a sample definition for a customer entity.

public class CustomerEntity : TableEntity
{
    public CustomerEntity(string lastName, string firstName)
    {
        this.PartitionKey = lastName;
        this.RowKey = firstName;
    }

    public CustomerEntity() { }

    public string Email { get; set; }

    public string PhoneNumber { get; set; }
}

The following snippet shows how to insert an entity with the Azure storage SDK. Azure Cosmos DB is designed for guaranteed low latency at any scale, across the world.

Writes complete <15 ms at p99 and ~6 ms at p50 for applications running in the same region as the Azure Cosmos DB account. And this duration accounts for the fact that writes are acknowledged back to the client only after they are synchronously replicated, durably committed, and all content is indexed.

The Table API for Azure Cosmos DB is in preview. At general availability, the p99 latency guarantees are backed by SLAs like other Azure Cosmos DB APIs.

// Create a new customer entity.
CustomerEntity customer1 = new CustomerEntity("Harp", "Walter");
customer1.Email = "Walter@contoso.com";
customer1.PhoneNumber = "425-555-0101";

// Create the TableOperation object that inserts the customer entity.
TableOperation insertOperation = TableOperation.Insert(customer1);

// Execute the insert operation.
table.Execute(insertOperation);

Insert a batch of entities

Azure Table storage supports a batch operation API, that lets you combine updates, deletes, and inserts in the same single batch operation. Azure Cosmos DB does not have some of the limitations on the batch API as Azure Table storage. For example, you can perform multiple reads within a batch, you can perform multiple writes to the same entity within a batch, and there is no limit on 100 operations per batch.

// Create the batch operation.
TableBatchOperation batchOperation = new TableBatchOperation();

// Create a customer entity and add it to the table.
CustomerEntity customer1 = new CustomerEntity("Smith", "Jeff");
customer1.Email = "Jeff@contoso.com";
customer1.PhoneNumber = "425-555-0104";

// Create another customer entity and add it to the table.
CustomerEntity customer2 = new CustomerEntity("Smith", "Ben");
customer2.Email = "Ben@contoso.com";
customer2.PhoneNumber = "425-555-0102";

// Add both customer entities to the batch insert operation.
batchOperation.Insert(customer1);
batchOperation.Insert(customer2);

// Execute the batch operation.
table.ExecuteBatch(batchOperation);

Retrieve a single entity

Retrieves (GETs) in Azure Cosmos DB complete <10 ms at p99 and ~1 ms at p50 in the same Azure region. You can add as many regions to your account for low latency reads, and deploy applications to read from their local region ("multi-homed") by setting TablePreferredLocations.

You can retrieve a single entity using the following snippet:

// Create a retrieve operation that takes a customer entity.
TableOperation retrieveOperation = TableOperation.Retrieve<CustomerEntity>("Smith", "Ben");

// Execute the retrieve operation.
TableResult retrievedResult = table.Execute(retrieveOperation);

Query entities using automatic secondary indexes

Tables can be queried using the TableQuery class. Azure Cosmos DB has a write-optimized database engine that automatically indexes all columns within your table. Indexing in Azure Cosmos DB is agnostic to schema. Therefore, even if your schema is different between rows, or if the schema evolves over time, it is automatically indexed. Since Azure Cosmos DB supports automatic secondary indexes, queries against any property can use the index and be served efficiently.

CloudTable table = tableClient.GetTableReference("people");

// Filter against a property that's not partition key or row key
TableQuery<CustomerEntity> emailQuery = new TableQuery<CustomerEntity>().Where(
    TableQuery.GenerateFilterCondition("Email", QueryComparisons.Equal, "Ben@contoso.com"));

foreach (CustomerEntity entity in table.ExecuteQuery(emailQuery))
{
    Console.WriteLine("{0}, {1}\t{2}\t{3}", entity.PartitionKey, entity.RowKey,
        entity.Email, entity.PhoneNumber);
}

In preview, Azure Cosmos DB supports the same query functionality as Azure Table storage for the Table API. Azure Cosmos DB also supports sorting, aggregates, geospatial query, hierarchy, and a wide range of built-in functions. The additional functionality will be provided in the Table API in a future service update. See Azure Cosmos DB query for an overview of these capabilities.

Replace an entity

To update an entity, retrieve it from the Table service, modify the entity object, and then save the changes back to the Table service. The following code changes an existing customer's phone number.

TableOperation updateOperation = TableOperation.Replace(updateEntity);
table.Execute(updateOperation);

Similarly, you can perform InsertOrMerge or Merge operations.

Delete an entity

You can easily delete an entity after you have retrieved it by using the same pattern shown for updating an entity. The following code retrieves and deletes a customer entity.

TableOperation deleteOperation = TableOperation.Delete(deleteEntity);
table.Execute(deleteOperation);

Delete a table

Finally, the following code example deletes a table from a storage account. You can delete and recreate a table immediately with Azure Cosmos DB.

CloudTable table = tableClient.GetTableReference("people");
table.DeleteIfExists();

Clean up resources

If you're not going to continue to use this app, use the following steps to delete all resources created by this tutorial in the Azure portal.

1. From the left-hand menu in the Azure portal, click Resource groups and then click the name of the resource you created.
2. On your resource group page, click Delete, type the name of the resource to delete in the text box, and then click Delete.

Next steps

In this tutorial, we covered how to get started using Azure Cosmos DB with the Table API, and you've done the following:

You can now proceed to the next tutorial and learn more about querying table data.