Accessing MaxCompute Lightning with Java and Python for App Development

Join us at the Alibaba Cloud ACtivate Online Conference on March 5-6 to challenge assumptions, exchange ideas, and explore what is possible through digital transformation.

MaxCompute Lightning provides interactive query services for Alibaba Cloud MaxCompute, and supports easy connection to MaxCompute projects based on the PostgreSQL protocol and syntax. This service allows you to quickly query and analyze MaxCompute project data using standard SQL and commonly used tools.

Many developers want to use Lightning's features to develop data applications. This article will introduce examples showing how Java and Python connect to Lightning for application development (the endpoint and user authentication information of the region where your project is located needs to be replaced when referring to these examples in the article).

Using JDBC to Access Lightning with Java

Example:

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 = "<your_maxcompute_access_id>";
        String accessKey = "<your_maxcompute_access_key>";
        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;
    }
}

Using Druid to Access Lightning with Java

Pom dependency:

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.23</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.3-1101-jdbc4</version>
        </dependency>

Spring configuration:

    <bean id="LightningDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="jdbc:postgresql://lightning.cn-shanghai.maxcompute.aliyun.com:443/project_name? prepareThreshold=0&sslmode=require"/> <! -- Replace it with the endpoint of the region where your project is located—>
        <property name="username" value="Access Key ID of the user"/>
        <property name="password" value="Access Key Secret of the user"/>
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="dbType" value="postgresql"/>
        <property name="initialSize" value="1" />  
        <property name="minIdle" value="1" />
        <property name="maxActive" value="5" />  <!— The Lightning service limits the number of connections per project to 20, so do not set this value too high, and instead configure it as needed, otherwise, query_wait_timeout errors may occur -->
 
        <! -- The following two configurations are used to detect the validity of the connection and fix the occasional "create connection holder error" error. -->
        <property name="testWhileIdle" value="true" />
        <property name="validationQuery" value="SELECT 1" />
    </bean>

  <bean class="com.xxx.xxx.LightningProvider">
    <property name="druidDataSource" ref="LightningDataSource"/>
  </bean>

Code access:

public class LightningProvider {

    DruidDataSource druidDataSource;
    /**
     * execute 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);
            //The code for resolving and processing the return value
            //Processing by row. The data of each row is put into a map
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<LinkedHashMap> 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();
            }
        }
    }
}

Using pyscopg2 to Access Lightning with Python

Example:

#! /usr/bin/env python
# coding=utf-8

import psycopg2
import sys

def 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 result

if __name__ == "__main__":
    # step1. setup configuration
    lightning_conf = {
        "dbname": "your_project_name",
        "user": "<your_maxcompute_access_id>", 
        "password": "<your_maxcompute_access_key>", 
        "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])

Using ODBC to Access Lightning with Python

You need to install and configure an ODBC driver on your computer to use this method.

Sample code:

import pyodbc
conn_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 provides a PostgreSQL-compatible interface, so you can develop Lightning applications just like PostgreSQL applications.

Learn more about Alibaba Cloud MaxCompute at https://www.alibabacloud.com/product/maxcompute

Community

Accessing MaxCompute Lightning with Java and Python for App Development

Using JDBC to Access Lightning with Java

Using Druid to Access Lightning with Java

Using pyscopg2 to Access Lightning with Python

Using ODBC to Access Lightning with Python

Read previous post:

Read next post:

Alibaba Cloud MaxCompute

You may also like

Comments

Alibaba Cloud MaxCompute

Related Products

MaxCompute

Big Data Consulting for Data Technology Solution

Big Data Consulting Services for Retail Solution

DataWorks