Troubleshoot Hive ODBC Connections in Excel

1Microsoft has deprecated the Hive plug-in for Excel (which is a little disappointing—it was never production ready, but it did offer some really nice functionality). If you download and install the latest driver the Hive plugin will actually go away. So I started attempting to use the connection via ODBC in Excel.

So, I configured a new System DSN in ODBC settings using the default Hive driver and port. This is a local HDInsight installation—I’m still having issues connecting to my Linux cluster, but will work that out in a separate post.

2

 

 

 

 

 

 

 

 

I accept the default port which is 10000. This has worked in the past with HDInsight (I thought—then I reread my documentation). One thing to note—there is no “Test Connection” button in the DSN configuration, there is one in Excel, but not here. If anyone from Microsoft is reading this, a connection test would be a really nice feature to include in the next revision of the driver.

 

3

Once in Excel, I go through the process of creating a new ODBC connection though the Data Connection Wizard.

4

 

5

The wizard finds my connection called “hive”. However, a Data Link properties box is launched when the connection is unable to be made directly. If I execute a “Test Connection” I get the error batch size is not set or invalid.

6

That error was pretty cryptic—fortunately I had tracing turned on for the connection. The log revealed the below:

[DEBUG]-> ThreadID 10716 # 08/15/13 09:31:51 # src\Conn.c # SQLDriverConnectW # 1065 # DB = default, HOST = 10.10.50.111, PORT = 10001, PATH = , FRAMED = 0

[ERROR]-> ThreadID 10716 # 08/15/13 09:31:52 # src\Conn.c # ConnectToHive # 346 # 08001 : Could not connect client socket. Details: <Host: 10.10.50.111 Port: 10001> endpoint: 10.10.50.111, error: connection_refused

[ERROR]-> ThreadID 10716 # 08/15/13 09:31:52 # src\Conn.c # ConnectToHive # 348 # 08001 : Unable to establish connection with hive server

When I googled the error, I found this post which suggested pointing to port 10001 instead of the default 10000. SoI fixed it—changing port to 10001.

7

 

8

 

9

 

10

And success—we have Hive data into Excel and we can load into Power Pivot if needed. However, my one bit of disappointment with the Hive ODBC driver compared the Hive plugin for Excel is that there is no interactive query mode, it’s pretty much an all or nothing proposition. Ideally, I would like to see that functionality return into the driver.

About jdanton1
A DBA, cyclist, cook (who likes to play chef occasionally)

2 Responses to Troubleshoot Hive ODBC Connections in Excel

  1. Mark Kromer says:

    Nice job, Joe! I actually ran into this today and forgot to change the port for HD Insight’s Hive connector! Best, Mark

  2. Ernesto says:

    I read a lot of interesting content here.
    Probably you spend a lot of time writing, i know how to save you a lot of time,
    there is an online tool that creates high quality, SEO friendly posts in minutes,
    just type in google – masagaltas free content

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: