Peanut Butter & Chocolate: Integrating Hadoop with SQL Server
WARNING!
This presentation is now obsolete. The information in this talk has not been updated in a while and could be outdated. Use the information in this presentation at your own risk and be sure to double-check things to ensure that any information you use is accurate.ABSTRACT
So you jumped on the bandwagon and set up a Hadoop cluster--but now what? Your database developers and app developers know how to integrate with and develop against SQL Server, but the Hadoop world can be a completely different experience. This talk will help bridge the gap between SQL Server and Hadoop, using tools such as SQL Server Integration Services and Sqoop to migrate data between a Hadoop cluster and a SQL Server instance, as well as PolyBase to integrate the two like never before.ADDITIONAL MEDIA
On August 16, 2017, I gave a version of this talk at NDC Sydney. You can get the recording on the NDC Youtube channel.
SLIDES
Click here to access the slides for this presentation.
The slides are licensed under Creative Commons Attribution-ShareAlike.
DEMO CODE
Click here to access demo code for this presentation. This includes a various T-SQL and Hive scripts, an SSIS project, and an F# project
The source code is licensed under the terms offered by the GPL.
LINKS & FURTHER INFO
Basic Links
- To follow along with the presentation, grab the Hortonworks HDP sandbox and SQL Server 2016.
- As you install SQL Server, you'll want to select the Polybase option and then perform some additional configuration.
- You will also want to install the Microsoft JDBC driver for SQL Server on your Hadoop cluster. For help installing the driver, Hortonworks has a tutorial, but it's out of date in one aspect: you will need to put the driver into /usr/hdp/[version]/sqoop/lib instead of /usr/lib/sqoop/lib.
- I am indebted to Abraham Elmahrek for his discussion of Sqoop security. Be sure to read the comments, as there is a great one on using the Hadoop credential store.
- To integrate Hive with SQL Server (for SSIS and linked servers), install the Microsoft Hive ODBC driver on your SQL Server instance. Tyler Chessman has a good prep article, as does Hilmar Buchta. If you're running a linked server query and get "Requested conversion is not supported." errors, your ODBC connection might not be set up correctly; specifically, go to the Advanced Options and set string length to 8000 or less.
- Should you wish to integrate Sqoop with Windows authentication, there is a (for-pay) DataDirect driver which enables this. You can also try setting up Active Directory over Kerberos on Linux.
- For SSIS integration, download SQL Server Data Tools for Visual Studio 2015, specifically the version which supports SQL Server 2016.
- Mariner has a very quick overview of Polybase. James Serra has a nice article, though it's a bit dated (as it came out in 2014). Robert Sheldon also has a nice intro.
- At this time, it appears that Polybase has limited collation support. The default US collation worked fine for me, though.
- There is also a good MSDN article on getting started with Polybase.
- You might also want to see my series on Polybase.
Data Sets
- I used the AdventureWorks 2014 database as well as a locally created database, OOTP.
- My OOTP player ratings are in my Github repo, along with the code.