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 to transform SQL statements into MapReduce tasks. The advantages are low learning costs and the ability to implement simple mapreduce statistics at high speed through class-SQL statements. There is no need to develop dedicated mapreduce applications, which is ideal for statistical analysis of data warehouses.

2. In accordance with hive conditions of preparation

2.1 Hadoop cluster environment is already 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 folder

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

3.3 Environment variable Settings

Export hive_home=/opt/apache-hive-0.13

Export path= $PATH: $HIVE _home/bin

Export classpath= $CLASSPATH: $HIVE _home/bin

3.4. Change hive-env.xml, copy 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 changesHive-site.xml,major changes to 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

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvymx1zwhlyxj0mja=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

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 MySQL. access to the hive Database

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvymx1zwhlyxj0mja=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

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 deleting a demo of the database tables.

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. Then run the show databases error such as the following:
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. Another type of 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 run show databases

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

Installation and simple testing of hive based on MySQL metadata