Import JSON into Cosmos DB

2 hours
  • 5 Learning Objectives

About this Hands-on Lab

Azure Cosmos DB is Microsoft’s globally distributed, multi-model database service. With a click of a button, Cosmos DB enables you to elastically and independently scale throughput and storage across any number of Azure regions worldwide. In this hands-on lab, we will export a table from our sample Azure SQL database to a JSON-formatted file. We will then use MongoImport to import this data into our Cosmos DB account as a new collection using PowerShell.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log into the Azure Portal and Prepare Azure Resources
  1. Log in to the Azure Portal with the credentials provided.
  2. Click on All Resources in the navigation hub menu and familiarize yourself with the Azure resources that have been provisioned, including:

    • An Azure virtual network and network security group allowing SSH access to virtual machines
    • One CentOS 7.5 virtual machine, along with supporting components (NIC, public IP, disk, etc.)
    • One Azure SQL database and Azure SQL server
    • One CosmosDB account

Obtain Public IP Address for VM Access

  1. Click on the virtual machine named vm-XXXXX, where XXXXX is a five-character unique lab ID for this lab.
  2. Inside the virtual machine blade, note the public IP address for this virtual machine.

Enable SQL Firewall Access to the Virtual Network

  1. Click on the SQL server named sqls-XXXXX, where XXXXX is a five-character unique lab ID for this lab.
  2. Inside the SQL server blade, click on Firewall and virtual networks.
  3. Click the + Add client IP button to add the IP of your local workstation to the SQL server firewall.
  4. Click Save to save the changes.

Log On to the Virtual Machine

  1. Open up a terminal window on your local machine.

    • For MacOS and Linux workstations, you can use the Terminal application.
    • For Windows virtual machines, you may have to install an SSH client such as PuTTY.
  2. In the terminal, connect to vm-XXXXX by typing in the following (replacing ip.address.of.vm with the IP you noted earlier):

    ssh azureadmin@ip.address.of.vm
  3. Type yes to confirm connection, and enter the password for the virtual machine: LA!2019!Lab1.

Install Database Tools on the Virtual Machine

Reference MongoDB repository

The mongodb-org package does not exist within the default repositories for CentOS. However, MongoDB maintains a dedicated repository. Let’s add it to our server.

sudo su 

Enter the password for the virtual machine if prompted: LA!2019!Lab1.

curl https://raw.githubusercontent.com/linuxacademy/content-azure-az301-labs/master/mongodb-org.repo > /etc/yum.repos.d/mongodb-org.repo

exit

Install MongoDB Tools

Install the mongodb-org package from the third-party repository using the yum utility:

sudo yum install mongodb-org-tools

Enter the password for the virtual machine if prompted: LA!2019!Lab1.

Enter y twice when prompted to confirm the installation and import the GPG key.

Export the Customers Table from the SQL Database
  1. Back in the Azure Portal, click on All Resources, then on the SQL database named sqldb-XXXXX, where XXXXX is a five-character unique lab ID for this lab.

  2. Click on Query editor (preview) in the blade navigation menu.

  3. Sign in to the Azure database using azureadmin as the login and LA!2019!Lab1 as the password.

  4. Enter the following T-SQL query into the window:

    SELECT TOP (10) * FROM [SalesLT].[Customer]
  5. Click Run to execute the query.

  6. Click the Export data as .json button to download a JSON file to your local workstation.

  7. Open this file, named Query1.json, in a text editor on your local workstation.

  8. Press Control+A on your keyboard (Command+A on MacOS) and then press Control+C (Command+C on MacOS) to copy the contents of the file to the clipboard.

  9. Return to the virtual machine, and use the vi editor to create a new file:

    vi customer.json
  10. In the vi editor, press i to insert new text into the file, and then press Control+V (Command+V on MacOS) to paste the text into the file.

  11. Save the file by pressing Escape, then :wq, and then Enter. You will be returned to the shell.

Import the JSON File into the CosmosDB Database as a New Collection
  1. In the Azure Portal, click on All Resources, then on the Cosmos DB account named cosmos-XXXXX, where XXXXX is a five-character unique lab ID for this lab.

  2. In the cosmos-XXXXX blade, click on Connection String. You will need information on this pane to complete this objective.

  3. Return to the virtual machine, and run the following command to import the JSON file into the Cosmos DB account using mongoimport:

    mongoimport -h exampledevto.documents.azure.com:10255 
    -d ImportedSQL -c Customer -u exampledevto 
    -p  YOURPASSWORDHERE --ssl --jsonArray --file customer.json 
    • Replace exampledevto.documents.azure.com with the HOST value under Connection String in the Azure Portal.
    • Replace exampledevto with the USERNAME value under Connection String in the Azure Portal.
    • Replace YOURPASSWORDHERE with the PRIMARY PASSWORD value under Connection String in the Azure Portal.
  4. Your command should resemble the following:

    mongoimport -h cosmos-dcru5.documents.azure.com:10255 -d Customer -c example -u cosmos-dcru5 
    -p  2vlxaaoaWcgKhGE1XAaNA1nBrvOnJzA299PyqyZDJxlo2PJFX9JNojxZZ5givkDUOsgR0KKwUvcjh16rmU9T5g== 
    --ssl --jsonArray --file customer.json
View Your Imported Data in the Azure Portal

Enable Cosmos Firewall Access to Your IP Address

  1. Click on the Cosmos DB account named cosmos-XXXXX, where XXXXX is a five-character unique lab ID for this lab.
  2. Inside the Cosmos DB blade, click on Firewall and virtual networks.
  3. Under the Firewall section of the pane, click the + Add my current IP button to add the IP of your local workstation to the Cosmos DB firewall.
  4. Click Save to save the changes.
  5. While remaining in the Cosmos DB blade, click on Data Explorer. Note the new collection named Customer (you may need to refresh the collections).
  6. Expand Customer, and then click on Documents.
  7. Click on several of the documents that appear in the list and verify that they contain customer data.

CosmosDB results

Additional Resources

Scenario

In this lab, we work for Houston, TX-based Altuve Trophy Company. As we look to expand into the Washington, DC market, we must evaluate our current database architecture and shift to a solution that provides multi-region writes and higher performance for our online catalog.

Currently, we utilize Azure SQL as our back-end database solution.

  • sqldb-XXXXX

Note: XXXXX symbolizes a unique five-character lab ID for this lab.

IT management has tasked us with testing CosmosDB with our production data. We will be exporting data out of our existing SQL database and importing it into CosmosDB, configured for MongoDB API.

  • cosmos-XXXXX

Note: XXXXX symbolizes a unique five-character lab ID for this lab.

We will be exporting the SQL data to a JSON file and using mongoimport to import the data into a new CosmosDB collection.

SSH Access to Virtual Machines

We will be using SSH to access our Linux virtual machines in this lab. For MacOS and Linux workstations, you can use the Terminal application. For Windows virtual machines, you may have to install an SSH client such as PuTTY.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?