sqoop导出hive数据到oracle

淳于夏萱

语句：
   sqoop  export --table tableName  -connect jdbc:oracle:thin:@xx.xx.xx.xx:port:xxx  --username userNameA --password pwd     --export-dir sourceFile  --input-fields-terminated-by '\001'      --input-lines-terminated-by '\n'

sqoop导出数据到oracle：报错：
java.lang.IllegalArgumentException: Attempted to generate class with no columns!
at org.apache.sqoop.orm.ClassWriter.generateClassForColumns(ClassWriter.java:1295)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1176)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:64)
at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
Failing Oozie Launcher, Main class [org.apache.oozie.action.hadoop.ShellMain], exit code [1]
*****************
2013-08-29 12:18:17,217 INFO org.apache.sqoop.manager.SqlManager: Executing SQL statement: SELECT t.* FROM TMP t WHERE 1=0
2013-08-29 12:18:17,235 ERROR org.apache.sqoop.Sqoop: Got exception running Sqoop: java.lang.IllegalArgumentException: Attempted to generate class with no columns!

查看Sqoop源码发现这么一段：
此异常是在没有找到目标表的column时抛出：
private StringBuilder generateClassForColumns(
Map<String, Integer> columnTypes,
String [] colNames, String [] dbWriteColNames) {
if (colNames.length ==0) {
throw new IllegalArgumentException("Attempted to generate class with "
+ "no columns!");
}

再查导致获取columns为空的原因，发现columns的获取是通过如下代码：
String [] colNames = options.getColumns();
if (null == colNames) {
if (null != tableName) {
// Table-based import. Read column names from table.
colNames = connManager.getColumnNames(tableName);
} else if (options.getCall() != null) {
// Read procedure arguments from metadata
colNames = connManager.getColumnNamesForProcedure(
this.options.getCall());
} else {
// Infer/assign column names for arbitrary query.
colNames = connManager.getColumnNamesForQuery(
this.options.getSqlQuery());
}
}

可以发现，它是先找参数columns的值，如果没有再去通过table去查。
通过table查寻是通过该table的owner去查：
见org.apache.sqoop.manager.OracleManager.java
中getColumnNames()方法：
  conn = getConnection();
pStmt = conn.prepareStatement(QUERY_COLUMNS_FOR_TABLE,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
pStmt.setString(1, tableOwner);
pStmt.setString(2, shortTableName);
rset = pStmt.executeQuery();
其主要查询语句为：
public static final String QUERY_COLUMNS_FOR_TABLE =
"SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE "
+ "OWNER = ? AND TABLE_NAME = ? ORDER BY COLUMN_ID";

在我的sqoop语句中并没有指定columns的值，它会理所当然的去查table的columns。
而我的table是同义词，其属主用户并非userNameA。
这下解决方式很明显了，两种：
1.将user替换成table的属主用户。
2.添加--columns属性，显示的指定columns的值。