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
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"
|