SQLSaturday Sri Lanka - 2017

Let's make a complex dataset simple using Azure Cosmos DB

Session Abstract: Azure Cosmos DB is an evolution of Azure Document DB. 

It's the industry first globally distributed database service, allow you to scale storage and throughput across many geographical regions with promising low latency.

Cosmos DB supports for multi model database, different types of database engines like key-value, document, graph and multi API support like SQL, Azure Table Storage, Gremlin, MongoDB.

Let's see in action with a demo, how you can transfer your complex data into a simpler format with Document API and scale it in different regions with specific storage and throughput.

Event : https://www.sqlsaturday.com/639/Sessions/Schedule.aspx

Blog: Azure Cosmos DB: Designing your data structure

Related Blogs : Explore Azure Cosmos DB - document API

Slides : Azure Cosmos DB

Azure Cosmos DB: Designing your data structure

1 Introduction

This article provide an introduction to Azure Cosmos DB and its extensive features including data partitioning, global distribution, elastic scaling, latency, throughput and different service level agreements.

It describes how you can design your cosmos db with different collections and relationships, You can run queries top of that design and analyse throughput of the query. If its not performing up to the expected level, you can remodel your database until it reaches to an expected level

---

2 What is Azure Cosmos DB

Azure Cosmos DB is a database service that offers variety of data models and data APIs

It supports for key-value storage, document db and graph db

It is a global distributed database service, with a single button click you can replicate your data into different regions, You can scale out your database runs in Azure Cosmos DB service, when the no of users increases and same time you can achieve low response time

Azure Cosmos DB service offers no of service level agreements, which vary with amount of availability of your data and consistency of your data, you can select according to your business requirements

If you want to distribute your data into multiple regions, closer to your users, when you want to scale out your application, when application grows and at the same time you need to achieve low latency, high data availability & consistency, you can go for Azure CosmosDB

---

3 Why Azure Cosmos DB

Let's see what is the difference between Azure SQL database and Azure Cosmos DB, Both databases are database services

Azure Cosmos DB supports for document store, Graph DBMS, key value store and column store. SQL databases supports for relational database systems.

Both database services can be replicated to many data centres and achieve high data availability. We can scale out and improve latency in both database services

Azure SQL databases and Azure Cosmos DB both has different level of service agreements.

---

4 Data Partitioning & Global Distribution

Data in Azure Cosmos DB can be partitioned into different sections. Let's say we have products and product categories, you can define product category as the partition key and split products into different sections, partitions as above picture. If we take East Asia region, products has been locally distributed according to the product category id, (product category A, B & C) products has distributed globally for different regions like East Asia, Central US and West US

You can host copy of your data along with the partitions into another region and make data synchronize with each other

You can configure multiple read/write regions, one region can act as a write region and all the other regions can act as read regions. you can order fail over regions, If East Asia goes down, read from Central US, If East Asia & Central US both goes down, read from West US

In above picture I have hosted cosmos db in East Asia region, since i haven't hosted this cosmos db in multiple regions, read & write location will be East Asia region. Let's see how we can host our database service in multiple regions and how to configure read, write regions

Go to region configuration in Cosmos DB, you can see 'Replicate data globally' screen. click on a region and you can replicate your data into different regions, i have replicated data to Central US & West US regions. You can see write region & read region configuration, I selected East Asia as a write region and Central US & West US as read regions, primary read region is Central US in this scenario. You can order your data read regions as you want.

---

5 Data Consistency & Availability

What happens to data consistency & availability when you distribute your data within multiple regions, You cant't achieve high level of data availability & consistency in same time

Your application requirements should decide what to do, whether to provide highly available system or to focus on consistency in your data, In a shopping cart application, you may get duplicate items, sometime items you added to the cart may not be there, that's not good for a better user experience, but It's acceptable. When it comes to a mission critical application, your data should be high consistent.

If you partition your data, you have to choose either consistency or availability, If we get a one db instance it's not a problem

You have to decide whether to focus on consistency or availability, but that's not a single binary choice. You can go for a certain amount of consistency with certain amount of availability as per your requirements. In Cosmos DB, you can select level of data consistencies, it can depend on the operations you need to perform

cosmos DB offers 99.9% availability SLA with 5 different consistency levels such as strong to eventual, In strong consistency its not high available, response time will be high, not easy to scale. In eventual consistency level, data not consistent & it shows out of order reads

In a Cosmos DB service, default consistency level is session consistency, session consistency is useful for user centric applications. you can get a consistent view of your data in your region, but you may not see latest data in other regions as soon as you commit your changes,  its more user centric

You can change your consistency level programmatically, If you want to read more consistent data in a specific read, you can increase the consistency level and achieve strongly consistent index and data

Go to default consistency in Cosmos DB service, you can see available consistency levels and can select one of them

---

6 Data Consistency vs Response time

It's not the consistency vs availability you are dealing with, it's with response time.

The more you want to have consistent across the nodes, more nodes need to involve with communication, it's going to slow down the response time

You can let your each node work with different actions and sort it later. In that case, you can get high response time even your data is not consistent. Again that's a business decision. Even though your network is up, you can sync up things but don't go and do it, if you want to have high response time

If you build a system that deals with safety and aliveness, you can give more concentrate on data consistency, even though your system has low response time

---

7 Scale throughput and storage

You can elastically scale out database throughput, measured in seconds or minutes

You can scale out Cosmos DB as per the size requirements of your application

When you have a cloud hosted database, it's easy to scale out database throughput and size requirement, If you run your database in a physical server, that's difficult, you may want to use additional configurations

---

8 Database schema & Index management

In Cosmos DB service, it's going to drive the database from application, no need to do any changes in your database when you have a schema change

Every property in a document is getting indexed, no need to create indexes manually. Hash index is created for every path in the document, range based index is getting created for numbers & strings. You can define exclusion paths from a portion of your document you are never going to query, so you can save the unnecessary write time of the index. Indexing policies can be changed as application grows.

Go to Azure Cosmos DB account and click on Settings, then navigate to indexing policies for a one collection. In indexing policies, it has defined included paths & excluded paths arrays. By default it has added range index for numbers and strings. If you want to exclude some paths in your document, you can add it in excludedpaths array in this json document

---

9 Request Units (RU) & Pricing

Request Units are the currency in Azure Cosmos DB, each query has its own request charge, how much cost involved to process that request

Request units are calculated based on the computation need to server the request, you can change your data structure by looking at the required request units

At first, design the database structure with collections & documents, if it requires many no of request units to process your queries, move your data into different collection or else remap your relationships differently

As application grows and access pattern of your users get changed, increase or decrease throughput in Cosmos DB service

When you create a collection you define the request units,  RU is getting charged for stored procedures and triggers in your collection. If you go above that you start to getting throttled, we can define an alert to notify before getting throttled

Capacity units in Cosmos DB service measured in storage and throughput, we can select a capacity unit when creating the database and scale it later

---

10 Azure Document API

Azure Cosmos DB is a database service, it supports for multiple data models as key value API, column family API, Graph database & document API

In Azure Document API, Document is a JSON object, it stores data as documents

Azure document has a flexible schema, de-normalized data, it can have mixed type of data such as a string value, number, array or an object

In Azure document API, referential integrity is not enforced as a relational database

---

11 How to model data for a document database

Modeling data in a document database is equally important as a relational database. It's slightly different than a relational data model.

At first, model your data and run your queries, see what will be the cost for your queries, It it's expensive remodel your data until it comes to a certain level. When your application grows with data, you may want to restructure your data again

In a document database, schema enforcement needs to handle by the developers

---

12 Are you Non-relational ?

Let's say we want to show a product catalog on the screen, catalog contains product info, price, discounts and promotion information.

If we store product information in a relational database, you have a normalized data in different tables. When we want to show catalog, you should perform joins and convert that models to a view model. conversion from model to view model is going to handle by a ORM - (Object relational mapping)

When we store product catalog in a document database, you dont want to convert database model to something else to show it on the screen. What you store in the database is what we show on the screen

---

12.1 Modeling data in a relational way

This diagram shows a database schema in an order processing system. It has Order entity, each order has multiple items and a customer. Item has a relationship with a product

We have mapped order in 4 different tables, when you want to show an order, you have to join all these tables and construct the order

---

12.2 Modeling data in document way

When we store a product in document database, it shows like this. Order has a id field which is a scalar value, Order has a customer object with name & address fields. Order contain items, it's an array, in items array it has scalar values as well as product object. When you want to show an order on the screen, you want to read only this document, no need to join multiple tables.

---

13 Embed or Reference

In a relational database, we manage relationships between tables with foreign keys. In a document database also we should manage relationships between documents. Let's see what are the available relationships in a document database

In above example, it shows a speaker session management system. For a speaker session it has sessionId & sessionName scalar properties.

In the left side it shows an example of an embedded relationship, speakers has been embedded into the speaker session document

If you want to show a speaker session on the screen, you have to read only one document, no need to join multiple documents

If you refer to the right side example, you have speakers in separate documents and session in another document, basically you have two type of documents, session type document and speaker type document

In speaker document, you have speaker id & name, In session document, speakers has been referenced by their ids

When we want to show speaker session details on the page, you have to join speaker document with session document

When you create entities in a document database, you can choose whether to go with an embedded or referenced relationship, according to the requirement you can select a relationship and create your documents & collections

---

14 Demo : How to design a order processing database

Let's create a Azure Cosmos DB account and create multiple collections and documents in it.

---

14.1 Create Cosmos DB account and collections

Create a Cosmos DB account to store order information, inside that create multiple collections to store orders, users and product comments.  I will explain why we created separate  collections for user and products

---

14.2 Explore collection & documents

Navigate to Data Explorer tab in Azure Cosmos DB account, You can see available collections in OrderDB as above

---

14.3 Go through Order documents

Expand Order collection in collections tab, click on Documents, then you can see order collection has 2 documents.

Click on first document, it has order information along with customer details and items

Order document has customer information with order item details, since order processing system usually query an order, so no need to have multiple document for a customer and order items. We can store order items with customer, without any joins between documents, you can retrieve an order with a single read

In Order document, Customer object is embedded into an order, since Order & Customer get queried together

Order Items are stored as an array, For each order item a product has been embedded with product vendor information. In a document you can navigate to any level of property and retrieve information as application requires

If you see Order collection, you can store documents as well as can write a stored procedure, user defined function, trigger for a collection.

---

14.4 Let's check ProductComment collection

In ProductComment collection, we have stored comments for each product, As you remember we stored product information along with order and order item. But we have created separate collection for product comments

A product can have many no of comments from different users, if we store product comments along with product details that document can be very big in size. that's why its better to separate it to another document and store product comments as a bulk document that grows fast

If you see the structure of this document it has product id field, and product id field has been referenced in order document. Product and product comment entities has referenced relationship

---

14.5 Stored procedure in a collection

You can write a stored procedure for a collection. Let's say you want to change customer address in customer name 'Hansamali'

In a document database, data reads are faster but when it comes to data writes, it's going to be bit slower. so what we can do is we can run a stored procedure in the background to update necessary records

---

14.6 Why separate collection for Users

In this example, we created separate collection for Orders and another collection for users of the system.

User information is not getting queried with orders, that's why its better to have separate collections.

User document has his profile information and social media details as above. When we want to get user information you can read this user collection and retrieve all the necessary user details in a one single read

---

15 Let's query the document collection

Let's run few queries against these collections and see how we can improve their performance

Go to Order collection and click on New SQL Query, Let's get all the orders in Order collection. execute your query, It shows 2.65 RU as request charge. Order collection has 2 Order documents

Let's try to get a complete order by passing order id as above, It costs 2.31 request units (In previous query to retrieve 2 orders, it costs 2.65 request units)

You can use SQL syntax to run queries against document db collections

In previous queries, we projected Order entity. In above query we have started to query from order and projects order items. In where clause you can navigate to any property and filter from necessary information

If you can see the request charge for above query, its 2.58 request units, with the where clause request charge was bit high than this

This query returns all the customers lives in Colombo or Malabe, We have used IN operator in above query,

---

16 How we can use joins in document collection

In above query, we want to get order details where item no 'AA1' got ordered,

You can see order document as above, we want to find item No 'AA1', We should navigate to Items array to reach Item No property, since Items is an array we have to perform a join, If we want to reach Customer address property, we can navigate from Order.Customer.Address since customer is an object

If we want to navigate to an array and perform some selection in a document collection, we should perform a join operation as above

Order collection joined with Order items then filtered from item no and projects order and item information

You can perform a range operation as above. It's the same query as above but with a different where clause

We want to get order and item information where itemNo is 'AA1' and vendor code is 'ABC'

At first, navigate to Order collection, (FROM o),

We should filter from item No, so navigate to Items, Items is an array, (JOIN Orders with Items array)

Then we should filter from vendor code, You can find Vendors array in each Product, (JOIN Vendors array with item)

You have performed two joins in this query, then you can filter the result in where clause and retrieve necessary information in select clause

You can run this type of a query and format its results as above

---

17 Conclusion

We have created Order database with different collections and ran few queries on top of it. According to the velocity of data reads, we have to create separate documents. When we design the data collection, We should think about the volume of data, frequent reads comes to the database and request charges for common queries

If the query charges are high, then you may have to restructure your collections, some referenced relationships may need to convert into embedded ones

If you documents are big in size, you may have to convert embedded relationship into a referenced relationship to maintain the cost for you data

---

TechNet Guru Awards June 2017

TechNet Guru Awards June 2017 - ASP.NET - Gold medal

I won the GOLD medal for my ASP.NET article on TechNet Guru competition

Article: ASP.NET Core : Advanced JQuery dialog actions        

Code :

     TechNet Gallery: ASP.NET Core : Advanced JQuery dialog

     GitHub : advancedjquerydialog

Data Community Meetup - July 2017

First steps to Azure Cosmos DB: Getting Started with MongoDB and NoSQL

Session Abstract: MongoDB is a document database with the scalability and flexibility, with the support of querying and indexing. The session is focused on how to use NoSQL and related features of this non-relational database. This session will serve as one of the first steps to learn about Azure's Cosmos DB.

Event : https://www.meetup.com/sldatacommunity/events/241717040/

Blog: Explore Azure Cosmos DB - document API

Slides : First steps to Azure Cosmos DB: Getting Started with MongoDB and NoSQL 

Explore Azure Cosmos DB - document API

1 Introduction

This article provides an introduction to Azure Cosmos DB - Document API with MongoDB. It mainly focuses on NoSQL - non-relational databases. Let's explore MongoDB - document database and finally move into Microsoft's latest web scale database innovation which is Azure Cosmos DB.

---

2 What is NoSQL

NoSQL is the next generation database, In the world of databases, we have SQL & NoSQL databases, relational & non-relational, NoSQL means "not only SQL".

The difference between these two types of database technologies is, what type of data they store, how they store it and the way we use it. Relational databases are structured like a phone book that stores phone numbers. Non-relational databases are document based & distributed like folders that hold everything from phone numbers to images.

NoSQL databases are non-relational, A non-relational data model doesn't have a table model, Instead, data can be stored in a single document file. In a relational database, data is organized into tables and each table has columns and data rows.

Relational databases are dominated the software industry for a long time, But now non-relational databases are taking its place.

An important feature of NoSQL database is polyglot persistence, polyglot persistence means. we can use more than one storage mechanism to accomplish application storage needs.

NoSQL databases are scalable, they can scale up to thousands and millions of users.

Most of the NoSQL databases are open source, You can just download them and setup a test environment.

NoSQL databases are schema free, In NoSQL database technology, no predefined structure, no schema

It has easy replication support, When you have more customers via web & mobile, availability becomes a major concern. In a relational database deployed to a single physical server, if it fails database becomes unavailable. But NoSQL databases, partitions and distributes data into multiple database instances, The data can be replicated to one or more instances for high availability. If you use Oracle software, it requires separate software to replicate data, but with NoSQL, we don't need anything extra.

NoSQL has a simple API, When it comes to relational databases like SQL, It's not compatible with Java, Php or Python. But NoSQL databases, they have overcome this issue with their available APIs. When you use a NoSQL database technology, you don't need to understand about the underlying architecture of their system.

The relational data structure & in memory data structure of the application is different in SQL. When you use a NoSQL database, developers don't need to convert in memory structure of data to a relational structure, storage need for an ERP application is different from a storage need for facebook or twitter.

NoSQL supports for a huge amount of data, for modern web scale applications, you have millions of data from millions of customers., so better to go for NoSQL database technology.

---

3 Types of NoSQL databases

In NoSQL database technology, we have different types of NoSQL databases

• Key - value databases: It's least complex database option, stores data with an indexed key and a value, the value is a blob. It has primary key access, because of that has great performance and scalability, example - Azure table storage

• Column store databases: stores data tables as columns rather than rows, example - Hadoop, Cassandra

• Document databases: Document DBs great for managing data that document oriented, but somewhat structured, example - MongoDB, CouchDB. RavenDB, Documents can be in XML, JSON and BSON format. A document is a hierarchical tree like data structure consists of maps, collections & scalar values. In a document database, documents store value part of the key value store.

• Graph databases: Graph DB consist of interconnected data that can be represented as a graph, this data set is high in complexity. example - ArangoDB & OrientDB

---

4 Why we should use a NoSQL database

Let's see why we need to go for a NoSQL database,

We can improve programmer productivity when using a database technology that matches with application needs.

When you need to improve performance of large volume of data, better to use a NoSQL database. When you have more and more customers, you want to improve throughput of the application, You can reduce latency of data processing, you can improve throughput, no of transactions per second.

Most of the NoSQL databases are open source, you can download them and check whether you can achieve level of programmer productivity & performance by using a NoSQL database.

You can go for polyglot persistence, you can break your solution into different services and use a NoSQL database for a specific service, and for all the others use a relational database.

When you want to have a high scalable and super responsive data layer, go for NoSQL database

NoSQL databases are much faster in most type of operations, If you have high volume of data, NoSQL is usually the way to go as long as your data don't have loads of inter connected relationships

---

5 NoSQL: Myths

• NoSQL can't replace SQL, It's an alternative for specific requirements

• NoSQL is better/worse than SQL, some projects need to use a relational database, some needs to use both

• The language/framework determines the database, Php, MySQL(relational), Linux, on a LAMP server, MEAN stack - MongoDB, ExpressJS, Angular, Node and .NET, IIS, SQL server and Java, Apache, Oracle These technology stacks are practical and we can find commercial reasons why these are evolved. But don't think them as rules. We can use MongoDB with Php as well as .NET projects. We can use MySQL, SQL server with NodeJS.

An application can talk to multiple types of databases utilizing each for what it's best, relational, document db, key value store.

---

6 MongoDB - A leading NoSQL database

MongoDB is a free, open source, cross platform & document oriented database. It's based on NoSQL database technology. data records are stored as documents, it uses JSON like syntax.

In relational databases, we need to define exact schema, what are the tables, fields, and type of those fields. In NoSQL, need to plan out the structure of our database and collections, no need to do any pre defined structuring for schema.

---

7 MongoDB is the one

Let's see MongoDB is the right choice for your application, The data environment has changed a lot since SQL was first released,

If you are working with location based data, with a high volume of data better to use a NoSQL technology

Collect data from sensors and connected devices, they create millions of data points, it's a challenge for a relational database to check them and analyse them without time-consuming ETL process. But MongoDB can analyze data of any kind within the database itself.

Powering content management systems (CMS), MongoDB can store so many different types of data, can store multimedia files, tweets, and comments

MongoDB releases new versions fast, You can make modifications to your application without any cost, If you use relational database, It's a bit tedious to perform a version upgrade

We can build mobile apps fast since it can handle unstructured data, it's ideal for mobile apps

If your team has knowledge on NoSQL databases, MongoDB is the one of the easiest NoSQL based databases

When your application gets data from different location, go for MongoDB, If your application expects to grow, use MongoDB, MongoDB can be easily scaled horizontally

---

8 How MongoDB is changing today's business

Modern data is vast, unstructured, big & complicated, But clients have big expectations, this lead to the development of NoSQL databases

Document databases allows storing data in more logical manner,

MongoDB is highly used in MEAN stack, but compatible with .NET and Java, it's open source & cross platform compatible, so you can download a copy of MongoDB and test whether it can fulfill your requirements,

---

9 Explore MongoDB

9.1 Install MongoDB

When you use MongoDB API in Azure CosmosDB, you don't need to install MongoDB locally, But just to understand how it works and how we can query documents let's install it and try some queries in local.

Let's go to MongoDB site, https://www.mongodb.com/ and download Mongo setup

Download community edition for Windows,

Double click on Mongo setup and you will get a window like this,

Click on Next button, and accept end user license agreement

MongoDB is getting installed

---

9.2 Setup MongoDB environment

Open up a command line in administration mode and go to the location where MongoDB is installed

create data and log folders in MongoDB installed location, inside data folder create a folder for db, all the database files will be stored in this db folder

navigate to bin folder in MongoDB installed location, and type following command, specify path to the database, file to store all mongoDB logs. This command allows to run MongoDB as a service

mongod --dbpath C:\mongodb\data\db --logpath C:\mongodb\log\mongo.log --logappend --rest --install

Run the following command to start MongoDB service

net start mongodb

Run Mongo shell from the bin directory, It shows Mongo shell version and Mongo local url if you want to access it over http

create a database by using the following command,

use movieDB

Type db command to check what is current db

db

You can see what are the available databases using this command,

show dbs



create a user by typing this command, assign that user readWrite and dbAdmin roles

db.createUser( { user: "hansamali", pwd: "12345", roles: [ "readWrite", "dbAdmin" ] } )



---

9.3 Create collection and Insert/read data

Collections are similar to tables in relational database, they hold documents inside collection

Create a collection by passing collection name.


db.createCollection('favouritemovies')

show collections

insert a document into favouritemovies collection

db.favouritemovies.insert({Title:"Spider-Man:Homecoming", Genre:"science fiction"});

Try to see available data in the collection by using the following command,  _id field shows an object id, it's a unique value for a document

db.favouritemovies.find()

Let's try to insert multiple documents, and add a new field into the collection. In NoSQL databases we don't need to define a schema, we can add a new field into the collection without changing anything.

db.favouritemovies.insert({Title:"Wonder woman", Genre:"science fiction", Director:"Patty Jenkins"});

db.favouritemovies.insert([{Title:"The Mummy", Genre:"Fantacy/Thriller"},{Title:"Transformers", Genre:"science fiction"}]);

Use pretty() helper function to format the data output as below,

db.favouritemovies.find().pretty()

---

9.4 Update a field in a document

We want to add Director field to movie The Mummy, let's use update method in db, first parameter is, pass a match to a record, it acts as a where clause, next parameter is what we need to replace with, we should pass whole record to replace when we use update method


db.favouritemovies.update({Title:"The Mummy"}, {Title:"The Mummy", Genre:"Fantacy/Thriller", Director:"Alex Kurtzman"});

When we use set attribute to update a field, need to pass only Director field, no need to pass the whole record, It will only update Director field without replacing full record

db.favouritemovies.update({Title:"Transformers"}, {$set:{Director:"Michael Bay"}});

---

9.5 Remove a field in a document

Let's remove a field from a document since NoSQL databases don't have a schema, we can unset a field from a specific document


db.favouritemovies.update({Title:"Transformers"}, {$unset:{Director:""}});

---

10 Azure CosmosDB

Azure DocumentDB to Azure CosmosDB, it's not only a rename, the next generation database technology. In Azure document DB it only supported document API, But in Azure CosmosDB, it supports for multiple APIs document API, MongoDB API, Graph API etc.

When you use CosmosDB, You can develop applications without borders, You can bring data close to your users,

Azure portal itself uses Azure Cosmos DB, Azure CosmosDB is elastically scalable database service, it's not possible with relational databases

When your application data changes a lot, you can choose document API or key-value pair API, If your application requires millions of actions per second, needs high speed, high availability, You can use CosmosDB in the cloud

As long as you don't perform joins, Azure CosmosDB is good in any kind of data, key-value pair, documents or graphs.

You can host a part of your data in an SQL database and another part in an Azure Cosmos DB

---

11 Conclusion

In Azure Cosmos DB we can use many types of APIs including Mongo API, Document - SQL API, Gremlin API, Table storage API. This article has described how to use Mongo API in Azure Cosmos DB with examples. You can move your MongoDB hosted in a server to Azure Cosmos DB without any major modifications to the application, your clients will not feel the difference of it. Cosmos DB is not supported with relational databases, you can host NoSQL databases with Cosmos DB along with a specific model and API

---

12 References

• Take a Modern Approach to Data in Your Apps
• Azure Cosmos DB with Scott Hanselman
• Azure Functions Cosmos DB Api Demo
• Journey from SQL to NoSQL with Azure Cosmos DB (formerly DocumentDb)
• Getting started with Cosmos DB
• Modeling Data for NoSQL Document Databases
• GOTO 2012 - Introduction to NoSQL
• O'Reilly Webcast: MongoDB Schema Design: How to Think Non-Relational
• How does Azure Cosmos DB index data?