SQOOP EXPORT OF BLANKS VALUES AS BLANK IN TERADATA

[#1]

I have pig output like below in hdfs

a,,b,,

I created target table as,

CREATE SET TABLE panda.test ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( col VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, col2 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, col3 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, col4 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( col );

And when I export it

sqoop export --connect jdbc:teradata://xxxxx/database=xx --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username xx --password xx --export-dir /test/xxx/xx/temp/teradata1 --table test --input-fields-terminated-by ',' -m 2 --input-null-string "\\N" --input-null-non-string "\\N"

or

sqoop export --connect jdbc:teradata://xxxxx/database=xx --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username xx --password xx --export-dir /test/xxx/xx/temp/teradata1 --table test --input-fields-terminated-by ',' -m 2

or

sqoop export --connect jdbc:teradata://xxxxx/database=xx --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username xx --password xx --export-dir /test/xxx/xx/temp/teradata1 --table test --input-fields-terminated-by ',' -m 2 --input-null-string "\\" --input-null-non-string "\\"

I get null values in place of blanks in teradata table.

Any one has suggestion so that I can export blank as blank in teradata.

[#2]

@Ashish Vishnoi

Try the following

sqoop export --connect jdbc:teradata://xxxxx/database=xx --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username xx --password xx --export-dir /test/xxx/xx/temp/teradata1 --table test --input-fields-terminated-by ',' -m 2 --input-null-string '\n' --input-null-non-string '\n'

[#3]

Thank you, I already tried it. But It converts blanks into nulls. I want blanks as blanks for pig output stored in hdfs like a,,b,,

[#4]

data:
a||c
|b|c
a|b|
a|\N\c
a|N|c
a|\\N|c
a|NULL|c

Table:
CREATE SET TABLE panda.test_tera2 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      col VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      col2 CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      col3 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( col );

---------------------------
Command:
sqoop export --connect jdbc:teradata://xxx/database=xx --connection-manager  org.apache.sqoop.teradata.TeradataConnManager --username  --password xx --export-dir /test/xxtemp/teradata2 --table test_tera2 --input-fields-terminated-by '|' -m 2
-------------------------
Result:
	col	col2	col3
1	a	?	        c
2	?	b         	c
3	a	\N\c      	?
4	a	N         	c
5	a	b         	?
6	a	NULL      	c
7	a	\\N       	c
------------------------------------------------
Command:
sqoop export --connect jdbc:teradata://xx/database=xx --connection-manager  org.apache.sqoop.teradata.TeradataConnManager --username xx --password xx --export-dir /test/xx/teradata2 --table test_tera2 --input-fields-terminated-by '|' -m 2 --input-null-non-string "\\N"


RESULT:
	col	col2	col3
1	a	?		c
2	?	b         	c
3	a	\N\c      	?
4	a	N         	c
5	a	b         	?
6	a	NULL      	c
7	a	\\N       	c


------------------------------------------------
Command:
sqoop export --connect jdbc:teradata://xx/database=xx --connection-manager  org.apache.sqoop.teradata.TeradataConnManager --username xx --password xx --export-dir /test/xx/teradata2 --table test_tera2 --input-fields-terminated-by '|' -m 2 --input-null-non-string "\\N" --input-null-string "\\N"
RESULT:
	col	col2	   col3
1	a	?		c
2	?	b         	c
3	a	\N\c      	?
4	a	N         	c
5	a	b         	?
6	a	NULL      	c
7	a	\\N       	c
--------------------------------------------------





I am still failed to export blanks || field between pipes as blanks in teradata with sqoop export.

Could someone explain me the reason and way to solve it?

[#5]

@mqureshi I still failed to export blanks as blanks.

[#6]

Hi @Ashish Vishnoi.

In the sqoop-export doco, it says:

"The --input-null-string and --input-null-non-string arguments are optional. If --input-null-string is not specified, then the string "null" will be interpreted as null for string-type columns. If --input-null-non-string is not specified, then both the string "null" and the empty string will be interpreted as null for non-string columns. Note that, the empty string will be always interpreted as null for non-string columns, in addition to other string if specified by --input-null-non-string"

There is another discussion here around a similar issue.

If it doesn't work even for string columns, it may be that a workaround of some kind is needed. E.g. conversion of blanks to another character (or set of characters that wouldn't normally be a part of your data set) prior to export which can be converted back to blanks once in Teradata.

Hope this helps.

[#7]

Thank you Emily for your reply.

[#8]

I have similar scenario, where I need to pull the blanks from HIVE and load to Teradata using SQOOP Export. 

In HIVE the data is stored as blanks after sqoop export the respective columns are loaded as null in Teradata. 

we are using input-null-string and input-null-non-string our sqoop export. 

If someone has an workaround please let me know. 

[#9]

@Srinivascnu As this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post. Thanks.