DataWorks:Configure a PolarDB for PostgreSQL node

Background information

PolarDB for PostgreSQL is a cloud-native relational database service that is developed by Alibaba Cloud. PolarDB for PostgreSQL is 100% compatible with PostgreSQL and is highly compatible with Oracle. PolarDB for PostgreSQL features rapid scalability, high performance, mass storage, and high security and reliability. It also supports the multi-dimensional, multi-model, spatio-temporal information engine Ganos that is developed by Alibaba Cloud and the open source geographic information engine PostGIS. For more information, see What is PolarDB?

Prerequisites

• A workflow is created.

  Development operations in different types of compute engines are performed based on workflows in DataStudio. Therefore, before you create a node, you must create a workflow. For more information, see Create a workflow.

• A PolarDB for PostgreSQL data source is added.

  You must add your PolarDB for PostgreSQL database to DataWorks as a PolarDB for PostgreSQL data source before you can use the data source to access data in the database. For information about how to add a data source, see Add and manage data sources. For information about how to use a PolarDB for PostgreSQL data source in DataWorks, see PolarDB data source.

  Note

  You can create PolarDB for PostgreSQL nodes based on only PolarDB for PostgreSQL data sources that are added to DataWorks in Java Database Connectivity (JDBC) connection string mode.

• A network connection is established between your data source and a resource group.

  You must make sure that the desired data source is connected to the resource group that you want to use. For more information about how to configure network connectivity, see Network connectivity solutions.

• (Required if you use a RAM user to develop tasks) The RAM user is added to the DataWorks workspace as a member and is assigned the Develop or Workspace Administrator role. The Workspace Administrator role has more permissions than necessary. Exercise caution when you assign the Workspace Administrator role. For more information about how to add a member and assign roles to the member, see Add workspace members and assign roles to them.

Step 1: Create a PolarDB for PostgreSQL node

1. Go to the DataStudio page.

   Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Development and Governance > Data Development. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Development.

2. On the DataStudio page, find the desired workflow, right-click the workflow name, and then choose Create Node > Database > PolarDB Postgresql.

3. In the Create Node dialog box, configure the Name parameter and click Confirm. Then, you can use the created node to develop and configure tasks.

Step 2: Develop a PolarDB for PostgreSQL task

(Optional) Select a PolarDB for PostgreSQL data source

If multiple PolarDB for PostgreSQL data sources are added to your workspace, you must select one from the Select Data Source drop-down list in the upper part of the configuration tab of the node based on your business requirements. If only one PolarDB for PostgreSQL data source is added to your workspace, the PolarDB for PostgreSQL data source is used to develop tasks.

Develop SQL code: Simple example

In the code editor on the configuration tab of the PolarDB for PostgreSQL node, write task code. Sample code:

SELECT * FROM usertablename;

Develop SQL code: Use scheduling parameters

DataWorks provides scheduling parameters whose values are dynamically replaced in the code of a task based on the configurations of the scheduling parameters in periodic scheduling scenarios. You can define variables in the task code in the ${Variable} format and assign values to the variables in the Scheduling Parameter section of the Properties tab. For information about the supported formats of scheduling parameters and how to configure scheduling parameters, see Supported formats of scheduling parameters and Configure and use scheduling parameters.

Sample code:

SELECT '${var}'; -- You can assign a specific scheduling parameter to the var variable.

Step 3: Configure task scheduling properties

If you want the system to periodically run a task on the node, you can click Properties in the right-side navigation pane on the configuration tab of the node to configure task scheduling properties based on your business requirements. For more information, see Overview.

Step 4: Debug task code

You can perform the following operations to check whether the task is configured as expected based on your business requirements:

1. Optional. Select a resource group and assign custom parameters to variables.

   • Click the icon in the top toolbar of the configuration tab of the node. In the Parameters dialog box, select a resource group for scheduling that you want to use to debug and run task code.

   • If you use scheduling parameters in your task code, assign the scheduling parameters to variables as values in the task code for debugging. For more information about the value assignment logic of scheduling parameters, see Debugging procedure.

2. Save and run task code.

   In the top toolbar, click the icon to save task code. Then, click the icon to run task code.

3. Optional. Perform smoke testing.

   You can perform smoke testing on the task in the development environment to check whether the task is run as expected when you commit the task or after you commit the task. For more information, see Perform smoke testing.

Step 5: Commit and deploy the task

After a task on a node is configured, you must commit and deploy the task. After you commit and deploy the task, the system runs the task on a regular basis based on scheduling configurations.

1. Click the icon in the top toolbar to save the task.

2. Click the icon in the top toolbar to commit the task.

   In the Submit dialog box, configure the Change description parameter. Then, determine whether to review task code after you commit the task based on your business requirements.

   Note

   • You must configure the Rerun and Parent Nodes parameters on the Properties tab before you commit the task.

   • You can use the code review feature to ensure the code quality of tasks and prevent task execution errors caused by invalid task code. If you enable the code review feature, the task code that is committed can be deployed only after the task code passes the code review. For more information, see Code review.

If you use a workspace in standard mode, you must deploy the task in the production environment after you commit the task. To deploy a task on a node, click Deploy in the upper-right corner of the configuration tab of the node. For more information, see Deploy tasks.

What to do next

Task O&M: After you commit and deploy the task, the task is periodically run based on the scheduling configurations. You can click Operation Center in the upper-right corner of the configuration tab of the corresponding node to go to Operation Center and view the scheduling status of the task. For more information, see View and manage auto triggered tasks.