在前面文章中,我们介绍了使用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;
}
}
验证后,转换的图片内容与原存入的图片完全一致。