Cosmos DB – List 3

Warm up

Tasks

1. Please create a new collection that:
   1. Will use already created a database

2. Add the following document to the collection:

{
  "lastName":"Cartwright",
  "parents":[
    {
      "firstName":"Elvira",
      "role":"mother",
      "age":64
    },
    {
      "firstName":"Randolph",
      "role":"father",
      "age":67
    }
  ],
  "children":[
    {
      "grade":5,
      "pets":[],
      "firstName":"Dana",
      "age":15,
      "gender":"female"
    },
    {
      "grade":7,
      "pets":[
        {
          "name":"Concepcion",
          "type":"guinea pig"
        },
        {
          "name":"Haleigh",
          "type":"hamster"
        }
      ],
      "name":"Pat",
      "age":13,
      "gender":"male"
    }
  ],
  "address":{
    "state":"North Dakota",
    "city":"West Bretthaven",
    "country":"Guinea"
  }
}

3. Familiarise with presented queries:

SELECT * FROM c
SELECT * FROM family_1
SELECT f.address FROM family_1 as f
SELECT f.address.city, f.address.state FROM family_1 as f
SELECT * FROM c WHERE c.address.city = 'West Bretthaven'
SELECT * FROM c.children
SELECT * FROM c.children WHERE c.address.city = 'West Bretthaven'
SELECT c.children FROM c WHERE c.address.city = 'West Bretthaven'

SELECT * FROM ch in c.children
SELECT
  ch.firstName ?? ch.name as firstName,
  ch.grade,
  ARRAY_LENGTH(ch.pets) ?? 0 AS numberOfPets,
  ch.pets
FROM ch in c.children

SELECT
{
  "firstName" : ch.firstName ?? ch.name ,
  "grade": ch.grade,
  "numberOfPets": ARRAY_LENGTH(ch.pets) ?? 0,
  "pets": ch.pets
} AS child
FROM ch in c.children

SELECT
[
  ch.firstName ?? ch.name,
  ch.grade,
  ARRAY_LENGTH(ch.pets) ?? 0,
  ch.pets
] AS Child
FROM ch in c.children

SELECT VALUE
[
  ch.firstName ?? ch.name,
  ch.grade,
  ARRAY_LENGTH(ch.pets) ?? 0,
  ch.pets
]
FROM ch in c.children

SELECT
  ch.name ?? ch.firstName AS childName,
  f.parents,
  ARRAY_LENGTH(ch.pets) ?? 0 AS numberOfPets,
  ch.pets
FROM c AS f
JOIN ch IN f.children

SELECT
  ch.name ?? ch.firstName AS childName,
  f.parents,
  ARRAY_LENGTH(ch.pets) ?? 0 AS numberOfPets,
  p.name AS petName,
  p.type
FROM c AS f
JOIN ch IN f.children
JOIN p IN ch.pets

SELECT p.name
FROM c AS f
JOIN ch IN f.children
JOIN p IN ch.pets

SELECT VALUE p.name
FROM c AS f
JOIN ch IN f.children
JOIN p IN ch.pets

Main part

Preparation

1. Please create a new collection that:
   1. Will use already created a database
   2. Partition key will be set to /lastName
2. Please import a list of families by migration tool:
   1. Add json file to import and select to decompress data
   2. Remember to add the name of Database at the end of the connection string. It should look like:
      AccountEndpoint=<CosmosDBEndpoint>;AccountKey=<CosmosDBKey>;Database=<DatabaseName>;
3. To increase the speed of import, you can increase in Advanced Options in section Target Information
   1. Collection Throughput – 10 000
   2. Number of parallel requests – 1000

NOTE: After import, please reduce Throughput to a minimal level – 400 (open your collection and select Scale & Settings)

Downloads

• DataMigration tool
• 10k families – import time about 30 seconds
• 200 families – for Mac users

Queries

Try to prepare the following queries and monitor the cost. Please remember that the minimum value of throughput is 400 RU/s:

1. Select all elements from the database
2. Provide names of families stored in the database
3. Count number of families stored in the database
4. Get all different names from the database
5. Get all last names and id
6. Get all last names as a flat string array
7. Get all different last names as a flat string array
8. Find families that the last name starts at A or B or C or D
   1. Can you do it in different ways? Which one is the cheapest?
9. Flatten fields id, last name, city and country across all documents
10. Get all cities and states from database
11. Find all families that live in the UK or USA.
12. Then for each family, please provide a family name, number of kids, country and name of the currency used in that country
13. Provide a list of children. It should contain the following information:
    1. Full name of the child
    2. Mother full name
    3. Father full name
    4. Grade
    5. Gender
14. Provide a list of families sorted by the age of the first parent
15. Could you please try to find the most sophisticated query on those data?
16. Check how those queries cost when you are searching within one partition (add the last name into where)

Other

Check how metrics connected to your Cosmos DB service looks like.