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.
Table of Contents (12 chapters)

Creating an Azure subscription

One of the objectives of this cookbook is to show you how to integrate your on-premises data warehousing solutions with cloud-based data storage, maintenance, and consumption capabilities of the Microsoft data platform. One of the prerequisites needed to follow this objective is an active Azure subscription.

Azure provides a variety of services and features that you can use to host your data off-premises, extend your own on-premises and cloud-based data management solutions with cloud-based services provided by Microsoft and Microsoft Partner Network, and even host your own solutions as cloud-based services to be used by your clients.

If you already have access to a valid Azure subscription, you are welcome to use it for all the relevant recipes in this book; otherwise, you can use this recipe to create a new one.

An Azure free subscription will provide you with access to the services for 12 months – free of charge. A number of services are even provided at no cost independently of the subscription period.

The free subscription includes a credit of 200 USD to be used for paid services for a period of 30 days from the subscription activation. This period should be enough for you to run all of the cloud-oriented recipes provided in this cookbook.

Important note

Certain paid services in Azure incur costs while online, even if they are not actually being used. This will be pointed out in each recipe dealing with such services, and the instructions on how to either turn a feature off or remove it completely will be provided.

However, by using Azure, you take full responsibility for all the costs involved in using the services provided therein.

Getting ready

Azure is an online service, located at https://portal.azure.com/. Generally, you would use an internet browser to access it; however, on the latest versions of the Windows operating system, you can also use the Azure portal application. The instructions in this cookbook use an internet browser to access the Azure portal.

How to do it…

Use your favorite internet browser and navigate to the Azure portal website:

  1. On the Azure Portal app landing page, click Continue to Azure Portal website.
  2. On the Sign in page, do not sign in with your existing Microsoft Azure credentials, but instead, click Create one! to create a new account.

    On the next screen, provide a valid email address for the account. Use the address of an existing email account that you have access to and is not currently associated with an existing Azure subscription.

    Alternatively, you can use a phone number instead of an email. If you prefer to use a phone number, click Use a phone number instead, and the form will allow you to enter it. Again, use an existing phone number of a smart device that you have access to and that is not currently associated with an Azure account.

    You can even create a new email account. If you prefer to use a new email account, click Get a new email address, and the form will allow you to create a new outlook.com or hotmail.com email account. When ready, click Next to continue.

  3. On the next screen, create a password for the account. It is recommended that you follow the standard recommendations on how to create a safe password.
  4. On the next screen, provide your country/region information and the date of your birth. When ready, click Next to continue.

    An automated confirmation email will be sent to the email address you used earlier, or a message will be sent to your phone if you used a phone number.

  5. Copy the code you were sent in the message from the Microsoft account team into the text box of the Verify email form. When ready, click Next to continue.
  6. On the next screen, you will be required to prove that you are a real person by entering the characters you recognize in the screenshot.

    If the characters are difficult to read, click New for a new image, or click Audio for the images to be read out through your audio device.

    When you have entered the correct characters, click Next to continue.

  7. After the account has been created, the Welcome to Azure web page will open, prompting you to start the tour. Click Maybe later to close the dialog.

    In a new free Azure account, none of the paid services can be accessed until the trial is activated. You do not have to activate it at this time; this can be done when you go through the recipes in the following chapters in which paid Azure services are required.

    You can close the internet browser. However, if you do want to activate the trial, continue on to Step 8.

  8. To start the free trial, click Start:
    Figure 1.25 – Activating the Microsoft Azure Free Trial

    Figure 1.25 – Activating the Microsoft Azure Free Trial

  9. Follow the instructions on the web page. You will have to provide some of your personal information, including an email address and a valid phone number.

    Important note

    If Azure is not available in your region, please contact Microsoft, and they will be able to assist you further.

    To complete the activation, your identity will be verified by phone using the method you select – via a text message or through a call from the service center.

    As part of the identity verification, you will also have to provide valid credit card details.

    Important note

    Credit card information will not be used unless you upgrade your free subscription to a pay-as-you-go subscription.

    Before you can complete the activation procedure, you also have to read and accept the subscription agreement.

    Important note

    We recommend that you familiarize yourself with the subscription agreement, the details of the offer, and the privacy statement before continuing.

  10. When ready, click Sign up to activate your Azure free trial.

The activation should complete within a few minutes.

How it works…

You have just created a free Azure subscription; it will not only allow you to complete most of the recipes in this cookbook but will also allow you to explore additional services that you can use for your personal or professional needs.

Important note

One of the purposes of this cookbook is to introduce SSIS development in the cloud, using more than just the basic features of SQL Server or Azure. Unfortunately, the free Azure subscription does not provide access to more advanced features (such as the Azure Kubernetes Service or Azure Databricks); therefore, in order to complete specific recipes, you will have to upgrade your account.

Each of those recipes will contain a special warning and provide the appropriate instructions on how to proceed.

There's more…

If you decide on using Azure for more than just learning about SSIS development, you will eventually have to upgrade your account. If you design applications or provide services that can also be hosted in Azure, you might even consider joining the Microsoft Partner Network yourself.