12 Integrating Data Factory with SQL Database

 

This chapter covers:

  • Importing data into Azure SQL Database with external data sources
  • Configuring cross-database queries in Azure SQL Database
  • Importing file data into Azure SQL Database with Azure Data Factory
  • Configuring version control with Azure Data Factory

In the last chapter, you learned about creating and configuring Azure SQL Database (SQLDB). A relational database like SQLDB provides a very accessible endpoint for queries into both the serving layer and speed layer of a Lambda architecture analytics system. In chapter 6, you saw the real-time part of the processing workflow, with Stream Analytics calculations that flowed into SQLDB. Chapter 10 demonstrated using Azure Data Factory (ADF) to automate the batch-processing part using Azure Data Lake Analytics (ADLA). SQLDB sits at the center of this web of data flows, and forms the primary service of the Serving layer.

Figure 12.1. Lambda architecture with Azure PaaS services
Lambda Architecture

In this chapter, you’ll learn how to move data in Azure services into SQLDB. This includes the outputs of batch processing with ADLA, data loaded in Blob storage, and data in other SQLDBs. Providing data via an RDBMS gives more users access, with a wider range of tools, than data files only.

12.1  Before you begin

12.2  Importing data with external data sources

12.2.1  Creating a database scoped credential

12.2.2  Creating an external data source

12.2.3  Creating an external table

12.2.4  Importing blob files

12.3  Importing file data with ADF

12.3.1  Authenticating between ADF and SQLDB

12.3.2  Creating SQL Database linked service

12.3.3  Creating datasets

12.3.4  Creating Copy activity and pipeline

12.4  Version control of ADF configuration files

12.4.1  Git version control

12.4.2  Using ADF with Git version control

12.5  Exercises

12.5.1  Exercise 1

12.5.2  Exercise 2

12.5.3  Exercise 3