使用 Java SDK 获取 MaxCompute 的表结构并写入到 Excel 中

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;
    }
}