Sqoop 学习笔记(我也刚学)

绝地灬酷狼 2023-03-01 09:11 23阅读 0赞

## Sqoop环境搭建 ##

### 下载并解压 ###

下载地址:http://mirrors.hust.edu.cn/apache/sqoop/1.4.6/

上传安装包到虚拟机

解压安装`tar -zxf sqoop-1.4.6-cdh5.14.2.tar.gz -C /opt/softs/`

创建软链接`ln -s sqoop-1.4.6-cdh5.14.2.tar.gz sqoop`

配置环境变量`vi /etc/profile ...`

### 修改配置文件 ###

#### sqoop-env.sh ####

cd /opt/softs/sqoop/conf   #进入配置文件所在目录
    cp sqoop-env-template.sh sqoop-env.sh  #复制副本文件，并修改文件名
    vi sqoop-env.sh
    #---------------------------------------------
    # 路径根据个人安装路径修改
    export HADOOP_COMMON_HOME=/opt/install/hadoop
    export HADOOP_MAPRED_HOME=/opt/install/hadoop
    export HIVE_HOME=/opt/install/hive
    export ZOOKEEPER_HOME=/opt/install/zookeeper
    export ZOOCFGDIR=/opt/install/zookeeper
    export HBASE_HOME=/opt/install/hbase

### 拷贝jdbc驱动 ###

使用sqoop，需要根据使用数据库拷贝对应驱动到sqoop的lib目录下(驱动网上多的是)

`cp mysql-connector-java-5.1.27-bin.jar /opt/install/sqoop/lib/`

### 验证sqoop ###

sqoop help #检测是否成功配置
    sqoop list-databases --connect jdbc:mysql://hadoop1:3306/ --username root --password ok #检测是否可以成功连接数据库

## 作用 ##

一个开源工具，sqoop主要用于Hadoop(Hive)与传统数据库(mysql)间进行数据迁移

## 原理 ##

将导入或导出命令翻译成MapReduce程序实现

> 在翻译出的MapReduce中主要是对inputformat和outputformat进行定制

## RDB导入数据库到HDFS ##

sqoop import \
    --connect jdbc:mysql://localhost:3306/retail_db \	#配置数据库连接为mysql
    --driver com.mysql.jdbc.Driver \
    --table customers \
    --username root \
    --password ok \
    --target-dir /data/retail_db/customers \	#hdfs目标路径
    --m 3		#指定Mapper数量

### where ###

# 使用where语句
    sqoop import \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --driver com.mysql.jdbc.Driver \
    --table orders \
    --where "orders_id<100" \
    --username root \
    --password ok \
    --delete-target-dir \ 		#如果目录已存在，则删除(覆盖数据)
    --target-dir /data/retail_db/orders \
    --m 3

### columns指定列 ###

# 使用columns过滤,导入指定列
    sqoop import \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --driver com.mysql.jdbc.Driver \
    --table customers \
    --columns "customer_id,customers_fname,customer_lname" \
    --username root \
    --password ok \
    --target-dir /data/retail_db/customers \
    --m 3

### query方式导入 ###

使用查询语句进行过滤
    sqoop import \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --driver com.mysql.jdbc.Driver \
    --query "select * from orders where order_status!='CLOSED' and \$CONDITIONS" \  
    #所有查询都应该以\$CONDITIONS结束;sqoop内部使用该条件将记录范围分发给所有Mapper
    --username root \
    --password ok \
    --split-by host \ # 指定用于分隔数据的列为host
    --delete-target-dir \
    --target-dir /data/retail_db/orders \
    --m 3

### sqoop增量导入数据 ###

append指定一个递增的列,只导入比last-value更大的数据

`--incremental append --check-column id --last-value 0`

也可以不用append,根据时间戳增量导入

`--incremental lastmodified --check-column time --last-value '2013-01-01 11:0:00`

增量导入
    sqoop import \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --driver com.mysql.jdbc.Driver \
    --table orders \
    --username root \
    --password ok \
    --incremental append \ 		#append 追加数据至已经存在的数据集
    --check-column order_date 	#指定递增的列
    --last-value '2013-07-24 00:00:00'#指定上一次导入的最大值
    --delete-target-dir \
    --target /data/retail_db/orders
    --m 3

> 导入时可以指定文件格式参数(加在语句末尾)  
> –as-textfile 导入数据为text文件（默认）  
> –as-avrodatafile 导入数据为avro文件  
> –as-sequencefile 导入数据为sequence文件  
> –as-parquetfile 导入数据为parquet文件

### 使用job增量导入(懒人最爱，拒绝熬夜) ###

# 创建job
    sqoop job --create mysql2hdfs \
    --import \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --driver com.mysql.jdbc.Driver \
    --table orders \
    --username root \
    --password ok \
    --incremental append \ 		#append 追加数据至已经存在的数据集
    --check-column order_date 	#指定递增的列
    --last-value '0'#指定上一次导入的最大值
    --delete-target-dir \
    --target /data/retail_db/orders
    --m 3
    
    # 查看job
    sqoop job --list
    
    #执行job
    sqoop job --exec job名

> 每次job执行成功之后都会修改 --last-value 值 将最后一次的最大值填充进去  
> 这里的’0’没有实际意义，只是为了保证第一次数据导入时值最小

## RDB 导入数据到Hive ##

> 该过程分为两步，第一步将数据导入到HDFS，第二步将导入到HDFS的数据迁移到Hive仓库，第一步默认的临时目录是/user/atguigu/表名

执行导入到Hive相关操作前，需要复制hive相关jar包

复制hive相关jar包，到sqoop/lib
    cp /opt/softs/hive/lib/hive-common-1.1.0-cdh5.14.2.jar /opt/softs/sqoop/lib/
    cp /opt/softs/hive/lib/hive-shims* /opt/softs/sqoop/lib/

直接导入到Hive(不常用)

sqoop import \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --driver com.mysql.jdbc.Driver \
    --table orders \
    --username root \
    --password ok \
    --hive-import \			#指定导入到Hive
    --create-hive-table 	#指定创建一个新的Hive表
    --hive-database retail_db
    --hive-table orders
    --m 3

导入数据到Hive分区中

# 删除Hive表
    drop table if exists orders;
    
    # 导入
    sqoop import \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --driver com.mysql.jdbc.Driver \
    --query "select order_id,order_status from orders where order_date>='2013-11-03' and order_date <'2013-11-04' and \$CONDIDIONS" \
    --username root \
    --password ok \
    --delete-target-dir \
    --target-dir /data/retail_db/orders \
    --split-by order_id \
    --hive-import \
    --hive-database retail-db \
    --hive-table orders \
    --hive-partition-key "order_date" \
    --hive-partition-value "2013-11-03" \
    --m 3
    
    ## 注意：分区字段不能当成普通字段导入表中

## RDB导入数据到HBase ##

sqoop import \
    --connect jdbc:mysql://localhost:3306/retail_db \
    # --driver com.mysql.jdbc.Driver \
    --username root \
    --password ok \ 
    --table orders
    --columns "order_id,order_date" \
    --hbase-tbale orders \
    --column-family 0 \
    --hbase-row-key order_id \
    --m 3

> 若sqoop不能自动创建HBase表，则通过手动创建

## HDFS/Hive导出到RDB ##

> 导出为从大数据集群向非大数据集群传输数据

sqoop export \
    --connect jdbc:mysql://localhost:3306/retail_db \
    --username root
    --password ok
    --table orders \
    --m 1 \  # num-mappers 1
    --export-dir .../warehouse/orders \
    --input-fields-terminated-by "\t"

## 脚本打包 ##

可以使用opt格式的文件打包sqoop命令，然后执行

# 创建一个.opt文件
    $ touch job_HDFS2RDBMS.opt
    
    # 编写sqoop脚本 //导入到hbase
    $ vi job_HDFS2RDBMS.opt
    # ------------
    import 
    --connect 
    jdbc:mysql://localhost:3306/retail_db 
    --username 
    root 
    --password 
    ok  
    --table 
    orders
    --columns 
    "order_id,order_date" 
    --hbase-tbale 
    orders 
    --column-family 
    0 
    --hbase-row-key 
    order_id 
    --m 
    3
    
    # --------------
    # 执行脚本
    $ sqoop --option-file  job_HDFS2RDBMS.opt