[SQOOP-478] Sqoop export tool unexpectedly re-order Oracle table columns

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.3.0
• Fix Version/s: 1.4.1-incubating
• Component/s: connectors/oracle
• Labels: columns export incorrect oracle order

Environment

CDH3 Update 1 (cdh3u1)

• Assignee: Cheolsoo Park
• Reporter: Joseph Lin
• Created: 26/Apr/12 23:35
• Updated: 27/Apr/12 17:42
• Resolved: 27/Apr/12 17:42

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.