Configure the TDSQL for PostgreSQL output component

Prerequisites

• You have created a TDSQL for PostgreSQL data source. For more information, see Create a TDSQL for PostgreSQL data source.

• Your account must have the sync write permission for the data source. If you do not have the permission, you must apply for it. For more information, see Apply for, renew, and return data source permissions.

Procedure

1. In the top menu bar of the Dataphin homepage, choose Development > Data Integration.

2. In the top menu bar of the integration page, select a Project. If you are in Dev-Prod mode, you also need to select an environment.

3. Click Offline Integration in the left navigation bar, and then click the offline pipeline that you want to develop in the Offline Integration list to open its configuration page.

4. In the upper-right corner of the page, click Component Library to open the Component Library panel.

5. In the Component Library panel, click Output in the left-side navigation. Find the TDSQL for PostgreSQL component and drag it to the canvas.

6. Drag a connection from the icon on the source component to the TDSQL for PostgreSQL output component.

7. Click the icon on the TDSQL for PostgreSQL output component card to open the TDSQL for PostgreSQL Output Configuration dialog box.

   

8. In the TDSQL for PostgreSQL Output Configuration dialog box, configure the following parameters.

   Parameter		Description
   Basic settings	Step Name	The name of the TDSQL for PostgreSQL output component. Dataphin automatically generates a step name, which you can change. The naming rules are as follows: Can contain only Chinese characters, letters, underscores (_), and digits. Cannot exceed 64 characters in length.
   	Data source	In the drop-down list, select a TDSQL for PostgreSQL data source. The list displays all data sources of this type, regardless of whether you have the required sync write permission. Click the icon to copy the data source name. If you lack sync write permission for a data source, click Apply next to it to request the permission. For more information, see Apply for data source permissions. If you do not have a TDSQL for PostgreSQL data source, click the icon to create one. For more information, see Create a TDSQL for PostgreSQL data source.
   	Schema (Optional)	You can select tables across different schemas. Select the schema where the table resides. If you do not specify one, Dataphin uses the schema configured in the data source by default.
   	Table	Select the target table. You can search for a table by entering a keyword, or enter the exact table name and click Exact Search. After you select a table, the system automatically checks its status. Click the icon to copy the selected table name. If the target table does not exist in the TDSQL for PostgreSQL data source, you can use the one-click table creation feature to create it. The steps are as follows: Click one-click table creation. Dataphin automatically generates a table creation script, which includes a target table name (defaults to the source table name) and field types converted from the Dataphin types in the source. Modify the SQL script if necessary, and then click Create. After the table is created, Dataphin automatically sets it as the target table. Note If a table with the same name already exists in the development environment, Dataphin returns an error when you click Create. If no match is found, you can still perform the integration by manually entering a table name.
   	Missing production table policy	Specifies the action to take if the production table does not exist. You can select Do Nothing or Create Automatically. The default is Create Automatically. If you select Do Nothing, the task is published without creating the production table. If you select Create Automatically, the system creates a table with the same name in the target environment when the task is published. Do Nothing: If the target table does not exist, the system displays a message upon submission, but you can still publish the task. In this case, you must manually create the target table in the production environment before the task can run successfully. Create Automatically: You must Edit Table Creation Statement. The system pre-fills the creation statement for the selected table, which you can adjust. In the statement, the table name uses the placeholder ${table_name}, and only this placeholder is supported. At runtime, the system replaces this placeholder with the actual table name. If the target table does not exist, the system attempts to create it based on the statement. If table creation fails, the publish check fails. You can then fix the statement based on the error message and publish again. If the target table already exists, the creation step is skipped. Note This parameter is supported only in Dev-Prod mode projects.
   	Loading Policy	Select the strategy for writing data to the target table. The Loading Policy options include: append data (insert into): If a primary key or constraint conflict occurs, the system reports a dirty data error. update on primary key conflict (on conflict do update set): If a primary key or constraint conflict occurs, the system updates the data in the mapped fields of the existing record.
   	Synchronous write	The update-on-conflict operation is not atomic. If the data to be written contains duplicate primary keys, you must Enable synchronous write. Otherwise, the system uses parallel writing. Synchronous writing has lower performance than parallel writing. Note This option is available only when you set the Loading Policy to update on primary key conflict.
   	Batch write size (Optional)	The amount of data to write in a single batch. You can also set the batch write count. During the write process, the system writes data whichever limit is reached first. The default is 32 MB.
   	Batch write count (Optional)	The default is 2,048 rows. Data synchronization uses a batching strategy configured by the batch write count and batch write size parameters. When the accumulated data reaches either the count or size limit, the system considers the batch full and immediately writes it to the target. We recommend setting the batch write size to 32 MB. You can adjust the batch write count based on the size of a single record. A larger value improves the efficiency of batch writing. For example, if a single record is about 1 KB, you can set the batch write size to 16 MB and the batch write count to a value greater than 16,384 (16 MB / 1 KB), such as 20,000. With this configuration, the system triggers a write operation each time the accumulated data reaches 16 MB.
   	Pre-SQL statement (Optional)	An SQL script that runs on the database before the data import begins. For example, to ensure continuous service availability, you can run a pre-SQL statement that creates a target table named Target_A. After the current step writes data to Target_A, a post-SQL statement can rename the active service table Service_B to Temp_C, rename Target_A to Service_B, and then delete Temp_C.
   	Post-SQL statement (Optional)	An SQL script that runs on the database after the data import is complete.
   Field Mapping	Input fields	Displays the input fields based on the output of the upstream component.
   	Output fields	Displays the output fields. You can perform the following actions: Field Management: Click Field Management to select output fields. Click the icon to move fields from Selected Input Fields to Unselected Input Fields. Click the icon to move fields from Unselected Input Fields to Selected Input Fields. Batch Add: Click Batch Add to configure fields in bulk by using the JSON, TEXT, or DDL format. Example of JSON format: // Example: [{ "name": "user_id", "type": "String" }, { "name": "user_name", "type": "String" }] Note name specifies the name of the field to import, and type specifies its data type. For example, "name":"user_id","type":"String" imports the field named user_id and sets its data type to String. Example of TEXT format: // Example: user_id,String user_name,String The row delimiter separates each field definition. The default is a newline character (\n). The supported delimiters are the newline character (\n), semicolon (;), and period (.). The column delimiter separates the field name from the field type. The default is a comma (,). Example of DDL format: CREATE TABLE tablename ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); Add a new output field: Click +New Output Field, enter a Field name, select a Type, and click the icon to save the row.
   	Mapping relation	You can manually map fields from the input to the target table. Quick Mapping options include map by position and map by name. map by name: Maps fields that have the same name. map by position: Maps fields that are in the same column position, regardless of their names. This is useful when the field names of the source and target tables do not match but their column order does.

9. Click Confirm to save the configuration for the TDSQL for PostgreSQL output component.