Sqoop-命令大全

Sqoop命令大全

版本

Sqoop-version : 1.4.6
sqoop 1.4.6|7
=> hadoop 2.x + hive 1.x + hbase 1.x
=> hadoop 3.1.3 + hive 3.1.2 + hbase 2.3.5(not support)

Sqoop安装

1. 解压并改名

tar -zxvf /opt/download/sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/software/
mv /opt/software/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ /opt/software/sqoop146/

2. 配置文件

#释放配置项
cp /opt/software/sqoop146/conf/sqoop-env-template.sh /opt/software/sqoop146/conf/sqoop-env.sh
#编辑配置项
vim /opt/software/sqoop146/conf/sqoop-env.sh
-------------------------
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/opt/software/hadoop313
#Set path to where hadoop -*-core.jar is available
export HADOOP_MAPRED_HOME=/opt/software/hadoop313
#Set the path to where bin/hive is available
export HIVE_HOME=/opt/software/hive312
#Set the path to where bin/hbase is available
export HBASE_HOME=/opt/software/hbase235/
#Set the path to where bin/zookeeper is available
export ZOOCFGDIR=/opt/software/zookeeper357
-------------------------

3. 环境变量

cd /etc/profile.d/myenv.sh
-----------------------------------
#sqoop
export SQOOP_HOME=/opt/software/sqoop146
export PATH=$SQOOP_HOME/bin:$PATH
export LOGDIR=$SQOOP_HOME/logs
---------------------------------------
source /etc/profile

4. jar包

#mysql-connector-java-5.1.47.jar 放到 /opt/software/sqoop146/lib
cd /opt/software/sqoop146/lib
find /opt/software/hive312/ -name 'mysql*.jar'
cp /opt/software/hive312/lib/mysql-connector-java-5.1.48.jar ./
#其他所需jar包
cp /opt/software/hadoop313/share/hadoop/common/hadoop-common-3.1.3.jar ./
cp /opt/software/hadoop313/share/hadoop/hdfs/hadoop-hdfs-3.1.3.jar ./
cp /opt/software/hadoop313/share/hadoop/mapreduce/hadoop-mapreduce-client-core-3.1.3.jar ./

5. 测试是否连接成功

sqoop list-databases --connect jdbc:mysql://192.168.232.180:3306 --username root --password password

如果没有问题会显示数据库列表。

Sqoop语法

$ sqoop tool-name [tool-arguments]
常用工具即命令

• 列出所有可用数据库
  list-databases
• 列出数据库所有可用表
  list-tables
• 导入数据库表数据到HDFS中
  import
• 导入数据库所有表数据到HDFS中
  import-all-tables
• 导出HDFS目录中的文件数据到数据库表中
  export
• 导入表的结构到Hive中
  create-hive-table
• 生成与数据记录交互的代码
  codegen
• 生成一个sqoop的任务，生成后，该任务并不执行，除非使用命令执行该任务
  job
• 将HDFS中不同目录下面的数据和在一起，并存放在指定的目录中
  merge
• 列出所有可用工具
  help
• 显示版本信息
  version
  工具参数及含义
  – connect (jdbc-url) #指定JDBC连接字符串
  –connection-manager (class-name) #指定要使用的连接管理器类
  –driver (class-name) #指定要使用的JDBC驱动类
  –hadoop-mapred-home (dir) #指定$HADOOP_MAPRED_HOME路径（MapReduce主目录）
  –password-file #设置用于存放认证的密码信息文件的路径
  -P (大写的P) #从控制台读取设置输入如的密码
  –password (password) #设置认证密码
  –username (username) #设置认证用户名
  –verbose #打印详细的运行信息
  –connection-param-file (filename) #指定存储数据库连接参数的属性文件（可选）

查看

查看所有有数据库

sqoop list-databases \
--connect jdbc:mysql://(hostname|ip):3306/ \
--username root \
--password password

查看某个数据库中所有表

sqoop list-tables \
--connect jdbc:mysql://(hostname|ip):3306/userdb \
--username root \
--password password

MySQL -> HDFS (import)

把数据从MySQL表中导入到HDFS

#全量导入
sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table sqp_order \
--m 1 \
--delete-target-dir \
--target-dir /kb12/sqoop/m2h_all \
--fields-terminated-by '\t'
--lines-terminated-by '\n'

列裁剪

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table sqp_order \
--columns user_name,total_volume \
--m 1 \
--delete-target-dir \
--target-dir /kb12/sqoop/m2h_colcut \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'

行列裁剪+多map(-m 2 表示启动两个maptask)–(分区)

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table sqp_order \
--columns user_name,total_volume \
--where "total_volume>=200" \
--m 2 \
--split-by user_name \
--delete-target-dir \
--target-dir /kb12/sqoop/m2h_rowcut \
--fields-terminated-by ','
--lines-terminated-by '\n'

#使用query
sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--query "select user_name,total_volume from sqp_order where total_volume>=300 and \$CONDITIONS" \
--m 2 \
--split-by user_name \
--delete-target-dir \
--target-dir /kb12/sqoop/m2h_rowcut2 \
--fields-terminated-by ',' \
--lines-terminated-by '\n'

增量导入,mode=append
根据ID，增量导入

#append
#先在mysql中插入一些数据
#先导入一部分数据
sqoop import \
--connect jdbc:mysql://hostnam:3306/test \
--username root \
--password password \
--table studentinfo \
--m 1
--delete-target-dir \
--target-dir /kb12/sqoop/m2h_incr_append \
--fields-terminated-by ','
--lines-terminted-by '\n'


#再向MySQL中插入一些数据
#再增量导入一部分数据
sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--query "select * from studentinfo where \$CONDITIONS" \
--m 1 \
--target-dir /kb12/sqoop/m2h_incr_append \
--fields-terminated-by ',' \
--lines-terminated-by '\n' \
--append \
--check-column stuId \
--incremental append \
--last-value 20

根据时间，增量导入 mode =lastmodified

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--query "select * from sqp_incr_time where \$CONDITIONS" \
--m 1 \
--target-dir /kb12/sqoop/m2h_incr_lastmodified \
--fields-terminated-by ',' \
--lines-terminated-by '\n' \
--append \
--check-column incrTime \
--incremental lastmodified \
--last-value '2021-06-29 12:23:12.0'

注意：

• 使用lastmodified模式要指定–apend表示在后边追加记录，或–merge-key ‘value’， 表示合并。
• 另外按时间增量导入时，只能导入上次修改到当前时间内的记录。

job

生成一个sqoop任务，生成后不会立即执行，需要手动执行。利用job结合增量导入会极大方便我们，job会帮助我们记录上次导入的值（last-value）。

1. 免密。释放sqoop-site.xml中的下列项

<property>
		<name>sqoop.metastore.client.record.password</name>
		<value>true</value>
		<description>If true, allow saved passwords in the metastore.
		</description>
</property>

2. job相关操作

#查看job
sqoop job --list
#删除job
sqoop job --delete job_m2hv_par
#查看job定义
sqoop job --show job_m2hv_par
#创建job
$ sqoop job \
--create myjob \
--import-all-tables \
--connect jdbc:/mysql://hostname:3306/test \
--username root
--password password
#执行job
$ sqoop job --exec myjob

3. 实例
   将增量导入任务创建为job,然后执行job,系统会自动记录并更新last-value。

sqoop job \
--create job_m2hv_par \
-- import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--query "select * from sqp_incr_time where \$CONDITIONS" \
--m 1 \
--target-dir /kb12/sqoop/m2h_incr_lastmodified \
--fields-terminated-by ',' \
--lines-terminated-by '\n' \
--append \
--check-column incrTime \
--incremental lastmodified \
--last-value '2021-06-29 13:23:12.0'

MySQL -> HDFS (import-all-tables)

导入Mysql数据库中所有表到HDFS

sqoop import-all-tables \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--warehouse-dir /all_tables

HDFS -> MySQL (export)

从HDFS导出表到MySQL中

sqoop export \
--connect jdbc:mysql://singlesimin:3306/test \
--username root \
--password password \
--table sqp_order \
--m 1 \
--export-dir /kb12/hive/orderinfo \
--fields-terminated-by '\t'

Mysql -> Hive (import)

导入数据到hive中，自动建立hive表

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table studentinfo \
--m 1 \
--hive-import \
--hive-table test.studentinfo \
--create-hive-table

筛选增加

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table studentinfo \
--where 'stuId>=49' \
--m 1 \
--hive-import \
--hive-table test.studentinfo \

筛选覆盖

sqoop import \
--connect jdbc:mysql://singlesimin:3306/test \
--username root \
--password password \
--table studentinfo \
--where 'stuId>=49' \
--m 1 \
--hive-import \
--hive-table test.studentinfo \
--hive-overwrite

生成与关系数据库表结构对应的hive表结构

sqoop create-hive-table \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table studentinfo \
--hive-database sqoophive
--hive-table student_hive

导入整个数据库

sqoop import-all-tables
--connect jdbc:mysql://hostname:3306/dbname \
---username root \
--password password \
--m 10 \
--hive-import \
--hive-database sqoophive
--create-hive-table \
--hive-overwrite

【增量导入】
Hive增量导入类似于HDFS增量导入，本质其实一样。【注意！！！】创建hive表时注意指定字段分隔符，增量导入时必须一致，否则会出错误。
（1）append模式
建立与mysql对应的hive表

sqoop create-hive-table \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table studentinfo \
--hive-table test.sqp_incr_stu2 \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'

先插入一些数据

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table studentinfo \
--where 'stuId<=10' \
--m 1 \
--hive-import \
--hive-table test.sqp_incr_stu2 \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'

再增量导入

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table studentinfo \
--m 1 \
--target-dir /user/hive/warehouse/test.db/sqp_incr_stu2 \
--fields-terminated-by '\t' \
--incremental append \
--check-column stuId \
--last-value 10

（2）lastmodified模式
导入一些数据，自动创建hive表

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table sqp_incr_time \
--m 1 \
--hive-import \
--hive-table test.sqp_incr_time \
--create-hive-table \
--fields-terminated-by ',' \
--lines-terminated-by '\n'

再增量导入

sqoop import \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table sqp_incr_time \
--m 1 \
--target-dir /user/hive/warehouse/test.db/sqp_incr_time \
--fields-terminated-by ',' \
--incremental lastmodified \
--append \
--check-column incrTime \
--last-value '2021-07-01 22:33:36.0'

【分区表导入】如每天导入订单信息，按天分区导入。

(1)方法一，先导入HDFS再加载hive表

1. 建立mysql表，并插入数据

create table sqp_partition(
id int,
name varchar(20),
dotime datetime
);

insert into sqp_partition(id,name,dotime) values
(1,'henry','2021-06-01 12:13:14'),
(2,'pola','2021-06-01 12:55:16'),
(3,'ariel','2021-06-01 13:02:55'),
(4,'rose','2021-06-01 13:22:56'),
(5,'jack','2021-06-01 14:15:12');

2. 建立hive表

create table sqp_partition(
id int,
name string,
dotime timestamp
)
partitioned by (dodate date)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

3. 用sqoop将mysql数据导入HDFS

sqoop import \
--connect jdbc:mysql://singlesimin:3306/test \
--username root \
--password password \
--table sqp_partition \
--where "cast(dotime as date)='2021-06-01'" \
--m 1 \
--delete-target-dir \
--target-dir /user/hive/warehouse/test.db/sqp_partition/dodate=2021-06-01 \
--fields-terminated-by ',' \
--lines-terminated-by '\n'

4. 关联hive表和HDFS数据

alter table sqp_partition add partition(dodate='2021-06-01');

5. 通过shell脚本实现自动导入昨天产生的订单

#!/bin/bash

DATE=`date -d '-1 day' +%F`


sqoop import \
--connect jdbc:mysql://singlesimin:3306/test \
--username root \
--password password \
--table sqp_partition \
--where "cast(dotime as date)='$DATE'" \
--m 1 \
--delete-target-dir \
--target-dir /user/hive/warehouse/test.db/sqp_partition/dodate=$DATE \
--fields-terminated-by ',' \
--lines-terminated-by '\n'


hive -e "alter table test.sqp_partition add partition(dodate='$DATE')"

(2)方法二，直接分区导入hive

1. 建立hive分区表

create table sqp_order_par(
buytime date,
name string,
pay decimal(10,2)
)
partitioned by(yearmonth string)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

2. 使用sqoop进行分区导入作业

sqoop import \
--connect jdbc:mysql://singlesimin:3306/test \
--username root \
--password password \
--table sqp_order \
--where 'year(create_date)=2021 and month(create_date)=1' \
--m 1 \
--hive-import \
--hive-table test.sqp_order_par \
--hive-partition-key month \
--hive-partition-value '2021-1' \
--fields-terminated-by ',' \
--lines-terminated-by '\n'

4. 编写shell脚本执行

#!/bin/bash

YEAR=$1
MONTH=$2

sqoop import \
--connect jdbc:mysql://singlesimin:3306/test \
--username root \
--password password \
--table sqp_order \
--where "year(create_date)=$YEAR and month(create_date)=$MONTH" \
--m 1 \
--hive-import \
--hive-table test.sqp_order_par \
--hive-partition-key yearmonth \
--hive-partition-value "$YEAR-$MONTH" \
--fields-terminated-by ',' \
--lines-terminated-by '\n'

codegen

将关系型数据库中的表映射为一个Java类，再该类中有各列独赢的各个字段。

sqoop codegen \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table studentinfo \
--binddri /home/admin/Dektop/studentinfo \
--class-name Student \
--fields-terminated-by ","
--lines-terminated-by "\n"

eval

返回SQL语句执行的结果，经常用于再import数据前验证SQL是否正确，数据是否正常。

sqoop eval \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--query "select * from studentinfo"

merge

将HDFS中不同目录下面的数据合并在一起并放入指定目录中

创建JavaBean
sqoop codegen \
--connect jdbc:mysql://hostname:3306/test \
--username root \
--password password \
--table studentinfo\
--bindri /home/admin/Desktop/staff \
--class-name Student \
--fields-terminated-by '\t'

合并
sqoop merge \
--new-data /test/new/ \
--onto /test/old \
--target-dir /test/merged
--jar-file /home/admin/Desktop/student/Student.jar \
--class-name Student \
--merge-key id

metastore

记录sqoop job的元数据信息，如果不启动该服务，那么默认job元数据的存储目录为~/.sqoop,可在sqoop-site.xml中修改

$ sqoop metastore