Skip to main content

Import JSON into Cosmos DB

Hands-On Lab

 

Photo of James Lee

James Lee

Training Architect

Length

02:00:00

Difficulty

Intermediate

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.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Import JSON into Cosmos DB

Introduction

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.

Solution

Log in to the Azure Portal using the credentials provided on the lab instructions page.

Note: Throughout this lab guide, you'll see resources with -XXXXX appended to the end of their name. That indicates the unique five-character lab ID for the lab (e.g., sqls-lrrtc).

Log into the Azure Portal and Prepare Azure Resources

  1. 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
  2. Click on the virtual machine named vm-XXXXX.

  3. Inside the virtual machine blade, copy the public IP address for this virtual machine and paste it into a text file, as we'll need it in a minute.

  4. Click on the SQL server named sqls-XXXXX.

  5. Inside the SQL server blade, click on Firewalls and virtual networks.

  6. Click the + Add client IP button to add the IP of your local workstation to the SQL server firewall.

  7. Click Save.

  8. 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.
  9. In the terminal, connect to vm-XXXXX by typing in the following (replacing ip.address.of.vm with the IP you copied earlier):

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

Install Database Tools on the Virtual Machine

  1. Become superuser:

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

  3. Download and install the MongoDB repository containing the mongodb-org package:

    curl https://raw.githubusercontent.com/linuxacademy/content-azure-az301-labs/master/mongodb-org.repo > /etc/yum.repos.d/mongodb-org.repo
    
    exit
  4. Install the mongodb-org package from the third-party repository using the yum utility:

    sudo yum install mongodb-org-tools
  5. Enter the password for the virtual machine if prompted: LA!2019!Lab1.

  6. 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.

  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.
  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.

View Your Imported Data in the Azure Portal

  1. Click on the Cosmos DB account named cosmos-XXXXX.
  2. Inside the Cosmos DB blade, click on Firewalls 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.
  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

Conclusion

Congratulations on completing this hands-on lab!