使用sqoop迁移Oracle中blob到Trafodion

在前面文章中，我们介绍了使用sqoop把blob迁移到Hive，具体可参考博客使用sqoop迁移blob到Hive表。
由于Hive本身不支持LOB类型，我们使用String来存储BLOB数据，sqoop导入到HDFS中BLOB字段被存储为16进制的字符串形式。
数据在Hive中之后，我们便可以直接使用Trafodion自带的LOAD INTO命令把数据直接从Hive表加载到Trafodion表，如下
（注：我们也可以在load过程中直接把16进制字符串中的空格去除，使用replace函数把空格替换为空，因为这些空格不是有效信息，去除空格可以节省存储空间）

SQL>create table t_blob(a int, b varchar(10), c varchar(1000000));
SQL>load into t_blob select a,b,replace(c,' ','') as c from hive.hive.t_blob;
UTIL_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Task:  LOAD            Status: Started    Object: TRAFODION.SEABASE.T_BLOB                                                      
Task:  CLEANUP         Status: Started    Time: 2019-02-20 18:15:17.866553                                                      
Task:  CLEANUP         Status: Ended      Time: 2019-02-20 18:15:17.893927
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.027
Task:  LOADING DATA    Status: Started    Time: 2019-02-20 18:15:17.893976                                                      
       Rows Processed: 1 
       Error Rows:     0 
Task:  LOADING DATA    Status: Ended      Time: 2019-02-20 18:15:19.86087
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:01.192
Task:  COMPLETION      Status: Started    Time: 2019-02-20 18:15:19.86128                                                       
       Rows Loaded:    1 
Task:  COMPLETION      Status: Ended      Time: 2019-02-20 18:15:37.738476
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:18.652

--- SQL operation complete.

SQL>select a,b,length(c) from t_blob;

A           B          (EXPR)    
----------- ---------- ----------
          1 ABC            21333

--- 1 row(s) selected.

>>select a,b,substr(c,1,100) from t_blob;

A            B           (EXPR)
-----------  ----------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          1  ABC         ffd8ffe000104a46494600010001009600960000fffe001f4c45414420546563686e6f6c6f6769657320496e632e2056312e                                                                                                                                                                                                                                                                                                            

--- 1 row(s) selected

下面我们使用以下一段Java程序来验证字段C中的16进制串是否能还原为存储的图片。

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


public class TrafodionHex2Image {
	protected static String url = "jdbc:t4jdbc://10.10.12.25:23400/:";
	protected static String driverClass = "org.trafodion.jdbc.t4.T4Driver";
	protected static String userName = "trafodion";
	protected static String pwd = "traf123";
	protected static Connection conn;

	public static void main(String[] args) throws Exception {
		TrafodionHex2Image to = new TrafodionHex2Image();
		String s = null;
		
		//get hex string from trafodion, from blob
        try {
			Class.forName(driverClass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		conn = DriverManager.getConnection(url, userName, pwd);
		String sql = "select c from seabase.t_blob";
		PreparedStatement statement = conn.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		
		while (resultSet.next()) {
			s = resultSet.getString(1).replace(" ", "").toUpperCase();
			System.out.println(s);
			System.out.println(s.length());
		}
		//hex convert to image
		to.saveToImgFile(s, "c:/file/filename123.jpg");
		
		resultSet.close();
		statement.close();
	}

	public void saveToImgFile(String src, String output) {
		if (src == null || src.length() == 0) {
			return;
		}
		try {
			FileOutputStream out = new FileOutputStream(new File(output));
			byte[] bytes = src.getBytes();
			for (int i = 0; i < bytes.length - 1; i += 2) {
				out.write(charToInt(bytes[i]) * 16 + charToInt(bytes[i + 1]));
			}
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private int charToInt(byte ch) {
		int val = 0;
		if (ch >= 0x30 && ch <= 0x39) {
			val = ch - 0x30;
		} else if (ch >= 0x41 && ch <= 0x46) {
			val = ch - 0x41 + 10;
		}
		return val;
	}
}

验证后，转换的图片内容与原存入的图片完全一致。