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.
- Create a database in ADLA
- Create a catalog credential
- Create AZURESQLDW data source
- Create external tables
- 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.
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.
