Simplest way to install and configure Hive for Mac OSX Lion :

I have till date not found a simple way to install Hive with MySql and not derby as its metastore in Mac, so I decided to brainstorm and figure it out myself.

Prerequisites :
1. You should have already installed Hadoop. Check my previous post on Hadoop install for help.
2. You should have brew which can be installed by :

ruby -e "$(curl -fsSkL raw.github.com/mxcl/homebrew/go)

Steps :
1. Install hive via brew. This will take some time

$ brew install hive

2. Add hadoop and hive to your path by editing your bash_profile
Note : If you do not have a bash_profile, do not worry just follow the steps below and it will create a new file for you. If you do have one already then, just add the lines to your existing file

vim ~/.bash_profile

Now add the following lines to bash_profile

export HADOOP_HOME=/usr/local/Cellar/hadoop/hadoop.version.no
export HIVE_HOME=/usr/local/Cellar/hive/hive.version.no/libexec

Save and compile your bash_profile

source ~/.bash_profile

3. Download the mysql connector
Note :The version might change over time. Please go to this link and download the mysql-connector-java file directly

$ curl -L 'http://www.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.22.tar.gz/from/http://mysql.he.net/' | tar xz

Copy the jar to your hive lib folder

$ sudo cp mysql-connector-java-5.1.15/mysql-connector-java-5.1.22-bin.jar /usr/local/Cellar/hive/hive.version.no/libexec/lib/

4. Create the mysql metastore

$ mysql
mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> CREATE USER 'hiveuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE ON metastore.* TO 'hiveuser'@'localhost';

5. Copy the hive-default-xml template as hive-site.xml
Note :Please replace hive.version.no with respective hadoop and hbase version downloaded using brew.

$ cd /usr/local/Cellar/hive/hive.version.no/libexec/conf
$ cp hive-default.xml.template hive-site.xml

6. Add/Edit the following lines in your hive-site.xml

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost/metastore</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hiveuser</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>password</value>
</property>
<property>
  <name>datanucleus.fixedDatastore</name>
  <value>false</value>
</property>

7. Test if hive works

$ hive;
hive > show tables;
hive> create table temp_table temp_col string;

No error should occur in the above steps

8. Revoke few permissions on the mysql metastore

$ mysql
mysql> REVOKE ALTER,CREATE ON metastore.* FROM 'hiveuser'@'localhost';

9. Further troubleshooting :
(a) If you get a bin log error saying statement format is not support. Login to your mysql console as root

$ mysql -uroot
mysql > SET GLOBAL binlog_format = 'ROW';

(b) You could also try reading the logs as follows. Logs can be emitted to the bash prompt while running hive by setting hive.root.logger to INFO,console.

$ hive -hiveconf hive.root.logger=INFO,console

(c)You could also read the raw hive logs which is usually located at /tmp/user_name/hive.log

(d)If you still have any errors, feel free to comment.