搭建Sqoop, Hive和Mysql之间数据导入 - HBase

TOP

搭建Sqoop, Hive和Mysql之间数据导入

2019-05-14 13:47:41 【大中小】浏览:361次

经过一下午的努力，终于实现了Sqoop，在Hive和Mysql之间互相导入数据（表）

前期准备：Hapood平台、Zookeeper、Hbase、Hive都安装好。（安装可以参考其它资源）
本Demo版本：jdk1.8.0_171，hadoop-2.7.3，zookeeper-3.4.9，mysql-5.6.40-linux-glibc2.12-x86_64，hbase-1.2.4，apache-hive-2.1.1-bin

1.安装部署Sqoop

Sqoop有Sqoop1和Sqoop2之区。

可以参阅此文章：http://doc.yonyoucloud.com/doc/ae/921008.html

我使用的是Sqoop1：sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz （有人说Sqoop2不支持Hive与Mysql之间，因此选择Sqoog1）

还有这个Jar包：mysql-connector-java-5.1.45-bin.jar（这个是必须的）

对Sqoop的。gz文件进行释放，这里不在赘述。

把Mysql-connector的包拷贝到你安装的Sqoop的lib目录下。（$SQOOP_HOME/lib）

2. Sqoop配置

Sqoop环境变量配置：

[root@master software]# more ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export JAVA_HOME=/application/software/jdk1.8.0_171
export HIVE_HOME=/application/software/apache-hive-2.1.1-bin
export HADOOP_HOME=/application/software/hadoop-2.7.3
export HBASE_HOME=/application/software/hbase-1.2.4
export MAVEN_HOME=/application/software/apache-maven-3.5.4
export SQOOP_HOME=/application/software/sqoop-1.4.7.bin__hadoop-2.6.0
export ZOOKEEPER_HOME=/application/software/zookeeper-3.4.9
export HIVE_CONF_DIR=/application/software/apache-hive-2.1.1-bin/conf #这行比较重要，不配置后边报错
export HADOOP_CLASSPATH=$HBASE_HOME/lib/*:$HIVE_HOME/lib/*:$HADOOP_HOME/lib/* #这行也是，不配置报错。
export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$SQOOP_HOME/bin:$MAVEN_HOME/bin:$ZOOKEEPER_HOME/bin:$HBASE_HOME/bin:$HIVE_H
OME/bin
[root@master software]#

Sqoop-env.sh配置：

[root@master conf]# cp sqoop-env-template.sh sqoop-env.sh

[root@master conf]# more sqoop-env.sh
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# included in all the hadoop scripts with source command
# should not be executable directly
# also should not be passed any arguments, since we need original $*

# Set Hadoop-specific environment variables here.

#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/application/software/hadoop-2.7.3

#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/application/software/hadoop-2.7.3

#set the path to where bin/hbase is available
export HBASE_HOME=/application/software/hbase-1.2.4

#Set the path to where bin/hive is available
export HIVE_HOME=/application/software/apache-hive-2.1.1-bin

#Set the path for where zookeper config dir is
export ZOOCFGDIR=/application/software/zookeeper-3.4.9
export HIVE_CONF_DIR=/application/software/apache-hive-2.1.1-bin/conf
[root@master conf]#

注意:根据实际路径配置HADOOP_COMMON_HOME，HADOOP_MAPRED_HOME，HIVE_HOME

Sqoop-site.xml 我未作任何修改。

3. 环境已具备，可以做实验了。

1）首先是从Hive导出数据到Mysql

Hive里的表内容：

hive> show tables> ;
OK
blog_hive_hbase
brand_dimension
record
record_orc
record_parquet
record_partition
sogoutest
stocks
stu
stu2
stu3
stu_hive
user_dimension
Time taken: 0.031 seconds, Fetched: 13 row(s)
hive> desc sogoutest;
OK
rid string
uid string
price int
Time taken: 0.058 seconds, Fetched: 3 row(s)
hive> select * from sogoutest;
OK
0000000005 00000008 120
0000000004 00000002 429
0000000003 00000004 347
0000000002 00000004 697
0000000001 00000001 252
0000000000 00000001 625
Time taken: 0.102 seconds, Fetched: 6 row(s)
hive>

在Mysql要创建同样的表结构。

mysql> create table sogoutest1(
-> ridvarchar(50),uidvarchar(50),price int);
Query OK, 0 rows affected (0.02 sec)

mysql>

执行操作：

[root@master conf]# sqoop export --connect jdbc:mysql://master:3306/test --username root --password root123 --table sogoutest1 --export-dir '/Hive/sogoutest' --fields-terminated-by '\t' -m 1

--table sogoutest1 #你在mysql里创建的表

--export-dir #是你在Hive里的表

可以通过这个命令查看：

hive> show create table sogoutest;
OK
CREATE TABLE `sogoutest`(
`rid` string,
`uid` string,
`price` int)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim'='\t',
'serialization.format'='\t')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://master:9000/Hive/sogoutest' #这个位置
TBLPROPERTIES (
'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}',
'numFiles'='1',
'numRows'='6',
'rawDataSize'='138',
'totalSize'='144',
'transient_lastDdlTime'='1530754256')
Time taken: 0.069 seconds, Fetched: 22 row(s)
hive>

结果展示（在Mysql里可以查到数据）：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sogoutest1 |
| stu_mysql |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from sogoutest1;
+------------+----------+-------+
| rid | uid | price |
+------------+----------+-------+
| 0000000005 | 00000008 | 120 |
| 0000000004 | 00000002 | 429 |
| 0000000003 | 00000004 | 347 |
| 0000000002 | 00000004 | 697 |
| 0000000001 | 00000001 | 252 |
| 0000000000 | 00000001 | 625 |
+------------+----------+-------+
6 rows in set (0.00 sec)

mysql>

2）其次是从Mysql导出数据到Hive。

Mysql的表内容：

不需要在Hive里创建表。通过Sqoop可以在Hive里创建。

直接执行操作：（这是全表导入，也可以选择几列进行导入）

[root@master conf]# sqoop import --connect jdbc:mysql://master:3306/test --username root --password root123 --table stu_mysql --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --hive-overwrite --create-hive-table --hive-table stu3 -m 1

结果展示：

hive> show tables;
OK
blog_hive_hbase
brand_dimension
record
record_orc
record_parquet
record_partition
sogoutest
stocks
stu
stu2
stu3
stu_hive
user_dimension
Time taken: 0.037 seconds, Fetched: 13 row(s)
hive> desc stu3;
OK
stu_id int
name string
sex string
score int
Time taken: 0.07 seconds, Fetched: 4 row(s)
hive> select * from stu3;
OK
1 mafeng male 899
2 mazhuoxing male 1000
3 qizhuoxing male 1000
Time taken: 0.117 seconds, Fetched: 3 row(s)
hive>

4. 遇到的坑：

1）报错：Caused by: java.lang.RuntimeException: java.sql.SQLException: null, message from server: "Host 'datanode1' is not allowed to connect to this MySQL server"

解决方案：改变Mysql授权。

例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。

Mysql>GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

Mysql>FLUSH PRIVILEGES;

2)报错：

18/07/05 12:03:55 ERROR hive.HiveConfig: Could not load org.apache.hadoop.hive.conf.HiveConf. Make sure HIVE_CONF_DIR is set correctly.

解决方案：
在~/.bash_profile里增加：
export JAVA_HOME=/application/software/jdk1.8.0_171
export HIVE_HOME=/application/software/apache-hive-2.1.1-bin
export HADOOP_HOME=/application/software/hadoop-2.7.3
export HBASE_HOME=/application/software/hbase-1.2.4
export MAVEN_HOME=/application/software/apache-maven-3.5.4
export SQOOP_HOME=/application/software/sqoop-1.4.7.bin__hadoop-2.6.0
export ZOOKEEPER_HOME=/application/software/zookeeper-3.4.9
export HIVE_CONF_DIR=/application/software/apache-hive-2.1.1-bin/conf
export HADOOP_CLASSPATH=$HBASE_HOME/lib/*:$HIVE_HOME/lib/*:$HADOOP_HOME/lib/*


【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】

上一篇：利用数据库存储订单通知和任务，..	下一篇：hadoop配置项的调优