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.
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
Create Data source:
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
Method 2 : U-SQL using Location to query Azure sql database
Above query outputs are stored in Azure data lake store folder:
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:
No comments:
Post a Comment