package com.xjr7670;
import com.aliyun.odps.*;
import com.aliyun.odps.account.Account;
import com.aliyun.odps.account.AliyunAccount;
import com.aliyun.odps.type.TypeInfo;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Array;
import java.util.*;
/**
* Hello world!
*
*/
public class GetOdpsData
{
private static final String accessId = "你的 accessId";
private static final String accessKey = "你的 accessKey";
private static final String project = "项目名";
private static final String endpoint = "http://service.cn-shenzhen.maxcompute.aliyun.com/api";
public static void main(String[] args) {
Map<String, ArrayList<ArrayList<String>>> data = getData();
write2excel(data);
}
private static void write2excel(Map<String, ArrayList<ArrayList<String>>> mapData) {
/**
* 写入到 Excel 中
*/
String resultExcelPath = "e:/temp/odps_table.xlsx";
try {
System.out.println("正在写入 Excel...");
FileOutputStream fos = new FileOutputStream(resultExcelPath);
HSSFWorkbook wbk = new HSSFWorkbook();
Set<String> keys = mapData.keySet();
List<String> sortedNames = new ArrayList<String>(keys);
Collections.sort(sortedNames, new Comparator<String>() { // 排个序,把 ods 的表放前面,dwd 的表放后面
@Override
public int compare(String o1, String o2) {
String o1Pre3 = o1.substring(0, 3);
String o2Pre3 = o2.substring(0, 3);
String o1Sub = o1.substring(4);
String o2Sub = o2.substring(4);
if (o1Pre3.compareTo(o2Pre3) == 0 ) {
// 相同的 ods/dwd 表,比较 _ 后面的字符
if (o1Sub.compareTo(o2Sub) > 0) {
return 1;
} else {
return -1;
}
} else {
return o2.compareTo(o1);
}
}
});
int sheetIndex = 0;
for (String tbName : sortedNames) {
ArrayList<ArrayList<String>> colLists = mapData.get(tbName);
HSSFSheet sheet = wbk.createSheet();
wbk.setSheetName(sheetIndex++, tbName);
int r = 0;
HSSFRow titleRow = sheet.createRow(r++);
titleRow.createCell(0).setCellValue("字段名");
titleRow.createCell(1).setCellValue("字段类型");
titleRow.createCell(2).setCellValue("字段注释");
for (ArrayList<String> row : colLists) {
int col = 0;
String colName = row.get(0);
String colComment = row.get(1);
String colType = row.get(2);
HSSFRow sheetRow = sheet.createRow(r++);
sheetRow.createCell(col++).setCellValue(colName);
sheetRow.createCell(col++).setCellValue(colType);
sheetRow.createCell(col).setCellValue(colComment);
}
fos.flush();
}
wbk.write(fos);
fos.close();
System.out.println("Excel 写入成功!");
} catch (FileNotFoundException e) {
System.out.println("文件不存在。");
e.printStackTrace();
} catch (IOException e) {
System.out.println("IOException");
e.printStackTrace();
}
}
public static Map<String, ArrayList<ArrayList<String>>> getData() {
Account account = new AliyunAccount(accessId, accessKey);
Odps odps = new Odps(account);
odps.setEndpoint(endpoint);
odps.setDefaultProject(project);
Map<String, ArrayList<ArrayList<String>>> data = new HashMap<>();
System.out.println("正在获取 ODPS 表结构...");
for (Table t: odps.tables()) {
TableSchema schema = t.getSchema();
String tName = t.getName();
if (tName.startsWith("ods") || tName.startsWith("dwd")) { // 只取 ods 和 dwd 层的表
System.out.println("\t" + tName);
ArrayList<ArrayList<String>> list = new ArrayList<>();
List<Column> columns = schema.getColumns();
for (Column col: columns) {
ArrayList<String> tmpList = new ArrayList<>();
String colName = col.getName();
String colComment = col.getComment();
TypeInfo typeInfo = col.getTypeInfo();
String colType = typeInfo.getTypeName();
tmpList.add(colName);
tmpList.add(colComment);
tmpList.add(colType);
list.add(tmpList);
}
data.put(tName, list);
}
}
System.out.println("ODPS 数据获取成功,开始写入...");
return data;
}
}