Post
Ask Question

Query Unstructured Data From SQL Server Using Polybase

Scope

The following article demonstrates how unstructured data and relational data can be queried, joined and processed in a single query using Polybase, a new feature in SQL Server 2016.

Pre-Requisites

Introduction

Traditionally, Big Data is processed using Apache Hadoop which is totally fine. But what if the result of this needs to be linked to the traditional Relation Database? For example, assume that from the analysis of tons of application logs, marketing needs to contact some customs that faced problems in an application following a failure in the application.

app
 

 

This problem is solved with PolyBase. PolyBase allows you to use Transact-SQL (T-SQL) statements to access data stored in Hadoop or Azure Blob Storage and query it in an ad-hoc fashion. It also lets you query semi-structured data and join the results with relational data sets stored in SQL Server. PolyBase is optimized for data warehousing workloads and intended for analytical query scenarios.

Moreover, the user querying from T-SQL does not have to worry about Map-Reduce jobs processing the unstructured data, all this processing is transparent to the user.

user

 

 

Installing PolyBase

In this article, SQL Server 2016 RC0 is used and it is used in an Azure VM that is already pre-configured and can be provisioned at any time.

 

time
 

 

1. Ensure all the required software are installed:

2. Install PolyBase

Go to control panel > Add/Remove programs > Microsoft SQL Server 2016 and click on Uninstall/ change. Then click on Add and browse to the setup of SQL Server 2016 (64-bit).

 
On an Azure VM, the set-up is found at C:\SQLServer_13.0_Full
 

 

vm
vm

 

 

3. Add feature to the existing SQL Server installation

feature

 

Then install Polybase Query Service for Extarnal Data

Data

 

4. Verify installation

a. In services.msc, the 2 services below should be running

services

 

 b. Verify Polybase installation in SSMS

Run the following query to view the status of the Polybase installation.

1 = Installed

0= Not installed

services

 

Configure Polybase to connect with an existing Big Data Solution

1. Create a Master Key Encryption

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password.

  1. CREATEMASTERKEYENCRYPTIONBYPASSWORD=’MyP@ssword31’;  

2. Create a Database-Scoped Credential

The credential is used by the database to access to the external location anytime the database is performing an operation that requires access.

  1. CREATEDATABASESCOPEDCREDENTIAL HadoopUser1  
  2.   
  3. WITHIDENTITY=‘xxxx’,  
  4.   
  5. Secret=‘xxxxxx’;  
  6.   
  7. Identity= Storage Account Name  
  8.   
  9. Secret = Storage Account Key  

 
3. Reconfigure Hadoop Connectivity

Reconfigure updates the currently configured value (the config_value column in the sp_configure result set) of a configuration option changed with the sp_configure system stored procedure.

In this example, it will change the global configuration settings for PolyBase Hadoop and Azure blob storage connectivity.

Below are the connections that can be used at the time of writing:

  • Option 0: Disable Hadoop connectivity
  • Option 1: Hortonworks HDP 1.3 on Windows Server
  • Option 1: Azure blob storage (WASB[S])
  • Option 2: Hortonworks HDP 1.3 on Linux
  • Option 3: Cloudera CDH 4.3 on Linux
  • Option 4: Hortonworks HDP 2.0 on Windows Server
  • Option 4: Azure blob storage (WASB[S])
  • Option 5: Hortonworks HDP 2.0 on Linux
  • Option 6: Cloudera 5.1, 5.2, 5.3, 5.4, and 5.5 on Linux
  • Option 7: Hortonworks 2.1, 2.2, and 2.3 on Linux
  • Option 7: Hortonworks 2.1, 2.2, and 2.3 on Windows Server
  • Option 7: Azure blob storage (WASB[S])

 
In this example, since Azure Blob Storage is being used, option will be used.

  1. Sp_configure‘hadoop connectivity’, 1;  
  2.   
  3. reconfigure;  

The next step is to restart the MSSQLSERVER service. If this is not done, several errors will be encountered during the next steps.

When this service is being restarted, the Polybase services will also be restarted.

services

 
4. Create an External Data Source

This defines from which source to fetch and process the data.

  1. CREATEEXTERNALDATASOURCE AzureDs1  
  2. WITH(TYPE = HADOOP,  
  3.     --Specifiy the container name and account name LOCATION = ‘wasbs: //[email protected]/’,  
  4.     --Specify the credential that we created earlier CREDENTIAL = HadoopUser1);  
  5. X = Hadoop cluster name  
  6. Z = Storage account name  

 
5. Create a File Format

This is a prerequisite for creating the actual layout of the data in the external table and defines the structure of the file to be read.

  1. CREATEEXTERNALFILEFORMAT CommaFormat  
  2. WITH(FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS(FIELD_TERMINATOR = ’, ’));  

PolyBase supports these file formats:

  • Delimited text,
  • Hive RCFile, and
  • Hive ORC

 
6. Test: load and query sample data from Azure Blog Storage

  1. CREATEEXTERNALTABLE Hvac(  
  2.   
  3. Datevarchar(10),  
  4.   
  5. Timevarchar(10),  
  6.   
  7. TargetTemp smallint,  
  8.   
  9. ActualTemp smallint,  
  10.   
  11. SystemID smallint,  
  12.   
  13. SystemAge smallint,  
  14.   
  15. BuildingID smallint  
  16.   
  17. )  
  18.   
  19. WITH (  
  20.   
  21. --Set the file to be the HVAC sensor sample file  
  22.   
  23. LOCATION=’/HdiSamples/SensorSampleData/hvac/HVAC.csv’,  
  24.   
  25. DATA_SOURCE= AzureDs,  
  26.   
  27. FILE_FORMAT= CommaFormat,  
  28.   
  29. --We will allow the header to be rejected  
  30.   
  31. REJECT_TYPE=VALUE,  
  32.   
  33. REJECT_VALUE= 1  
  34.   
  35. );  

 

table

 

 

Example: Analyzing Application Logs

Assume someone has a business running and has been storing all his application logs.

Someday, the application got a severe error on one of the most critical areas and the management decides to contact all the customers that tries to access the Application at that time.

Since, the database down, the only way to track these customers were through the logs. But now, how to get the details of these customers?

This problem can be easily solved with Polybase.

The following example demonstrates the analytics and BI capabilities of joining both the relational and unstructured data using Polybase.

 

Relational Customer table

 

table

 

 

Application Logs on Azure Blob Store

logs

1. Create external table for the logs

  1. CREATEEXTERNALTABLE APPLICATION_LOGS(  
  2.   
  3. [LOG_DATE] varchar(50),  
  4.   
  5. [LOG_TIME] varchar(50),  
  6.   
  7. [CUSTOMER_ID] int,  
  8.   
  9. [PAGE] varchar(50),  
  10.   
  11. [ACTION] varchar(50),  
  12.   
  13. [STATUS] varchar(50)  
  14.   
  15. )  
  16.   
  17. WITH (  
  18.   
  19. --Set the file to be the HVAC sensor sample file  
  20.   
  21. LOCATION='/ApplicationLogSample.txt',  
  22.   
  23. DATA_SOURCE= AzureDs,  
  24.   
  25. FILE_FORMAT= CommaFormat,  
  26.   
  27. --We will allow the header to be rejected  
  28.   
  29. REJECT_TYPE=VALUE,  
  30.   
  31. REJECT_VALUE= 1  
  32.   
  33. );  

 

table

 

 
2. View all customers that got an error on the EPayment Page and their contact details

  1. select  
  2.   
  3. CUST.[FIRST_NAME],  
  4.   
  5. CUST.LAST_NAME,  
  6.   
  7. CUST.PHONE,  
  8.   
  9. COUNT(1)  
  10.   
  11. from APPLICATION_LOGS APPLO  
  12.   
  13. innerjoin [dbo].[CUSTOMER] CUST  
  14.   
  15. ON CUST.[ID] = APPLO.CUSTOMER_ID  
  16.   
  17. WHERE APPLO.STATUS='ERROR'  
  18.   
  19. ANDPAGE='EPAYMENT'  
  20.   
  21. GROUPBY CUST.[FIRST_NAME],  
  22.   
  23. CUST.LAST_NAME,  
  24.   
  25. CUST.PHONE  
  26.   
  27. HAVINGCOUNT(1)>3  

 

table

 

 
3. Fetch the features that got more than 10 errors

  1. select  
  2.   
  3. count(1),  
  4.   
  5. page  
  6.   
  7. from APPLICATION_LOGS  
  8.   
  9. groupbypage  
  10.   
  11. havingcount(1)> 10  

error

 
4. Migrate data from the Blob storage to a relational table.

  1. SELECT*INTO  
  2.   
  3. RELATIONAL_LOGS  
  4.   
  5. FROM  
  6.   
  7. APPLICATION_LOGS;  

table

 
5. Transparent BI experience with both the unstructured and relational data using PowerBI Desktop.

a. Connect to SQL Server Database

 

SQL Server Database

 

b. Data can be imported both from the structured and external unstructured tables

 

tables

 

c. Relationships can also be defined between the tables

tables

 
d. Creates Dashboard using any of the data seamlessly

Dashboard

References


Similar Articles