Hive——环境搭建
相关hadoop和mysql环境已经搭建好。我博客中也有相关搭建的博客。
一、下载Hive并解压到指定目录(本次使用版本hive-1.1.0-cdh5.7.0,下载地址:http://archive.cloudera.com/cdh5/cdh/5/)
tar zxvf ./hive-1.1.0-cdh5.7.0.tar.gz -C ~/app/
二、Hive配置:参考官网:https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-InstallationandConfiguration
1、配置环境变量
1)vi .bash_profile
export HIVE_HOME=/home/hadoop/app/hive-1.1.0-cdh5.7.0
export PATH=$HIVE_HOME/bin:$PATH
2)source .bash_profile
source .bash_profile
2、hive-1.1.0-cdh5.7.0/conf/hive-env.sh
1)cp hive-env.sh.template hive-env.sh
cp hive-env.sh.template hive-env.sh
2)vi hive-env.sh 添加HADOOP_HOME
HADOOP_HOME=/home/hadoop/app/hadoop-2.6.0-cdh5.7.0
3、hive-1.1.0-cdh5.7.0/conf/hive-site.xml(自己创建配置)
(mysql驱动包需要自己手动拷贝到hive-1.1.0-cdh5.7.0/lib中)。
javax.jdo.option.ConnectionURL
jdbc:mysql://localhost:3306/rdb_hive?createDatabaseIfNotExist=true
javax.jdo.option.ConnectionDriverName
com.mysql.jdbc.Driver
javax.jdo.option.ConnectionUserName
root
javax.jdo.option.ConnectionPassword
root
三、启动hive
hive-1.1.0-cdh5.7.0/bin/hive
启动日志:
[hadoop@hadoop01 bin]$ ./hive
which: no hbase in (/home/hadoop/app/hive-1.1.0-cdh5.7.0/bin:/home/hadoop/app/hadoop-2.6.0-cdh5.7.0
/bin:/home/hadoop/app/jdk1.8.0_131/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/hadoop/
.local/bin:/home/hadoop/bin)
Logging initialized using configuration in jar:file:/home/hadoop/app/hive-1.1.0-cdh5.7.0/lib/
hive-common-1.1.0-cdh5.7.0.jar!/hive-log4j.properties
WARNING: Hive CLI is deprecated and migration to Beeline is recommended.
hive>
启动后会自动在mysql库上建立数据库和表:
mysql> show tables;
+--------------------+
| Tables_in_rdb_hive |
+--------------------+
| CDS |
| DATABASE_PARAMS |
| DBS |
| FUNCS |
| FUNC_RU |
| GLOBAL_PRIVS |
| PARTITIONS |
| PART_COL_STATS |
| ROLES |
| SDS |
| SEQUENCE_TABLE |
| SERDES |
| SKEWED_STRING_LIST |
| TAB_COL_STATS |
| TBLS |
| VERSION |
+--------------------+
四、hive简单入门
使用hive实现wordcount。
1、创建表:create table hive_wordcount(context string);
hive> create table hive_wordcount(context string);
OK
Time taken: 1.203 seconds
hive> show tables;
OK
hive_wordcount
Time taken: 0.19 seconds, Fetched: 1 row(s)
2、导入数据:load data local inpath '/home/hadoop/data/hello.txt' into table hive_wordcount;
hive> load data local inpath '/home/hadoop/data/hello.txt' into table hive_wordcount;
Loading data to table default.hive_wordcount
Table default.hive_wordcount stats: [numFiles=1, totalSize=44]
OK
Time taken: 2.294 seconds
3、查询表数据看是否导成功:select * from hive_wordcount;
hello.txt内容:
Deer Bear River
Car Car River
Deer Car Bear
hive> select * from hive_wordcount;
OK
Deer Bear River
Car Car River
Deer Car Bear
Time taken: 0.588 seconds, Fetched: 3 row(s)
4、使用sql实现wordcount:select word,count(1) from hive_wordcount lateral view explode(split(context,' ')) wc as word group by word;
hive> select word,count(1) from hive_wordcount lateral view explode(split(context,' ')) wc as
word group by word;
Query ID = hadoop_20180904070404_b23d8c2e-161b-4e65-a2cc-206ce343d9e8
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=In order to limit the maximum number of reducers:
set hive.exec.reducers.max=In order to set a constant number of reducers:
set mapreduce.job.reduces=Starting Job = job_1536010835653_0002,
Kill Command = /home/hadoop/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job -kill job_1536010835653_0002
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2018-09-04 07:05:49,279 Stage-1 map = 0%, reduce = 0%
2018-09-04 07:06:01,893 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.95 sec
2018-09-04 07:06:10,804 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 3.44 sec
MapReduce Total cumulative CPU time: 3 seconds 440 msec
Ended Job = job_1536010835653_0002
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 3.44 sec HDFS Read: 8797 HDFS
Write: 28 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 440 msec
OK
Bear 2
Car 3
Deer 2
River 2
Time taken: 37.441 seconds, Fetched: 4 row(s)
可以看到结果:
Bear 2
Car 3
Deer 2
River 2
注意:在创建表的时候遇到一个错误:
Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:
For direct MetaStore DB connections, we don't support retries at the client level.)
从字面意思是是连接msql有问题。从网上查询大概有两种解决办法:
1、换mysql jdbc驱动包,比如换成 mysql-connector-java-5.1.34-bin.jar,但我试过,我这里没有解决
2、换对应mysq 上MetaStore 数据库的编码,换成 latin1,亲测,解决。