Recently I have found an issue in Sqoop that when exporting data to Teradata with data value larger than 64KB to CLOB data type, job will be failed with error similar to below:
[Teradata JDBC Driver] [TeraJDBC 15.10.00.26] [Error 1186] [SQLState HY000] Parameter 11 length is 110185 bytes, which is greater than the maximum 64000 bytes that can be set.
After contacting with Teradata Support, it turned out to be a limitation/restriction from Teradata side, that for data larger than 64KB, it will require client code to use special APIs to stream data into Teradata. The sample code to achieve this can be found below, which was provided by Teradata Support personnel, and is available to be downloaded here.
//*********************************************************************
//
// Copyright (c) 2004-2008 by Teradata Corporation
// All Rights Reserved
//
//*********************************************************************
//
// File: T20203JD.java
// Header: none
// Purpose: Demonstrate insertion of LOB values using parameter
// markers.
// The program will:
// - Connect as user guest/please
// - Perform a series of insertions into the table using
// parameter markers for the values
// - Disconnect.
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeUpdate
//
// Version: Updated for Teradata V2R6
//
//*********************************************************************
import java.sql.*;
import java.io.*;
public class T20203JD
{
// Name of the user able to create, drop, and manipulate tables
public static String sUser = "guest";
public static String sPassword = "please";
public static void main(String args[])
throws ClassNotFoundException, FileNotFoundException
{
// Creation of URL to be passed to the JDBC driver
String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";
// Strings representing a prepared statement and its parameter values,
// respectively
String sInsert = "INSERT INTO employee2 VALUES(?,?,?,?,?,?,?)";
int[] Id = {100002, 100003, 100004, 100005, 100006, 100007, 100008};
String[] Name = {"James Parker", "Mary Jones", "John Walker",
"Steven Brown", "Susan Young", "Brian Lee", "Robert Martinez"};
String[] Department =
{"Marketing", "Product Development", "Human Resources",
"Customer Service", "Product Development", "Marketing",
"Human Resources"};
String[] Description =
{"Manager coordinating international sales",
"Software engineer responsible for regression test plans",
"Manager responsible for employee benefits programs",
"Software engineer providing technical applications support",
"QA engineer overlooking overseas testing",
"Team leader managing market research",
"Recruiter in charge of university relations"};
// Flag setting signaling that the BLOB data was inserted by
// streaming data out of a file
byte isRaw = 0;
try
{
System.out.println("\n Sample T20203JD: \n");
System.out.println(" Looking for the Teradata JDBC driver... ");
// Loading the Teradata JDBC driver
Class.forName("com.teradata.jdbc.TeraDriver");
System.out.println(" JDBC driver loaded. \n");
// Attempting to connect to Teradata
System.out.println(
" Attempting to connect to Teradata via JDBC driver...");
// Creating a connection object
Connection con = DriverManager.getConnection(url, sUser, sPassword);
System.out.println(" User " + sUser + " connected.");
System.out.println(" Connection to Teradata established. \n");
try
{
System.out.println(" Preparing this SQL statement for execution:\n "
+ sInsert);
// Creating a prepared statement object from an active connection
PreparedStatement pstmt = con.prepareStatement(sInsert);
System.out.println(" Prepared statement object created.");
try
{
int RowCount; // Return value for row count
// The following code will perform a series of INSERTions
// into the table using a prepared statement.
// Retrieving the number of insertions by using ID field
// to count, since ID can never be null
int len = Id.length;
for (int i = 0; i < len; i++)
{
System.out.println("\n Attempting an insertion...\n");
// Set parameter values indicated by ? (dynamic update)
// PreparedStatement.setInt, PreparedStatement.setString
// PreparedStatement.setAsciiStream, and
// PreparedStatement.setBinaryStream methods will be
// demonstrated. Please refer to the supporting driver
// documentation for the full list of methods,
// their implementations, and their return values.
System.out.println(
" Using setInt() and setString() to assign"
+ " values to parameter markers 1-4:");
// Set employee ID number
pstmt.setInt(1, Id[i]);
System.out.println(" FIRST ? set to: " + Id[i]);
// Set employee name
pstmt.setString(2, Name[i]);
System.out.println(" SECOND ? set to: " + Name[i]);
// Set employee department name
pstmt.setString(3, Department[i]);
System.out.println(" THIRD ? set to: " + Department[i]);
// Set job description
pstmt.setString(4, Description[i]);
System.out.println(" FOURTH ? set to: " + Description[i]);
System.out.println(
" Using setAsciiStream() and setBinaryStream()"
+ " to assign values to parameter markers 5-6:");
// Create a new file object to enable access to the
// resume document
File res = new File(Id[i] + ".txt");
// Create a new file input stream object to enable
// reading of data from the text file
FileInputStream empResume = new FileInputStream(res);
// Set employee resume CLOB: set the parameter value
// to the input stream above, which will have the
// specified number of bytes.
pstmt.setAsciiStream(5, empResume, (int)(res.length()));
System.out.println(" FIFTH ? set to the file: " +
Id[i] + ".txt");
// Create a new file object to enable access to the
// photograph
File photo = new File(Id[i] + ".jpg");
// Create a new file input stream object to enable
// reading of data from the binary file
FileInputStream empPhoto = new FileInputStream(photo);
// Set employee photo BLOB: set the parameter value
// to the input stream above, which will have the
// specified number of bytes.
pstmt.setBinaryStream(6, empPhoto, (int)(photo.length()));
System.out.println(" SIXTH ? set to the file: " +
Id[i] + ".jpg");
// Set insertion type flag, 0 for insertion from a file
pstmt.setByte(7, isRaw);
System.out.println(" SEVENTH ? set to : " + isRaw);
// Call without parameter to execute the SQL command
RowCount = pstmt.executeUpdate();
System.out.println("\n Insertion completed successfully: "
+ RowCount + " row(s) inserted.");
}
}
finally
{
// Close the statement
pstmt.close();
System.out.println("\n Statement object closed. \n");
}
}
finally
{
// Close the connection
System.out.println(" Closing connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed. \n");
}
System.out.println(" Sample T20203JD finished. \n");
}
catch (SQLException ex)
{
// A SQLException was generated. Catch it and display
// the error information.
// Note that there could be multiple error objects chained
// together.
System.out.println();
System.out.println("*** SQLException caught ***");
while (ex != null)
{
System.out.println(" Error code: " + ex.getErrorCode());
System.out.println(" SQL State: " + ex.getSQLState());
System.out.println(" Message: " + ex.getMessage());
ex.printStackTrace();
System.out.println();
ex = ex.getNextException();
}
throw new IllegalStateException ("Sample failed.") ;
}
} // End main
} // End class T20203JD
Currently Sqoop does not support the use of this special APIs to inject data into Teradata, so the export will fail with mentioned error.
There is no other way, but have to either reduce the data or use different Database. I have tested MySQL’s CLOB works well without issues.
Hope above information can be useful.
