Tuesday, September 1, 2015

Setting up PolyBase for YARN in SQL Server 2016

If your target Hadoop system is YARN based, you will need to follow the optional configuration outlined in MSDN for PolyBase and make one more configuration adjustment. You'll need to add the key value for the yarn.application.classpath from the Hadoop server and add it to your SQL Server yarn-site.xml file.

Locating the Hadoop yarn.application.classpath
On my Hortonworks 2.0 server, the configuration key value for yarn.application.classpath is in the yarn-site.xml file. This file is located in the Hadoop configuration directory, typically in the folder:  etc/hadoop/ on your Hadoop server. If you are unsure about where the yarn-site.xml file is located, use the Linux command locate to find the file.



If the Linux command locate is not on your system, use the find command:  find / -name myfilename.  On both my single node Hortonworks 2.0 sandbox, and my Hortonwworks 2.3 sandbox, the file was located in the directory:  etc/hadoop/conf/. After locating the file, open it with the Linux command:  vi yarn-site.xml and scroll down to read the value.


To exit out of vi, use the command:  :q!<enter>

 
  For my Hortonworks 2.0 installation, the yarn.application.classpath value was:

       
<name>yarn.application.classpath</name>
<value>/etc/hadoop/conf,/usr/lib/hadoop/*,/usr/lib/hadoop/lib/*,/usr/lib/hadoop-hdfs/*,/usr/lib/hadoop-hdfs/lib/*,/usr/lib/hadoop-yarn/*,/usr/lib/hadoop-yarn/lib/*,/usr/lib/hadoop-mapreduce/*,/usr/lib/hadoop-mapreduce/lib/*</value>
    


For my Hortonworks 2.3 installation, the yarn.application.classpath value was:
       
<name>yarn.application.classpath</name>
<value>$HADOOP_CONF_DIR,/usr/hdp/current/hadoop-client/*,/usr/hdp/current/hadoop-client/lib/*,/usr/hdp/current/hadoop-hdfs-client/*,/usr/hdp/current/hadoop-hdfs-client/lib/*,/usr/hdp/current/hadoop-yarn-client/*,/usr/hdp/current/hadoop-yarn-client/lib/*</value>

Once you have this value, locate on your SQL server machine, the file yarn-site.xml and update the yarn.application.classpath value.  You can find this file in the SQL Server installation path:  <SqlBinRoot>\Polybase\Hadoop\Conf.  In most cases, the path is: 


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


yarn-site.xml File on SQL Server Before update:

    
<!-- Applications' Configuration-->
 <property>
<description>CLASSPATH for YARN applications. A comma-separated list of CLASSPATH entries</description>
<!-- Please set this value to the correct yarn.application.classpath that matches your server side configuration -->
<!-- For example: $HADOOP_CONF_DIR,$HADOOP_COMMON_HOME/share/hadoop/common/*,$HADOOP_COMMON_HOME/share/hadoop/common/lib/*,$HADOOP_HDFS_HOME/share/hadoop/hdfs/*,$HADOOP_HDFS_HOME/share/hadoop/hdfs/lib/*,$HADOOP_YARN_HOME/share/hadoop/yarn/*,$HADOOP_YARN_HOME/share/hadoop/yarn/lib/* -->
<name>yarn.application.classpath</name>
<value></value>
 </property>


yarn-site.xml File on SQL Server After update:


<!-- Applications' Configuration-->
 <property>
<description>CLASSPATH for YARN applications. A comma-separated list of CLASSPATH entries</description>
<!-- Please set this value to the correct yarn.application.classpath that matches your server side configuration -->
<!-- For example: $HADOOP_CONF_DIR,$HADOOP_COMMON_HOME/share/hadoop/common/*,$HADOOP_COMMON_HOME/share/hadoop/common/lib/*,$HADOOP_HDFS_HOME/share/hadoop/hdfs/*,$HADOOP_HDFS_HOME/share/hadoop/hdfs/lib/*,$HADOOP_YARN_HOME/share/hadoop/yarn/*,$HADOOP_YARN_HOME/share/hadoop/yarn/lib/* -->
<name>yarn.application.classpath</name>
<value>/etc/hadoop/conf,/usr/lib/hadoop/*,/usr/lib/hadoop/lib/*,/usr/lib/hadoop-hdfs/*,/usr/lib/hadoop-hdfs/lib/*,/usr/lib/hadoop-yarn/*,/usr/lib/hadoop-yarn/lib/*,/usr/lib/hadoop-mapreduce/*,/usr/lib/hadoop-mapreduce/lib/*</value>
 </property>

After updating the yarn.application.classpath on your SQL Server machine, restart all of the services.


For more information on MSDN for PolyBase for YARN, see MSDN - PolyBase for Yarn

Setting up PolyBase in SQL Server 2016

Installing and completing the initial setup for PolyBase on SQL Server 2016 is straight forward. However, PolyBase does have a few key requirements, so I've outlined these below.  
They include:


  • Oracle Java SE RunTime Environment (JRE) version 7.51 or higher. The installer will fail if JRE is not present. For my installation, I used the file:  jre-8u51-windows-x64.exe. You can find the latest JRE here Java SE Runtime download.
  • Microsoft .NET Framework 4.0
  • 64-bit SQL Server Evaluation Edition (CTP 2.2)
  • Minimum memory of 4 GB
  • Minimum hard disk space of 2 GB

The easiest way to install the PolyBase feature is to use the SQL Server Installation Center installation wizard.  When you get to the feature selection page, simply select the PolyBase feature. Then, in the server configuration page, setup both the PolyBase Engine Service and the PolyBase Data Movement Service to run under the same account. With the PolyBase feature installed, it's time to move on to the next step of configuring the database server for PolyBase.



Server Configuration
The next step is to configure SQL Server for your specific Hadoop server. For my initial test, I was running an older version of Hortonworks 2.0 on Linux, so my configuration value was 5.  
Configure the server for Hortonworks 2.0.

       

sp_configure @configname = 'hadoop connectivity', @configvalue = 5;   
go
reconfigure
go
       
 


The configuration value is specific to the target Hadoop environment you plan to use, so be sure to review the full documentation for setting up the server configuration. MSDN-PolyBase Configuration Options

The current listing for the Hadoop configuration options is as follows:

·         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 on Linux
·         Option 7: Hortonworks 2.1 and 2.2 on Linux  ( used this for Hortonworks 2.3 )
Note: Hortonworks 2.3 contains Hadoop version 2.7.1. I have found references where Option 7 works with the generic Apache 2.7.x installs.
·         Option 7: Hortonworks 2.2 on Windows Server
·         Option 7: Azure blob storage (WASB[S])

Which Azure Option:  1,4 or 7?
If you are planning on setting up a connection to an Azure Blob, it is unclear which option to choose. Is it Option 1, Option 4, or Option 7. As of September 2, 2015, I could not find any documentation on which one to use.

One more unknown, is that when you get to setting up an External Data Source, one of the options is a connection to an Azure SQL Database ({type =  SHARD_MAP_MANAGER}. As of September 2, 2015, it was not clear which configuration option you would choose if you wanted to connect to an Azure SQL Database.

If your target Hadoop is a YARN based cluster, you will need to update the yarn-site.xml file. You can find the current setup information for PolyBase in MSDN here: MSDN Getting Started with PolyBase

Confirm Successful Install

Use SERVERPROPERTY to confirm if your installation was successful.
Successful returns 1.  Otherwise you get a big 0.

       
        SELECT SERVERPROPERTY ('IsPolybaseInstalled') AS IsPolybaseInstalled; 

        Results
        IsPolybaseInstalled
        ------------------- 
        1

       (1 row(s) affected)
       
 

Security and User Considerations

Our test system used an open Hortonworks Sandbox. However, we did find one posting that suggested that the HadoopUserName value be updated. Thanks to Hilmar Buchta for pointing out this hidden user name setting.


See also:



Getting Started with PolyBase in SQL Server 2016

Several years ago, Microsoft introduced PolyBase as part of the Parallel Data Warehouse, now called Analytic Platform System. Unfortunately, since it was only available to the few who could afford the Analytic Platform System, not much has been written about PolyBase.  With SQL Server 2016's Community Technology Preview 2.2 (CTP 2.3) released this summer PolyBase will now be available to a much wider audience. As a premium feature, it will require the Enterprise Edition license.  So just what is PolyBase, and should anyone be interested?  Update:  On June 1, 2016, the GA version
(general availability) was released.  See SQL Server 2016 Now Available - June 1, 2106 for more details.

PolyBase acts as a real-time query engine that bridges the traditional relational database structures held in SQL Server with the massive amounts of data held in Hadoop. It is real time in that once you have setup your Hadoop data set within SQL Server, you can query it using T-SQL. Even to the point where you are joining relational tables with Hadoop tables.

Perhaps the hardest part about PolyBase is the setup and configuration required to connect to your Hadoop environment. It is a multi-step process. First to establish the SQL Server to hadoop connection. And then to configure your External Data Source, External File formats, and finally, the External Table.

The first step is to enable the PolyBase Feature. This first step is part of the standard setup process built into SQL Server.  I've installed the CTP 2.3 version of SQL Server 2016 several times, and I've found that for PolyBase, it is best to first install SQL Server first. Ensure that it s installed properly, and then install the PolyBase feature. The screen snapshot below shows that PolyBase as just another feature selection.


The default port range for PolyBase; as of CTP 3.3 is:  16450-16460


The next step is to actually install and initially configure PolyBase that is discussed in the next post. Setting up PolyBase in SQL Server 2016

If you are interested in reading more about PolyBase, a good place to start is with James Serra's blog on PolyBase. Polybase Explained - James Serra

Wednesday, July 29, 2015

Power BI connections: Personal Gateway or Analysis Services Connector

You have your Power BI account, you've  looked over the samples and created a few reports with static data from Excel and a few flat files. Now you want to make the next step and start using data from your organizations data warehouse and tabular databases.  But you've noticed that there seems to be more than one tool to make the connection.  But which tool, if any do you need? Or do you even need them at all? Earlier you created a report using data pulled from a networked SQL Server, all without installing either the Personal Gateway or the Analysis Services Connector. So, which is it?  The Power BI Personal Gateway, the Analysis Services Connector, both or neither?



Personal Gateway vs. Analysis Services Connector

A good way to think of the Personal Gateway is that it allows your reports, on your Power BI portal to connect to the organization's data warehouses and other supported databases and systems. The Analysis Services Connector, on the other hand, connects a single SSAS tabular server to all the Power BI users (who have access permission, naturally).

Personal Gateway
The most natural location to install your Personal Gateway is on your computer. Key things to consider:
  • It cannot be installed on the same computer as the Analysis Services Connector
  • The Personal Gateway runs on a computer you control. it is for your reports alone
  • It must be up and running to support a refresh. So the computer where it is installed must be up and running.
  • It's used for on-premise data sources, except tabular servers
  • Installation: Runs as a service if installed by an administrator. Otherwise, it must be started like an application. So try to get your administrator to set it up for you.
  • If you want to refresh your data from on-premise data sources, you need to install the Personal Gateway
  • It's only supported on 64 bit Windows operating systems (including Windows 10)
  • MS Power BI Support:Power BI Personal Gateway

Analysis Services Connector

The most elegant approach for installing the Analysis Services Connector is to install it on the same server running the SSAS tabular server.Key things to consider:
  • It cannot be installed on the same computer as the Personal Gateway
  • The Connector is mapped to a single Analysis Services server. So for every tabular server you want to use, you need to install and configure an Analysis Services Connector
  • It can run on any domain computer, but ideally it can be installed on the tabular server
  • Only supports SSAS tabular models at this time
  • MS Power BI Support: 





Tuesday, July 28, 2015

Power BI 2.0: Reasons to upgrade to Power BI Desktop

In an earlier post, we discussed that with the general availability (GA) of Power BI 2.0, that the preview Power BI Designer had been replaced with Power BI Desktop. But besides a new name and some color changes, is there any reason to upgrade?

Absolutely!  Here, are a few of the new features that have been added to the Power BI 2.0 Desktop.  And, while I cannot confirm it, I have heard that it is now available in both a 64 bit and a 32 bit version.

Figure 1:  New Visualizations, including the very nice waterfall chart. (Comparison of the waterfall chart with the line and column charts)


Figure 2: Add a company logo, a report title or summary text with the text and image insert tools.



Figure 3: In the preview designer, customizing reports was near impossible. 
Now, you can control the  graph legends, title, data labels, and colors.


















Figure 4:  You can even control colors down to the field level.







Saturday, July 25, 2015

Power BI 2.0: Upgrading to the Power BI Desktop

With the general availability release of Power BI 2.0 on July 24, 2015, preview users will want to upgrade from the preview release of Power BI Designer to the latest Power BI Desktop.

Over the last several months, Microsoft has issued several updates to the Power BI Designer, all with an included time stamp in the installer package file.  The latest was PBIDesigner_x64_20150517.msi.
September 23, 2015, Microsoft released an updated Desktop.  Power BI Desktop is now available as download, with the file name of PBIDesktop_x64.msi. After logging into Power BI, simply use the download option, to download the new Power BI Desktop.

Figure 1: Download



During installation, it will remove the earlier version of Power BI Designer. Icon colors have changed to gold and black from the initial green and white. The interface has also been updated to better match the changes they made this week to the website prior to release.

Figure 2:  Improved Power BI Desktop layout



Figure 3:  The earlier Power BI Designer layout




First Look - Power BI 2.0

Yesterday, July 24, 2015, Microsoft announced the general availability of the updated Power BI. While release 1.0 of Power BI was available only to users of Office 365, Power BI 2.0 is open to all. And the basic subscription price is free, while the Pro version only $9.99/month per person.

It's a great tool, and with the latest release, could become the game changer for to the self-service business intelligence market. Excel users will appreciate the integration with Excel, but unlike the first release, you do not need to install any plug-ins. And power users with direct access to the company data warehouse can setup automatic refreshes to the reports and dashboards you have shared with your colleagues. You can ready my in-depth review online at Redmond Magazine First Look Power BI 2.0.