一、使用命令

psql数据库操作

  1. #查看数据库
  2. sqoop list-databases --connect jdbc:postgresql://10.121.120.42:5432/etl_service --username postgres --password hawk@159
  4. #sqoop根据postgresql表创建hive表
  5. sqoop create-hive-table --connect jdbc:postgresql://10.121.120.42:5432/etl_service --username postgres --password hawk@159 --table aijie --hive-table aijie
  7. #psql->hive
  8. sqoop import --connect jdbc:postgresql://10.121.120.42:5432/etl_service --username postgres --password hawk@159 --table aijie -m 1 --hive-home /home/hive --hive-import --create-hive-table --hive-database prdhawk --hive-table aijie

mysql操作

  1. sqoop --options-file xxx.sqoop
  1. import
  2. --connect
  3. jdbc:mysql://10.161.12.98:4606/tianyan?useUnicode=true&characterEncoding=UTF-8
  4. --username
  5. tianyan
  6. --password
  7. ywjk@123
  8. --as-textfile
  9. --columns
  10. id,host,monitor_time,monitor_time_begin,monitor_time_end
  11. --table
  12. alarm_host
  13. --fields-terminated-by
  14. '\001'
  15. --delete-target-dir
  16. --target-dir
  17. /apps/hive/warehouse/tianyan.db/sqoop/alarm_host_data
  18. -m
  19. 1
  21. --hive-home                   
  22. /home/hive             
  23. --hive-import
  24. --create-hive-table
  25. --hive-database
  26. tianyan
  27. --hive-table
  28. alarm_host
  1. import 
  2. --connect 
  3. jdbc:mysql://node1:3306/songresult?dontTrackOpenResources=true&defaultFetchSize=10000&useCursorFetch=true&useUnicode=yes&characterEncod
  4. ing=utf8 --username 
  5. root
  6. --password 
  7. 123
  8. --table 
  9. song
  10. --target-dir 
  11. /user/hive/warehouse/data/song/TO_SONG_INFO_D/ 
  12. --delete-target-dir 
  13. --num-mappers 
  14. 1 
  15. --hive-import 
  16. --hive-database 
  17. spark 
  18. --hive-overwrite 
  19. --fields-terminated-by 
  20. '\t' 
  21. --hive-table 
  22. TO_SONG_INFO_D

oracle操作

  1. sqoop import --connect jdbc:oracle:thin:@10.96.80.188:1521:PRDMDMN1 --username PRDHAWK --password PRDHAWK0827 --table PRDHAWK.ADDRESS -m 1 --hive-home /home/hive --hive-import --create-hive-table --hive-database prdhawk --hive-table address

17/05/02 18:15:47 ERROR tool.ImportTool: Imported Failed: There is no column found in the target table tysecdb.secsight_config_count. Please ensure that your table name is correct
sqoop导入到出时 对应RDBMS数据库的表名必须大写 不支持小写。

二、安装配置

下载地址：https://mirrors.tuna.tsinghua.edu.cn/apache/sqoop/1.4.7/
1、把sqoop-1.4.7.binhadoop-2.6.0.tar.gz上传到linux
2、解压sqoop-1.4.7.binhadoop-2.6.0.tar.gz到/opt/module/目录下

  1. tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/module/
  2. cd /opt/module/
  3. mv sqoop-1.4.7.bin__hadoop-2.6.0/ sqoop

3、修改/opt/module/sqoop/conf目录下的sqoop-env-template.sh名称为sqoop-env.sh

  1. mv sqoop-env-template.sh sqoop-env.sh
  2. #配置sqoop-env.sh文件
  3. vim sqoop-env.sh
  4. #根据实际情况填写
  5. export HADOOP_COMMON_HOME=/opt/module/hadoop-2.7.2
  6. export HADOOP_MAPRED_HOME=/opt/module/hadoop-2.7.2
  7. export HIVE_HOME=/opt/module/hive
  8. export HIVE_CONF_DIR=/opt/module/hive/conf
  9. export ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10
  10. export ZOOCFGDIR=/opt/module/zookeeper-3.4.10
  13. export HADOOP_COMMON_HOME=/apache/hadoop
  14. export HADOOP_MAPRED_HOME=/apache/hadoop
  15. export HIVE_HOME=/apache/hive
  16. export ZOOKEEPER_HOME=/usr/local/apache-zookeeper-3.6.3-bin
  17. export ZOOCFGDIR=/usr/local/apache-zookeeper-3.6.3-bin
  18. export HIVE_CONF_DIR=/apache/hive/conf

4、链接数据库需要添加JDBC驱动

  1. /opt/module/sqoop/lib/

5、添加hive驱动

  1. cp -p /apache/hive/lib/hive-common-2.2.0.jar /opt/module/sqoop/lib/

6、添加环境变量

  1. export SQOOP_HOME=/opt/module/sqoop
  2. export ZOOKEEPER_HOME=/usr/local/apache-zookeeper-3.6.3-bin
  3. export PATH=/apache/jdk/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/apache/hadoop/bin:/apache/hadoop/sbin:/apache/scala/bin:/apache/spark/bin:/apache/hive/bin:/apache/livy/bin:/bin:/bin:/opt/module/sqoop/bin:/usr/local/apache-zookeeper-3.6.3-bin/bin
  4. export HIVE_CONF_DIR=/apache/hive/conf