Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-478

Sqoop export tool unexpectedly re-order Oracle table columns

    XMLWordPrintableJSON

    Details

      Description

      Sqoop export tool (v1.3.0-cdh3u1) when exports a set of files from HDFS back to an Oracle database unexpectedly re-order Oracle table columns (number of columns > 62). As a result, input data fields mismatched table columns.

      Step to reproduce:
      1) create an Oracle table with 100 columns. For instance, create table lingo (col1 varchar(8), col2 varchar(8), ...., col100 varchar(8));

      2) create a text file contains the following line and copy it over to HDFS (/user/lingo/data/lingo.csv)
      1,2,3,...,100

      3) run sqoop export tool similar to the following -
      sqoop export --connect jdbc:oracle:thin:@//dbhost:1521/dbSID
      --username scott --password tiger --table lingo --input-fields-terminated-by , --verbose --export-dir /user/lingo/data

      One would expect sqoop export tool correctly insert a record, similar to the following -

      col1 col2 col3 ... col100
      1 2 3 ... 100

      Unfortunately, here is the record inserted to Oracle table -

      col1 col2 col3 ... col39 col40 col41 ... col99 col100
      62 63 64 ... 100 1 2 ... 60 61

      Looking at the Sqoop export console, it says -
      ...
      12/04/25 17:27:43 DEBUG orm.ClassWriter: selected columns:
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL62
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL63
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL64
      ...
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL97
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL98
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL99
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL100
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL1
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL2
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL3
      ...
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL59
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL60
      12/04/25 17:27:43 DEBUG orm.ClassWriter: COL61

      Apparently, somewhat Sqoop export tool re-ordered an Oracle table's columns and thus input data fields mismatched the columns.

        Attachments

          Activity

            People

            • Assignee:
              cheolsoo Cheolsoo Park
              Reporter:
              lingo_lin Joseph Lin
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: