Unable to connect Excel 2016 for MAC to Hive

[#1]

Now the hive ODBC driver can be installed in OS X 10.11, however, Excel 2016 for MAC still can not connect. My DSN was tested successfully with iodbctest, however, Excel failed to connect with error message "IM003 [iODBC][Driver Manager]Specified driver could not be loaded" and "00000 [iODBC][Driver Manager]dlopen(/opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib, 6): no suitable image found. Did find /opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib: open() failed with errno=1"

[#2]

was this Sandbox? local? server?

Also did you set the configuration files?

http://hortonworks.com/hadoop-tutorial/how-to-install-and-configure-the-hortonworks-odbc-driver-on-m...

[#3]

This was the Sandbox. I followed the same instruction.

[#4]

Can you post the contents of your configuration files and hosts. I had the problem on Mac that the files were ROOT read only and that the sandbox did not have permissions. Can you access other items on the Sandbox remote? Sometimes anti-virus will block ports. Can you access from ambari?

[#5]

I am experiencing the same problem when following the tutorial at http://hortonworks.com/hadoop-tutorial/how-to-install-and-configure-the-hortonworks-odbc-driver-on-m... . I am using Sandbox v2.4.

The data source test in iODBC Administrator reports "The connection DSN was tested successfully, and can be used at this time", but when I test it under Excel 15.21 I get the same error.

[#6]

It seems like an abrupt ending to this topic. It seems like I was stuck in a similar problem, however, it looks more like and excel and configuration issue rather than the iODBC manager itself.

I tried testing this via isql and it worked:

HW13382:~ srai$ isql "Sample Hortonworks Hive DSN"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show tables
+-------------------------------------------------
| tab_name                                        
+-------------------------------------------------
| sample_07                                       
| sample_08                                       
| testtable                                       
| yahoo_orc_table                                 
+-------------------------------------------------
SQLRowCount returns -1
4 rows fetched
SQL>

Whereas, working with the same code, shows precisely the same error message as reported by Sean:

00000 [iODBC][Driver Manager]dlopen(/opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib, 6): no suitable image found. Did find /opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib: open() failed with errno=1"

At least, its clear that the problem is not with the driver, but looks more like an excel/office issue.

[#7]

My version details:

OSX El Capitan 10.11.3

[root@sandbox ~]# hadoop version
Hadoop 2.7.1.2.4.0.0-169
Subversion git@github.com:hortonworks/hadoop.git -r 26104d8ac833884c8776473823007f176854f2eb
Compiled by jenkins on 2016-02-10T06:18Z
Compiled with protoc 2.5.0
From source with checksum cf48a4c63aaec76a714c1897e2ba8be6
This command was run using /usr/hdp/2.4.0.0-169/hadoop/hadoop-common-2.7.1.2.4.0.0-169.jar

Dltest also returns successful.

HW13382:~ srai$ dltest /opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib
SUCCESS: Loaded /opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib

File command also displays that its a shared library which supports i386 and x86_64

HW13382:~ srai$ file /opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib
/opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib: Mach-O universal binary with 2 architectures
/opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib (for architecture i386):	Mach-O dynamically linked shared library i386
/opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib (for architecture x86_64):	Mach-O 64-bit dynamically linked shared library x86_64

[#8]

copy your driver to /usr/lib will work

[#9]

I guess I'd have to disable mac security to copy anything under /usr/lib. Thanks, will try it out.

[#10]

copy your driver to /usr/lib will work

[#11]

Tried that one, didn't work. I am trying to use system DSN by the way, however, I do not suppose it really matters. The error is the same, it looks like it didn't even try to look at the new file.

[#12]

Did you set Driver=/usr/lib/libhortonworkshiveodbc.dylib in your ODBC configuration.

[#13]

Another alternative is to run excel without sandboxing:

http://stackoverflow.com/questions/9365861/how-can-i-run-a-sandboxed-os-x-app-without-sandboxing

[#14 · Accepted solution]

I ran into this same problem with Excel 2016 on El Capitan. Jiaxing Liang is right in that OS X's sandboxing is blocking access to libhortonworkshiveodbc.dylib. You can verify the same by launching /Applications/Utilities/Console and filtering on 'sandboxd'. The Activity Monitor can also display if sandboxing is enabled. View -> Columns -> Sandbox.

As a work around I copied the Hortonworks Hive ODBC driver from the default install location of /opt/hortonworks to /Library/ODBC/hortonworks. I then updated the odbc.ini and odbcinst.ini files to reference the new driver location.

# Driver: The location where the ODBC driver is installed to. Driver=/Library/ODBC/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib

The ErrorMessagesPath in the hortonworks.hiveodbc.ini files was being blocked too so that needed updating as well.

ErrorMessagesPath=/Library/ODBC/hortonworks/hiveodbc/ErrorMessages/

[#15]

Thanks, @bschofield!

[#16]

I should add my odbc.ini and odbcinst.ini files are under /Library/ODBC too. I am using System DSNs only.

[#17]

thanks @bschofield, it worked like a charm

[#18]

Thank you! @bschofield

[#19]

Thank you @bschofield that worked like charm

[#20]

I'm having the same problem. I followed the tutorial you linked, and the data source didn't show up in the Data Source Selection box until I moved the config files into /Library/ODBC. Once there, the data source appears as a system DSN. The data source test in iODBC Administrator reports "The connection DSN was tested successfully, and can be used at this time", but when I test it under Excel I get the same error Sean Sun is getting. This is in an attempt to contact the sandbox. I'm using the same version of OS X and Excel he's using.

odbc.ini:

[ODBC]

# Specify any global ODBC configuration here such as ODBC tracing.

[ODBC Data Sources]

Sample Hortonworks Hive DSN=Hortonworks Hive ODBC Driver

[Sample Hortonworks Hive DSN]

# Description: DSN Description.

# This key is not necessary and is only to give a description of the data source.

Description=Hortonworks Hive ODBC Driver DSN

# Driver: The location where the ODBC driver is installed to.

Driver=/opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib

# When using No Service Discovery, specify the IP address or host name of the Hive server.

# When using ZooKeeper as the Service Discovery Mode, specify a comma-separated list of ZooKeeper

# servers in the following format:

# <zk_host1:zk_port1>,<zk_host2:zk_port2>,...

#HOST=192.168.56.101

HOST=localhost

# The TCP port Hive server is listening. This is not required when using ZooKeeper as the service

# discovery mode as the port is specified in the HOST connection attribute.

PORT=10000

# The name of the database schema to use when a schema is not explicitly specified in a query.

Schema=default

# Set to 0 to when connecting directory to Hive Server 2 (No Service Discovery).

# Set to 1 to do Hive Server 2 service discovery using ZooKeeper.

# Note service discovery is not support when using Hive Server 1.

ServiceDiscoveryMode=0

# The namespace on ZooKeeper under which Hive Server 2 znodes are added. Required only when doing

# HS2 service discovery with ZooKeeper (ServiceDiscoveryMode=1).

ZKNamespace=

# Set to 1 if you are connecting to Hive Server 1. Set to 2 if you are connecting to Hive Server 2.

HiveServerType=2

# The authentication mechanism to use for the connection.

# Set to 0 for No Authentication

# Set to 1 for Kerberos

# Set to 2 for User Name

# Set to 3 for User Name and Password

# Note only No Authentication is supported when connecting to Hive Server 1.

AuthMech=2

# The Thrift transport to use for the connection.

#Set to 0 for Binary

#Set to 1 for SASL

#Set to 2 for HTTP

# Note for Hive Server 1 only Binary can be used.

ThriftTransport=1

# When this option is enabled (1), the driver does not transform the queries emitted by an

# application, so the native query is used.

# When this option is disabled (0), the driver transforms the queries emitted by an application and

# converts them into an equivalent from in HiveQL.

UseNativeQuery=0

# Set the UID with the user name to use to access Hive when using AuthMech 2 to 8.

UID=sandbox

# The following is settings used when using Kerberos authentication (AuthMech 1 and 10)

# The fully qualified host name part of the of the Hive Server 2 Kerberos service principal.

# For example if the service principal name of you Hive Server 2 is:

# hive/myhs2.mydomain.com@EXAMPLE.COM

# Then set KrbHostFQDN to myhs2.mydomain.com

KrbHostFQDN=[Hive Server 2 Host FQDN]

# The service name part of the of the Hive Server 2 Kerberos service principal.

# For example if the service principal name of you Hive Server 2 is:

# hive/myhs2.mydomain.com@EXAMPLE.COM

# Then set KrbServiceName to hive

KrbServiceName=[Hive Server 2 Kerberos service name]

# The realm part of the of the Hive Server 2 Kerberos service principal.

# For example if the service principal name of you Hive Server 2 is:

# hive/myhs2.mydomain.com@EXAMPLE.COM

# Then set KrbRealm to EXAMPLE.COM

KrbRealm=[Hive Server 2 Kerberos realm]

# Set to 1 to enable SSL. Set to 0 to disable.

SSL=0

# Set to 1 to enable two-way SSL. Set to 0 to disable. You must enable SSL in order to

# use two-way SSL.

TwoWaySSL=0

# The file containing the client certificate in PEM format. This is required when using two-way SSL.

ClientCert=

# The client private key. This is used for two-way SSL authentication.

ClientPrivateKey=

# The password for the client private key. Password is only required for password protected

# client private key.

ClientPrivateKeyPassword=

odbcinst.ini:

[ODBC Drivers]

Hortonworks Hive ODBC Driver=Installed

[Hortonworks Hive ODBC Driver]

Description=Hortonworks Hive ODBC Driver

Driver=/opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib

hortonworks.hiveodbc.ini:

[Driver]

ErrorMessagesPath=/opt/hortonworks/hiveodbc/ErrorMessages/

LogLevel=0

LogPath=

SwapFilePath=/tmp

[#21]

I can confirm that the work around does enable applications/tools like Excel and Tableau to function normally.