Convert incremental data from Tablestore to a full data format

Data Studio (legacy)

1. Go to the Data Development page.

   1. Log on to the DataWorks console.

   2. In the top navigation bar, select a resource group and region.

   3. In the navigation pane on the left, click Data Development and O&M > Data Development.

   4. On the Data Development page, select the target workspace from the drop-down list and click the Go to Data Development button.

2. Create a JAR resource.

   1. In the DataStudio console, on the Data Studio page, click New and select New Resource > MaxCompute > JAR.

      Alternatively, open the business flow, right-click MaxCompute, and select New Resource > JAR.

   2. In the New Resource dialog box, configure the parameters and click Create.

      Important

      • If the resource has not been uploaded using the MaxCompute client, select the Upload as ODPS Resource check box. If the resource has already been uploaded using the MaxCompute client, clear the Upload as ODPS Resource check box.

      • If you select Upload as ODPS Resource, the resource is stored in both DataWorks and MaxCompute.

      • The resource name must end with .jar.

3. Commit the JAR resource.

   1. Click the icon.

   2. In the Commit dialog box, enter a description as needed.

   3. Click Confirm.

Data Studio (new version)

1. You can go to the Resource Management page.

   1. Log on to the DataWorks console.

   2. In the top navigation bar, select a resource group and region.

   3. In the left navigation pane, click Data Development and O&M > Data Studio.

   4. On the Data Development page, select the desired workspace from the drop-down list and click Go To DataStudio.

   5. In the navigation pane on the left, click the Resource Management icon. The Resource Management page appears.

2. Create a JAR resource.

   1. On the Resource Management page, click the icon and choose New Resource > MaxCompute Jar.

   2. In the New Resource and Function dialog box, you can select a path, enter a resource name, and click Confirm.

      Important

      The resource name must end with .jar.

   3. On the configuration tab of the new resource, configure the upload parameters as described in the following table.

      Parameter	Description
      File Source	The source of the object file. Options include Local and OSS.
      File Content	If you select Local, click Click to upload in the Upload File section to upload a local file. If you select OSS, select the OSS file from the Select File drop-down list.
      Data Source	The data source to which the uploaded MaxCompute resource belongs.

   4. To save the configuration, click Save.

3. Publish the JAR resource.

   1. Click Publish.

   2. On the Publish tab of the resource, optionally enter a description for the release and click Start Publishing to Production.

   3. Follow the process guide and click Confirm Publish.

Data Studio (legacy)

1. Create a function.

   1. On the Data Studio page in the DataStudio console, right-click the target business flow and choose New Function > MaxCompute > Function.

   2. In the New Function dialog box, you can select a path and enter a function name.

   3. Click Create.

2. Register the function.

   Configure the function parameters as described in the following table, and then click the icon to save the configuration.

   Parameter	Description
   Function Type	Select Other Function.
   Class Name	Enter the class name based on the type and pattern of the Tablestore source table. Note You can determine whether the Tablestore source table is a single-version or multi-version table by its MaxVersion property. Single-version table Single-version pattern Class name: com.aliyun.ots.stream.utils.mergecell.oneversion.MergeCell Multi-version table Multi-version pattern V1 Class name: com.aliyun.ots.stream.utils.mergecell.multiversion.MergeCellV1 Multi-version pattern V2 Class name: com.aliyun.ots.stream.utils.mergecell.multiversion.MergeCellV2 Important Multi-version pattern V1 and V2 are two different policies. Multi-version pattern V1 manages versions at the row level. In the output, each version retains a complete snapshot of the entire row. Multi-version pattern V2 manages versions at the column level. In the output, the versions of different columns are independent, and only the incremental changes of the columns are retained.
   Resources	Select the name of the resource you created in Step 1.

3. Commit the function.

   1. Click the icon.

   2. In the Commit dialog box, enter an optional description for the commit.

   3. Click Confirm.

Data Studio (new version)

1. Create a function.

   1. On the Resource Management page, click the icon, and then choose New Function > MaxCompute Function.

   2. In the New Resource and Function dialog box, select a path, enter a function name, and click Confirm.

   3. On the configuration tab of the new function, configure the function parameters as described in the following table.

      Parameter	Description
      Function Type	Select OTHER (Other Function).
      Data Source	The data source to which the MaxCompute function belongs.
      Class Name	Enter the class name based on the type and pattern of the Tablestore source table. Note You can determine whether the Tablestore source table is a single-version or multi-version table by its MaxVersion property. Single-version table Single-version pattern Class name: com.aliyun.ots.stream.utils.mergecell.oneversion.MergeCell Multi-version table Multi-version pattern V1 Class name: com.aliyun.ots.stream.utils.mergecell.multiversion.MergeCellV1 Multi-version pattern V2 Class name: com.aliyun.ots.stream.utils.mergecell.multiversion.MergeCellV2 Important Multi-version pattern V1 and V2 are two different policies. Multi-version pattern V1 manages versions at the row level. In the output, each version retains a complete snapshot of the entire row. Multi-version pattern V2 manages versions at the column level. In the output, the versions of different columns are independent, and only the incremental changes of the columns are retained.
      Type	Select Resource Function.
      Resources	Select the name of the resource you created in Step 1.

   4. To save the configuration, click Save.

2. Publish the function.

   1. Click Publish.

   2. On the Publish tab, you can optionally enter a description for the release and then click Start Publishing to Production.

   3. Follow the process guide and click Confirm Publish.

Data Studio (legacy)

1. Create an ODPS SQL node.

   1. On the Data Studio page in the DataStudio console, click New and select New Node > MaxCompute > ODPS SQL.

      Alternatively, you can right-click the target business flow and choose New Node > MaxCompute > ODPS SQL.

   2. In the New Node dialog box, select a path and enter a name.

   3. Click Confirm.

2. On the node editing page, write the ODPS SQL statement.

   The format of the SQL statement is as follows:

   SELECT function_name(para_list) 
   AS (custom_para_list)
   FROM(
       SELECT * FROM stream_table_name 
       DISTRIBUTE BY primary_keys 
       SORT by primary_keys, SequenceID
   )t;

   The following table describes the parameters.

   Parameter	Description
   function_name	The function name. Enter the name of the function you created in Step 2.
   para_list	The parameter list. Configure the parameters for the Tablestore source table and the MaxCompute incremental table.
   custom_para_list	The custom parameter list. Define the field names in the result.
   stream_table_name	The name of the MaxCompute table that stores the incremental data from Tablestore.
   primary_keys	The list of primary keys for the Tablestore source table.
   SequenceID	The time series information. Enter `sequenceInfo`.

   Important

   For ODPS SQL examples and parameter settings, see Appendix: Pattern selection. Select the appropriate pattern (Single-version pattern, Multi-version pattern V1, or Multi-version pattern V2) based on the function that you created in Step 2.

3. Run the ODPS SQL statement.

   Click the icon. The results appear on the Result tab.

Data Studio (new version)

1. In the DataStudio console, navigate to the Data Studio page, click the icon, and choose New Node > MaxCompute > MaxCompute SQL.

2. In the New Node dialog box, select a path, enter a name, and click Confirm.

3. On the node editing page, write the ODPS SQL statement.

   The format of the SQL statement is as follows:

   SELECT function_name(para_list) 
   AS (custom_para_list)
   FROM(
       SELECT * FROM stream_table_name 
       DISTRIBUTE BY primary_keys 
       SORT by primary_keys, SequenceID
   )t;

   The following table describes the parameters.

   Parameter	Description
   function_name	The function name. Enter the name of the function you created in Step 2.
   para_list	The parameter list. Configure the parameters for the Tablestore source table and the MaxCompute incremental table.
   custom_para_list	The custom parameter list. Define the field names in the result.
   stream_table_name	The name of the MaxCompute table that stores the incremental data from Tablestore.
   primary_keys	The list of primary keys for the Tablestore source table.
   SequenceID	The time series information. Enter `sequenceInfo`.

   Important

   For ODPS SQL examples and parameter settings, see Appendix: Pattern selection. Select the appropriate pattern (Single-version pattern, Multi-version pattern V1, or Multi-version pattern V2) based on the function that you created in Step 2.

4. Run the ODPS SQL statement.

   Click Run. The results display on the Result tab.

Single-version pattern

SELECT mergeCell(2,3,"col1","col2","col3",pk1,pk2,colname,version,colvalue,optype,sequenceinfo)
AS (pk1,pk2,col1,col1_is_deleted,col2,col2_is_deleted,col3,col3_is_deleted)
FROM(
    SELECT * FROM stream_table_name 
    DISTRIBUTE BY pk1,pk2 
    SORT by pk1,pk2,sequenceInfo
)t;

Multi-version pattern V1

SELECT mergeCell(2,3,"col1","col2","col3",pk1,pk2,colname,version,colvalue,optype,sequenceinfo)
AS (pk1,pk2,version,col1,col1_is_deleted,col2,col2_is_deleted,col3,col3_is_deleted)
FROM(
    SELECT * FROM stream_table_name 
    DISTRIBUTE BY pk1,pk2 
    SORT by pk1,pk2,sequenceInfo
)t;

Multi-version pattern V2

SELECT mergeCell(2,3,3,"col1","col2","col3",pk1,pk2,colname,version,colvalue,optype,sequenceinfo)
AS (pk1,pk2,col1,col2,col3)
FROM(
    SELECT * FROM stream_table_name 
    DISTRIBUTE BY pk1,pk2 
    SORT by pk1,pk2,sequenceInfo
)t;