column_name is the actual column name in ods( I make up that name as column_name since those tables and columns are really existing ). case sensitive is not the issue( I checked). Sqoop is not complaining about the column names and as I said I was able to successfully run the sqoop with the same --columns argument ( same order of columns) for the insert operation. When I included upsert operation sqoop demands for the columns as the same order as in the oracle. I changed the csv file order as per the oracle order and it worked perfectly fine.
On Mon, Dec 4, 2017 at 11:03 PM, Markus Kemper <[email protected]> wrote: > Hello Ajana, > > *re --update-key column_name* > > Is "column_name" the actual name of the column having the unique key in > the Oracle table. > > *re: --columns "id,START_DT,EXP_DT,QUALITY,VAL,PROCES_DT"* > > With Oracle options that reference column names are most likely going to > be case-sensitive, is the "id" column actually "ID" in your Oracle table > schema? > > > Markus Kemper > Customer Operations Engineer > [image: www.cloudera.com] <http://www.cloudera.com> > > > On Mon, Dec 4, 2017 at 10:05 PM, Ajana Chandiruthil Sathian < > [email protected]> wrote: > >> Hello Markus, >> >> I tried with --input-lines-terminated-by instead of lines-terminated-by >> still, the --column argument is working as it supposed to work. The >> --column option worked perfectly when I did only the insert option( the >> initial load) when I am doing the upsert option, it is not considering the >> order as I mentioned in the --column sqoop argument. Please see the >> attached sqoop argument and the CSV file screenshot. I could not understand >> why --column option is not working when I am doing upsert. Andy kind of >> help is really appreciated. >> >> Column order in ods: id,START_DT,EXP_DT,VAL,QUALITY,PROCES_DT >> value order in csv file; id,START_DT,EXP_DT,QUALITY,VAL,PROCES_DT >> >> --Sqoop command. >> sqoop export --connect connection_string \ >> --username xxx \ >> --password xxxx \ >> --table xxx \ >> --export-dir /location/file.csv \ >> --input-fields-terminated-by ',' \ >> --input-lines-terminated-by '\n' \ >> --update-key column_name \ >> --update-mode allowinsert \ >> --columns "id,START_DT,EXP_DT,QUALITY,VAL,PROCES_DT" \ >> -m 1 >> >> Best, >> Ajana Sathian >> Big Data Intern >> Cars.com >> http://ajanacs.com/ >> >> On Sun, Dec 3, 2017 at 3:41 PM, Markus Kemper <[email protected]> >> wrote: >> >>> Hey Alana, >>> >>> —lines-* and —fields-* are for (sqoop import) and —input-lines-* and >>> —input-fields-* are for (sqoop export). >>> >>> If you do not specify it will use the default which may or may not be >>> compatible with your data files. >>> >>> As a best practice I always recommend explicitly setting all options to >>> avoid any confusion or assumptions. >>> >>> Thanks, Markus >>> >>> On Dec 3, 2017, at 12:46, Ajana Chandiruthil Sathian <[email protected]> >>> wrote: >>> >>> Hello Markus, >>> >>> Can I ask you one question? Does that make any change? Both are for the >>> same purpose, right? Please correct me if I am wrong. >>> >>> On Sun, Dec 3, 2017 at 10:39 AM, Markus Kemper <[email protected]> >>> wrote: >>> >>>> Hello Ajana, >>>> >>>> Have you tried using (--input-lines-terminated-by '\n') instead of >>>> (--lines-terminated-by >>>> '\n')? >>>> >>>> >>>> Markus Kemper >>>> Customer Operations Engineer >>>> [image: www.cloudera.com] <http://www.cloudera.com> >>>> >>>> >>>> On Sun, Dec 3, 2017 at 8:45 AM, Ajana Chandiruthil Sathian < >>>> [email protected]> wrote: >>>> >>>>> >>>>> ---------- Forwarded message ---------- >>>>> From: Ajana Chandiruthil Sathian <[email protected]> >>>>> Date: Wed, Nov 15, 2017 at 11:09 AM >>>>> Subject: Upsert option in Sqoop export command. >>>>> To: [email protected] >>>>> >>>>> >>>>> To whom ever it may concern, >>>>> >>>>> I have a csv file in Hadoop and I did Sqoop export to Oracle. The >>>>> column data type order in ODS is number,date, date,float,varchar and the >>>>> column data type in the csv file is number,date, float,date,varchar and I >>>>> used the --columns sqoop argument to get control in column ordering and it >>>>> worked. But I could not control the column ordering when I was doing the >>>>> upsert operation in sqoop export. It is giving me misalignment in ODS >>>>> after Sqoop( please see the attached image). The below given is the sqoop >>>>> command: >>>>> >>>>> sqoop export --connect ConnectionString \ >>>>> --username xxx \ >>>>> --password xxxx \ >>>>> --table tableName \ >>>>> --export-dir /dir/TestUpdate.txt \ >>>>> --input-fields-terminated-by ',' \ >>>>> --lines-terminated-by '\n' \ >>>>> --update-key column_name \ >>>>> --update-mode allowinsert \ >>>>> --columns "id,START_DT,VAL,end_DT,QUALITY" \ >>>>> -m 4 >>>>> >>>>> >>>> >>> >> >
