Hadoop+Hive+Mysql数据分析与入库

环境准备

• JDK 1.8
• Hadoop环境 集群模式(伪分布式亦可），3.3.0
• hive环境 3.1.2
• mysql环境 5.7
• sqoop工具 1.4.7_hadoop

以上工具安装步骤省略，hadoop集群安装参考

配置hive及初始化

配置元数据到mysql

编辑 $HIVE_HOME/conf/hive-site.xml文件

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<property>
		<name>javax.jdo.option.ConnectionURL</name>
		<value>jdbc:mysql://rm-2ze8m9018ujn2k1s3.mysql.rds.aliyuncs.com:3306/hive</value>
	</property>
	<property>
		<name>javax.jdo.option.ConnectionDriverName</name>
		<value>com.mysql.jdbc.Driver</value>
	</property>
	<property>
		<name>javax.jdo.option.ConnectionUserName</name>
		<value>iwanvi_wr</value>
	</property>
	<property>
		<name>javax.jdo.option.ConnectionPassword</name>
		<value>2019@iwanvi_rds</value>
	</property>
</configuration>

初始化

注意需要与配置选择一致

$HIVE_HOME/bin/schematool -dbType mysql -initSchema

## dbType有多种：
#     derby 本地服务器数据库，java编写，小巧
#     mysql
#     postgresql

使用hive

本文仅简单示例，详细使用请参考官方文档

注意

• hive启动需要hadoop先启动

访问hive

# 如果仅使用可以直接启动：
$HIVE_HOME/bin/hive

# 如果需要启动hive和webui则启动：
$HIVE_HOME/bin/hiveserver2
## hive端口 10000，可使用beeline访问，如果有验证，请已正确用户登录
beeline -u jdbc:hive2://localhost:10000
## web ui 端口10002 

hive ddl

本文仅简单示例，详细使用请参考官方文档

# create 创建；show 展示；describe 详细；drop 删除；alter 修改
hive> create database db_name;
# 创建表 
## ROW FORMAT 行标准
## FIELDS TERMINATED BY 字段分隔符
## LOCATION 数据库数据存储位置
## PARTITIONED BY 分区字段

hive> CREATE TABLE IF NOT EXISTS dmp_tb ( \
`ip` string comment 'source ip', \
`time` bigint comment 'log record time', \
`did` string comment 'user device unique sign', \
`host` string comment 'ad land url host address') \
ROW FORMAT DELIMITED  \
FIELDS TERMINATED BY '|'  \
PARTITIONED BY (`hour` string) \
LOCATION '/data/logs/mysql/dmp_mysql_db';

hive dml

本文仅简单示例，详细使用请参考官方文档

# load 加载数据；insert 插入数据；select 查询；update 更新；delete 删除
## 注意 hive推荐增量添加，不推荐修改，所以默认不支持update和delete，需要开启事务

# 加载数据  local为本地文件标识，否则以hdfs文件目录查找；OVERWRITE 是否重写表
hive> LOAD DATA [LOCAL] INPATH '/data/logs/test.log' [OVERWRITE] INTO TABLE dmp_tb;
# 插入新数据 OVERWRITE是否重写表
hive> insert [OVERWRITE] into dmp_tb(`ip`,`time`,`did`,`host`) values('192.168.78.100','165885454','dfsdsfew5465','www.test.com');
# 从查询结果插入新数据
hive> insert into dmp_tb select dmp.`ip`,dmp.`time`,dmp.`did`,dmp.`host` from dmp_tb;

# select 不仅可以从表中查找数据，还可以从方法中查找结果
hive> select * from dmp_tb;

Hive表与Mysql同步

本文使用sqoop工具同步，详细命令请参考sqoop help
需要准备以下信息

• mysql信息(数据库连接、操作用户、用户密码、同步表、同步字段[如果全同步可以不需要])
• hive信息(待同步表或者表目录、字段分割符)

# 示例
sqoop export --connect "jdbc:mysql://db.test.com:3306/hive?characterEncoding=UTF-8" --username root --password root --table dmp_test --fields-terminated-by '|' --columns "ip,time,did,host" --export-dir '/data/logs/mysql/dmp_mysql_db'
# 小提示 --password root 可以更换为 -Proot 哟

提示

以上所有命令，如果执行异常，都可以根据即时日志查看，如果涉及hadoop，可通过hadoop服务器:8088查看各个任务mr的日志，具体请参考实际环境

整理Hive问题

1、需要配置HADOOP_HOME环境变量

Cannot find hadoop installation: $HADOOP_HOME or $HADOOP_PREFIX must be set or hadoop must be in the path

2、Hive中部分包与Hadoop的版本不一致：guava.jar，将Hadoop的该包($HADOO{P}_{H}OME/shared/hadoop/common/)替换到hive的($HIVE_HOME/lib/)

Exception in thread "main" java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V

3、类加载器无法转换：JDK版本问题，之前使用了JDK 11导致hive启动失败，可以改为JDK1.8（官方建议1.7）

java.lang.RuntimeException: Error applying authorization policy on hive configuration: class jdk.internal.loader.ClassLoaders$AppClassLoader cannot be cast to class java.net.URLClassLoader (jdk.internal.loader.ClassLoaders$AppClassLoader and java.net.URLClassLoader are in module java.base of loader 'bootstrap')

4、hadoop未启动：必须先启动hadoop，hive启动会主动连接，启动后会自动恢复正常

java.lang.RuntimeException: Error applying authorization policy on hive configuration: java.net.ConnectException: Call From tmp.dongle.com/192.168.78.100 to tmp.dongle.com:9000 failed on connection exception: java.net.ConnectException: 拒绝连接; For more details see:  http://wiki.apache.org/hadoop/ConnectionRefused

5、beeline连接错误：提示root不允许

org.apache.hive.service.cli.HiveSQLException: Failed to open new session: java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: root is not allowed to impersonate anonymous

修改hadoop中的core-site.xml文件，修改一下配置(注意根据不同用户替换root)

<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>

6、hive无权限访问hdfs文件

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception: org.apache.hadoop.security.AccessControlException Permission denied: user=anonymous, access=WRITE, inode="/data/logs/test/dmp":hadoop:hadoop:drwxr-xr-x

修改hadoop中的hdfs-site.xml文件，修改配置

<property>
    <name>dfs.permissions</name>
    <value>false</value>
</property>

7、未知Hadoop域名

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:java.lang.IllegalArgumentException: java.net.UnknownHostException: hadoop-master)

FAILED: SemanticException Unable to determine if hdfs://hadoop-master:9000/data/logs/test/dmp is encrypted: java.lang.IllegalArgumentException: java.net.UnknownHostException: hadoop-master

这里着重说一下，如果hadoop配置的是域名设置或者域名做了更改，hive最好重新初始化，或者做以下更改

# 如果事务没有开启，最好直接通过数据库修改，如果通过hive会提示事务未开启，不能修改的问题
hive> update DBS set DB_LOCATION_URI=REPLACE(DB_LOCATION_URI,'old host','new host');
hive> update SDS set LOCATION=REPLACE(LOCATION,'old host','new host');

8、事务不支持

FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.

更新删除需要事务支持，并且hive不推荐修改，尽量只做增量添加数据操作

<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>
<property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
</property>
<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
<property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
<property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
</property>
<property>
    <name>hive.compactor.worker.threads</name>
    <value>1</value>
</property>
<property>
    <name>hive.in.test</name>
    <value>true</value>
</property>