Dataphin:Configure the PostgreSQL input component

Prerequisites

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

• Your account must have read permission on the data source. If you do not have the required permission, apply for it. For more information, see Apply for, renew, and revoke permissions on a data source.

Procedure

1. In the top navigation bar of the Dataphin homepage, choose Develop > data integration.

2. In the top navigation bar of the integration page, select a project. If you are in Dev-Prod mode, you must also select an environment.

3. In the left navigation bar, click Offline Integration, and in the Offline Integration list, click the offline pipeline that you want to develop 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 left-side navigation pane of the component library panel, select Input. Find the PostgreSQL component in the list on the right and drag it to the canvas.

6. On the PostgreSQL input component card, click the icon to open the PostgreSQL Input Configuration dialog box.

7. In the PostgreSQL Input Configuration dialog box, configure the following parameters.

   Parameter	Description
   Step name	Dataphin automatically generates a step name, which you can modify. The name must meet the following requirements: Can contain only Chinese characters, letters, underscores (_), and digits. Must be 64 characters or less.
   Data source	The drop-down list shows all PostgreSQL data sources, including those for which you have read permission and those for which you do not. Click the icon to copy the current data source name. If you do not have read permission for a data source, click Apply to request it. For more information, see Apply for, renew, and revoke permissions on a data source. If you do not have a PostgreSQL data source, click Create Data Source to create one. For more information, see Create a PostgreSQL data source.
   Time zone	Time-based data is processed using the specified time zone. This parameter defaults to the time zone configured for the selected data source and cannot be changed. Note For tasks created before version 5.1.2, you can select either Data source default configuration or Channel configuration time zone. The default option is Channel configuration time zone. Data source default configuration: The default time zone of the selected data source. Channel configuration time zone: The time zone set for the current integration task in Properties > channel configuration.
   Schema (Optional)	Select the schema that contains the source table. If you do not specify a schema, the system defaults to the one configured for the data source.
   Number of source tables	Select the number of source tables: single table or multiple tables. single table: Synchronizes data from one source table to one destination table. multiple tables: Synchronizes data from multiple source tables to a single destination table. When data from multiple tables is written to one table, a UNION operation is performed.
   Table matching mode	Select either general rule or database regular expression. Note This setting is available only when Number of source tables is set to Multiple tables.
   Table	Select the source table or tables. If Number of source tables is set to single table, search for a table by entering keywords, or enter the exact table name and click Precise Search. After selecting a table, the system automatically checks its status. You can click the icon to copy the selected table name. If you select multiple tables for number of source tables, you can enter different expressions to add tables based on the table matching mode. If general rule is selected: Enter an expression to filter for tables with the same structure. The system supports enumerations, regular expression-like patterns, and a mix of both. For example: table_[001-100];table_102;. If database regular expression is selected: Enter a regular expression that your database supports. The system uses this expression to match tables in the source database. At runtime, the task dynamically matches the latest set of tables based on the expression for synchronization. After you enter the expression, click Precise Search to view a list of matched tables in the Confirm Match Details dialog box.
   Split key (Optional)	The system splits the data into shards based on the specified split key. This enables parallel reads based on the configured concurrency. You can use any column from the source table as the split key. For better performance, we recommend using a primary key or an indexed column as the split key. Important If you select a column of a date or time type, the system identifies the minimum and maximum values and splits the data based on the total time range and the concurrency setting. The data distribution across shards is not guaranteed to be even.
   Batch read size (Optional)	The number of records to read in a single batch. Instead of reading records one by one, you can specify a batch size (for example, 1,024) to reduce the number of interactions with the data source, improve I/O efficiency, and lower network latency.
   Input filter (Optional)	Enter the filter condition for the input field, such as ds=${bizdate}. The Input filter applies to the following two scenarios: Filtering for a fixed subset of data. Filtering with parameters.
   Output field	This section displays all fields from the selected source table or tables. You can perform the following operations: Field Management: You can remove fields that you do not want to output to downstream components. To remove a single field: In the row for the field, click the icon in the Actions column. To remove fields in batches: Click Field Management. In the Field Management dialog box, select the fields that you want to remove, click the left arrow icon to move them to the unselected list, and then click OK. Add Fields in Batches: Click Add Fields in Batches to add fields by using JSON, TEXT, or DDL format. Note After you click OK, the new fields overwrite the existing field configuration. To add fields in JSON format, use the following example: // Example: [{ "index": 0, "name": "id", "type": "int(10)", "mapType": "Long", "comment": "comment1" }, { "index": 1, "name": "user_name", "type": "varchar(255)", "mapType": "String", "comment": "comment2" }] Note The index parameter specifies the column number, name specifies the output field name, and type specifies the field type. For example, "index":3,"name":"user_id","type":"String" imports the fourth column from the source, names it user_id, and sets its type to String. To add fields in TEXT format, use the following example: // Example: 0,id,int(10),Long,comment1 1,user_name,varchar(255),Long,comment2 The row delimiter separates the information for each field. The default is a newline character (\n). Other supported delimiters include the semicolon (;) and period (.). The column delimiter is used to separate field names from field types. The default is a half-width comma (,). The character ',' is supported. The field type can be omitted, and it defaults to ','. To add fields in DDL format, use the following example: CREATE TABLE tablename ( user_id serial, username VARCHAR(50), password VARCHAR(50), email VARCHAR (255), created_on TIMESTAMP, ); New Output Field: Click + New Output Field, specify the field, type, and comment, and select a mapping type. After configuring the row, click the icon to save it. System fields: You can select system fields to identify the source of each row of data. This is useful when you synchronize data from multiple tables, such as sharded databases and tables, to a single destination table. When you select a system field, the system automatically populates its field type and mapping type. Available system fields include: dp_datasource: The name of the source data source. dp_dbname: The name of the source database, if applicable. dp_schema: The name of the source schema, if applicable. dp_source_table: The name of the source table. dp_read_timestamp: The timestamp when the SELECT operation started at the source. You can concatenate multiple system fields, such as dp_dbname.dp_source_table. Note You cannot modify the field type or mapping type of system fields. The dp_read_timestamp field must be used alone and cannot be concatenated with other system fields.

8. Click OK to save the configuration for the PostgreSQL input component.