Create an offline dataset by using SQL processing

Prerequisites

Before you can create an offline dataset, you must first establish the tag project to which it will be associated. For more information, see Create a tag project.

Procedure

1. On the Dataphin home page, click the top menu bar Tag > Tag Workbench.

2. In the top menu bar, select Project.

3. In the left-side navigation pane, select Data Preparation > Offline Dataset.

4. On the Offline Dataset page, click Add Dataset. In the Add Offline Dataset dialog box, select SQL Processing.

5. On the New SQL Processing configuration page, configure the dataset's Basic Information, Processing Logic, and O&M Configuration.

   • Basic Information

     Parameter	Description
     Dataset Name	Enter the name information of the dataset. Supports Chinese, English, numbers, and underscores (_), within 64 characters.
     Dataset Code	The unique identifier of the offline dataset. When there are duplicate offline dataset names, it helps you locate the specific offline dataset. Must start with a letter, allowing lowercase English letters, numbers, and underscores (_), within 64 characters.
     Dataset Update Method	Supports Periodic Update and Manual Update methods. Periodic Update: Automatically updates the dataset at a certain time interval. Manual Update: Updates the dataset through manual operation.
     Owner	Please select the owner of the offline dataset.
     Description	Enter a brief description of the offline dataset, within 1000 characters.

   • Processing Logic

     Parameter	Description
     Processing Logic (SQL)	Enter the SQL code for processing the data source. The SQL code is explained as follows: Construct the mapping relationship between Entity and Metric through SQL processing. The SELECT query must have at least two fields. The system usually parses the first field as the Entity, for example, subjectId in SQL; the subsequent fields are parsed as Metrics, for example, indicatorA and indicatorB. If the parsing of the entity identifier is inaccurate, you can adjust it in the metric definition below. Supports cross-project table processing and can use physical tables and logical tables. The Physical Table Name format is project_en_name.source_table; the Logical Table Name format is data_section_en_name.logical_table. SELECT subjectId, indicatorA, indicatorB FROM project_en_name.source_table WHERE ds = ${bizdate};
     Metric Configuration	After completing the Processing Logic, you need to click Metric Parsing to parse the metric configuration list of the SQL. Entity Id-value Type: Select the field and value type of the entity ID, which will be used for automatic mapping of the entity ID during tag processing. Only supports Long Integer and String. Metric Search: Supports searching metrics by entering Metric Name or Description. Configure Lookup Table: Supports configuring lookup tables for Integer, Decimal(M,0), Boolean, and String fields. Click to enter the Configure Lookup Table dialog box. In the Configure Lookup Table dialog box, configure the relevant parameters. Configure Lookup Table: By default, it is not configured. You can select Reference Tables to configure the corresponding lookup table for the metric. Lookup Table Source: Currently, only Manual Configuration is supported. Lookup Table Name: Enter the name of the lookup table. Supports Chinese, English, numbers, and special characters, within 128 characters. Lookup Table Description: Enter a brief description of the lookup table, within 1000 characters. Code Information: Supports single input and batch input, up to 500 groups. Single Input: Click Add Code Value, enter Code Value and Code Name, both must not be empty and must be unique. In addition, the type of the code value needs to match the value type of the metric. You can click to delete the current row. Batch Input: Click Batch Input, and in the Batch Input Code Information dialog box, batch input code values and code names, each group is separated by a line, and the code value and code name are separated by a colon (:). Click Click To Identify, and the system will automatically parse the code information in the batch input boxand fill it into the code information list. One-click Purge: Click One-click Purge, and the system will automatically clear the information list. Click OK to complete the code value configuration. Note When batch inputting code information, if there are duplicate code values or code names, after clicking OK, the system will automatically locate the first error row. Actions: Click to delete the current metric.

   • O&M Configuration

     Note

     If the dataset update method is Manual Update, no configuration is required.

     1. Scheduling Cycle

        • Planned update time: Supports scheduling at a specific time of day. This means the task runs automatically once a day, and you can specify the time point according to your needs.

        • Scheduling Run Plan: Click Preview. The scheduling run plan displays all scheduling instances and their scheduling types for each day of a specific month according to the configured scheduling cycle and conditional scheduling. The preview date type can be selected according to Data Timestamp or Run Date (Scheduling Date).

          If there are multiple scheduling type statuses for all instances in one day, all included scheduling type statuses are displayed by color, and the name of each scheduling type status and the corresponding number of instances are displayed. For example, the figure below shows that on the 4th of a certain month, the current scheduling task has 44 normal scheduling instances, 2 paused instances, and 12 dry-run instances.

          Hover the mouse over the scheduling type module of a certain day to view the detailed scheduling instance list of the current scheduling task on that day, including scheduling type, scheduling conditions, and condition names.

        • Conditional Scheduling: You can set multiple scheduling conditions. The system evaluates these conditions sequentially from top to bottom. When a condition is met, the corresponding schedule is executed and the evaluation of any remaining conditions ceases. If no conditions are met, the system executes the default scheduling configuration. For more information, see Conditional Scheduling Rule Description.

          Important

          Conditional scheduling is only effective when the scheduling type is Normal Scheduling.

     2. Scheduling Dependency

        Scheduling dependency refers to the upstream and downstream dependency relationship between nodes. In Dataphin, the downstream task node will start running only after the upstream task node has completed running successfully.

        • Automatic Parsing

          The system will automatically parse the upstream dependency nodes based on task lineage and associate them. Data updates will depend on the output of upstream data.

          Note

          • If the result of automatic parsing does not meet expectations, you can click to close the effective button. After closing, it means not selecting to depend on the node.

          • Default is to depend on the current cycle.

        • Add Dependency

          If Automatic Parsing cannot parse the scheduling dependency relationship or the upstream dependency configuration generated by Automatic Parsing does not match the actual application, you can manually add the node's Upstream Dependency.

          Click Add Dependency, select to add Physical Node or Logical Table Node, and in the pop-up dialog box, select one or more target physical or logical table nodes, then click OK.

          Note

          • If Intelligent R&D version is not purchased, only Physical Node dependency is supported.

          • After manually adding dependencies, click automatic parsing again. If the parsed nodes generated are consistent with the manually added dependency nodes, the system will automatically perform an overwrite operation.

        • Edit Dependency

          In the scheduling dependency list, click the Actions column icon of the target upstream dependency table. A dialog box will appear where you can modify the Dependency Cycle, Dependency Policy, and Dependency Field (modification is supported only for logical table nodes). For details on dependency configuration and description, see Configure Offline Task Scheduling Dependency, Scheduling Dependency Scene Rules and Examples.

          Click the Actions column of the target upstream dependency table icon to delete the corresponding dependency node.

6. Click Save And Publish to complete the creation of the offline dataset.

   Note

   After saving successfully, you can click Data Preview. The system will display the corresponding data information based on the configured processing logic to help you verify whether the processing logic is correct.

What to do next

After you have created and configured the offline dataset, you can proceed to create corresponding offline tags for it. For more information, see Offline Tags.