I Tried Using Structure Parser Transformation in IDMC

I Tried Using Structure Parser Transformation in IDMC

Clock Icon2024.08.21

Introduction

I'm Shiwani from the Data Analytics Division.
This time, i tried using Structure Parser to converts input data into a user-defined structure format based on an intelligent structure model.
The structure model will read data from a table embedded in an actual Microsoft Word document. It parses the data while ignoring the text and formatting characters in the Word document.

Objective

  • Create a mapping using Structure Parser transformation.

Prerequisite

The following preparations are assumed to be ready in advance:

  • The IICS Secure Agent and VPC have been configured.
  • Click weekly_sales.txt and download the sample file.
  • Create a .txt file with the name parser_input.txt and the path of the weekly_sales.docx file as shown in the example below:
Path
\home\IICSLabFiles\SrcFiles\weekly_sales.docx
  • Create a connecter to establish connection with the source files (parser_input.txt, weekly_sales.docx ).
  • Create a connecter to establish connection with the target file.
  • Create an Intelligent Structure Model with the name WEEKLY_SALES_WORD_DOC , refer to this link to article.

Create Mapping

  • Create Mapping with the name m_02_PARSER_INTEL_STRUCTURE.

str-1

Add Source Transformation

  • Click Source transformation and set Name to WEEKLY_SALES_DOC.
    str-2

  • Select Source file connecter from Connection. here i have created connection with the name FF_Source_2. Select parser_input.txt from object.
    str-3

Add Structure Parser Transformation

  • Add Structure Parser Transformation on the link between
    WEEKLY_SALES_DOC and Target transformations.

str-4

  • Select the previously created WEEKLY_SALES_WORD_DOC structure mode.

str-5

  • The Structure Parser transformation output is Relational, JSON, XML, Avro,
    Parquet, and ORC format. This time, keep the output format as
    Relational.

  • Link WEEKLY_SALES_DOC to StructureParser.

  • Map the incoming fields to the structure parser input fields.

map6

Add an Expression Transformation

  • Add Expression Transformation between StructureParser and Target
    transformations.
  • Link StructureParser with Expression transformation on the mapping.
  • Select element and click OK.

str-exp-1
str-exp-2

  • Click Expression transformation and add the following properties details.

    • General :

      • Enter the Name as CALC_AVG_TRANS_REV.
        str-exp-3
    • Expression :

      • To add a new expression, click the plus icon.
      • Enter the details as shown in table below.
      Field Type Name Type Precision Scale
      Output Field AVG_TRANS_REV bigint 19 0

      str-exp5

      • To add an expression for AVG_TRANS_REV, click Configure and enter the following expression.

      TO_INTEGER(Revenue)/TO_INTEGER(Transactions)

      str-exp-6

  • Link the CALC_AVG_TRANS_AVG to the Target.

  • Click the Target transformation and enter the properties details.

    • General
      • Enter the name as WEEKLY_SALES_BY_STORE.
        str-exp-7
      • Click Target. From the Connection drop-down, select your target database connection.
      • Select the Target Object as WEEKLY_SALES_BY_STORE.
        str-exp-8
      • Click Field Mapping and Match the fields. Finally Save and Run the mapping.

Results

  • Click My Jobs and check the execution status of the mapping. When the task completes, the status changes to Success.
    str-exp-test1

Conclusion

I have successfully developed and tested the mapping using Structure Parser transformation in IDMC.
Structure Parser uses an intelligent structure model to convert incoming data to a user-defined structure format. This Structure Parser transformation is useful for analyzing log files, clickstreams, XML and JSON files, Word tables, and other unstructured or semi-structured data types.

この記事をシェアする

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.