Installation and simple testing of hive based on MySQL metadata

Introduction: Hive is a powerful Data Warehouse query language, similar to SQL, this article will describe how to build hive development test environment.

1. What is hive?

Hive is a Hadoop-based data warehousing tool that maps structured data files into a single database table and provides simple SQL query functionality that translates SQL statements into MapReduce tasks. The advantage is that the learning cost is low, the simple mapreduce statistics can be quickly realized through the class SQL statements, and it is very suitable for the statistical analysis of data Warehouse without developing specialized mapreduce applications.

2. Follow Hive's readiness conditions

2.1 Hadoop cluster environment is installed

2.2 This article uses Ubuntu as a development environment (14.04)

3. Installation Steps

3.1 Download Hive Package: apache-hive-0.13.1-bin.tar.gz

3.2 Unzip it into the/OPT directory

Tar xzvf apache-hive-0.13.1-bin.tar.gz

3.3 Setting environment variables

Export hive_home=/opt/apache-hive-0.13

Export path= $PATH: $HIVE _home/bin

Export classpath= $CLASSPATH: $HIVE _home/bin

3.4. Modify the Hive-env.xml and copy the hive-env.xml.template.

# Set Hadoop_home to point to a specific HADOOP install directory

hadoop_home=/opt/hadoop-1.2.1

# Hive Configuration Directory can be controlled by:

Export hive_conf_dir=/opt/apache-hive-0.13/conf

3.5 modificationsHive-site.xml,Primary modification of database connection information.

 <property> <name>hive.metastore.uris</name> <value>thrift://127.0.0.1:9083</value> <description>thrift URI for the remote Metastore. Used by Metastore client-to-connect to remote metastore.</description></property><property> <name& Gt;javax.jdo.option.connectionurl</name> <value>jdbc:mysql://bladestone-laptop:3306/hive? createdatabaseifnotexist=true</value> &LT;DESCRIPTION&GT;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&Gt;javax.jdo.option.connectionpassword</name> <value>123456</value> <description> Password to use against Metastore database</description></property>

3.6 Installing the MySQL Database(Ubuntusystem)

sudo apt-get install Mysql-server

3.7 CreateMySQLUserHive

3.8 Creating a hive database in MySQL

3.9 DownloadsMySQLDrive,and copy the drive toHive_home/LibClass Library

Mysql-connector-java-5.1.31-bin.jar

3.10 StartHive

3.11 Creating a table in h ive

3.12 Log in to MySQLto access the hive Database

3.13 Delete a table from Hive

3.14 Log in to MySQL, query The data in TBLs

4. Summary

With the above steps, we have installed a full hive and demonstrated the relationship between hive and MySQL metabase by adding and removing a demo of a database table.

5. Frequently Asked Questions

5.1 starts directly from the Hive command, an error message appears:

Logging initialized using configuration in jar:file:/opt/apache-hive-0.13/lib/hive-common-0.13.1.jar!/ Hive-log4j.propertiesexception in thread "main" java.lang.RuntimeException:java.lang.RuntimeException:Unable to Instantiate Org.apache.hadoop.hive.metastore.HiveMetaStoreClientat Org.apache.hadoop.hive.ql.session.SessionState.start (sessionstate.java:346) at Org.apache.hadoop.hive.cli.CliDriver.run (clidriver.java:681) at Org.apache.hadoop.hive.cli.CliDriver.main ( clidriver.java:625) at Sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method) at Sun.reflect.NativeMethodAccessorImpl.invoke (nativemethodaccessorimpl.java:62) at Sun.reflect.DelegatingMethodAccessorImpl.invoke (delegatingmethodaccessorimpl.java:43) at Java.lang.reflect.Method.invoke (method.java:483) at Org.apache.hadoop.util.RunJar.main (runjar.java:160) caused by: Java.lang.RuntimeException:Unable to instantiate Org.apache.hadoop.hive.metastore.HiveMetaStoreClientat Org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance (Metastoreutils.java:1412) at Org.apache.hadoop.hive.metastore.retryingmetastoreclient.<init> ( retryingmetastoreclient.java:62) at Org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy ( retryingmetastoreclient.java:72) at Org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient (hive.java:2453 ) at Org.apache.hadoop.hive.ql.metadata.Hive.getMSC (hive.java:2465) at Org.apache.hadoop.hive.ql.session.SessionState.start (sessionstate.java:340) ... 7 morecaused By:java.lang.reflect.InvocationTargetExceptionat Sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method) at Sun.reflect.NativeConstructorAccessorImpl.newInstance (nativeconstructoraccessorimpl.java:62) at Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (delegatingconstructoraccessorimpl.java:45) at Java.lang.reflect.Constructor.newInstance (constructor.java:408) at Org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance (metastoreutils.java:1410) ... morecaused by:metaexception (message:could not connectTo meta store using any of the URIs provided. Most recent Failure:org.apache.thrift.transport.TTransportException:java.net.ConnectException:Connection Refusedat Org.apache.thrift.transport.TSocket.open (tsocket.java:185) at Org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open (hivemetastoreclient.java:336) at Org.apache.hadoop.hive.metastore.hivemetastoreclient.<init> (hivemetastoreclient.java:214) at Sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method) at Sun.reflect.NativeConstructorAccessorImpl.newInstance (nativeconstructoraccessorimpl.java:62) at Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (delegatingconstructoraccessorimpl.java:45) at Java.lang.reflect.Constructor.newInstance (constructor.java:408) at Org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance (metastoreutils.java:1410) at Org.apache.hadoop.hive.metastore.retryingmetastoreclient.<init> (retryingmetastoreclient.java:62) at Org.apache.hadoop.hive.metastore.retryingmetastoreclient.gEtproxy (retryingmetastoreclient.java:72) at Org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient ( hive.java:2453) at Org.apache.hadoop.hive.ql.metadata.Hive.getMSC (hive.java:2465) at Org.apache.hadoop.hive.ql.session.SessionState.start (sessionstate.java:340) at Org.apache.hadoop.hive.cli.CliDriver.run (clidriver.java:681) at Org.apache.hadoop.hive.cli.CliDriver.main ( clidriver.java:625) at Sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method) at Sun.reflect.NativeMethodAccessorImpl.invoke (nativemethodaccessorimpl.java:62) at Sun.reflect.DelegatingMethodAccessorImpl.invoke (delegatingmethodaccessorimpl.java:43) at Java.lang.reflect.Method.invoke (method.java:483) at Org.apache.hadoop.util.RunJar.main (runjar.java:160) caused by: Java.net.ConnectException:Connection refusedat java.net.PlainSocketImpl.socketConnect (Native Method) at Java.net.AbstractPlainSocketImpl.doConnect (abstractplainsocketimpl.java:345) at Java.net.AbstractPlainSocketImpl.connectToAddress (abstractplainsocketimpl.java:206) at Java.net.AbstractPlainSocketImpl.connect (abstractplainsocketimpl.java:188) at Java.net.SocksSocketImpl.connect (sockssocketimpl.java:392) at Java.net.Socket.connect (socket.java:589) at Org.apache.thrift.transport.TSocket.open (tsocket.java:180) ... + +) at Org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open (hivemetastoreclient.java:382) at Org.apache.hadoop.hive.metastore.hivemetastoreclient.<init> (hivemetastoreclient.java:214) ... More

Workaround: Hive--service metastore, use this command to start hive.

5.2 Configuring Hive.metastore.uris
A. Do not start metastore and Hiveserver services
Direct command line hive enters the hive shell environment and then executes show databases error as follows:
ERROR QL. Driver:FAILED:Execution Error, return code 1 from Org.apache.hadoop.hive.ql.exec.DDLTask. Java.lang.RuntimeException:Unable to instantiate Org.apache.hadoop.hive.metastore.HiveMetaStoreClient
B. Second situation
1. Configure Hive.metastore.uris
2. Start Metastore service Hive--service Metastore
Then direct command line hive into the Hive shell environment and then execute show databases

C: The third case
1.NotesConfiguration Item Hive.metastore.uris
2. Do not start the Metastore service
Then direct command line hive into the Hive shell environment and then execute show databases