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

What this book covers

Chapter 1, Getting Started with Azure and SSIS 2019, describes, in step-by-step fashion, how to set up SQL Server 2019 to deploy the features that are used throughout the book. It also covers how to set up an Azure subscription.

Chapter 2, Introducing ETL, explains why ETL is needed, where it comes from, and the differences between ETL and ELT.

Chapter 3, Creating and Using SQL Server 2019 Big Data Clusters, discusses how to create, deploy, connect to, and retrieve data from SQL Server 2019 Big Data Clusters.

Chapter 4, Azure Data Integration, talks about the Azure Feature Pack, which allows SSIS to integrate Azure data from blob storage and HDInsight clusters.

Chapter 5, Extending SSIS with Custom Tasks and Transformations, talks about extending and customizing the built-in toolset with custom-developed control flow tasks and data flow transformations.

Chapter 6, Azure Data Factory, talks about using Azure Data Factory to move and transform data in the cloud.

Chapter 7, Azure Databricks, talks about using Azure Databricks to transform data in the cloud.

Chapter 8, SSIS cloud Migration Strategies, talks about scaling out SSIS packages to Azure.

Chapter 9, Profiling data in Azure, contains various recipes that show you how to perform data profiling in Azure Databricks.

Chapter 10, Manage SSIS and Azure Data Factory with BIML, contains various recipes that demonstrate how to manage SSIS and Azure Data Factory with BIML using metadata.

Previous Section
Next Section