Using Hive Metastore on Mac

I used 'brew' to install mysql on the mac.  Once installed and if you follow the directions on the HDP site about manually configuring it for the Hive Metastore, you may run into an issue when you try to start the metastore.

If you see the following errors in your Hive Metastore start like: GOTO THE UPDATE AT THE BOTTOM...

Index column size too large. The maximum column size is 767 bytes.

This is an issue with the 'innodb' engine and the charset used to create some additional tables in the hive database.  You can try to set things like: innodb_large_prefix as you'll most likely find on the web as a common solution.  This didn't work for me.

Instead, I manually created the 'additional' tables and 'indexes' that error out during the hive metastore startup.  To startup the metastore manually and see the errors:

./hive --service metastore -hiveconf hive.root.logger=DEBUG,console

In the resulting log, you'll set many create statements.  Copy them from the log and start a mysql session as the "hive" database owner.  Go into the 'hive' database and manually run the create statements while adding this to the end of each.

DEFAULT CHARSET=latin1

For example:

CREATE TABLE `TABLE_PARAMS`
(
    `TBL_ID` BIGINT NOT NULL,
    `PARAM_KEY` VARCHAR(256) BINARY NOT NULL,
    `PARAM_VALUE` VARCHAR(4000) BINARY NULL,
    CONSTRAINT `TABLE_PARAMS_PK` PRIMARY KEY (`TBL_ID`,`PARAM_KEY`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

Restart the Hive Metastore and repeat until the all the creation errors are gone.

UPDATE:

Follow the instructions for setting up the database and user account in the database setup section.  Create the database and run the following command BEFORE launching the megastore:

alter database hive character set latin1;

This will set the default CHARSET for the database and will be applied when the metastore creates the tables it needs.