Hadoop Series Part 17 - Configuration of Hive and MySQL

Hive is a datawarehouse based on the top of hadoop. Hive is used for managing and querying structured data. You can interact with hive using several ways.
Hive stores metadata in a standard relational database. Hive comes with default embedded SQL database called 'Derby' which runs on client machine along with hive.
To change default metastore to mysql use following steps:  

Configuration Steps:
1) Download and install MySQL. Download
2) Download mysql-connector-java-5.x.x.jar file and copy to $HIVE_HOME/lib directory. Download
3) Login to mysql using following command:
    root@kb:/home/kb#mysql -u root -p

4) Create database and user in mysql and grant privileges to user;

database and user creation and grant permission

"hivekb" username same as ConnectionUserName in hive-site.xml
"hivepass" password same as ConnectionPassword in hive-site.xml
flush privileges is used because it reload/refresh all privileges after changes applied.

5) Configure hive-site.xml
By default there is no hive-site.xml file available in hive/conf dir.
We have to create a new file and save it as hive-site.xml or you can copy hive-default.xml.template file to hive-site.xml file using cp command.
Configuration details hive-site.xml  

6) Access your metastore_db by using following commands:
    mysql -u root -p
    mysql>use metastore_db;
    mysql>show tables;
Tables in metastore_db

"Successful configuration of Hive and MySQL!" 
Now you will see names of your Hive tables in MySQL.Verify it by adding table.
Go ahead and start programming....
For more practice on hive click here

1) http://www.cloudera.com
2) "Hadoop In Action by Chuck Lam"


Post a Comment