This blog highlights how to load and query using PolyBase by authenticating via Azure Active Directory (AAD) pass-through to Azure Data Lake Storage Gen2. AAD pass-through authentication with PolyBase is much more secure and compliant where you no longer need CONTROL permissions on the data warehouse to initiate a load. You can now securely and immediately achieve high throughput data ingestion with only a few steps:
Create an external file format -
CREATE EXTERNAL FILE FORMAT CustomerFileFormat WITH ( FORMAT_TYPE = DelimitedText, FORMAT_OPTIONS (FIELD_TERMINATOR = ',') );
Note: Requires ALTER ANY EXTERNAL FILE FORMAT permission
Create an external data source -
CREATE EXTERNAL DATA SOURCE AADPassthrough_storage WITH ( TYPE=hadoop, LOCATION='abfss://firstname.lastname@example.org' );
Note: Requires ALTER ANY EXTERNAL DATA SOURCE
Create an external table for the load -
CREATE EXTERNAL TABLE [dbo].[customer_ext] ( NAME varchar(20) not null, AGE int ) WITH ( LOCATION='/customer/', DATA_SOURCE = AADPassthrough_storage, FILE_FORMAT = CustomerFileFormat );
Note: Requires CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL DATA SOURCE, and ALTER ANY EXTERNAL FILE FORMAT.
No database scoped credential was required to to set up customer_ext external table where you can now load and query from your ADLS Gen2 storage account.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.