Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

SQOOP EXPORT OF BLANKS VALUES AS BLANK IN TERADATA

avatar
Contributor

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.

8 REPLIES 8

avatar
Super Guru
@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'

avatar
Contributor

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,,

avatar
Contributor
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?




avatar
Contributor

@mqureshi I still failed to export blanks as blanks.

avatar
Rising Star

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.

avatar
Contributor

Thank you Emily for your reply.

avatar
New Contributor

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. 

avatar
Community Manager

@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.


Regards,

Diana Torres,
Community Moderator


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community: