Microsoft Excel features calculations, graphing tools, pivot tables, and a macro programming language that allows users to work with data in many of the ways that suit their needs, whether on a Windows machine or a Macintosh machine. This article walks through creating a DSN for Apache Hive data in iODBC and accessing Apache Hive data in Microsoft Excel, all on a machine running Mac OS X. Installing the CData ODBC Drivers on Mac OS X The CData ODBC Driver for Apache Hive is preconfigured for the iODBC driver manager, as are many other products like Microsoft Excel. This makes the driver easy to use with these tools. Licensing the Driver In a terminal run the following commands to license the driver.
To activate a trial, omit the key input. Cd '/Applications/CData ODBC Driver for Salesforce/bin' sudo./install-license Creating a DSN for iODBC Installing the ODBC Driver for Apache Hive registers the driver with the iODBC driver manager, so the only remaining step is to use the iODBC Administrator to supply the necessary connection properties in a DSN. The driver installation automatically creates a User DSN. However, modifying a system DSN requires elevated permissions. Open a terminal and enter the following command to start the iODBC Administrator with the necessary permissions: sudo /Applications/iODBC/iODBC Administrator.app/Contents/MacOS/iODBC Administrator. On the Users tab, select CData Hive Source. Click the Add button to enter connection properties as key-value pairs.
Jump to Configure the Hive ODBC Connector Driver on Mac OS X - Installed ODBC driver manager. For Apache Hive shared libraries. The MapR Hive ODBC Connector is an ODBC driver for Apache Hive that complies with the ODBC 3.52 specification. To use the ODBC driver, configure a Data Source Name (DSN), a definition that specifies how to connect to Hive.
Set the Server, Port, TransportMode, and AuthScheme connection properties to connect to Hive. If you wish to authenticate using OAuth, you will need to add an additional key-value pair to the DSN to ensure that the OAuth flow can execute properly: Keyword Value Other CheckPromptMode=False Accessing Apache Hive Data from Microsoft Excel You can use the DSN configured above to access Apache Hive data from Microsoft Excel.
Open Microsoft Excel and open a spreadsheet (new or existing). Navigate to the data ribbon and select New Database Query From Database. Select the User or System DSN that you previously configured and click OK. Build your SQL query in the Microsoft Query wizard:.
Click Return Data to execute the query and pull data into Excel. Using the CData ODBC Driver for Apache Hive, you can easily pull your Apache Hive data directly into Excel. Once there, you can leverage all of the powerful features native to Excel to analyze, report, transform your Apache Hive data, and more!
Important: As of August 2016, Excel 2016 for the Mac was updated from 32-bit software to 64-bit software. To see if you have this update, click the Excel menu About Excel. If you have version 15.25 or later, you have the 64-bit version. The 64-bit update helps Excel run faster, and makes it more reliable when working with very large files. However, if you are using a 32-bit ODBC driver from one of the companies listed above, it may cause Excel to crash when connecting. To avoid this, install a 64-bit driver from the company’s website.