Home Forums Sqoop Unable to sqoop Oracle data into hive

Tagged: ,

This topic contains 3 replies, has 2 voices, and was last updated by  tedr 1 year, 10 months ago.

  • Creator
    Topic
  • #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

Viewing 3 replies - 1 through 3 (of 3 total)

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

  • Author
    Replies
  • #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.

    Collapse
    #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

    Collapse
    #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

    Collapse
Viewing 3 replies - 1 through 3 (of 3 total)