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的该包( H A D O O P H O M E / s h a r e d / h a d o o p / c o m m o n / ) 替 换 到 h i v e 的 ( HADOOP_HOME/shared/hadoop/common/)替换到hive的( HADOOPHOME/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>