DataWorks:PostgreSQL

Background information

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

Prerequisites

Before you begin, ensure that you have:

• A Business Flow — DataStudio organizes development by Business Flows. For more information, see Create a workflow.

• A PostgreSQL data source added to DataWorks using a Java Database Connectivity (JDBC) connection string. PostgreSQL nodes support only JDBC-based data sources. For more information, see PostgreSQL data source.

• (Optional; required for RAM users) The RAM user added to the workspace with the Develop or Workspace Administrator role assigned. Grant the Workspace Administrator role with caution due to its elevated privileges. For more information, see Add members to a workspace.

Supported regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), Germany (Frankfurt), US (Silicon Valley), and US (Virginia).

Step 1: Create a 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 O&M > Data Development. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Development.

2. Right-click the target business flow and choose Create Node > Database > PostgreSQL.

3. In the Create Node dialog box, enter a Name for the node and click OK.

Step 2: Develop the PostgreSQL task

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

Use scheduling parameters

DataWorks scheduling parameters let you pass dynamic values to your SQL at runtime. Define variables in your code using ${variable_name}, then assign values in the Scheduling parameter section on the Schedule tab.

The following example passes the table type as a runtime variable. The scheduling parameter a is set to BASE_TABLE at runtime.

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

For supported formats and configuration details, see Supported formats of scheduling parameters and Configure and use scheduling parameters.

Step 3: Configure task scheduling

Click Scheduling Configuration on the right and configure the scheduling properties. For more information, see Overview.

Step 4: Debug the task

1. (Optional) Select a debugging resource group and assign parameter values. Click the icon in the toolbar. In the Parameters dialog box, select a resource group and assign values to any scheduling parameters used in the task. For more information about parameter assignment logic, see Task debugging process.

2. Save and run the task. Click the icon to save, then click the icon to run.

3. (Optional) Run a smoke test. Run a smoke test during or after submission to verify execution in the development environment. For more information, see Perform smoke testing.

Step 5: Submit and publish the task

Submit and publish the node to activate scheduling.

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

2. Click the icon in the toolbar to submit the node. In the Submit dialog box, enter a Change Description and select code review options.

   Configure the Rerun Property and Upstream Dependent Node before submitting. If code review is enabled, a reviewer must approve the code before publication. For more information, see Code review.

3. In standard mode workspaces, click Publish in the upper-right corner to deploy to production. For more information, see Publish tasks.

What's next

After the task is published, it runs on a recurring schedule based on the node's configuration. Click O&M in the upper-right corner of the node configuration tab to go to Operation Center, where you can view the scheduling and running status of the task. For more information, see Manage recurring tasks.