PostgreSQL node

Background information

PostgreSQL is a powerful and flexible open-source relational database management system (RDBMS). It features a robust data model, high scalability and stability, and a rich set of core functionalities. For more information, see PostgreSQL.

Prerequisites

• Create a workflow.

  In DataStudio, you develop tasks for different engines within workflows. Therefore, you must create a workflow before you create a node. For more information, see Create a workflow.

• A PostgreSQL data source is created.

  You must first add your PostgreSQL database as a DataWorks PostgreSQL data source before you can access the data in the database. For more information, see PostgreSQL data source.

  Note

  PostgreSQL nodes support only PostgreSQL data sources that are created with a JDBC connection string.

• (Optional, for RAM users) Add the RAM user for task development to the workspace and grant them the Development or Workspace Administrator role. The Workspace Administrator role has extensive permissions, so assign it with caution. For more information about how to add members and grant roles, see Add members to a workspace.

Limits

This feature is available in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), Germany (Frankfurt), US (Silicon Valley), and US (Virginia).

Step 1: Create PostgreSQL node

1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.

2. Right-click the target workflow and choose Create Node > Database > POSTGRESQL.

3. In the Create Node dialog box, enter a Name for the node and click OK. Once the node is created, you can develop and configure the task in the node's editor.

Step 2: Develop PostgreSQL task

(Optional) Select PostgreSQL data source

If your workspace has multiple PostgreSQL data sources, select the one you need on the node editor page. If there is only one data source, DataWorks uses it by default.

Develop SQL code: simple example

In the code editor of the PostgreSQL node, write the SQL script for your task. The following example shows how to query information about all base tables in a PostgreSQL database:

SELECT * FROM information_schema.tables
WHERE table_type = 'BASE TABLE';

Develop SQL code: Use scheduling parameters

DataWorks provides Scheduling Parameter to dynamically pass values to your code in scheduled jobs. You can define variables in your task code by using the ${variable_name} format. Then, in the right-side pane of the node editor, go to Scheduling Settings > Scheduling Parameter to assign a value to each variable. For more information about supported formats and configuration, see Formats of scheduling parameters and Configure and use scheduling parameters.

Example: Querying information about all base tables in a database. Set the value of the parameter a to BASE_TABLE.

Sample code:

SELECT * FROM information_schema.tables
WHERE table_type = '${a}';

Step 3: Configure task scheduling

If you need to run the task on a schedule, click Scheduling in the right-side navigation pane of the node editor. Configure the scheduling information for the node based on your requirements. For more information, see Configure scheduling properties for a task.

Step 4: Debug the task

Debug the task to verify that it runs as expected:

1. Select a resource group to run the code and assign values to custom parameters.

   • Click the icon in the toolbar. In the Parameter dialog box, select the scheduling resource group to use for debugging.

   • If you use scheduling parameter variables in your code, assign debugging values to them here. For more information about the parameter assignment logic, see Debug a task.

2. Save and run the code.

   Click the icon in the toolbar to save the code. Then, click the icon to run the task.

3. (Optional) Perform smoke testing.

   To verify that a scheduled task runs as expected, you can perform smoke testing in the development environment when you submit the node or afterward. For more information, see Perform smoke testing.

Step 5: Submit and deploy the task

After you configure the node, you must submit and deploy it. The node will then run periodically based on its scheduling configuration.

1. Click the icon in the toolbar to save the node.

2. Click the icon in the toolbar to submit the node task.

   In the Submission dialog box, enter a Change Description, and select whether to request a code review after submitting the node.

   Note

   • You must set the Rerun attribute and Parent Nodes of a node to submit it.

   • The code review feature ensures code quality and prevents errors that can occur when unverified code is deployed to the production environment. If you enable this feature, a reviewer must approve the submitted code before deployment. For more information, see Code review.

If your workspace is in standard mode, submit the task and then click Deploy in the upper-right corner of the node editor page to deploy it to the production environment. For more information, see Deploy tasks.

Next steps

Task operations: After a task is committed and published, it runs periodically based on the node's configuration. You can click O&M in the top-right corner of the node editing page to go to the Operation Center and view the scheduling and running status of the scheduled task. For more information, see Managing Scheduled Tasks.