大数据系列8：Sqoop – HADOOP和RDBMS数据交换
NeboFeng 2013-10-02
Sqoop1：
wget http://mirrors.ustc.edu.cn/apache/sqoop/1.4.4/sqoop-1.4.4.bin__hadoop-1.0.0.tar.gz
tar -xzvf sqoop-1.4.4.bin__hadoop-1.0.0.tar.gz
mv sqoop-1.4.4.bin__hadoop-1.0.0 sqoop-1.4.4
cd sqoop-1.4.4
sudo vi /etc/profile
增加：
export HADOOP_COMMON_HOME=/home/ysc/hadoop-1.2.1
export HADOOP_MAPRED_HOME=/home/ysc/hadoop-1.2.1
export PATH=$PATH:/home/ysc/sqoop-1.4.4/bin
export HBASE_HOME=/home/ysc/hbase-0.94.12
source /etc/profile
sqoop help
将JDBC驱动mysql-connector-java-5.1.18.jar拷贝到/home/ysc/sqoop-1.4.4/lib
sqoop list-databases --connect jdbc:mysql://host001 --username root --password ysc
sqoop list-tables --connect jdbc:mysql://host001/mysql --username root --password ysc
sqoop import --connect jdbc:mysql://host001/test --username root --password ysc --table person
sqoop import --connect jdbc:mysql://host001/test --username root --password ysc --table person -m 1
sqoop import --connect jdbc:mysql://host001/test --username root --password ysc --table person --direct -m 1
sqoop import-all-tables --connect jdbc:mysql://host001/test --username root --password ysc --direct -m 1
sqoop export --connect jdbc:mysql://host001/test --username root --password ysc --table person --export-dir person
sqoop export --connect jdbc:mysql://host001/test --username root --password ysc --table animal --export-dir animal
Sqoop2：
wget http://mirror.bit.edu.cn/apache/sqoop/1.99.2/sqoop-1.99.2-bin-hadoop100.tar.gz
tar -xzvf sqoop-1.99.2-bin-hadoop100.tar.gz
mv sqoop-1.99.2-bin-hadoop100 sqoop-1.99.2
cd sqoop-1.99.2
sudo apt-get install zip
bin/addtowar.sh -hadoop-version 1.2.1 -hadoop-path /home/ysc/hadoop-1.2.1
bin/addtowar.sh -jars /home/ysc/mysql-connector-java-5.1.18.jar
vi server/conf/sqoop.properties
修改org.apache.sqoop.submission.engine.mapreduce.configuration.directory=/etc/hadoop/conf/为
org.apache.sqoop.submission.engine.mapreduce.configuration.directory=/home/ysc/hadoop-1.2.1/conf/
启动Sqoop 2 server：
bin/sqoop.sh server start
停止Sqoop 2 server：
bin/sqoop.sh server stop
客户端连接Sqoop 2 server：
客户端直接解压即可运行
MySQL准备数据库和表:
create database test;
create table history (userId int, command varchar(20));
insert into history values(1, 'ls');
insert into history values(1, 'dir');
insert into history values(2, 'cat');
insert into history values(5, 'vi');
交互模式：
bin/sqoop.sh client
sqoop:000> set server --host host001 --port 12000 --webapp sqoop
sqoop:000> show version --all
sqoop:000> show connector --all
sqoop:000>create connection --cid 1
Name: mysql
JDBC Driver Class: com.mysql.jdbc.Driver
JDBC Connection String:
jdbc:mysql://host001:3306/test?useUnicode=true&characterEncoding=UTF-8&createDatabaseIfNotExist=true&autoReconnect=true
Username: root
Password: ***
entry#回车
Max connections:100
sqoop:000>create job --xid 1 --type import
Name:ImportHistory
Schema name:
Table name: history
Table SQL statement:
Table column names:
Partition column name:userId
Boundary query:
Choose:0
Choose: 0
Output directory: output-sqoop2-history
Extractors:
Loaders:
sqoop:000> submission start --jid 1
sqoop:000> submission status --jid 1
sqoop:000> submission stop --jid 1
批处理模式：
sqoop.sh client /home/ysc/script.sqoop
vi /home/ysc/script.sqoop
输入：
#指定服务器信息
set server --host host001 --port 12000 --webapp sqoop
#执行JOB
submission start --jid 1
