Accessing MaxCompute Lightning with Java and Python for App Development
4 min readFeb 21, 2019
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-8import psycopg2
import 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": "<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
