Azure CosmosDB Utilizing SQL API
Introduction
On this tutorial, we’re going to focus on Azure CosmosDB and run just a few queries utilizing one of many APIs that’s really useful when creating new functions. Earlier than this, let’s perceive what Azure CosmosDB is, and why it is wonderful.
Azure CosmosDB is a NoSQL Database. By NoSQL, we imply that it shops knowledge in a Non-Relational Format. It’s a document-based database system. Nevertheless, not like others, it’s Multi-Mannequin. Which means there are a number of methods to entry and retailer knowledge in it, relying on the enterprise use circumstances.
- You may migrate knowledge from Cassendra and MongoDB and use Azure CosmosDB as a backend retailer and run queries of Cassendra or MongoDB, whichever you used. Your backend will change and the applying will stay working with minimal adjustments.
- You need to use Azure Desk Storage API, which implies you possibly can change the backend from Azure Desk Storage to Azure CosmosDB by working the identical Queries.
- If the enterprise calls for a Graph Database-Primarily based Question Mannequin, we will use Gremlin API to get issues carried out.
- If we’re constructing an software from scratch, we will use SQL API that’s strongly really useful for working with Azure. The queries we run utilizing SQL API provide question construction much like SQL with some limitations.
Azure CosmosDB is cloud-based. It presents low-latency (<=10ms). It ensures excessive availability i.e. 99.99% Availability and 99.999% in Geo-Replication. It could possibly scale very giant and ensures limitless scaling with excessive workloads.
It has enterprise-level safety.
Anybody can do hands-on with out problem. This can enable builders to do simple on-boarding and enhance their abilities: https://www.documentdb.com/sql/demo
Extra Particulars: https://azure.microsoft.com/en-in/providers/cosmos-db/
Storage Construction
Right here is the diagram illustration of how a doc is organized internally:
Extra particulars: https://docs.microsoft.com/en-us/azure/cosmos-db/account-databases-containers-items
Palms-On
Now, we’re going to work on CosmosDB utilizing SQL API.
Comply with the steps under:
- SELECT * FROM Individuals
To rely all data:
Technique 1
- SELECT COUNT(P) FROM Individuals P
- [
- {
- “$1”: 4
- }
- ]
Technique 2
- SELECT VALUE COUNT(P) FROM Individuals P
- [
- 4
- ]
Notice
WON’T WORK – SELECTCOUNT[
Failed to question merchandise for container Individuals: Gateway Did not Retrieve Question Plan: Message: {“errors”:[{“severity”:”Error”,”location”,
{“start”:13,”end”:14},”code”:”SC1001″,”message”:”Syntax error, incorrect syntax near ‘*’.”}]} ActivityId: 5d1842d2-d232-4948-a4c8-21599f2980c4, Microsoft.Azure.Paperwork.Frequent/2.11.0, Microsoft.Azure.Paperwork.Frequent/2.11.0
Deciding on Particular Columns
- SELECT P.firstname,P.age FROM Individuals P
- [
- {
- “firstname”: “亜妃子”,
- “age”: 5
- },
- {
- “firstname”: “Eva”,
- “age”: 44
- },
- {
- “firstname”: “John”,
- “age”: 23
- },
- {
- “firstname”: “Véronique”,
- “age”: 50
- }
- ]
- SELECT P.firstname,P.age FROM Individuals P WHERE P.age>20 AND P.age<50
- [
- {
- “firstname”: “Eva”,
- “age”: 44
- },
- {
- “firstname”: “John”,
- “age”: 23
- }
- ]
Array Information
- SELECT [P.firstname,P.age] AS Array_fname_age FROM Individuals P
- [
- {
- “Array_fname_age”: [
- “亜妃子”,
- 5
- ]
- },
- {
- “Array_fname_age”: [
- “Eva”,
- 44
- ]
- },
- {
- “Array_fname_age”: [
- “John”,
- 23
- ]
- },
- {
- “Array_fname_age”: [
- “Véronique”,
- 50
- ]
- }
- ]
Return Data with Particular Key in JSON
- SELECT P.firstname,P.lastname,P.age FROM Individuals P WHERE IS_DEFINED(P.lastname)
- [
- {
- “firstname”: “Varun”,
- “lastname”: “Setia”,
- “age”: 30
- }
- ]
To Preserve Information Consistency
Suppose one doc has lastname and others don’t. We use Coalesce
- SELECT P.firstname,P.lastname??“NotSet” AS lastname,P.age FROM Individuals P
- [
- {
- “firstname”: “亜妃子”,
- “lastname”: “NotSet”,
- “age”: 5
- },
- {
- “firstname”: “Eva”,
- “lastname”: “NotSet”,
- “age”: 44
- },
- {
- “firstname”: “John”,
- “lastname”: “NotSet”,
- “age”: 23
- },
- {
- “firstname”: “Véronique”,
- “lastname”: “NotSet”,
- “age”: 50
- },
- {
- “firstname”: “Varun”,
- “lastname”: “Setia”,
- “age”: 30
- }
- ]
To get all IDs as an array:
- SELECT * FROM Individuals.id
- [
- “e141ad63-ea4b-4778-9788-1d39ec7956dd”,
- “9cbac020-2b79-460b-b037-9af18ab21f42”,
- “a82ae393-fdf4-4f78-a66e-d3b2a98d1696”,
- “20566083-a724-4e02-a035-c17691bf17ba”,
- “65313e59-9c7c-4fe5-bef8-e0891b4b7cd0”
- ]
Mixed Question Array and JSON
- SELECT P.id,[P.firstname,P.lastname] AS fullname FROM Individuals P
- [
- {
- “id”: “e141ad63-ea4b-4778-9788-1d39ec7956dd”,
- “fullname”: [
- “亜妃子”
- ]
- },
- {
- “id”: “9cbac020-2b79-460b-b037-9af18ab21f42”,
- “fullname”: [
- “Eva”
- ]
- },
- {
- “id”: “a82ae393-fdf4-4f78-a66e-d3b2a98d1696”,
- “fullname”: [
- “John”
- ]
- },
- {
- “id”: “20566083-a724-4e02-a035-c17691bf17ba”,
- “fullname”: [
- “Véronique”
- ]
- },
- {
- “id”: “65313e59-9c7c-4fe5-bef8-e0891b4b7cd0”,
- “fullname”: [
- “Varun”,
- “Setia”
- ]
- }
- ]
Thanks for studying. Additionally, please share your ideas and opinions.