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.
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
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.
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.
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.
Figure 5 – Creating the resource on Azure
Once the deployment is completed, you can navigate to the Resource and the Overview page appears.
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.
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.
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.
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.
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.
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.
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.
|
1 |
SELECT * FROM c |
Click on Execute Query and this will return all the records from the database.
Figure 13 – Querying the data using SQL
Interestingly, you can also view the Query Stats for the executed query.
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 |
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
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021