Introduction to Data Storage in Azure Supplemental Guide

This is a supplemental resource to the Intro to Data Storage in Azure presentation. It includes the workshop demo and resources to learn more.

Summary

The cloud is a popular option for data storage because it manages some of the work it takes to keep you data secure, backed up and accessible to end users during fluctuations in traffic.
Considering Azure for data? It's easy to integrate with other Azure services and has many options for your data learn more
Focus on your data with the tools you love

Assessing your data

What is the structure of your data?
How much data will you be working with?
Is having some of your data on premise important to you?
What tools or services depend on your data right now?
Will this data be accessed by many?

Demo

The notebook for this demo explores a data source from the Azure Open Datasets Catalog on NOAA Integrated Surface Data (ISD). We'll be converting the structure of one of these data sets to another with Azure Data factory and using Azure Machine Learning Studio to view the data and form our query with Python and the Pandas library.

Prerequisites

Azure Account

Instructions

Create Azure Services

Click this button to create a MySQL database, and Data Factory

Recommended: Once your Azure resources are deployed, pin it to your dashboard in the Azure Portal

Setup Azure Services

In the Azure portal, open your MySQL Server (starts with mysql-).
Go to Connection Security and click + Add client IP* to add a firewall rule for your ip address.
Set Allow access to Azure Services to On.
Go back to the Overview in MySQL
Click on the terminal icon at the top of the portal to open the Cloud Shell Terminal.

Set up MySQL database in the Cloud Shell

To connect to MySQL, you'll need a cerfiticate to communicate over SSL with your database. The certificate used in this demo is recommended by Microsoft. It is recommended to use the new certificate, named DigiCertGlobalRootG2.crt.pem as the old one will expire on February 15th

Download the SSL certificate: https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem
Upload the downloaded certificate to your cloud shell (confirm it's uploaded with ls). Here's instructions on how to upload
Connect to MySQL with the following command. Replace the servername and adminname with the values in the overview of MySQL:

mysql -h servername.mysql.database.azure.com -u adminname@servername -p --ssl-ca=DigiCertGlobalRootG2.crt.pem

You're now connected to your MySQL server. The following commands will set up the database and table:

USE weather;

CREATE TABLE surfaceLevelWeather(
    id serial PRIMARY KEY,
    time DATETIME,
    latitude DOUBLE,
    longitude DOUBLE,
    elevation DOUBLE,
    station VARCHAR(100),
    temperature DOUBLE,
    windSpeed DOUBLE,
    windAngle INT,
    currentWeatherCode INT,
    currentWeather VARCHAR(50)
);

Using the Notebook

Azure Machine Learning Studio will be used to work with the notebook, you may have already completed the creation step. If not refer to the instructions on how to create a Machine Learning Workspace.

Download/Clone the notebook content in this repo (notebook-data folder)
Go to your created Azure Machine Learning service in the Portal > Launch Now to open the Machine Learning Studio
Go to Notebooks > Start Now
Under My Files, select the upload folder button (4th button from the left) and upload the entire notebook folder.
Open and edit the configuration file named myconfig.cfg and replace the servername and adminname with the values in the overview of MySQL from the Portal.

[my_db]
host:servername.mysql.database.azure.com
user:adminname@servername
password: mypassword
database: mydatabase

Create a compute instance to run the notebook: in the Compute section, above of the notebook, select the + to open the New compute instance form.
Select a compute name and change the virtual machine size to Standard_D1 , then select Create. CPU and Region can stay at their default settings.
Select the created instance from the compute dropdown, it may take a minute or two to create. Select the the Play/Start Compute button to start the instance.
When the computer instance enters a Running state, select Python 3.6 - Azure ML as the Jupyter kernel.
Follow the remaining instructions in the notebook named Weather and Elevation

Cleanup

Locate the name of your resource group and follow these instructions

paladique/Workshop-DataInAzure