Connecting Azure Data Warehouse to Data Lake

Question

From Azure Data Warehouse can we connect to Azure Catalog tables or Lake Store files as external tables?

I want to be able to run SQL queries against the Azure Data Warehouse, but want to get the data from the Data Lake instead.

Answer 1

Yes you can connect to files in Azure Data Lake Store (ADLS) as external tables using Polybase. This was announced in Feb 2017 here.

As per the tutorial, complete the following steps:

1. Create a scoped database credential
2. Create an external datasource, pointing at your datalake using the credential created above
3. Create an external file format (eg comma, pipe-delimited etc) for your table

4. Create an external table, using the external datasource, eg

   CREATE EXTERNAL TABLE dbo.yourTable (
       col1    INT NOT NULL,
       col2    VARCHAR(20) NOT NULL,
       col3    DATETIME NOT NULL
   )
   WITH (
       LOCATION = '/someADLSFolder/',
       DATA_SOURCE = yourDataSource,
       FILE_FORMAT = yourFileformat,
       REJECT_TYPE = VALUE,
       REJECT_VALUE = 0
       );