In Part One of this UDP data virtualization through PolyBase series, I introduced PolyBase and provided the prerequisites for this demo. In this final installment, you can dive into the demo.
Perform the following steps to conduct the demo:
To allow SQL Server to access the external text file placed on the Azure® blob, run the following query:
EXEC sp_configure @configname = 'hadoop connectivity', @configvalue = 7; Go
Now, you need to either use an existing database or create a new one. I created a new one named PolybaseTestDB for this demo.
After you have your DB, create a database master key and credentials by using the following query:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Welcome@3452'; USE PolybaseTestDB Go CREATE DATABASE SCOPED CREDENTIAL [Polybasecred] WITH IDENTITY = ‘Polybaseaccount’, SECRET=’qdfAVoBN+yr2rHN796gXFtpfhxKgAW3JDAtSPi0RnWcWgy9YRSwyGQR7BoINTHSgjiorUFTCmZpLTZspCbXesg==’;
In the preceding query, SECRET is the access key of the storage account you created previously. You can get it by performing the following steps:
Run the following query to create the external data source. After you create it, you can find the external data source under the DB external resources.
CREATE EXTERNAL DATA SOURCE AzureStorage WITH ( TYPE = HADOOP, LOCATION = 'wasbs://email@example.com', CREDENTIAL = [Polybasecred] );
Note: The Azure Storage Blob is an extension built on top of the Hadoop® Distributed
File System (HDFS) APIs, so the TYPE in the preceding query is
Run the following query:
CREATE EXTERNAL FILE FORMAT TextFileFormat WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR =',',USE_TYPE_DEFAULT = TRUE))
Note: Because it’s a comma-separated text file, the FIELD_TERMINATOR is
Run the following query:
CREATE EXTERNAL TABLE dbo.SQLPolybaseTable ( [Name] varchar(500), [Subject] varchar(500), [Education] varchar(500) ) WITH ( LOCATION='/Data.txt', This is the name of the file uploaded to the container. DATA_SOURCE=AzureStorage, The Data Source created in previous steps. FILE_FORMAT=TextFileFormat The File Format Name created in previous steps. );
Query the external table as you would any other table on your SQL Server. The results validate that PolyBase helps us to fetch external data. Run the following query:
Select * from <Tablename>
In future posts, I plan to explore in-depth the following concepts:
Also, because I encountered a couple of issues during this demo, I’d like to consolidate and present those experiences in an upcoming blog.
Use the Feedback tab to make any comments or ask questions. You can also click Let’s Talk to start the conversation.