May 5, 2013

Simple metastore creation for Hive in MySQL


For Hive, the meta-store is like the system catalog which contains metadata about the tables stored in Hive. This metadata is specified during table creation and reused every time the table is referenced in HiveQL. The database is a namespace for tables, where ‘default’ is used for tables with no user supplied database name. The metadata for table contains list of columns and their types, owner, storage and SerDe information (which I can detail in future posts). It can also contain any user supplied key and value data; which can be used for table statistics. Storage information includes location of the table’s data in the underlying file system, data formats and bucketing information. SerDe (which controls how Hive serializes/deserializes the data in a row) metadata includes the implementation class of serializer and deserializer methods and any supporting information required by that implementation. The partitions can have its own columns and SerDe and storage information which can be used in the future to evolve Hive schema.The metastore uses either a traditional relational database (like MySQL, Oracle) or file system and not HDFS since it is optimized for sequential scans only),thus the fired HiveQL statements are executed slow which only access metadata objects.


its simple to install the metastore.

-install mysql-conector
$ sudo yum install mysql-connector-java
-create a symbolic link in the Hive directory
$ ln -s /usr/share/java/mysql-connector-java.jar /usr/lib/hive/lib/mysqlconnector-java.jar

-create the database for the Hive metastore.cdh4 ships with scripts for derby,mysql,oracle and postgre
$ mysql -u root -p
mysql> CREATE DATABASE hivemetastoredb;
mysql> USE hivemetastoredb;
mysql> SOURCE /usr/lib/hive/scripts/metastore/upgrade/mysql/hive-schema- 0.9.0.mysql.sql;

-create a user for the metastore
mysql>CREATE USER 'hive'@'%' IDENTIFIED BY 'hive';

-grant access for all hosts in the network
mysql> GRANT ALL PRIVILEGES ON hivemetastoredb.* TO hive@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

following entries in the file /etc/hive/conf/hive-sites.xml, if you are trying a jdbc connection
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/hivemetastoredb</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>


8 comments:

  1. can we use Oracle ? if yes then how.?

    ReplyDelete
  2. I know we can use all databases that support "JPOX".

    ReplyDelete
  3. Get your Summer Collections with over 50% discounts now!!! http://www.shopclues.com/index.php?

    dispatch=products.search&company_id=69063&search_performed=Y

    ReplyDelete
  4. Get your Summer Collections with over 50% discounts now!!! http://www.shopclues.com/index.php?dispatch=products.search&company_id=69063&search_performed=Y

    ReplyDelete
  5. A Great Information about MySQL; which I was looking for.

    ReplyDelete
  6. Nice article Dear, I love to read this types of articles. Thanks a lot to share with us...... Click here for Latest Govt Jobs, Tech news, Blogging Tiricks, Earning Tips and more....

    ReplyDelete
  7. Pune packers movers your personal property as per the nature of the item with the help of our vocational Packaging Experts. We ease you from all your difficulty & concern by maintaining timely and secured submission of your consignment at your entry phase. We offering cost-effective, adept and attribute appearance movers not only smoothes your transportation but also assure that it is accomplish to struggle any abrupt danger during the transportation.

    http://www.toppackersmoverspune.in/

    http://ipackersmoversbangalore.top4th.in/

    ReplyDelete
  8. 1. I want to have unicode encoding to be enabled for hive with Mysql as metastore. I followed the above steps to configure Mysql as metastore, after that I edited /etc/mysql/my.cnf and modified its character encoding to UTF-8. But still if I insert unicode data ( other than english like Hindi) through hive prompt then at time of display it is shown in some absurd form.

    For example when I inserted माधुरी दीक्षित, it was added successfully but at time retrieval it displays as : .>'A0@ &@\025M7?$.

    Do I need to do any settings in Hive as well ? If yes then what?

    2. I am starting hive by : $HIVE_HOME/bin/hive --service hiveserver2
    Is it the correct way to start hive even if I want to use mysql as my metastore.

    Any Help is Appreciated.

    ReplyDelete