使用Sqoop进行数据的导入

一 下载地址

http://archive.apache.org/dist/sqoop/1.4.4/

[root@localhost bin]# wget http://archive.apache.org/dist/sqoop/1.4.4/sqoop-1.4.4.bin__hadoop-0.20.tar.gz

二 解压sqoop

[root@localhost bin]# tar -zxvf sqoop-1.4.4.bin__hadoop-0.20.tar.gz

三 导入两个环境变量和导入ojdbc14.jar库

[root@localhost ~]# export HADOOP_COMMON_HOME=/opt/hadoop-1.2.1/

[root@localhost ~]# export HADOOP_MAPRED_HOME=/opt/hadoop-1.2.1/

四 sqoop程序结构认识

[root@localhost bin]# ls

configure-sqoop sqoop-codegen sqoop-import sqoop-merge

configure-sqoop.cmd sqoop-create-hive-table sqoop-import-all-tables sqoop-metastore

emp.java sqoop-eval sqoop-job sqoop-version

sqoop sqoop-export sqoop-list-databases start-metastore.sh

sqoop.cmd sqoop-help sqoop-list-tables stop-metastore.sh

五 准备oracle数据库中的表

SQL> select * from emp;

EMPNO ENAME EMPJOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

1001 Tome 5000 10

1002 Tome 5000 10

7369 SMITH CLERK 7902 17-12月-80 4800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 5600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 5250 500 30

7566 JONES MANAGER 7839 02-4月 -81 9075 20

7654 MARTIN SALESMAN 7698 28-9月 -81 5250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 8850 30

7782 CLARK MANAGER 7839 09-6月 -81 8450 10

7788 SCOTT ANALYST 7566 19-4月 -87 7000 20

7839 KING PRESIDENT 17-11月-81 12111 10

EMPNO ENAME EMPJOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7844 TURNER SALESMAN 7698 08-9月 -81 5500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 5100 20

7900 JAMES CLERK 7698 03-12月-81 4950 30

7902 FORD ANALYST 7566 03-12月-81 7000 20

7934 MILLER CLERK 7782 23-1月 -82 5300 10

已选择16行。

六 实战

1、使用Sqoop导入oracle数据到HDFS中

./sqoop import --connect jdbc:oracle:thin:@192.168.0.107:1521:orcl --username scott --password password --table emp --columns 'empno,ename,empjob,sal,deptno' -m 1 --target-dir 'sqoop/emp'

Warning: /usr/lib/hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.

Please set $HCAT_HOME to the root of your HCatalog installation.

Warning: $HADOOP_HOME is deprecated.

17/08/27 17:27:17 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

17/08/27 17:27:17 INFO manager.SqlManager: Using default fetchSize of 1000

17/08/27 17:27:17 INFO tool.CodeGenTool: Beginning code generation

17/08/27 17:27:18 INFO manager.OracleManager: Time zone has been set to GMT

17/08/27 17:27:18 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM emp t WHERE 1=0

17/08/27 17:27:18 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-1.2.1

Note: /tmp/sqoop-root/compile/01b577f440858d215f88c58ab15210d6/emp.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

17/08/27 17:27:20 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/01b577f440858d215f88c58ab15210d6/emp.jar

17/08/27 17:27:20 INFO manager.OracleManager: Time zone has been set to GMT

17/08/27 17:27:20 INFO mapreduce.ImportJobBase: Beginning import of emp

17/08/27 17:27:24 INFO mapred.JobClient: Running job: job_201708271710_0002

17/08/27 17:27:25 INFO mapred.JobClient: map 0% reduce 0%

17/08/27 17:28:46 INFO mapred.JobClient: map 100% reduce 0%

17/08/27 17:28:58 INFO mapred.JobClient: Job complete: job_201708271710_0002

17/08/27 17:28:58 INFO mapred.JobClient: Counters: 18

17/08/27 17:28:58 INFO mapred.JobClient: Job Counters

17/08/27 17:28:58 INFO mapred.JobClient: SLOTS_MILLIS_MAPS=88849

17/08/27 17:28:58 INFO mapred.JobClient: Total time spent by all reduces waiting after reserving slots (ms)=0

17/08/27 17:28:58 INFO mapred.JobClient: Total time spent by all maps waiting after reserving slots (ms)=0

17/08/27 17:28:58 INFO mapred.JobClient: Launched map tasks=1

17/08/27 17:28:58 INFO mapred.JobClient: SLOTS_MILLIS_REDUCES=0

17/08/27 17:28:58 INFO mapred.JobClient: File Output Format Counters

17/08/27 17:28:58 INFO mapred.JobClient: Bytes Written=423

17/08/27 17:28:58 INFO mapred.JobClient: FileSystemCounters

17/08/27 17:28:58 INFO mapred.JobClient: HDFS_BYTES_READ=87

17/08/27 17:28:58 INFO mapred.JobClient: FILE_BYTES_WRITTEN=61082

17/08/27 17:28:58 INFO mapred.JobClient: HDFS_BYTES_WRITTEN=423

17/08/27 17:28:58 INFO mapred.JobClient: File Input Format Counters

17/08/27 17:28:58 INFO mapred.JobClient: Bytes Read=0

17/08/27 17:28:58 INFO mapred.JobClient: Map-Reduce Framework

17/08/27 17:28:58 INFO mapred.JobClient: Map input records=16

17/08/27 17:28:58 INFO mapred.JobClient: Physical memory (bytes) snapshot=66248704

17/08/27 17:28:58 INFO mapred.JobClient: Spilled Records=0

17/08/27 17:28:58 INFO mapred.JobClient: CPU time spent (ms)=960

17/08/27 17:28:58 INFO mapred.JobClient: Total committed heap usage (bytes)=15794176

17/08/27 17:28:58 INFO mapred.JobClient: Virtual memory (bytes) snapshot=855474176

17/08/27 17:28:58 INFO mapred.JobClient: Map output records=16

17/08/27 17:28:58 INFO mapred.JobClient: SPLIT_RAW_BYTES=87

17/08/27 17:28:58 INFO mapreduce.ImportJobBase: Transferred 423 bytes in 97.9276 seconds (4.3195 bytes/sec)

17/08/27 17:28:58 INFO mapreduce.ImportJobBase: Retrieved 16 records.

校验结果

2、使用Sqoop导入oracle数据到Hive中

[root@localhost bin]# ./sqoop import --hive-import --connect jdbc:oracle:thin:@192.168.0.107:1521:orcl --username scott --password password --table emp -m 1 --columns 'EMPNO,ENAME,EMPJOB,SAL,DEPTNO'

Warning: /usr/lib/hbase does not exist! HBase imports will fail.

Please set $HBASE_HOME to the root of your HBase installation.

Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.