SQL Server 2016 – PolyBase Objects In SSMS

I recently installed CTP 3.1 for SQL Server 2016, and noticed a few new entries in SSMS (although I didn’t install PolyBase before, so these features may have been included in an earlier CTP).
These items have to do with the PolyBase Query Service, which allows a user to query a Hadoop or Azure Blob dataset using SQL.
The first thing I noticed was that three new databases are installed with the instance: DWConfiguration, DWDiagnostics and DWQueue. They are installed as User databases, but they’re just for use by PolyBase.
Also, in SSMS, expanding the tree for a User database under Tables reveals a new entry for External Tables. Right-clicking and selecting “New External Table…” opens a T-SQL template. An external table can be used to reference the Hadoop data outside of SQL Server. You can query the external table with T-SQL using SELECT FROM EXTERNAL TABLE or you can also import the external data into the table using SELECT INTO FROM EXTERNAL TABLE.
MSDN – External Table
Going down the User database tree, there is a new entry for External Resources, and expanding this entry reveals entries for Data Sources and File Formats. Right-clicking either entry will give us the option to create a new object, which will open a T-SQL template. When creating an external table we’ll need to reference a data source and file format for our table.
As you would expect, the Data Source describes the type of the external data source as well as how to access the data, like an address and port number for a Hadoop installation, as well as any sort of credentials needed to connect.
MSDN – External Data Source
The File Format specifies information we need to interpret the external data. We can specify if we’re using delimited files, or a Hive format (Hive is the Apache Hadoop Data Warehousing component). If we’re using delimited text files, we can specify the column delimiter, string delimiter, date format and so on.
MSDN – External File Format
These External Table features are also used in Azure Elastic queries, where you may want to query across multiple databases.
Just a short look around the new PolyBase entries for SSMS, I would like to dig a little more deeply into the setup and use of the PolyBase features, since this is one of the major features being added to SQL Server 2016.

Links:
PolyBase
Getting Started With Polybase

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: