This tutorial will provide step by step instruction to setup Hive on Mac OS.
Download Apache Hive
$HOME/hadoop/
$ hadoop version
Output: Hadoop 3.0.3
## HIVE env variables
export HIVE_HOME=$HOME/hadoop/apache-hive-3.0.0-bin
export PATH=$PATH:/$HIVE_HOME/bin
## MySQL ENV
export PATH=$PATH:/usr/local/mysql-8.0.12-macos10.13-x86_64/bin
export PATH=$PATH:/usr/local/mysql-8.0.12-macos10.13-x86_64/bin
mysql -u root -p
CREATE DATABASE metastore;
use metastore;
SOURCE [$HOME path]/hadoop/apache-hive-3.0.0-bin/scripts/metastore/upgrade/mysql/hive-schema-3.0.0.mysql.sql
CREATE USER 'hiveuser'@'%' IDENTIFIED BY 'hivepassword';
GRANT all on metastore.* to hiveuser;
FLUSH PRIVILEGES;
cd $HOME/hadoop
schematool -dbType derby -initSchema
Error: FUNCTION 'NUCLEUS_ASCII' already exists. (state=X0Y68,code=30000)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
Underlying cause: java.io.IOException : Schema script failed, errorcode 2
Use --verbose for detailed stacktrace.
*** schemaTool failed ***
$HOME/hadoop/apache-hive-3.0.0-bin/scripts/metastore/upgrade/derby/hive-schema-3.0.0.derby.sql
And run the below command again
schematool -dbType derby -initSchema
$HOME/hadoop/apache-hive-3.0.0-bin/lib
export HADOOP_HOME=[$HADOOP_HOME path]
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<configuration>
<property>
<name>hive.querylog.location</name>
<value>[$HIVE_HOME PATH]/log/hive.log</value>
<description>Location of Hive run time structured log file</description>
</property>
<property>
<name>hive.querylog.enable.plan.progress</name>
<value>false</value>
<description>
Whether to log the plan's progress every time a job's progress is checked.
These logs are written to the location specified by hive.querylog.location
</description>
</property>
<property>
<name>hive.log.explain.output</name>
<value>false</value>
<description>
Whether to log explain output for every query.
When enabled, will log EXPLAIN EXTENDED output for the query at INFO log4j log level
and in WebUI / Drilldown / Show Query.
</description>
</property>
<!-- Properties for MySql as Metastore -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true&useSSL=false</value>
<description>metadata is stored in a MySQL server</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.cj.jdbc.Driver</value>
<description>MySQL JDBC driver class</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
<description>user name for connecting to mysql server</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hivepassword</value>
<description>password for connecting to mysql server</description>
</property>
<!-- Properties to use Hive by Spark -->
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
<description>connection from Spark</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>hdfs://localhost:9000/user/hive/warehouse</value>
<description>Warehouse Location</description>
</property>
</configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:$HOME/hadoop/metastore_db;create=true </value>
<description>JDBC connect string for a JDBC metastore </description>
</property>
Create warehouse folder under hive and provide permission
hadoop fs -mkdir -p /user/hive/warehouse
hadoop fs -chmod g+w /user/hive/warehouse
Create tmp folder in root and provide permission
hadoop fs -mkdir -p /tmp
hadoop fs -chmod g+w /tmp
hadoop fs -mkdir -p /tmp/hive
hadoop fs -chmod 777 /tmp/hive
$ hive
hive> show databases;
OK
default
FAILED: HiveException java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
hive --hiveconf hive.root.logger=INFO,console
hive> show databases;
Note: Hive console in above mode will show exact error why Hive is failing.
java.sql.SQLException: The server time zone value 'PDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
Then change the below value in hive-site.xml
FROM: jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true&useSSL=false
TO: jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true&useSSL=false&serverTimezone=UTC