16.10 - Loading Unicode Data with Unicode Pass Through - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)
To eliminate the need to cleanse Unicode data before loading and to reduce translation errors, use Unicode Pass Through. This option allows pass through characters to be imported into and exported from Teradata. Pass through characters include:
  • BMP from Unicode versions 6.1.0 to 9.0.0, which Teradata does not support, including emoji
  • SMP from all Unicode versions, which Teradata does not support
  • Unassigned characters
  • Private use characters

UTF-8 or UTF-16 sessions with Unicode Pass Through enabled allow pass through characters to be used in requests and to be stored in or retrieved from Unicode columns. Noncharacters and characters with an invalid encoding form are changed to the REPLACEMENT CHARACTER (U+FFFD), which can pass through and be stored in Teradata with this option.

Unicode Pass Through is not supported with FastLoad, MultiLoad, Teradata Parallel Data Pump (TPump), and FastExport. Use Teradata Parallel Transporter (TPT), BTEQ, or the Teradata JDBC Driver to load or unload data containing pass through characters.
If you do not specify Unicode Pass Through for a session, the default is OFF.

BTEQ Example with Unicode Pass Through

.SESSIONS 5
.LOGON mysystem/uptuser,emoji
.REPEAT 5
SET SESSION CHARACTER SET UNICODE PASS THROUGH ON;

Teradata Parallel Transporter Example with Unicode Pass Through

VARCHAR UnicodePassThrough = ‘ON’,
The syntax applies only to these TPT operators:
  • Load
  • Update
  • Export
  • Stream
  • DDL
  • SQL Inserter
  • SQL Selector

JDBC Example with User Startup String

create user uptuser as password = emoji, startup = ’set session character set unicode pass through on’;
Connection con = DriverManager.getConnection("jdbc:teradata://dbs/RUNSTARTUP=ON", "uptuser", "emoji");

JDBC Example with SQL Request

stmt.executeUpdate("set session character set unicode pass through on");

Teradata Studio Example

In the Studio Administration Create User form, provide a startup string, such as “set session character set unicode pass through on".



When creating the connection profile for that user, add the JDBC property RUNSTARTUP=ON.



Usage Notes

  • Customers should not use Unicode Pass Through if they rely on Teradata to screen out Teradata unsupported characters.
  • You may have existing processes in place to cleanse Unicode data before loading it into Teradata Database, such as access modules and UDFs. To take full advantage of Unicode Pass Through, you must change or eliminate prior methods of cleansing Unicode data before loading. See the TPT documentation for details on configuring how TPT uses access modules. For details about configuring access modules to allow pass through characters, see Teradata Tools and Utilities Access Module Reference, in particular the sections regarding automatic character conversions.

For More Information

For the syntax of using SET SESSION CHARACTER SET UNICODE, see SQL Data Definition Language - Syntax and Examples. For further details on Unicode Pass Through, see International Character Set Support.