Hive: Metastore Configuration

Metastore Conf

All the metadata for Hive tables and partitions are stored in Hive Metastore.

there are 3 different ways to setup metastore server using different Hive configurations:

Embedded Metastore

An embedded metastore is mainly used for unit tests. Only one process can connect to the metastore at a time, so it is not really a practical solution but works well for unit tests.Derby is the default database for the embedded metastore.

Local Metastore

In local metastore setup, each Hive Client will open a connection to the datastore and make SQL queries against it. The following config will set up a metastore in a MySQL server. Make sure that the server is accessible from the machines where Hive queries are executed since this is a local store. Also make sure the jdbc client library is in the classpath of Hive Client.

Remote Metastore

In remote metastore setup, all Hive Clients will make a connection to a metastore server which in turn queries the datastore (MySQL in this example) for metadata. Metastore server and client communicate using Thrift Protocol.

Starting with Hive 0.5.0, you can start a Thrift server by executing the following command:

#hive --service metastore

In my situation, I configure the remote metastore mode

/path/to/hive-0.12.0-bin/conf/hive-site.xml

<property>
  <name>hive.metastore.uris</name>
  <value>thrift://192.168.122.1:9083</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://192.168.122.1/hivedb?createDatabaseIfNotExist=true</value>
  <description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
  <description>Driver class name for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hive</value>
  <description>username to use against metastore database</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>hive123</value>
  <description>password to use against metastore database</description>
</property>

<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>/user/hive/warehouse</value>
  <description>location of default database for the warehouse</description>
</property>

#hive --service metastore
#hive --service metastore -p <port_num>

You should create user 'hive' and grant privileges to him in mysql datatbase;

If you are using MySQL as the datastore for metadata, put MySQL client libraries in HIVE_HOME/lib before starting Hive Client or HiveMetastore Server.

when run  command  #hive --service metastore

there are some errors :

MetaException(message:Version information not found in metastore

Solution:

#schematool -help

#schematool -dbType mysql -initSchema

Could not create ServerSocket on address 0.0.0.0/0.0.0.0:9083

Solution:

#netstat -tulpn |grep 9083

#kill -s 9 5776    //metastore daemon is running,so kill it with its pid 5776

when start hive CLI

hive>show databases;

eroor: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient

Solution:

#hive -hiveconf hive.root.logger=DEBUG,console     //check detail logs

---------------------

Others:

<property>
  <name>hive.server2.thrift.port</name>
  <value>10000</value>
  <description>Port number of HiveServer2 Thrift interface.
  Can be overridden by setting $HIVE_SERVER2_THRIFT_PORT</description>
</property>

<property>
  <name>hive.server2.thrift.bind.host</name>
  <value>192.168.122.1</value>
  <description>Bind host on which to run the HiveServer2 Thrift interface.
  Can be overridden by setting $HIVE_SERVER2_THRIFT_BIND_HOST</description>
</property>

 Note:Starting in Hive release 0.11.0, HCatalog is installed and configured with Hive. The HCatalog server is the same as the Hive metastore.

references:

https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin

http://www.cloudera.com/content/cloudera-content/cloudera-docs/CDH5/latest/CDH5-Installation-Guide/cdh5ig_hive_schema_tool.html

http://www.cloudera.com/content/cloudera-content/cloudera-docs/CDH4/4.2.0/CDH4-Installation-Guide/cdh4ig_topic_18_4.html?scroll=topic_18_4_1_unique_1__title_508_unique_1