Installation of hive combined with MySQL

#HIVE可以在任意节点搭建, experiment in master

Link: http://pan.baidu.com/s/1i4LCmAp Password: 302x hadoop+hive Download

# #原封不动的复制, will die, please fill in the relevant parameters and paths according to the actual

1. Hive Infrastructure

A, based on the already built Hadoop

B. Download the hive version, and note that it corresponds to Hadoop.

2. Installing hive

A, download the package extracted to/usr/local/named Hive

TAR-ZXVF apache-hive-1.2.1-bin.tar.gz-c/usr/local

Cd/usr/local

Mvapache-hive-1.2.1-bin Hive

B. Setting environment variables

Vim/etc/profile

Exporthive_home=/usr/local/hive

Exporthive_conf_dir=/usr/local/conf

Exportpath= $PATH: $HIVE _home/bin

exporthive_lib= $HIVE _home/lib

This is where you start hive.

[Email protected] ~]# hive

Logging initialized using configuration injar:file:/usr/local/hive/lib/hive-common-1.2.1.jar!/ Hive-log4j.properties

hive> show databases;

Ok

Default

Time taken:1.096 seconds, Fetched:1row (s)

By default, the hive metadata is saved in the embedded Derby database, allowing only one session connection and only for simple testing. In order to support multi-user multi-session, we need a separate meta-database, we use MySQL as a meta-database, the internal Hive for MySQL provides good support.

Ii. using MySQL to store meta data

1, Start mysql5.6 version (Operation process slightly)

2, configure the hive file, the configuration file in the/usr/local/hive/conf/directory has a. template file, copied as hive-env.sh

[email protected] conf]# CP hive-env.sh.templatehive-env.sh

[Email protected] conf]# vim hive-env.sh

# #这里的配置和/etc/profile Repeat, can not be configured, configure environment variables to

1. Environment variables

exporthadoop_heapsize=1024

Hadoop_home=/usr/local/hadoop

Export hive_conf_dir=/usr/local/hive/conf

Export Hive_aux_jars_path=/usr/local/hive/lib

2. Copy a copy of a hive-site.xml file from a template file

Cp/usr/local/hive/conf/hive-default.xml.template./hive-site.xml

Find the value, modify the value, and configure the file download by using the values in the name tag:

http://down.51cto.com/data/2260702

# #原封不动的复制, will die undoubtedly, please fill in the relevant parameters and path according to the actual, marked red Note to modify their actual

<name>hive.metastore.warehouse.dir</name>

<value>/home/hive/warehouse</value>

<name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:mysql://192.168.1.108:3306/hive? Characterencoding=utf-8</value>

#需要在数据库里添加存储元数据的库

<name>javax.jdo.option.ConnectionUserName</name>

<value>Hive</value>

<name>javax.jdo.option.ConnectionPassword</name>

<value>MySQL</value>

# #连接数据库的用户名和密码, authorized user name and password

<name>hive.hwi.listen.port </name>

<value>9999</value>

<name>hive.exec.local.scratchdir</name>

<value>/home/hive</value>

<name>hive.downloaded.resources.dir</name>

<value>/home/hive/tmp</value>

<name>hive.querylog.location</name>

<value>/home/hive</value>

Configuring log information for Output

Hive.log.dir=/home/hive

Hive.log.file=hive.log

Log4j.appender.eventcounter=org.apache.hadoop.log.metrics.eventcounter

3. Create a directory of hive storage data based on configuration

Mkdir/home/hive/tmp–p

4. Configuring the JDBC Connector

1. Download the package and unzip it

2. Copy the Mysql-connector-java-5.1.6-bin.jar to the lib below the hive

Link: http://pan.baidu.com/s/1qXIGeSG Password: Iykt download package

CP mysql-connector-java-5.1.6-bin.jar/usr/local/hive/lib/

Page Tips

Http://192.168.1.114:50070/explorer.html#/home hints that this needs to be modified

Permission denied:user=dr.who, access=read_execute,inode= "Home": root:supergroup:drwx-wx-wx

Add the following in the Vim/usr/local/hadoop/etc/hadoop/hdfs-site.xml, and then restart

<property>

<name>dfs.permissions.enabled</name>

<value>false</value>

</property>

#配置完这些把namenode节点重启生效

The test hive operation can be displayed in MySQL and on the Hadoop page.

Login Hive

1. 5.2 Creating an internal table
   Internal table features: data is loaded into internal tables if the data is locally copied to a directory specified by the internal location, and if the data is on HDFS, the data in HDFs is MV to the location specified in the internal table. When you delete an internal table, the data under the corresponding location is deleted.

CREATE TABLE neibu_table (id int);

Show Tables #进行查看

The default location for hive in HDFs is/user/hive/warehouse, which can be modified, as determined by the properties Hive.metastore.warehouse.dir in the configuration file Hive-site.xml, in the/home/ Create the Student directory under Hive/warehouse/testdb.db.
You can view it through the browser

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/8A/77/wKiom1gxNf7TD_jRAAB58m_Z4Co909.png "title=" aa.png "alt=" Wkiom1gxnf7td_jraab58m_z4co909.png "/>

To load data into a table method:

# #注意aa文件的数据要先创建好

hive> LOAD DATA LOCAL inpath '/home/aa ' into TABLE neibu_table;

Loading Data to Table default.neibu_table

Table default.neibu_table Stats: [Numfiles=1, totalsize=10]

Ok

Time taken:0.639 seconds

Select * from Neibu_table

When using select* without conditions, no mapreduce is executed, execution is faster, and the last line shows NULL, because there is a line of space in the file;

Note: The internal table copies the data to the table directory, and if you delete the internal table metadata, the data below the metadata is also deleted;

The second method of loading data into student
Note the path to the Bb.txt file and write a list of numbers;
Execute command Hadoop fs-put bb.txt/home/hive/warehouse/neibu_table or Hdfsdfs-put t/home/hive/warehouse/neibu_table

Add a library: Create DATABASE hive20161120

CREATE table a20161120, where multiple columns are created, specify a delimiter of \ t

To create a partitioned table:

CREATE TABLE fenqu (id int) partitioned by (d int);

LOAD DATA LOCAL inpath ' bb.txt into TABLE fenqupartition (d=1);
LOAD DATA LOCAL inpath ' bb2.txt ' into TABLE fenqupartition (d=2);

1. 8. Bucket table
   (When the table joins, according to the number of buckets to take the modulo operation, the different data into different barrels)
   Create a bucket type table
   CREATE TABLE student4 (ID int) clustered by (ID) into 4 buckets;
   The bucket table must be enabled
   Set hive.enforce.bucketing = true;
   Inserting data, where load is not used, but uses the Insert,insert to load the data using MapReduce.
   Insert INTO table STUDENT4 Select ID fromstudent3;

The table added in hive can view his metadata information in MySQL, and record all related records under the configuration Q-Definition library

Eg:select * from TBLs;

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1874753

Hive installation is combined with MySQL