AnalyticDB:Schedule Spark jobs in DataWorks

Step 1: Create an ADB Spark SQL node

1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a desired region. Find the desired workspace and choose Shortcuts > Data Studio in the Actions column.

2. Click the icon next to Project Folder, and select Create Node > ADB > ADB Spark SQL.

3. In the dialog box that appears, enter a name for the node and press Enter.

Step 2: Develop the ADB Spark SQL node

1. This topic provides an example of how to create an external database on an ADB Spark SQL node. For information about how to create an internal table, see Create an internal table using Spark SQL.

   CREATE DATABASE IF NOT EXISTS `adb_spark_db` LOCATION 'oss://testBucketname/db_dome';

2. Create an external table adb_spark_db.tb_order in the ADB Spark SQL node.

   CREATE TABLE IF NOT EXISTS adb_spark_db.tb_order(id int, name string, age int) 
   USING parquet 
   LOCATION 'oss://testBucketname/db_dome/tb1' 
   TBLPROPERTIES ('parquet.compress'='SNAPPY');

3. Query data.

   After the external table is created, you can use a SELECT statement in AnalyticDB for MySQL to query the Parquet data.

   SELECT * FROM adb_spark_db.tb_order limit 100;

4. Create a Delta Lake table adb_spark_db.raw_order in the ADB Spark SQL node.

   CREATE TABLE IF NOT EXISTS adb_spark_db.raw_order(id int, name string, age int) 
   USING delta;

5. Import data from adb_spark_db.tb_order to adb_spark_db.raw_order.

   INSERT INTO adb_spark_db.raw_order SELECT * FROM adb_spark_db.tb_order;

6. Create a database in AnalyticDB for MySQL. If a database is already created, you can skip this step. The following is an example:

   CREATE DATABASE adb_demo; 

7. Create an internal table in AnalyticDB for MySQL to store the data imported from the Delta Lake table. The following is an example:

   CREATE TABLE adb_demo.order_xuanwu_format (
       `id` int, 
       `name` string, 
       `age` int) 
   using adb 
       TBLPROPERTIES (
       'distributeType'='HASH',
       'distributeColumns' = 'id',
       'storagePolicy' = 'hot'
   );

8. Import the data from the Delta Lake table adb_spark_db.raw_order to order_xuanwu_format.

   INSERT OVERWRITE adb_demo.order_xuanwu_format SELECT * FROM adb_spark_db.adb_spark_db.raw_order;

Step 3: Configure and run the ADB Spark SQL node

1. In the right-side pane, click Debugging Configurations to configure runtime parameters for the ADB Spark SQL node.

   Parameter Type	Parameter	Description
   Computing Resource	Computing Resource	Select the attached AnalyticDB for Spark computing resource.
   	AnalyticDB Computing Resource Group	Select the Interactive resource group with the Spark engine that you created in the AnalyticDB for MySQL cluster.
   Resource Group	Resource Group	Select the DataWorks resource group that passed the connectivity test when you attached the AnalyticDB for Spark computing resource.
   	CUs For Computing	The node uses the default CU value. You do not need to modify the CU.
   Script Parameters	Parameter Name	The name of the parameter that you specified for the ADB Spark SQL node. For example, you can configure the $[yyyymmdd] parameter in the script to perform batch synchronization of daily new data. For the supported parameters and their formats, see Configure Scheduling Parameters. Note The system automatically detects the parameter names configured in the node.
   	Parameter Value	Configure the parameter value. At runtime, the parameter is dynamically replaced with its actual value.

2. (Optional) To run node tasks on a schedule, in the Scheduling pane on the right side of the node, configure the Computing Resource, AnalyticDB Computing Resource Group, and Resource Group parameters in the Scheduling Policies section. Then, configure the parameters in the Scheduling Parameters section.

3. After you configure the debug settings, click the icon to save the SQL node. Then, click the icon to test the SQL script and verify that it runs as expected.

4. After the scheduling configuration is complete, you can submit and publish the database nodes to the production environment.

5. After you publish a task, it runs periodically based on the configured parameters. You can view and manage the published auto triggered tasks on the Operation Center > Node O&M > Auto Triggered Node O&M > Auto Triggered Nodes page. For more information, see Introduction to Operation Center.

Step 1: Create an ADB Spark node

1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a desired region. Find the desired workspace and choose Shortcuts > Data Studio in the Actions column.

2. Click the icon next to Project Folder, and select Creaet Node > ADB > ADB Spark.

3. In the dialog box that appears, enter a name for the node and press Enter.

Step 2: Develop the ADB Spark node

ADB Spark nodes support development in Java/Scala and Python.

Step 3: Configure and run the ADB Spark node

1. In the right-side pane, click Debugging Configurations to configure runtime parameters for the ADB Spark node.

   Parameter Type	Parameter	Description
   Computing Resource	Computing Resource	Select the attached AnalyticDB for Spark computing resource.
   	AnalyticDB Computing Resource Group	Select the Job resource group that you created in the AnalyticDB for MySQL cluster.
   Resource Group	Resource Group	Select the DataWorks resource group that passed the connectivity test when you attached the AnalyticDB for Spark computing resource.
   	CUs For Computing	The node uses the default CU value. You do not need to modify the CU.
   Script Parameters	Parameter Name	The name of the parameter that you configured in the ADB Spark JAR job. Note The system automatically detects the parameter names configured in the node.
   	Parameter Value	Configure the parameter value. At runtime, the parameter is dynamically replaced with its actual value.

2. (Optional) To run a node task on a schedule, in the Scheduling pane on the right side of the node, configure the Computing Resource, AnalyticDB Computing Resource Group, and Resource Group parameters in the Scheduling Policies section. Then, configure the parameters in the Scheduling Parameters section.

3. After you configure the debug settings, click the icon to save the node. Then, click the icon to test the script and verify that it runs as expected.

4. After you complete the scheduling configuration, you can publish the database nodes to the production environment.

5. A published auto triggered task runs periodically based on the configured parameters. You can view and manage the task on the Operation Center > Node O&M > Auto Triggered Node O&M > Auto Triggered Nodes page. For more information, see Introduction to Operation Center.