Load Unicode Data with Unicode Pass Through | Teradata Vantage - Loading Unicode Data with Unicode Pass Through - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™
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 Vantage, 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, B035-2425, in particular the sections regarding automatic character conversions.

For More Information

For more information on... See...
SET SESSION CHARACTER SET UNICODE Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144
Unicode Pass Through Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125