Support Questions
Find answers, ask questions, and share your expertise
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Cloudera Community
- Support
- Support Questions
- sqoop import inconsistent datatype issue
Options
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Options
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
sqoop import inconsistent datatype issue
Labels:
Super Collaborator
Created 06-26-2018 02:03 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am getting inconsistent data type in sqoop import , below are my source and target tables and sqoop command
error
18/06/26 09:54:27 INFO mapreduce.Job: map 0% reduce 0%
18/06/26 09:54:33 INFO mapreduce.Job: Task Id : attempt_1526339051549_1088_m_000000_0, Status : FAILED
Error: java.io.IOException: SQLException in nextKeyValue
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
sqoop command
sqoop job -Dmapreduce.job.user.classpath.first=true --create incjob -- import --connect "jdbc:oracle:thin:@(description=(address=(protocol=tcp)(host=dotstodb070)(port=1527))(connect_data=(service_name=trhprod)))" --username TRANHOST --incremental append --check-column INSERT_TIME --table TRANHOST.UFM_VIEW -split-by UFM_VIEW.UFMID --target-dir /user/root/_sqoop --hbase-table 'Sami:UFM2' --column-family F1 --hbase-row-key "UFMID" --columns "UFMID,LANEUFMSEQNO,LANEID,PLAZAID,TXNTM,TIP_ID,TIPUFMSEQ,INSERT_TIME" --boundary-query "SELECT MIN(\"INSERT_TIME\"), MAX(\"INSERT_TIME\") FROM \"UFM_VIEW\" WHERE ( \"INSERT_TIME\" > TO_DATE('24-JAN-2017', 'DD-MON-YYYY') AND \"INSERT_TIME\" <= TO_DATE('25-JAN-2017', 'DD-MON-YYYY'))"
source table structure
SQL> desc ufm_view Name Null? Type ----------------------------------------- -------- ---------------------------- UFMID NOT NULL NUMBER(19) LANEUFMSEQNO NOT NULL NUMBER(10) LANEID NOT NULL NUMBER(2) PLAZAID NOT NULL VARCHAR2(6) TXNTM NOT NULL TIMESTAMP(6) TIP_ID NOT NULL VARCHAR2(255) TIPUFMSEQ NOT NULL NUMBER(10) CCTVETM TIMESTAMP(6) CCTVSTM TIMESTAMP(6) COLID VARCHAR2(4) LIST1 VARCHAR2(160) LIST2 VARCHAR2(160) LIST3 VARCHAR2(160) LIST4 VARCHAR2(160) LIST5 VARCHAR2(160) LIST6 VARCHAR2(160) LIST7 VARCHAR2(160) LIST8 VARCHAR2(160) LIST9 VARCHAR2(160) LIST10 VARCHAR2(160) LIST11 VARCHAR2(160) LIST12 VARCHAR2(160) LIST13 VARCHAR2(160) LIST14 VARCHAR2(160) LIST15 VARCHAR2(160) LIST16 VARCHAR2(160) LIST17 VARCHAR2(160) LIST18 VARCHAR2(160) LIST19 VARCHAR2(160) LIST20 VARCHAR2(160) LIST21 VARCHAR2(160) LANEMODE VARCHAR2(5) LANEPOS VARCHAR2(2) LANESTATE VARCHAR2(7) LANETYPE VARCHAR2(5) LEGACY NUMBER(1) POSLSTTM TIMESTAMP(6) RATETBLTM TIMESTAMP(6) TAGPLTTBLTM TIMESTAMP(6) TIPADJ NUMBER(1) TOURID VARCHAR2(4) TXNURL VARCHAR2(255) LEVOFSVC NUMBER(2) MAINTTYPE VARCHAR2(6) MAINTID VARCHAR2(4) CALCLASS NUMBER(2) CALCLASSRULE VARCHAR2(6) CALPVPLZID VARCHAR2(6) CALPVPLZRULE VARCHAR2(6) PLTACCTID NUMBER(8) PREPAIDPASS VARCHAR2(7) INDREV NUMBER(6,2) FULFAREREV NUMBER(6,2) DISFAREREV NUMBER(6,2) ETCREV NUMBER(6,2) ETCDISREV NUMBER(6,2) ACTREV NUMBER(6,2) PAYMETHOD VARCHAR2(8) TMDAYCSHMULT NUMBER(6,2) TMDAYETCMULT NUMBER(6,2) LVLSVCCSHMULT NUMBER(6,2) LVLSVCETCMULT NUMBER(6,2) DAYOFWKMULT NUMBER(6,2) HOLIDAYMULT NUMBER(6,2) TXNREC1LANEID NUMBER(2) TXNREC1LNTOK NUMBER(19) TXNREC2LANEID NUMBER(2) TXNREC2LNTOK NUMBER(19) TXNREC3LANEID NUMBER(2) TXNREC3LNTOK NUMBER(19) TXNREC4LANEID NUMBER(2) TXNREC4LNTOK NUMBER(19) TXNREC5LANEID NUMBER(2) TXNREC5LNTOK NUMBER(19) TXNREC6LANEID NUMBER(2) TXNREC6LNTOK NUMBER(19) APMCASHEQSTAT VARCHAR2(7) DISPENSER1ID VARCHAR2(7) DISPENSER1TYPE CHAR(3) DISPENSER1COUNT NUMBER(2) DISPENSER2ID VARCHAR2(7) DISPENSER2TYPE CHAR(3) DISPENSER2COUNT NUMBER(2) DISPENSER3ID VARCHAR2(7) DISPENSER3TYPE CHAR(3) DISPENSER3COUNT NUMBER(2) VAULT1ID VARCHAR2(7) VAULT1CURRENCYCTS CHAR(78) VAULT2ID VARCHAR2(7) VAULT2CURRENCYCTS CHAR(78) VAULT3ID VARCHAR2(7) VAULT3CURRENCYCTS CHAR(78) VAULT4ID VARCHAR2(7) VAULT4CURRENCYCTS CHAR(78) APMCREQSTAT VARCHAR2(7) APMCRAUTHCODE NUMBER(6) APMCRAUTHSTAT VARCHAR2(7) APMCRCCTXNNO CHAR(28) APMCRCARDTYPE VARCHAR2(5) APMCREXPDATE VARCHAR2(6) APMCRGATEWAY NUMBER(1) APMCRLASTFOUR VARCHAR2(4) APMCRNRCID VARCHAR2(15) APMCRNRCVALID NUMBER(1) AVIEQSTAT VARCHAR2(7) AGENCY NUMBER(2) INTERNAL NUMBER(19) LOWBAT NUMBER(1) PASBAK NUMBER(1) AVIPREVLANE NUMBER(2) AVIPREVPLZID VARCHAR2(6) AVIPREVTKID NUMBER(4) AVITXNID NUMBER(4) AVIPREVTM TIMESTAMP(6) REGION NUMBER(2) REVTYPE VARCHAR2(10) TPDRID VARCHAR2(8) TPDRSTAT VARCHAR2(11) CANOPYLTEQSTAT VARCHAR2(7) GATEEQSTAT VARCHAR2(7) ITLEQSTAT VARCHAR2(7) OCREQSTAT VARCHAR2(7) GCKEYCONF NUMBER(4) HPRDCONF NUMBER(4) HORPOS NUMBER(4) HORSIZE NUMBER(4) JURIS VARCHAR2(3) JURISCONF NUMBER(4) MATCHFPID NUMBER(10) OCRIMGURL VARCHAR2(255) PLATETYPE NUMBER(2) PLTAREACONF NUMBER(4) PLTMATCHCONF NUMBER(4) PLTRDCONF NUMBER(4) PLTREAD VARCHAR2(16) FEATMATCHCONF NUMBER(4) VEHMATCHCONF NUMBER(4) VERTPOS NUMBER(4) VERTSIZE NUMBER(4) POSTAVCEQSTAT VARCHAR2(7) POSTAVCLOOPCLASS NUMBER(2) POSTAVCLOOPSPEED NUMBER(3) POSTAVCREVAXLECT NUMBER(2) PREAVCEQSTAT VARCHAR2(7) PREAVCLOOPCLASS NUMBER(2) PREAVCLOOPSPEED NUMBER(3) PREAVCREVAXLECT NUMBER(2) PTDEQSTAT VARCHAR2(7) RECEIPTPRTEQSTAT VARCHAR2(7) VESEQSTAT VARCHAR2(7) IMG1RANK NUMBER(1) IMG1URL VARCHAR2(255) IMG1TYPE CHAR(1) IMG2RANK NUMBER(1) IMG2URL VARCHAR2(255) IMG2TYPE CHAR(1) IMG3RANK NUMBER(1) IMG3URL VARCHAR2(255) IMG3TYPE CHAR(1) IMG4RANK NUMBER(1) IMG4URL VARCHAR2(255) IMG4TYPE CHAR(1) IMG5RANK NUMBER(1) IMG5URL VARCHAR2(255) IMG5TYPE CHAR(1) IMG6RANK NUMBER(1) IMG6URL VARCHAR2(255) IMG6TYPE CHAR(1) MLTEQSTAT VARCHAR2(7) MLTCOLCMT VARCHAR2(50) MLTCOLVEHCLASS NUMBER(2) MLTCONVOY NUMBER(1) MLTINSUFFUNDS NUMBER(1) MLTNRCID VARCHAR2(15) MLTNRCVALID NUMBER(1) MLTPREVLANE NUMBER(2) MLTPREVPLZID VARCHAR2(6) MLTPREVTKID NUMBER(4) MLTPREVTKVOID NUMBER(1) MLTPREVTM TIMESTAMP(6) MLTRUNTHRU NUMBER(1) MLTCREQSTAT VARCHAR2(7) MLTCRAUTHCODE NUMBER(6) MLTCRAUTHSTAT VARCHAR2(7) MLTCRCCTXNNO CHAR(28) MLTCRCARDTYPE VARCHAR2(5) MLTCREXPDATE VARCHAR2(6) MLTCRGATEWAY NUMBER(1) MLTCRLASTFOUR VARCHAR2(4) MLTCRNRCID VARCHAR2(15) MLTCRNRCVALID NUMBER(1) ATIMEQSTAT VARCHAR2(7) TKID NUMBER(4) TKTAKEN NUMBER(1) TKRDREQSTAT VARCHAR2(7) TKRDRPREVCLASS NUMBER(2) TKRDRPREVPLZID VARCHAR2(6) TKRDRPREVTKID NUMBER(4) TKRDRPREVTM TIMESTAMP(6) TKRDRPREVLANE NUMBER(2) VDACEQSTAT VARCHAR2(7) VDACCLASS NUMBER(2) VDACHEIGHT NUMBER(3) VDACLEN NUMBER(3) VDACWIDTH NUMBER(2) INSERT_TIME NOT NULL TIMESTAMP(6) CMPSITMULT NUMBER(5,2) LANETMHZN TIMESTAMP(6) PREVTKID NUMBER(4) SQL>
target Hbase table
hbase(main):002:0> describe 'Sami:UFM2'
Table Sami:UFM2 is ENABLED
Sami:UFM2
COLUMN FAMILIES DESCRIPTION
{NAME => 'EXLIST', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NO
NE', TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}
{NAME => 'F1', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NONE',
TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}
2 row(s) in 6.9330 seconds
hbase(main):003:0>
2 REPLIES 2
Super Collaborator
Created 06-26-2018 02:21 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I narrowed the error to the boundry query , if I remove it the sqoop command works. .
Guru
Created 06-27-2018 06:09 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Sami Ahmad!
Looking at your sqoop command, it seems that your issue comes from the different types used in --split-by (number column) and --boundary-query (timestamp column). If you take a look at the documentation (at 7.2.2. Selecting the Data to Import).
https://sqoop.apache.org/docs/1.4.2/SqoopUserGuide.html
By default sqoop will use query select min(<split-by>), max(<split-by>) from <table name> to find out boundaries for creating splits. In some cases this query is not the most optimal so you can specify any arbitrary query returning two numeric columns using --boundary-query argument.Guess if you change your split-by column to the same column as boundary-query it should work (or vice-versa) 🙂
Hope this helps!
Announcements
What's New @ Cloudera
What's New @ Cloudera
What's New @ Cloudera
What's New @ Cloudera
What's New @ Cloudera
