We are now part of Eviden, discover more...

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.


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.

PROVIDER_STRING = “Database=DatabaseName;
, 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.

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.

Learn more about Visual BI’s Microsoft Azure offerings here.

Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!