怎样使用应用程序访问MaxCompute Lightning进行数据开发

这篇文章将为大家详细讲解有关怎样使用应用程序访问MaxCompute Lightning进行数据开发，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

MaxCompute Lightning是MaxCompute产品的交互式查询服务，支持以PostgreSQL协议及语法连接访问Maxcompute项目，让您使用熟悉的工具以标准 SQL查询分析MaxCompute项目中的数据，快速获取查询结果。
很多开发者希望利用Lightning的特性来开发数据应用，小编将结合示例来介绍Java和Python如何连接访问Lightning进行应用开发(参考时需要替换为您项目所在region的Endpoint及用户认证信息)。
一、Java使用JDBC访问Lightning
示例如下：

import java.sql.*;public class Main {    private static Connection connection;    public static void main(String[] args) throws SQLException {        String url = "jdbc:postgresql://lightning.cn-shanghai.maxcompute.aliyun.com:443/your_project_name?prepareThreshold=0&sslmode=require";        String accessId = "";        String accessKey = "";        String sql = "select * from dual";        try {            Connection conn = getCon(url, accessId, accessKey);            Statement st = conn.createStatement();            System.out.println("Send Lightning query");            ResultSet rs = st.executeQuery(sql);            while (rs.next()) {                System.out.println(rs.getString(1)+ "\t");            }            System.out.println("End Lightning query");            conn.close();        } catch (SQLException e) {            e.printStackTrace();        }    }    public static Connection getCon(String lightningsHost, String lightningUser, String lightningPwd) {        try {            if (connection == null || connection.isClosed()) {                try {                    Class.forName("org.postgresql.Driver").newInstance();                    DriverManager.setLoginTimeout(1);                    connection = DriverManager.getConnection(lightningsHost, lightningUser, lightningPwd);                } catch (Exception ex) {                    ex.printStackTrace();                }            }        } catch (Exception ex) {            ex.printStackTrace();        }        return connection;    }}

二、Java使用druid访问Lightning
1.pom依赖

            com.alibaba            druid            1.0.23                            org.postgresql            postgresql            9.3-1101-jdbc4        

2.spring配置

3.代码访问

public class LightningProvider {    DruidDataSource druidDataSource;    /**     * 执行sql     * @param sql     * @return     * @throws Exception     */    public void execute(String sql) throws SQLException {        DruidPooledConnection connection = null ;        Statement st = null;        try{            connection = druidDataSource.getConnection();            st = connection.createStatement();            ResultSet resultSet = st.executeQuery(sql);            //对返回值的解析和处理的代码            //按行处理，每行的数据放到一个map中            ResultSetMetaData metaData = resultSet.getMetaData();            int columnCount = metaData.getColumnCount();            List rows = Lists.newArrayList();            while(resultSet.next()){            LinkedHashMap map = Maps.newLinkedHashMap();            for(int i=1;i<=columnCount;i++){                String label = resultSet.getMetaData().getColumnLabel(i);                map.put(label,resultSet.getString(i));            }            rows.add(map);        }           }catch (Exception e){             e.printStackTrace();        }finally {            try {                if(st!=null) {                    st.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if(connection!=null) {                    connection.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

三、Python使用pyscopg2访问Lightning
示例如下：

#!/usr/bin/env python# coding=utf-8import psycopg2import sysdef query_lightning(lightning_conf, sql):    """Query data through Lightning by sql    Args:        lightning_conf: a map contains settings of 'dbname', 'user', 'password', 'host', 'port'        sql:  query submit to Lightning    Returns:        result: the query result in format of list of rows    """    result = None    conn = None    conn_str = None    try:        conn_str = ("dbname={dbname} "                    "user={user} "                    "password={password} "                    "host={host} "                    "port={port}").format(**lightning_conf)    except Exception, e:        print >> sys.stderr, ("Invalid Lightning' configuration "                       "{}".format(e))        sys.exit(1)    try:        conn = psycopg2.connect(conn_str)        conn.set_session(autocommit=True) # This will disable transaction                                   # started with keyword BEGIN,                                   # which is currently not                                   # supported by Lightning' public service        cur = conn.cursor()        # execute Lightning' query        cur.execute(sql)        # get result        result = cur.fetchall()    except Exception, e:        print >> sys.stderr, ("Failed to query data through "                       "Lightning: {}".format(e))    finally:        if conn:            conn.close()    return resultif __name__ == "__main__":    # step1. setup configuration    lightning_conf = {        "dbname": "your_project_name",        "user": "",         "password": "",         "host": "lightning.cn-shanghai.maxcompute.aliyun.com",  #your region lightning endpoint        "port": 443    }    # step2. issue a query    result = query_lightning(lightning_conf, "select * from test")    # step3. print result    if result:        for i in xrange(0, len(result)):            print "Got %d row from Lightning:%s" % (i + 1, result[i])

四、Python使用ODBC访问Lightning
您需要现在电脑上安装并和配置odbc驱动。代码示例如下：

import pyodbcconn_str = (    "DRIVER={PostgreSQL Unicode};"    "DATABASE=your_project_name;"    "UID=your_maxcompute_access_id;"    "PWD=your_maxcompute_access_key;"    "SERVER=lightning.cn-shanghai.maxcompute.aliyun.com;" #your region lightning endpoint    "PORT=443;"    )conn = pyodbc.connect(conn_str)crsr = conn.execute("SELECT * from test")row = crsr.fetchone()print(row)crsr.close()conn.close()

由于Lightning提供了PostgreSQL兼容的接口，您可以像开发PostgreSQL的应用一样开发Lightning应用程序。

关于怎样使用应用程序访问MaxCompute Lightning进行数据开发就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。