Use Spark SQL to access JDBC-connected MySQL data sources - AnalyticDB for MySQL

AnalyticDB for MySQL Data Lakehouse Edition (V3.0) allows you to submit a Spark SQL job to access self-managed MySQL databases, ApsaraDB RDS for MySQL, or PolarDB for MySQL in view mode or catalog mode. This topic describes how to use Spark SQL to access ApsaraDB RDS for MySQL data.

Prerequisites

An AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster and an ApsaraDB RDS for MySQL instance are created in the same region.
A job resource group is created in the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster. For more information, see Create a resource group.
A database account is created for the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster.
- If you use an Alibaba Cloud account, you must create a privileged database account. For more information, see the "Create a privileged account" section of the Create a database account topic.
- If you use a Resource Access Management (RAM) user, you must create a privileged account and a standard account and associate the standard account with the RAM user. For more information, see Create a database account and Associate or disassociate a database account with or from a RAM user.
The ApsaraDB RDS for MySQL instance is added to a security group. The inbound and outbound rules of the security group allow requests from the port of the ApsaraDB RDS for MySQL instance. For more information, see Configure a security group for an ApsaraDB RDS for MySQL instance and Add a security group rule.

Step 1: Prepare data

Create a database in the ApsaraDB RDS for MySQL instance, create a table in the database, and then insert data into the table. Sample statements:

CREATE DATABASE `db`;

CREATE TABLE `db`.`test` (
  `id` int(11) DEFAULT NULL,
  `first_name` varchar(32) DEFAULT NULL,
  `laster_name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

INSERT INTO test VALUES(1,'a','b',5);
INSERT INTO test VALUES(2,'c','d',6);
INSERT INTO test VALUES(3,'e','f',7);

(Optional) Step 2: Upload a CA certificate and the ApsaraDB RDS for MySQL driver

Note

If you do not want to access ApsaraDB RDS for MySQL data over SSL, you can skip this step and submit a Spark SQL job. For more information, see the "Step 3: Submit a Spark SQL job" section of this topic.

Enable SSL encryption for the ApsaraDB RDS for MySQL instance and download a CA certificate. For more information, see Configure the SSL encryption feature.
Important
- When you enable SSL encryption, you can select whether to encrypt an internal connection or a public connection. In this example, an internal connection is encrypted.
- The default validity period of a CA certificate is one year. If the CA certificate expires, you must generate another certificate. You cannot use an expired CA certificate to access ApsaraDB RDS for MySQL data over an SSL connection.
Download the driver that is compatible with the engine version of the ApsaraDB RDS for MySQL instance from https://dev.mysql.com/downloads/connector/j/.
In this example, the mysql-connector-java-8.0.29.jar package is used.
Decompress the CA certificate package. Upload the JKS file and the ApsaraDB RDS for MySQL driver to Object Storage Service (OSS). For more information, see Upload objects.

Step 3: Submit a Spark SQL job

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Data Lakehouse Edition (V3.0) tab, find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, choose Job Development > SQL Development.
On the SQLConsole tab, select the Spark engine and the job resource group.

On the SQLConsole tab, configure the parameters based on different access modes.

View mode

If you access data in view mode, a temporary view is created. Every time you start a job, you must create a view. In view mode, you can select whether to access ApsaraDB RDS for MySQL data over an elastic network interface (ENI) or an SSL connection.

Access ApsaraDB RDS for MySQL data over an ENI

set spark.adb.eni.enabled=true;
set spark.adb.eni.vswitchId=<vsw-bp1sxxsodv28ey5dl****>;   
set spark.adb.eni.securityGroupId=<sg-bp19mr685pmg4ihc****>;    

CREATE TEMPORARY VIEW table_tmp
USING org.apache.spark.sql.jdbc
OPTIONS (
  url '<jdbc:mysql://rm-bp1k87323a7ia****.mysql.rds.aliyuncs.com:3306>',     
  dbtable '<db.test>',    
  user '<user>',       
  password '<password>'      
);

select * from table_tmp;

The following table describes the parameters.

Parameter	Description
spark.adb.eni.enabled	Specifies whether to enable ENI. When you access data, you must set the `spark.adb.eni.enabled` parameter to true.
spark.adb.eni.vswitchId	The vSwitch ID of the ApsaraDB RDS for MySQL instance. To view the vSwitch ID, move the pointer over VPC on the Database Connection page.
spark.adb.eni.securityGroupId	The ID of the security group to which the ApsaraDB RDS for MySQL instance is added. For more information, see Configure a security group for an ApsaraDB RDS for MySQL instance.
table_tmp	The name of the view. In this example, table_tmp is used.
USING org.apache.spark.sql.jdbc	The value of this parameter is automatically set to `USING org.apache.spark.sql.jdbc`.
OPTIONS	url: the internal endpoint and port number of the ApsaraDB RDS for MySQL instance. Format: `jdbc:mysql://rm-bp1k87323a7ia**.mysql.rds.aliyuncs.com:3306`. dbtable: the name of the table in the ApsaraDB RDS for MySQL instance. Format: `db_name.table_name`. In this example, db_1.table_11 is used. user: the username of the database account that is used to connect to the ApsaraDB RDS for MySQL instance. password**: the password of the database account.

Access ApsaraDB RDS for MySQL data over an SSL connection

add jar oss://<bucketname>/mysql-connector-java-8.0.11.jar;
set spark.app.name=SSL_RDS_SQL;
set spark.adb.eni.enabled=true;
set spark.adb.eni.vswitchId=<vsw-bp1sxxsodv28ey5dl****>;
set spark.adb.eni.securityGroupId=<sg-bp19mr685pmg4ihc****>;
set spark.kubernetes.driverEnv.ADB_SPARK_DOWNLOAD_FILES=oss://<bucketname>/ApsaraDB-CA-Chain.jks;
set spark.executorEnv.ADB_SPARK_DOWNLOAD_FILES=oss://<bucketname>/ApsaraDB-CA-Chain.jks;

CREATE TEMPORARY VIEW table_view
USING org.apache.spark.sql.jdbc
OPTIONS (
  url '<jdbc:mysql://rm-bp1k87323a7ia****.mysql.rds.aliyuncs.com:3306/?useSSL=true&clientCertificateKeyStoreUrl=file:///tmp/<bucketname>/folder/ApsaraDB-CA-Chain.jks&clientCertificateKeyStorePassword=apsaradb&trustCertificateKeyStoreUrl=file:///tmp/<bucketname>/folder/ApsaraDB-CA-Chain.jks&trustCertificateKeyStorePassword=apsaradb&trustCertificateKeyStoreType=JKS&clientCertificateKeyStoreType=JKS>',
  dbtable '<db.test>',
  user '<user>',
  password '<password>'
);

The following table describes the parameters.

Parameter	Description
add jar	The OSS path of the ApsaraDB RDS for MySQL driver. In this example, the OSS path of the mysql-connector-java-8.0.11.jar package is used.
spark.app.name	The name of the Spark SQL job.
spark.adb.eni.enabled	Specifies whether to enable ENI. When you access data, you must set the `spark.adb.eni.enabled` parameter to true.
spark.adb.eni.vswitchId	The vSwitch ID of the ApsaraDB RDS for MySQL instance. To view the vSwitch ID, move the pointer over VPC on the Database Connection page.
spark.adb.eni.securityGroupId	The ID of the security group to which the ApsaraDB RDS for MySQL instance is added. For more information, see Configure a security group for an ApsaraDB RDS for MySQL instance.
spark.kubernetes.driverEnv.ADB_SPARK_DOWNLOAD_FILES	The OSS path of the JKS certificate file. Separate multiple JKS certificate files with commas (,). Example: `spark.kubernetes.driverEnv.ADB_SPARK_DOWNLOAD_FILES": "oss://<bucketname>/a.jks,oss://<bucketname>/b.jks`.
spark.executorEnv.ADB_SPARK_DOWNLOAD_FILES	The OSS path of the JKS certificate file. Separate multiple JKS certificate files with commas (,). Example: `spark.executor.ADB_SPARK_DOWNLOAD_FILES": "oss://<bucketname>/a.jks,oss://<bucketname>/b.jks`.
table_view	The name of the view. In this example, table_view is used.
USING org.apache.spark.sql.jdbc	The value of this parameter is automatically set to `USING org.apache.spark.sql.jdbc`.
OPTIONS	url: the internal endpoint, port number, and SSL parameters of the ApsaraDB RDS for MySQL instance. Format: `jdbc:mysql://rm-bp1k87323a7ia**.mysql.rds.aliyuncs.com:3306/?useSSL=true&clientCertificateKeyStoreUrl=file:///tmp/<bucketname>/folder/ApsaraDB-CA-Chain.jks&clientCertificateKeyStorePassword=apsaradb&trustCertificateKeyStoreUrl=file:///tmp/<bucketname>/folder/ApsaraDB-CA-Chain.jks&trustCertificateKeyStorePassword=apsaradb&trustCertificateKeyStoreType=JKS&clientCertificateKeyStoreType=JKS`. For information about the SSL parameters, see the "Parameters" section of the Access ApsaraDB RDS for MySQL topic. dbtable: the name of the table in the ApsaraDB RDS for MySQL instance. Format: `db_name.table_name`. In this example, db.test is used. user: the username of the database account that is used to connect to the ApsaraDB RDS for MySQL instance. password**: the password of the database account.

Catalog mode

set spark.adb.eni.enabled=true;
set spark.adb.eni.vswitchId=<vsw-bp1d14ddiw46fkgu1****>;
set spark.adb.eni.securityGroupId=<sg-bp19varsa8j0hyb****>;
set spark.sql.catalog.jdbc=org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTableCatalog;
set spark.sql.catalog.jdbc.url=<jdbc:mysql://rm-bp11mpql1e01l****.mysql.rds.aliyuncs.com:3306>;
set spark.sql.catalog.jdbc.user=<user>;
set spark.sql.catalog.jdbc.password=<password>;
use jdbc;
select * from db.test;

The following table describes the parameters.

Parameter	Description
spark.sql.catalog.jdbc	The method that is supported for Spark SQL to configure the data source. The value of this parameter is automatically set to `org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTableCatalog`. Note You can customize `catalog_name` for the `spark.sql.catalog.catalog_name` parameter. In this example, `jdbc` is used.
spark.sql.catalog.jdbc.url	The internal endpoint and port number of the ApsaraDB RDS for MySQL instance. Format: `jdbc:mysql://rm-bp1k87323a7ia****.mysql.rds.aliyuncs.com:3306`.
spark.sql.catalog.jdbc.user	The username of the database account that is used to connect to the ApsaraDB RDS for MySQL instance.
spark.sql.catalog.jdbc.password	The password of the database account.

Note

For information about more parameters that are required to access data sources by using Java database connectivity (JDBC), see JDBC To Other Databases.