#ploybase

New Post has been published on http://robertfertig.com/connect-sql-with-azure-blob-using-polybase/

Connect SQL with Azure Blob using PolyBase

Connect SQL with Azure Blob using PolyBase

In the last post I explained how to Install PolyBase in SQL Server 2016. This time I will show you how to Connect SQL with Azure Blob using Polybase.

Requirements:

SQL Server 2016 x64

PolyBase installed.

Azure Storage Account

Azure Storage Container

  Steps:

Connect to your SQL Server Instance using SQL Server Management Studio.

Open a new query to master database

Run the follow TSQL query  to enable hadoop connectivity:

use master go exec sp_configure ‘show advanced options’,1 go reconfigure with override go exec sp_configure ‘hadoop connectivity’,7 go reconfigure with override go

Restart SQL Server Database engine

Open a new query to database where do you want to define Azure Blob connection.

Create a master key using bellow query:

create master key encryption by password = ‘your_password’;

Create a database scoped credential

create database scoped credential PolybaseCredential with identity = ‘azure_storage_user’, secret = ‘azure_storage_key’;

Create an external data source

create external data source blob_storage with ( type = hadoop, location =’wasbs://[email protected]’, credential = PolybaseCredential );

Create an external file format

create external file format blob_format with ( format_type = delimitedtext, format_options (field_terminator =’|’) );

Create external table

create external table blob_test ( name varchar(100), date date, country varchar(50) ) with ( location=’/blob’, data_source = blob_storage, file_format = blob_format ) ;

Enable data insertion

sp_configure ‘allow polybase export’,1 reconfigure with override

Insert data

insert blob_test select ‘Robert’,getdate(),’Malta’

Select data