This document describes how to connect to Microsoft Azure SQL Data Warehouse from Denodo.
The Azure SQL Data Warehouse is Transact-SQL compatible, i.e. all mechanisms applicable to SQL Server are applicable to Azure SQL Data Warehouse.
Accessing SQL Data Warehouse from Denodo is a standardized JDBC connection. Therefore, the Microsoft JDBC driver delivered with Denodo can be used.
- The Supported Database adapters are Microsoft SQL Server 20**driver. (eg.2016,2014).
- The Microsoft JDBC driver is delivered starting from Denodo 7.0.
- The Microsoft JDBC driver is delivered with Denodo 6.0 from update 20170515. For prior updates of Denodo 6.0, the Microsoft SQL Server driver can be downloaded from the Microsoft Website here.
- Since Denodo update denodo-vdp-v70-update-20190312 the database adapter for Azure SQL Data Warehouse is available. The Microsoft JDBC driver is set automatically to mssql-jbc-7.x
- Get the Connection String to the Azure SQL Data Warehouse database from the Azure Dashboard:
- Note that you might need to provide a firewall rule in Azure which allows your client to access the cloud instance of Azure SQL Data Warehouse.
In order to create a JDBC data source in Denodo follow these steps.
- From the design studio, create a new JDBC data source by navigating to “File > New > Data source > JDBC”. This will open the wizard to create a connection to a data source with a JDBC driver.
- Fill all the required fields.
- Name: Name of the new data source.
- Database adapter:
- Denodo versions prior to denodo-vdp-v70-update-20190312: select Microsoft SQL Server 2016 (MS Driver).
- Denodo versions from denodo-vdp-v70-update-20190312 and Denodo 8: select Azure SQL Data Warehouse.
- Login and Password: Credentials to access the database.
- Click on “Test Connection” to make sure connection is successful.
Example prior to denodo-vdp-v70-update-20190312
Example for Denodo 8
The specific Database URI can be found in the Azure database connection string settings. An example JDBC URI connection string can be found here.
- Introspect the data source to create base views.
Virtual DataPort Administration Guide: JDBC Sources
Virtual DataPort Administration Guide: Supported JDBC Data Sources
Azure SQL Data Warehouse