Sqoop导入Oracle数据表到HDFS

1、问题

[root@node1 sqoop-1.4.7]# bin/sqoop import --connect jdbc:oracle:thin:@192.168.1.100:1521:TPADC --username test --password test --table fund_info  -m 1;
Warning: /opt/sqoop-1.4.7/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
18/05/22 15:57:38 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/22 15:57:38 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/22 15:57:39 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
18/05/22 15:57:39 INFO manager.SqlManager: Using default fetchSize of 1000
18/05/22 15:57:39 INFO tool.CodeGenTool: Beginning code generation
18/05/22 15:57:57 INFO manager.OracleManager: Time zone has been set to GMT
18/05/22 15:57:57 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM fund_info t WHERE 1=0
18/05/22 15:57:58 ERROR tool.ImportTool: Import failed: There is no column found in the target table fund_info. Please ensure that your table name is correct.

2、解决办法，将表名改成大写

[root@node1 sqoop-1.4.7]# bin/sqoop import --connect jdbc:oracle:thin:@192.168.1.100:1521:TPADC --table FUND_INFO --username test --password test -m 1;
Warning: /opt/sqoop-1.4.7/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
18/05/22 16:00:59 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/22 16:00:59 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/22 16:00:59 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
18/05/22 16:00:59 INFO manager.SqlManager: Using default fetchSize of 1000
18/05/22 16:00:59 INFO tool.CodeGenTool: Beginning code generation
18/05/22 16:01:35 INFO manager.OracleManager: Time zone has been set to GMT
18/05/22 16:01:35 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM fund_info t WHERE 1=0
18/05/22 16:01:35 ERROR tool.ImportTool: Import failed: There is no column found in the target table fund_info. Please ensure that your table name is correct.
[root@node1 sqoop-1.4.7]# bin/sqoop import --connect jdbc:oracle:thin:@192.168.1.100:1521:TPADC --table fund_info  --username test --password test -m 1;
Warning: /opt/sqoop-1.4.7/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
18/05/22 17:05:23 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/22 17:05:24 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/22 17:05:24 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
18/05/22 17:05:24 INFO manager.SqlManager: Using default fetchSize of 1000
18/05/22 17:05:24 INFO tool.CodeGenTool: Beginning code generation
18/05/22 17:05:38 INFO manager.OracleManager: Time zone has been set to GMT
18/05/22 17:05:38 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM fund_info t WHERE 1=0
18/05/22 17:05:38 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-2.7.5
Note: /tmp/sqoop-root/compile/4075f495e729969d29b0eaff12af4492/TPA_DW_DIM_FUND_INFO.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
18/05/22 17:05:41 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/4075f495e729969d29b0eaff12af4492/fund_info.jar
18/05/22 17:05:41 INFO manager.OracleManager: Time zone has been set to GMT
18/05/22 17:05:42 WARN manager.OracleManager: The table fund_info contains a multi-column primary key. Sqoop will default to the column VC_SOURCE only for this job.
18/05/22 17:05:42 INFO manager.OracleManager: Time zone has been set to GMT
18/05/22 17:05:43 WARN manager.OracleManager: The table fund_info contains a multi-column primary key. Sqoop will default to the column VC_SOURCE only for this job.
18/05/22 17:05:43 INFO mapreduce.ImportJobBase: Beginning import of fund_info
18/05/22 17:05:43 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
18/05/22 17:05:43 INFO manager.OracleManager: Time zone has been set to GMT
18/05/22 17:05:44 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
18/05/22 17:05:45 INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2
18/05/22 17:05:47 INFO db.DBInputFormat: Using read commited transaction isolation
18/05/22 17:05:47 INFO mapreduce.JobSubmitter: number of splits:1
18/05/22 17:05:47 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1526097883376_0002
18/05/22 17:05:47 INFO impl.YarnClientImpl: Submitted application application_1526097883376_0002
18/05/22 17:05:48 INFO mapreduce.Job: The url to track the job: http://bigdata03-test:8088/proxy/application_1526097883376_0002/
18/05/22 17:05:48 INFO mapreduce.Job: Running job: job_1526097883376_0002
18/05/22 17:05:57 INFO mapreduce.Job: Job job_1526097883376_0002 running in uber mode : false
18/05/22 17:05:57 INFO mapreduce.Job:  map 0% reduce 0%
18/05/22 17:06:09 INFO mapreduce.Job:  map 100% reduce 0%
18/05/22 17:06:09 INFO mapreduce.Job: Job job_1526097883376_0002 completed successfully
18/05/22 17:06:09 INFO mapreduce.Job: Counters: 30
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=142851
        FILE: Number of read operations=0
        FILE: Number of large read operations=0
        FILE: Number of write operations=0
        HDFS: Number of bytes read=87
        HDFS: Number of bytes written=565997
        HDFS: Number of read operations=4
        HDFS: Number of large read operations=0
        HDFS: Number of write operations=2
    Job Counters 
        Launched map tasks=1
        Other local map tasks=1
        Total time spent by all maps in occupied slots (ms)=10014
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=10014
        Total vcore-milliseconds taken by all map tasks=10014
        Total megabyte-milliseconds taken by all map tasks=10254336
    Map-Reduce Framework
        Map input records=1123
        Map output records=1123
        Input split bytes=87
        Spilled Records=0
        Failed Shuffles=0
        Merged Map outputs=0
        GC time elapsed (ms)=64
        CPU time spent (ms)=2870
        Physical memory (bytes) snapshot=263938048
        Virtual memory (bytes) snapshot=2146078720
        Total committed heap usage (bytes)=112721920
    File Input Format Counters 
        Bytes Read=0
    File Output Format Counters 
        Bytes Written=565997
18/05/22 17:06:09 INFO mapreduce.ImportJobBase: Transferred 552.7314 KB in 24.7303 seconds (22.3504 KB/sec)
18/05/22 17:06:09 INFO mapreduce.ImportJobBase: Retrieved 1123 records.
[root@node1 sqoop-1.4.7]#

3、查看HDFS上数据

[root@node1 sqoop-1.4.7]# hdfs dfs -ls /user/root
Found 3 items
drwxr-xr-x   - root supergroup          0 2018-05-22 17:06 /user/root/FUND_INFO
drwxr-xr-x   - root supergroup          0 2018-04-23 14:05 /user/root/input
drwxr-xr-x   - root supergroup          0 2018-05-22 13:36 /user/root/users
[root@node1 sqoop-1.4.7]# hdfs dfs -ls /user/root/FUND_INFO
Found 2 items
-rw-r--r--   3 root supergroup          0 2018-05-22 17:06 /user/root/FUND_INFO/_SUCCESS
-rw-r--r--   3 root supergroup     565997 2018-05-22 17:06 /user/root/FUND_INFO/part-m-00000
[root@node1 sqoop-1.4.7]#