The Hortonworks Community Connection is now live. A completely rebuilt Q&A forum, Knowledge Base, Code Hub and more, backed by the experts in the industry.

You will be redirected here in 10 seconds. If your are not redirected, click here to visit the new site.

The legacy Hortonworks Forum is now closed. You can view a read-only version of the former site by clicking here. The site will be taken offline on January 31,2016

Sqoop Forum

Unable to sqoop Oracle data into hive

  • #13743
    Trang Nguyen
    Member

    Hi,

    My first attempt to sqoop data from Oracle into hive failed with an error:
    sqoop import –connect jdbc:oracle:thin:@xdc-tst-ora-003.openmarket.com:1521/devdwh1.openmarket.com –username trangn_dw –password trangn_dw –table WMP_DW.SMS_MESSAGE –where “SUBMITTED_DATE >= ’01-NOV-2012′ and SUBMITTED_DATE = ’01-NOV-2012′ and REPORTING_UPDATED_DATE < '02-NOV-2012' and rownum<10" –hive-import –table sms_message_test –split-by wmp_record_id –outdir /user/app-sms/sms_message_test
    13/01/17 14:27:47 ERROR orm.CompilationManager: Could not make directory: /user/app-sms/sms_message_test

    Any help is appreciated.
    Thanks,Trang

  • Author
    Replies
  • #13747
    Trang Nguyen
    Member

    Sorry – my first message got clobbered.

    sqoop import –connect jdbc:oracle:thin:@xdc-tst-ora-003.openmarket.com:1521/devdwh1.openmarket.com –username trangn_dw –password trangn_dw –table WMP_DW.SMS_MESSAGE –where “SUBMITTED_DATE >= ’01-NOV-2012′ and SUBMITTED_DATE < '02-NOV-2012'" –hive-import –table sms_message-20121101 –split-by CUSTOMER_ACCOUNT_ID

    13/01/17 05:12:39 INFO mapred.JobClient: SPLIT_RAW_BYTES=931
    13/01/17 05:12:39 INFO mapreduce.ImportJobBase: Transferred 11.7305 GB in 3,320.4143 seconds (3.6176 MB/sec)
    13/01/17 05:12:39 INFO mapreduce.ImportJobBase: Retrieved 18023532 records.
    13/01/17 05:12:39 INFO manager.OracleManager: Time zone has been set to GMT
    13/01/17 05:12:39 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM WMP_DW.SMS_MESSAGE t WHERE 1=0
    13/01/17 05:12:39 WARN hive.TableDefWriter: Column SUBMITTED_DATE had to be cast to a less precise type in Hive

    13/01/17 05:12:44 INFO hive.HiveImport: FAILED: Error in metadata: InvalidObjectException(message:There is no database named wmp_dw)
    13/01/17 05:12:44 INFO hive.HiveImport: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
    13/01/17 05:12:44 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Hive exited with status 9
    at org.apache.sqoop.hive.HiveImport.executeExternalHiveScript(HiveImport.java:364)

    Data got into HDFS but failed during the import to hive.

    Any help is appreciated.
    Thanks,
    Trang

    #13748
    Trang Nguyen
    Member

    With the second attempt, I tried to specify an output directory for sqoop but got a different error:
    qoop import –connect jdbc:oracle:thin:@xdc-tst-ora-003.openmarket.com:1521/devdwh1.openmarket.com –username trangn_dw –password trangn_dw –table WMP_DW.SMS_MESSAGE –where “REPORTING_UPDATED_DATE >= ’01-NOV-2012′ and REPORTING_UPDATED_DATE < '02-NOV-2012' and rownum<10" –hive-import –table sms_message_test –split-by wmp_record_id –outdir /user/app-sms/sms_message_test


    13/01/17 14:27:47 ERROR orm.CompilationManager: Could not make directory: /user/app-sms/sms_message_test
    13/01/17 14:27:47 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-app-sms/compile/af94adc2e3658d88189cf01fcae6d0f7/WMP_DW.SMS_MESSAGE.jar
    13/01/17 14:27:47 INFO mapreduce.ImportJobBase: Beginning import of WMP_DW.SMS_MESSAGE

    I can create the /user/app-sms/sms_message_test wihout issues as user "app-sms" so am not sure why sqoop would run into issues:

    hadoop fs -mkdir /user/app-sms/sms_message_test
    hadoop fs -rmr /user/app-sms/sms_message_test

    #13790
    tedr
    Member

    Hi Trang,

    Thanks for using HDP.

    This does look like a permissions issue, are you able to create the directory as the user trangn_dw?

    Thanks,
    Ted.

The topic ‘Unable to sqoop Oracle data into hive’ is closed to new replies.

Support from the Experts

A HDP Support Subscription connects you experts with deep experience running Apache Hadoop in production, at-scale on the most demanding workloads.

Enterprise Support »

Become HDP Certified

Real world training designed by the core architects of Hadoop. Scenario-based training courses are available in-classroom or online from anywhere in the world

Training »

Hortonworks Data Platform
The Hortonworks Data Platform is a 100% open source distribution of Apache Hadoop that is truly enterprise grade having been built, tested and hardened with enterprise rigor.
Get started with Sandbox
Hortonworks Sandbox is a self-contained virtual machine with Apache Hadoop pre-configured alongside a set of hands-on, step-by-step Hadoop tutorials.
Modern Data Architecture
Tackle the challenges of big data. Hadoop integrates with existing EDW, RDBMS and MPP systems to deliver lower cost, higher capacity infrastructure.