Run SQL on Dataphin via JDBC Driver and OpenAPI - Dataphin - Alibaba Cloud - Dataphin

Learn how to connect to Dataphin using a JDBC driver.

Prerequisites

Before using the JDBC driver, you must activate the Dataphin OpenAPI feature.

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: Executing an SQL statement with the Dataphin JDBC driver is equivalent to executing it in the Dataphin console. Therefore, the execution results are consistent. Features configured in Dataphin, such as permissions, desensitization rules, security settings, and code specifications, also affect the execution results of the Dataphin JDBC driver.

Limitations

You can use JDBC to connect to Dataphin from all compute engines and some data sources. Supported data sources include MySQL, Oracle, MaxCompute, Hive, AnalyticDB for MySQL 3.0, StarRocks, AnalyticDB for PostgreSQL, and Doris.
When you execute an SQL statement with the Dataphin JDBC driver, Dataphin pre-processes the statement for tasks such as SQL translation and data desensitization. The result data is also transmitted and forwarded through Dataphin. These processes can introduce performance overhead, resulting in longer query times compared to querying the compute engine directly.
The Dataphin JDBC driver submits SQL statements to Dataphin through OpenAPI for processing, which are then forwarded to the underlying compute engine. Before you use the Dataphin JDBC driver, you must evaluate the expected call volume and determine whether you need to scale out your Dataphin cluster and compute engine.
Note
If you cannot perform this evaluation, you can submit a capacity evaluation request for your Dataphin cluster to the Dataphin O&M team. This does not include a capacity evaluation for the compute engine.
Dataphin does not currently support traffic control or concurrency control. Evaluate the potential impacts carefully.

JDBC driver version and download link

To obtain the driver JAR package, contact the Dataphin O&M team.

Connection parameters

Note

When Dataphin JDBC uses the 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_source_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 square brackets ([]) are for illustration only and must be omitted from the actual connection string.

Parameter	Required	Description	Example
host	Yes	The domain name of Dataphin OpenAPI. You can obtain the domain name from the OpenAPI invocation address section on the AccessKey Management page in the Personal Center, as shown in the following figure:	dataphin-openapi.****.aliyun.com
port	No	The port is determined by whether HTTPS is enabled for the Dataphin OpenAPI. If HTTPS is enabled, the port is `443`. Otherwise, the port is `80`, which is the default.	80
catalog	Yes	The default query scope. To use a Dataphin physical table, specify 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 (starts with `ds_`). Note If the default query scope is a data block, 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 compute_project.	Exprojectname
tenant_id	Yes	The tenant ID to query.	111***111
ssl	No	Specifies whether to use HTTPS. True: Use an HTTPS domain name. False: Use an HTTP domain name. The default value is False.	False
currentschema	No	The schema of the data source. If the catalog uses a Dataphin physical table or logical table, you do not need to specify this parameter. If the data source used in the catalog does not support schemas, such as MySQL, you do not need to specify this parameter. If the data source used in the catalog 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	Specify the English name of the project. This project executes the SQL query. The project and its bound compute engine must have read permissions on the queried tables. If the specified query scope is a Dataphin project, this parameter is optional. If you do not specify this parameter, the project specified in the default query scope is used to execute the SQL statement. If the specified query scope is a Dataphin data block, this parameter is required. If the specified query scope is a Dataphin data source, you do not need to specify this parameter.	Exprojectname
user	Yes	The AccessKey ID of a user or the platform. When you use proxy mode, specify the platform's AccessKey ID. To obtain the platform AccessKey ID, contact the Dataphin O&M team. You can obtain a user's AccessKey ID from the AccessKey Management page in the Personal Center.	kIB**********PT0
log_level	No	The logging level. Valid values: DEBUG INFO WARNING ERROR	DEBUG
password	Yes	The AccessKey Secret for the specified user. To obtain the platform AccessKey, contact the Dataphin O&M team. You can obtain a user's AccessKey ID from the AccessKey Management page in the Personal Center.	Cy**************r2T
delegation_uid	No	In proxy mode, this parameter specifies the Dataphin user to impersonate. For the proxied Dataphin user, you must provide the corresponding account ID based on the selected account_type. Setting this parameter enables proxy mode.	999***999
account_type	No	When you use proxy mode for authentication, you must specify the account type of the proxied user. ACCOUNT_NAME: The username in Dataphin. This mode is recommended when the application username is the same as the Dataphin username. USER_ID: The unique internal ID in Dataphin. This mode is generally not recommended. SOURCE_USER_ID: The account ID in the source system. This is available when Dataphin is configured for Single Sign-On (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 specified, the default type is USER_ID. If duplicate users exist, authentication fails.	USER_ID
connect_timeout	No	The connection acquisition timeout, in seconds. Greater than 0: The timeout in seconds. The minimum value is 10. Less than or equal to 0: Wait indefinitely.	10
engine	No	The compute engine for the project specified by project_name in the session or connection string. For a Hadoop compute engine, the default engine is Hive, but you can use this parameter to set it to Impala or Spark. The engine type must be pre-configured in the compute engine. If the specified engine is not supported by the project, this setting is ignored and a warning is returned. 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 select the code of any acceleration source within the tenant.	starrocks_code
acceleration_resource_group	No	The acceleration resource group. You can select a resource group that is configured for the selected acceleration source.	starrocks_resource_group

Authentication modes

Simple mode

Set the username to the user's AccessKey ID and the password to the user's AccessKey Secret. This authenticates the connection as that user. To view your AccessKey, see Dataphin OpenAPI AccessKey Management.

When you access Dataphin through the JDBC driver, Dataphin authenticates the AccessKey and authorizes the user for the requested resources or SQL statements. Dataphin then executes the SQL statement as that user.

Proxy mode

Important

Before you use proxy mode, you must contact the Dataphin O&M team to activate and configure this feature.

Proxy mode is typically used for system-level integration with Dataphin. This method avoids distributing or configuring AccessKeys for individual users. You can specify the proxied user in the connection string (JDBC URL) to ensure that operations are authorized based on that user's permissions. A platform-level AccessKey has high privileges and can proxy any user. For example, if the client uses a platform AccessKey, set delegation_uid=userA when userA connects. Dataphin then impersonates userA for the connection and performs permission checks based on that user's profile.

Dataphin driver

com.aliyun.dataphin.jdbc.DataphinDriver

API	Description	Definition
connect	Establishes a database connection.	`Connection connect (String url, Properties info) throws SQLException;`
acceptsURL	Checks whether the URL is supported.	`boolean acceptsURL(String url) throws SQLException;`

com.aliyun.dataphin.jdbc.DataphinConnection

API

Description

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	Definition
executeQuery	Executes an SQL statement and returns a ResultSet object.	`ResultSet executeQuery (String sql) throws SQLException;`
setFetchSize	Specifies the number of rows to fetch from the database in a single batch. If this parameter is not set or is set to 0, a default of 1000 is used.	`void setFetchSize(int rows) throws SQLException`
cancel	Attempts to cancel the execution of the `Statement` object.	`void cancel() throws SQLException;`

com.aliyun.dataphin.jdbc.DataphinPrepareStatement

API	Description	Definition
executeQuery	Executes an SQL statement and returns a ResultSet object.	`ResultSet executeQuery (String sql) throws SQLException;`

com.aliyun.dataphin.jdbc.DataphinResultSetMetaData

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

com.aliyun.dataphin.jdbc.ResultSet

API	Description	Definition
next	Moves the cursor to the next row in the `ResultSet` object.	`boolean next() throws SQLException;`

com.aliyun.dataphin.jdbc.DatabaseMetaData

API

Description

Definition

getTables

Gets table information.

Parameters:
- catalog: The default value is default.
- schemaPattern: The project name or data block name.
- tableNamePattern: The table name. Fuzzy matching is supported. Regular expressions are not supported.
- types: This parameter is not supported.
Result:
- The result is a ResultSet object.
- Each row in the ResultSet describes a table. You can iterate through the ResultSet using next(). Only the table name can be retrieved from each row.
Get the table name:
- resultSet.getString("table_name")

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

getColumns

Gets column information for a table.

Parameters:
- catalog: The project name or data block name.
- schemaPattern: This parameter is not supported.
- tableNamePattern: The full name of the table. Fuzzy matching and regular expressions are not supported.
- columnNamePattern: This parameter is not supported.
Result:
- The result is a ResultSet object.
- Each row in the ResultSet describes a table column. You can iterate through the ResultSet using next(). Only the column name and data type can be retrieved from each row.
Get the column name: resultSet.getString("column_name").
Get the column data type: resultSet.getString("data_type").

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

Examples

You can get catalog information by using the Dataphin JDBC driver.

1. List tables

Lists the 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 development projects and data blocks, you must explicitly add the _Dev suffix.
If db_name is not specified, it defaults to the project_name specified in the connection string.

Result

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

Shows the schema of a physical table or physical view.

Syntax

{DESCRIBE | DESC} table_name;

Note

This command supports only physical tables and physical views.

Parameters

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

Result

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

Server-side connection control

Maximum number of connections: The default value is 100.
Connection timeout: The default value is 288000s (2 hours). If an established connection remains idle, it automatically disconnects after this period. If you try to perform an operation on the disconnected connection, an error or a close exception is returned. This corresponds to the Connection_Idle_Timeout in the JDBC URL.

Metadata for JDBC tasks in MaxCompute

When a JDBC task is submitted from Dataphin to a MaxCompute execution instance, the following metadata is included in the submission:

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 upper-layer platform that submits the task to MaxCompute. The default value is Dataphin.
biz_id	The ID of the Dataphin user who executes the JDBC task.
odps.idata.userenv	User environment information. This includes the Java SDK version, Java version, IP address, and device MAC information. Example: `JavaSDK Revision:fcedc4d,Version:0.37.6,JavaVersion:1.8.0_152,IP:11...,MAC:00----*-25`.

You can use this information for use cases such as analyzing MaxCompute bills and viewing job durations. For more information, see Collect statistics on accounts of top N costs and time-consuming jobs of MaxCompute projects.