SQOOP部署及简单使用

前言

㈠Sqoop官网释义

Apache Sqoop(TM) is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases.

⑴Sqoop分Sqoop1(1.4.X)和Sqoop2(1.99.X)，且二者不兼容

⑵Sqoop由SQL-To-Hadoop简写而来

⑶Sqoop架构

㈡产生背景
RDBMS<==>Hadoop 之间的数据传输传统模式是通过MR的DBInputFormat / TextOutFormat来实现的，这种方式不仅繁琐，而且新的业务线来了之后MR代码需重开发，在这种情况下就产生了SQOOP

部署

一、SQOOP包下载
sqoop-1.4.6-cdh下载

解压到指定目录

tar -zxvf sqoop-1.4.6-cdh5.7.0.tar.gz -C /opt/app/

二、环境变量配置及参数配置
配置环境变量

vi /etc/profileexport SQOOP_HOME=/opt/app/sqoop-1.4.6-cdh5.7.0export PATH=$SQOOP_HOME/bin:$PATH

参数配置

vi sqoop-env.shexport HADOOP_COMMON_HOME=/opt/app/2.6.0-cdh5.7.0export HADOOP_MAPRED_HOME=/opt/app/2.6.0-cdh5.7.0export HIVE_HOME=/opt/app/hive-1.1.0-cdh5.7.0

sqoop-env.sh中还有其他参数，根据自己的需求配置。

三、简单应用
1、执行sqoop help命令查看sqoop使用帮助

sqoop helpusage: sqoop COMMAND [ARGS]Available commands:  codegen            Generate code to interact with database records  create-hive-table  Import a table definition into Hive  eval               Evaluate a SQL statement and display the results  export             Export an HDFS directory to a database table  help               List available commands  import             Import a table from a database to HDFS  import-all-tables  Import tables from a database to HDFS  import-mainframe   Import datasets from a mainframe server to HDFS  job                Work with saved jobs  list-databases     List available databases on a server  list-tables        List available tables in a database  merge              Merge results of incremental imports  metastore          Run a standalone Sqoop metastore  version            Display version information

2、从帮助命令可以知道查看sqoop版本信息命令如下

[root@hadoop001 conf]# sqoop versionWarning: /opt/app/sqoop-1.4.6-cdh5.7.0/../hbase does not exist! HBase imports will fail.Please set $HBASE_HOME to the root of your HBase installation.Warning: /opt/app/sqoop-1.4.6-cdh5.7.0/../hcatalog does not exist! HCatalog jobs will fail.Please set $HCAT_HOME to the root of your HCatalog installation.Warning: /opt/app/sqoop-1.4.6-cdh5.7.0/../accumulo does not exist! Accumulo imports will fail.Please set $ACCUMULO_HOME to the root of your Accumulo installation.Warning: /opt/app/sqoop-1.4.6-cdh5.7.0/../zookeeper does not exist! Accumulo imports will fail.Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.17/10/10 15:23:26 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.7.0Sqoop 1.4.6-cdh5.7.0

3、通过sqoop list-tables –help查看list-tables如何使用（其他命令的使用也是通过这中方式来查看）

 sqoop list-tables --help

从帮助信息可以知道可以通过以下命令来列出一个数据库中所有的表

 sqoop list-tables --connect jdbc:mysql://hadoop001:3306/hive  --username root   --password 123456

[root@hadoop001 conf]# sqoop list-tables \> --connect jdbc:mysql://hadoop001:3306/hive \> --username root \> --password 123456Warning: /opt/app/sqoop-1.4.6-cdh5.7.0/../hbase does not exist! HBase imports will fail.Please set $HBASE_HOME to the root of your HBase installation.Warning: /opt/app/sqoop-1.4.6-cdh5.7.0/../hcatalog does not exist! HCatalog jobs will fail.Please set $HCAT_HOME to the root of your HCatalog installation.Warning: /opt/app/sqoop-1.4.6-cdh5.7.0/../accumulo does not exist! Accumulo imports will fail.Please set $ACCUMULO_HOME to the root of your Accumulo installation.Warning: /opt/app/sqoop-1.4.6-cdh5.7.0/../zookeeper does not exist! Accumulo imports will fail.Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.17/10/10 15:36:39 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.7.017/10/10 15:36:39 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.17/10/10 15:36:39 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.BUCKETING_COLSCDSCOLUMNS_V2DATABASE_PARAMSDBSFUNCSFUNC_RUGLOBAL_PRIVSIDXSINDEX_PARAMSPARTITIONSPARTITION_KEYSPARTITION_KEY_VALSPARTITION_PARAMSPART_COL_PRIVSPART_COL_STATSPART_PRIVSROLESSDSSD_PARAMSSEQUENCE_TABLESERDESSERDE_PARAMSSKEWED_COL_NAMESSKEWED_COL_VALUE_LOC_MAPSKEWED_STRING_LISTSKEWED_STRING_LIST_VALUESSKEWED_VALUESSORT_COLSTABLE_PARAMSTAB_COL_STATSTBLSTBL_COL_PRIVSTBL_PRIVSVERSION