Azure Data Fundamentals

Source: Course with Microsoft trainer Michelle Xie

Agenda

Module 1: Explore core data concepts
Module 2: Explore relational data in Azure
Module 3: Explore non-relational data in Azure
Module 4: Explore modern data warehouse analytics

Module 1: Data Concepts

Data = Collection of facts, numbers, descriptions, objects, stored in a structured (e.g. tables), semi-structured (e.g. JavaScript Object Notation JSON), unstructured way (e.g. media, visuals)

Transactional vs analytical data stores

Online Transactional Processing (OLTP) - discrete units of work
- Normalization improves I/O, but not efficient for reporting
Online Analytical Processing (OLAP) - users query data
- Data source (e.g. on premise, cloud, SaaS) > ingestion > storage > processing > visualization
  - Ingestion - no aggregation or heavy calculations
  - Processing - Clean and transformation
    - Time/date/metric conversions, summarize/aggregation, standardized, missing values added, de-duplication
    - Possible Azure services:
      - Functions
      - Databricks
      - Cognitive services

OLAP Stages
- Ingestion can be batch or streaming
- Extract, Transform, Load (ETL)
  - Can be continuous pipeline, good for low dependency of items
  - Sensitive data can be removed
- Extract, Load, Transform (ELT)
  - Data is stored first
  - Good for complex model or processing that data depending on multiple items in database
  - Appropriate for cloud models
- Pipelines
  - Azure Data Factory - pipelines to build data flows and handle ETL / ELT
- Data Visualization
  - Should raise questions for business, support decision making, see history/trends
  - Implemented in PowerBI
- Analytics, using statistics
  - Descriptive - what is happening
  - Diagnostic - root cause, anomalies
  - Predictive - what is likely to happen based on trends and patterns
  - Prescriptive - actions to take for a goal
  - Cognitive - self-learning, get conclusions based on existing analytics

Roles of People

Administrator
- Database Management
- Data Security
- Backups
- User Access
- Monitors performance
- Tools: Azure Data Studio, SQL Server Management Studio, Azure Portal/CLI
Engineer
- Pipelines, processing
- Ingestion
- Prepare data for analytics
- Tools: Azure Synapse Studio (portal, Azure Data Factory, SQL/Spark pools), SQL Server Management Studio, Azure Portal/CLI
Analyst
- Provides insights into the data
- Visual Reporting
- Modeling Data for Analysis
- Combines data for visualization and analysis
- Tools: PowerBI server/desktop, report builder

Relational data

Tables, using a model
- Data is stored in a table
- Table consists of rows and columns
- All rows have same # of columns
- Each column is defined by a datatype
Primary and Foreign keys
- Primary - unique key in table
- Foreign key - reference a primary key from another table
Index
- Like index at end of book to quickly find pages for a topic
- Optimizes search queries for faster data retrieval
- Reduces the amount of data pages that need to be read to retrieve the data in a SQL Statement
- Data is retrieved by joining tables together in a query
- Consumes storage space and CRUD operations needs index maintenance and can impact DB operations.
  - Infrequently accessed but often queried data, indexes can speed up operations
  - Frequently CRUD tables, but infrequently queried data, indexes slow down operations
View
- Custom table consisting of data from other tables

Non-relational data

Entities
- Non-relational data, no schema on data. Non-relational collections:
  - Can have multiple entities in same collection or container with different fields
  - Have different non-tabular schema
  - Are often defined by labeling each field with the name it represents (key-value pairs)
- Queries can be made more complex as all entities must be parsed for non-standard fields, e.g. parse for unique key value
- Example: Azure Cosmos DB
Non-relational data, semi-structured data
- Data structure is defined within the actual data by fields. Format/file types include:
  - JSON
  - AVRO - Apache, row based, header has structure, body is JSON, good for data compression
  - ORC - data in columns rather than rows, Apache Hive, strip of data with an index, footer has statistical information on strip of data
  - Parquet - Cloudera/Twitter, columnar data format, metadata describing rows, chunk of data, designed for nested data types and good for encoding and compression
NoSQL
- Loosely means non-relational data
- Categories:
  - Key-value stores
    - Tool must parse contents and return
    - Usually restricted to insert and delete, updates are made in memory of client
    - Good for streaming large data amounts
    - Can be implemented by Azure Cosmos DB
  - Document based
    - Each document has unique ID
      - Good for queries on ID and indexed fields of documents and for streaming
    - Document has all data for an entity that in a relational data would be spread across multiple tables
    - May be stored as JSON, YAML, XML, binary JSON, text, and others
    - Good for data flexibility and frequent requirements changes
    - Can be implemented by Azure Cosmos DB via core SQL API
  - Column family databases
    - e.g. ORC, Parquet files. Similar to row based tables
    - Tabular data but with column families
      - E.g. person database with address information and family information
    - Popular is Apache Cassandra, implemented by in Azure Cosmos DB Cassandra API
  - Graph databases
    - Stores entities centric around relationships
    - Good for:
      - OLTP apps with highly correlated data
      - Easy updates to single or many objects
      - Flexible data modelling
      - Data requirements that evolve
      - Hierarchical data structures
        
        e.g. people organization charts
    - In SQL would be expensive to query with recursion and joins.
    - Implemented as Azure Cosmos DB Gremlin API

Unstructured Data

Can be stored in Binary Live Object (BLOB) storage
Does not naturally contain fields:
- Examples: video, audio, media streams, documents
- Often used to extract data form and categorize or identify ”structures” to produce file’s metadata
- Frequently used in combination with Machine Learning or Cognitive Services capabilities to “extract data” by using:
  - Text Analytics
  - Sentiment Analysis with Cognitive APIs
  - Vision API

Module 2: Relational data in Azure

Azure Data Services

Microsoft SQL
- SQL Server on Azure VMs - IaaS
  - OS level access
  - Higher administration time and dedicated hosting costs
  - Features:
    - SQL, OS versions, Windows, Linux, SSRS and SQL server services
    - Lift and shift to Azure
    - Licensing and edition flexibility
    - Memory, storage optimization
    - VNET integration with on premise, Advanced data security
    - High availability and Disaster Recovery (HADR) services
- Azure SQL Managed Instance (MI) - PaaS
  - Compatibility with SQL server and VNET support
  - Control of SQL instance, multiple databases
  - Automates monitoring, backup
- Azure SQL Database - PaaS
  - Good for latest SQL versions, variable loads, scalable loads
  - Features:
    - Elastic compute, storage
    - Private link supported
    - High availability 99.995% and 5 second RPO and 30 second RTO
    - Point in time restore
    - Most cost efficient
    - Integrates with Azure security services
- Azure database for MySQL, Maria DB - PaaS
- PaaS is lower cost and administration, but less control and customization (scripts, modules). If customization needs, use IaaS
PostgreSQL, MariaDB, MySQL
- PostgreSQL is the popular database for modern apps
  - Good for geometric data
  - Query language: pgSQL
  - Community version
  - Deployment options: single server, hyperscale (HA and scalable)
  - Compatible with PostgreSQL admin tool
- MySQL for LAMP stack
  - Free community edition
  - Standard and Enterprise editions give performance, security improvements
- MariaDB is community fork of MySQL with focus on user community
  - Re-written MySQL
  - Compatible with Oracle DB
  - Supports temporal data (history)
Benefits
- Fully managed services, integration with Azure services
- High availability and low total cost of ownership
- Configurable and automated performance, scaling, security, and compliance
Provisioning of relational resources

In Azure Portal:
- Basics > network connectivity > additional settings (data source, collation, time zone, advanced security and threat protection ) > tags > review and create
  - Basics = subscription, resource group, service config, name, admin credentials, region, pools, compute & storage, pricing
  - Network connectivity = publie/private, VNET, firewall, connection type
    - Azure SQL uses 1433
    - Need to add IP addresses of database clients
      - 0.0.0.0 allows all Azure services
      - Range 0.0.0.0 to 255.255.255.255 opens to all internet
      - Connection policy modes:
        
        Redirect
        
        Service tags can allow connectivity from certain Azure services
        
        Recommended with policy restriction on connectors
        
        Proxy
        
        Connections proxies via Azure proxy gateway
        
        All packets flows via gateway
        
        Default:
        
        Redirect all client connections in Azure (low latency)
        
        Proxy all external connections
Security
- Network
  - Firewall rules
  - Virtual Network Rules
  - Private Link, private endpoint - Private IP from VNET
  - Can use Azure Gateway or ExpressRoute
  - VNET pairing to group VNETs and their resources
- Authentication and Access Control
  - Integration with Azure AD
  - Server-level principle for logical server for DB
  - Support SSO, token based authentication, MFA
- Role Based Access Control
  - Control Azure operations through Azure RBAC
  - Scoping in subscription, resource group, resource
  - Security principle - service user with role
- Azure DB read replicas
  - Supports replication from master server to up to 5 replicas
  - Use cases: replica for analytics, DR in different region, good for read only workloads not CRUD, not optimized for write
  - Billed for storage

Querying Relational Data

About SQL
- SQL is a standard language for use with relational databases
- SQL standards are maintained by ANSI and ISO
- Proprietary RDBMS systems have their own extensions/variants of SQL such as T—SQL (Microsoft), PL/SQL (Oracle), pgSQL (PostgreSQL)
- SQL Statement Types
  - Data Manipulation Language - Used to query and manipulate data
    - SELECT - It retrieves data from a database
    - INSERT - It inserts data into a table
    - UPDATE - It updates existing data within a table
    - DELETE - It deletes all records from a table, the space for the records remain
    - MERGE - UPSERT operation (insert or update)
    - CALL - It calls a PL/SQL or Java subprogram
    - EXPLAIN PLAN - It explains the access path to data
    - LOCK TABLE - It controls concurrency
  - Data Definition Language - Used to define database objects
    - CREATE - used to create objects in the database
    - ALTER - used to alters the structure of the database
    - DROP - used to delete objects from the database
    - TRUNCATE - used to remove all records from a table, including all spaces allocated for the records are removed
    - COMMENT - used to add comments to the data dictionary
    - RENAME - used to rename an object
  - Data Control Language - used to control privilege in databases.
    - SYSTEM - creating a session, table, etc. are all types of system privilege.
    - OBJECT - any command or query to work on tables comes under object privilege. DCL is used to define two commands. These are:
    - GRANT - It gives user access privileges to a database.
    - REVOKE - It takes back permissions from the user.
Query Tools
- Microsoft SQL
  - Azure Portal: On Azure SQL resource page, use query editor to execute queries in the web interface
  - SSMS
  - SQL Server Data Tools
  - Azure Data Studio
  - SQL CMD
  - Azure CLI/cloud shell
- PostgreSQL
  - PG admin
- MySQL
  - Azure cloud shell
  - MySQL workbench

Module 3: Non-relational data in Azure

Azure Table storage

Key-value store in Azure Storage account
Key = like row’s main key
Value = “columns”, but flexible
Example: key = customer ID, value = all data about that customer
No relationships, primary keys, stored procedures. Data is de-normalized.
Good for IoT systems for quick storage

Azure Blob Storage

Stored under Azure storage account
Block blobs - data stored as blocks, good for infrequent large discrete binary objects
Page blobs - collection fixed sized 512 byte pages, good for virtual disks for VMs
Append blobs - block blob to optimize append operations
Hot tier - frequent access, e.g. web access
Cool tier - infrequent access
Archive tier - slow retrieval in order of hours
Geo-replication supported

Azure File Storage

File shares
Can synchronize with local file shares
Geo-replication and encrypted is supported

Azure Cosmos DB

Partitions of documents, document database, partition contains documents that share a partition key and are related to each other
Documents can refer to Azure Blob storage
Automatic indexes of document IDs, document fields. Allows queries of fields.
Configured as storage containers
Azure Portal: Data Explorer in resource allows CRUD on database like create databases, manage containers, queries
APIs and NoSQL functionality:
- SQL
- MongoDB
- Gremlin (graph)
- Table
- Cassandra (columnar)
- Allows switching between Cosmos features and other DB functionalities

Use cases
- Web, retail, marketing. Less than 10ms response time
- Gaming - cloud storage of in-game stats, social media, leader boards = millisecond response times
- Internet of Things (IoT) - ingest sensor real time data

Provisioning for Non-relational resources

Cosmos DB
- Azure Portal
  - Provisioned throughput = dedicated resources, serverless = on demand and good for development
  - Multi-region writes = multiple master database around world
  - Data encryption = Microsoft manager or bring you own key
- Azure Command Line Interface (CLI)
- PowerShell
- ARM templates (JSON code)
Storage account (SA)
- Access tiers hot/cool can be selected
- Data lake storage gen2 - allows SA to be a data lake

Data Migration Tool

Import data to Azure Cosmos DB from sources like:

JSON files
MongoDB
SQL Server
CSV files
Azure Table storage
Amazon DynamoDB
HBase
Azure Cosmos containers

Azure Authentication

User: password + option multifactor authentication (MFA), authentication token given to be used with application

Data in Cosmos DB

Consistency
- Updates are asynchronous
Within a single region, Cosmos DB uses a cluster of servers. This approach helps to improve scalability and availability.
1. Eventual
2. Consistent Prefix - changes in order
3. Session - changes in order
4. Bounded Staleness - lag in read and write
5. Strong - high latency, best consistency
- Choose consistency to match requirements of application

Query

Core SQL API allows dialect of SQL, like SELECT statements

SELECT <select_list>
[FROM <optional_from_specification)]
[WHERE <optional_filter_condition>]
[ORDER BY <optional_sort_specification>]
[JOIN <optional_join_specification>]

-- Example where c is a container
SELECT * FROM c
-- Display JSON data from container

SELECT * FROM c
WHERE c.Country == "Japan"
-- Dislay JSON data from records where field has Japan

-- Aggregation functions such as SUM, Average, Min, Max
SELECT COUNT(*) FROM Products p

SELECT SUM(p.quantity) FROM Products p
WHERE p.expired = 9

SELECT AVG(p.price) AS ‘Average Price’

FROM Products p

SELECT p1.ID, p.Name, p1.Description,
p1.Price FROM Products p1

WHERE p1.Price = (SELECT MIN(p2.Price) FROM
Product p2)

Containers
- Manage BLOBs into containers, for example images container
- Configure access: anonymous, public, authenticated, SAS token (with expiry dates)
- SAS tokens can be configured for files
File Share
- Create with access tier, storage quota
- Manage files:
  - Desktop
  - az copy CLI command - specific source file, destination container/file

Module 4: Datawarehouse Analytics

Data Storage and Processing in Azure

Ingest:

Azure Data Factory (ADF)
- Data integration service to automate data moving and transformation
Databricks
- Apache Spark based platform
  - Spark supports multiple languages (e.g. Python, Java, Scala, C#, R)
- Uses Azure security
- Integrates with Azure and PowerBI
- Can process from different data sources

Model, & Serve:

Azure Synapse Analytics
- Analytics engine
- Synapse SQL, pipelines, Link, Studio, Spark
- Transform data for analytics processing, generate reports

Visualize:

PowerBI can do visuals, ad hoc queries
Azure Active Directory = authentication for PowerBI and Analysis Services

Store:

Azure Data Lake Storage
- Repository of data
- Organizes data into directories
- Support POSIX and RBAC permissions
- Compatible with Hadoop distributed file system

Azure HDInsight
- Distributed processing compute Spark processing engine
- Apache Kafka (messages), Hadoop (storage), Hive (query) supported

Data Ingestion in Azure

ADF - heterogenous ingestion
- Linked server - data store, databricks
- Trigger > Pipeline > Dataset
- Uses parameters, integration runtime, control flow
PolyBase - file based data sources made to look like tables
SQL Server Integration Services (SSIS) - heterogenous - load, mine data, files

Example: Ingest data in Synapse for analytics processing

Create a storage account
- Create a new file share, hot tier
- Upload file
Create a ADF
Create an Azure Synapse Analytics workspace
- Add SQL pool
- At resource in Azure portal, open Synapse studio
  - Data > Databases
    - New SQL script, create a table
In ADF, set up new copy data
- Select source as Azure File Storage and the file
- Set destination as Synapse
- Set up data to table mapping