When working with loads and loads of data, we need to scale out the database. By scaling out the database, it means to work on the storage as well as throughput.

The container that we create in the CosmosDB is partitioned then to manage its growth because as and when the data will be added to the database, it will be added accordingly and partitions will take place. CosmosDB will then keep on creating as many partitions as needed to manage the data that is growing.

We can think of partitions as glasses filled with water, as and when one glass gets full, water is then filled in the next glass and so on. So basically, one glass (i.e. partition) acts as one container in itself. However, on a larger scale, a container is a collection of partitions.

But the problem that then appears is that when CosmosDB keeps on saving the data in the partitions, how does it look for that particular data in the whole container? Does it check every partition for that required data?

So that’s when the role of partition keys comes into picture. Defining a partition key at the beginning of creation a collection is the most important thing to do. Choosing the right partition key can be dependent on the best property to choose. Once you choose the property which will be the partition key, it then creates a hashed value. This hash is used to figure out as to which partition the data should be stored.

Each partition then contains a fixed storage capacity. Also, CosmosDB has a range-algorithm which stores multiple partition keys within a single partition. All the data with the same partition keys lives in the same partition.

Another important thing is that, when looking for data, you should search for the query by specifying the partition key as well. This makes the task a whole lot easier as CosmosDB would not need to search the whole database, all the partitions to looks for that data. 
The property that we choose as the partition key must be a broad and common one among all the partitions in the database. This makes it easier to search across the data. A partition key must be the one with multiple distinct values as that will make it easier to locate the data widely.

Usually, the best choice for a partition key would be a uniquely defined ID that stores the data for a particular item or person and creates a specific partition for that. This also makes it easier to look for data later by running the query with the particular ID getting the data quickly and easily.

Hot partitions

Choosing the right property for the partition key is very important as this will also create bottlenecks for dealing with large data. For example, if we choose the “Month created” as the partition key, all the data will be directed to the same partition which is definitely not what we want it to do.

Similarly, we do not want something as Land size to be chosen as the partition key because in that case, one land can be much bigger than the other one and that will result in loads of data being directed to one partition.

These problems can be handled by creating a separate container for that particular partition. This new container can then be categorized by a specific partition key and be used much more efficiently.

This means that we create a specific container for Land 7, as example here. And this can further be categorized using a specific partition key.

To summarize, horizontal partitioning helps to manage the growth of the container and then partitions keep on taking place when the data is being added to the database. We also saw that what are the hot partitions and how can those be avoided.

Using our data created inside the Azure Table Storage and working with this data in Cosmos DB needs us to migrate from one to another. In the previous blog posts, we have seen that to work with the data in Cosmos DB, we can simply change the connection string from the Azure Table Storage one to that of Cosmos DB Table API.

In this blog post, we look at how we can actually migrate from one to another and what all changes do we need to bring in our code, if at all.

We create a new CosmosDB account using the table API, since we are sure that we do not need to use any other API or switch between APIs at a later stage.

In the above screenshot, we click on New to create a new Cosmos DB account, choose databases, and then select Azure Cosmos DB.

When creating the new Database, we enter the ID, and choose the Azure Table API from the dropdown, create a new resource group, and set the location.

The checkbox to enable geo-redundancy works similarly as for SQL API and allows global distribution of data.
We click on Create, and once the deployment is succeeded, we can move on to bring in the data inside our Table.

There are two ways of getting this data out of the Azure Table Storage

• Using AZ Copy, a command line utility that copies data in and out of the Azure Table Storage.
• Using the Cosmos DB Data Migration Tool which we have learned about in some of the previous blog posts.

Let us look at getting the data using the Data Migration tool. The two interfaces that this tool has are GUI and command-line. The GUI is not compatible with the Table API yet, so we use the command line when working with Table API.

Go to the command line and enter the path where you have downloaded the data migration tool. Enter the source and the target details with the connection string and the primary connection string of the new CosmosDB account that we have created for the table API.

This one long command will help you migrate over your table data from table storage to the Cosmos DB account.

dt /s: AzureTable /s.ConnectionString:DefaultEndpointsProtocol=https;AccoutnName=azuretable;AccountKey=OndsgdsjhuiIUVSFDRhdvsgdnuihjnmns+shjdsnndnjdjdnmbbndnmdkjdkjdnmdn==;EndpointSuffix=core.windows.net /s.Table:Users /t:TableAPIBulk /t

followed by the same details for the target.

When this completes, you can go to the Overview page and check if you can see the imported table in here now. You can also click on the table and view the data explorer page and view the data and add, delete, edit, and so on.

We can see the data inside the explorer, but we need to see if these tables would still work.

We should change the connection string inside the application configuration file for the new CosmosDB account.

With this, our application works now, with just changing the connection string the appconfig file. This will now get our properties indexed, predictable throughput. We can also easily globally distribute the data using the portal.

In the blog post about SQL operators and functions, we saw an introduction to Scalar Expression Queries. Let us see what that actually means.

When selecting data from a collection, we use Select * FROM collection, but in case you do not want to mention collection name since it doesn’t help you join across documents, you can emit the FROM clause and run the query without it. 
This leads to querying expressions and not documents. Now querying the expressions result in a scalar value, and thus, the queries resulting in a scalar value are known as Scalar Expression Queries.

Let’s see how to run them now.

Lets say we have some query data inside the query explorer, which look like:

SELECT name
SELECT “hello””
SELECT VALUE “Welcome”

As you can see, none of these queries contain the FROM clause, and therefore, these are known as Scalar Expression queries.
Now when you try to run any of these queries, say

SELECT “hello”

You get the result as:

[
 {
$1: “Hello”
 }
]

Now, since we didn’t provide any name for the query, Cosmos DB generated an identifier by itself. In this case you can give the name as using the query:

SELECT “Hello” AS word

This will give results as:

[
 {
“word”: “Hello”
 }
]

Now since this is an object as a result of running this expression, you can use the query below to get another better result.

SELECT VALUE “Hello”

This will give the result as:

[
“Hello”
]

To select two objects, we use:

SELECT “Hello”, “Hi”

This gives the results as:

[
{
“$1”: “Hello”,
“$2”: “Hi”
}
]

These can again be replaced with our own words then.

SELECT
“Hello” as word1,
“Hi” as word2

We can also shape the JSON object and nest them inside the array of strings.

For example,

SELECT
{
“word1”: “Hello”,
“word2”: “Hi”
}
AS words

The result in this will be the nested one:

[
{
“words”:
{
“word1”: “Hello”,
“word2”: “Hi”
}
}
]

Similarly, we can use many more queries without actually having to use the FROM clause and running the queries with expressions only.

SUMMARY

In this blog post, we learnt what are Scalar Expression Queries and how we can use them to query expressions without using the FROM clause actually. We also saw how we can nest the data and shape our JSON objects flexibly with Scalar Expression Queries.

We have seen in the previous blog posts how the entity-relationship is portrayed when working with the graph databases. So vertices are the entities whereas the edges are called the relationships. But they are actually just items inside the graph-based container.

The vertices and edges have properties associated with them. The properties are:

• ID property
• Label Property

and some other properties like maybe the partition key etc.

The ID property is like an identifier for your different vertices and edges and the Label property is like a name for these vertices and edges to differentiate between them as they would have names for themselves.

So we choose a good value for the partition key of the vertices for high-scale global distribution and use this value in an arbitrary property. And in such a way, each vertex becomes unique with the combination of the partition key and the ID.

EDGES

Edges, the relationships between two vertices, are again just items in the graph-based container. They connect two vertices, so the different property values that edges have are:

• ID property
• Label property
• Properties like partition key etc.
• Cardinality

All the properties have the same meaning as with the vertices but let us look at this new property called the Cardinality property. This basically refers to the no. of vertices attached to a particular edge. One basic edge will have cardinality 2 most of the times with only a source and a target vertex.

This is a small use-case scenario and in the next blog posts, we will see what it means by bidirectional flow, how to create a simple graph, how to write simple Gremlin queries and many more.

In the previous article, we got an introduction to integrating out data into Cosmos DB using a data migration tool and using a sample SQL database server.

In this blog post, we will see how to migrate the data from the SQL server into Cosmos DB. First step to begin with is to install the Data migration tool from the Microsoft Azure Cosmos DB Documentation page.

When this is downloaded, we see that there are two versions of the tool, one is to use from the command line and the second one is a graphical user interface. 

We will use GUID to proceed, i.e. dtui.exe

Here we get this option to import the data from and we get these many options as in the image below:

We then choose any of the given options and the page appears accordingly. So for example, I choose SQL, it then asks me for Connection String, query, and the nesting separator. 

Put in the connection string of your SQL server and the query from your database. You can use the nesting separator to be a ‘.’ This will make the tool look for a . inside your query and separate it with the help of a . into columns.

Having done that, you will get the option to define the target information now.

In the Export To option, you will see three alternatives:

When trying to export to the SQL API, you can probably use the Azure Cosmos DB options, or the other option is to use the JSON file, which will give you the option to work around on the data before pushing everything into the Cosmos DB account, so this comes out to be a better approach most of the times.

But when choosing Azure Cosmos DB as an option, you’d want to go with the bulk option which is super fast, but then only sequential supports the partitioned collections option which is mostly what we want.

Next we put in the connection string of the Cosmos DB SQL API. We can obtain this connection string from the Azure portal but that wont contain the database reference in it, so after the connection string, we need to update the database name in it. 

Now paste it inside the connection string input and append with the database name.

Like below:

connectionstring*; Database=mydatabase

Next, enter you collection name, partition key which you can use zipcode for, and then the collection throughput which you can keep as the default.

Also, you get this option to provide id inside the id field, which is not mandatory. If not given, it creates a unique id for your collection based on the GUID.

With this done, you can go on to next page and move on without creating error log file, if you don’t want it.

 Finally, you’ll come to the summary page. Once you review everything, you can click on import and the process will start.

Now that your import is done without any failures, you can finally go to your data explorer to check if the database ‘mydatabase’ has been created, the collection that you mentioned has been imported and the data is present inside the documents.

With that, you’ll also be able to see all the other resource properties like rid etc. created for you inside the document.

SUMMARY

In this blog post, we saw the usage of Data migration tool to get our data from any source into the Cosmos DB account. From the installation of the Data Migration Tool to getting the data inside the Azure Cosmos DB account, we covered the topics using a practical representation at every step.