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

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

<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://192.168.5.135:3306/hive?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=utf-8</value>
	</property>
	<property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
	</property>
	<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
	</property>
	<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>000000</value>
	</property>

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

Metastore connection URL:        jdbc:mysql://192.168.5.135:3306/hive?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=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，hbasesqoop这篇文章

CREATE EXTERNAL TABLE hive1(row_key string, fpdm string,fphm string ,kprq string,xhdwsbh string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping"=":key, i:fpdm, i:fphm, i:kprq, i:xhdwsbh")
TBLPROPERTIES("hbase.table.name" = "iw:test01"); 

create table hive2(row_key string, fpdm string, fphm string, kprq string, xhdwsbh string);

insert overwrite table hive2 select * from hive1;

执行上述命令完毕后，再执行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=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
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.jar
        at 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这个人遇到的问题跟我一样，但没解决问题
hive-hcatalog-core-*.jar does not exist，这篇文章提出了一种方案，即更改$HIVE_HOME/conf/hive_env.sh中,但依旧报错

export HIVE_AUX_JARS_PATH=/home/hadoop/application/hive/auxlib/hive-hcatalog-core-2.1.1.jar

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


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

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

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

通过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 '\001'

否则会提示

18/01/12 13:36:05 INFO mapreduce.Job: Task Id : attempt_1515721654214_0008_m_000002_1, Status : FAILED
Error: java.io.IOException: Can't export data, please check failed map task logs
        at 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't parse input data: '7eb759a7230f4573becc7a7f8f3e22cd42001643200227871020170421422301197006201087'
        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.NoSuchElementException
        at java.util.ArrayList$Itr.next(ArrayList.java:834)
        at t_invoice_ticket_2.__loadFromFields(t_invoice_ticket_2.java:358)
        ... 12 more