Aveek Das
Exploring the Data

Learn NoSQL in Azure: Getting started with DocumentDB SQL API

July 7, 2021 by

This article is a part of the series Learn NoSQL in Azure where we will learn about the DocumentDB SQL API in detail. Azure Cosmos DB is a serverless NoSQL database service provided by Azure that is highly available, globally distributed, and responds within the minimum latency. Due to these features, it is a perfect fit for building modern-day applications using the DocumentDB or SQL API of Azure Cosmos DB.

The basic way of storing data is in JSON form, which everyone is quite familiar with. Also, the SQL API for DocumentDB provides a unique way of querying your NoSQL data using plain SQL queries. This is very easy to understand because most of the customers or users of Azure Cosmos DB have some idea of relational databases and when SQL queries are executed on these NoSQL databases, it makes working with data really interesting.

Creating a DocumentDB using the SQL API in Azure

Let us now head over to the Azure portal and start building a NoSQL database. Navigate to https://portal.azure.com and click on Create a Resource. Under the Databases tab, click on Azure Cosmos DB and you will be taken to a new page to start defining your own template for the database creation.

Create a resource using Azure Cosmos DB - DocumentDB

Figure 1 – Create a resource using Azure Cosmos DB

Once you click on this, a new page for getting started with Azure Cosmos DB will appear as follows. You need to provide the following details to get started.

  • Subscription –This is the default subscription mode for your Azure account under which the billing is done usually
  • Resource Group – The resource group is required to group the resources that work towards the same logical application. If you do not have one, feel free to create a new one
  • Account Name – This is a unique name that you need to choose for your DocumentDB API. This name is important because it will also appear on your URI while connecting from external sources or code
  • API – Since Azure Cosmos DB is a multi-model database service, that offers APIs like SQL, MongoDB, Graph, etc. you need to choose one to get started. For the purpose of this tutorial, we will proceed with the Core (SQL) API, which is the API for creating DocumentDB in Azure
  • Location – The global location where you would want your database to reside. Practically, this has to be the location where most of your users accessing your application will reside since it will decrease the latency, and responses will be comparatively faster
  • Capacity Mode – This mode is defined as how your Azure Cosmos DB database will be billed while consuming the services. There are two pre-defined modes – Provisioned throughput and Serverless (in preview mode). By default, Provisioned Throughput is selected and we will proceed with that as well

There are some other options as well that you need to provide in order to get started. You can keep the default settings and proceed forward.

  • Apply Free Tier Discount – Apply
  • Account Type – Non-Production
  • Geo-Redundancy – Disable
  • Multi-Region Writes – Disable

Configuration Details for Azure Cosmos DB (SQL API)

Figure 2 – Configuration Details for Azure Cosmos DB (SQL API)

In the Networking tab, select Connectivity Method as All Networks. This can be used to provide more restricted access if required only to private networks.

Configuring the Connectivity Mode for Azure Cosmos DB - DocumentDB

Figure 3 – Configuring the Connectivity Mode for Azure Cosmos DB

For the Backup Policy, I will go with the default settings that are selected. Notice that only two copies of your backup will be free of charge, while all additional backups will be chargeable.

Setting up Backup Configuration

Figure 4 – Setting up Backup Configuration

Once done, review all the details and click on Create. It might take some time for Azure to create all the required resources.

Creating the resource on Azure - DocumentDB

Figure 5 – Creating the resource on Azure

Once the deployment is completed, you can navigate to the Resource and the Overview page appears.

Azure Cosmos DB database deployed

Figure 6 – Azure Cosmos DB database deployed

As you can see in the figure above, the public URI of the database is available and it can be used to communicate with the database service publicly. Since this is a fresh database account, we do not have any databases yet. So we need to go ahead and create databases and containers to get started.

First, we need to create a Database and then a Container. Click on New Container and select New Database. The New Database blade appears on the right.

Configuring New Database

Figure 7 – Configuring New Database

Since this is database is only for demonstration purposes, I will go ahead with 40 RU/s as the higher limit for scale. Next is adding the new container to store the data. A container in NoSQL is equivalent to a table in a relational database.

Creating a new Container in Azure Cosmos DB

Figure 8 – Creating a new Container in Azure Cosmos DB

As you can see in the figure above, we have provided a new name for the container “orders” as we are going to store some order-related information in the database. Additionally, we also need to specify a partition key using which the database engine will be able to create logical as well as physical partitions. This key has to exist for all the item entries in the container. Click OK once done. This will create the orders collection under the sqlshack-db database.

Orders collection has been created

Figure 9 – Orders collection has been created

Now, the next step is to add items to the orders collection. This can be done by opening the Items blade and selecting New Item from the top menu bar. You will notice that a query tab appears and you can enter data in there. I have already created some sample data which you can use to create your own dataset.

Saving an Item

Figure 10 – Saving an Item

As soon as you save your item, you will see that the item has been saved, but there are some additional fields within the item which are preceded by an underscore.

New Item Saved

Figure 11 – New Item Saved

The system-generated new fields added within the item are as follows.

  • _rid – This is the resource ID
  • _self – This is the unique addressable ID of the document
  • _etag – This is used for concurrency control
  • _attachments – If any attachments are present for this document
  • _ts – The last updated timestamp

Similarly, you can add as many items as you want to the collection.

Exploring the Data

Figure 12 – Exploring the Data

Once you have added all the data into the collection, you can view the data in the explorer. The data will be available by the partition key that has been defined earlier. As you can see, we have two partitions created, and both the records are available under each of the partitions.

Alternatively, you can also use SQL to query your data. To write a new query, select New SQL Query from the menu and write the query as follows.

Click on Execute Query and this will return all the records from the database.

Querying the data using SQL

Figure 13 – Querying the data using SQL

Interestingly, you can also view the Query Stats for the executed query.

Viewing the Query Stats

Figure 14 – Viewing the Query Stats

With this, you can get an idea about how your monthly costs will be depending on the RUs consumed.

Conclusion

In this article, we have learned how to use the Azure Cosmos DB to create a DocumentDB by using the core SQL API. Initially, Azure launched only DocumentDB as its NoSQL databases, however, later they have introduced Azure Cosmos DB which is a kind of a superset of the DocumentDB along with other APIs like the Key-Value store, columnar store, and graph databases. We are going to learn about each of these APIs in detail in the upcoming articles in the series. The unique feature of Azure Cosmos DB to use SQL queries to read data from the Cosmos DB makes it really helpful and handy for customers to start using it as a service.

Table of contents

Learn NoSQL in Azure: An overview of Azure Cosmos DB
Learn NoSQL in Azure: Diving Deeper into Azure Cosmos DB
Learn NoSQL in Azure: Getting started with DocumentDB SQL API
Aveek Das
Azure, Azure Cosmos DB

About Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure. He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums. In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn View all posts by Aveek Das

168 Views