Can’t connect Excel to Hive using ODBC driver on MAC

So you done everything right and can’t connect Excel to Hive using ODBC driver on your macOS?

Let’s see what is going on. Are you running El Capitan on Sierra? Well I was running Sierra and tried connecting before while running on El Capitan. Some of my research lead to this, and turns out it indeed was a problem of mac sandbox security. I could see those “deny file-data-read” violations!  🙄

SandboxViolation: Microsoft Excel(12403) deny file-read-data /opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib
Violation:       deny file-read-data /opt/hortonworks/hiveodbc/lib/universal/libhortonworkshiveodbc.dylib
Process:         Microsoft Excel [12403]
Path:            /Applications/Microsoft Excel.app/Contents/MacOS/Microsoft Excel
Load Address:    0x1076d8000
Identifier:      com.microsoft.Excel
Version:         15.24.16070700 (15.24)
Code Type:       x86_64 (Native)
Parent Process:  launchd [1]
Responsible:     /Applications/Microsoft Excel.app/Contents/MacOS/Microsoft Excel [12403]
User ID:         502

Date/Time:       2016-08-08 10:38:43.576 CDT
OS Version:      Mac OS X 10.12 (16A270f)

Finally ended up moving my data

sudo cp -R /opt/hortonworks /Library/ODBC/

and setting up odbc.ini and odbcinst.ini files to reference the new driver location.

sudo vi /Library/ODBC/odbcinst.ini

sudo vi /Library/ODBC/hortonworks/hiveodbc/lib/universal/hortonworks.hiveodbc.ini

Eh, no, it didn’t work for me with just those two files. So I looked up for robin-mac:~$ sudo find / -name *odbc*ini -type f and changed every reference of /opt/hortonworks to /Library/ODBC/hortonworks

And finally, everything was just fine!

References

1. http://hortonworks.com/hadoop-tutorial/how-to-install-and-configure-the-hortonworks-odbc-driver-on-mac-os-x/
2. https://community.hortonworks.com/questions/25738/unable-to-connect-excel-2016-for-mac-to-hive.html