• Stars
    star
    125
  • Rank 286,335 (Top 6 %)
  • Language
    Java
  • License
    Other
  • Created about 9 years ago
  • Updated about 1 month ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

JDBC Driver for ODPS

ODPS JDBC

build Maven Central

Chinese Docs

MaxCompute JDBC介绍

Installation

Generally, there are two ways to use ODPS JDBC driver in your project.

1.The first one is to use the standalone library:

2.The second is to rely on maven to resolve the dependencies for you:

<dependency>
  <groupId>com.aliyun.odps</groupId>
  <artifactId>odps-jdbc</artifactId>
  <version>VERSION</version>
</dependency>

Getting Started

Using ODPS JDBC driver is just as using other JDBC drivers. It contains the following few steps:

1. Explictly load the ODPS JDBC driver using Class.forName():

Class.forName("com.aliyun.odps.jdbc.OdpsDriver");

2. Connect to the ODPS by creating a Connection object with the JDBC driver:

Connection conn = DriverManager.getConnection(url, accessId, accessKey);

The ODPS server works with RESTful API, so the url looks like:

String url = "jdbc:odps:ENDPOINT?project=PROJECT_NAME&charset=UTF-8";

The connection properties can also be passed through Properties. For example:

Properties config = new Properties();
config.put("access_id", "...");
config.put("access_key", "...");
config.put("project_name", "...");
config.put("charset", "...");
Connection conn = DriverManager.getConnection("jdbc:odps:<endpoint>", config);

3. Submit SQL to ODPS by creating Statement object and using its executeQuery() method:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT foo FROM bar");

4. Process the result set.

For example:

while (rs.next()) {
    ...
}

Connection String Parameters

It is recommended that the key and value in URL should be encoded by using java.net.URLEncoder#encode(java.lang.String).

Basic

URL key Property Key Required Default value Description
endpoint end_point True The endpoint of your MaxCompute service
project project_name True The name of your MaxCompute project
accessId access_id True Your Alibaba Cloud access key ID
accessKey access_key True Your Alibaba Cloud access key secret
interactiveMode interactive_mode False false For MCQA, enable MCQA
logview logview_host False Provided by MC The endpoint of MaxCompute Logview
tunnelEndpoint tunnel_endpoint False Provided by MC The endpoint of the MaxCompute Tunnel service
enableOdpsLogger enable_odps_logger False false Enable MaxCompute JDBC logger

Advanced

URL key Property Key Required Default value Description
stsToken sts_token False The Alibaba Cloud STS token
logConfFile log_conf_file False The configuration path for SLF4J
charset charset False UTF-8 The charset of the inputs and outputs
executeProject execute_project_name False For MCQA, the name of the MaxCompute project in which actually execute the queries
alwaysFallback always_fallback False false For MCQA, fall back to regular mode if any exception happened
instanceTunnelMaxRecord instance_tunnel_max_record False -1 (unlimited) For MCQA, max number of records within a result set, enableLimit option should set to false
instanceTunnelMaxSize instance_tunnel_max_size False -1 (unlimited) For MCQA, max size of a result set in byte
enableLimit enable_limit False true(limited) For MCQA, download permission won't be checked if enableLimit is set true, but your result record count will be limited to 10000
autoLimitFallback auto_limit_fallback False False(no auto fallback) For non-MCQA mode, result record count will be limited to 10000 when no download permission exception happened and autoLimitFallback is set to true

Example

JDBC Client Sample Code

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class OdpsJdbcClient {
  private static String driverName = "com.aliyun.odps.jdbc.OdpsDriver";

  /**
   * @param args
   * @throws SQLException
   */
  public static void main(String[] args) throws SQLException {
    try {
      Class.forName(driverName);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
      System.exit(1);
    }

    // fill in the information here
    String accessId = "your_access_id";
    String accessKey = "your_access_key";
    Connection conn = DriverManager.getConnection("jdbc:odps:https://service.odps.aliyun.com/api?project=<your_project_name>", accessId, accessKey);
    Statement stmt = conn.createStatement();
    String tableName = "testOdpsDriverTable";
    stmt.execute("drop table if exists " + tableName);
    stmt.execute("create table " + tableName + " (key int, value string)");

    String sql;
    ResultSet rs;

    // insert a record
    sql = String.format("insert into table %s select 24 key, 'hours' value from (select count(1) from %s) a", tableName, tableName);
    System.out.println("Running: " + sql);
    int count = stmt.executeUpdate(sql);
    System.out.println("updated records: " + count);

    // select * query
    sql = "select * from " + tableName;
    System.out.println("Running: " + sql);
    rs = stmt.executeQuery(sql);
    while (rs.next()) {
      System.out.println(String.valueOf(rs.getInt(1)) + "\t" + rs.getString(2));
    }

    // regular query
    sql = "select count(1) from " + tableName;
    System.out.println("Running: " + sql);
    rs = stmt.executeQuery(sql);
    while (rs.next()) {
      System.out.println(rs.getString(1));
    }

    // do not forget to close
    stmt.close();
    conn.close();
  }
}

Running the JDBC Sample Code

# compile the client code
mvn clean package -DskipTests

# run the program with specifying the class path
# using prepared shell script (linux)
./jdbc_test.sh 'jdbc:odps:http://service.odps.aliyun.com/api?project=odpsdemo&accessId=...&accessKey=...&charset=UTF-8&logconffile=logback/logback.xml' 'select * from dual'

# using java command
java -cp "target/odps-jdbc-2.2-jar-with-dependencies.jar:logback/logback-core-1.2.3.jar:logback/logback-classic-1.2.3.jar" com.aliyun.odps.jdbc.JdbcTest "jdbc:odps:http://service.odps.aliyun.com/api?project=odpsdemo&accessId=...&accessKey=...&charset=UTF-8&logconffile=logback/logback.xml" "select * from dual"

Setting SQL task properties

stmt.execute("set biz_id=xxxxxx");
stmt.execute("set odps.sql.mapper.split.size=512");

Third-party Integration

It is also recommended to use ODPS by using other third-party BI tools or DB visualizer that supports JDBC.

For example:

Getting Involved

The project is under construction (and not fully JDBC-compliant). If you dicover any good features which have not been implemented, please fire me an Email or just pull a request.

Architecture

Build and run unitest

1.Build from source locally:

git clone ....
cd odps-jdbc
mvn package -DskipTests

2.Copy out a configuration file:

cp ./src/test/resources/conf.properties.example ./src/test/resources/conf.properties

3.Fill in your connection strings:

access_id=...
access_key=...
end_point=...
project_name=...
logview_host=...
charset=UTF-8

4.Run maven test command (or just test it in IntelliJ IDEA):

mvn test

Data Type Mapping

Currently, 13 ODPS data types are supported. Please see the following table for supported ODPS data types and corresponding JDBC interfaces.

ODPS Type JDBC Interface JDBC Type
TINYINT java.sql.ResultSet.getByte TINYINT
SMALLINT java.sql.ResultSet.getShort SMALLINT
INT java.sql.ResultSet.getInt INTEGER
BIGINT java.sql.ResultSet.getLong BIGINT
FLOAT java.sql.ResultSet.getFloat FLOAT
DOUBLE java.sql.ResultSet.getDouble DOUBLE
BOOLEAN java.sql.ResultSet.getBoolean BOOLEAN
DATETIME java.sql.ResultSet.getTimestamp TIMESTAMP
TIMESTAMP java.sql.ResultSet.getTimestamp TIMESTAMP
VARCHAR java.sql.ResultSet.getString VARCHAR
STRING java.sql.ResultSet.getString VARCHAR
DECIMAL java.sql.ResultSet.getBigDecimal DECIMAL
BINARY java.sql.ResultSet.getBytes BINARY

NOTE: Possible timezone issue

DATETIME in MaxCompute is actually defined as EPOCH in milliseconds, which is UTC, and so is TIMESTAMP in JDBC. This driver fill the DATETIME value directly into JDBC TIMESTAMP and do no parse or format action. When application that using JDBC display a DATETIME as a human-readable string format, it is the application itself did the format using application defined or OS defined timezone. It is suggested to keep your application/OS timezone setting same to MaxCompute to avoid inconsistent datetime parse/format.

Type Conversion

Implicit type conversion happens when accessing a ODPS data type with JDBC interfaces other than the recommended one. Please see the following table for supported implicit conversions.

JAVA\ODPS TINYINT SMALLINT INT BIGINT FLOAT DOUBLE DECIMAL VARCHAR STRING DATETIME TIMESTAMP BOOLEAN BINARY
byte Y Y Y Y Y Y Y
short Y Y Y Y Y Y Y Y
int Y Y Y Y Y Y Y Y
long Y Y Y Y Y Y Y Y
float Y Y Y Y Y Y Y Y
double Y Y Y Y Y Y Y Y
BigDecimal Y
String Y Y Y Y Y Y Y Y Y Y Y Y
byte[] Y Y Y Y Y Y Y Y Y Y Y Y Y
Date Y Y Y
Time Y Y Y
Timestamp Y Y Y
boolean Y Y Y Y Y Y Y Y Y

MaxCompute Service Compatibility and Recommended JDBC version

Since Sprint27, MaxCompute tunnel service supported a feature named instance tunnel that allowing client read query result set through tunnel endpoint, to release client from creating temporary table. And this JDBC driver began adopt using instance tunnel since version 2.0.

However, for users using MaxCompute deploy that is earlier than Sprint27 (especially Private Cloud cases), please stick to the latest version before 2.0.

MaxCompute JDBC
Public Service latest
Non PRC Public Service latest
<= Sprint27 1.9.2

Authors && Contributors

License

licensed under the Apache License 2.0

More Repositories

1

oss-browser

OSS Browser 提供类似windows资源管理器功能。用户可以很方便的浏览文件,上传下载文件,支持断点续传等。
JavaScript
3,175
star
2

aliyun-openapi-java-sdk

Alibaba Cloud SDK for Java
Java
1,379
star
3

aliyun-oss-java-sdk

Aliyun OSS SDK for Java
Java
1,216
star
4

alibaba-cloud-sdk-go

Alibaba Cloud SDK for Go
Go
1,104
star
5

alicloud-android-demo

Java
990
star
6

aliyun-openapi-python-sdk

Alibaba Cloud SDK for Python
Python
980
star
7

aliyun-oss-php-sdk

Aliyun OSS SDK for PHP
PHP
975
star
8

aliyun-oss-go-sdk

Aliyun OSS SDK for Go
Go
951
star
9

aliyun-oss-python-sdk

Aliyun OSS SDK for Python
Python
935
star
10

darabonba

Darabonba 是一种用于 OpenAPI 的 DSL 语言,可以用来生成多语言的 SDK、Code Sample、Test Case 等代码
JavaScript
894
star
11

alibabacloud-alfa

阿里云微前端解决方案
TypeScript
845
star
12

aliyun-oss-android-sdk

Android SDK for aliyun object storage service
Java
793
star
13

aliyun-cli

Alibaba Cloud CLI
Go
770
star
14

ossfs

Export s3fs for aliyun oss.
C++
735
star
15

aliyun-openapi-php-sdk

[Abandoned] Open API SDK for PHP developers
PHP
605
star
16

terraform-provider-alicloud

Terraform AliCloud provider
Go
590
star
17

aliyun-openapi-net-sdk

Alibaba Cloud SDK for .NET
C#
535
star
18

rds_dbsync

围绕 PostgreSQL Greenplum ,实现易用的数据的互迁功能项目
C
528
star
19

openapi-sdk-php

Alibaba Cloud SDK for PHP
PHP
501
star
20

iotkit-embedded

高速镜像: https://code.aliyun.com/linkkit/c-sdk
C
492
star
21

ossutil

A user friendly command line tool to access AliCloud OSS.
Go
456
star
22

aliyun-oss-ios-sdk

iOS SDK for aliyun object storage service
Objective-C
450
star
23

aliyun-odps-python-sdk

ODPS Python SDK and data analysis framework
Python
433
star
24

alicloud-ios-demo

Demos for AMS iOS SDKs
Objective-C
431
star
25

alibabacloud-microservice-demo

An Alibaba Cloud native microservice demo powered by Apache Dubbo and Spring Cloud Alibaba
Java
379
star
26

api-gateway-demo-sign-java

aliyun api gateway request signature demo by java
Java
371
star
27

NeWCRFs

Python
365
star
28

aliyun-oss-csharp-sdk

Aliyun OSS SDK for C#
C#
360
star
29

surftrace

surftrace is a tool that allows you to surf the linux kernel
Python
332
star
30

conditional-lane-detection

Python
328
star
31

aliyun-log-jaeger

Go
294
star
32

coolbpf

C
240
star
33

tablestore-examples

Example code for aliyun tablestore.
Java
238
star
34

tablestore-timeline

TableStore-Timeline Model for Social scene
Java
236
star
35

aliyun-log-c-sdk

Aliyun LOG Producer for C/C++
C
215
star
36

openapi-sdk-php-client

Official repository of the Alibaba Cloud Client for PHP
PHP
214
star
37

aliyun-log-logback-appender

Java
186
star
38

aliyun-log-android-sdk

Java
179
star
39

alibabacloud-jindodata

alibabacloud-jindodata
176
star
40

openapi-core-nodejs-sdk

OpenAPI POP core SDK for Node.js
JavaScript
175
star
41

aliyun-emapreduce-datasources

Extended datasource support for Spark/Hadoop on Aliyun E-MapReduce.
Scala
168
star
42

aliyun-log-python-sdk

Use python to manage, produce and consume data with Aliyun Log Service.
Python
166
star
43

data-mapping-component

A React Component which focus on Data-Mapping & Table-Field-Mapping.(基于React的数据/表字段映射组件)
JavaScript
155
star
44

aliyun-oss-react-native

Objective-C
148
star
45

aliyun-apsaradb-hbase-demo

C++
146
star
46

django-oss-storage

Django storage backends for AliCloud OSS.
Python
144
star
47

aliyun-oss-c-sdk

Aliyun OSS SDK for C
C
144
star
48

react-visual-modeling

A DAG React Component for visualization modeling, suitable for UML, database modeling, data warehouse construction.(一个基于React的数据可视化建模的DAG图,适用于UML,数据库建模,数据仓库建设等业务)
JavaScript
138
star
49

aliyun-oss-ruby-sdk

Aliyun OSS SDK for Ruby
Ruby
138
star
50

ram-policy-editor

AliCloud RAM Policy Editor for OSS
JavaScript
136
star
51

aliyun-log-java-sdk

Java
135
star
52

serverless-aliyun-function-compute

Serverless Alibaba Cloud Function Compute Plugin – Add Alibaba Cloud Function Compute support to the Serverless Framework
JavaScript
134
star
53

alibabacloud-console-components

阿里云企业云管理平台 UI 组件库
TypeScript
133
star
54

aliyun-log-java-producer

Aliyun LOG Java Producer
Java
131
star
55

fc-nodejs-sdk

The Node.js SDK of FunctionCompute.
JavaScript
130
star
56

aliyun-cms-grafana

JavaScript
127
star
57

alibabacloud-quantization-networks

alibabacloud-quantization-networks
Python
122
star
58

aliyun-emapreduce-demo

Java
121
star
59

aliyun-maxcompute-data-collectors

Java
119
star
60

alicloud-ams-demo

C#
117
star
61

alibabacloud-iot-device-sdk

alibaba cloud for iot device javascript SDK , connect with linkplatform , run at node/broswer/winxin min program /ali min program. 阿里云IoT物联网平台javascript版本sdk,可以运行在node/broswer/winxin min program /ali min program. 阿里云IoT物联网平台javascript版本sdk,可以运行在node/broswer/winxin min program /ali min program
JavaScript
110
star
62

MaxCompute-Spark

MaxCompute spark demo for building a runnable application.
Scala
106
star
63

api-gateway-nodejs-sdk

The API Gateway SDK for Node.js
JavaScript
104
star
64

cloud-design

阿里云前端组件库,由专有云&公有云前端团队共建
CSS
99
star
65

gm-jsse

开源国密通信纯 Java JSSE 实现
Java
95
star
66

aliyun-odps-console

ODPS Console Source Code.
Java
93
star
67

aliyun-openapi-cpp-sdk

Alibaba Cloud SDK for C++
C++
90
star
68

aliyun-odps-java-sdk

ODPS SDK for Java Developers
Java
89
star
69

aliyun-tablestore-nodejs-sdk

Aliyun TableStore(原OTS) SDK for Node.js
JavaScript
88
star
70

algorithm-base

让算法工程化更简单
Python
86
star
71

aliyun-log-ios-sdk

Aliyun LOG iOS SDK
Swift
84
star
72

iotx-api-demo

PHP
82
star
73

plugsched

Live upgrade Linux kernel scheduler subsystem
Python
82
star
74

DCT-Mask

Python
81
star
75

aliyun-openapi-nodejs-sdk

Alibaba Cloud SDK for Node.js
JavaScript
80
star
76

aliyun-specs

Aliyun Mobile Service CocoaPods specs.
Ruby
77
star
77

alibabacloud-console-design

阿里云管平台研发解决方案
TypeScript
77
star
78

alibabacloud-redis-training-demo

Java
76
star
79

aliyun-oss-php-sdk-laravel

A Laravel service provider for the AliCloud OSS SDK for PHP
PHP
75
star
80

aliyun-tablestore-go-sdk

TableStore SDK for Golang
Go
75
star
81

alibabacloud-sdk

Tea
75
star
82

fc-docker

Dockerfiles for local building or running function of FC
Dockerfile
74
star
83

elasticsearch-repository-oss

Java
74
star
84

dro-sfm

Python
74
star
85

packagist-mirror

Alibaba Cloud Packagist Mirror
Go
73
star
86

react-monitor-dag

A React-based operation/monitoring DAG diagram.(基于React的运维/监控DAG图)
JavaScript
69
star
87

aliyun_assist_client

Aliyun Assist Client 阿里云 云助手
Go
67
star
88

aliyun-log-php-sdk

PHP
67
star
89

alibabacloud-hologres-connectors

alibabacloud-hologres-connectors
Java
66
star
90

aliyun-tsdb-java-sdk

Aliyun TSDB SDK for Java
Java
64
star
91

aliyun-log-log4j-appender

aliyun-log-log4j-appender
Java
63
star
92

fc-java-sdk

The Java SDK of FunctionCompute.
Java
61
star
93

oss-ftp

The ftp proxy for Aliyun OSS.
Python
61
star
94

react-lineage-dag

JavaScript
61
star
95

aliyun-log-cli

Command Line Interface for Aliyun Log Service
Python
60
star
96

csb-sdk

The CSB-SDK is a client-side invocation SDK for HTTP or Web Service API opened by the CSB (Cloud Service Bus) product. It is responsible for invoking the open API and signing the request information.
Java
58
star
97

ossimport

Data migration tool
58
star
98

aliyun-log-flink-connector

flink log connector
Java
58
star
99

oss-emulator

OSS Emulator
Ruby
58
star
100

aliyun-log-dotnetcore-sdk

C#
55
star