You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@sqoop.apache.org by Venkat Ranganathan <n....@live.com> on 2013/03/19 17:45:57 UTC
Review Request: Fix for SQOOP-932
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/
-----------------------------------------------------------
Review request for Sqoop and Jarek Cecho.
Description
-------
Fixes for SQOOP-932
Diffs
-----
src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
src/java/org/apache/sqoop/manager/DirectNetezzaManager.java 0a1e605
src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
Diff: https://reviews.apache.org/r/10018/diff/
Testing
-------
All unit tests pass - added more tests for testing this functionality
Thanks,
Venkat Ranganathan
Re: Review Request: Fix for SQOOP-932
Posted by Jarek Cecho <ja...@apache.org>.
> On March 22, 2013, 3:22 a.m., Jarek Cecho wrote:
> > Ship It!
Would you mind attaching the final patch to the JIRA, Venkat?
- Jarek
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18236
-----------------------------------------------------------
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Jarek Cecho <ja...@apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18236
-----------------------------------------------------------
Ship it!
Ship It!
- Jarek Cecho
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Venkat Ranganathan <n....@live.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/
-----------------------------------------------------------
(Updated March 21, 2013, 12:08 a.m.)
Review request for Sqoop and Jarek Cecho.
Changes
-------
Updated patch with trunk changes
Description
-------
Fixes for SQOOP-932
Diffs (updated)
-----
src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
Diff: https://reviews.apache.org/r/10018/diff/
Testing
-------
All unit tests pass - added more tests for testing this functionality
Thanks,
Venkat Ranganathan
Re: Review Request: Fix for SQOOP-932
Posted by Venkat Ranganathan <n....@live.com>.
> On March 20, 2013, 9:16 p.m., Jarek Cecho wrote:
> > src/java/org/apache/sqoop/manager/DirectNetezzaManager.java, lines 78-83
> > <https://reviews.apache.org/r/10018/diff/2/?file=271931#file271931line78>
> >
> > My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
Thanks Jarcec
The Netezza external table feature supports NULLVALUE for nonstring columns also. The unit tests tests it with an Integer column if you see.
Thanks
Venkat
- Venkat
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 19, 2013, 6:06 p.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 19, 2013, 6:06 p.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java 0a1e605
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Venkat Ranganathan <n....@live.com>.
On March 20, 2013, 9:16 p.m., Venkat Ranganathan wrote:
> > Jarcec
Hi Jarcec
With 937 checked in, I don't think this patch will apply cleanly. Let me rebase and regenerate this patch
Thanks
- Venkat
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 19, 2013, 6:06 p.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 19, 2013, 6:06 p.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java 0a1e605
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Venkat Ranganathan <n....@live.com>.
> On March 20, 2013, 9:16 p.m., Jarek Cecho wrote:
> > src/java/org/apache/sqoop/manager/DirectNetezzaManager.java, lines 78-83
> > <https://reviews.apache.org/r/10018/diff/2/?file=271931#file271931line78>
> >
> > My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
>
> Venkat Ranganathan wrote:
> Thanks Jarcec
>
> The Netezza external table feature supports NULLVALUE for nonstring columns also. The unit tests tests it with an Integer column if you see.
>
> Thanks
> Venkat
>
> Jarek Cecho wrote:
> Hi Venkat,
> thank you for your feedback. I was actually looking at that particular test before, but I came to a conclusion that it's passing simply because "\N" is invalid value for integer based column. To verify my theory, I've added another test to DirectNetezzaExportManualTest that is using string "1" for null escape character (e.g. value that is valid for integer based column):
>
> @Test
> public void testNullStringExport2() throws Exception {
>
> String [] extraArgs = {
> "--input-null-string", "1",
> "--input-null-non-string", "1",
> "--input-escaped-by", "\\",
> };
> ColumnGenerator[] extraCols = new ColumnGenerator[] {
> new ColumnGenerator() {
> @Override
> public String getExportText(int rowNum) {
> return "1";
> }
>
> @Override
> public String getVerifyText(int rowNum) {
> return null;
> }
>
> @Override
> public String getType() {
> return "INTEGER";
> }
> },
> };
>
> String[] argv = getArgv(true, 10, 10, extraArgs);
> runNetezzaTest(getTableName(), argv, extraCols);
> }
>
> And this particular test is failing for me:
>
> Testcase: testNullStringExport2 took 2.528 sec
> FAILED
> Got unexpected column value expected:<null> but was:<1>
> junit.framework.ComparisonFailure: Got unexpected column value expected:<null> but was:<1>
> at com.cloudera.sqoop.TestExport.assertColValForRowId(TestExport.java:380)
> at com.cloudera.sqoop.TestExport.assertColMinAndMax(TestExport.java:398)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.runNetezzaTest(DirectNetezzaExportManualTest.java:131)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.testNullStringExport2(DirectNetezzaExportManualTest.java:205)
>
>
> I've also tried similar test for direct import by adding following test to NetezzaImportManualTest:
>
> @Test
> public void testDirectNullStringValue() throws Exception {
>
>
> String [] extraArgs = {
> "--null-string", "\\\\N",
> "--null-non-string", "\\\\N",
> };
>
> String[] expectedResultsWithNulls =
> getExpectedResultsWithNulls();
> String tableNameWithNull = getTableName() + "_W_N";
>
> runNetezzaTest(true, tableNameWithNull, expectedResultsWithNulls,
> extraArgs);
> }
>
> Generated output seems to be suggesting that the substitution character is not being used for the integer column:
>
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 1,Aaron,2009-05-14,1000000,T,engineering,,1
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 3,Fred,2009-01-23,15,F,marketing,,3
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 2,Bob,2009-04-20,400,T,sales,,2
>
> Hope it helps!
>
> Jarcec
>
> Venkat Ranganathan wrote:
> Thanks Jarcec for the examples
>
> On the export - I think it may be a bug in NZ load that some valid values for NULL representation (like 1 in your case) are passed as is. If \N is not treated as NULL, then we will have the records treated as bad and written to bad records, right?
>
> On the import, I saw the issue, but it was with any columns. I think it might also be tied to the JDBC driver version and the NZ version. For example, when I was researching Netezza forums, I saw reports that the NULL string should be 0-4 chars and only ASCII chars and no special chars, but then it has been changed in the later documentation to UTF-8 chars.
>
> I agree it is not consistent.
> Thanks
>
> Venkat
>
> Jarek Cecho wrote:
> Export:
> Yeah agreed, I would also expect this particular row to be moved as bad one. But I'm not extremely surprised to be honest. I've tried the same test with VARCHAR based column and everything worked. Thus my assumption that the NULLVALUE is used only for string based columns.
>
> Import:
> I've tried the same example with VARCHAR based column and I got the NULL substitution character instead of empty string. Which is source of my feeling that NULLVALUE is used only for string based columns. Would you mind taking a look how it behave in your environment?
>
>
> Venkat Ranganathan wrote:
> Hi Jarcec
>
> I tried it with NPS 6 and 7. The char/varchar columns do get converted and int column was not. I tested it with my 6 and 7 emulators. But if you go by the Data loading guide for Netezza, then the option applies to all columns in both load and unload. So, it is more of a bug I would assume. But it is good to document it.
>
> Thanks for bringing this up. I will create a JIRA to update the doc
Hi Jarcec
BTW, I created the JIRA and updated a patch for that also
Thanks
- Venkat
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Jarek Cecho <ja...@apache.org>.
> On March 20, 2013, 9:16 p.m., Jarek Cecho wrote:
> > src/java/org/apache/sqoop/manager/DirectNetezzaManager.java, lines 78-83
> > <https://reviews.apache.org/r/10018/diff/2/?file=271931#file271931line78>
> >
> > My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
>
> Venkat Ranganathan wrote:
> Thanks Jarcec
>
> The Netezza external table feature supports NULLVALUE for nonstring columns also. The unit tests tests it with an Integer column if you see.
>
> Thanks
> Venkat
>
> Jarek Cecho wrote:
> Hi Venkat,
> thank you for your feedback. I was actually looking at that particular test before, but I came to a conclusion that it's passing simply because "\N" is invalid value for integer based column. To verify my theory, I've added another test to DirectNetezzaExportManualTest that is using string "1" for null escape character (e.g. value that is valid for integer based column):
>
> @Test
> public void testNullStringExport2() throws Exception {
>
> String [] extraArgs = {
> "--input-null-string", "1",
> "--input-null-non-string", "1",
> "--input-escaped-by", "\\",
> };
> ColumnGenerator[] extraCols = new ColumnGenerator[] {
> new ColumnGenerator() {
> @Override
> public String getExportText(int rowNum) {
> return "1";
> }
>
> @Override
> public String getVerifyText(int rowNum) {
> return null;
> }
>
> @Override
> public String getType() {
> return "INTEGER";
> }
> },
> };
>
> String[] argv = getArgv(true, 10, 10, extraArgs);
> runNetezzaTest(getTableName(), argv, extraCols);
> }
>
> And this particular test is failing for me:
>
> Testcase: testNullStringExport2 took 2.528 sec
> FAILED
> Got unexpected column value expected:<null> but was:<1>
> junit.framework.ComparisonFailure: Got unexpected column value expected:<null> but was:<1>
> at com.cloudera.sqoop.TestExport.assertColValForRowId(TestExport.java:380)
> at com.cloudera.sqoop.TestExport.assertColMinAndMax(TestExport.java:398)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.runNetezzaTest(DirectNetezzaExportManualTest.java:131)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.testNullStringExport2(DirectNetezzaExportManualTest.java:205)
>
>
> I've also tried similar test for direct import by adding following test to NetezzaImportManualTest:
>
> @Test
> public void testDirectNullStringValue() throws Exception {
>
>
> String [] extraArgs = {
> "--null-string", "\\\\N",
> "--null-non-string", "\\\\N",
> };
>
> String[] expectedResultsWithNulls =
> getExpectedResultsWithNulls();
> String tableNameWithNull = getTableName() + "_W_N";
>
> runNetezzaTest(true, tableNameWithNull, expectedResultsWithNulls,
> extraArgs);
> }
>
> Generated output seems to be suggesting that the substitution character is not being used for the integer column:
>
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 1,Aaron,2009-05-14,1000000,T,engineering,,1
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 3,Fred,2009-01-23,15,F,marketing,,3
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 2,Bob,2009-04-20,400,T,sales,,2
>
> Hope it helps!
>
> Jarcec
>
> Venkat Ranganathan wrote:
> Thanks Jarcec for the examples
>
> On the export - I think it may be a bug in NZ load that some valid values for NULL representation (like 1 in your case) are passed as is. If \N is not treated as NULL, then we will have the records treated as bad and written to bad records, right?
>
> On the import, I saw the issue, but it was with any columns. I think it might also be tied to the JDBC driver version and the NZ version. For example, when I was researching Netezza forums, I saw reports that the NULL string should be 0-4 chars and only ASCII chars and no special chars, but then it has been changed in the later documentation to UTF-8 chars.
>
> I agree it is not consistent.
> Thanks
>
> Venkat
>
> Jarek Cecho wrote:
> Export:
> Yeah agreed, I would also expect this particular row to be moved as bad one. But I'm not extremely surprised to be honest. I've tried the same test with VARCHAR based column and everything worked. Thus my assumption that the NULLVALUE is used only for string based columns.
>
> Import:
> I've tried the same example with VARCHAR based column and I got the NULL substitution character instead of empty string. Which is source of my feeling that NULLVALUE is used only for string based columns. Would you mind taking a look how it behave in your environment?
>
>
> Venkat Ranganathan wrote:
> Hi Jarcec
>
> I tried it with NPS 6 and 7. The char/varchar columns do get converted and int column was not. I tested it with my 6 and 7 emulators. But if you go by the Data loading guide for Netezza, then the option applies to all columns in both load and unload. So, it is more of a bug I would assume. But it is good to document it.
>
> Thanks for bringing this up. I will create a JIRA to update the doc
>
> Venkat Ranganathan wrote:
> Hi Jarcec
>
> BTW, I created the JIRA and updated a patch for that also
>
> Thanks
Thank you for creating the documentation JIRA Venkat! I've tried yet another test case:
@Test
public void testNullStringExport3() throws Exception {
String [] extraArgs = {
"--input-null-string", "N",
"--input-null-non-string", "N",
"--input-escaped-by", "\\",
};
ColumnGenerator[] extraCols = new ColumnGenerator[] {
new ColumnGenerator() {
@Override
public String getExportText(int rowNum) {
return "B";
}
@Override
public String getVerifyText(int rowNum) {
return null;
}
@Override
public String getType() {
return "INTEGER";
}
},
};
String[] argv = getArgv(true, 10, 10, extraArgs);
runNetezzaTest(getTableName(), argv, extraCols);
}
Interestingly this test case actually fails. I found the behavior of the external table feature quite buggy on Netezza side. I think that your change to documentation explains it correctly, so I'll go ahead and commit it.
- Jarek
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Jarek Cecho <ja...@apache.org>.
> On March 20, 2013, 9:16 p.m., Jarek Cecho wrote:
> > src/java/org/apache/sqoop/manager/DirectNetezzaManager.java, lines 78-83
> > <https://reviews.apache.org/r/10018/diff/2/?file=271931#file271931line78>
> >
> > My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
>
> Venkat Ranganathan wrote:
> Thanks Jarcec
>
> The Netezza external table feature supports NULLVALUE for nonstring columns also. The unit tests tests it with an Integer column if you see.
>
> Thanks
> Venkat
Hi Venkat,
thank you for your feedback. I was actually looking at that particular test before, but I came to a conclusion that it's passing simply because "\N" is invalid value for integer based column. To verify my theory, I've added another test to DirectNetezzaExportManualTest that is using string "1" for null escape character (e.g. value that is valid for integer based column):
@Test
public void testNullStringExport2() throws Exception {
String [] extraArgs = {
"--input-null-string", "1",
"--input-null-non-string", "1",
"--input-escaped-by", "\\",
};
ColumnGenerator[] extraCols = new ColumnGenerator[] {
new ColumnGenerator() {
@Override
public String getExportText(int rowNum) {
return "1";
}
@Override
public String getVerifyText(int rowNum) {
return null;
}
@Override
public String getType() {
return "INTEGER";
}
},
};
String[] argv = getArgv(true, 10, 10, extraArgs);
runNetezzaTest(getTableName(), argv, extraCols);
}
And this particular test is failing for me:
Testcase: testNullStringExport2 took 2.528 sec
FAILED
Got unexpected column value expected:<null> but was:<1>
junit.framework.ComparisonFailure: Got unexpected column value expected:<null> but was:<1>
at com.cloudera.sqoop.TestExport.assertColValForRowId(TestExport.java:380)
at com.cloudera.sqoop.TestExport.assertColMinAndMax(TestExport.java:398)
at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.runNetezzaTest(DirectNetezzaExportManualTest.java:131)
at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.testNullStringExport2(DirectNetezzaExportManualTest.java:205)
I've also tried similar test for direct import by adding following test to NetezzaImportManualTest:
@Test
public void testDirectNullStringValue() throws Exception {
String [] extraArgs = {
"--null-string", "\\\\N",
"--null-non-string", "\\\\N",
};
String[] expectedResultsWithNulls =
getExpectedResultsWithNulls();
String tableNameWithNull = getTableName() + "_W_N";
runNetezzaTest(true, tableNameWithNull, expectedResultsWithNulls,
extraArgs);
}
Generated output seems to be suggesting that the substitution character is not being used for the integer column:
22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 1,Aaron,2009-05-14,1000000,T,engineering,,1
22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 3,Fred,2009-01-23,15,F,marketing,,3
22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 2,Bob,2009-04-20,400,T,sales,,2
Hope it helps!
Jarcec
- Jarek
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Venkat Ranganathan <n....@live.com>.
> On March 20, 2013, 9:16 p.m., Jarek Cecho wrote:
> > src/java/org/apache/sqoop/manager/DirectNetezzaManager.java, lines 78-83
> > <https://reviews.apache.org/r/10018/diff/2/?file=271931#file271931line78>
> >
> > My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
>
> Venkat Ranganathan wrote:
> Thanks Jarcec
>
> The Netezza external table feature supports NULLVALUE for nonstring columns also. The unit tests tests it with an Integer column if you see.
>
> Thanks
> Venkat
>
> Jarek Cecho wrote:
> Hi Venkat,
> thank you for your feedback. I was actually looking at that particular test before, but I came to a conclusion that it's passing simply because "\N" is invalid value for integer based column. To verify my theory, I've added another test to DirectNetezzaExportManualTest that is using string "1" for null escape character (e.g. value that is valid for integer based column):
>
> @Test
> public void testNullStringExport2() throws Exception {
>
> String [] extraArgs = {
> "--input-null-string", "1",
> "--input-null-non-string", "1",
> "--input-escaped-by", "\\",
> };
> ColumnGenerator[] extraCols = new ColumnGenerator[] {
> new ColumnGenerator() {
> @Override
> public String getExportText(int rowNum) {
> return "1";
> }
>
> @Override
> public String getVerifyText(int rowNum) {
> return null;
> }
>
> @Override
> public String getType() {
> return "INTEGER";
> }
> },
> };
>
> String[] argv = getArgv(true, 10, 10, extraArgs);
> runNetezzaTest(getTableName(), argv, extraCols);
> }
>
> And this particular test is failing for me:
>
> Testcase: testNullStringExport2 took 2.528 sec
> FAILED
> Got unexpected column value expected:<null> but was:<1>
> junit.framework.ComparisonFailure: Got unexpected column value expected:<null> but was:<1>
> at com.cloudera.sqoop.TestExport.assertColValForRowId(TestExport.java:380)
> at com.cloudera.sqoop.TestExport.assertColMinAndMax(TestExport.java:398)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.runNetezzaTest(DirectNetezzaExportManualTest.java:131)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.testNullStringExport2(DirectNetezzaExportManualTest.java:205)
>
>
> I've also tried similar test for direct import by adding following test to NetezzaImportManualTest:
>
> @Test
> public void testDirectNullStringValue() throws Exception {
>
>
> String [] extraArgs = {
> "--null-string", "\\\\N",
> "--null-non-string", "\\\\N",
> };
>
> String[] expectedResultsWithNulls =
> getExpectedResultsWithNulls();
> String tableNameWithNull = getTableName() + "_W_N";
>
> runNetezzaTest(true, tableNameWithNull, expectedResultsWithNulls,
> extraArgs);
> }
>
> Generated output seems to be suggesting that the substitution character is not being used for the integer column:
>
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 1,Aaron,2009-05-14,1000000,T,engineering,,1
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 3,Fred,2009-01-23,15,F,marketing,,3
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 2,Bob,2009-04-20,400,T,sales,,2
>
> Hope it helps!
>
> Jarcec
>
> Venkat Ranganathan wrote:
> Thanks Jarcec for the examples
>
> On the export - I think it may be a bug in NZ load that some valid values for NULL representation (like 1 in your case) are passed as is. If \N is not treated as NULL, then we will have the records treated as bad and written to bad records, right?
>
> On the import, I saw the issue, but it was with any columns. I think it might also be tied to the JDBC driver version and the NZ version. For example, when I was researching Netezza forums, I saw reports that the NULL string should be 0-4 chars and only ASCII chars and no special chars, but then it has been changed in the later documentation to UTF-8 chars.
>
> I agree it is not consistent.
> Thanks
>
> Venkat
>
> Jarek Cecho wrote:
> Export:
> Yeah agreed, I would also expect this particular row to be moved as bad one. But I'm not extremely surprised to be honest. I've tried the same test with VARCHAR based column and everything worked. Thus my assumption that the NULLVALUE is used only for string based columns.
>
> Import:
> I've tried the same example with VARCHAR based column and I got the NULL substitution character instead of empty string. Which is source of my feeling that NULLVALUE is used only for string based columns. Would you mind taking a look how it behave in your environment?
>
>
> Venkat Ranganathan wrote:
> Hi Jarcec
>
> I tried it with NPS 6 and 7. The char/varchar columns do get converted and int column was not. I tested it with my 6 and 7 emulators. But if you go by the Data loading guide for Netezza, then the option applies to all columns in both load and unload. So, it is more of a bug I would assume. But it is good to document it.
>
> Thanks for bringing this up. I will create a JIRA to update the doc
>
> Venkat Ranganathan wrote:
> Hi Jarcec
>
> BTW, I created the JIRA and updated a patch for that also
>
> Thanks
>
> Jarek Cecho wrote:
> Thank you for creating the documentation JIRA Venkat! I've tried yet another test case:
>
>
> @Test
> public void testNullStringExport3() throws Exception {
>
> String [] extraArgs = {
> "--input-null-string", "N",
> "--input-null-non-string", "N",
> "--input-escaped-by", "\\",
>
> };
> ColumnGenerator[] extraCols = new ColumnGenerator[] {
> new ColumnGenerator() {
> @Override
> public String getExportText(int rowNum) {
> return "B";
> }
>
> @Override
> public String getVerifyText(int rowNum) {
> return null;
> }
>
> @Override
> public String getType() {
> return "INTEGER";
> }
> },
> };
>
> String[] argv = getArgv(true, 10, 10, extraArgs);
> runNetezzaTest(getTableName(), argv, extraCols);
> }
>
> Interestingly this test case actually fails. I found the behavior of the external table feature quite buggy on Netezza side. I think that your change to documentation explains it correctly, so I'll go ahead and commit it.
Yes. I found it buggy. The documentation seems to be evolving also. I will update the patch to the JIRA. We can keep an eye on this to see if there is any JDBC/Netezza changes that changes the behavior in future.
Thanks Jarcec
Venkat
- Venkat
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Venkat Ranganathan <n....@live.com>.
> On March 20, 2013, 9:16 p.m., Jarek Cecho wrote:
> > src/java/org/apache/sqoop/manager/DirectNetezzaManager.java, lines 78-83
> > <https://reviews.apache.org/r/10018/diff/2/?file=271931#file271931line78>
> >
> > My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
>
> Venkat Ranganathan wrote:
> Thanks Jarcec
>
> The Netezza external table feature supports NULLVALUE for nonstring columns also. The unit tests tests it with an Integer column if you see.
>
> Thanks
> Venkat
>
> Jarek Cecho wrote:
> Hi Venkat,
> thank you for your feedback. I was actually looking at that particular test before, but I came to a conclusion that it's passing simply because "\N" is invalid value for integer based column. To verify my theory, I've added another test to DirectNetezzaExportManualTest that is using string "1" for null escape character (e.g. value that is valid for integer based column):
>
> @Test
> public void testNullStringExport2() throws Exception {
>
> String [] extraArgs = {
> "--input-null-string", "1",
> "--input-null-non-string", "1",
> "--input-escaped-by", "\\",
> };
> ColumnGenerator[] extraCols = new ColumnGenerator[] {
> new ColumnGenerator() {
> @Override
> public String getExportText(int rowNum) {
> return "1";
> }
>
> @Override
> public String getVerifyText(int rowNum) {
> return null;
> }
>
> @Override
> public String getType() {
> return "INTEGER";
> }
> },
> };
>
> String[] argv = getArgv(true, 10, 10, extraArgs);
> runNetezzaTest(getTableName(), argv, extraCols);
> }
>
> And this particular test is failing for me:
>
> Testcase: testNullStringExport2 took 2.528 sec
> FAILED
> Got unexpected column value expected:<null> but was:<1>
> junit.framework.ComparisonFailure: Got unexpected column value expected:<null> but was:<1>
> at com.cloudera.sqoop.TestExport.assertColValForRowId(TestExport.java:380)
> at com.cloudera.sqoop.TestExport.assertColMinAndMax(TestExport.java:398)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.runNetezzaTest(DirectNetezzaExportManualTest.java:131)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.testNullStringExport2(DirectNetezzaExportManualTest.java:205)
>
>
> I've also tried similar test for direct import by adding following test to NetezzaImportManualTest:
>
> @Test
> public void testDirectNullStringValue() throws Exception {
>
>
> String [] extraArgs = {
> "--null-string", "\\\\N",
> "--null-non-string", "\\\\N",
> };
>
> String[] expectedResultsWithNulls =
> getExpectedResultsWithNulls();
> String tableNameWithNull = getTableName() + "_W_N";
>
> runNetezzaTest(true, tableNameWithNull, expectedResultsWithNulls,
> extraArgs);
> }
>
> Generated output seems to be suggesting that the substitution character is not being used for the integer column:
>
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 1,Aaron,2009-05-14,1000000,T,engineering,,1
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 3,Fred,2009-01-23,15,F,marketing,,3
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 2,Bob,2009-04-20,400,T,sales,,2
>
> Hope it helps!
>
> Jarcec
Thanks Jarcec for the examples
On the export - I think it may be a bug in NZ load that some valid values for NULL representation (like 1 in your case) are passed as is. If \N is not treated as NULL, then we will have the records treated as bad and written to bad records, right?
On the import, I saw the issue, but it was with any columns. I think it might also be tied to the JDBC driver version and the NZ version. For example, when I was researching Netezza forums, I saw reports that the NULL string should be 0-4 chars and only ASCII chars and no special chars, but then it has been changed in the later documentation to UTF-8 chars.
I agree it is not consistent.
Thanks
Venkat
- Venkat
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Venkat Ranganathan <n....@live.com>.
> On March 20, 2013, 9:16 p.m., Jarek Cecho wrote:
> > src/java/org/apache/sqoop/manager/DirectNetezzaManager.java, lines 78-83
> > <https://reviews.apache.org/r/10018/diff/2/?file=271931#file271931line78>
> >
> > My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
>
> Venkat Ranganathan wrote:
> Thanks Jarcec
>
> The Netezza external table feature supports NULLVALUE for nonstring columns also. The unit tests tests it with an Integer column if you see.
>
> Thanks
> Venkat
>
> Jarek Cecho wrote:
> Hi Venkat,
> thank you for your feedback. I was actually looking at that particular test before, but I came to a conclusion that it's passing simply because "\N" is invalid value for integer based column. To verify my theory, I've added another test to DirectNetezzaExportManualTest that is using string "1" for null escape character (e.g. value that is valid for integer based column):
>
> @Test
> public void testNullStringExport2() throws Exception {
>
> String [] extraArgs = {
> "--input-null-string", "1",
> "--input-null-non-string", "1",
> "--input-escaped-by", "\\",
> };
> ColumnGenerator[] extraCols = new ColumnGenerator[] {
> new ColumnGenerator() {
> @Override
> public String getExportText(int rowNum) {
> return "1";
> }
>
> @Override
> public String getVerifyText(int rowNum) {
> return null;
> }
>
> @Override
> public String getType() {
> return "INTEGER";
> }
> },
> };
>
> String[] argv = getArgv(true, 10, 10, extraArgs);
> runNetezzaTest(getTableName(), argv, extraCols);
> }
>
> And this particular test is failing for me:
>
> Testcase: testNullStringExport2 took 2.528 sec
> FAILED
> Got unexpected column value expected:<null> but was:<1>
> junit.framework.ComparisonFailure: Got unexpected column value expected:<null> but was:<1>
> at com.cloudera.sqoop.TestExport.assertColValForRowId(TestExport.java:380)
> at com.cloudera.sqoop.TestExport.assertColMinAndMax(TestExport.java:398)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.runNetezzaTest(DirectNetezzaExportManualTest.java:131)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.testNullStringExport2(DirectNetezzaExportManualTest.java:205)
>
>
> I've also tried similar test for direct import by adding following test to NetezzaImportManualTest:
>
> @Test
> public void testDirectNullStringValue() throws Exception {
>
>
> String [] extraArgs = {
> "--null-string", "\\\\N",
> "--null-non-string", "\\\\N",
> };
>
> String[] expectedResultsWithNulls =
> getExpectedResultsWithNulls();
> String tableNameWithNull = getTableName() + "_W_N";
>
> runNetezzaTest(true, tableNameWithNull, expectedResultsWithNulls,
> extraArgs);
> }
>
> Generated output seems to be suggesting that the substitution character is not being used for the integer column:
>
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 1,Aaron,2009-05-14,1000000,T,engineering,,1
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 3,Fred,2009-01-23,15,F,marketing,,3
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 2,Bob,2009-04-20,400,T,sales,,2
>
> Hope it helps!
>
> Jarcec
>
> Venkat Ranganathan wrote:
> Thanks Jarcec for the examples
>
> On the export - I think it may be a bug in NZ load that some valid values for NULL representation (like 1 in your case) are passed as is. If \N is not treated as NULL, then we will have the records treated as bad and written to bad records, right?
>
> On the import, I saw the issue, but it was with any columns. I think it might also be tied to the JDBC driver version and the NZ version. For example, when I was researching Netezza forums, I saw reports that the NULL string should be 0-4 chars and only ASCII chars and no special chars, but then it has been changed in the later documentation to UTF-8 chars.
>
> I agree it is not consistent.
> Thanks
>
> Venkat
>
> Jarek Cecho wrote:
> Export:
> Yeah agreed, I would also expect this particular row to be moved as bad one. But I'm not extremely surprised to be honest. I've tried the same test with VARCHAR based column and everything worked. Thus my assumption that the NULLVALUE is used only for string based columns.
>
> Import:
> I've tried the same example with VARCHAR based column and I got the NULL substitution character instead of empty string. Which is source of my feeling that NULLVALUE is used only for string based columns. Would you mind taking a look how it behave in your environment?
>
Hi Jarcec
I tried it with NPS 6 and 7. The char/varchar columns do get converted and int column was not. I tested it with my 6 and 7 emulators. But if you go by the Data loading guide for Netezza, then the option applies to all columns in both load and unload. So, it is more of a bug I would assume. But it is good to document it.
Thanks for bringing this up. I will create a JIRA to update the doc
- Venkat
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Jarek Cecho <ja...@apache.org>.
> On March 20, 2013, 9:16 p.m., Jarek Cecho wrote:
> > src/java/org/apache/sqoop/manager/DirectNetezzaManager.java, lines 78-83
> > <https://reviews.apache.org/r/10018/diff/2/?file=271931#file271931line78>
> >
> > My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
>
> Venkat Ranganathan wrote:
> Thanks Jarcec
>
> The Netezza external table feature supports NULLVALUE for nonstring columns also. The unit tests tests it with an Integer column if you see.
>
> Thanks
> Venkat
>
> Jarek Cecho wrote:
> Hi Venkat,
> thank you for your feedback. I was actually looking at that particular test before, but I came to a conclusion that it's passing simply because "\N" is invalid value for integer based column. To verify my theory, I've added another test to DirectNetezzaExportManualTest that is using string "1" for null escape character (e.g. value that is valid for integer based column):
>
> @Test
> public void testNullStringExport2() throws Exception {
>
> String [] extraArgs = {
> "--input-null-string", "1",
> "--input-null-non-string", "1",
> "--input-escaped-by", "\\",
> };
> ColumnGenerator[] extraCols = new ColumnGenerator[] {
> new ColumnGenerator() {
> @Override
> public String getExportText(int rowNum) {
> return "1";
> }
>
> @Override
> public String getVerifyText(int rowNum) {
> return null;
> }
>
> @Override
> public String getType() {
> return "INTEGER";
> }
> },
> };
>
> String[] argv = getArgv(true, 10, 10, extraArgs);
> runNetezzaTest(getTableName(), argv, extraCols);
> }
>
> And this particular test is failing for me:
>
> Testcase: testNullStringExport2 took 2.528 sec
> FAILED
> Got unexpected column value expected:<null> but was:<1>
> junit.framework.ComparisonFailure: Got unexpected column value expected:<null> but was:<1>
> at com.cloudera.sqoop.TestExport.assertColValForRowId(TestExport.java:380)
> at com.cloudera.sqoop.TestExport.assertColMinAndMax(TestExport.java:398)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.runNetezzaTest(DirectNetezzaExportManualTest.java:131)
> at com.cloudera.sqoop.manager.DirectNetezzaExportManualTest.testNullStringExport2(DirectNetezzaExportManualTest.java:205)
>
>
> I've also tried similar test for direct import by adding following test to NetezzaImportManualTest:
>
> @Test
> public void testDirectNullStringValue() throws Exception {
>
>
> String [] extraArgs = {
> "--null-string", "\\\\N",
> "--null-non-string", "\\\\N",
> };
>
> String[] expectedResultsWithNulls =
> getExpectedResultsWithNulls();
> String tableNameWithNull = getTableName() + "_W_N";
>
> runNetezzaTest(true, tableNameWithNull, expectedResultsWithNulls,
> extraArgs);
> }
>
> Generated output seems to be suggesting that the substitution character is not being used for the integer column:
>
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 1,Aaron,2009-05-14,1000000,T,engineering,,1
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 3,Fred,2009-01-23,15,F,marketing,,3
> 22218 [main] INFO com.cloudera.sqoop.manager.NetezzaImportManualTest - Line read from file = 2,Bob,2009-04-20,400,T,sales,,2
>
> Hope it helps!
>
> Jarcec
>
> Venkat Ranganathan wrote:
> Thanks Jarcec for the examples
>
> On the export - I think it may be a bug in NZ load that some valid values for NULL representation (like 1 in your case) are passed as is. If \N is not treated as NULL, then we will have the records treated as bad and written to bad records, right?
>
> On the import, I saw the issue, but it was with any columns. I think it might also be tied to the JDBC driver version and the NZ version. For example, when I was researching Netezza forums, I saw reports that the NULL string should be 0-4 chars and only ASCII chars and no special chars, but then it has been changed in the later documentation to UTF-8 chars.
>
> I agree it is not consistent.
> Thanks
>
> Venkat
Export:
Yeah agreed, I would also expect this particular row to be moved as bad one. But I'm not extremely surprised to be honest. I've tried the same test with VARCHAR based column and everything worked. Thus my assumption that the NULLVALUE is used only for string based columns.
Import:
I've tried the same example with VARCHAR based column and I got the NULL substitution character instead of empty string. Which is source of my feeling that NULLVALUE is used only for string based columns. Would you mind taking a look how it behave in your environment?
- Jarek
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
On March 21, 2013, 12:08 a.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 21, 2013, 12:08 a.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java ef98936
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Jarek Cecho <ja...@apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/#review18174
-----------------------------------------------------------
Hi Venkat,
good job on this patch. I do have one question:
src/java/org/apache/sqoop/manager/DirectNetezzaManager.java
<https://reviews.apache.org/r/10018/#comment38344>
My Netezza knowledge is a bit rusty these day, but I do have feeling that the external table parameter "NULLVALUE" is used only for string based columns (varchar, ...). For all other column types (int, float, ...) empty string is used to encode NULL value. On precondition that this is still the case, shouldn't the condition be more if(nullNonStrValue != null) { error; }?
Jarcec
- Jarek Cecho
On March 19, 2013, 6:06 p.m., Venkat Ranganathan wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/10018/
> -----------------------------------------------------------
>
> (Updated March 19, 2013, 6:06 p.m.)
>
>
> Review request for Sqoop and Jarek Cecho.
>
>
> Description
> -------
>
> Fixes for SQOOP-932
>
>
> Diffs
> -----
>
> src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
> src/java/org/apache/sqoop/manager/DirectNetezzaManager.java 0a1e605
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
> src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
> src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
> src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
> src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
> src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
>
> Diff: https://reviews.apache.org/r/10018/diff/
>
>
> Testing
> -------
>
> All unit tests pass - added more tests for testing this functionality
>
>
> Thanks,
>
> Venkat Ranganathan
>
>
Re: Review Request: Fix for SQOOP-932
Posted by Venkat Ranganathan <n....@live.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/10018/
-----------------------------------------------------------
(Updated March 19, 2013, 6:06 p.m.)
Review request for Sqoop and Jarek Cecho.
Changes
-------
This time with checkstyle fixes! Sorry about that
Description
-------
Fixes for SQOOP-932
Diffs (updated)
-----
src/java/org/apache/sqoop/lib/DelimiterSet.java ef62ba0
src/java/org/apache/sqoop/manager/DirectNetezzaManager.java 0a1e605
src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableExportMapper.java 410a569
src/java/org/apache/sqoop/mapreduce/db/netezza/NetezzaExternalTableImportMapper.java 9e6cab6
src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java 2a702d9
src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableImportJob.java 7ee6f70
src/test/com/cloudera/sqoop/manager/DirectNetezzaExportManualTest.java 938ffc5
src/test/com/cloudera/sqoop/manager/NetezzaExportManualTest.java 50d27fe
src/test/com/cloudera/sqoop/manager/NetezzaImportManualTest.java 3482dd8
Diff: https://reviews.apache.org/r/10018/diff/
Testing
-------
All unit tests pass - added more tests for testing this functionality
Thanks,
Venkat Ranganathan