In this post, we will see how to configure MySQL to be used as Hive Metastore
Let us create a new user in MySQL. We are going to use this User ID & Password to connect from Hive to MySQL
mysql> CREATE USER 'mountain'@'localhost' IDENTIFIED by 'mountain';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON * . * TO 'mountain'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Here I have given both User ID & Password to be 'mountain'. Let us configure Hive to point to MySQL
Change the following properties in $HIVE_HOME/conf/hive-site.xml
(I have given both old & new values)
javax.jdo.option.ConnectionURL
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=metastore_db;create=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
javax.jdo.option.ConnectionDriverName
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.apache.derby.jdbc.EmbeddedDriver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
javax.jdo.option.ConnectionUserName
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>APP</value>
<description>Username to use against metastore database</description>
</property>
With all the configuration set, let us connect to Hive using 'cli'(The Command Line Interface)
mountain@mountain:~/del$ hive cli
Looks like Hive 'cli' is not able to find MySQL driver to connect to MySQL. The Class com.mysql.jdbc.Driver is available in jar mysql-connector. Download the connector, unzip it & move the jar to lib folder in Hive
tar -xvzf mysql-connector-java-5.1.36.tar.gz
cp mysql-connector-java-5.1.36/mysql-connector-java-5.1.36-bin.jar ~/hv/lib/
Let us connect again
mountain@mountain:~/del$ hive cli
Logging initialized using configuration in file:/home/mountain/Desktop/installations/apache-hive-1.0.0-bin/conf/hive-log4j.properties
hive>
Now we are able to successfully connect to Hive. Let us check in MySQL if our metastore database is created. Remember we have given name to our database as hivemetastore
mountain@mountain:~/del$ mysql -umountain -pmountain
mysql> use hivemetastore;
mysql> show tables;
+---------------------------+
| Tables_in_hivemetastore |
+---------------------------+
| BUCKETING_COLS |
| CDS |
| COLUMNS_V2 |
| DATABASE_PARAMS |
| DBS |
| FUNCS |
| FUNC_RU |
| GLOBAL_PRIVS |
| PARTITIONS |
| PARTITION_KEYS |
| PARTITION_KEY_VALS |
| PARTITION_PARAMS |
| PART_COL_STATS |
| ROLES |
| SDS |
| SD_PARAMS |
| SEQUENCE_TABLE |
| SERDES |
| SERDE_PARAMS |
| SKEWED_COL_NAMES |
| SKEWED_COL_VALUE_LOC_MAP |
| SKEWED_STRING_LIST |
| SKEWED_STRING_LIST_VALUES |
| SKEWED_VALUES |
| SORT_COLS |
| TABLE_PARAMS |
| TAB_COL_STATS |
| TBLS |
| VERSION |
+---------------------------+
29 rows in set (0.00 sec)
https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql
http://stackoverflow.com/questions/5555328/error-1396-hy000-operation-create-user-failed-for-jacklocalhost
https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin
Create MySQL user
Let us create a new user in MySQL. We are going to use this User ID & Password to connect from Hive to MySQL
mysql> CREATE USER 'mountain'@'localhost' IDENTIFIED by 'mountain';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON * . * TO 'mountain'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Here I have given both User ID & Password to be 'mountain'. Let us configure Hive to point to MySQL
Configure Hive
Change the following properties in $HIVE_HOME/conf/hive-site.xml
(I have given both old & new values)
javax.jdo.option.ConnectionURL
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=metastore_db;create=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/hivemetastore?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
javax.jdo.option.ConnectionDriverName
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.apache.derby.jdbc.EmbeddedDriver</value>
<description>Driver class name 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>APP</value>
<description>Username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>mountain</value>
<description>Username to use against metastore database</description>
</property>
javax.jdo.option.ConnectionPassword
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>mine</value>
<description>password to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>mountain</value>
<description>password to use against metastore database</description>
</property>
With all the configuration set, let us connect to Hive using 'cli'(The Command Line Interface)
Connect to Hive using 'cli'
mountain@mountain:~/del$ hive cli
Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
...
Caused by: org.datanucleus.store.rdbms.connectionpool.DatastoreDriverNotFoundException: The specified datastore driver ("com.mysql.jdbc.Driver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
...
Looks like Hive 'cli' is not able to find MySQL driver to connect to MySQL. The Class com.mysql.jdbc.Driver is available in jar mysql-connector. Download the connector, unzip it & move the jar to lib folder in Hive
tar -xvzf mysql-connector-java-5.1.36.tar.gz
cp mysql-connector-java-5.1.36/mysql-connector-java-5.1.36-bin.jar ~/hv/lib/
Let us connect again
mountain@mountain:~/del$ hive cli
Logging initialized using configuration in file:/home/mountain/Desktop/installations/apache-hive-1.0.0-bin/conf/hive-log4j.properties
hive>
mountain@mountain:~/del$ mysql -umountain -pmountain
mysql> use hivemetastore;
mysql> show tables;
+---------------------------+
| Tables_in_hivemetastore |
+---------------------------+
| BUCKETING_COLS |
| CDS |
| COLUMNS_V2 |
| DATABASE_PARAMS |
| DBS |
| FUNCS |
| FUNC_RU |
| GLOBAL_PRIVS |
| PARTITIONS |
| PARTITION_KEYS |
| PARTITION_KEY_VALS |
| PARTITION_PARAMS |
| PART_COL_STATS |
| ROLES |
| SDS |
| SD_PARAMS |
| SEQUENCE_TABLE |
| SERDES |
| SERDE_PARAMS |
| SKEWED_COL_NAMES |
| SKEWED_COL_VALUE_LOC_MAP |
| SKEWED_STRING_LIST |
| SKEWED_STRING_LIST_VALUES |
| SKEWED_VALUES |
| SORT_COLS |
| TABLE_PARAMS |
| TAB_COL_STATS |
| TBLS |
| VERSION |
+---------------------------+
29 rows in set (0.00 sec)
That completes our configuration to set MySQL as Hive Metastore
Reference
https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql
http://stackoverflow.com/questions/5555328/error-1396-hy000-operation-create-user-failed-for-jacklocalhost
https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin
No comments:
Post a Comment