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

心已赠人 2022-06-02 10:43 249阅读 0赞

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

它会生成一堆表。  
![1][]

**2 hive常见操作指令**  
参考[hive常用命令][hive]  
**3 hbase数据导入到hive**  
参考[hbase结合hive和sqoop实现导数据到mysql，hbasesqoop][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_java.io.FileNotFoundException_ File does not exist]这个人遇到的问题跟我一样，但没解决问题  
[hive-hcatalog-core-\*.jar 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配置，其他全部删掉采用默认的方式，再次执行，可以看到成功了  
![3][]  
![4][]  
**4 hive数据导入到mysql**  
按照[sqoop从hbase导出数据到mysql][sqoop_hbase_mysql]  
这篇文章操作,我遇到了下面的问题 select \* 什么得不到，而select count(\*)可以获取到数据量  
![5][]  
google上搜到两篇文章，[Hive中select \* 没有数据，而select count(\*)有数据解决方法][Hive_select _ _select count]，以及[解决方案2][2]，都跟我的原因不同，我写的规规矩矩的，还是这样的错误。  
如果是我创建外部表的问题，我做了下面的尝试，并没有错误。  
![6][]  
无奈之下，到hbase中看个究竟，原来我使用sqoop从mysql中提取数据到hbase的时候，fpdm、fphm、kprq、xhdwsbh是作为主键rowkey的，那么hbase中行数据中也就不会有这些字段，所以无论通过hive进行查询是查不到值的，但是因为rowkey的存在，所以统计是可以的。  
![7][]  
通过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

![8][]

[Hive2.1.1]: http://www.cnblogs.com/hmy-blog/p/6506422.html
[The reference to entity _characterEncoding_ must end with the _ delimiter]: http://www.cnblogs.com/mjhuang/p/4047115.html
[1]: /images/20220602/b26109f401cc4f3fbc3a52a09bd1cef1.png
[hive]: https://www.cnblogs.com/BlueBreeze/p/4232421.html
[hbase_hive_sqoop_mysql_hbasesqoop]: http://www.bkjia.com/sjkqy/869960.html
[hive_java.io.FileNotFoundException_ File does not exist]: http://www.aboutyun.com/thread-8869-1-1.html
[hive-hcatalog-core-_.jar does not exist]: https://stackoverflow.com/questions/25136075/usr-lib-hive-hcatalog-share-hcatalog-hive-hcatalog-core-jar-does-not-exist
[tez hive]: https://community.hortonworks.com/content/supportkb/155511/errorfile-does-not-exist-hdfsxxxtmphive-tez-sessio.html
[3]: /images/20220602/9831649385f440caa6136f80927473ed.png
[4]: /images/20220602/699876d963654d4fa310548ba3e46596.png
[sqoop_hbase_mysql]: http://blog.csdn.net/zgc625238677/article/details/51566471
[5]: /images/20220602/c6699ea9220845619f556596749564f6.png
[Hive_select _ _select count]: http://blog.csdn.net/qq_31382921/article/details/52119328
[2]: http://bbs.csdn.net/topics/391994965
[6]: /images/20220602/fe3ff06d1ac84728b4d522e92da05d41.png
[7]: /images/20220602/1b08675bf76e433da33926fd5662deb0.png
[8]: /images/20220602/d4261bb45e2b480f882adf599ef21ba6.png