Sqoop简单回顾总结

1 Sqoop简介

SQL to Hadoop

开源工具

用于hadoop(hive)与传统数据库之间数据的导入导出

输入:Mysql、Oracle、DB2等关系数据数据导入到Hadoop

输出：从Hadoop的数据导出到Mysql、Oracle等等

2 Sqoop原理

导入和导出都需要在底层调用mapreduce，换言之使用sqoop必须得开yarn。

3 Sqoop安装

需要已具备jdk环境和Hadoop环境

##1. 解压并配置环境变量
[root@hadoop software]# tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/apps/ & cd /opt/apps
[root@hadoop apps]# mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop-1.4.6

[root@hadoop sqoop-1.4.6]# vi /etc/profile
## 自定义环境变量
export JAVA_HOME=/opt/apps/jdk1.8.0_45
export HADOOP_HOME=/opt/apps/hadoop-2.8.1
export HIVE_HOME=/opt/apps/hive-1.2.1
export HBASE_HOME=/opt/apps/hbase-1.2.1
export COLLECT_HOME=/opt/apps/collect-app
export FRP_HOME=/opt/apps/frp
export SCRIPT_HOME=/opt/apps/scripts
export SQOOP_HOME=/opt/apps/sqoop-1.4.6

export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin:$HBASE_HOME/bin
export PATH=$PATH:$COLLECT_HOME:$FRP_HOME:$SCRIPT_HOME:$SQOOP_HOME/bin
export CLASS_PATH=.:$JAVA_HOME/lib
export FLUME_HOME=/opt/apps/flume-1.9.0
export PATH=$PATH:/opt/apps/flume-1.9.0/bin

[root@hadoop sqoop-1.4.6]# source /etc/profile

##2. sqoop-env.sh
[root@hadoop conf]# mv sqoop-env-template.sh 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=/opt/apps/hadoop-2.8.1

#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/opt/apps/hadoop-2.8.1

#set the path to where bin/hbase is available
#export HBASE_HOME=

#Set the path to where bin/hive is available
export HIVE_HOME=/opt/apps/hive-1.2.1

#Set the path for where zookeper config dir is
#export ZOOCFGDIR=

##3. 导入jdbc的mysql的驱动导入到sqoop的lib
[root@hadoop sqoop-1.4.6]# cp /opt/apps/hive-1.2.1/lib/mysql-connector-java-5.1.47-bin.jar ./lib/

##4. 测试
[root@hadoop sqoop-1.4.6]# sqoop version
21/05/24 14:27:43 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
Sqoop 1.4.6
git commit id c0c5a81723759fa575844a0a1eae8f510fa32c25
Compiled by root on Mon Apr 27 14:38:36 CST 2015

4 Sqoop简单使用案例

4.1 全量导入

sqoop import \\
--connect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--table student \\
--target-dir /user/source/student \\
--delete-target-dir \\
--num-mappers 1 \\
--fields-terminated-by "\\t"

4.2 查询导入

sqoop import \\
--connnect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--table student \\
--target-dir /user/source/student \\
--delete-target-dir \\
--num-mappers 1 \\
--fields-terminated-by "\\t" \\
--query 'select id,name,sex from student where sex = 0 and $CONDITIONS;'

--query "select id,name,sex from student where sex = 0 and \\$CONDITIONS;"

4.3 使用内部提供的增量导入

##1. append模式
sqoop import \\
--connect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--table student \\
--target-dir /user/hive/warehouse/student \\
--fields-terminated-by '\\001' \\
--split-by id \\
--num-mappers 1 \\
--check-column id \\
--incremental append \\
--last-value 1

##2. lastmodified模式
sqoop import \\
--connect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--table student \\
--append \\
--target-dir /user/hive/warehouse/student \\
--fields-terminated-by '\\001' \\
--split-by id \\
--num-mappers 1 \\
--check-column create \\
--incremental lastmodified \\
--last-value '2021-07-26 16:13:25'

4.3 按列导入

sqoop import \\
--connnect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--table student \\
--columns id,sex \\
--target-dir /user/source/student \\
--delete-target-dir \\
--num-mappers 1 \\
--fields-terminated-by "\\t" 

4.4 按列条件查询

sqoop import \\
--connnect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--target-dir /user/source/student \\
--delete-target-dir \\
--num-mappers 1 \\
--fields-terminated-by "\\t" 
--table student \\
--columns id,sex \\
--where "sex=0"

4.5 MySQL To Hive

sqoop import \\
--connnect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--table student \\
--num-mappers 1 \\
--hive-import \\
--fields-terminated-by "\\t" \\
--hive-overwrite \\
--hive-table student_hive

4.6 MySQL To Hbase

• 创建Hbase中的表

create 'zxy_hbase','info'

• 查看

list

• 执行导入语句

sqoop import \\
--connect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--table student  \\
--hbase-table zxy_hbase \\
--column-family info  \\
--hbase-create-table \\
--hbase-row-key id

4.7 导出

sqoop export \\
--connect jdbc:mysql://hadoop:3306/zxy \\
--username root \\
--password root \\
--table student  \\
--num-mappers 1 \\
--export-dir /user/hive/warehouse/zxy_hive \\
--input-fields-terminated-by "\\t"