Azure Data Lake Analytics (ADLA) offers extensive computation capabilities for the files stored in Azure Data Lake Store (ADLS) using U-SQL (Big data query language of ADLA). In addition to this, ADLA provides the capability to query data from external data sources from where it lives, without importing the data into ADLS.

U-SQL supports three different data sources – AZURESQLDB, AZURESQLDW, SQLSERVER.

In this blog, let us see how to connect to Azure SQL Data Warehouse and query it using external tables.

 

To be able to query Azure SQL data warehouse tables follow the steps.

  1. Create a database in ADLA
  2. Create a catalog credential
  3. Create AZURESQLDW data source
  4. Create external tables
  5. Query the tables

 

 Step 1 : Database in Azure Data Lake Analytics

Create a database in ADLA in U-SQL.

CREATE DATABASE ADLADatabase;

 

Step 2 : Catalog credential

A catalog credential is used for connecting to external data sources in ADLA Catalog. It contains the username and password to log into the external data source.

Create a credential in powershell using the script.

Specify the Data warehouse user name and password.

$password = ConvertTo-SecureString “SQLDWPassword” -AsPlainText -Force 

             $credentials = New-Object System.Management.Automation.PSCredential (“SQLDWUserName“, $password)

 

Create a catalog credential using the credential created above.

New-AzureRmDataLakeAnalyticsCatalogCredential -Account adlaAccountName -Credential $credentials -CredentialName credentialName -DatabaseHost vbinewelldw.database.windows.net -DatabaseName -Port portNumber

The catalog credential will now be listed in the ADLA data explorer.

 

Step 3 : Data source

Now in ADLA, create a data source referring to the Data Warehouse using U-SQL query. It contains the connection information and information about its capabilities to execute query expressions passed and translated from U-SQL to its local query engine.

Make use of the credential created above for authentication.

CREATE DATA SOURCE DWDataSource FROM AZURESQLDW WITH (

PROVIDER_STRING = “Database=DatabaseName;

Trusted_Connection=False;Encrypt=True”

, CREDENTIAL = credentialName

, REMOTABLE_TYPES = (bool, byte, sbyte, short, ushort, int, uint, long,

ulong, decimal, float, double, string, DateTime)

       );

 

The remotable types option lists sample built-in U-SQL types for which the U-SQL query will push supported expressions to external data source.

 

Step 4 : External table

Create external table in ADLA using U-SQL query for any table in DW.

CREATE EXTERNAL TABLE sampleET (

                                    columnList with data types

                        ) FROM DWDataSource LOCATION “dbo.tableName

 

The external table will be listed in the ADLA data explorer under Catalog.

Query SQL Data Warehouse tables from Data Lake Analytics in Microsoft Azure

 

Step 4 : Query the external table

Now the table contents can be queried using U-SQL query.

@tableResultSet = SELECT columns FROMsampleET;

 

Similarly, we can create multiple external tables. Now we can query tables in an external data source without loading the whole data into the ADLS.

Subscribe to our Newsletter

5600 Tennyson Pkwy
Suite 120
Plano TX 75024.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com