Book Image

Microsoft SQL Server 2012 with Hadoop

By : Debarchan Sarkar
Book Image

Microsoft SQL Server 2012 with Hadoop

By: Debarchan Sarkar

Overview of this book

With the explosion of data, the open source Apache Hadoop ecosystem is gaining traction, thanks to its huge ecosystem that has arisen around the core functionalities of its distributed file system (HDFS) and Map Reduce. As of today, being able to have SQL Server talking to Hadoop has become increasingly important because the two are indeed complementary. While petabytes of unstructured data can be stored in Hadoop taking hours to be queried, terabytes of structured data can be stored in SQL Server 2012 and queried in seconds. This leads to the need to transfer and integrate data between Hadoop and SQL Server. Microsoft SQL Server 2012 with Hadoop is aimed at SQL Server developers. It will quickly show you how to get Hadoop activated on SQL Server 2012 (it ships with this version). Once this is done, the book will focus on how to manage big data with Hadoop and use Hadoop Hive to query the data. It will also cover topics such as using in-memory functions by SQL Server and using tools for BI with big data. Microsoft SQL Server 2012 with Hadoop focuses on data integration techniques between relational (SQL Server 2012) and non-relational (Hadoop) worlds. It will walk you through different tools for the bi-directional movement of data with practical examples. You will learn to use open source connectors like SQOOP to import and export data between SQL Server 2012 and Hadoop, and to work with leading in-memory BI tools to create ETL solutions using the Hive ODBC driver for developing your data movement projects. Finally, this book will give you a glimpse of the present day self-service BI tools such as Excel and PowerView to consume Hadoop data and provide powerful insights on the data.
Table of Contents (12 chapters)

Configuring the SQL Linked Server to Hive

Since Hive tables are exposed only through ODBC, there is no direct way to connect an Analysis Services database to Hadoop as Analysis Services can only connect to OLE DB compliant data sources. To address this, the only way is to create a Linked Server in a SQL Server instance using Hive ODBC driver and consume it through OLE DB for ODBC. We will reuse the DSN HadoopOnLinux that we created during our earlier chapters to connect to Hive.

To create the Linked Server, perform the following steps:

  1. Connect to the SQL Server instance using SQL Server Management Studio and expand the Server Objects as shown in the following screenshot:

  2. Right-click on Linked Servers and choose New Linked Server. This should bring up the New Linked Server window as shown in the following screenshot:

  3. You have to fill in the details of the Hive Data source that you would like connect to. In this case, I have named the Linked Server as LINKTOHIVE. You have to choose Microsoft OLE...