In this very short blog post, we ll see an introduction on how to query Cosmos DB with the SQL API. Now SQL is just like another language when you want to get data out of the database, but the problem that arises is that it a relational database whereas CosmosDB is a NoSQL database.

To avoid this scenario, the semantics used for SQL here are all JSON documents and the JavaScript data types in place of SQL API data types. There is this another option of using the LINQ provider when working with the .NET sdk.

What is LINQ?

Linq provider translates the language-integrated queries written in C# into Cosmos DB SQL. It is good to use Linq for such query translation however this also eventually is an SQL only.

So to avoid all of this, it is implemented in the form of JSON and JavaScript. For Example, in place of using T-SQL, expressions will be evaluated as the JavaScript expressions. Similarly, the operations like SELECT, FROM, WHERE etc. are used but only with JavaScript defined data types like boolean, number etc.

But…

JavaScript has objects, nested arrays, etc. How does SQL manage with that!!?

So, to do this, The Cosmos DB team answers with the options below:

• Using the dot notation to navigate to the path to properties of the nested objects, and arrays.
• The second technique is, just like the tables are joined using Inner Join inside a relational database, we can use Intra-document join to combine the nested elements under a parent element.

For an example, let us consider this:

Doesn’t it look very much like a SQL query?

But do you notice these dot notations between the properties which are actually helping to navigate to the paths of the properties. Also, you can see these intra-document joins done between these documents which again show that it isn’t a SQL query but a smart way of using it by implementing the techniques of JSON semantics and JavaScript expressions.

SUMMARY

In this blog post, we saw an introduction on querying CosmosDB with SQL API. We covered how we can adapt to NoSQL CosmosDB and work with it using the SQL API which is used for relational databases ironically.

When working with the data migrated from the Azure Table Storage to the CosmosDB Table API, we need to look at one more aspect.

In the article about migrating from ATS to CosmosDB Table API, we saw how just changing the connection string in the appconfig file helps us migrate the data to CosmosDB. There is no need to redeploy the application as such, and the application still continues to work.

But one important thing to look at is, we did not really upgrade the SDK to that of Cosmos DB. We are still using the Azure Table Storage SDK. Though it is not mandatory, it is definitely a recommended thing to do.

The Cosmos DB SDK for Table API communicated with the Cosmos DB with the native binary protocol that bypasses the gateway. We need to rebuild and redeploy our application because there are new assemblies to be used now from the NuGet packages.

Right-click and choose the Manage NuGet packages option. Search for Cosmos DB and select the Microsoft Azure Cosmos DB Table option. Install it and Visual Studio does the work for you. It will get it inside the database and add it to the required fields.

Now there are two namespaces, one for the old Azure Table Storage SDK and one for this new one, so we go to the code and change the using statements.

The statements that contain the Windows.Azure keyword refer to the old SDK so we replace it with the Microsoft.Azure.Storage keyword and Microsoft.Azure.CosmosDB.Table.

The same steps for replacing the using statements should be performed in the entity class. In our case, the Users entity class.

With this step completed, our code is actually pointing to the new SDK that we have provided and the code still compiles and displays the tables. As we saw, there was no complex change in the code, but just the change in statements to point to the SDK usage.

CONCLUSION
In this blog post, we saw how to upgrade to Cosmos DB SDK for better performance in our database and how it is so simple to change few small things and you are good to go.

In the previous blog posts, we have seen the usage of SQL API and Table API to work with our database in CosmosDB. In this article, we look at the usage of Gremlin API to work with the Graph databases.

These different APIs that we work with actually act as containers for our data in the databases. So basically, Gremlin API is a container for graphs in the graph databases, same is with the tables in a Table API and documents in a SQL API.

So, as we have worked with entities and relationships, in Graph databases these terms are replaced by vertex and edges. So basically, a vertex is some entity and the edge is a relationship that is one-way between two vertices.

We can say that to learn what are graph databases, this knowledge of vertex and edges is the building block. And just like we store the key-value pair attributes for the SQL or the Table API, we can store any number of attributes with the vertex and edges.

Working with Vertices and Edges

Let us see how to work with the vertices that is the entities and the edges which are the relationships between those vertices. So, to create, read, update, and delete the vertices and edges, we work with GraphSON and Gremlin.

What are GraphSON and Gremlin? These conventions come from the Apache Open Source project called TinkerPop and used to work with vertices and edges in graph databases.

GraphSON is used for describing vertices and edges in a JSON format. And Gremlin is a language used to create the vertices and edges and to then query the graphs and their relationships.

One good thing with the graph databases is that they are used for handling complex relationships and are better than relational databases to handle complex entity-relationships. This is because, in a graph database, relationships themselves are defined as objects.

You might be wondering what is so special about Graph databases that everyone is talking about those?

Well, who would not when there is a data model which actually focuses on the connection between two data points (edges/relationships) and not just the data points which are actually just the entities. Or we can say vertices in the context of graph databases.

Our relational databases, as we have seen get very complex when there are a lot of relationships with different entities involved. And this is the scenario when we like to consider graph databases.

So imagine a scenario where you have multiple many-to-many relationships and you need to communicate through using excessive JOINs inside your relational database. A better idea here is to avoid so many JOIN operations and use a Graph Database.

Now, this is one basis for choosing to use a Graph database. Let us look at some other scenarios. Graph Databases are best to use social network type of setup where different entities need to communicate with each again making it a many-to-many kind of relationship. Functionalities like choosing a person, showing their friend list, recommending what you looked at in the past, recommending what your friends like and you don’t yet, and so on.

This not only applies to the virtual examples but the real-life ones too. For example, a college annual festival where there are multiple entities and relationships between each one of those.

CONCLUSION

In this small blog post, we quickly saw the different scenarios when there is a need to use the graph databases and what special features it adds to the implementation that makes it better to work with Graph databases when working with many-to-many relationships.