第6.1.1章hive2.1.1相关操作及问题汇总

1 hive部署
参考Hive2.1.1的安装
1.1 hive-site.xml中mysql配置
注意&在xml中字符转移&＃xff0c;如果出现解决方案参考报错The reference to entity “characterEncoding” must end with the ‘;’ delimiter

javax.jdo.option.ConnectionURLjdbc:mysql://192.168.5.135:3306/hive?createDatabaseIfNotExist&＃61;true&useUnicode&＃61;true&characterEncoding&＃61;utf-8javax.jdo.option.ConnectionDriverNamecom.mysql.jdbc.Driverjavax.jdo.option.ConnectionUserNamerootjavax.jdo.option.ConnectionPassword000000


1.2 schematool -dbType mysql -initSchema
hive的元数据写入到mysql中了。

Metastore connection URL: jdbc:mysql://192.168.5.135:3306/hive?createDatabaseIfNotExist&＃61;true&useUnicode&＃61;true&characterEncoding&＃61;utf-8
Metastore Connection Driver : com.mysql.jdbc.Driver
Metastore connection User: root
Starting metastore schema initialization to 2.1.0
Initialization script hive-schema-2.1.0.mysql.sql
Initialization script completed
schemaTool completed


它会生成一堆表。

2 hive常见操作指令
参考hive常用命令
3 hbase数据导入到hive
参考hbase结合hive和sqoop实现导数据到mysql&＃xff0c;hbasesqoop这篇文章

CREATE EXTERNAL TABLE hive1(row_key string, fpdm string,fphm string ,kprq string,xhdwsbh string)
STORED BY &＃39;org.apache.hadoop.hive.hbase.HBaseStorageHandler&＃39;
WITH SERDEPROPERTIES ("hbase.columns.mapping"&＃61;":key, i:fpdm, i:fphm, i:kprq, i:xhdwsbh")
TBLPROPERTIES("hbase.table.name" &＃61; "iw:test01"); create table hive2(row_key string, fpdm string, fphm string, kprq string, xhdwsbh string);insert overwrite table hive2 select * from hive1;


执行上述命令完毕后&＃xff0c;再执行select count(*) from hive1;,提示下面的异常

Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):set hive.exec.reducers.bytes.per.reducer&＃61;
In order to limit the maximum number of reducers:set hive.exec.reducers.max&＃61;
In order to set a constant number of reducers:set mapreduce.job.reduces&＃61;
java.io.FileNotFoundException: File does not exist: hdfs://dashuju174:9000/home/hadoop/application/apache-hive-2.1.1-bin/lib/hbase-server-1.1.1.jarat org.apache.hadoop.hdfs.DistributedFileSystem$17.doCall(DistributedFileSystem.java:1072)at org.apache.hadoop.hdfs.DistributedFileSystem$17.doCall(DistributedFileSystem.java:1064)


网上搜到hive查询报错java.io.FileNotFoundException: File does not exist这个人遇到的问题跟我一样&＃xff0c;但没解决问题
hive-hcatalog-core-*.jar does not exist&＃xff0c;这篇文章提出了一种方案&＃xff0c;即更改$HIVE_HOME/conf/hive_env.sh中,但依旧报错

export HIVE_AUX_JARS_PATH&＃61;/home/hadoop/application/hive/auxlib/hive-hcatalog-core-2.1.1.jar


tez hive这篇文章跟我遇到的问题一样&＃xff0c;但是set tez.client.asynchronous-stop&＃61;false并不是hive的配置,苦恼
我对以前安装的hive重新更改了配置&＃xff0c;如上述1.1&＃xff0c;只保留mysql配置&＃xff0c;其他全部删掉采用默认的方式&＃xff0c;再次执行&＃xff0c;可以看到成功了


4 hive数据导入到mysql
按照sqoop从hbase导出数据到mysql
这篇文章操作,我遇到了下面的问题 select * 什么得不到&＃xff0c;而select count(*)可以获取到数据量

google上搜到两篇文章&＃xff0c;Hive中select * 没有数据&＃xff0c;而select count(*)有数据解决方法&＃xff0c;以及解决方案2&＃xff0c;都跟我的原因不同&＃xff0c;我写的规规矩矩的&＃xff0c;还是这样的错误。
如果是我创建外部表的问题&＃xff0c;我做了下面的尝试&＃xff0c;并没有错误。

无奈之下&＃xff0c;到hbase中看个究竟&＃xff0c;原来我使用sqoop从mysql中提取数据到hbase的时候&＃xff0c;fpdm、fphm、kprq、xhdwsbh是作为主键rowkey的&＃xff0c;那么hbase中行数据中也就不会有这些字段&＃xff0c;所以无论通过hive进行查询是查不到值的&＃xff0c;但是因为rowkey的存在&＃xff0c;所以统计是可以的。

通过sqoop将hive中的数据写入到mysql, 注意hive的默认分隔符为’\001’

sqoop export \
--connect jdbc:mysql://192.168.5.135:3306/toolbox --username root -P \
--table t_invoice_ticket_2 \
--export-dir /user/hive/warehouse/hive2/000000_0 --input-fields-terminated-by &＃39;\001&＃39;


否则会提示

18/01/12 13:36:05 INFO mapreduce.Job: Task Id : attempt_1515721654214_0008_m_000002_1, Status : FAILED
Error: java.io.IOException: Can&＃39;t export data, please check failed map task logsat org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784)at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)at java.security.AccessController.doPrivileged(Native Method)at javax.security.auth.Subject.doAs(Subject.java:415)at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1656)at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.lang.RuntimeException: Can&＃39;t parse input data: &＃39;7eb759a7230f4573becc7a7f8f3e22cd42001643200227871020170421422301197006201087&＃39;at t_invoice_ticket_2.__loadFromFields(t_invoice_ticket_2.java:378)at t_invoice_ticket_2.parse(t_invoice_ticket_2.java:306)at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)... 10 more
Caused by: java.util.NoSuchElementExceptionat java.util.ArrayList$Itr.next(ArrayList.java:834)at t_invoice_ticket_2.__loadFromFields(t_invoice_ticket_2.java:358)... 12 more