This blog post talks about the use of Table API in Cosmos DB for a key-value data model.

The Table API emulates Azure Table Storage which is one of the storage services that came up with Azure in 2010. It came in with the Windows Azure which we now know as Microsoft Azure. It is a no SQL non-relational database where we model our data as key-value pairs.

But wait, what is Key-value Data Model?

Key-value Data Model

A data model which simply stores the items as pairs of keys and values by setting a unique key for an item and retrieving its value using that key. To understand it in terms of the Azure Table Data Storage, the key is made up of actually two different keys. What are those?

Key = Partition Key + Row Key

So basically, every key is a unique combination of a partition key and row key which later gets the value that is associated with this key from the table. This is similar to how documents in a collection are a unique combination of the partition key and ID properties. Another important thing to notice here is that unlike relational databases, we can store items of different types in the same table.

The core usage of Table API is the seamless migration of back-end to the service. But there are some of the limitations that Azure Table Storage suffers from, which Cosmos DB helps with.

• Throughput which is totally unpredictable in Azure Table storage is provisioned in Cosmos DB depending on the number of Request Units (RUs).
• We can geo replicate our tables in Cosmos DB when we have to globally distribute our documents.
• We have automatic indexing in Cosmos DB which has a default indexing strategy automatically indexing every value in the table.

Why use the Table API?

We have several APIs to choose from when working with Cosmos DB since it is a multi-model database. We can also switch from one API to another over the same data because everything is placed over the ARS storage.

The primary usage of Table API is when you want to migrate your data from the Azure Table Storage to Cosmos DB Table API. We really do not need to change any code in our applications.

Just changing the connection string works to migrate your data from Azure Table data storage to Cosmos DB, however, it still uses the Azure Table Data Storage SDK so it is better to upgrade it to the Cosmos DB SDK.

In Azure Cosmos DB, Cassandra API can be used as the data store for the applications that have been written for Apache Cassandra.

This means that the applications users have built using Apache Cassandra can communicate with Cosmos DB using the Cassandra API. And this can be achieved simply by changing the connection string.

This API helps us to interact with the data stored in Azure Cosmos DB using the Cassandra Query Language (CQL), Cassandra-based tools, and Cassandra client drivers.

Some of the benefits of using the Cassandra API for Cosmos DB are:

• Cassandra can handle high read/write throughputs
• Guarantees the consistency of throughputs
• Handles workload when working with messaging apps, fraud detection, etc.
• There is no single point of failure
• Cosmos DB scales a Cassandra cluster to add more nodes without requiring any manual configuration.
• Friendly to work with CQL if a prior experience with SQL

Let us take a look at basic CRUD functionality using CQL (Cassandra Query Language)

Create Operation

INSERT INTO KeyspaceName.TableName (ColName1, ColName2, ColName3 . . . )
VALUES (Col1Value, Col2Value, Col3Value . . . )

Read Operation

SELECT ColName1, ColName2, ColName3 . . .
FROM KeyspaceName.TableName
WHERE ColName1 = Col1Value AND
    ColName2 = Col2Value AND
    ColName2 = Col2Value AND
  . . .

Update Operation

UPDATE KeyspaceName.TableName
SET ColName1 = new Col1Value,
    ColName2 = new Col2Value,
    ColName3 = new Col3Value,
    . . .
WHERE ColName1 = ColValue

Delete Operation

DELETE FROM KeyspaceName.TableName
WHERE ColName1 = ColValue

Summary

In this blog post, we learned about the Cassandra API usage in Cosmos DB. We saw it acts as a data store for the data stored in Azure. We also saw the benefits of using Cassandra API in Cosmos DB.

We learned how to perform CRUD operations using CQL in Cassandra API for Cosmos DB.

In the list of the APIs provided by Cosmos DB, Microsoft’s globally distributed multi-model database service, the API for MongoDB makes it very easy for the Mongo DB users to migrate to Cosmos DB without much of a hassle. We can communicate with the Cosmos DB’s API for MongoDB using any of the open source MongoDB client drivers. The details about these drivers can be found here: https://docs.mongodb.com/ecosystem/drivers/

The users can enjoy all the functionalities of Mongo DB API with the provided benefits of Cosmos DB like global distribution, automatic indexing, etc.

Features

Cosmos DB’s API for MongoDB is compatible with MongoDB server version 3.2. Any MongoDB features or query operators can be viewed in the Cosmos DB API for MongoDB.

Cosmos DB’s API for MongoDB provides support for MongoDB query language constructs, operations, stages, operators, commands, etc.

Some of the Administration commands are:

• dropDatabase
• listCollections
• drop
• create
• filemd5
• createIndexes
• listIndexes
• dropIndexes

Query and write operation commands:

• findAndModify
• getLastError
• getMore
• insert
• update

As Cosmos DB provides support for Aggregation Pipeline, some of the Aggregation commands are:

• aggregate
• count
• distinct

and Aggregation stages are:

• $skip
• $unwind
• $group
• $sample
• $sort

Example:

{
 “ Name”: “Tom”,
 “Country”: “United States”,
 “Height”: 172,
 “Gender”: “Male”,
 “Age”: 43,
}

Operators

$exists{ "Age": { $exists: true } }

$eq{ "Name": { $eq: "Tom" } }

$type{ "Age": { $type: "string" } }

and so on.

Conclusion

In this blog post, we saw the different support features of Mongo DB API by Cosmos DB in short. We also saw an example of using the operators on some JSON data.

In the previous articles, we have seen how to query documents using the SQL API and how widely it is used. But then the question that arises is why do we still use other APIs if SQL APIs are so flexible to work with for any data type.

In this blog post, we will focus on comparing the differences between the SQL API and Table API.

The concept of containers in Cosmos DB using any API remains the same, with the only difference being in the name used to call that container. In the case of SQL API, it is called collection, however, table when using Table API.

Items

The SQL API calls the items as documents which come inside a collection. This basically means that the collection comprises of many documents with unique document IDs. In the case of Table API, it is called as Rows. So just like a collection contains many documents, a table contains various rows which are the items in that table and each row is indexed.

Partition Key

When choosing the partition key for the data, when using SQL API, we can actually use any of the attributes as the partition key. However, this is different when using the Table API. With Table API, we need to name the attribute to be used as PartitionKey.

SQL API:

{
 category: “fruits”,
 id: “apple”,
 quantity: 30
 }

Table API:

Unique Identifier

In the SQL API, the id that is used as a unique identifier is called RowKey when using the Table API. The other properties can be simply used as an arbitrary key-value pair.

CONCLUSION

In this blog post, we quickly saw the important differences between the SQL API that is widely used and the Table API that is most used when getting the data from Azure Table Data Storage to Cosmos DB Table API.

If you have been reading about CosmosDB for some time now, you might have seen how fast it works. Cosmos DB works really fast and does not disable viewing/editing any document or collection when somebody is working on them. However, this results in the possibility of two actions happening parallel on the same document or collection.

Due to this, it is possible that somebody who started editing the document at a later point makes the changes and the one already working on the document is actually trying to update the old document. This scenario arises because of the “Last write wins” strategy that Cosmos DB follows.

LAST WRITE WINS

This means that whenever the last user made changes to the document or the collection in Cosmos DB, that will be considered as the latest document. So basically, the valid document will be the one which is the latest version of the document.

Now to avoid the scenario of using the outdated document and making sure that the document that you are modifying is the latest version of it, comes in the concept of Optimistic concurrency.

Cosmos DB handles this situation by providing the concept of optimistic concurrency. The response header tag and the document contain a special tag called as an entity tag. This is used along with an AccessCondition tag to ensure that we get an error if we are updating an old version of the document.

Entity Tag or Etag

The resources with the property _etag in Cosmos DB contain an Etag which is a system-defined meta property. It looks something like:

“_etag”: “\”00000000–0000–0000–5347-b22u29kl87v8\””

We can provide the etag with our update, insert or delete operations. This can be done by proving an object called AccessCondition as per RequestOptions. When we provide this etag, we need to specify the behavior that should be enforced as a part of the AccessCondition object.

Let us say you have an AccessCondition object.

public AccessCondition{
}

It contains two properties:

• Type
• Condition

The condition property is where the etag is specified to perform operations. The Type property, however, is the enumerator of type AccessConditionType and has two values:

• IfMatch
• IfNoneMatch

And if the AccessCondition is not met after the request, CosmosDB rejects the operation and returns failure.