Sqoop导入Oracle数据表到Hive

最新推荐文章于 2024-04-15 10:19:04 发布
最新推荐文章于 2024-04-15 10:19:04 发布
阅读量1.7k 收藏 2
点赞数
分类专栏： Hadoop基础教程 文章标签： sqoop oracle hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chengyuqiang/article/details/80427805
版权

1、问题1

[root@node1 sqoop-1.4.7]# bin/sqoop import --connect jdbc:oracle:thin:@192.168.1.31:1521:users --table FUND_INFO --username tpa_query --password tpa_query --split-by VC_FUNDCODE --hive-import --target-dir temp_table --hive-table fund_info --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '\001' --hive-drop-import-delims -m 4 --hive-overwrite --delete-target-dir
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/23 16:16:47 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/23 16:16:47 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/23 16:16:47 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
18/05/23 16:16:47 INFO manager.SqlManager: Using default fetchSize of 1000
18/05/23 16:16:47 INFO tool.CodeGenTool: Beginning code generation
18/05/23 16:17:16 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 16:17:16 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM FUND_INFO t WHERE 1=0
18/05/23 16:17:16 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-2.7.5
Note: /tmp/sqoop-root/compile/9398ba28d24e7b9db6b1260fa083e904/FUND_INFO.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
18/05/23 16:17:19 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/9398ba28d24e7b9db6b1260fa083e904/FUND_INFO.jar
18/05/23 16:17:20 INFO tool.ImportTool: Destination directory temp_table is not present, hence not deleting.
18/05/23 16:17:20 INFO mapreduce.ImportJobBase: Beginning import of FUND_INFO
18/05/23 16:17:20 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
18/05/23 16:17:20 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 16:17:20 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
18/05/23 16:17:20 INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2
18/05/23 16:17:22 INFO db.DBInputFormat: Using read commited transaction isolation
18/05/23 16:17:22 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(VC_FUNDCODE), MAX(VC_FUNDCODE) FROM FUND_INFO
18/05/23 16:17:22 INFO mapreduce.JobSubmitter: Cleaning up the staging area /tmp/hadoop-yarn/staging/root/.staging/job_1526097883376_0004
18/05/23 16:17:22 ERROR tool.ImportTool: Import failed: java.io.IOException: Generating splits for a textual index column allowed only in case of "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" property passed as a parameter
    at org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:204)
    at org.apache.hadoop.mapreduce.JobSubmitter.writeNewSplits(JobSubmitter.java:301)
    at org.apache.hadoop.mapreduce.JobSubmitter.writeSplits(JobSubmitter.java:318)
    at org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:196)
    at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1290)
    at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1287)
    at java.security.AccessController.doPrivileged(Native Method)
    at javax.security.auth.Subject.doAs(Subject.java:422)
    at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1754)
    at org.apache.hadoop.mapreduce.Job.submit(Job.java:1287)
    at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:1308)
    at org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:200)
    at org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:173)
    at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:270)
    at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
    at org.apache.sqoop.manager.OracleManager.importTable(OracleManager.java:454)
    at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:520)
    at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: Generating splits for a textual index column allowed only in case of "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" property passed as a parameter
    at org.apache.sqoop.mapreduce.db.TextSplitter.split(TextSplitter.java:67)
    at org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:201)
    ... 23 more

[root@node1 sqoop-1.4.7]#

因为指定了-m 4，也就是4个map处理，-m > 1 必须有主键。因为这个表没有主键，所以需要需要设置-m 1

2、修改参数，再次执行

[root@node1 sqoop-1.4.7]# bin/sqoop import --connect jdbc:oracle:thin:@192.168.1.31:1521:users --table FUND_INFO --username tpa_query --password tpa_query --hive-import --target-dir temp_table --hive-table fund_info --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '\001' --hive-drop-import-delims -m 1 --hive-overwrite --delete-target-dir
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/23 22:21:42 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/23 22:21:42 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/23 22:21:42 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
18/05/23 22:21:42 INFO manager.SqlManager: Using default fetchSize of 1000
18/05/23 22:21:42 INFO tool.CodeGenTool: Beginning code generation
18/05/23 22:21:55 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:21:55 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM FUND_INFO t WHERE 1=0
18/05/23 22:21:55 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-2.7.5
Note: /tmp/sqoop-root/compile/5c5a70aeddefb84000a6c46dab882820/FUND_INFO.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
18/05/23 22:21:57 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/5c5a70aeddefb84000a6c46dab882820/FUND_INFO.jar
18/05/23 22:21:58 INFO tool.ImportTool: Destination directory temp_table is not present, hence not deleting.
18/05/23 22:21:58 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:21:59 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/23 22:21:59 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:22:00 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/23 22:22:00 INFO mapreduce.ImportJobBase: Beginning import of FUND_INFO
18/05/23 22:22:00 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
18/05/23 22:22:00 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:22:00 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
18/05/23 22:22:00 INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2
18/05/23 22:22:03 INFO db.DBInputFormat: Using read commited transaction isolation
18/05/23 22:22:03 INFO mapreduce.JobSubmitter: number of splits:1
18/05/23 22:22:03 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1526097883376_0005
18/05/23 22:22:03 INFO impl.YarnClientImpl: Submitted application application_1526097883376_0005
18/05/23 22:22:04 INFO mapreduce.Job: The url to track the job: http://ndoe1:8088/proxy/application_1526097883376_0005/
18/05/23 22:22:04 INFO mapreduce.Job: Running job: job_1526097883376_0005
18/05/23 22:22:12 INFO mapreduce.Job: Job job_1526097883376_0005 running in uber mode : false
18/05/23 22:22:12 INFO mapreduce.Job:  map 0% reduce 0%
18/05/23 22:22:31 INFO mapreduce.Job:  map 100% reduce 0%
18/05/23 22:22:31 INFO mapreduce.Job: Job job_1526097883376_0005 completed successfully
18/05/23 22:22:31 INFO mapreduce.Job: Counters: 30
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=142841
        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=483891
        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)=16641
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=16641
        Total vcore-milliseconds taken by all map tasks=16641
        Total megabyte-milliseconds taken by all map tasks=17040384
    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)=68
        CPU time spent (ms)=2710
        Physical memory (bytes) snapshot=232636416
        Virtual memory (bytes) snapshot=2141016064
        Total committed heap usage (bytes)=109051904
    File Input Format Counters 
        Bytes Read=0
    File Output Format Counters 
        Bytes Written=483891
18/05/23 22:22:31 INFO mapreduce.ImportJobBase: Transferred 472.5498 KB in 30.9626 seconds (15.2619 KB/sec)
18/05/23 22:22:31 INFO mapreduce.ImportJobBase: Retrieved 1123 records.
18/05/23 22:22:31 INFO mapreduce.ImportJobBase: Publishing Hive/Hcat import job data to Listeners for table FUND_INFO
18/05/23 22:22:31 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:22:31 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM FUND_INFO t WHERE 1=0
18/05/23 22:22:31 WARN hive.TableDefWriter: Column CREATE_DATE had to be cast to a less precise type in Hive
18/05/23 22:22:31 WARN hive.TableDefWriter: Column EDATE had to be cast to a less precise type in Hive
18/05/23 22:22:31 WARN hive.TableDefWriter: Column BEGIN_DATE had to be cast to a less precise type in Hive
18/05/23 22:22:31 WARN hive.TableDefWriter: Column END_DATE had to be cast to a less precise type in Hive
18/05/23 22:22:31 WARN hive.TableDefWriter: Column SET_AMOUNT had to be cast to a less precise type in Hive
18/05/23 22:22:31 WARN hive.TableDefWriter: Column GLF_RATIO had to be cast to a less precise type in Hive
18/05/23 22:22:31 WARN hive.TableDefWriter: Column CLEAR had to be cast to a less precise type in Hive
18/05/23 22:22:31 INFO hive.HiveImport: Loading uploaded data into Hive
18/05/23 22:22:31 ERROR hive.HiveConfig: Could not load org.apache.hadoop.hive.conf.HiveConf. Make sure HIVE_CONF_DIR is set correctly.
18/05/23 22:22:31 ERROR tool.ImportTool: Import failed: java.io.IOException: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf
    at org.apache.sqoop.hive.HiveConfig.getHiveConf(HiveConfig.java:50)
    at org.apache.sqoop.hive.HiveImport.getHiveArgs(HiveImport.java:392)
    at org.apache.sqoop.hive.HiveImport.executeExternalHiveScript(HiveImport.java:379)
    at org.apache.sqoop.hive.HiveImport.executeScript(HiveImport.java:337)
    at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:241)
    at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:537)
    at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:338)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at org.apache.sqoop.hive.HiveConfig.getHiveConf(HiveConfig.java:44)
    ... 12 more

[root@node1 sqoop-1.4.7]#

这个问题很蛋疼，百度了一下，大多是说配置环境变量之类，但是依然没有解决问题。

3、缺少Hive的Jar包

[root@node1 ~]# cd /opt/sqoop-1.4.7/lib/
[root@node1 lib]# ln -s /opt/hive-1.2.2/lib/hive-exec-1.2.2.jar hive-exec-1.2.2.jar

成功执行

[root@node1 sqoop-1.4.7]# bin/sqoop import --connect jdbc:oracle:thin:@192.168.1.31:1521:users --table FUND_INFO --username tpa_query --password tpa_query --hive-import --target-dir temp_table --hive-table fund_info --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '\001' --hive-drop-import-delims -m 1 --hive-overwrite --delete-target-dir
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/23 22:43:25 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/23 22:43:25 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/23 22:43:25 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
18/05/23 22:43:25 INFO manager.SqlManager: Using default fetchSize of 1000
18/05/23 22:43:25 INFO tool.CodeGenTool: Beginning code generation
18/05/23 22:43:39 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:43:39 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM FUND_INFO t WHERE 1=0
18/05/23 22:43:39 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-2.7.5
Note: /tmp/sqoop-root/compile/794664c2fe6c8abd0aa7d8b24b3f0b62/FUND_INFO.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
18/05/23 22:43:42 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/794664c2fe6c8abd0aa7d8b24b3f0b62/FUND_INFO.jar
18/05/23 22:43:43 INFO tool.ImportTool: Destination directory temp_table deleted.
18/05/23 22:43:43 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:43: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/23 22:43:43 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:43: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/23 22:43:43 INFO mapreduce.ImportJobBase: Beginning import of FUND_INFO
18/05/23 22:43:43 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
18/05/23 22:43:43 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:43:43 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
18/05/23 22:43:43 INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2
18/05/23 22:43:45 INFO db.DBInputFormat: Using read commited transaction isolation
18/05/23 22:43:45 INFO mapreduce.JobSubmitter: number of splits:1
18/05/23 22:43:46 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1526097883376_0009
18/05/23 22:43:46 INFO impl.YarnClientImpl: Submitted application application_1526097883376_0009
18/05/23 22:43:46 INFO mapreduce.Job: The url to track the job: http://node1:8088/proxy/application_1526097883376_0009/
18/05/23 22:43:46 INFO mapreduce.Job: Running job: job_1526097883376_0009
18/05/23 22:43:53 INFO mapreduce.Job: Job job_1526097883376_0009 running in uber mode : false
18/05/23 22:43:53 INFO mapreduce.Job:  map 0% reduce 0%
18/05/23 22:44:03 INFO mapreduce.Job:  map 100% reduce 0%
18/05/23 22:44:04 INFO mapreduce.Job: Job job_1526097883376_0009 completed successfully
18/05/23 22:44:04 INFO mapreduce.Job: Counters: 30
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=143252
        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=483891
        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)=8250
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=8250
        Total vcore-milliseconds taken by all map tasks=8250
        Total megabyte-milliseconds taken by all map tasks=8448000
    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)=131
        CPU time spent (ms)=2980
        Physical memory (bytes) snapshot=308367360
        Virtual memory (bytes) snapshot=2146000896
        Total committed heap usage (bytes)=154140672
    File Input Format Counters 
        Bytes Read=0
    File Output Format Counters 
        Bytes Written=483891
18/05/23 22:44:04 INFO mapreduce.ImportJobBase: Transferred 472.5498 KB in 21.5561 seconds (21.9219 KB/sec)
18/05/23 22:44:04 INFO mapreduce.ImportJobBase: Retrieved 1123 records.
18/05/23 22:44:04 INFO mapreduce.ImportJobBase: Publishing Hive/Hcat import job data to Listeners for table FUND_INFO
18/05/23 22:44:04 INFO manager.OracleManager: Time zone has been set to GMT
18/05/23 22:44:04 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM FUND_INFO t WHERE 1=0
18/05/23 22:44:04 WARN hive.TableDefWriter: Column CREATE_DATE had to be cast to a less precise type in Hive
18/05/23 22:44:04 WARN hive.TableDefWriter: Column EDATE had to be cast to a less precise type in Hive
18/05/23 22:44:04 WARN hive.TableDefWriter: Column BEGIN_DATE had to be cast to a less precise type in Hive
18/05/23 22:44:04 WARN hive.TableDefWriter: Column END_DATE had to be cast to a less precise type in Hive
18/05/23 22:44:04 WARN hive.TableDefWriter: Column SET_AMOUNT had to be cast to a less precise type in Hive
18/05/23 22:44:04 WARN hive.TableDefWriter: Column GLF_RATIO had to be cast to a less precise type in Hive
18/05/23 22:44:04 WARN hive.TableDefWriter: Column CLEAR had to be cast to a less precise type in Hive
18/05/23 22:44:04 INFO hive.HiveImport: Loading uploaded data into Hive
18/05/23 22:44:07 INFO hive.HiveImport: 
18/05/23 22:44:07 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/opt/hive-1.2.2/lib/hive-exec-1.2.2.jar!/hive-log4j.properties
18/05/23 22:44:11 INFO hive.HiveImport: OK
18/05/23 22:44:11 INFO hive.HiveImport: Time taken: 3.071 seconds
18/05/23 22:44:12 INFO hive.HiveImport: Loading data to table default.fund_info
18/05/23 22:44:12 INFO hive.HiveImport: Table default.fund_info stats: [numFiles=1, numRows=0, totalSize=483891, rawDataSize=0]
18/05/23 22:44:12 INFO hive.HiveImport: OK
18/05/23 22:44:12 INFO hive.HiveImport: Time taken: 0.913 seconds
18/05/23 22:44:13 INFO hive.HiveImport: Hive import complete.
18/05/23 22:44:13 INFO hive.HiveImport: Export directory is contains the _SUCCESS file only, removing the directory.
[root@node1 sqoop-1.4.7]#

4、查看Hive结果

[root@node1 opt]# hive

Logging initialized using configuration in file:/opt/hive-1.2.2/conf/hive-log4j.properties
hive> show tables;
OK
fund_info
t1
Time taken: 1.411 seconds, Fetched: 2 row(s)
hive> select count(*) from fund_info;
Query ID = root_20180523224519_4aa04e7c-abea-465e-a0a5-9a1fe0fe4d3d
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1526097883376_0010, Tracking URL = http://bigdata03-test:8088/proxy/application_1526097883376_0010/
Kill Command = /opt/hadoop-2.7.5/bin/hadoop job  -kill job_1526097883376_0010
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2018-05-23 22:45:29,355 Stage-1 map = 0%,  reduce = 0%
2018-05-23 22:45:34,654 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.02 sec
2018-05-23 22:45:41,967 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 4.89 sec
MapReduce Total cumulative CPU time: 4 seconds 890 msec
Ended Job = job_1526097883376_0010
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 4.89 sec   HDFS Read: 499445 HDFS Write: 5 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 890 msec
OK
1123
Time taken: 23.424 seconds, Fetched: 1 row(s)
hive>

5、测试导入一张大表

[root@bigdata01-test sqoop-1.4.7]# bin/sqoop import --connect jdbc:oracle:thin:@10.17.12.31:1521:TPADC --table DETAIL --username tpa_query --password tpa_query --hive-import --target-dir temp_table --hive-table fact_invest_detail --null-string '\\N' --null-non-string '\\N' --fields-terminated-by '\001' --hive-drop-import-delims -m 1 --hive-overwrite --delete-target-dir
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/24 11:12:11 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/05/24 11:12:12 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/05/24 11:12:12 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
18/05/24 11:12:12 INFO manager.SqlManager: Using default fetchSize of 1000
18/05/24 11:12:12 INFO tool.CodeGenTool: Beginning code generation
18/05/24 11:12:45 INFO manager.OracleManager: Time zone has been set to GMT
18/05/24 11:12:46 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM DETAIL t WHERE 1=0
18/05/24 11:12:46 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/hadoop-2.7.5
Note: /tmp/sqoop-root/compile/b7308f9eec89b55d6b8e63ccd9ffbcca/TPA_DW_FACT_INVEST_DETAIL.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
18/05/24 11:12:49 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/b7308f9eec89b55d6b8e63ccd9ffbcca/DETAIL.jar
18/05/24 11:12:50 INFO tool.ImportTool: Destination directory temp_table is not present, hence not deleting.
18/05/24 11:12:50 INFO manager.OracleManager: Time zone has been set to GMT
18/05/24 11:12:51 INFO manager.OracleManager: Time zone has been set to GMT
18/05/24 11:12:52 INFO mapreduce.ImportJobBase: Beginning import of DETAIL
18/05/24 11:12:52 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
18/05/24 11:12:52 INFO manager.OracleManager: Time zone has been set to GMT
18/05/24 11:12:52 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
18/05/24 11:12:52 INFO client.ConfiguredRMFailoverProxyProvider: Failing over to rm2
18/05/24 11:12:54 INFO db.DBInputFormat: Using read commited transaction isolation
18/05/24 11:12:54 INFO mapreduce.JobSubmitter: number of splits:1
18/05/24 11:12:55 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1526097883376_0011
18/05/24 11:12:55 INFO impl.YarnClientImpl: Submitted application application_1526097883376_0011
18/05/24 11:12:55 INFO mapreduce.Job: The url to track the job: http://bigdata03-test:8088/proxy/application_1526097883376_0011/
18/05/24 11:12:55 INFO mapreduce.Job: Running job: job_1526097883376_0011
18/05/24 11:13:02 INFO mapreduce.Job: Job job_1526097883376_0011 running in uber mode : false
18/05/24 11:13:02 INFO mapreduce.Job:  map 0% reduce 0%
18/05/24 11:18:34 INFO mapreduce.Job:  map 100% reduce 0%
18/05/24 11:18:35 INFO mapreduce.Job: Job job_1526097883376_0011 completed successfully
18/05/24 11:18:35 INFO mapreduce.Job: Counters: 30
    File System Counters
        FILE: Number of bytes read=0
        FILE: Number of bytes written=144210
        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=2981482292
        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)=328641
        Total time spent by all reduces in occupied slots (ms)=0
        Total time spent by all map tasks (ms)=328641
        Total vcore-milliseconds taken by all map tasks=328641
        Total megabyte-milliseconds taken by all map tasks=336528384
    Map-Reduce Framework
        Map input records=6351441
        Map output records=6351441
        Input split bytes=87
        Spilled Records=0
        Failed Shuffles=0
        Merged Map outputs=0
        GC time elapsed (ms)=3074
        CPU time spent (ms)=320810
        Physical memory (bytes) snapshot=246095872
        Virtual memory (bytes) snapshot=2141949952
        Total committed heap usage (bytes)=114294784
    File Input Format Counters 
        Bytes Read=0
    File Output Format Counters 
        Bytes Written=2981482292
18/05/24 11:18:35 INFO mapreduce.ImportJobBase: Transferred 2.7767 GB in 342.9783 seconds (8.2902 MB/sec)
18/05/24 11:18:35 INFO mapreduce.ImportJobBase: Retrieved 6351441 records.
18/05/24 11:18:35 INFO mapreduce.ImportJobBase: Publishing Hive/Hcat import job data to Listeners for table DETAIL
18/05/24 11:18:35 INFO manager.OracleManager: Time zone has been set to GMT
18/05/24 11:18:35 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM DETAIL t WHERE 1=0
18/05/24 11:18:35 WARN hive.TableDefWriter: Column BMONTH_VALPRICE had to be cast to a less precise type in Hive
18/05/24 11:18:35 WARN hive.TableDefWriter: Column BQUART_VALPRICE had to be cast to a less precise type in Hive
18/05/24 11:18:35 WARN hive.TableDefWriter: Column BYEAR_VALPRICE had to be cast to a less precise type in Hive
18/05/24 11:18:35 WARN hive.TableDefWriter: Column CAPITUP_BY had to be cast to a less precise type in Hive
18/05/24 11:18:35 WARN hive.TableDefWriter: Column CAPITUP_DIVIDENTS had to be cast to a less precise type in Hive
18/05/24 11:18:35 WARN hive.TableDefWriter: Column CAPITUP_SUM had to be cast to a less precise type in Hive
18/05/24 11:18:35 WARN hive.TableDefWriter: Column CAPITUP_DIVIDENTS had to be cast to a less precise type in Hive
18/05/24 11:18:35 WARN hive.TableDefWriter: Column AMOUNT_GZ had to be cast to a less precise type in Hive
18/05/24 11:18:35 WARN hive.TableDefWriter: Column FAIRCHANGE had to be cast to a less precise type in Hive
18/05/24 11:18:35 INFO hive.HiveImport: Loading uploaded data into Hive
18/05/24 11:18:45 INFO hive.HiveImport: 
18/05/24 11:18:45 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/opt/hive-1.2.2/lib/hive-exec-1.2.2.jar!/hive-log4j.properties
18/05/24 11:18:49 INFO hive.HiveImport: OK
18/05/24 11:18:49 INFO hive.HiveImport: Time taken: 1.94 seconds
18/05/24 11:18:49 INFO hive.HiveImport: Loading data to table default.fact_invest_detail
18/05/24 11:18:50 INFO hive.HiveImport: Table default.fact_invest_detail stats: [numFiles=1, numRows=0, totalSize=2981482292, rawDataSize=0]
18/05/24 11:18:50 INFO hive.HiveImport: OK
18/05/24 11:18:50 INFO hive.HiveImport: Time taken: 0.903 seconds
18/05/24 11:18:50 INFO hive.HiveImport: Hive import complete.
18/05/24 11:18:50 INFO hive.HiveImport: Export directory is contains the _SUCCESS file only, removing the directory.
[root@bigdata01-test sqoop-1.4.7]#

花费六七分钟

hive> show tables;
OK
detail
fund_info
t1
Time taken: 1.406 seconds, Fetched: 3 row(s)
hive> select count(*) from detail;
Query ID = root_20180524112136_44d65b6a-14d4-4f36-aa69-46081ecdc64a
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1526097883376_0012, Tracking URL = http://ndoe1:8088/proxy/application_1526097883376_0012/
Kill Command = /opt/hadoop-2.7.5/bin/hadoop job  -kill job_1526097883376_0012
Hadoop job information for Stage-1: number of mappers: 12; number of reducers: 1
2018-05-24 11:21:50,959 Stage-1 map = 0%,  reduce = 0%
2018-05-24 11:21:58,895 Stage-1 map = 17%,  reduce = 0%, Cumulative CPU 4.24 sec
2018-05-24 11:21:59,959 Stage-1 map = 33%,  reduce = 0%, Cumulative CPU 16.26 sec
2018-05-24 11:22:02,132 Stage-1 map = 42%,  reduce = 0%, Cumulative CPU 19.99 sec
2018-05-24 11:22:03,178 Stage-1 map = 53%,  reduce = 0%, Cumulative CPU 28.85 sec
2018-05-24 11:22:04,218 Stage-1 map = 67%,  reduce = 0%, Cumulative CPU 44.23 sec
2018-05-24 11:22:05,257 Stage-1 map = 78%,  reduce = 0%, Cumulative CPU 51.24 sec
2018-05-24 11:22:06,298 Stage-1 map = 89%,  reduce = 0%, Cumulative CPU 52.63 sec
2018-05-24 11:22:07,336 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 53.68 sec
2018-05-24 11:22:09,415 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 56.66 sec
MapReduce Total cumulative CPU time: 56 seconds 660 msec
Ended Job = job_1526097883376_0012
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 12  Reduce: 1   Cumulative CPU: 56.66 sec   HDFS Read: 2983164717 HDFS Write: 8 SUCCESS
Total MapReduce CPU Time Spent: 56 seconds 660 msec
OK
6351441
Time taken: 34.987 seconds, Fetched: 1 row(s)
hive>
程裕强
关注
专栏目录
oracle导入到hive中,sqoop操作之ORACLE导入到HIVE
weixin_39587010的博客
04-12 1228
导入表的所有字段sqoop import --connect jdbc:oracle:thin:@192.168.1.107:1521:ORCL \--username SCOTT --password tiger \--table EMP \--hive-import --create-hive-table --hive-table emp -m 1;如果报类似的错:ERROR tool.I...
Sqoop从Oracle导入表到Hive
我的博客
04-06 1万+
最近甲方项目希望建立大数据平台，需要将保存在Oracle中的表和记录全部导入到大数据平台中。原计划是将表导入到HBase中，作为Hive外部表，但甲方对如何使用还没有明确想法，HBase行键不好设计，但甲方又急于将数据导入试用。于是决定先将数据导入到Hive中，方便他们做统计分析试验。调研一番后，决定采用Sqoop。现将使用过程总结如下。
hive "Failed with exception"
Tak-Wah Blog
03-13 1349
hive 追加数据 "insert into" 报下列错误： Failed with exception copyFiles: error while checking/creating destination directory!!! hive 追加数据 "insert overwrite" 报下列错误： Failed with exception Unable to move sou...
[999]sqoop导入数据‘‘--query搭配$CONDITIONS‘‘的理解
周小董
05-29 1035
文章目录运行测试原理理解 引言 sqoop在导入数据时，可以使用--query搭配sql来指定查询条件，并且还需在sql中添加$CONDITIONS，来实现并行运行mr的功能。 运行测试 测试均基于sqoop1，mysql数据准备如下。 （1）只要有--query+sql，就需要加$CONDITIONS，哪怕只有一个maptask。 # 只有一个maptask[hadoop@node01 /kkb/bin]$ sqoop import --connect jdbc:mysql://node01:3306/
hive使用sqoop与oracle传输数据
最新发布
cyhysr的博客
04-15 1290
下载地址两个版本sqoop1（1.4.x）和sqoop2（1.99.x），两种不同的架构。本文使用sqoop1。sqoop是apache旗下一款“hadoop与关系数据库之间传送数据”的工具。导入数据：从MySQL、Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等存储系统。导出数据：从Hadoop的HDFS、HIVE中导出数据到关系数据库mysql等。sqoop的工作机制是将导入导出命令翻译成mapreduce程序来实现。
Sqoop将MySQL数据导入到hive中
cny0120的博客
01-30 188
错误解决：http://blog.itpub.net/25854343/viewspace-2565234/ MySQL表： mysql> desc t3; ...
sqoop从oracle到hive,sqoop从oracle数据库抽取数据,导入到hive
weixin_32503531的博客
04-10 1306
环境:hadoop-2.7.5sqoop-1.4.7zookeeper-3.4.10hive-2.3.3 (使用mysql配置元数据库)jdk1.8.0_151oracle 11.2.0.3.0经过一番baidu,总算初步成功,现在记录一下中间过程.1.拷贝hive/conf/hive_site.xml到sqoop/conf目录2.配置sqoop-evn.sh,将变量设置为对应的目录.export...
Sqoop导Oracle数据到Hive
12-21
### Sqoop导入Oracle数据到Hive的详细步骤与解析 #### 一、概述 在大数据处理领域中，经常需要将关系型数据库中的数据导入到Hadoop的HDFS中，以便进一步进行数据分析或处理。其中，Sqoop作为一种强大的工具，能够...
sqoop导入oracle数据至hive
彩虹柠檬水
11-05 3722
一、目的 将oracle数据文件dmp中的数据导入到hive中。 目标分解： 由于已有的数据都在dmp文件中，因此要先将dmp文件中的数据导入到oracle中，再将oracle中的表导入到hive中。 二、dmp文件导入到oracle数据库 dmp文件：只有一个不知来源的dmp文件，不知道从哪个数据库中导出的，也不知道导出用户是谁。（不要紧，后面可以根据错误提示一步步改） oracle
安装部署（八） Hive+Sqoop安装部署和使用
星门学派（Star School）
08-12 4364
Hive+Sqoop安装 haddoop 2.7.2 spark 2.0.0 zookeeper 3.4.8 kafka 0.10.0.0 hbase 1.2.2 jdk1.8.0_101 ubuntu 14.04.04 x64 参考： http://blog.csdn.net/yinedent/article/details/48275407 http://blo
Sqoop异常记录
shengpli′s blog
04-27 829
基于Apache-Sqoop-1.4.6 一、Mysql to Hive 1.2017-10-30 20:19:44,317 [myid:] - WARN [main:TableDefWriter@205] - Column register_date had to be cast to a less precise type in Hive 2017-10-30 20:19:44,317...
【大数据开发运维解决方案】Sqoop全量同步mysql/Oracle数据到hive
热门推荐
赵延东的一亩三分地
03-19 17万+
接下来本篇文章详细介绍一下使用sqoop全量同步oracle/mysql数据到hive，这里实验采用oracle数据库为例，后面一篇文章将详细介绍：1、sqoop --incremental append 附加模式增量同步数据到hive2、sqoop --incremental --merge-key合并模式增量同步到hive。
用sqoop将Oracle数据全量/增量导入到HIve的Shell/python脚本(第二版)
qq_40870024的博客
02-15 2387
全量导入 #!/usr/bin/env bash # /bin/bash biz_date=20210101 biz_fmt_date=2021-01-01 dw_parent_dir=/data/dw/ods/one_make/full_imp workhome=/opt/sqoop/one_make full_imp_tables=${workhome}/full_import_tables.txt mkdir ${workhome}/log orcl_srv=oracle.bigdata.cn or
利用Sqoop把Oracle数据迁移到HDFS、Hive
u010185220的专栏
01-17 2万+
提示：以下操作是在Xshell终端进行的，导入数据后若在Xshell终端上查看导入的数据，数据中的中文会出现乱码。要在Linux系统（最好是在可视化的Linux系统端）中去查看数据不会出现乱码。本人刚开始一直都是在Xshell上进行数据的导入、查看测试，花了一天时间解决“乱码”情况都没解决，最后才知是Xshell终端的问题，这里提示一下。一、利用Sqoop，从Oracle到HDFS第一步：把Ora
使用pyspark模仿sqoop从oracle导数据到hive的主要功能（自动建表，分区导入，增量，解决数据换行符问题）...
weixin_30945039的博客
07-31 517
　　最近公司开始做大数据项目，让我使用sqoop（1.6.4版本）导数据进行数据分析计算，然而当我们将所有的工作流都放到azkaban上时整个流程跑完需要花费13分钟，而其中导数据（增量）就占了4分钟左右，老板给我提供了使用 spark 导数据的思路，学习整理了一个多星期，终于实现了sqoop的主要功能。 　　这里我使用的是pyspark完成的所有操作。 　　 　　条件：hdfs平台，pys...
oracle sqoop 导出_sqoop导入导出hive/oracle
weixin_39976081的博客
01-13 311
版本：sqoop1最新版用户指导：http://archive.cloudera.com/cdh5/cdh/5/sqoop-1.4.6-cdh5.9.3/SqoopUserGuide.html1.sqoop从关系型数据库oracle导数据到hive，数据存储为parquet格式。首先，在hive中建表，按月增量存储的建分区表。存储为parquet文件格式而不是默认的textfile是为了便于在im...
sqoop导入oracle数据到hive
03-16
sqoop是一个用于在Hadoop和关系型数据库之间传输数据的工具。以下是将Oracle数据导入到Hive的步骤： 1. 确保你已经安装了sqoop和Hive。 2. 在Oracle数据库中创建一个表或视图。 3. 在Hive中创建一个表，该表的结构应该与Oracle表或视图相同。 4. 使用以下命令将数据从Oracle导入到Hive： sqoop import --connect jdbc:oracle:thin:@//hostname:port/service_name --username username --password password --table oracle_table --hive-import --hive-table hive_table 其中，hostname是Oracle数据库的主机名，port是Oracle数据库的端口号，service_name是Oracle数据库的服务名，username是Oracle数据库的用户名，password是Oracle数据库的密码，oracle_table是要导入的Oracle表的名称，hive_table是要导入到Hive中的表的名称。 5. 执行命令后，sqoop将从Oracle中提取数据，并将其加载到Hive表中。 希望这可以帮助你将Oracle数据导入到Hive中。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值