All about SQLServer: Query Azure SQL database tables using U-SQL

In my previous post, I wrote about U-SQL basic example. In this post, let us see how we can query azure sql database tables using U-SQL.

First step is to create U-SQL database.Similar to other database systems and SQL-inspired Big Data processing systems such as Hive, U-SQL uses the concept of a database to group related objects together.

CREATE DATABASE IF NOT EXISTS TestUSQLDB;

I have created a Azure SQL database and server that resides in same location where azure data lake analytics is created.

Below is the azure sql server firewall setting - Allow access to Azure services - ON

Create catalog credential:

CREATE/ALTER/DROP CREDENTIAL statements are now deprecated, we can use below powershell command to create credential.
In the below command, replace
-AccountName "Azure data lake analytics account name"
-DatabaseName "above created U-SQL database"
-CredentialName "Credential name"
-Credential (Get-Credential) - Get-Credential will prompt for Username and password.
Provide Azure SQL database credential.
-DatabaseHost "Azure sql database server"

-Port Portnumber

New-AzureRmDataLakeAnalyticsCatalogCredential -AccountName "msazdla" -DatabaseName "TestUSQLDB" -CredentialName "AzureSQLDBCredential" -Credential (Get-Credential) -DatabaseHost "azsqldbserv.database.windows.net" -Port 1433;

Create Data source:

USE DATABASE TestUSQLDB;



CREATE DATA SOURCE AzureSQLDBDataSource
FROM AZURESQLDB
WITH
(
    PROVIDER_STRING = "Database=azsqldb;Trusted_Connection=False;Encrypt=True",
    CREDENTIAL = AzureSQLDBCredential,
    REMOTABLE_TYPES = (bool, byte, sbyte, short, ushort, int, uint, long, ulong, decimal, float, double, string, DateTime)
);

In the Azure data lake analytics -> Data Explorer -> we can see the credential & data source created.

Method 1 : U-SQL using Execute to query Azure sql database

@results =
    SELECT *
    FROM EXTERNAL AzureSQLDBDataSource EXECUTE 
        @"SELECT [ProductModelID]
      ,[Name]
      ,[CatalogDescription]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [SalesLT].[ProductModel] WITH (NOLOCK)";

OUTPUT @results
TO "/myoutput/Query1.csv"
USING Outputters.Csv(outputHeader: true);

Method 2 : U-SQL using Location to query Azure sql database

@results =
    SELECT *
    FROM EXTERNAL MyAzureSQLDBDataSource LOCATION "[SalesLT].[ProductModel]";

OUTPUT @results
TO "/myoutput/Query2.csv"
USING Outputters.Csv(outputHeader: true);