Dataphin:Configure the Microsoft SQL Server input component

Prerequisites

• You have created a Microsoft SQL Server data source. For more information, see Create a Microsoft SQL Server data source.

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

Procedure

1. In the top navigation bar of the Dataphin homepage, choose R&D > Data Integration.

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

3. In the left-side navigation pane, click Offline Integration. In the Offline Integration list, click the offline pipeline to open its configuration page.

4. Click Component Library in the upper-right corner of the page to open the Component Library panel.

5. In the left-side navigation pane of the Component Library panel, select Input. In the input component list on the right, find the Microsoft SQL Server component and drag it onto the canvas.

6. Click the icon on the Microsoft SQL Server input component card to open the Microsoft SQL Server Input Configuration dialog box.

7. In the Microsoft SQL Server Input Configuration dialog box, configure the following parameters.

   Parameter	Description
   Step name	The name of the component. Dataphin generates a default step name, which you can customize. It can contain only Chinese characters, letters, underscores (_), and digits. Its length cannot exceed 64 characters.
   Data source	In the drop-down list, select a data source. The list displays all Microsoft SQL Server data sources, regardless of whether you have the read permission for synchronization. You can click the icon to copy the current data source name. If you lack the read permission for synchronization for a data source, click Apply next to it to request access. For more information, see Apply for, renew, and return data source permissions. If you do not have a Microsoft SQL Server data source, click Create Data Source to create one. For more information, see Create a Microsoft SQL Server data source.
   Time zone	This parameter specifies the time zone for processing time-based data. By default, it inherits the time zone from the selected data source and is not modifiable. Note For tasks created before version V5.1.2, you can select 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 configured for the current integration task in Properties > Channel Configuration.
   Schema (Optional)	You can select tables across schemas. Select the schema that contains the table. If left blank, the schema configured for the data source is used by default.
   Number of source tables	Select the number of source tables. Options include single table and multiple tables: Single table: Use this option to synchronize business data from a single source table to a single target table. Multiple tables: Use this option to synchronize business data from multiple source tables to the same target table. The system uses the UNION algorithm to combine data from multiple tables into a single target table.
   Table matching method	You can select general rule or database regular expression. Note This parameter 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, you can search by entering keywords from the table name, or enter the exact table name and click Exact Match. After you select a table, the system automatically checks its status. You can click the icon to copy the name of the selected table. If number of source tables is set to multiple tables, you can add tables by entering expressions based on the selected table matching method. If the matching method is general rule: In the input box, enter an expression to select tables that have the same structure. The system supports enumeration, regular-expression-like patterns, and a mix of both. For example, table_[001-100];table_102;. If the matching method is database regular expression: In the input box, enter a regular expression supported by the current database. The system uses this expression to match tables in the target database. At runtime, the task dynamically matches the latest set of tables for synchronization based on the database regular expression. After you enter the expression, click Exact Match. In the Confirm Match Details dialog box, view the list of matched tables.
   Split key (Optional)	The system splits the data into shards based on the configured split key field. This enables parallel data reading in conjunction with the concurrency setting. You can use a column from the source table as the split key. For optimal transfer performance, we recommend using a primary key or an indexed column as the split key. Important If you select a date or time data type, the system identifies the minimum and maximum values and splits the data across the total time range based on the concurrency level. This method does not guarantee an even split.
   Fetch size (Optional)	The number of records to read in a single batch. By specifying a fetch size, such as 1024, the system retrieves records in batches. This reduces data source interactions, improves I/O efficiency, and lowers network latency.
   Input filter (Optional)	Enter a filter condition for the input data, for example, ds=${bizdate}. The input filter is useful in the following scenarios: Filtering a fixed subset of data. Filtering with parameters.
   Hint (Optional)	By using specific hints, you can override the query optimizer's default execution plan. To apply hints, put them in parentheses after the table name in the FROM clause and separate multiple hints with commas. For example: SELECT * FROM MyTable WITH (INDEX(IX_MyIndex), NOLOCK)
   Output field	This section displays all fields from the selected table or tables that match the filter criteria. You can perform the following operations: Field Management: To exclude fields from being sent to downstream components, remove them: To delete a single field: Click the icon in the Actions column to remove the field. To delete multiple fields in a batch: Click Field Management. In the Field Management dialog box, select multiple fields, click the left arrow icon to move the selected input fields to the unselected list, and then click OK. Batch Add: Click Batch Add to configure fields in a batch using JSON, TEXT, or DDL format. Note After you click OK, the batch-added fields will overwrite any existing field configurations. To configure in JSON format, for example: // Example: [ { "index": 1, "name": "Id", "type": "int(10)", "mapType": "Long", "comment": "" }, { "index": 2, "name": "Data", "type": "nvarchar(100)", "mapType": "String", "comment": "" } ] Note index specifies the source column number, name is the destination field name, and type is the destination field type. For example, "index":3,"name":"user_id","type":"String" imports the fourth column from the file, names the field user_id, and sets its type to String. To configure in TEXT format, for example: // Example: 1,Id,int(10),Long, 2,Data,nvarchar(100),String, 3,RowVersion,timestamp,Bytes, The row delimiter separates the information for each field. The default is the newline character (\n). The semicolon (;) and period (.) are also supported. The column delimiter separates field names from field types. The default is a comma (,). The supported delimiter is ','. The field type is optional, and the default is ','. To configure in DDL format, for example: CREATE TABLE tablename ( user_id serial, username VARCHAR(50), password VARCHAR(50), email VARCHAR (255), created_on TIMESTAMP, ); Create an output field: Click + Create Output Field and specify the Field, Type, Remarks, and Mapping Type. After you configure the row, click the icon to save. System field: You can select system fields to identify the origin of each data row. After you select one, the system automatically populates the field type and mapping type. This is useful when you synchronize data from multiple tables (such as sharded databases and tables) to the same target table. System fields include: dp_datasource: The name of the data source being synchronized. dp_dbname: The name of the database being synchronized (if any). dp_schema: The name of the schema being synchronized (if any). dp_source_table: The name of the source table being synchronized. dp_read_timestamp: The start time of the SELECT operation at the source. You can concatenate multiple system variables to form a system field, for example, dp_dbname.dp_source_table. Note The field type and mapping type of a system field cannot be modified. dp_read_timestamp must be configured as a standalone field and cannot be concatenated with other variables.

8. Click OK to save the component's configuration.