Sqoop 搭建
系统环境
操作系统： CentOS 7 主机名： centos02 IP： 192.168.122.1 Java： 1.8 Hadoop： 2.8.5 Hive： 2.3.4 MySQL： 8.0.12 Sqoop： 1.4.7
新建Sqoop目录
[root@centos02 centos02]# cd /opt/bigdata [root@centos02 bigdata]# mkdir -m 777 sqoop [root@centos02 bigdata]#
解压Sqoop到指定目录
[root@centos02 bigdata]# cd /opt [root@centos02 bigdata]# tar -xvzf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/bigdata/sqoop [root@centos02 bigdata]# [root@centos02 opt]# cd ./bigdata/sqoop [root@centos02 sqoop]# ls sqoop-1.4.7.bin__hadoop-2.6.0 [root@centos02 sqoop]#
重命名目录名称
[root@centos02 sqoop]# mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.7 oot@centos02 sqoop]# ls sqoop-1.4.7 [root@centos02 sqoop]#
进入Sqoop目录
[root@centos02 sqoop]# cd ./sqoop-1.4.7 [root@centos02 sqoop-1.4.7]# ll 总用量 2020 drwxr-xr-x 2 centos02 centos02 4096 12月 19 2017 bin -rw-rw-r-- 1 centos02 centos02 55089 12月 19 2017 build.xml -rw-rw-r-- 1 centos02 centos02 47426 12月 19 2017 CHANGELOG.txt -rw-rw-r-- 1 centos02 centos02 9880 12月 19 2017 COMPILING.txt drwxr-xr-x 2 centos02 centos02 150 12月 19 2017 conf drwxr-xr-x 5 centos02 centos02 169 12月 19 2017 docs drwxr-xr-x 2 centos02 centos02 96 12月 19 2017 ivy -rw-rw-r-- 1 centos02 centos02 11163 12月 19 2017 ivy.xml drwxr-xr-x 2 centos02 centos02 4096 12月 19 2017 lib -rw-rw-r-- 1 centos02 centos02 15419 12月 19 2017 LICENSE.txt -rw-rw-r-- 1 centos02 centos02 505 12月 19 2017 NOTICE.txt -rw-rw-r-- 1 centos02 centos02 18772 12月 19 2017 pom-old.xml -rw-rw-r-- 1 centos02 centos02 1096 12月 19 2017 README.txt -rw-rw-r-- 1 centos02 centos02 1108073 12月 19 2017 sqoop-1.4.7.jar -rw-rw-r-- 1 centos02 centos02 6554 12月 19 2017 sqoop-patch-review.py -rw-rw-r-- 1 centos02 centos02 765184 12月 19 2017 sqoop-test-1.4.7.jar drwxr-xr-x 7 centos02 centos02 73 12月 19 2017 src drwxr-xr-x 4 centos02 centos02 114 12月 19 2017 testdata [root@centos02 sqoop-1.4.7]#
[root@centos02 sqoop-1.4.7]# cd ./conf [root@centos02 conf]# ll 总用量 28 -rw-rw-r-- 1 centos02 centos02 3895 12月 19 2017 oraoop-site-template.xml -rw-rw-r-- 1 centos02 centos02 1404 12月 19 2017 sqoop-env-template.cmd -rwxr-xr-x 1 centos02 centos02 1345 12月 19 2017 sqoop-env-template.sh -rw-rw-r-- 1 centos02 centos02 6044 12月 19 2017 sqoop-site-template.xml -rw-rw-r-- 1 centos02 centos02 6044 12月 19 2017 sqoop-site.xml [root@centos02 conf]#
重命名Sqoop运行环境文件
[root@centos02 conf]# mv sqoop-env-template.sh sqoop-env.sh [root@centos02 conf]# ll 总用量 28 -rw-rw-r-- 1 centos02 centos02 3895 12月 19 2017 oraoop-site-template.xml -rwxr-xr-x 1 centos02 centos02 1345 12月 19 2017 sqoop-env.sh -rw-rw-r-- 1 centos02 centos02 1404 12月 19 2017 sqoop-env-template.cmd -rw-rw-r-- 1 centos02 centos02 6044 12月 19 2017 sqoop-site-template.xml -rw-rw-r-- 1 centos02 centos02 6044 12月 19 2017 sqoop-site.xml [root@centos02 conf]#
修改 Sqoop运行环境变量
[root@centos02 conf]# vim sqoop-enc.sh
export HADOOP_COMMON_HOME=/opt/bigdata/hadoop/hadoop-2.8.5 export HADOOP_MAPRED_HOME=/opt/bigdata/hadoop/hadoop-2.8.5 export HIVE_HOME=/opt/bigdata/hive/hive-2.3.4 export HIVE_CONF_DIR=${HIVE_HOME}/conf
添加环境变量
[root@centos02 conf]# vim /etc/profile
export SQOOP_HOME=/opt/bigdata/sqoop/sqoop-1.4.7 export PATH=$PATH:$SQOOP_HOME/bin export CLASSPATH=$CLASSPATH:$SQOOP_HOME/lib
[root@centos02 conf]# source /etc/profile
将MySQL的驱动包拷贝到sqoop的lib目录下
[root@centos02 conf]# cp /opt/mysql-connector-java-8.0.13.jar $SQOOP_HOME/lib
启动 Sqoop
[root@centos02 sqoop-1.4.7]# cd $SQOOP_HOME/bin [root@centos02 bin]# pwd /opt/bigdata/sqoop/sqoop-1.4.7/bin [root@centos02 bin]#
[root@centos02 bin]# sqoop Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../zookeeper does not exist! Accumulo imports will fail. Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation. Try 'sqoop help' for usage. [root@centos02 bin]#
[root@centos02 bin]# sqoop help Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../zookeeper does not exist! Accumulo imports will fail. Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation. 19/08/30 02:17:08 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7 usage: 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 See 'sqoop help COMMAND' for information on a specific command. [root@centos02 bin]#
列出MySQL的数据库
[root@centos02 bin]# sqoop list-databases --connect jdbc:mysql://centos02:3306 --username root -password sa123_*****. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../zookeeper does not exist! Accumulo imports will fail. Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation. 19/08/30 02:22:47 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7 19/08/30 02:22:47 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 19/08/30 02:22:47 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. mysql information_schema performance_schema sys testdb OfficialCashMid hiveDB [root@centos02 bin]#
列出数据库的所有表
[root@centos02 bin]# sqoop list-tables --connect jdbc:mysql://centos02:3306/testdb --username root -password sa123_*****. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../zookeeper does not exist! Accumulo imports will fail. Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation. 19/08/30 02:31:24 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7 19/08/30 02:31:24 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 19/08/30 02:31:24 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. TUser [root@centos02 bin]#
不暴露密码，需要输入密码的方式
[root@centos02 bin]# sqoop list-tables --connect jdbc:mysql://centos02:3306/testdb --username root -P Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. Warning: /opt/bigdata/sqoop/sqoop-1.4.7/../zookeeper does not exist! Accumulo imports will fail. Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation. 19/08/30 02:32:23 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7 Enter password: 19/08/30 02:32:38 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. TUser [root@centos02 bin]#
mysql> use testdb; Database changed mysql> mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | TUser | +------------------+ 1 row in set (0.08 sec) mysql>
