Book Image

ETL with Azure Cookbook

By : Christian Cote, Matija Lah, Madina Saitakhmetova
Book Image

ETL with Azure Cookbook

By: Christian Cote, Matija Lah, Madina Saitakhmetova

Overview of this book

ETL is one of the most common and tedious procedures for moving and processing data from one database to another. With the help of this book, you will be able to speed up the process by designing effective ETL solutions using the Azure services available for handling and transforming any data to suit your requirements. With this cookbook, you’ll become well versed in all the features of SQL Server Integration Services (SSIS) to perform data migration and ETL tasks that integrate with Azure. You’ll learn how to transform data in Azure and understand how legacy systems perform ETL on-premises using SSIS. Later chapters will get you up to speed with connecting and retrieving data from SQL Server 2019 Big Data Clusters, and even show you how to extend and customize the SSIS toolbox using custom-developed tasks and transforms. This ETL book also contains practical recipes for moving and transforming data with Azure services, such as Data Factory and Azure Databricks, and lets you explore various options for migrating SSIS packages to Azure. Toward the end, you’ll find out how to profile data in the cloud and automate service creation with Business Intelligence Markup Language (BIML). By the end of this book, you’ll have developed the skills you need to create and automate ETL solutions on-premises as well as in Azure.

Related Content you might be interested in

Current Title:

Small book image
ETL with Azure Cookbook
Christian Cote | Matija Lah | Madina Saitakhmetova
Sep, 2020 | 446 pages
Small book image
SQL Server 2017 Integration Services Cookbook
Dejan Sarka | Christo Olivier | Samuel Lester | David Peter Hansen | Christian Cote | Matija Lah
Jun, 2017 | 558 pages
Small book image
Hands-On Data Warehousing with Azure Data Factory
Michelle Gutzait | Christian Cote | Giuseppe Ciaburro
May, 2018 | 284 pages
Small book image
Azure Data Factory Cookbook
Dmitry Anoshin | Xenia Ireton | Dmitry Foshin | Roman Storchak
Dec, 2020 | 382 pages
Table of Contents (12 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Sections
Get in touch
Reviews
1
Chapter 1: Getting Started with Azure and SSIS 2019
Chapter 1: Getting Started with Azure and SSIS 2019
Technical requirements
Installing Microsoft SQL Server 2019 Integration Services
Installing SQL Server Management Studio
Installing SQL Server Data Tools
Installing Azure Data Studio
Creating an Azure subscription
Free Chapter
2
Chapter 2: Introducing ETL
Chapter 2: Introducing ETL
Creating a SQL Azure database
Connecting SQL Management Studio to Azure SQL
Creating a simple ETL package
Loading data before its transformation
3
Chapter 3: Creating and Using SQL Server 2019 Big Data Clusters
Chapter 3: Creating and Using SQL Server 2019 Big Data Clusters
Technical requirements
Creating a SQL Server 2019 Big Data Cluster
Stopping and starting Azure resources
Setting up a Big Data Cluster
Loading data into a Big Data Cluster
Extracting data from a Big Data Cluster
4
Chapter 4: Azure Data Integration
Chapter 4: Azure Data Integration
Setting up the Azure Feature Pack in SSIS
Setting up a Java Runtime Environment
Setting up an Azure storage account
Creating an on-demand Azure HDInsight cluster
Transforming data with Hive
Transforming data with Pig
5
Chapter 5: Extending SSIS with Custom Tasks and Transformations
Chapter 5: Extending SSIS with Custom Tasks and Transformations
Technical requirements
Designing a Custom Control Flow Task
Designing a Custom Data Flow Component
Deploying SSIS customizations
Upgrading a Custom Data Flow Component
6
Chapter 6: Azure Data Factory
Chapter 6: Azure Data Factory
Data factory creation
Copying data from the internet
Moving and transforming data
Triggering and monitoring our pipeline
7
Chapter 7: Azure Databricks
Chapter 7: Azure Databricks
Creating a Databricks workspace
Creating a cluster in our workspace
Connecting to an internet data source
Using Delta Lake
Writing in Azure SQL Server
8
Chapter 8: SSIS Migration Strategies
Chapter 8: SSIS Migration Strategies
Running SSIS packages in ADF
Rewriting an SSIS package using ADF
9
Chapter 9: Profiling data in Azure
Chapter 9: Profiling data in Azure
Technical requirements
Using SQL in Spark
Using Spark built-in functions
Using pandas profiling
10
Chapter 10: Manage SSIS and Azure Data Factory with Biml
Chapter 10: Manage SSIS and Azure Data Factory with Biml
Technical requirements
Installing prerequisites
Generating T-SQL select statements for all tables
Generating T-SQL to drop and create all indexes
Generating a basic SSIS package
Generating an SSIS package with Execute SQL Tasks
Using Convert SSIS package to Biml
Generating a mass change to stored procedures
Generating an SSIS package containing a Data Flow Task
Generating your first Azure Data Factory
Loading multiple files using Data Factory
11
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Chapter 1: Getting Started with Azure and SSIS 2019

In this cookbook, we are going to cover Extract, Transform, Load (ETL) development with Microsoft Azure. We will start with Microsoft SQL Server 2019 Integration Services (SSIS) and then gradually move on to data management capabilities in Azure. Throughout this cookbook we will use hands-on examples, which will not only provide you with genuine first-hand experience in SSIS development but also demonstrate how to design a working ETL solution or a module that could be built into an ETL solution.

Let's start by establishing your development environment. We are going to guide you through the installation of a new SQL Server instance, including all the components needed for on-premises as well as cloud-based SSIS solution development. You are also going to install the tools used in administering and maintaining SQL Server instances – either on-premises or in the cloud. You are also going to install the tools used in developing, testing, and deploying your SSIS solutions. You will finish this chapter by creating an Azure subscription, which will allow your on-premises development environment to extend into the cloud.

This chapter covers the following recipes that will help you get started with SSIS development on-premises as well as in the cloud:

  • Installing Microsoft SQL Server 2019 Integration Services
  • Installing SQL Server Management Studio
  • Installing SQL Server Data Tools
  • Installing Azure Data Studio
  • Creating an Azure subscription
Previous Section
End of Section 1
Next Section