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);




Above query outputs are stored in Azure data lake store folder:






Reference: 



See Also: 

No comments: