阿里云数据源连接(ODPS、DRDS)

阿里云数据源连接(ODPS、DRDS)

一、ODPS

1.准备工作

1）阿里云账号的accessId和accessKey(几乎等同于账号)

2）ODPS连接所需API地址，公网ODPS统一为 http://service.odps.aliyun.com/api

专有云ODPS联系相关负责人员获取API地址。

3）ODPS中project名称(工作空间名称或称为库名)

4）官方提供ODPS的SDK开发包，maven依赖如下：

        <dependency>

            <groupId>com.aliyun.odps</groupId>

            <artifactId>odps-jdbc</artifactId>

            <version>3.0.1</version>

        </dependency>

 

2.获取连接

根据官方SDK提供的API获取ODPS连接对象。

    public static Odps getConn(String endPoint, String accessId, String accessKey, String project) {

        Account account = new AliyunAccount(accessId, accessKey);

        Odps odps = new Odps(account);

        odps.setEndpoint(endPoint);

        odps.setDefaultProject(project);

        return odps;

    }

 

3.获取表信息

根据官方SDK提供的API获取ODPS表中英文名称。

    public ArrayList<Map<String, String>> showTables(Odps stmt) {

        ArrayList<Map<String, String>> tablelist = new ArrayList<>();

        Tables tables = stmt.tables();

        for (Table table : tables) {

            Map<String, String> tableInfo = new HashMap<>();

            tableInfo.put("tableName", table.getName());

            tableInfo.put("remarks", table.getComment());

            tablelist.add(tableInfo);

        }

        return tablelist;

    }

 

3.获取字段信息

根据官方SDK提供的API获取ODPS表字段中英文名称。

    public ArrayList<Map<String, String>> showTableFields(Odps stmt, String tablename) throws OdpsException {

        ArrayList<Map<String, String>> tableFieldlist = new ArrayList<>();

        Table t = stmt.tables().get(tablename);

        t.reload();

        TableSchema schema = t.getSchema();

        List<Column> columns = schema.getColumns();

        for (Column column : columns) {

            Map<String, String> fieldInfo = new HashMap<>();

            fieldInfo.put("columnName", column.getName());

            fieldInfo.put("remarks", column.getComment());

            tableFieldlist.add(fieldInfo);

        }

        return tableFieldlist;

    }

 

4.获取表数据

ODPS支持类SQL，通过查询语句获取表数据(类似于JDBC方式)。

    public void getTableData(Odps stmt, String tablename) throws OdpsException {

        String sql = "select * from " + tablename + " limit 5;";

        Instance i = SQLTask.run(stmt, sql);

        i.waitForSuccess();

        List<Record> records = SQLTask.getResult(i);

        for (Record r : records) {

            String fielddata1 = r.getString(1);

            String fielddata = r.getString("name");

        }

    }

二、DRDS分布式数据库服务(中间件)

1.简介

基于MYSQL协议进行通信，连接方式同理于连接MYSQL，操作方式本质同MYSQL。

 

2.准备工作

1）MYSQL驱动，若版本不适更换驱动版本即可，maven依赖如下：

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>5.1.25</version>

        </dependency>

2）DRDS连接IP、端口、账号、密码

 

3.获取连接

参考MYSQL获取数据库连接方式。

JDBC获取数据库连接如下：

    public static Connection getConns(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {

        Connection conn = null;

        Class.forName(driver);

        conn = DriverManager.getConnection(url, username, password);

        return conn;

    }

 

3.获取表信息

参考MYSQL获取表方式。

JDBC获取表中英文名名称如下：

    public ArrayList<Map<String, String>> getTablesLists(Connection conn) throws SQLException {

        ArrayList<Map<String, String>> tablelist = new ArrayList<>();

        ResultSet rs = null;

        Statement stmt = conn.createStatement();

        DatabaseMetaData dbmd = conn.getMetaData();

        String[] types = { "TABLE" };

        rs = dbmd.getTables(null, null, "%", types);

        while (rs.next()) {

            Map<String, String> tableInfo = new HashMap<String, String>();

            String tableName = rs.getString("TABLE_NAME");

            tableInfo.put("tableName", tableName);// 表名

            ResultSet rstemp = stmt.executeQuery("SHOW CREATE TABLE `" + tableName + "`");

            if (rstemp != null && rstemp.next()) {

                String create = rstemp.getString(2);

                tableInfo.put("remarks", parse(create));// 注释

            }

            tablelist.add(tableInfo);

        }

        return tablelist;

    }

 

    public String parse(String all) {

        String comment = null;

        int index = all.indexOf("COMMENT='");

        if (index < 0) {

            return null;

        }

        comment = all.substring(index + 9);

        if (comment.contains("' dbpartition by")) {

            comment = comment.substring(0, comment.indexOf("' dbpartition by"));

        } else {

            comment = comment.substring(0, comment.indexOf("'"));

        }

        try {

            comment = new String(comment.getBytes("utf-8"));

        } catch (UnsupportedEncodingException e) {

            e.printStackTrace();

        }

        return comment;

    }

 

4.获取字段信息

参考MYSQL获取字段方式。

JDBC获取字段中英文名称如下：

    public ArrayList<Map<String, String>> getColumnsInfos(String tablename, Connection conn) throws SQLException {

        ArrayList<Map<String, String>> fieldList = new ArrayList<>();

        ResultSet rs = null;

        DatabaseMetaData dbmd = conn.getMetaData();

        rs = dbmd.getColumns(null, null, tablename, null);

        while (rs.next()) {

            Map<String, String> fieldInfo = new HashMap<String, String>();

            fieldInfo.put("columnName", rs.getString("COLUMN_NAME"));// 列名

            fieldInfo.put("remarks", rs.getString("REMARKS"));// 注释

            fieldList.add(fieldInfo);

        }

        return fieldList;

    }

5.获取表数据

参考MYSQL获取表数据方式。

JDBC获取表数据如下：

    private void getFieldData(String tablename, Connection conn) throws SQLException {

        String sql = "select * from `" + tablename + "` limit 5";

        Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {

            String fielddata1 = rs.getString(1);

            String fielddata = rs.getString("name");

        }

    }