Azure Cosmos DB

The Azure Cosmos DB is a NoSQL database or “nonrelational” database where the data is stored in Documents and these documents are organized as collections.

The benefit of using a NoSQL database like Cosmos DB is to handle large amounts of data that changes frequently and needs to be available in multiple geographic locations. It is also highly scalable which makes it useful when the growth rate is unknown.

The relational database Developer and Administrator may not be familiar with this type of Database in Azure so we will walk through setting up a new DB and populating some data manually using the Azure portal.

Azure Account PreRequisites

You will need an Azure account to follow through this example, I am using a Free Azure Account to write this post.

There are plenty of examples for creating an Azure account so for this tutorial you should already have an Azure account and have created a “Resource Group”, or have a Resource Group you can use to add the Cosmos DB resource to.

Since the only thing in my Resource Group is the Database I am just calling it “COSMOS”, Azure will add the region info and a string of numbers to the end of it. It should look something like:

COSMOS_RG_East_US_04a7fa7c-36B1-471b-2b40-3e6cb921322

Create Azure Cosmos DB Account

Once created, click on the Resource Group or find it under “Resource Groups” in the Portal Menu. You can click the “Create” button and choose from the Popular Products or start from the Portal Menu.

Using the “Home” portal menu:

• Click on the “Resource Groups”
• Click the Name of your group (eg. COSMOS_RG_East…)
• Click the “Create” button to “Create a resource”
• Click “Azure Cosmos DB” or find it in the Databases Category
• Click the “Create” button

We are using the “Core (SQL) – Recommended”, Click the Create button.

Basics: Create a Container (Create Azure Cosmos DB Account – Core (SQL))

Be sure to change the Subscription and Resource Group if they are not correct.

The Account Name is a little weird as this needs to be unique for more than just your account and you cannot use spaces or underscores ( _ ). You also might get the “Name is not available” message which means someone already chose it. Avoid obvious names like “testingdb” or “mycosmosdb”. I used:

dbtales-cosmosdb

Provisioned Throughput: Choose to manually select your expected throughput and the ability to add more Azure regions later. The MAX storage is unlimited here.

Check “Limit total account throughput” if necessary.

Serverless: Choose to limit the MAX storage and limit the DB to 1 Azure region.

For this example we are choosing the “Serverless” as this will not be a Prod database.

Cosmos DB Account Settings

Be sure to review the other tabs such as Global Distribution, Networking and the Backup Policy. For a production database disaster recovery will be an important topic.

For this example we will be accepting the defaults and Click “Review + Create”.

Note: If using the Free Azure Tier you are limited to one account so you might get “client … does not have authorization to perform action” if you already have a Cosmos DB Account created.

Creating the Cosmos DB Account may take a few minutes, in my case I created the DB in the East region and it took 6 minutes to complete.

Data Explorer

After the account is created, head back to the portal menu and click the “Data Explorer”. For the relational DBA’s, The terminology is a little different and the “Container” is like the Database and “Items” are like tables but not really 🙂

Click on “New Container” and type a name for the “Database id”, I used:

MySampleDB

Leave the settings as the default for now.
Enter a container id, I used:

MySampleContainer

The partition key can be anything depending on the data we are adding but needs to be a value in your JSON. There is already an ID created so I am using a different ID to demonstrate the difference. We will be creating this manually anyway so I used myid:

/myid

Click OK to create the new Database, it will appear under “SQL API”.

If you expand the “MySampleDB” and then the “MySampleContainer” you will a few familiar things like “Stored Procedures”, “User Defined Functions” and “Triggers”.

Click “MySampleContainer” and notice the the Tabs will change, then try clicking “Items”.

Add Data Using New Items

Let’s add some data to this database using some JSON code. Click on the “New Item” button replace the default ” { “id”: “replace_with_new_document_id” } ” with this:

{
    "id": "2001",
    "myid": "234",
    "class": "Feb. CDB Training",
    "date": "2/22/2031",
	"curriculum": {
		"subject1": "Azure Beginner",
		"subject2": "Cosmos DB Basics",
		"subject3": "Importing to Cosmos DB"
		},
    "student": [
			{ "name": "Samphry"	},
			{ "name": "Amand"	},
			{ "name": "Stanley"	}
		]
}

Click “Save” to add the Item to the database. You should see a new “id”, 2001, and our myid, 234.

Click “New Item” again and add a few more records so we can query them. Just be sure to change the “id” and other data so as not to duplicate and you must click “New Item” for each and “Save” will be grey’d out if the JSON format is incorrect (format error).

Note: These are JSON snippets so you need to paste them and Save one at a time.

{
    "id": "2002",
    "myid": "235",
    "class": "March CDB Training",
    "date": "3/12/2031",
	"curriculum": {
		"subject1": "Azure Beginner",
		"subject2": "Cosmos DB Basics",
		"subject3": "Importing to Cosmos DB"
		},
    "student": [
			{ "name": "Coby"	},
			{ "name": "Jostlin"	},
			{ "name": "Shak"	}
		]
}

{
    "id": "2003",
    "myid": "236",
    "class": "June CDB Training",
    "date": "6/03/2031",
	"curriculum": {
		"subject1": "Azure Beginner",
		"subject2": "Cosmos DB Basics",
		"subject3": "Importing to Cosmos DB"
		},
    "student": [
			{ "name": "Amandeep"	},
			{ "name": "Kerry",
			{ "name": "Scott"	}
		]
}

Cosmos DB Query Examples

Now we can query the new records, Click the “New Query” button and try some of these queries to see the results.

SELECT c.id, c.date, c.curriculum.subject2 from c WHERE c.id = "2003"

SELECT * FROM MySampleContainer t WHERE t.id = "2002"

SELECT t.date, t.curriculum FROM MySampleContainer t WHERE t.myid = "236"

SELECT t.date, t.curriculum FROM MySampleContainer t WHERE t.id = "2001"

SELECT c.name FROM MySampleContainer t JOIN c IN t.student WHERE t.myid = "235"

SELECT c.class, c.name FROM MySampleContainer t JOIN c IN t.student WHERE t.date > "3/12/2031"

Connect SSMS to Cosmos DB

For the DBA’s looking to connect to Cosmos DB using the SQL Server Management System, sorry this does not work as you would with SQL Server.
There is a method using ODBC and a linked Server but I have not attempted this yet. Please let me know if you get it to work.

---