Read and write SQL Server data using Spark SQL

Prerequisites

• An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.

• A job resource group is created for the AnalyticDB for MySQL cluster.

• A database account is created for the AnalyticDB for MySQL cluster.

  • If you use an Alibaba Cloud account, you need to only create a privileged account.

  • 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.

• The AnalyticDB for MySQL cluster and the ApsaraDB RDS for SQL Server instance are in the same virtual private cloud (VPC).

• The ApsaraDB RDS for SQL Server instance is in a security group, and the group's inbound and outbound rules allow access on the instance's port.

• The CIDR block of the vSwitch to which the ApsaraDB RDS for SQL Server instance is connected must be included in the whitelist of the instance.

Step 1: Prepare data

In ApsaraDB RDS for SQL Server, create a database and a table, and insert data using the following statements:

CREATE DATABASE db;

CREATE TABLE test (
    id INT NULL,
    first_name NVARCHAR(32) NULL,
    last_name NVARCHAR(32) NULL,
    age INT NULL
);

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

Step 2: Download and upload the driver

1. Download the ApsaraDB RDS for SQL Server driver.

2. Unzip the downloaded driver package. Upload the JAR file that ends with jre8.jar from the jars folder to OSS.

Step 3: Submit a Spark SQL job

1. 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. Find the cluster that you want to manage and click the cluster ID.

2. In the left-side navigation pane, choose Job Development > SQL Development.

3. In the SQL Console window, select the Spark engine and a job resource group.

4. In the SQL Console window, enter the following job content:

   ADD jar oss://testBucketName/mssql-jdbc-12.8.1.jre8.jar;
   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-t4nn744j34****.mssql.singapore.rds.aliyuncs.com:1433;databaseName=db;trustServerCertificate=true;encrypt=true;>',     
     driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
     dbtable '<tableName>',    
     user '<user>',       
     password '<password>'      
   );
   
   INSERT INTO table_tmp VALUES(4,'e','f',8);
   
   SELECT * FROM table_tmp;

   The following table describes the parameters.

   Parameter	Required	Description
   ADD jar	Yes	The OSS path to the ApsaraDB RDS for SQL Server driver. This topic uses the OSS path to the mssql-jdbc-12.8.1.jre8.jar file as an example.
   spark.adb.eni.enabled	Yes	Specifies whether to enable access over an elastic network interface (ENI). Set the spark.adb.eni.enabled parameter to true to access data over an ENI.
   spark.adb.eni.vswitchId	Yes	The ID of the vSwitch. On the Database Connection page of the ApsaraDB RDS for SQL Server instance, hover over the VPC field to obtain the vSwitch ID.
   spark.adb.eni.securityGroupId	Yes	The ID of the security group that contains the ApsaraDB RDS for SQL Server instance. For instructions on how to configure one, see Configure security group rules.
   USING org.apache.spark.sql.jdbc	Yes	Must be set to USING org.apache.spark.sql.jdbc.
   OPTIONS	Yes	url: The internal endpoint and port of the ApsaraDB RDS for SQL Server instance. Format: jdbc:sqlserver://rm-t4nn744j34****.mssql.singapore.rds.aliyuncs.com:1433;databaseName=db;trustServerCertificate=true;encrypt=true;. In the connection string, set databaseName to the name of the database that you want to access. Set both trustServerCertificate and encrypt to true. driver: Must be com.microsoft.sqlserver.jdbc.SQLServerDriver. dbtable: The name of the table in ApsaraDB RDS for SQL Server. Use the db_name.table_name format. This example uses db.test. user: The username of the ApsaraDB RDS for SQL Server database account. password: The password of the ApsaraDB RDS for SQL Server database account.

5. Click Execute.

6. After the Spark job succeeds, you can find the table data in the logs. To view the logs, navigate to the Spark JAR Development page, open the Applications tab, and click Logs for the job. For more information, see Spark editor.