Best Practice for PolyBase Table Location - Use a Folder

Use Folders!  For production, create a new folder for each file type.  Then point your LOCATION= parameter to just the folder, and not the specific file.

Why?  

1) Add more files to the directory, and Polybase External table will automagically read them.
2) Do INSERTS and UPDATES from PolyBase back to your files in Hadoop.
    ( See PolyBase - Insert data into a Hadoop Hue Directory ,
             PolyBase - Insert data into new Hadoop Directory    ).
3) It's cleaner.


Here is a typical data folder in Hortonworks:

And here is the corresponding Create External Table:

       
  CREATE EXTERNAL TABLE [dbo].AWDW_CSV_Sales_Date (
     ModelName  nvarchar(200) NULL ,
     ShipDate  datetime      NULL ,
     ExtendedAmt  money         NULL ,
     OrderQty  smallint   NULL 
 )
 WITH (LOCATION='/user/hue/AWDW2012_SalesData',
    DATA_SOURCE = hdp23 ,
    FILE_FORMAT = CSVfile  
    );       
 

Use a web browser to verify the hadoop IPC port is accessible

A quick, simple method to verify that your Hadoop / hdfs / IPC port is accessible is to simply use a web browser.  Setting up PolyBase, you'll need to create an external data source. This is where you point to the Hadoop IPC port.  PolyBase is not known for its robust error messages, so just because you created a data sources successfully does not mean it will work.

Lets say your Hadoop hdfs port has this address:

                   hdfs://192.168.1.120:8020

Simply replace hdfs with http and try to connect with a browser. Like this:

                   http://192.168.1.120:8020/

You should get the message:

       It looks like you are making an HTTP request to a Hadoop 
       IPC port. This is not the correct port for the web 
       interface on this daemon.

If you don't get this message, you'll need to work out why your Hadoop system is not responding.

Command(s) completed successfully.

PolyBase does not validate your Hadoop Location when you create a new external data source. For example, we created our new, but invalid data source with the following command. One that has a totally invalid hdfs location value:

               CREATE EXTERNAL DATA SOURCE [invalid] 
               WITH ( 
                TYPE = HADOOP
                , LOCATION = N'hdfs://192.168.1.130:8020'
                , RESOURCE_MANAGER_LOCATION = N'192.168.1.130:8050'
                )
               GO

PolyBase simple returned this value:  Command(s) completed successfully. The point, is that just because you can create a Hadoop external data source does not mean that it is valid.

PolyBase Error ...app.MRAppMaster not found

On the HortonWorks community site, we noticed an unusual error relating to the PolyBase pushdown capability and the resulting error:

Error: Could not find or load main class org.apache.hadoop.mapreduce.v2.app.MRAppMaster
 

We have not seen this error, but wanted to include it in our listing of identified PolyBase errors. The user later noted that their solution required a change to the mapred-site.xml file, as follows:

       
 <property> <name>mapreduce.app-submission.cross-platform</name> <value>True</value> </property>
       

You can find the full user thread here:  Yarn ClassPath Value for Polybase Pushdown

PolyBase - user pdw_user

Whom or what is the Hadoop user pdw_user?

If you have started to use PolyBase to connect to your Hadoop installation, you may have noticed that the user name pdw_user shows up when you insert records back to Hadoop from PolyBase.  You find this value in the database DWConfiguration:

       
    SELECT * FROM [DWConfiguration].[dbo].[configuration_properties]
      where [key] = 'HadoopUserName';
       
 

Which returns:

id	key	value	default	protection	access	datatype
Hadoop	HadoopUserName	NULL	pdw_user	0	0	string

There is little documentation, so its best not to make a change to this, or other values in the tables.

Have PolyBase INSERT create a new Directory for you

You can use PolyBase to create a new directory for you when you plan on inserting records from SQL Server.

In our earlier example ( see PolyBase - Insert data into new Hadoop Directory) we first created a new Directory in Hue. To skip that step, all you need to do is to append the new directory name on the the location path when you create your new external table in PolyBase.

Location path to existing directory:

       
       WITH (LOCATION='/user/hue/AWDW2012_SalesData',
       
 

Location path to new directory to be created:

       
      WITH (LOCATION='/user/hue/AWDW2012_SalesData/AWDW_FileNameTest',
       
 

When you run the INSERT, PolyBase instructs Hue to create the new folder.

PolyBase - Insert data into a Hadoop Hue Directory

You can use PolyBase as an ETL tool to insert data into your Hadoop installation from SQL Server. For this example, we will insert data from the Adventure Works data warehouse into a new, empty Hue Directory on HortonWorks. Please Note: this example installs data into the Hadoop file repository. We are not inserting data into a Hive or HCat table.

Summary:

1. Hadoop: Create a new Directory in Hue **
2. PolyBase: Create a new External Table pointing to the Directory
3. Run your INSERT command

The Details:

With PolyBase, it inserts records into a directory (folder) - not a file. 

Using Hue, go to the file browser, and create a new Directory.

We named our new Directory: AWDW2012_SalesData.  (we are using an old Adventure Works data warehouse database for this example. No reason, it was just there.)

Navigate to the directory, and you'll see that it is empty.

Using the full directory path from Hue, create a new external table.  

(see Have PolyBase INSERT create a new Directory for you on how you can use the Create External Table DDL to instruct Hue to create a new Directory for you). 

DataTypes:  For our external table, our data types match exactly the data types in our SQL Server data warehouse. 

       
  CREATE EXTERNAL TABLE [dbo].AWDW_CSV_Sales_Date (
  ModelName  nvarchar(200) NULL ,
  ShipDate  datetime      NULL ,
  ExtendedAmt  money         NULL ,
  OrderQty  smallint   NULL 
 )
 WITH (LOCATION='/user/hue/AWDW2012_SalesData',
    DATA_SOURCE = hdp23 ,
    FILE_FORMAT = CSVfile  
    );       
 

Test your new external table.  Results will return an empty set:

       
      select  * from  [dbo].AWDW_CSV_Sales_Date;

     ModelName,ShipDate,ExtendedAmt,OrderQty
     (0 row(s) affected)
       
 

Now, all we need to do is run the INSERT.

       
   INSERT INTO  [dbo].AWDW_CSV_Sales_Date (ModelName, ShipDate, ExtendedAmt, OrderQty)
   SELECT top 100 p.[ModelName], s.[ShipDate], s.[ExtendedAmount], s.[OrderQuantity]
     FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] as s 
 left outer join [AdventureWorksDW2012].[dbo].[DimProduct] as p 
           on s.[ProductKey] = p.[ProductKey];


   Results:

   (100 row(s) affected)
       
 

To test, re-run the select query: 

       
      select  * from  [dbo].AWDW_CSV_Sales_Date;


      ModelName,ShipDate,ExtendedAmt,OrderQty
      Road-150,2005-07-26 00:00:00.000,3578.27,1
      Road-150,2005-07-26 00:00:00.000,3578.27,1
      Road-150,2005-07-26 00:00:00.000,3578.27,1
      ...
      ...
     (100 row(s) affected)
       
 

Let's go back to Hadoop, and review the contents of the Hue directory. PolyBase created eight (8) files to hold 100 records.

With the PolyBase external table setup, you can insert additional records.

PolyBase - "Login timeout expired" when creating External Data Source

You are trying to create a new external data source with PolyBase setup in SQL Server 2016.

       
 CREATE EXTERNAL DATA SOURCE DS_hdp23 with (
    TYPE = HADOOP,
           -- Hortonworks 2.0, 2.1, 2.2, or Cloudera 5.1:  = 
           --   LOCATION = 'hdfs://NameNode_IP:8020'
    LOCATION ='hdfs://192.168.1.120:8020',
           -- Hortonworks HDP 2.0, 2.1, 2.2 on Linux: 
           -- RESOURCE_MANAGER_LOCATION = 'NameNode_IP:8050'
    RESOURCE_MANAGER_LOCATION='192.168.1.120:8050');
       
 

 and you get the following error:

       
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or 
instance-specific error has occurred while establishing a connection to SQL Server. Server is 
not found or not accessible. Check if instance name is correct and if SQL Server is configured 
to allow remote connections. For more information see SQL Server Books Online.".
Msg 10061, Level 16, State 1, Line 9
TCP Provider: No connection could be made because the target machine actively refused it.       
 

....because the target machine actively refused it.

We'll, our first instinct is to look at the Hadoop cluster. Perhaps security?  Could be, but the first place to check is with SQL Server. Using the SQL Server Configuration Manager, verify that the TCP/IP protocol for SQL Server has been enabled. There is a good chance it has not been enabled.

Enable the protocol and restart the services.  Once enabled, the create external data source should succeed.

PolyBase - Port Range Defaults

When installing PolyBase, the default port range is  16450 - 16460.

It would appear, that you can verify your running value here:

SELECT * FROM [DWConfiguration].[dbo].[configuration_properties]
  where [key] = 'ManagerControlPort';

SQL Server 2016 - June 1st release date

Today, on the SQL Server Blog, the team announced that their target date for general availability (GA) will be June 1st, 2016.

Over the past year, Microsoft has made some major enhancements to SQL Server 2016, many of which you find here in our blog. PolyBase is one of the biggest, allowing real-time, T-SQL based access to all of your data stored in Hadoop or Azure. They have completely rebuilt Reporting Services (SSRS) in the image of Power BI 2.0, and made significant upgrades to Master Data Services (MDS). A good starting point is our article on Redmond Magazine:
SQL Server 2016 Preview Reveals Path to Azure and Big Data.

You can find more detail about the pending release at Get ready, SQL Server 2016 coming on June 1st

PolyBase vs. Spark vs. Hive

PolyBase vs. Spark vs. Hive

Hadoop has been gaining grown in the last few years, and as it grows, some of its weaknesses are starting to show.  For analysis/analytics, one issue has been a combination of complexity and speed. Given that Hadoop is designed to store unstructured data, the reality is that at least with the first phases of ETL/ELT/EL against the unstructured data it will be complex. And yes, call it what you want, but it is in fact a form of ETL/ELT/EL. But once the data has been organized, let's say structured, the next questions will be - where do we put the data, and how do we manipulate the data.

In the early days of Hadoop, it appeared that the typical approach was to transfer the data to a more traditional database.  It might be an MPP system, such as Vertica or Teradata, or a relational database such as SQL Server. or you could move the data to a Hive table. Hive uses many of the SQL commands, but the early design of Hive was slow. And HBase was available, but few if any analysts knew the cryptic commands for HBase.

Improvements to Analysis

These weaknesses have been addressed in one of two approaches:  Improve the current Hadoop functionality, or create new external tools that address both the complexity and the speed issues.

YARN & SPARK
Mapreduce against Hadoop is slow. Spark allows the creation of a clustering computation engine that can be run against HDFS, or a few other non-Hadoop data structures. The enabler for HDFS was Hadoop 2 with YARN. Spark runs under YARN, but much faster than mapreduce. If you are running Hadoop, you will want to include Spark.

And over time, Hive has improved, with the introduction of ORC tables (optimized row columnar), which greatly improved performance (see ORC File in HDP 2: Better Compression, Better Performance). For 2016, an even faster Hive will be introduced, called Hive LLAP (live long and process).  The goal, provide sub-second response for Hive tables For external tools, Here are two links that provide additional details on Hive LLAP:

         INTERACTIVE SQL ON HADOOP WITH HIVE LLAP
         HIVE LLAP PREVIEW ENABLES SUB-SECOND SQL ON HADOOP AND MORE

Working with PolyBase, we have found that once we get the data setup, using it is straight forward. But, we still had to get the data into some form of structure. Looking over the Spark documentation, Spark has a lot to offer, but it too begs to have the data first organized into a standardized structure (see Is Apache Spark going to replace Hadoop). They are all somewhat different, so the question might be:  how do I choose between MapReduce, Spark, Hive  and PolyBase.  If you are already using SQL Server, and have access to PolyBase, that is the best place to start.  You can access traditional text files in Hadoop, as well as the ORC tables in Hive (or delimitedtext tables). PolyBase allows you to use the T-SQL command you already know, and will bypass MapReduce as needed. If you have both PolyBase and Hadoop/Spark it is not an either/or question.  The question is which tool is the best for this problem.

Se also:
http://www.infoworld.com/article/3014440/big-data/five-things-you-need-to-know-about-hadoop-v-apache-spark.html

http://www.infoworld.com/article/3019754/application-development/16-things-you-should-know-about-hadoop-and-spark-right-now.html#tk.drr_mlt

PolyBase and HDInsight

HDInsight has two variants:  Azure, and a "difficult to find" on-premise version.  Here, for this posting, we'll primarily focus on the on-premise version. But it's not clear (as of Nov 24, 2015) just what is HDInsight for Windows Server and if it even still exists.  Several years ago, I downloaded an early version, but I can no longer find a download.  Now, I can only find an install for the Emulator (below). The other references point to www.microsoft.com/bigdata/ and those point to SQL Server.

But, before you get started, overall, it seems that an on-premise HDInsight product has been abandoned by Microsoft for the Hortonworks for Windows kits. The focus is with Azure HDInsight, and the Hortonworks for Windows.  The documentation is old, the links do not always work, and there are few comments about this elusive on-premise package, and those are about how it does not work.  So, if you want Hadoop running on Windows, skip directly to Hortonworks (see link below). And keep in mind, that there are no listed sp_configure options for HDInsight - none!

Can we get it to Work?

Be forewarned, several people have commented that they cannot connect PolyBase to HDInsight in Azure. And I have not had time to give it a go. 

Where do I get started?

Start with the technet overview:  HDInsight Server.
This will take you to another Technet article about Getting Started with the Windows Azure HDInsight Emulator. So, what does that mean?  From some phrases, it seems to mean that the emulator is limited to a single node - for testing purposes.  Down the page, another reference takes you to a Hortonworks partnership page:  Hortonworks & Microsoft: Bringing Apache Hadoop to Windows.  And this page has links to try HDP on Windows, or to try HDP in Azure.

Deep in one of the pages, you'll find your way to the Microsoft Azure page:  Install the HDInsight Emulator.

HDInsight Emulator for Windows Azure 

Once you get to the Azure page, you'll find a link to the install page:  Microsoft HDInsight Emulator for Windows Azure.  And for an earlier preview version:  There is no date for this, so it might not be current, but here is the link: Microsoft HDInsight Emulator for Windows Azure (Preview).  I found one comment that it does not show up in the installer! If you have any success with the Emulator, please let me know.

Is HDP in Azure different from or the same as HDInsight

According to this partners page:  Hortonworks Data Platform, they are different. Perhaps similar, but different.

PolyBase to the HDInsight Emulator

Once I have more information, I'll update.

Other Resources

Introduction to Azure HDInsight
Microsoft HDInsight Server for Windows and Windows Azure HDInsight Service Announced
How to Install Microsoft HDInsight Server Hadoop on Windows 8 Professional

Connect PolyBase to your Hive ORC Table

Using PolyBase to connect to a plain text Hive table (file) is no different from connecting to any other file in Hadoop. (See:  Connect PolyBase to your Hive database Table: SQL Server 2016) But the future of Hive is moving to the optimized row columnar (ORC) format. According to a posting on the Hortonworks site, both the compression and the performance for ORC files are vastly superior to both plain text Hive tables and RCfile tables. For compression, ORC files are listed as 78% smaller than plain text files. And for performance, ORC files support predicate pushdown and improved indexing that can result in a 44x (4,400%) improvement. Needless to say, for Hive, ORC files will gain in popularity.  (you can read the posting here: ORC File in HDP 2: Better Compression, Better Performance).

Setting up ORC tables in PolyBase is a three step process:

External Data Source      - no sharing between file format types!

External File Format        - specific for ORC

External Table                   - relies on the ORC file format

This follows the same approach used to connect to plain text files. But, do we need to make changes to all three? To some extent, yes. External tables rely on the external file format, so we'll need to either create a new external table, or modify an existing external table. The external file format is where we specify that the source table is an ORC. And finally, the external data source can only support one format type. So, you will need different external data sources for your plain text file formats and your ORC file formats - even if they are pointing to the same Hadoop cluster.  On MSDN - CREATE EXTERNAL TABLE(Transact-SQL), near the bottom in the examples section is a note about data sources. Specifically it states:

Note

All data sources must have the same format type. You cannot have some data sources with text-delimited file formats and some with RCFILE formats.

ORC Specific External Data Source

For our test, we created a specific external data source just for ORC Hive tables.

CREATE EXTERNAL DATA SOURCE hdp23_orc with 

 (

  TYPE = HADOOP,

  LOCATION ='hdfs://192.168.1.120:8020',

  RESOURCE_MANAGER_LOCATION='192.168.1.120:8050'

 ); 

ORC Specific External File Format

Next, we created three external file formats just for our ORC tables.  Notice that unlike the DELIMITEDTEXT external file formats, there is no need for a field terminator. string delimiter or date format. Besides the FORMAT_TYPE option, the only other option for the ORC format type is for compression.  Here, you can either ask your Hadoop administrator, or experiment to see which one works. We created all three external file formats. One for each of the two explicit data compression options, and a third where we omitted the data compression completely. In our Hortonworks cluster, both the SnappyCodec data compression format and the file format where we omitted the declaration for the data compression worked. Here they are:

  CREATE EXTERNAL FILE FORMAT ORCdefault

  WITH (

        FORMAT_TYPE = ORC

       ,DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec'

       );

  go

  CREATE EXTERNAL FILE FORMAT ORCsnappy

  WITH (

        FORMAT_TYPE = ORC

       ,DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

       );

  go

  CREATE EXTERNAL FILE FORMAT ORCnocompress

  WITH (

        FORMAT_TYPE = ORC

       );

 go

ORC Specific External Table

With the ORC specific data source setup and the ORC specific file formats ready to use, all we need is to setup the table. We found that PolyBase wants a strongly typed ORC Hive Table, so here you will need to make sure your SQL data types match those that are present in Hadoop. (See PolyBase wants a strongly typed ORC Hive Table)

   CREATE EXTERNAL TABLE [dbo].sample_07c (

     codeid          nvarchar(200) NULL ,

     descrip         nvarchar(200) NULL ,

     total_emp             int null,

     salary                int null

       )

       WITH (

             LOCATION    ='/apps/hive/warehouse/sample_07c/000000_0',

             DATA_SOURCE = hdp23_orc ,

             FILE_FORMAT = ORCsnappy                  

            );

See also:

• Getting Started with PolyBase in SQL Server 2016
• Create an External Data Source to Hadoop in SQL Server 2016
• Create a PolyBase CSV External File Format in SQL Server 2016
• Create PolyBase External Table in SQL Server 2016
• PolyBase YARN configurations in SQL Server 2016
• PolyBase Settings Folder in SQL Server 2016

PolyBase wants a strongly typed ORC Hive Table

It appears that PolyBase wants a strongly typed external table definition to make a connection to a Hive ORC table in Hortonworks.  For new tables, or new Hadoop connections, I initially declare all of the columns as varchar or nvarchar. For this test, my ORC table had four columns, the first two were string, and the others were int.

So, starting off, all four columns were declared as nvarchar.  But this was a Hive ORC table, and it did not work.  The return message was:

     Msg 106000, Level 16, State 1, Line 75
     org.apache.hadoop.io.IntWritable cannot be cast to org.apache.hadoop.io.Text
     OLE DB provider "SQLNCLI11" for linked server "(null)" returned message
     "Unspecified error".
     Msg 7421, Level 16, State 2, Line 76
     Cannot fetch the rowset from OLE DB provider "SQLNCLI11" for linked server " (null)"..


Looking closely, the key phrase in the error message was "cannot be cast." Once the two int columns were properly declared as int the create table command ran successfully, and the table could now be queried.  The successful DDL:

CREATE EXTERNAL TABLE [dbo].sample_07c (
codeid nvarchar(200) null,
descrip nvarchar(200) null,
total_emp int null,
salary int null
)
WITH (
         LOCATION='/apps/hive/warehouse/sample_07c/000000_0',
 DATA_SOURCE = hdp23_orc ,
 FILE_FORMAT = ORCsnappy
 );
         go


Also note that I had a dedicated data source for the ORC file type. According to MSDN, all data sources must have the same file format associated with it.  So, you'll need a different data source for your DELIMITEDTEXT format types, and another for your ORC format types. The exact warning in MSDN is:

Note

All data sources must have the same format type. You cannot have some data sources with text-delimited file formats and some with RCFILE formats.

            See:  MSDN: CREATE EXTERNAL TABLE (Transact-SQL)

SQL Server 2016 First Look - Review

The upcoming release of SQL Server 2016 promises to have some big enhancements, based on the various prereleases. PolyBase is just one of the major enhancements.

You can find my published review covering SQL Server 2016 community technology preview (CTP) at Redmond Magazine. Find it here: SQL Server 2016 Preview Reveals Path to Azure and Big Data.

Another exciting addition included with SQL Server 2016 is the Query Store. Here, Microsoft stayed with a simple, and meaningful name.  The query store does literally store past queries along with performance statistics that allow DBA's to monitor, and manage how they should be run in the future.  Redgate's Simple Talk web site has started a series of posts that cover the Query Store in-depth. you can find it here: TheSQL Server 2016 Query Store: Overview and Architecture.

PolyBase Configuration for Cloudera

Cloudera is perhaps the biggest player in Hadoop, so it makes sense that we understand what's needed to connect SQL Server 2016 to Cloudera.  To get started, we downloaded the latestvirtual server image from Cloudera, which for our purposes was 5.4.2.0. You can get a Cloudera QuickStart VM here.  

Once we had Cloudera up and running, we could move on to the next step of configuring our server for Cloudera. The PolyBase documentation does not specifically list out our version of Cloudera 5.4.2.0, so we chose the next best listing - which was Option 6: Cloudera 5.1 on Linux.  With this, we need to update our server configuration accordingly:

sp_configure 'hadoop connectivity', 6;

reconfigure

One thing to keep in mind is that PolyBase can only connect to one Hadoop installation at atime.  For a more detailed listing of how to setup PolyBase in SQL Server 2016 see our post on:  Setting Up PolyBase in SQL Server 2016. 

Hadoop YARN - Locating the yarn.application.classpath

Cloudera 5.4.2.0 is a YARN based Hadoop server, so we'll also need to locate the yarn.application.classpath value in Hadoop and then update the SQL Server 2016 with the  yarn.application.classpath value from Hadoop. Fortunately, the Cloudera VM starts up with a typical CentoOS UI, so locating the 'yarn-site.xml'  file is a lot easier than in Hortonworks. And you only need to do it once.

In Cloudera, the easiest way to find the file using the UI tools is with the File Browser. At the top left of the UI locate the Applications tab and navigate to Applications | System Tools | File Browser, and open the File Browser. 

Next, in File Browser using the search "binoculars" locate 'yarn-site.xml'.   Then open the yarn-site.xml file that is in the /etc/hadoop/conf.empty folder. 

Open the file and locate the yarn-application.classpath.  

For my Cloudera installation the yarn.application.classpath is:

       <property>

         <description>Classpath for typical applications.</description>

          <name>yarn.application.classpath</name>

          <value>

             $HADOOP_CONF_DIR,

             $HADOOP_COMMON_HOME/*,$HADOOP_COMMON_HOME/lib/*,

             $HADOOP_HDFS_HOME/*,$HADOOP_HDFS_HOME/lib/*,

             $HADOOP_MAPRED_HOME/*,$HADOOP_MAPRED_HOME/lib/*,

             $HADOOP_YARN_HOME/*,$HADOOP_YARN_HOME/lib/*

          </value>

 </property>

Use this value to update the complementary yarn-site.xml file on your SQL Server 2016 installation. Typically, you can find it here:

         C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Polybase\Hadoop\conf\

Our post on Setting up PolyBase for YARN in SQL Server 2016 has additional details on configuring SQL Server for a YARN server.  Once you have the server configured for Cloudera and YARN, you should be all set to connect and use Hadoop via the PolyBase engine. 

See our posts on:

•          Getting started with PolyBase
•          Creating an External Data Source
•          Creating an External File Format
•          Creating an External Table 

PolyBase - Inserts, Updates now supported (DML)

With SQL Server 2016 CTP2.x PolyBase connected to Hadoop, it's a great way to access all the data stored in your Hadoop installation using familiar T-SQL commands.  And if you have been reading some of the latest comments on NoSQL, your company may have the urge to start using Hadoop as your sole data warehouse and repository. All you have to do is migrate your data warehouse data, and with the PolyBase connection, it should be fairly simple - right?

[ Take me directly to PolyBase - Insert data into a Hadoop Hue Directory, where we outline just how to do it.]

For the early CTP releases INSERT was not supported. I have now tested it ( see PolyBase - Insert data into a Hadoop Hue Directory ), and you can now INSERT INTO an external table via PolyBase. Once you have an external table setup the next step is to run an INSERT command. 

Note: The following was written before INSERT was fully supported.

Perhaps something like this:

       insert into [dbo].InsertTest_1 ( firstcol, secondcol )

       values ('firstcolvalue1','secondcolvalue1');

In the earlier CTP versions, after running the insert command, you were greeted with an error message similar to the following:

       Msg 46519, Level 16, State 16, Line 68

       DML Operations are not supported with external tables.

With SQL Server 2016 released, we decided to cycle back and test the INSERT function. To keep it simple, we used the following command against a three column, all nvarchar table in Hadoop:


   insert into [dbo].AWDW_CSV_3String ( FullName, AddressLine1, DataSrc )
   values ( 'Ziggy Stardust','1 Moonshot Drive','Manual' );

The message returned was:

   Msg 46914, Level 16, State 1, Line 61
   INSERT into external table is disabled. Turn on the configuration
   option 'allow polybase export' to enable.

OK, update the configuration option.

   sp_configure 'allow polybase export', 1;
   reconfigure

Close, but still have issues.  PolyBase denies the existence of my table:

   Msg 7320, Level 16, State 102, Line 64
   Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11"
   for linked server "SQLNCLI11". EXTERNAL TABLE access failed because the
   specified path name 'hdfs://192.168.1.120:8020/user/hue/AWDW_CSV_3String.csv'
   does not exist. Enter a valid path and try again.

Switching to a Hive table, we were successful in inserting data into our table. But not initially.
The full file address for our test Hive table was

     LOCATION='/apps/hive/warehouse/sample_08/sample_08'

This did not work. Using only the relative Hive table location (below) did allow us to insert new data:


     LOCATION='/apps/hive/warehouse/sample_08'



Ok, now take me directly to PolyBase - Insert data into a Hadoop Hue Directory, where we outline just how to do it.

PolyBase, Performance and Statistics

Performance is always a consideration with databases, and using PolyBase is no different.  DBA's will create indexes, setup specialized indexed views and create and update statistics.  Unfortunately, external tables do not have most of these options. 

                Indexes?                  - No  

                Indexed views?       - No

                Statistics?                - Yes

Only statistics are the current option. Using a test dataset in a Hortonworks 2.0 Hadoop system, I was able to increase performance by about 10%. This was a small dataset, so a large increase was not expected. In the future I'll compare performance on a larger dataset.

PolyBase and Views

PolyBase and Views

Views can be created against PolyBase external tables, using T-SQL. Unfortunately, an indexed view is not supported. Once you include the option "with SCHEMABINDING"  in your Create View, you  get an error:

         Msg 46518, Level 16, State 9, Procedure PolyBaseTableView, Line 115

         The option 'SCHEMABINDING' is not supported with external tables.

I expect there will be situations where being able to create an indexed view against an external PolyBase table would be ideal. Conversely, the potential size of datasets loaded into Hadoop or Azure could be so large, that the process of creating a local index on such a large dataset could exceed the capacity of the database server.  Still, I wish we had the option - and let the DBA make the determination.

PolyBase – error connecting to Hadoop file

PolyBase – error connecting to Hadoop file

At least with the current SQL Server 2016 CTP 2.3, PolyBase errors out trying to connect to certain text files in Hortonworks Hadoop. You typically get an error message similar to the following:

Update - June 14, 2016:  MSFT published a Polybase focused white paper for the Azure Data Warehouse, which supports Polybase.  This out lined - at least for Azure DW, the file types supported by Polybase.  For Azure DW, Polybase only supports UTF-8 files. This could explain some of the failed load issues experienced.  

Msg 107090, Level 16, State 1, Line 79

Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed.

OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Unspecified error".

Msg 7421, Level 16, State 2, Line 79

Cannot fetch the rowset from OLE DB provider "SQLNCLI11" for linked server "(null)". 

I’ve investigated what might be causing these errors, trying to discover if there are certain traits that cause the error.

Could not find or load main class path.....

Dennes found on Hortonworks a possible solution for the following error.

         
      Error: Could not find or load main class org.apache.hadoop.mapreduce.v2.app.MRAppMaster  
 

This may be beneficial for other issues, such as Could not obtain block.

       
Answer by Montrial Harrell · Jun 11 at 04:19 AM
Got it!! I added the below property to the mapred-site.xml file and the query ran successfully.

<property> <name>mapreduce.app-submission.cross-platform</name> <value>True</value> </property>   
 

(this was provided by Montrial Harrell at the Yarn ClassPath Value for Polybase Pushdown - Hortonworks Community Connection    -- review the entire post, as there may be other suggestons that might be of assistance.)

...cannot be cast...

If you're error message includes the phrase:  "...cannot be cast..." see page PolyBase wants a strongly typed ORC Hive Table.

Could not obtain block:

Dennes found that the error message Could not obtain block:  "...usually means the port 50010 (data node port) is not responding to SQL Server."  See also adjustments noted above for "Could not find or load main class"

EOF does not match expected format:

From my investigation, if EOF (end of file) has a different format from that declared in the CREATE EXTERNAL TABLE command, you will get an error. If, for example, the file has a trailing line with row count, or other meta data.

Data Type Mismatch

If the declared data type does not match with the incoming data type, you’ll get an error. In setting up a new table, it might be useful to first declare all columns as varchar or nvarchar to minimize any potential errors.  Once you establish a working connection, create a new table with the correctly typed columns. Loading dates into PolyBase has a very specific protocol, and not all date formats are supported. 

Data Size insufficient

If the size of the declared data type is not sufficiently large for incoming data type, you’ll get an error. In setting up a new table, it might be useful to first declare all columns as long varchar or nvarchar to minimize any potential errors. An example might be where you declare the column to be varchar(10), but the maximum size found exceeds 10.  No auto-truncate.

Line Delimiter

At least so far, I’ve been able to connect to files with the three ‘standard’ approaches to establishing the EOL: 

1)  CR/LF   0D 0A     (DOS/Windows)

2)  LF                 0A    (UNIX)

3)  CR        0D           (Mac)

File Format

The UTF-8 format seems to be the most common format, PolyBase works with the UTF-8 format. I have had trouble with files in the older ANSI/ASCII format.  My single attempt with UTF-16 did not work, but that may not be due to the UTF-16 format.  As noted above, Azure Polybase only supports UTF-8.

Other – still to be discovered problems

Several Hadoop files I have are not connecting to PolyBase, and I have not resolved what the problem. As I get them resolved, I’ll add those discoveries here.

Connect PolyBase to your Hive database Table: SQL Server 2016

A straight forward way to connect PolyBase to your Hadoop text-delimited Hive tables is to use the DELIMITEDTEXT format method (non-ORC files). The only difference is that with Hive tables, the file data has been internally migrated from a simple file format into a Hive table. But, while it is treated as a Hive table, it is still a file within Hadoop. And you can access that file using the PolyBase External Table declaration syntax. (For ORC tables see Connect PolyBase to your Hive ORC Table)

Before you can create an External Table in PolyBase, you first need both an External Data Source, and an appropriate External File format. And like any text-delimited External Tables in PolyBase, you'll need to define how the columns are delimited, the column names and data types, and the physical location of the file on the Hadoop server.

To get started, first navigate to the Hive user interface in Hadoop. For this example, we are using Hortonworks.  With the UI open, select/click on the Tables tab. This will show a listing of the Hive tables available.

HIVE table listing - notice the database name:  xademo.

 

With the Table listing open, locate the table. Our target table is call_detail_records. After locating the table, select/click on it. This brings up the Table Metadata page.

Metadata page shows the column names and datatypes stored in HIVE.

The Metadata page defines how Hive has named the column and their data type. We will need this to create our External Table. On the left, locate and select/click on the View File Location action. This takes you to the folder where the file is located, as well as providing the actual file name stored in the Hive database. 

File Browser - File Folder Location.

In our example, the file and folder name to be used for the External Table is:

  /apps/hive/warehouse/xademo.db/call_detail_records/cdrs.txt

In the file-folder name, notice the folder xademo.db. This represents the Hive database named xademo. The next folder, call_detail_records is identical to the Hive table name. The last value, cdrs.txt, is the file name used within Hadoop on the server. You will also notice that you are now in the File Browser, and not Hive.

Select/click the file (in our case cdrs.txt). This opens the file in viewing mode, so you can discover the file delimiter being used by Hive. In our case, it is a pipe.

Preview of the file data in Hadoop for the file cdrs.txt

For the Pipe delimiter, we'll need to create a new External File format, with this syntax:

CREATE EXTERNAL FILE FORMAT Pipefile

WITH (

       FORMAT_TYPE = DELIMITEDTEXT

              , FORMAT_OPTIONS (

              FIELD_TERMINATOR = '|'                  

                     ,USE_TYPE_DEFAULT =  TRUE ),

              DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec'

       );

Using the column and data type values discovered earlier, we can now create the External Table using this syntax:

       CREATE EXTERNAL TABLE [dbo].HIVE_asfile_cdrs (

              PHONE         varchar(100) NULL ,

              TYPE_1        varchar(40) NULL ,

              IN_OUT        varchar(40) NULL ,

              REC_LOCAL     varchar(40) NULL ,

              DURATION      varchar(40) NULL ,

              REC_DATE      varchar(40) NULL ,

              TIME_         varchar(40) NULL ,

              ROAMING       varchar(40) NULL ,

              AMOUNT        varchar(40) NULL ,

              IN_NETWORK    varchar(40) NULL ,

              IS_PROMO      varchar(40) NULL ,

              TOLL_FREE     varchar(40) NULL ,

              BYTES         varchar(40) NULL ,

              TYPE_2        varchar(40) NULL

             )

           WITH (LOCATION='/apps/hive/warehouse/xademo.db/call_detail_records/cdrs.txt',

              DATA_SOURCE = hdp23 ,

              FILE_FORMAT = Pipefile           

              );

With the External Table created, test the connection by running a simple SELECT. If all goes well, your Hive table is now available via a PolyBase connection.