Introduction to R Services and R client – SQL Server 2016

Introduction

After some time using R and SQL server as two different tools (not 100% true because I already have imported data from SQL Server into R Studio), now Microsoft is offering as part of the SQL Server 2016 R services. That seems to be very promising, especially for Microsoft BI professionals. One of the advantages is to keep analytics close to data and use an integrated environment.

In this post I will show some basic operations and how to get started with these technologies. I took most of the R code from this Microsoft walkthrough that I highly recommend to you:

Data Science End-to-End Walkthrough

Prerequisites

• SQL Server 2016 – I installed the enterprise edition but with the others should work
• R services: this is part of the SQL Server 2016, so you need to add this feature during the first installation or add it later
• R client: http://aka.ms/rclient/download
• WideWorldImporters database (WWI): download and documentation . This is the new sample database for SQL Server 2016 replacing the famous AdventureWorks

Set up R Services

That is very well documented in the MSDN, nothing to add from my side:

Set up SQL Server R Services (In-Database)

Create a view to generate a dataset to analyze

Once you installed the WWI database, create this view:

USE [WideWorldImportersDW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [Fact].[SalesByLocation] as
select City, Location.Lat, Location.Long, sum(profit) AS SUM_Profit, AVG([Unit Price]) AVG_UnitPrice
from fact.Sale s
inner join Dimension.City c on s.[City Key] = c.[City Key]
group by City, Location.Lat, Location.Long
GO

 

Add the require R packages

We will need the following R packages:

• Ggmap
• Mapproj
• ROCR
• RODBC

Using the R client, we have to options:

• From the menu –> Package –> Install Packages

• Or running the following script

if (!('ggmap' %in% rownames(installed.packages()))){ 
  install.packages('ggmap') 
} 
if (!('mapproj' %in% rownames(installed.packages()))){ 
  install.packages('mapproj') 
} 
if (!('ROCR' %in% rownames(installed.packages()))){ 
  install.packages('ROCR') 
} 
if (!('RODBC' %in% rownames(installed.packages()))){ 
  install.packages('RODBC') 
}

 

Create a connection to the SQL Server instance from r client

library(RevoScaleR)
# Define the connection string
connStr <- "Driver=SQL Server;Server=HERZO01;Database=WideWorldImportersDW;
Trusted_Connection = True"
# Set ComputeContext
sqlShareDir <- paste("C:\\AllShare\\",Sys.getenv("USERNAME"),sep="")
sqlWait <- TRUE
sqlConsoleOutput <- FALSE
cc <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir,
                    wait = sqlWait, consoleOutput = sqlConsoleOutput)
rxSetComputeContext(cc)
sampleDataQuery <- "select * from [Fact].[SalesByLocation]"
inDataSource <- RxSqlServerData(sqlQuery = sampleDataQuery,
 connectionString = connStr, rowsPerRead=500)

 

The first step is to load the RevoScaleR library. This is an amazing library that allows to create scalable and performant applications with R.

Then a connection string is defined, in my case using Windows Authentication. If you want to use SQL Server authentication the user name and password are needed.

We define a local folder as the compute context.

RxInSQLServer: generates a SQL Server compute context using SQL Server R Services – documentation

Sample query: I already prepared the dataset in the view, this is a best practice in order to reduce the size of the query in the R code and for me is also easier to maintain.

rxSQLServerData generates the data source object

Get some basic statistics and visualize the dataset

# Dataset summary

rxGetVarInfo(data = inDataSource)

rxSummary(~SUM_Profit, data = inDataSource)

# Plot the distribution of the profit

rxHistogram(~SUM_Profit, data = inDataSource, title = "Sum of the profit")

#Plot the distribution of the average unit price

rxHistogram(~AVG_UnitPrice, data = inDataSource, title = "Average unit price")

In both histograms you can easily identify outliers and we have a better understanding about the distribution of the data. Here is where R plays an important role (as a tool). This kind of analysis is not performed by many BI professionals, or at least this is what I have seen in my professional life.

Summary

In this post I demonstrated how we can get data into R client from SQL Server and perform some basic analysis over a simple dataset. What would be the next steps?

• Continue visualizing the data
• Create a machine learning model
• Integrate the R code in SQL Server using functions and stored procedure

References

Data Science End-to-End Walkthrough

Big Data Analysis with Revolution R Enterprise 

Export data to Hadoop using Polybase – Insert into external table

Introduction

This post is a continuation of Polybase Query Service and Hadoop – Welcome SQL Server 2016

One of the most interesting use cases of Polybase is the ability to store historical data from relational databases into a Hadoop File System. The storage costs could be reduced while keeping the data accessible and still can be joined with the regular relational tables. So let`s do the first steps for our new archiving solution.

Requirements

• SQL Server 2016 Developer (I suppose it works with higher editions) download
• Hadoop 2.7.1 (I built my own sources and also successfully tested with HDP 2.4)
• AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3
• Install and configure Polybase: Get started with Polybase

Create a folder for the pdw_user in Hadoop

Polybase use the default user name pdw_user when connecting to the Hadoop cluster. For this example, I will use an unsecured Hadoop, that is, a Hadoop cluster without Kerberos authentication. For production environments a better security approach should be used.

Open a command line session with administrator rights and issue the following commands:

Create a directory for the pdw_user:

C:\>hadoop fs -mkdir /user/pdw_user

Change the ownership of the directory:

C:\>hadoop fs -chown -R pdw_user /user/pdw_user

Verify the results using the command line:

Verify the results using the web browser:

In my case: http://localhost:50070/explorer.html#/user

You can name the directory whatever you want, important is to change the ownership to the pdw_user user.

Create an external data source and file format

Open a query window in management studio pointing to the AdventureworksDW2016CTP3 and run the following queries.

CREATE EXTERNAL DATA SOURCE HDP2 WITH
(
    TYPE = HADOOP,
    LOCATION = 'hdfs://localhost:9000'
)

CREATE EXTERNAL FILE FORMAT SalesExport WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS (
                    FIELD_TERMINATOR =';',
                    DATE_FORMAT = 'yyyy-MM-dd' ,
                    USE_TYPE_DEFAULT = TRUE
                           )
)

Create an external table

CREATE EXTERNAL TABLE HistoricalSales
(
    SalesOrderNumber nvarchar(20)
       ,SalesOrderLineNumber tinyint
       ,ProductName nvarchar(50)
       ,SalesTerritoryCountry nvarchar(50)
       ,OrderQuantity smallint
       ,UnitPrice money
       ,ExtendedAmount money
       ,SalesAmount money
       ,OrderDate date
)
WITH
(
    LOCATION = '/user/pdw_user',
    DATA_SOURCE = HDP2,
    FILE_FORMAT = SalesExport,
    REJECT_TYPE = value,
    REJECT_VALUE=0
)

The key point here is the location. It must point to a directory and not to a specific file like in my previous post. If the location does not exist It will be created.

Insert into external table

This example uses the Adventure Works DW database:

-- Enable INSERT into external table
sp_configure 'allow polybase export', 1;
reconfigure

-- Export data: Move old data to Hadoop while keeping it query-able via an external table.
INSERT INTO [dbo].[HistoricalSales]
 SELECT 
       [SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,p.EnglishProductName as ProductName
      ,st.SalesTerritoryCountry
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[SalesAmount]
      ,convert(date,[OrderDate]) AS [OrderDate]
  FROM [AdventureworksDW2016CTP3].[dbo].[FactInternetSales] a
  inner join dbo.DimProduct p on a.ProductKey = p.ProductKey
  inner join dbo.DimSalesTerritory st 
  on st.SalesTerritoryKey = a.SalesTerritoryKey
  where year(OrderDate) < 2011

Examining the results

Using the web browser:

Polybase export operation creates multiple files under the specified location.The external files are named QueryID_date_time_ID.format, where ID is an incremental identifier and format is the exported data format.

Select the exported data from the external table:

Conclusion

With this small tutorial I demonstrated how to use SQL Server 2016 and Hadoop to create a cost effective and functional archiving solution. There are still several other aspects to explain and be considered but we can start to build our proof of concepts, let’s get started.

References

PolyBase Queries

Apache Hadoop File System Shell Commands

Acknowledgments

Special thanks to Sumin Mohanan and Sonya Marshall from Microsoft to helped me to troubleshoot my tests.

Polybase Query Service and Hadoop – Welcome SQL Server 2016

Introduction

One of the coolest features of SQL Server 2016 is Polybase. Already available for Parallel Data Warehouse, this functionality is now integrated in SQL Server 2016 and allows to combine relational and non-relational data, for example, query data in Hadoop and join it with relational data, import external data into SQL Server or export data from the server into Hadoop or Azure Blob Storage. This last case is especially interesting since is possible to transfer old transactions or historical data to a Hadoop file system and dramatically reduce the storage costs.

Setup Polybase

I installed the following components:

• Windows Server 2012 R2
• SQL Server 2016 RC3 download
• Hadoop 2.7.1 (I built the sources by myself: here how to)
• SQL Server Management Studio 2016 download
• AdventureWorks sample databases download + install instructions

After install SQL Server enable TCP/IP connectivity:

Verify that the Polybase services are running:

Create an external data source

Open a connection to the AdventureworksDW2016CTP3

Polybase connectivity configuration:

sp_configure @configname = 'hadoop connectivity', @configvalue = 7;
GO
RECONFIGURE
GO

‘hadoop connectivity’ is the name of the configuration option. The @configvalue is the corresponding supported Hadoop data source. In my case I selected the 7 corresponding to Hortonworks 2.1, 2.2, and 2.3 on Windows Server. I am using my own Hadoop 2.7.1 and the Hortonworks version is HDP 2.3 and 2.4

More info here:

Hortonworks Products

Polybase Connectivity Configuration

Create external data source script:

CREATE EXTERNAL DATA SOURCE HDP2 WITH
(
    TYPE = HADOOP,
    LOCATION = 'hdfs://localhost:9000'
)

HADOOP is the external data source type and the location is the NameNode URI. You will find this value in <your Hadoop directory>\etc\hadoop\core-site.xml

Once the source is created you will find it under “External Data Sources” folder in Management Studio:

It is important to remark that the location is not validated when you create the external data source

Create a sample file for this example

Just for demo purposes, create a .csv file and populate it with a query from AdventureworksDW2016CTP3. This is just an example, you can create your own example and also change the file format in the next section accordingly.

Here my query:

SELECT TOP 1000
  [SalesOrderNumber]
 ,[SalesOrderLineNumber]
 ,p.EnglishProductName as ProductName
 ,st.SalesTerritoryCountry
 ,[OrderQuantity]
 ,[UnitPrice]
 ,[ExtendedAmount]
 ,[SalesAmount]
 ,convert(date,[OrderDate]) AS [OrderDate]
FROM [AdventureworksDW2016CTP3].[dbo].[FactInternetSales] a
inner join dbo.DimProduct p on a.ProductKey = p.ProductKey
inner join dbo.DimSalesTerritory st on st.SalesTerritoryKey = a.SalesTerritoryKey

I populated the csv file using Management Studio as follows:

Open the Export wizard: right click on the database name –> Tasks –> Export Data…

Select a data source

Choose a destination

Specify a query to select the data to export

Source query

Configure flat file destination

Save and run the package

Export done!

Transfer the csv to HDFS

I created a directory called input in my Hadoop file system and store the csv file in c:\tmp

In case you haven’t done before, to create a directory in HDFS open a command prompt, go to your Hadoop directory and type:

<Your_hadoop-directoy>hadoop fs -mkdir /input

Here is my shell command to move the file from windows file system to HDFS:

<Your_hadoop-directoy>hadoop fs -copyFromLocal c:\tmp\AWExport.csv /input/

Set read and write permissions for other members of your group and others:

<Your_hadoop-directoy>hadoop fs -chmod 777 /input/AWExport.csv

List files in the input directory:

<Your_hadoop-directoy>hadoop fs -ls /input

Create an external file format

To create a file format, in a query window in management studio copy and paste the following script:

CREATE EXTERNAL FILE FORMAT SalesExport WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS (
                FIELD_TERMINATOR =';',
                DATE_FORMAT = 'yyyy-MM-dd' ,
                USE_TYPE_DEFAULT = TRUE
                           )
)

SalesExport is just the name I gave.

The format type is delimited. There are some other types, more info here

The field terminator is the same I used when I exported the data to the flat file.

The date format corresponds also to the format in the flat file

Create an external table

This table references the file stored in HDFS (In my case AWExport.csv). The format corresponds to the structure of the file.

CREATE EXTERNAL TABLE SalesImportcsv
(
    SalesOrderNumber nvarchar(20)
   ,SalesOrderLineNumber tinyint
   ,ProductName nvarchar(50)
   ,SalesTerritoryCountry nvarchar(50)
   ,OrderQuantity smallint
   ,UnitPrice money
   ,ExtendedAmount money
   ,SalesAmount money
   ,OrderDate date
)
WITH
(
   LOCATION = '/input/AWExport.csv',
   DATA_SOURCE = HDP2,
   FILE_FORMAT = SalesExport,
   REJECT_TYPE = value,
   REJECT_VALUE=0
)

Location: location of the file in HDFS.

Data Source: the one created in a previous step.

File Format: also the one created in a previous step.

Reject type: the rejected value is a literal value and not a percentage (the other option is percentage).

Reject value: how many rows could fail. Fail means dirty records, in this context, when a value does not match the column definition.

MSDN Documentation

Query the external table

If everything works you should be able to see the external table in management studio. Then just right click and select the top 1000 records, for example:

Further Topics

• Insert records in an external table.
• Configure an external source with credentials.
• Build a SSIS package to import and export data from Hadoop.
• View the execution plans of the Polybase queries

References

• Polybase in SQL Server 2016 CTP 2

• Get started with PolyBase

• How to get started with PolyBase in SQL Server 2016

My experience building Hadoop 2.7.1 on Windows Server 2012

Introduction

Building the Hadoop sources on windows could be cumbersome even when the official documentation states: “… building a Windows package from the sources is fairly straightforward”. There are several good resources containing the steps needed in order to successfully build a distribution. The most useful for me was this one:

Hadoop 2.7.1 for Windows 10 binary build with Visual Studio 2015 (unofficial)

I solved most of the hurdles with the directions in this blog post (thanks Kplitz Kahran), but I still had to suffer a little bit more. In this post I will show you the additional details I needed to fix.

You can try with the information in the link above and if you have no luck this solutions could help you.

Build winutils project – error C2065: ‘L’: undeclared identifier

I order to solve this problem; I just rewrote this line of code:

const WCHAR* wsceConfigRelativePath = WIDEN_STRING(STRINGIFY(WSCE_CONFIG_DIR)) L”\\” WIDEN_STRING(STRINGIFY(WSCE_CONFIG_FILE));

as:

const WCHAR* wsceConfigRelativePath = STRINGIFY(WSCE_CONFIG_DIR) “\\” STRINGIFY(WSCE_CONFIG_FILE);

Basically the concatenation of these variables are converted explicit to wide character (WCHAR) using a macro. I tested the concatenation without this explicit conversion and it worked. I am not sure why is failing without this, if someone could explain it, I will really appreciate it.

Build native project – LINK Error to libwinutils

The libwinutils library is an external reference of the native project. Verify in the project properties, Linker section, that the “Additional Library Directories” includes the targets of the libwinutils project.

Here a Screencast with the steps above:

 

[ERROR] Failed to execute goal org.apache.maven.plugins:maven-antrun-plugin

After solving the previous errors, I thought I was the master of the universe until the next error damaged my enthusiasm again. Luckily the fix proposed by Rushikesh Garadade in this Stack Overflow thread solved the issue:

http://stackoverflow.com/questions/21752279/failed-to-execute-goal-org-apache-maven-pluginsmaven-antrun-plugin1-6-run-pr

After this, the build crashed again but fortunately was just a temporary network error. And finally the happiest image of the day:

Hope that helps.

References

Build and Install Hadoop 2.x or newer on Windows

Hadoop 2.7.1 for Windows 10 binary build with Visual Studio 2015 (unofficial)

Working with Strings

Apache Kafka 0.8 on Windows

A very helpful step-by-step tutorial to help us learn and play with modern technologies using our windows computer.

JanSchulte.com

Apache Kafka is a scalable, distributed messaging system, which is increasingly getting popular and used by such renowned companies like LinkedIn, Tumblr, Foursquare, Spotify and Netflix [1].

Setting up a Kafka development environment on a Windows machine requires some configuration, so I created this little step-by-step installation tutorial for all the people who want to save themselves from some hours work😉

View original post 1,497 more words

Apache Spark installation on Windows 10

 

Introduction

This post is to help people to install and run Apache Spark in a computer with window 10 (it may also help for prior versions of Windows or even Linux and Mac OS systems), and want to try out and learn how to interact with the engine without spend too many resources. If you really want to build a serious prototype, I strongly recommend to install one of the virtual machines I mentioned in this post a couple of years ago: Hadoop self-learning with pre-configured Virtual Machines or to spend some money in a Hadoop distribution on the cloud. The new version of these VMs come with Spark ready to use.

A few words about Apache Spark

Apache Spark is making a lot of noise in the IT world as a general engine for large-scale data processing, able to run programs up to 100x faster than Hadoop MapReduce, thanks to its in-memory computing capabilities. It is possible to write Spark applications using Java, Python, Scala and R, and it comes with built-in libraries to work with structure data (Spark SQL), graph computation (GraphX), machine learning (MLlib) and streaming (Spark Streaming).

Spark runs on Hadoop, Mesos, in the cloud or as standalone. The latest is the case of this post. We are going to install Spark 1.6.0 as standalone in a computer with a 32-bit Windows 10 installation (my very old laptop). Let’s get started.

Install or update Java

For any application that uses the Java Virtual Machine is always recommended to install the appropriate java version. In this case I just updated my java version as follows:

Start –> All apps –> Java –> Check For Updates

Update Java

 

In the same way you can verify your java version. This is the version I used:

 

Java Version

 

Download Scala

Download from here. Then execute the installer.

I just downloaded the binaries for my system:

Scala Download

 

 

Download Spark

Select any of the prebuilt version from here

As we are not going to use Hadoop it make no difference the version you choose. I downloaded the following one:

Spark Download

 

Feel free also to download the source code and make your own build if you feel comfortable with it.

Extract the files to any location in your drive with enough permissions for your user.

Download winutils.exe

This was the critical point for me, because I downloaded one version and did not work until I realized that there are 64-bits and 32-bits versions of this file. Here you can find them accordingly:

32-bit winutils.exe

64-bit winutils.exe

In order to make my trip still longer, I had to install Git to be able to download the 32-bits winutils.exe. If you know another link where we can found this file you can share it with us.

Git client download (I hope you don’t get stuck in this step)

Extract the folder containing the file winutils.exe to any location of your preference.

Environment Variables Configuration

This is also crucial in order to run some commands without problems using the command prompt.

• _JAVA_OPTION: I set this variable to the value showed in the figure below. I was getting Java Heap Memory problems with the default values and this fixed this problem.
• HADOOP_HOME: even when Spark can run without Hadoop, the version I downloaded is prebuilt for Hadoop 2.6 and looks in the code for it. To fix this inconvenient I set this variable to the folder containing the winutils.exe file
• JAVA_HOME: usually you already set this variable when you install java but it is better to verify that exist and is correct.
• SCALA_HOME: the bin folder of the Scala location. If you use the standard location from the installer should be the path in the figure below.
• SPARK_HOME: the bin folder path of where you uncompressed Spark

 

Environment Variables 1/2

Environment Variables 2/2

 

Permissions for the folder tmp/hive

I struggled a little bit with this issue. After I set everything I tried to run the spark-shell from the command line and I was getting an error, which was hard to debug. The shell tries to find the folder tmp/hive and was not able to set the SQL Context.

I look at my C drive and I found that the C:\tmp\hive folder was created. If not you can created by yourself and set the 777 permissions for it. In theory you can do it with the advanced sharing options of the sharing tab in the properties of the folder, but I did it in this way from the command line using winutils:

Open a command prompt as administrator and type:

Set 777 permissions for tmp/hive

 

Please be aware that you need to adjust the path of the winutils.exe above if you saved it to another location.

We are finally done and could start the spark-shell which is an interactive way to analyze data using Scala or Python. In this way we are going also to test our Spark installation.

Using the Scala Shell to run our first example

In the same command prompt go to the Spark folder and type the following command to run the Scala shell:

 

Start the Spark Scala Shell

 

After some executions line you should be able to see a similar screen:

Shell started

 

You are going to receive several warnings and information in the shell because we have not set different configuration options. By now just ignore them.

Let’s run our first program with the shell, I took the example from the Spark Programming Guide. The first command creates a resilient data set (RDD) from a text file included in the Spark’s root folder. After the RDD is created, the second command just counts the number of items inside:

Running a Spark Example

 

And that’s it. Hope you can follow my explanation and be able to run this simple example. I wish you a lot of fun with Apache Spark.

References

Why does starting spark-shell fail with NullPointerException on Windows?

Apache Spark checkpoint issue on windows

Configure Standalone Spark on Windows 10

Business Intelligence without excuses part 1 – Business Analytics Platform Installation

Disclaimer

This first tutorial is part of a series that I’m planning in order to show how to use Pentaho to build BI applications. The expected audience is people without previous knowledge about Pentaho, for this reason I decided to start from the very beginning. I think and hope that students or professional who want to step into BI will find these tutorials useful.

For experienced Pentaho users I recommend this article to catch on what’s new in BA Server 5.0 CE: A first look to the new Pentaho BA Server 5.0 CE 

Introduction

The renamed Pentaho Analytic Platform is the central component to host the content of our BI application. From the platform it is possible to run and show reports and dashboards, manage security, perform OLAP analysis and many other tasks.

All Pentaho software, except the Pentaho Mobile App, requires the Sun/Oracle version 1.7 distribution of the Java Runtime Environment (JRE) or Java Development Kit (JDK), therefore is essential that Java is installed and at least the variable JRE_HOME or JAVA_HOME should be configured. I show how to set the JAVA_JOME system variable in a Windows environment.

As I mentioned in the first post of this series, the first step is to download the BA Server from:

Pentaho Community 

Pentaho BA Server CE 5.0 installation 

Plugins Installation

Using the Marketplace plugin (comes with the default installation) it is possible to install other useful plugins, which are going to be used to design dashboards, perform OLAP analysis, etc.

Users and Roles

The default installation comes with a set of users and their respective directories. The users with the admin role can see all of the directories. There is also a “Public” folder, where the examples showed in the screencast above are stored.

 

Summary

The Pentaho Business Analytics Platform hosts Pentaho-created and user-created content. It is open source and could easily download and install.  If you are a developer, specially a Java developer, I encourage you to dive and study how is build the whole platform, understand the architecture behind, and why not, to collaborate with the community.

In future posts I will examine in detail some important features and characteristics of the Server