MaxCompute:Use Kettle to schedule MaxCompute jobs

Prerequisites

Procedure

1. Step 1: Place the MaxCompute JDBC driver

   Place the MaxCompute JDBC driver in the Kettle driver directory.

2. Step 2: Connect to a MaxCompute project

   Connect Kettle to your MaxCompute project.

3. Step 3: Create a scheduling workflow

   Create a job scheduling workflow in the Spoon interface.

4. Step 4: Run the scheduling workflow

   Run the job scheduling workflow.

5. Step 5: View job results

   View the job results.

Step 1: Place the MaxCompute JDBC driver

Place the MaxCompute JDBC driver JAR file, such as odps-jdbc-3.2.9-jar-with-dependencies.jar, into the data-integration/lib directory of your Kettle installation.

Step 2: Connect to a MaxCompute project

1. In the data-integration directory of your Kettle installation, double-click Spoon.bat (on Windows) or Spoon (on macOS) to launch Spoon.

2. From the top menu bar, select File > New > Job to create a Kettle job for the job scheduling workflow.

3. On the View tab, right-click Database connections in the navigation tree and select Create.

4. In the Database Connection dialog box, on the General tab, configure the parameters as described in the following table.

   Parameter	Description
   Network Connection Name	A custom name for the database connection. For example, MaxCompute.
   Connection type	Select Generic database.
   Connection Method	Select Native (JDBC).
   Dialect	Select Hadoop Hive 2.
   Custom Connection URL	The connection URL for the MaxCompute project. The format is jdbc:odps:<maxcompute_endpoint>?project=<MaxCompute_project_name>. Remove the <> symbols when you configure the URL. Parameters: <MaxCompute_endpoint>: Required. The endpoint of the region where your MaxCompute project is located. For a list of endpoints, see Endpoints. <MaxCompute_project_name>: Required. The name of the target MaxCompute project. This is the name of your MaxCompute project, not a workspace. You can sign in to the MaxCompute console, switch the region in the upper-left corner, and find the project name on the Projects page.
   Custom Driver Class Name	The JDBC driver class. Set this to com.aliyun.odps.jdbc.OdpsDriver.
   User Name	Your AccessKey ID for the MaxCompute project. You can go to the AccessKey Management page to obtain the AccessKey ID.
   Password	Your AccessKey secret.

5. Click Test. After the connection succeeds, click OK in the confirmation dialog box. Then, click Confirm in the Confirm dialog box to save the connection.

Step 3: Create a scheduling workflow

On the Design tab in Spoon, build a job scheduling workflow by creating and linking core objects.

The following example walks through an ETL process that uses the LOAD command to load data from OSS into a MaxCompute internal table. For the sample data, see LOAD. The jobs in this process break down by core object type as follows.

1. In the Spoon interface, click the Design tab.

2. Based on the preceding object breakdown, drag the core objects from the navigation pane on the left to the job canvas on the right. Connect the core objects in the following structure.

   To connect core objects, select a source object, press and hold Shift, and click the destination object to create a connection hop.

   Select the Core Objects tab on the left panel, expand General and other categories to get components. The connection structure is: Start → Create table → Load from OSS → Processing → Success (success path); also set failure paths from Create table, Load from OSS, Processing to the Abort job node for error handling.

3. Right-click a script-based core object and select Edit job entry. In the SQL dialog box, configure the parameters listed in the following table, and then click OK. Repeat this process to configure all script-based core objects.

   Parameter	Description
   Job entry name	The name of the job entry. For example, Create table, Load from OSS, or Processing.
   Connection	The name of the database connection to use. This is the database connection you created in Step 2. For example, MaxCompute.
   Send SQL as single statement?	Deselect this option.
   SQL Script	The SQL script for the job entry. The SQL scripts for the script-based core objects in this example are as follows: Create table CREATE TABLE ambulance_data_csv_load ( vehicleId INT, recordId INT, patientId INT, calls INT, locationLatitute DOUBLE, locationLongtitue DOUBLE, recordTime STRING, direction STRING); Load from OSS LOAD OVERWRITE TABLE ambulance_data_csv_load FROM LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/' STORED BY 'com.aliyun.odps.CsvStorageHandler' WITH serdeproperties ( 'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole', -- The ARN of AliyunODPSDefaultRole. You can obtain it from the RAM console. 'odps.text.option.delimiter'=',' ); Processing INSERT OVERWRITE TABLE ambulance_data_csv SELECT * FROM ambulance_data_csv_load;

Step 4: Run the scheduling workflow

1. In the job scheduling workflow interface, click the run icon in the upper-left corner. In the Run Options dialog box, click Execute.

   In the dialog, set Run configuration to Pentaho local, Log level to Basic logging, keep Clear log before execution checked by default, and leave other options as default.

2. Optional: If prompted to save the job, click Yes and name the job scheduling workflow (for example, mc).

3. Monitor the run status in the DAG diagram on the workflow interface or in the Execution Results section. The job scheduling workflow is complete when a green checkmark appears on each object.

   When the job scheduling flow finishes, the DAG shows green checkmarks on Create table, Load from OSS, Processing, and Success nodes. The Execution Results area shows each job entry result as true, with the final output Job has ended.

Step 5: View job results

After the job scheduling workflow is complete, run a SQL query to verify that the data was written to the destination table as expected.

1. In the Spoon interface, click the View tab. Under the created Kettle job (for example, mc), expand Database connections.

2. Right-click the database connection that you created (for example, MaxCompute) and select SQL Editor.

3. In the Simple SQL editor dialog box, enter a SQL query and click Execute. The results appear in the Examine preview data dialog box.

   The SQL query is as follows:

   SELECT * FROM ambulance_data_csv;