在Hadoop集群环境中为MySQL安装配置Sqoop的教程

Sqoop是一个用来将Hadoop和关系型数据库中的数据相互转移的工具,可以将一个关系型数据库(例如 : MySQL ,Oracle ,Postgres等)中的数据导进到Hadoop的HDFS中,也可以将HDFS的数据导进到关系型数据库中。

Sqoop中一大亮点就是可以通过hadoop的mapreduce把数据从关系型数据库中导入数据到HDFS。

一、安装sqoop
1、下载sqoop压缩包,并解压

压缩包分别是:sqoop-1.2.0-CDH3B4.tar.gz,hadoop-0.20.2-CDH3B4.tar.gz, Mysql JDBC驱动包mysql-connector-java-5.1.10-bin.jar

[root@node1 ~]# ll
drwxr-xr-x 15 root root  4096 Feb 22 2011 hadoop-0.20.2-CDH3B4
-rw-r--r-- 1 root root 724225 Sep 15 06:46 mysql-connector-java-5.1.10-bin.jar
drwxr-xr-x 11 root root  4096 Feb 22 2011 sqoop-1.2.0-CDH3B4

2、将sqoop-1.2.0-CDH3B4拷贝到/home/hadoop目录下,并将Mysql JDBC驱动包和hadoop-0.20.2-CDH3B4下的hadoop-core-0.20.2-CDH3B4.jar至sqoop-1.2.0-CDH3B4/lib下,最后修改一下属主。

[root@node1 ~]# cp mysql-connector-java-5.1.10-bin.jar sqoop-1.2.0-CDH3B4/lib
[root@node1 ~]# cp hadoop-0.20.2-CDH3B4/hadoop-core-0.20.2-CDH3B4.jar sqoop-1.2.0-CDH3B4/lib
[root@node1 ~]# chown -R hadoop:hadoop sqoop-1.2.0-CDH3B4
[root@node1 ~]# mv sqoop-1.2.0-CDH3B4 /home/hadoop
[root@node1 ~]# ll /home/hadoop
total 35748
-rw-rw-r-- 1 hadoop hadoop  343 Sep 15 05:13 derby.log
drwxr-xr-x 13 hadoop hadoop  4096 Sep 14 16:16 hadoop-0.20.2
drwxr-xr-x 9 hadoop hadoop  4096 Sep 14 20:21 hive-0.10.0
-rw-r--r-- 1 hadoop hadoop 36524032 Sep 14 20:20 hive-0.10.0.tar.gz
drwxr-xr-x 8 hadoop hadoop  4096 Sep 25 2012 jdk1.7
drwxr-xr-x 12 hadoop hadoop  4096 Sep 15 00:25 mahout-distribution-0.7
drwxrwxr-x 5 hadoop hadoop  4096 Sep 15 05:13 metastore_db
-rw-rw-r-- 1 hadoop hadoop  406 Sep 14 16:02 scp.sh
drwxr-xr-x 11 hadoop hadoop  4096 Feb 22 2011 sqoop-1.2.0-CDH3B4
drwxrwxr-x 3 hadoop hadoop  4096 Sep 14 16:17 temp
drwxrwxr-x 3 hadoop hadoop  4096 Sep 14 15:59 user

3、配置configure-sqoop,注释掉对于HBase和ZooKeeper的检查

[root@node1 bin]# pwd
/home/hadoop/sqoop-1.2.0-CDH3B4/bin
[root@node1 bin]# vi configure-sqoop
#!/bin/bash
#
# Licensed to Cloudera, Inc. under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
.
.
.
# Check: If we can't find our dependencies, give up here.
if [ ! -d "${HADOOP_HOME}" ]; then
 echo "Error: $HADOOP_HOME does not exist!"
 echo 'Please set $HADOOP_HOME to the root of your Hadoop installation.'
 exit 1
fi
#if [ ! -d "${HBASE_HOME}" ]; then
# echo "Error: $HBASE_HOME does not exist!"
# echo 'Please set $HBASE_HOME to the root of your HBase installation.'
# exit 1
#fi
#if [ ! -d "${ZOOKEEPER_HOME}" ]; then
# echo "Error: $ZOOKEEPER_HOME does not exist!"
# echo 'Please set $ZOOKEEPER_HOME to the root of your ZooKeeper installation.'
# exit 1
#fi

4、修改/etc/profile和.bash_profile文件,添加Hadoop_Home,调整PATH

[hadoop@node1 ~]$ vi .bash_profile
# .bash_profile

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

# User specific environment and startup programs

HADOOP_HOME=/home/hadoop/hadoop-0.20.2
PATH=$HADOOP_HOME/bin:$PATH:$HOME/bin
export HIVE_HOME=/home/hadoop/hive-0.10.0
export MAHOUT_HOME=/home/hadoop/mahout-distribution-0.7
export PATH HADOOP_HOME

二、测试Sqoop

1、查看mysql中的数据库:

[hadoop@node1 bin]$ ./sqoop list-databases --connect jdbc:mysql://192.168.1.152:3306/ --username sqoop --password sqoop
13/09/15 07:17:16 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/09/15 07:17:17 INFO manager.MySQLManager: Executing SQL statement: SHOW DATABASES
information_schema
mysql
performance_schema
sqoop
test

2、将mysql的表导入到hive中:

[hadoop@node1 bin]$ ./sqoop import --connect jdbc:mysql://192.168.1.152:3306/sqoop --username sqoop --password sqoop --table test --hive-import -m 1
13/09/15 08:15:01 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/09/15 08:15:01 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
13/09/15 08:15:01 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
13/09/15 08:15:01 INFO tool.CodeGenTool: Beginning code generation
13/09/15 08:15:01 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:02 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:02 INFO orm.CompilationManager: HADOOP_HOME is /home/hadoop/hadoop-0.20.2/bin/..
13/09/15 08:15:02 INFO orm.CompilationManager: Found hadoop core jar at: /home/hadoop/hadoop-0.20.2/bin/../hadoop-0.20.2-core.jar
13/09/15 08:15:03 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/a71936fd2bb45ea6757df22751a320e3/test.jar
13/09/15 08:15:03 WARN manager.MySQLManager: It looks like you are importing from mysql.
13/09/15 08:15:03 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
13/09/15 08:15:03 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
13/09/15 08:15:03 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
13/09/15 08:15:03 INFO mapreduce.ImportJobBase: Beginning import of test
13/09/15 08:15:04 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:05 INFO mapred.JobClient: Running job: job_201309150505_0009
13/09/15 08:15:06 INFO mapred.JobClient: map 0% reduce 0%
13/09/15 08:15:34 INFO mapred.JobClient: map 100% reduce 0%
13/09/15 08:15:36 INFO mapred.JobClient: Job complete: job_201309150505_0009
13/09/15 08:15:36 INFO mapred.JobClient: Counters: 5
13/09/15 08:15:36 INFO mapred.JobClient: Job Counters
13/09/15 08:15:36 INFO mapred.JobClient:  Launched map tasks=1
13/09/15 08:15:36 INFO mapred.JobClient: FileSystemCounters
13/09/15 08:15:36 INFO mapred.JobClient:  HDFS_BYTES_WRITTEN=583323
13/09/15 08:15:36 INFO mapred.JobClient: Map-Reduce Framework
13/09/15 08:15:36 INFO mapred.JobClient:  Map input records=65536
13/09/15 08:15:36 INFO mapred.JobClient:  Spilled Records=0
13/09/15 08:15:36 INFO mapred.JobClient:  Map output records=65536
13/09/15 08:15:36 INFO mapreduce.ImportJobBase: Transferred 569.6514 KB in 32.0312 seconds (17.7842 KB/sec)
13/09/15 08:15:36 INFO mapreduce.ImportJobBase: Retrieved 65536 records.
13/09/15 08:15:36 INFO hive.HiveImport: Removing temporary files from import process: test/_logs
13/09/15 08:15:36 INFO hive.HiveImport: Loading uploaded data into Hive
13/09/15 08:15:36 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:36 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:41 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/home/hadoop/hive-0.10.0/lib/hive-common-0.10.0.jar!/hive-log4j.properties
13/09/15 08:15:41 INFO hive.HiveImport: Hive history file=/tmp/hadoop/hive_job_log_hadoop_201309150815_1877092059.txt
13/09/15 08:16:10 INFO hive.HiveImport: OK
13/09/15 08:16:10 INFO hive.HiveImport: Time taken: 28.791 seconds
13/09/15 08:16:11 INFO hive.HiveImport: Loading data to table default.test
13/09/15 08:16:12 INFO hive.HiveImport: Table default.test stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 583323, raw_data_size: 0]
13/09/15 08:16:12 INFO hive.HiveImport: OK
13/09/15 08:16:12 INFO hive.HiveImport: Time taken: 1.704 seconds
13/09/15 08:16:12 INFO hive.HiveImport: Hive import complete.

三、Sqoop 命令

Sqoop大约有13种命令,和几种通用的参数(都支持这13种命令),这里先列出这13种命令。
接着列出Sqoop的各种通用参数,然后针对以上13个命令列出他们自己的参数。Sqoop通用参数又分Common arguments,Incremental import arguments,Output line formatting arguments,Input parsing arguments,Hive arguments,HBase arguments,Generic Hadoop command-line arguments,下面说明一下几个常用的命令:
1.Common arguments
通用参数,主要是针对关系型数据库链接的一些参数
1)列出mysql数据库中的所有数据库

sqoop list-databases –connect jdbc:mysql://localhost:3306/ –username root –password 123456

2)连接mysql并列出test数据库中的表

sqoop list-tables –connect jdbc:mysql://localhost:3306/test –username root –password 123456

命令中的test为mysql数据库中的test数据库名称 username password分别为mysql数据库的用户密码

3)将关系型数据的表结构复制到hive中,只是复制表的结构,表中的内容没有复制过去。

sqoop create-hive-table –connect jdbc:mysql://localhost:3306/test
–table sqoop_test –username root –password 123456 –hive-table
test

其中 –table sqoop_test为mysql中的数据库test中的表 –hive-table
test 为hive中新建的表名称

4)从关系数据库导入文件到hive中

sqoop import –connect jdbc:mysql://localhost:3306/zxtest –username
root –password 123456 –table sqoop_test –hive-import –hive-table
s_test -m 1

5)将hive中的表数据导入到mysql中,在进行导入之前,mysql中的表
hive_test必须已经提起创建好了。

sqoop export –connect jdbc:mysql://localhost:3306/zxtest –username
root –password root –table hive_test –export-dir
/user/hive/warehouse/new_test_partition/dt=2012-03-05

6)从数据库导出表的数据到HDFS上文件

./sqoop import –connect
jdbc:mysql://10.28.168.109:3306/compression –username=hadoop
–password=123456 –table HADOOP_USER_INFO -m 1 –target-dir
/user/test

7)从数据库增量导入表数据到hdfs中

./sqoop import –connect jdbc:mysql://10.28.168.109:3306/compression
–username=hadoop –password=123456 –table HADOOP_USER_INFO -m 1
–target-dir /user/test –check-column id –incremental append
–last-value 3
(0)

相关推荐

  • Hadoop2.X/YARN环境搭建--CentOS7.0系统配置

    一.我缘何选择CentOS7.0 14年7月7日17:39:42发布了CentOS 7.0.1406正式版,我曾使用过多款Linux,对于Hadoop2.X/YARN的环境配置缘何选择CentOS7.0,其原因有: 1.界面采用RHEL7.0新的GNOME界面风,这可不是CentOS6.5/RHEL6.5所能比的!(当然,Fedora早就采用这种风格的了,但是现在的Fedora缺包已然不成样子了) 2.曾经,我也用了RHEL7.0,它最大的问题就是YUM没法用,而且总会有Warning提示注册购

  • Hadoop2.X/YARN环境搭建--CentOS7.0 JDK配置

    Hadoop是Java写的,他无法使用Linux预安装的OpenJDK,因此安装hadoop前需要先安装JDK(1.6以上) 原材料:在Oracle官网下载的32位JDK: 说明: 1.CentOS 7.0系统现在只有64位的,但是,Hadoop一般支持32位的,在64位环境下有事会有Warning出现,避免真的有神马问题,选择i586的JDK(即32位的),当然,64位的CentOS 7 肯定是兼容32位JDK的,记住:64位系统肯定兼容32位的软件,32位系统不能兼容64位软件.64位只是说

  • 用python + hadoop streaming 分布式编程(一) -- 原理介绍,样例程序与本地调试

    MapReduce与HDFS简介 什么是Hadoop? Google为自己的业务需要提出了编程模型MapReduce和分布式文件系统Google File System,并发布了相关论文(可在Google Research的网站上获得: GFS . MapReduce). Doug Cutting和Mike Cafarella在开发搜索引擎Nutch时对这两篇论文做了自己的实现,即同名的MapReduce和HDFS,合起来就是Hadoop. MapReduce的Data flow如下图,原始数据

  • Hadoop中的Python框架的使用指南

    最近,我加入了Cloudera,在这之前,我在计算生物学/基因组学上已经工作了差不多10年.我的分析工作主要是利用Python语言和它很棒的科学计算栈来进行的.但Apache Hadoop的生态系统大部分都是用Java来实现的,也是为Java准备的,这让我很恼火.所以,我的头等大事变成了寻找一些Python可以用的Hadoop框架. 在这篇文章里,我会把我个人对这些框架的一些无关科学的看法写下来,这些框架包括: Hadoop流 mrjob dumbo hadoopy pydoop 其它 最终,在

  • linux下搭建hadoop环境步骤分享

    1.下载hadoop包 wget http://apache.freelamp.com/hadoop/core/stable/hadoop-0.20.2.tar.gz2.tar xvzf hadoop-0.20.2.tar.gz3.安装JDK,从oracle网站上直接下载JDK,地址:http://www.oracle.com/technetwork/java/javase/downloads/index.html4.chmod +x jdk-6u21-linux-i586.bin;./jdk-

  • hadoop map-reduce中的文件并发操作

    这样的操作在map端或者reduce端均可.下面以一个实际业务场景中的例子来简要说明. 问题简要描述: 假如reduce输入的key是Text(String),value是BytesWritable(byte[]),不同key的种类为100万个,value的大小平均为30k左右,每个key大概对应 100个value,要求对每一个key建立两个文件,一个用来不断添加value中的二进制数据,一个用来记录各个value在文件中的位置索引.(大量的小文件会影响HDFS的性能,所以最好对这些小文件进行

  • hadoop的hdfs文件操作实现上传文件到hdfs

    hdfs文件操作操作示例,包括上传文件到HDFS上.从HDFS上下载文件和删除HDFS上的文件,大家参考使用吧 复制代码 代码如下: import org.apache.hadoop.conf.Configuration;import org.apache.hadoop.fs.*; import java.io.File;import java.io.IOException;public class HadoopFile {    private Configuration conf =null

  • windows 32位eclipse远程hadoop开发环境搭建

    本文假设hadoop环境在远程机器(如linux服务器上),hadoop版本为2.5.2 注:本文eclipse/intellij idea 远程调试hadoop 2.6.0主要参考了并在其基础上有所调整 由于我喜欢在win7 64位上安装32位的软件,比如32位jdk,32位eclipse,所以虽然本文中的操作系统是win7 64位,但是所有的软件都是32位的. 软件版本: 操作系统:win7 64位 eclipse: eclipse-jee-mars-2-win32 java: 1.8.0_

  • Hadoop 2.x伪分布式环境搭建详细步骤

    本文以图文结合的方式详细介绍了Hadoop 2.x伪分布式环境搭建的全过程,供大家参考,具体内容如下 1.修改hadoop-env.sh.yarn-env.sh.mapred-env.sh 方法:使用notepad++(beifeng用户)打开这三个文件 添加代码:export JAVA_HOME=/opt/modules/jdk1.7.0_67 2.修改core-site.xml.hdfs-site.xml.yarn-site.xml.mapred-site.xml配置文件 1)修改core-

  • 用PHP和Shell写Hadoop的MapReduce程序

    使得任何支持标准IO (stdin, stdout)的可执行程序都能成为hadoop的mapper或者 reducer.例如: 复制代码 代码如下: hadoop jar hadoop-streaming.jar -input SOME_INPUT_DIR_OR_FILE -output SOME_OUTPUT_DIR -mapper /bin/cat -reducer /usr/bin/wc 在这个例子里,就使用了Unix/Linux自带的cat和wc工具来作为mapper / reducer

随机推荐