AAtsushi's Blog
Azure

Azure DP-203 Study Notes

→ 日本語版を読む

Data Warehouse Architecture

Datalake → Data Warehouse → BI

  • Datalake: Azure Storage Gen2
  • Data Warehouse: Azure Synapse Analytics
  • BI: PowerBI

Azure Storage Account

There are various container types, but for data engineering the main one used is Blob containers.

With Blob containers, you can configure Gen2 file system.

Azure Synapse Analytics uses Gen2 file system.

Minimum Storage Duration

https://learn.microsoft.com/en-us/azure/storage/blobs/access-tiers-overview#summary-of-access-tier-options

  • Hot access tier: No minimum
  • Cool access tier: Must be stored for at least 30 days
  • Archive access tier: Must be stored for at least 180 days

Access Methods

  • SAS
  • Access keys

Power BI can also connect to a storage account.

Storage Explorer is an explorer tool for Azure Storage Account.

Access Control Lists for Azure Data Lake Storage Gen2

https://learn.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-access-control

Query Acceleration for Azure Data Lake Storage Gen2

https://learn.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-query-acceleration

Lifecycle Management Rule Definitions

https://learn.microsoft.com/en-us/azure/storage/blobs/lifecycle-management-overview#sample-rule

  • Use tierToCool, tierToArchive, and delete to define when to change to cool, archive, or delete.
{
  "rules": [
    {
      "enabled": true,
      "name": "sample-rule",
      "type": "Lifecycle",
      "definition": {
        "actions": {
          "version": {
            "delete": {
              "daysAfterCreationGreaterThan": 90
            }
          },
          "baseBlob": {
            "tierToCool": {
              "daysAfterModificationGreaterThan": 30
            },
            "tierToArchive": {
              "daysAfterModificationGreaterThan": 90,
              "daysAfterLastTierChangeGreaterThan": 7
            },
            "delete": {
              "daysAfterModificationGreaterThan": 2555
            }
          }
        },
        "filters": {
          "blobTypes": [
            "blockBlob"
          ],
          "prefixMatch": [
            "sample-container/blob1"
          ]
        }
      }
    }
  ]
}

SAS

Azure Storage Redundancy

https://learn.microsoft.com/en-us/azure/storage/common/storage-redundancy

  • Even with GRS (not RA-GRS), read access to the secondary region is possible during failover
  • With RA-GRS, read access to the secondary region is always possible even without a failure

Best Practices for File Size

https://learn.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-best-practices#file-size

  • Increasing file size improves performance and reduces costs.
  • Generally, organizing data into larger files (256 MB to 100 GB) improves performance.
  • Increasing file size can also reduce transaction costs. Since read and write operations are billed in 4 MB units, you are charged per operation regardless of whether the file size is 4 MB or a few KB.

Managed ID

https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview

  • Enabling Managed ID on an Azure resource registers the resource in Azure AD and an ID is issued. That is the Managed ID.
  • Can be automatically assigned or assigned by the user.
  • Using Managed ID, Azure resources can access other resources (when IAM is configured).
  • For example, when accessing Blob storage from Azure Synapse Analytics' dedicated SQL, you could use SAS or access keys, but granting permissions via IAM to the managed ID of the Azure Synapse Analytics workspace is more secure.

Azure SQL

Under the Azure SQL umbrella, two types of services exist:

  • Azure SQL Database
  • Azure SQL Managed Instance

Azure SQL Database

Deployment Options

  • Single database
    • Fully managed isolated database
  • Elastic pool
    • A collection of single databases with a shared set of resources such as CPU and memory
  • Managed Instance

Pricing Models

  • vCore
    • A model that provides optimal memory and storage options depending on the workload
  • DTU: Database Transaction Unit
    • A model with fixed storage capacity available at a flat rate

Azure SQL Managed Instance

Skipped

Transact-SQL

Syntax Conventions

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver16

I didn't know when [ ] was used, so I looked it up. Based on the above documentation, it seems optional items are indicated this way for readability (?).

GO Command

In Transact-SQL, values are not returned when you SELECT.

Executing the GO command retrieves the SELECT results. Everything up to the GO command is executed.

The GO command becomes effective from after the previous GO command was executed. Even if a variable is defined before the previous GO command, that variable cannot be used in lines after the previous GO command.

Azure Synapse Analytics

https://learn.microsoft.com/en-us/azure/synapse-analytics/overview-what-is

Azure Synapse Analytics provides the following services in an integrated manner:

  • Synapse Studio
  • Synapse SQL (serverless, dedicated)
  • Data Factory
  • Apache Spark for Azure Synapse
  • Azure Data Lake Storage Gen2
  • Azure Synapse Data Explorer

Double Encryption with Customer-Managed Keys

https://learn.microsoft.com/en-us/azure/synapse-analytics/security/workspaces-encryption#manage-the-workspace-customer-managed-key

  • Workspaces are encrypted by default with platform-managed keys.
  • Customer-managed key encryption can also be configured.
  • First, generate a key in Azure Key Vault.
  • When creating the Synapse Analytics workspace, enable "Double encryption using a customer-managed key" in the Security tab.
  • While the Synapse Analytics workspace is being created, configure the access policy for the key generated in Azure Key Vault. Grant "Get", "Wrap", and "Unwrap" permissions to the managed ID of Azure Synapse Analytics.

Synapse SQL

This document clearly summarizes it:

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/overview-architecture

In Synapse SQL, data computation is distributed across multiple nodes.

By separating compute from storage, compute can be scaled.

Compute nodes store all user data in Azure Storage and execute parallel queries.

Since data is stored and managed by Azure Storage, storage usage is billed separately.

Synapse SQL has two types:

  • Serverless SQL pool
  • Dedicated SQL pool

Serverless places files in blob containers etc. and reads them as external tables, so the data is external. On the other hand, the dedicated SQL pool has a server where data is placed. The dedicated SQL pool requires creating users and granting permissions. Additionally, workload groups and workload classifiers also appear. I don't quite understand, so needs verification!

Dedicated SQL Pool

Distributed Processing (Distribution)

Query requests are accepted at the control node. The control node distributes requests to multiple compute nodes for parallel processing. A SQL pool has multiple compute nodes (of course it can also be configured with just one).

Meanwhile, data is split into 60 distributions and stored. Distributions are saved in Azure Storage.

Since data is split, compute nodes can process in parallel. For example, by mapping compute nodes to distributions one-to-one, parallel processing is performed.

Furthermore, this configuration allows compute and storage to be scaled separately.

Choosing Distribution Columns with Evenly Distributed Data

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute#choose-a-distribution-column-with-data-that-distributes-evenly

When selecting distributions, refer to the following:

  • Contains many unique values.
  • Has no or few NULLs.
  • Is not a date column.

Table Types

SQL pool tables have 3 types:

  • Round robin: Inserts into physically different storage in sequence
  • Hash table: Converts a specific column to a hash value and separates storage by hash value
  • Replicated table: Puts data into all storage

By default, tables are round robin tables. Table types can be identified by the table icon in SSMS.

DBCC PDW_SHOWSPACEUSED('table name') can be used to check the node ID where each record of a table is stored.

When trying to GROUP BY values in a round robin table, data shuffling is required because the same values may be distributed to different nodes (shuffling data to gather the same values on the same node).

Table type can be specified with the WITH clause.

CREATE TABLE [dbo].[SalesFact]([ProductID] [int] NOT NULL,
    [SalesOrderID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL,
    [OrderQty] [smallint] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [OrderDate] [datetime] NULL,
    [TaxAmt] [money] NULL) WITH
(
DISTRIBUTION = HASH (CustomerID) )

Tools

  • SSMS (SQL Server Management Studio)
    • A tool similar to pgAdmin for PostgreSQL.

Partition

Keep data in small groups called partitions.

When filtering with a WHERE clause, using partitions can narrow down the data to read and improve performance.

Normally partitioned by date.

At least 1 million rows are needed per distribution and partition.

CREATE PARTITION FUNCTION myRangePF1 (datetime2(0))
    AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;
GO

CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    ALL TO ('PRIMARY') ;
GO

CREATE TABLE dbo.PartitionTable (col1 datetime2(0) PRIMARY KEY, col2 char(10))
    ON myRangePS1 (col1) ;
GO

https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16#partition-function

  • LEFT range specifies that when interval values are sorted in ascending order from left to right by the database engine, the boundary value belongs to the left side of the boundary value interval. That is, the maximum value of the boundary is included in the partition.
  • RIGHT range specifies that when interval values are sorted in ascending order from left to right by the database engine, the boundary value belongs to the right side of the boundary value interval. That is, the smallest boundary value is included in each partition.

Partition Switching

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition#partition-switching

  • The dedicated SQL pool supports splitting, merging, and switching partitions.
  • Uses the ALTER TABLE statement.
    • To switch partitions between two tables, partitions must be aligned at respective boundaries and table definitions must match.
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales_20000101]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101;

ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;

Column-Oriented

Azure SQL Database is a row-oriented database, but Azure Synapse Analytics SQL Pool is a column-oriented database.

Indexes

Clustered Columnstore Index

  • By default, a columnstore index is provided
  • Cannot use a clustered columnstore index if data type is varchar(max), nvarchar(max), or varbinary(max)
  • Only one index can be created per table
  • No specific column is specified when creating the index
CREATE TABLE myTable
  (
    id int NOT NULL,
    lastName varchar(20),
    zipCode varchar(6)
  )
WITH ( CLUSTERED COLUMNSTORE INDEX );

Clustered Index

  • Use this when a clustered columnstore index cannot be created.
  • Only one index can be created per table
  • A specific column is specified when creating the index
CREATE TABLE myTable
  (
    id int NOT NULL,
    lastName varchar(20),
    zipCode varchar(6)
  )
WITH ( CLUSTERED INDEX (id) );

Non-Clustered Index

  • Unlike clustered indexes, multiple non-clustered indexes can be created
  • Unlike clustered indexes, a separate view for the index is created apart from the table
CREATE INDEX zipCodeIndex ON myTable (zipCode);

Heap Table

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-ver16

  • Creating tables in cache can speed up data writes and reads.
  • Used for data transformation before loading into production tables.
  • Clustered indexes cannot be used with heaps.
  • Non-clustered indexes can be created.
CREATE TABLE <table name> (
  ...
)
WITH ( HEAP )

Encryption of Older Versions of Dedicated SQL Pool (formerly SQL DW)

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-manage-security#encryption

Data Masking

https://learn.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview?view=azuresql

  • Data can be easily masked with built-in functions.
  • Automatically finds columns containing email addresses, phone numbers, etc. and recommends masking.
  • Masking is not applied to users with administrator privileges.
  • Masking can also be added with SQL as shown below.
  • Note that data masking makes data unrecognizable visually, but the data is not encrypted.
ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)'

https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017

Auditing

https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview?view=azuresql#setup-auditing

  • A feature that can easily accumulate audit logs in Log Analytics or storage

How to configure:

  • Select Auditing under Security in the left menu of the target Azure Synapse Analytics.
  • Enable auditing.
  • Select Log Analytics etc. and specify the storage destination.

Data Discovery and Classification

https://learn.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview?view=azuresql

  • Can register which tables hold what sensitive data.
  • Clicking "Data Discovery & Classification" automatically reads columns and finds columns holding sensitive information.
  • Then provides classification recommendations.

Accessing Dedicated SQL with Azure AD Authentication

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/active-directory-authentication

  • Can access dedicated SQL with an Azure AD account (no SQL authentication account/password needed)
  • In the SSMS authentication screen, select "Azure Active Directory - Universal with MFA" and enter the account normally used to access Azure.
  • The following user must be created in the dedicated SQL and roles assigned in advance.
CREATE USER [xxxx@xxxxxx.onmicrosoft.com]
FROM EXTERNAL PROVIDER
WITH DEFAULT_SCHEMA = dbo;

CREATE ROLE [NewRole]
GRANT SELECT ON SCHEMA [dbo] TO [NewRole]
EXEC sp_addrolemember N'NewRole', N'xxxx@xxxxxx.onmicrosoft.com'

Row-Level Security

https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16#Typical

  • Simply put, use a WHERE clause to return only rows containing the name of the user executing the query.

Column-Level Security

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/column-level-security?view=sql-server-ver16#example

  • Achieve column-level security by using GRANT statements to allow SELECT on only specific columns.
GRANT SELECT ON Membership(MemberID, FirstName, LastName, Phone, Email) TO TestUser;

Creating External Tables Using Managed ID

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=user-identity#access-a-data-source-using-credentials

  • External tables can be created using Managed ID instead of SAS.
  • IDENTITY = 'Managed Identity' below specifies Managed ID authentication.
  • The Azure Synapse Analytics workspace must be granted storage access permissions via IAM in advance (and ACL permissions on storage if necessary).
CREATE DATABASE SCOPED CREDENTIAL SynapseWorkspaceIdentity
WITH IDENTITY = 'Managed Identity'

CREATE EXTERNAL DATA SOURCE log_data_managed
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>'
CREDENTIAL = SynapseWorkspaceIdentity,
TYPE = HADOOP
)

CREATE EXTERNAL FILE FORMAT TextFileFormatManaged WITH (
      FORMAT_TYPE = DELIMETEDTEXT,
      FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        FIRST_ROW=2
      )
)

CREATE EXTERNAL TABLE logdatamanaged
(
  ...
)
WITH (
  LOCATION = 'data/Log.csv',
  DATA_SOURCE = log_data_managed,
  FILE_FORMAT = TextFileFormatManaged
)

Checking Workload Using Dynamic Management Views

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor

  • Can check workloads such as query execution in the dedicated SQL pool using Dynamic Management Views (DMV).

Result Set Caching

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-result-set-caching

  • Result set caching can be enabled.
  • Automatically caches query results in the database.
  • Can improve performance.
  • If result set cache is not used, it is cleared every 48 hours.
  • Or deleted when the result set cache is full.
-- Check databases with caching enabled
-- Returns 1 for cache hit, 0 for cache miss, negative value for reason result set cache was not used.
SELECT name, is_result_set_caching_on FROM sys.databases

-- Enable database query store
-- Execute on master database.
ALTER DATABASE [database_name]
SET QUERY_STORE = ON;

-- Enable result set caching for database
ALTER DATABASE [database_name]
SET RESULT_SET_CACHING ON;

Workload Management

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-classification

Restoring from Restore Points

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/backup-and-restore#restoring-from-restore-points

Restore Point Retention Period
Details of the restore point retention period are as follows:

- Dedicated SQL pool deletes restore points when a 7-day retention period is reached and when the total number of restore points is at least 42 (both user-defined and automatic).
- Snapshots are not taken when the dedicated SQL pool is paused.
- The age of a restore point is measured by the absolute calendar days from when the restore point was taken (including when the SQL pool was paused).
- At any given time, a dedicated SQL pool is guaranteed to store up to 42 user-defined restore points or 42 automatic restore points, as long as these restore points have not reached the 7-day retention period.
- If a snapshot is taken and the dedicated SQL pool is paused for more than 7 days and then resumed, the restore points will be maintained until the total number of restore points reaches 42 (both user-defined and automatic).

Creating Surrogate Keys in Dedicated SQL Pool Using IDENTITY

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-identity

  • A surrogate key for a table is a column with a unique identifier for each row
  • Using the IDENTITY property, this goal can be easily and effectively achieved without impacting load performance.
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL
,    C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2)
,   CLUSTERED COLUMNSTORE INDEX
)
;

Connect to Dedicated SQL with Azure AD Identity

Create Contained Users Mapped to Azure AD Identities

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell&view=azuresql#create-contained-users-mapped-to-azure-ad-identities

  • To use Azure Active Directory authentication with SQL Database, you must use contained database users based on Azure AD identities.
  • Contained database users have no login in the master database and are mapped to an Azure AD identity associated with the database.
  • Azure AD identities can be either individual user accounts or group accounts.
CREATE USER [appName] FROM EXTERNAL PROVIDER;

Connect Using SSMS or SSDT with Azure AD Identity

  • Connect to SQL Database using Azure AD identity.

Cache Hit and Utilization

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-how-to-monitor-cache#cache-hit-and-used-percentage

  • Summarizes the relationship between cache hit rate and cache utilization.

Serverless SQL Pool

Reading JSON Files Using OPENROWSET

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files

  • Read line-delimited JSON files with OPENROWSET and return as individual rows.
select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows

https://learn.microsoft.com/en-us/sql/relational-databases/json/import-json-documents-into-sql-server?view=sql-server-ver16

  • OPENROWSET reads a single text value from a file.
  • OPENROWSET returns the value as BulkColumn, which is passed to the OPENJSON function.
  • OPENJSON iterates over the array of JSON objects within the BulkColumn array.

Creating External Tables

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#arguments-create-external-table

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]
        [, <reject_options> [ ,...n ] ]
    )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

<reject_options> ::=
{
    | REJECT_TYPE = value,
    | REJECT_VALUE = reject_value,
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

  • LOCATION = 'folder_or_filepath'

    • Specifies the folder or file path and file name of actual data in Azure Blob Storage
    • Unlike Hadoop external tables, native external tables do not return subfolders unless /** is specified at the end of the path.

  • Can read all files in a folder, recursively read folders, or read files from multiple folders.

  • For external tables, only the following Data Definition Language (DDL) statements can be used:

    • CREATE TABLE and DROP TABLE
    • CREATE STATISTICS and DROP STATISTICS
    • CREATE VIEW and DROP VIEW

  • Other DDL (ALTER, TRUNCATE) is not supported.

  • Data Manipulation Language (DML) delete, insert, update operations are also not supported.

Synchronizing External Table Definitions from Azure Synapse Spark Pool

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-spark-tables

  • For each Spark external table based on Parquet or CSV and placed in Azure Storage, an external table is created in the serverless SQL pool database.
  • Therefore, even after shutting down the Spark pool, queries can still be executed against Spark external tables from the serverless SQL pool.

PolyBase

https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver16

  • Can directly query external data in blob storage etc. from SQL Server.

  • PolyBase loads rows smaller than 1 MB. A single row cannot exceed 1 MB.

Converting from Vertical to Horizontal Layout with PIVOT

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

  • PIVOT transforms a table-valued expression by changing multiple unique values in one column of an expression into multiple columns in the output.
  • The UNPIVOT relational operator is the opposite of PIVOT, transforming columns of a table-valued expression into column values.
  • PIVOT syntax is simpler and easier to read than using complex combinations of SELECT...CASE statements to specify the same operation.

Transparent Data Encryption

https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview?tabs=azure-portal&view=azuresql

  • Encrypts database, backup, and log files to prevent direct access to data from backups or logs.
  • Enabled by default

Always Encrypted

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15

  • A feature designed to protect sensitive data such as credit card numbers and national identification numbers
  • With Always Encrypted, the client encrypts sensitive data within the client application and never exposes the encryption key to the database engine.
  • This separates those who own data and can view it from those who manage data but should not have access (on-premises database administrators, cloud database operators, or other high-privileged unauthorized users).
  • As a result, Always Encrypted allows customers to confidently store sensitive data in the cloud and reduce the possibility of data theft by malicious insiders.

Deterministic and Randomized Encryption

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16#selecting--deterministic-or-randomized-encryption

  • Deterministic encryption

    • Always generates the same encrypted value for a given plaintext value.
    • Can perform joins, grouping, and indexing on encrypted columns.

  • Randomized encryption

    • Randomized encryption is more secure.
    • Cannot perform searches, grouping, indexing, or joins on encrypted columns.

IP Address Restriction for Azure Synapse Analytics Workspace

https://learn.microsoft.com/en-us/azure/synapse-analytics/security/synapse-workspace-ip-firewall

  • Can restrict the IP addresses from which the Azure Synapse Analytics workspace is accessed.
  • IP addresses can be specified in "Network" in the left menu of the workspace.
  • There is also an option "Allow Azure services and resources to access this workspace."

Data Factory

  • A service for data transformation

Components

The main components are as follows. See the next link for details.

https://learn.microsoft.com/en-us/azure/data-factory/introduction#top-level-concepts

  • Linked Service: Holds connection string information for ingesting data from data sources or loading data to data destinations.
  • Dataset: Holds data structures. Not the data itself, but a named view referencing data.
  • Activity: Transformation logic
  • Pipeline: Bundles multiple activities
  • Integration Runtime: The computing infrastructure for data extraction, transformation, and loading
  • Pipeline run: Instance that executes a pipeline

Services Available Within Data Factory

  • Data Flow
  • Power Query

Integration Runtime

Data extraction, transformation, and load processing is performed by the Azure Integration Runtime.

Integration runtime is also used for completely different purposes. When connecting data from a VM to Synapse Analytics, you need to install the self-hosted integration runtime on the VM. Otherwise, data from the VM cannot be connected.

Types

  • Azure Integration Runtime (serverless)
  • Azure Managed VNET Integration Runtime
  • Self-hosted Integration Runtime

Setting Up Self-Hosted Integration Runtime

First, select Integration runtimes in the "Manage" screen of Data Factory.

Click New and set up the self-hosted integration runtime.

An authentication key will be displayed, so enter the authentication key in the self-hosted integration runtime installed on the VM.

This registers the VM with Data Factory.

On the Data Factory side, select File → File System in the new dataset.

Creating and Running Pipelines

In the case of a copy activity, in addition to the "Copy data" activity, create a linked service for the source and destination (2 total) and datasets (2 total).

As linked services, register, for example, the storage account's connection string and account key. As datasets, register, for example, the linked service, storage file path, delimiters, and escape characters.

After creating these resources, officially register them by "Publishing" before execution.

To run the pipeline immediately, click "Trigger now" from "Add trigger."

Pipeline execution status can be checked in "Monitor". Monitor allows checking detailed execution history and error messages.

Copy Activity

  • When a DB such as Azure SQL database is selected as the source, in addition to specifying the table itself, a query can be written for loading.

Preserving File Hierarchy in Copy Activity

https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-data-lake-storage?tabs=data-factory

  • When specifying Azure Data Lake Storage as the sink, PreserveHierarchy, FlattenHierarchy, or MergeFiles can be selected for copyBehavior:
    • PreserveHierarchy (default): Preserves the file hierarchy within the target folder. The relative path of the source file to the source folder is the same as the relative path of the target file to the target folder.
    • FlattenHierarchy: Places all files from the source folder at the first level of the target folder. Target files will have auto-generated names.
    • MergeFiles: Merges all files from the source folder into one file. If a file name is specified, the merged file name will be the specified name. Otherwise, it will be an auto-generated file name.

Get Metadata Activity

Can retrieve metadata of Blob storage and Azure SQL Database within the data flow. What metadata can be retrieved differs by resource. See the following for details. Can get all file names in storage with the Get Metadata activity and process each file with the For Each activity.

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity#supported-connectors

Data Flow Activity

  • Data transformation is possible by writing queries in copy activity, but using Data Flow avoids having to hard-code queries.
  • Can transform data without writing code.
  • An Apache Spark cluster is created and executed on the cluster. Starting the Apache Spark cluster takes time.
  • Transformation results can be confirmed in debug mode.
  • Debug mode is also billed hourly.
  • Billed for 1 hour even if used for only a few minutes.
  • Unit price is per vCore. At minimum, 8 vCores are used.

Representative Transformations

All "transformations" are listed below:

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-transformation-overview

Schema Drift

"Schema drift" means schema discrepancies. By allowing schema drift in the source or sink of a copy activity, even if columns are added or deleted, it won't result in an error but will be treated as a discrepancy.

If Auto mapping is on in mapping, drifted columns are also written to the sink.

To reference drifted columns, you need to name the columns using derived columns. By setting Column to $$ and Expression to $$, the input column name can be set as-is.

https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-schema-drift#map-drifted-columns-quick-action

Using Git with Data Factory

Create an Azure DevOps account, create a project and repository, and configure Azure DevOps Git repository in Git Configuration under Manage in Data Factory to save pipelines created in Data Factory.

https://learn.microsoft.com/en-us/azure/data-factory/source-control#author-with-azure-repos-git-integration

Using Azure Pipelines Releases in Azure DevOps, deployment to a Data Factory environment prepared for production can be automated.

https://learn.microsoft.com/en-us/azure/data-factory/continuous-integration-delivery-automate-azure-pipelines

Pricing

https://azure.microsoft.com/en-us/pricing/details/data-factory/data-pipeline/

  • Billed based on number of activity runs and their execution time
  • Integration runtime has 3 types of configurations, each with different pricing
  • Data Flow is also billed separately. Billed for Data Flow cluster execution time and debug time.
  • You can see how much is being consumed in the pipeline run history in "Monitor."

Incremental Copy

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-lastmodified-copy-data-tool

  • By using tumbling windows, you can periodically extract past data.
  • In tumbling windows, the start date and time is assumed to be UTC, so no timezone specification is needed.
  • Select Incremental load: LastModifiedDate in File loading behavior.

Double Encryption with Customer-Managed Keys

https://learn.microsoft.com/en-us/azure/data-factory/enable-customer-managed-key

  • By default, encrypted with Microsoft-managed keys that are randomly generated and uniquely assigned to the data factory.
  • Can additionally configure customer-managed keys for double encryption.
  • Generate a key in Azure Key Vault and grant "Get", "Wrap key", and "Unwrap key" permissions to Data Factory.
  • Select Customer managed key from Manage in Data Factory and configure the URI of the customer-managed key.

Monitoring with Azure Monitor

https://learn.microsoft.com/en-us/azure/data-factory/monitor-using-azure-monitor

Adding Annotations to Pipelines

https://learn.microsoft.com/en-us/azure/data-factory/concepts-annotations-user-properties?source=recommendations

  • Annotations are static values that can be assigned to pipelines, datasets, linked services, and triggers
  • Click the [+ New] button on the Properties icon and give the annotation an appropriate name.
  • Navigate to the [Monitor] tab where this annotation can be filtered in [Pipeline runs].
  • Dynamic values at the activity level can also be monitored.

Creating Azure Integration Runtime

Errors in Child Pipelines

  • When a parent pipeline calls a child pipeline and the child pipeline errors, the parent pipeline is also recorded as an error in the execution data.

Integration with On-Premises

  • To ingest data from on-premises, create a Linked service. Pipelines and activities are not used in this case.

Binary in Azure Data Factory and Azure Synapse Analytics

https://learn.microsoft.com/en-us/azure/data-factory/format-binary

  • Binary datasets can be used with Copy, GetMetadata, and Delete activities.
  • When using a binary dataset, the service does not parse the file contents and processes them as-is. This makes processing faster.

Azure Event Hubs

A streaming send/receive platform. Similar to Apache Kafka.

Create a namespace and create an Event Hub within it. An endpoint URL is issued, to which data is sent.

Events that have been received are not deleted until the configured expiration period passes.

Using checkpoints, you can avoid re-reading events that have already been read once.

By reading and writing in parallel with multiple partitions, the amount that can be processed at once is increased.

Writing to Blob Storage with Capture Feature

Using Azure Event Hubs Capture, streaming data from Event Hubs can be automatically delivered to Azure Blob Storage, Azure Data Lake Storage Gen1, or Azure Data Lake Storage Gen2 without using Azure Stream Analytics.

https://learn.microsoft.com/en-us/azure/event-hubs/event-hubs-capture-enable-through-portal

Dynamically Adding Partitions to Event Hub

https://learn.microsoft.com/en-us/azure/event-hubs/dynamically-add-partitions

  • The number of partitions can be specified when creating the event hub.
  • Partitions can also be added after the event hub is created.
  • ※ The method for dynamically adding partitions is only available in the premium and dedicated tiers of Event Hubs.

Azure Stream Analytics

Can execute queries against streaming data in real-time and output results.

If the read start time is set to Now, only data received after the current time is retrieved.

If the read start time is set to Custom, past events can also be retrieved.

Using Reference Data

https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-use-reference-data

  • Reference data can be JOINed to streaming data.
  • Reference data is placed in Azure Blob Storage and Azure SQL Database.
  • See below for how to define reference data input.

https://learn.microsoft.com/en-us/azure/stream-analytics/sql-reference-data

Window Functions

Tumbling Window

Groups events received at regular intervals.

Each window time does not overlap.

Example: Want to know the events that occurred every 10 seconds.

SELECT System.Timestamp() as WindowEndTime, TimeZone, COUNT(*) AS Count
FROM TwitterStream TIMESTAMP BY CreatedAt
GROUP BY TimeZone, TumblingWindow(second,10)

Hopping Window

Groups events received at regular intervals.

Each window time overlaps.

Example: Want to know the number of events that occurred in the past 10 seconds every 5 seconds.

SELECT System.Timestamp() as WindowEndTime, Topic, COUNT(*) AS Count
FROM TwitterStream TIMESTAMP BY CreatedAt
GROUP BY Topic, HoppingWindow(second,10,5)

Sliding Window

Groups going back a certain period when an event is received.

Example: Want to alert when 3 or more events occur within 10 seconds.

SELECT System.Timestamp() as WindowEndTime, Topic, COUNT(*) AS Count
FROM TwitterStream TIMESTAMP BY CreatedAt
GROUP BY Topic, SlidingWindow(second,10)
HAVING COUNT(*) >=3

Session Window

Started when the first event occurs. If another event occurs within the specified timeout period after the last event was ingested, the window extends to include the new event. If no event occurs within the timeout period, the window closes at timeout. If events keep occurring within the specified timeout period, the session window continues to extend until it reaches the maximum duration.

Example: Want to know the number of events with no more than 5 seconds between them.

SELECT System.Timestamp() as WindowEndTime, Topic, COUNT(*) AS Count
FROM TwitterStream TIMESTAMP BY CreatedAt
GROUP BY Topic, SessionWindow(second,5,10)

Snapshot Window

Groups events received at exactly the same time (at the same timestamp).

Example: Want to know the events that occurred at exactly the same time.

SELECT System.Timestamp() as WindowEndTime, Topic, COUNT(*) AS Count
FROM TwitterStream TIMESTAMP BY CreatedAt
GROUP BY Topic, System.Timestamp()

https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-window-functions

OVER Clause

https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?source=recommendations&view=sql-server-ver16

LAG/LAST Clause

https://learn.microsoft.com/en-us/stream-analytics-query/lag-azure-stream-analytics

https://learn.microsoft.com/en-us/stream-analytics-query/last-azure-stream-analytics

  • The LAG analytic operator allows referencing a "previous" event in the event stream within a specified constraint.
LAG(<scalar_expression >, [<offset >], [<default>])
     OVER ([PARTITION BY <partition key>] LIMIT DURATION(<unit>, <length>) [WHEN boolean_expression])

Example: Find the previous non-null sensor reading

SELECT
     sensorId,
     LAG(reading) OVER (PARTITION BY sensorId LIMIT DURATION(hour, 1) WHEN reading IS NOT NULL)
     FROM input

UDF

User-defined functions can be created in JavaScript and used in queries.

https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-javascript-user-defined-functions

Detecting Event Intervals

  • Query for detecting intervals between events

https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-stream-analytics-query-patterns#detect-the-duration-between-events

  • LAST function

Azure Stream Analytics Metrics

https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-job-metrics

  • The above metrics can be monitored.
  • In particular, the following metrics are important:
  • Backlogged input events
    • The number of backlogged input events. A non-zero value for this metric means the job is not keeping up with the number of events received. If this value is slowly increasing or is consistently non-zero, the job should be scaled out. See "Understanding and adjusting Streaming Units" for details.
  • Data conversion errors
    • Number of output events that could not be converted to the expected output schema. To drop events that occur in this scenario, change the error policy to "Drop."
  • Early input events
    • Events whose application timestamp is more than 5 minutes earlier than the arrival time.
  • Late input events
    • Events that arrived later than the configured late arrival tolerance period. See "Azure Stream Analytics event order considerations" for details.
  • Out-of-order events
    • The number of events received out-of-order that were either dropped or given an adjusted timestamp based on the event ordering policy. This metric can be affected by the configuration of the "out-of-order tolerance window" setting.

Streaming Units

  • When streaming unit utilization reaches 100%, the job will start failing
  • Streaming unit utilization must be continuously monitored

Best Practices for Streaming Units

https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-streaming-unit-consumption

  • Set a minimum of 6 streaming units for queries that don't use PARTITION BY.
  • More streaming units than needed must be allocated.

Creating Event Ordering Policy

https://learn.microsoft.com/en-us/azure/stream-analytics/event-ordering

  • Event/application time is the timestamp contained in the event payload (the time the event was generated)
  • Arrival time is the timestamp of when the event arrived at the input source (Event Hub/IoT Hub/Blob storage).
  • By default, Stream Analytics processes events by arrival time, but using TIMESTAMP BY clause in a query, events can also be processed by event time.
  • Late arrival and out-of-order policies can only be applied when processing events by event time
  • Late arrival policy
    • Events may arrive late for various reasons. For example, an event whose event time is 00:10:00 that arrives 40 seconds late will have an arrival time of 00:10:40. If the late arrival policy is set to 15 seconds, events arriving more than 15 seconds late are either dropped (not processed by Stream Analytics) or their event time is adjusted. In the above example, since the event arrived 40 seconds late (later than the policy setting), the event time is adjusted to 00:10:25 (arrival time - late arrival policy value) to match the maximum value of the late arrival policy. The default late arrival policy is 5 seconds.
  • Out-of-order policy
    • Events may also arrive out-of-order. After adjusting event time based on the late arrival policy, out-of-order events can also be automatically dropped or adjusted. If this policy is set to 8 seconds, out-of-order events that arrive within 8 seconds will be sorted by event time. Events that exceed the set time are dropped or adjusted to the maximum value of the out-of-order policy. The default out-of-order policy is 0 seconds.

Handling Time in Azure Stream Analytics

https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-time-handling

  • Watermark
    • An event time marker indicating up to what point events have been received by the streaming processor.
  • Late arrival events
    • Due to the definition of late arrival tolerance, Azure Stream Analytics compares event time with arrival time for each received event. If the event time is outside the tolerance period, the system can be configured to drop the event or adjust the event time to be within the tolerance period. Once a watermark is generated, the service may receive events with event times lower than the watermark. These events can be configured to be discarded or the event time adjusted to the watermark.
  • Early arrival events
    • You may notice another concept called early arrival window, which is the opposite of the late arrival tolerance value window. This window is fixed at 5 minutes and serves a different purpose from the late arrival tolerance value window. Since Azure Stream Analytics guarantees complete results, you only need to specify the job start time as the first output time of the job (not the input time). The job start time is needed to ensure that complete windows (not just from the middle of a window) are processed.

Monitoring Job Stoppage

  • Cannot be monitored with the Runtime Error metric. The Runtime Error metric only applies when the job can receive data but query processing is erroring.
  • Can be monitored with All Administrative operations. By monitoring the failure status of All Administrative operations, alerts are issued when a job unexpectedly stops.

Azure IoT Hub

When an IoT device is registered with Azure IoT Hub, a connection string is generated. Configure that on the device side.

The device sends stream data to IoT Hub, and Azure Stream Analytics is used to execute queries and extract data.

Scala

  • Typed language
  • Both object-oriented and functional programming language
  • Compiles to Java bytecode.
  • Runs on Java Runtime
  • Uses REPL (Read Evaluate Print and Loop), a command-line utility

Installation

Download and install from:

https://docs.scala-lang.org/getting-started/index.html

Enter Scala's interactive mode with the following command:

> scala3
Welcome to Scala 3.2.2 (19, Java Java HotSpot(TM) 64-Bit Server VM).
Type in expressions for evaluation. Or try :help.

scala> 3
val res0: Int = 3

scala> 4
val res1: Int = 4

scala> res0 + res1
val res2: Int = 7

var and val

var is a mutable variable.

val is an immutable variable (like a const variable in Java).

var is short for variable, val is short for value.

Attempting to reassign to a val results in a reassignment to val error.

Syntax

  • if statement
var i = 8

if (i < 10)
  {
    println("i is less than 10")
  }
else
  {
    println("i is more than and equal to 10")
  }
  • for statement
for (i<-1 to 10)
  println("the value is " + i)
  • while statement
var i = 0
while (i < 10) {
  println("the value is " + i)
  i += 1
}
  • case statement
val i = 29
i match{
  case i if i<30 => println("i is less than 30")
  case i if i>=30 => println("i is more than and equal to 30")
}
  • Function
def add(x:Int, y:Int): Int = {
  return x + y
}

val x = 2
val y = 1
println("x + y is " + add(x, y))
  • List
var numbers:List[Int] = List(0, 1, 2, 3, 4)

println("The number is " + numbers.head) // The number is 0
numbers.foreach{println} // 1 2 3 4
println("The number is " + numbers(2)) // The number is 1

var names:List[String] = List("Yamada", "Nishida", "Hanada")
names.foreach(println) // Yamada Nishida Hanada
println("The index is " + names.indexOf("Hanada")) // The index is 2

Azure Synapse Spark Pool

Apache Spark is a distributed processing framework.

Can run a Spark cluster in the Azure Synapse Analytics Spark pool.

The Driver node accepts Spark jobs and distributes them to Executor nodes.

Pricing

Per-minute billing per virtual core.

Since Spark pool is serverless, no charges are incurred just by creating a Spark pool.

Nodes are created and billed only when a Spark job is submitted.

The minimum number of nodes is 3, with one becoming the Driver node.

In Spark, code is not executed until the moment it should be executed.

Development

Create an Apache Spark pool in Azure Synapse Analytics.

Next, open Synapse Studio in Azure Synapse Analytics and create a notebook in Develop.

Assign the created Spark pool to the notebook.

Select Spark (Scala) as the language.

RDD (Resilient Distributed Dataset)

Using sc.parallelize(data) as shown below, data can be distributed to each node for parallel distributed processing.

val data = Array(1, 2, 3, 4, 5)

val rdd = sc.parallelize(data)

Spark Dataset

Can perform transformation processing in parallel.

Transformation processing creates a new dataset.

Spark datasets can be processed with Scala and Java.

val data = Array(1, 2, 3, 4, 5)

val rdd = sc.parallelize(data)

val ds

Spark DataFrame

A Spark DataFrame is a Spark Dataset with column names.

DataFrames can be created from external files.

Spark DataFrames can be processed with Scala, Java, Python, and R.

val data = Array(1, 2, 3, 4, 5)

val rdd = sc.parallelize(data)

val df = rdd.toDF() // Convert to DataFrame

For a comparison of RDD, DataFrame, and Dataset, refer to:

https://yubessy.hatenablog.com/entry/2016/12/11/095915

Sorting Columns

from pyspark.sql.functions import desc
from pyspark.sql.functions import col

sortted_df = df.sort(col("column name").desc())
display(sorted_df)

Loading Data

Load the Log.csv file placed in Storage Gen2 as a Spark DataFrame.

from pyspark.sql import SparkSession
from pyspark.sql.types import *

account_name = "datalake2000"
container_name = "data"
relative_path = "raw"
adls_path = 'abfss://%s@%s.dfs.core.windows.net/%s' % (container_name, account_name, relative_path)

spark.conf.set("fs.azure.account.auth.type.%s.dfs.core.windows.net" %account_name, "SharedKey")
spark.conf.set("fs.azure.account.key.%s.dfs.core.windows.net" %account_name ,"V0bi0fr1nxs3Ox4fbPUGNDtE5XlGqvYT9tJJt0hkYS2ncXmiJtcW5DO2OLzffWKLQ410oITK3Ra7xN9Qjn1hhA==")

df1 = spark.read.option('header', 'true') \
                .option('delimiter', ',') \
                .csv(adls_path + '/Log.csv')

display(df1)

Using SQL

Can create a view based on a Spark DataFrame and execute SQL queries against it.

df1.createOrReplaceTempView("logdata") # Create a view from the DataFrame

sql_1=spark.sql("SELECT Operationname, count(Operationname) FROM logdata GROUP BY Operationname") # Execute SELECT statement against the view
sql_1.show()

# Or

df1.createOrReplaceTempView("logdata") # Create a view from the DataFrame

%%sql  # Jupyter notebook magic command. Specifying this magic command allows executing the next SQL query in SQL language.
SELECT Operationname, count(Operationname) FROM logdata GROUP BY Operationname

Loading from Spark Pool to Dedicated SQL Pool

An error occurs if the table already exists in the dedicated SQL pool.

%%Spark # Using the Spark magic command to use Scala language

import com.microsoft.spark.sqlanalytics.utils.Constants
import org.apache.spark.sql.SqlAnalyticsConnector._

# Data schema configuration
val dataSchema = StructType(Array(
    StructField("Id", IntegerType, true),
    StructField("Correlationid", StringType, true),
    StructField("Operationname", StringType, true),
    StructField("Status", StringType, true),
    StructField("Eventcategory", StringType, true),
    StructField("Level", StringType, true),
    StructField("Time", TimestampType, true),
    StructField("Subscription", StringType, true),
    StructField("Eventinitiatedby", StringType, true),
    StructField("Resourcetype", StringType, true),
    StructField("Resourcegroup", StringType, true)))

val df = spark.read.format("csv").option("header","true").schema(dataSchema).load("abfss://data@datalake2000.dfs.core.windows.net/raw/Log.csv") # Using Datalake Gen2 within Synapse Analytics so no access key needed

df.printSchema()

# Load the DataFrame into the dedicated SQL pool as a table
df.write.sqlanalytics("<DBName>.<Schema>.<TableName>", <TableType>)

Creating Spark Tables

Spark tables can be created in the Spark pool's metastore.

Used as temporary tables for analysis. Disappears when the Spark pool is stopped.

The advantage is that Spark tables can be shared and used in the serverless SQL pool.

Note: The Datetime type cannot be used in Spark tables.

%%spark
val df = spark.read.sqlanalytics("newpool.dbo.logdata")  # Create DataFrame based on dedicated SQL pool table
df.write.mode("overwrite").saveAsTable("logdatainternal") # Write as Spark table (overwrite mode)

// Then we can reference the table via SQL commands

%%sql
SELECT * FROM logdatainternal # Execute SQL query against Spark table

Can also create a Spark database and create Spark tables within it:

%%sql
CREATE DATABASE internaldb
CREATE TABLE internaldb.customer(Id int,name varchar(200)) USING Parquet

%%sql
INSERT INTO internaldb.customer VALUES(1,'UserA')

%%sql
SELECT * FROM internaldb.customer

// If you want to load data from the log.csv file and then save to a table
%%pyspark
df = spark.read.load('abfss://data@datalake2000.dfs.core.windows.net/raw/Log.csv', format='csv'
, header=True
)
df.write.mode("overwrite").saveAsTable("internaldb.logdatanew")

%%sql
SELECT * FROM internaldb.logdatanew

Azure Databricks

Databricks is a cloud service. Available on both AWS and Azure.

Create a Databricks cluster as a workspace.

The cluster has Spark engine and other components installed.

  • Interactive Cluster

    • Can perform interactive data analysis using notebooks.
    • Has Standard Cluster and High Concurrency Cluster

  • Job Cluster

    • Pass and execute jobs.
    • Terminated when the job completes.

  • Standard Cluster

    • Impact of failures is not isolated from other users
    • Suitable for running single workloads
    • Supports Python, R, Scala, SQL
    • Can be automatically deleted after a period of inactivity
    • Can automatically scale out (increase the number of worker nodes)

  • High Concurrency Cluster

    • Fault isolation
    • Suitable for running workloads with multiple users
    • Supports Python, R, SQL
    • Supports table access control. Access control can be applied to Python and SQL.

  • Single Node

    • Only one node.
    • One node serves as both driver node and worker node.

Building a Databricks Environment

  • Create Azure Databricks workspace
  • Enter the Azure Databricks workspace and create a Databricks cluster
  • Notebooks can be created on the cluster.

Pricing

  • Billed based on Databricks Units (DBUs) and number of VMs.
  • When a task is run on a new cluster, the task is treated as a Data Engineering (task) workload and task workload pricing applies.
  • When a task is run on an existing all-purpose cluster, the task is treated as a Data Analytics (all-purpose) workload and all-purpose workload pricing applies.

Databricks File System

  • Abstract layer on top of object storage
  • Can interact with object storage through this
  • Files are maintained even if the cluster is deleted
  • Default storage location is called DBFS root
  • The following DBFS roots exist:
    • /FileStore: Place for imported files and libraries
    • /databricks-datasets: For sample public datasets
    • /user/hive/warehouse: Data and metadata for Hive tables
  • %fs ls can list files within DBFS
  • %fs is the magic command for DBFS

Creating Graphs

Can easily create graphs from DataFrames in Azure Databricks notebooks.

https://learn.microsoft.com/en-us/azure/databricks/visualizations/

Cluster Pinning

https://learn.microsoft.com/en-us/azure/databricks/clusters/clusters-manage#--pin-a-cluster

  • Clusters are completely deleted 30 days after termination.
  • Clusters can be pinned to maintain the configuration of an all-purpose cluster even after more than 30 days after termination.

Writing Structured Streaming to Azure Synapse

https://learn.microsoft.com/en-us/azure/databricks/getting-started/streaming

https://learn.microsoft.com/en-us/azure/databricks/structured-streaming/synapse

  • The first reference site explains what structured streaming is.
  • Streaming data received within Azure Databricks workspace is being written to Databricks' in-memory table.
  • The second site shows how to write streaming data received by some method to Azure Synapse Analytics SQL tables.
# Set up the Blob storage account access key in the notebook session conf.
spark.conf.set(
  "fs.azure.account.key.<your-storage-account-name>.dfs.core.windows.net",
  "<your-storage-account-access-key>")

# Prepare streaming source; this could be Kafka or a simple rate stream.
df = spark.readStream \
  .format("rate") \
  .option("rowsPerSecond", "100000") \
  .option("numPartitions", "16") \
  .load()

# Apply some transformations to the data then use
# Structured Streaming API to continuously write the data to a table in Azure Synapse.

df.writeStream \
  .format("com.databricks.spark.sqldw") \
  .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
  .option("tempDir", "abfss://<your-container-name>@<your-storage-account-name>.dfs.core.windows.net/<your-directory-name>") \
  .option("forwardSparkAzureStorageCredentials", "true") \
  .option("dbTable", "<your-table-name>") \
  .option("checkpointLocation", "/tmp_checkpoint_location") \
  .start()

Accessing Azure Data Lake Storage Using Azure Active Directory Credential Passthrough

https://learn.microsoft.com/en-us/azure/databricks/data-governance/credential-passthrough/adls-passthrough

  • Can automatically authenticate from an Azure Databricks cluster to Azure Data Lake Storage Gen2 (ADLS Gen2) using the same Azure Active Directory (Azure AD) identity used to log in to Azure Databricks.
  • When Azure Data Lake Storage credential passthrough is enabled on a cluster, commands running on that cluster can read and write data in Azure Data Lake Storage without configuring service principal credentials to access storage.
  • Azure Data Lake Storage credential passthrough is only supported for Azure Data Lake Storage Gen1 and Gen2.
  • The Azure Databricks workspace must be on the Premium plan.

Best Practices for File Ingestion

  • Ingest files as-is and store in a RAW zone. Then drop unnecessary data and store in a Filtered Zone. Finally, perform conversion/integration and store in a Curated zone.
  • Folder hierarchy should be: /<zone name>/<data source name>/YYYY/MM/DD/<file name>
  • Use compression such as Parquet.
  • Split files so they can be assigned to multiple compute nodes.

Azure Monitor

  • Can create action groups for metrics to send email notifications when specific conditions are met.
  • Can set alerts for metrics to notify of anomalies.

Slowly Changing Dimensions

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema#slowly-changing-dimensions

https://en.wikipedia.org/wiki/Slowly_changing_dimension

  • SCD (Slowly Changing Dimensions)
  • Examples include city names, product names, tax rates, etc.

Type 0: No Changes

  • Attributes are never changed and have permanent values

Type 1: Overwrite

  • Old data is overwritten by new data, so historical data is not tracked
  • Disadvantage: History of changes is not retained.
  • Advantage: Easy to maintain.

Before Change

Customer CodeCustomer NameCustomer HQ Prefecture
101Yamashita ConstructionSaitama

After Change (Customer HQ Prefecture changed)

Customer CodeCustomer NameCustomer HQ Prefecture
101Yamashita ConstructionTokyo

Type 2: Add New Row

  • Uses surrogate keys (record start/end dates) to track historical data.
  • Unlimited history is stored.
  • Also uses version numbers or flags.
Customer CodeCustomer NameCustomer HQ PrefectureStart DateEnd Date
101Yamashita ConstructionSaitama2000-01-01T00:00:002004-12-22T00:00:00
101Yamashita ConstructionTokyo2004-12-22T00:00:00NULL

Type 3: Add New Attribute

  • Tracks changes by adding columns.
  • Retains limited history.
  • "Previous" instead of "Original" is also OK.
Customer CodeCustomer NameCustomer HQ Prefecture_OriginalChange DateCustomer HQ Prefecture_Current
101Yamashita ConstructionSaitama2004-12-22T00:00:00Tokyo

Type 4: Add History Table

  • One table holds current data.
  • An additional table holds change records.

Customer Table

Customer CodeCustomer NameCustomer HQ Prefecture
101Yamashita ConstructionTokyo

Customer_History Table

Customer CodeCustomer NameCustomer HQ PrefectureStart Date
101Yamashita ConstructionSaitama2000-01-01T00:00:00
101Yamashita ConstructionTokyo2004-12-22T00:00:00

Type 5

  • Combination of Type 1 and Type 4
  • Needs detailed verification

Type 6: Hybrid Approach

  • Combination of Type 1, 2, and 3 approaches

Type 7: Both Surrogate and Natural Keys in Fact Table

  • Needs detailed verification

File Formats

Parquet Files

https://learn.microsoft.com/en-us/azure/databricks/external-data/parquet

  • Optimized for large-scale reads for analytical workloads.

Avro Files

https://learn.microsoft.com/en-us/azure/databricks/external-data/avro

  • Optimized for large-scale writes for transactional workloads.

Azure Information Protection

https://learn.microsoft.com/en-us/azure/information-protection/what-is-information-protection

  • A cloud-based encryption service that protects files and emails using Microsoft 365 authentication features

Choosing Stream Processing Technology in Azure

https://learn.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/stream-processing#general-capabilities

  • For developing streaming processing in Java, use Azure Databricks or Spark.
← Back to articles