Connect to Dataphin by using JDBC

This topic explains how to connect to Dataphin by using a jdbc driver.

Prerequisites

The Dataphin jdbc driver uses Dataphin OpenAPI to provide SQL query capabilities. You must enable the Dataphin OpenAPI feature before you use the Dataphin jdbc driver.

Overview

Two authentication modes: The Dataphin jdbc driver supports two authentication modes: simple mode and proxy mode. For more information, see Authentication modes.
Consistent execution results: SQL queries executed with the Dataphin jdbc driver produce the same results as queries run in the Dataphin console. Dataphin configurations, such as permissions, desensitization rules, security settings, and code specifications, also apply to queries executed through the jdbc driver.

Limitations

You can connect to Dataphin by using JDBC for all compute sources and some data sources. Supported data sources include MySQL, Oracle, MaxCompute, Hive, AnalyticDB for MySQL 3.0, StarRocks, AnalyticDB for PostgreSQL, and Doris.
The Dataphin jdbc driver adds performance overhead. The driver sends queries to Dataphin for preprocessing tasks like SQL translation and data desensitization. Dataphin then transfers and forwards the result sets. This process can increase query latency compared to querying a compute engine directly.
The Dataphin jdbc driver submits SQL queries to Dataphin through OpenAPI, and the queries are then executed by the underlying compute engine. Before you use the jdbc driver, evaluate your expected call volume to determine if you need to scale up your Dataphin cluster or compute engine.
Note
If you cannot perform this evaluation, contact the Dataphin operations team to request a capacity assessment for your Dataphin cluster. This assessment does not cover the capacity of your compute engine.
Dataphin does not currently support traffic control or concurrency control. Carefully assess the potential impact before you proceed.
Only the following compute engines support acceleration sources: MaxCompute. You can specify an acceleration source and its resource group in the Dataphin JDBC connection only if the acceleration source is already configured in Dataphin. For more information, see Acceleration source.

JDBC driver version and download link

Click here to download the driver JAR package.

Note

The current JAR package supports only Dataphin V4.4 and later. Later driver versions are not compatible with earlier Dataphin versions.

Connection parameters

Note

When Dataphin JDBC uses a platform AccessKey ID and AccessKey Secret (in proxy mode), you can use the set syntax to set the access user.

set dp_delegation_uid = 'target_user_uid';
set dp_delegation_name = 'target_user_name';

JDBC URL format: jdbc:dataphin://host:port/catalog?[tenant_id=TenantID][&ssl=true][&log_level=Log_Level][&user=UserName][&password=PassWord][&delegation_uid=DelegationUid][&account_type=AccountType][&connect_timeout=ConnectTimeout][&engine=engine_type][compute_project=ProjectName]

Important

Parameter names are case-sensitive.
The brackets are for illustrative purposes only. Remove them when you construct the URL.

Parameter	Required	Description	Example
host	Yes	The endpoint for Dataphin OpenAPI. Each region has a different endpoint. Select the endpoint that corresponds to the region where your Dataphin instance is located. China (Beijing): `dataphin-public.cn-beijing.aliyuncs.com` China (Hangzhou): `dataphin-public.cn-hangzhou.aliyuncs.com` China (Shanghai): `dataphin-public.cn-shanghai.aliyuncs.com` China (Shenzhen): `dataphin-public.cn-shenzhen.aliyuncs.com`	dataphin-public.cn-hangzhou.aliyuncs.com
port	No	The port is determined by whether HTTPS is enabled for Dataphin OpenAPI. The port is `443` if HTTPS is enabled, or `80` if it is not. The default is 80.	80
catalog	Yes	The default scope for data queries. To use a Dataphin physical table, enter the English name of the Dataphin project (project_name). If you use a Dataphin logical table, enter the English name of the Dataphin data board (starting with `LD_`). To use a data source table managed by Dataphin, enter the data source code configured in Dataphin (starting with `ds_`). Note If the default query scope is a board, the compute_project parameter is required. If the default query scope is a table from a data source managed by Dataphin, you do not need to specify the compute_project parameter.	Exprojectname
tenant_id	Yes	The ID of the tenant to be queried.	111***111
ssl	No	Indicates whether to use HTTPS. Valid values: True: Use an HTTPS domain name. False: Use an HTTP domain name. This is the default value.	False
currentschema	No	The schema of the data source. If the catalog parameter specifies a Dataphin physical or logical table, you do not need to set this parameter. If the catalog parameter specifies a data source without schema support, such as MySQL, you do not need to set this parameter. If the catalog parameter specifies a data source that supports schemas, such as Oracle, this parameter is optional. If you specify a schema, data from that schema is queried. If you do not specify a schema, the default schema of the database is queried.	information_schema
compute_project	No	The English name of the project that executes the SQL query. The project and its associated compute source must have read permissions on the table. If your default query scope is a Dataphin project, this parameter is optional. If you do not specify it, the project specified in the default query scope is used for SQL execution. If your default query scope is a Dataphin data board, this parameter is required. If your default query scope is a Dataphin data source, you do not need to set this parameter.	Exprojectname
user	Yes	The user's AccessKey ID or the platform's AccessKey ID. In proxy mode, use the platform's AccessKey ID. To obtain the platform AccessKey ID, contact the Dataphin operations team. To obtain a user's AccessKey ID, navigate to Me > AccessKey Management.	kIB**********PT0
log_level	No	The log level. Valid values: DEBUG INFO WARNING ERROR	DEBUG
password	Yes	The AccessKey Secret of the user. To obtain the platform AccessKey Secret, contact the Dataphin operations team. To obtain a user's AccessKey Secret, navigate to Me > AccessKey Management.	Cy**************r2T
delegation_uid	No	When using a platform AccessKey, you can connect to Dataphin on behalf of another user. This parameter specifies the Dataphin user to impersonate. Set this parameter to the account ID of the user to be impersonated, based on the selected account_type. Setting this parameter enables proxy mode authentication.	999***999
account_type	No	When you use proxy mode authentication, you must specify the account type of the delegated user. Valid values: ACCOUNT_NAME: The Dataphin username. This mode is recommended when the application and Dataphin usernames are the same. USER_ID: The internal unique ID in Dataphin. This mode is not generally recommended. SOURCE_USER_ID: The source system account ID. This is available when Dataphin is configured with SSO authentication (such as RAM, SAML, or oAuth). This is the user's account in the identity provider (IdP). Note This parameter is required only when delegation_uid is set. If this parameter is not set, the default type is USER_ID. If duplicate users exist, authentication fails.	USER_ID
connect_timeout	No	The maximum time to wait for a connection, in seconds. Greater than 0: The timeout period. The minimum value is 10s. Less than or equal to 0: Wait indefinitely.	10
engine	No	The offline engine for the project specified by project_name in the session or connection string. For a Hadoop compute source, the default is Hive, but you can set this parameter to Impala or Spark. The engine type must be pre-configured in the compute source. If the specified engine is not supported by the project, the setting is ignored and a warning is issued. Valid values: MaxCompute Hologres Hive Impala Inceptor ArgoDB Spark Note This parameter is ignored when you access a data source.	MaxCompute
acceleration_source	No	Specifies the acceleration source. You can use the code of any acceleration source within the tenant.	starrocks_code
acceleration_resource_group	No	The acceleration resource group. You can select any resource group configured for the selected acceleration source.	starrocks_resource_group

Authentication modes

Simple mode

Use the RAM user's AccessKey ID as the username and the RAM user's AccessKey Secret as the password. The connection is then authenticated as that user. For more information about how to create a RAM user, see Prepare a RAM user.

When you connect to Dataphin by using the jdbc driver, Dataphin authenticates the AccessKey and authorizes the corresponding user for the requested resources or SQL queries. Dataphin then executes the SQL query as that user.

Proxy mode

Important

Before you can use proxy mode, you must contact the Dataphin operations team to enable and configure this feature.

System-level integrations with Dataphin typically use proxy mode. This method avoids the need to distribute or configure an AccessKey for each user. You can specify the user to impersonate in the JDBC connection string (JDBC URL) to authorize operations based on that user's permissions. The platform-level AccessKey has high privileges and can impersonate any user. As shown in the following figure, the client is configured with the platform AccessKey. When userA accesses the system through the client, delegation_uid=userA must be specified to indicate access on behalf of userA. Dataphin then validates the request based on userA's functional and data permissions.

Dataphin driver

com.aliyun.dataphin.jdbc.DataphinDriver

API	Description	API definition
connect	Establishes a database connection.	`Connection connect (String url, Properties info) throws SQLException;`
acceptsURL	Checks if the driver supports the URL.	`boolean acceptsURL(String url) throws SQLException;`

com.aliyun.dataphin.jdbc.DataphinConnection

API

Description

API definition

createStatement

Creates a Statement object.

Statement createStatement
(int resultSetType, 
int resultSetConcurrency)
throws SQLException;

prepareStatement

Creates a PreparedStatement object.

PreparedStatement prepareStatement(String sql, int resultSetType,int resultSetConcurrency)throws SQLExcept;
PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability);

com.aliyun.dataphin.jdbc.DataphinStatement

API	Description	API definition
executeQuery	Executes an SQL query and returns a ResultSet.	`ResultSet executeQuery (String sql) throws SQLException;`
setFetchSize	Defines the number of rows to fetch per batch. If not set or set to 0, the default is 1000.	`void setFetchSize(int rows) throws SQLException`
cancel	Cancels the execution of the Statement.	`void cancel() throws SQLException;`

com.aliyun.dataphin.jdbc.DataphinPrepareStatement

API	Description	API definition
executeQuery	Executes an SQL query and returns a ResultSet.	`ResultSet executeQuery (String sql) throws SQLException;`

com.aliyun.dataphin.jdbc.DataphinResultSetMetaData

API	Description	API definition
getColumnCount	Gets the number of columns in the result schema.	`int getColumnCount() throws SQLException;`
getColumnName	Gets the name of a column in the result schema.	`int getColumnCount() throws SQLException;`

com.aliyun.dataphin.jdbc.ResultSet

API	Description	API definition
next	Moves the cursor to the next row of the result set.	`boolean next() throws SQLException;`

com.aliyun.dataphin.jdbc.DatabaseMetaData

API

Description

API definition

getTables

Retrieves a description of the tables available in the given catalog.

Parameters:
- catalog: The default value is default.
- schemaPattern: The project name or data board name.
- tableNamePattern: The table name. Fuzzy matching is supported, but regular expressions are not.
- types: This parameter is not currently supported.
Return value:
- Returns a ResultSet.
- You can call next() to retrieve the metadata for each table row by row. Only the table name is supported.
Get table name:
- resultSet.getString("table_name")

getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) throws SQLException

getColumns

Retrieves a description of the table's columns.

Parameters:
- catalog: The project name or data board name.
- schemaPattern: This parameter is not currently supported.
- tableNamePattern: The full name of the table. Fuzzy matching and regular expressions are not supported.
- columnNamePattern: This parameter is not currently supported.
Return value:
- Returns a ResultSet.
- You can call next() to retrieve the metadata for each column row by row. Only the column name and data type are supported.
Retrieve the column name:resultSet.getString("column_name").
Get the data type of the column:resultSet.getString("data_type").

ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)

Examples

You can retrieve catalog information by using the Dataphin jdbc driver.

1. Get table list

Gets a list of physical tables and physical views in a project.

Syntax

SHOW TABLES
    [FROM db_name]
    [LIKE 'pattern']

Parameters

db_name:

Dataphin project name: Displays the physical tables in the project.
For a development project and a data board, you must explicitly append _Dev.
If db_name is not specified, Dataphin lists tables under the project_name set in the URL.

Response

Name	Type	Description
dim_user	Logical table	User table.
ods_user	Physical table	User source table.
ods_user_logical_view	Logical view	Logical view.
ods_user_physical_view2	Physical view	Physical view.

2. Get table schema

Gets the column details of a physical table or physical view.

Syntax

{DESCRIBE | DESC} table_name;

Note

Only physical tables and physical views are supported.

Parameters

table_name: The name of the physical table or physical view.

Response

Name	Type	Description
ID	BigInt	User ID.
Name	String	User name.
DS	String	The partition time.

Server-side connection control

Maximum number of connections: The default is 100.
Connection timeout: The default is 288000s (2 hours). The system automatically disconnects a database connection that is idle for a specified period. If you try to perform an operation on this connection, the service reports an error or terminates unexpectedly. This corresponds to the connect_timeout parameter in the connection URL.

Dataphin JDBC task information in MaxCompute

When a Dataphin JDBC task is submitted to MaxCompute for execution, the system includes additional Dataphin-related information. The details are as follows:

Parameter	Description
logical_project	The name of the Dataphin project where the JDBC task is executed.
EXT_JDBC_TASKRUN_ID	The ID of the JDBC task.
EXT_DPN_TENANT_ID	The ID of the Dataphin tenant where the JDBC task is executed.
EXT_PLATFORM_ID	The ID of the platform that submitted the task to MaxCompute. The default is Dataphin.
biz_id	The Dataphin member ID of the user who executed the JDBC task.
odps.idata.userenv	User environment information. This includes the Java SDK version, Java version, IP address, and device MAC address. Example: `JavaSDK Revision:fcedc4d,Version:0.37.6,JavaVersion:1.8.0_152,IP:11...,MAC:00----*-25`.

You can use this information to analyze MaxCompute bills and track job execution times. For more information, see Collect statistics on accounts of top N costs and time-consuming jobs of MaxCompute projects.