Hive will store the metadata information (i.e like RDBMS will stores the table
and column information) out of HDFS and it will process the data available in HDFS.
By default Hive store its metastore into Derby a lightweight database.
which will serve single instance at a time. If you try to start mutltiple instance of Hive, you will get error like
"Another instance of Derby may have already booted the database".
In this will see how we can configure other RDBMS (MySQL & PostgreSQL) as Hive metastore.
Create / rename hive-default.xml.template TO hive-site.xml under $HIVE_HOME/conf
hadoop@solai# vim.tiny $HIVE_HOME/conf/hive-default.xml
change the value of the following property
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hivedb</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>mysqlroot</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive@123</value>
</property>
download and plcae the "mysql-connector-java-5.x.xx-bin.jar" to the $HIVE_HOME/lib
hadoop@solai# mv /home/hadoop/Downloads/mysql-connector-java-5.1.31.tar.gz $HIVE_HOME/lib
In Mysql create database "hivedb" and load the hive schema to the database "hivedb"
mysql> create database hivedb;
mysql> use hivedb;
## following will create hive schema in mysql database.
mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-0.12.0.mysql.sql
mysql> use hivedb;
## following will create hive schema in mysql database.
mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-0.12.0.mysql.sql
its important to restrict user to alter / delete hivedb.
mysql> CREATE USER 'mysqlroot'@'hivedb' IDENTIFIED BY 'hive@123';
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqlroot'@'hivedb';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,EXECUTE ON hivedb.* TO 'mysqlroot'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqlroot'@'hivedb';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,EXECUTE ON hivedb.* TO 'mysqlroot'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit;
Enter Hive CLI, for create table
hadoop@solai#$HIVE_HOME/bin/hive
hive> create table testHiveMysql(uname string, uplace string);
hive> create table testHiveMysql(uname string, uplace string);
enter into mysql to check the schema information created in hive environment. following lines will return the table and column information.
mysql> select * from TBLS;
mysql> select * from COLUMNS_V2;
mysql> show tables;
mysql> select * from COLUMNS_V2;
mysql> show tables;
show tables, will return all the tables pertaining to the Hive schema
