In this article, we will explore how to access data from an Azure SQL database using Azure Data Lake Analytics. Azure Data Lake Analytics is a powerful tool that allows us to process and analyze large amounts of data stored in Azure Data Lake Storage. By combining the capabilities of Azure Data Lake Analytics and Azure SQL Database, we can leverage the structured data stored in the database and perform complex analytics tasks.

Initial Setup

Before we begin, we need to have a few prerequisites in place. Firstly, we need to set up an Azure Data Lake Analytics account and create a database on it. Additionally, we require an Azure SQL database with some sample data. For this exercise, we will use a database named “azure-sql-server-001” with a table named “SalesLT.Address”.

Creating Database Credential

Once the setup is complete, we can proceed with creating a database credential. This credential will allow Azure Data Lake Analytics to access the Azure SQL database. We can use Azure PowerShell to execute the necessary commands. The command “New-AzureRnDataLakeAnalyticsCatalogCredential” is used to create the credential, and it requires parameters such as the account name, database name, credential name, database host, and port.

Creating Data Source

After creating the credential, we need to create a data source. The data source specifies the location of the Azure SQL database that we want to access. We can use the “CREATE DATA SOURCE” U-SQL command to create the data source. The command requires the name of the data source, the provider string (which is the name of the SQL Azure Database hosting the table), and the credential name.

Creating an External Table

With the data source in place, we can now create an external table. An external table allows us to access the Azure SQL database as a data source. We can use the “CREATE EXTERNAL TABLE” command to create the table. This command requires the column definition, the data types compatible with U-SQL, the name of the data source, and the location of the table in the Azure SQL database.

Accessing the Table with U-SQL

Once the external table is created, we can access it using a U-SQL job. We can write a U-SQL script that reads data from the table and performs any necessary transformations or analytics tasks. The script can then write the results to an output file on the Azure Data Lake Storage account.

Conclusion

In this article, we have seen how to access data from an Azure SQL database using Azure Data Lake Analytics. By combining the power of these two services, we can leverage structured data stored in Azure SQL database and perform complex analytics tasks using Azure Data Lake Analytics. This allows us to gain valuable insights from our data and make informed decisions.