Configuring Mapping Rules

Overview

This topic describes how to configure mapping rules for a data integration task. Configure the mapping rules between source and destination data fields so that the source data obtained can be converted to destination data, in either Mapping mode or Script mode. The Mapping mode can be automatic or manual.

Do not use the keywords of corresponding databases as source/destination field names. Otherwise, data integration tasks may fail.

Mapping Mode

• Automatic mapping

  If metadata is defined at both the source and destination, you can use the automatic mapping mode to configure mapping information.

  Click Automatic Mapping. A mapping rule between the source and destination data fields is automatically created.

  

  If API is used as the data source type at the source or destination, data fields do not support automatic mapping. You must manually configure a mapping for data fields.

  Figure 1 Automatic mapping
  
• Manual mapping

  If the data table fields at the source and at the destination are inconsistent, click Add to add a new mapping and configure its rule manually. This method applies to integration for all data types. You can configure a mapping rule by entering a key-value pair or entering a value in the text box.

Script Mode

Configure the mapping between source and destination data using JavaScript scripts when complex objects are involved. sourceObj indicates the field object set of the source table and targetObj indicates that of the destination table.

Script mapping examples:

• Templates

  function excute(sourceObj){
   //Enter the script content (case sensitive).
   //Define the destination data objects.
   targetObj = {};
    //Add, subtract, multiply, and divide operations are supported.
   targetObj.a= (sourceObj.id * 3 + 1) % 5;
   //The JavaScript Math function is supported.
   targetObj.b= Math.sqrt(100); 
   //Type conversion is supported.
   targetObj.c = Number("3.14");
  //The system time function can be called.
   targetObj.date = new Date().toLocaleString();
   //Regular expressions are supported.
   targetObj.fdi = sourceObj.name.replace(/world/i,"fdi");
   //JSON conversion is supported.
   var json = JSON.parse(sourceObj.infoJson);
   targetObj.address = json.address;
   targetObj.age = json.age;
   targetObj.sex = json.other.sex;
   targetObj.hobby = json.other.hobby;
   //Conditional statements are supported.
   if(targetObj.hobby == "rap"){ 
    targetObj.ikun = true;
   }else{
    targetObj.ikun = false;
   }
   return targetObj;
   }

• Mapping between fields only

  function excute(sourceObject) {//sourceObject indicates the data object transmitted from the source.
   //Write the script content.
   var targetObject = {};
   targetObject.mqs_id = sourceObject.id;//Map the id field in the source table to the mqs_id field in the destination table.
   targetObject.mqs_name = sourceObject.name;
   targetObject.mqs_date = sourceObject.date;
   targetObject.mqs_date = sourceObject['customized-outdutydate'];//Attributes containing hyphens (-) must be quoted using square brackets.
   return targetObject; //targetObject indicates the data object returned to the destination.
   }

• API as the destination data source type

  When the destination data source type is API, three objects are required for transmission: params, headers, and body.

  function excute(sourceObj) {
   print("execute js");
   print(sourceObj);
   var targetObj = {};
   targetObj.params = {};
   targetObj.params.id = sourceObj.uid
   targetObj.headers = {};
   targetObj.headers['X-HW-ID'] = "ApplicationID";
   targetObj.headers['X-HW-APPKEY'] = "AppKey";
   targetObj.body = {};
   return obj;
   }

  If the data to be converted involves time data, modify the JS script by referring to the following content in bold.

  

  During orchestration, if the destination data source type is API and the JS script is used for data conversion, source data of the Date type will be automatically converted to the yyyy-MM-dd HH:mm:ss string type. For example, if the source is 19:55:20, the time will be converted to 1970-01-01 19:55:20 by the JS script. 1970-01-01 is the default value added by the system.

  Therefore, to obtain time data in desired format, perform the conversion in the JS script by referring to the example below.

  function excute(sourceObj) {
  
  /**
   * Custom_time_format_function
   * @param {format} Time_display_format
   */
  Date.prototype.format = function (format) {
      var date = {
          "M+": this.getMonth() + 1,
          "d+": this.getDate(),
          "h+": this.getHours(),
          "m+": this.getMinutes(),
          "s+": this.getSeconds(),
          "q+": Math.floor((this.getMonth() + 3) / 3),
          "S": this.getMilliseconds()
      };
      if (/(y+)/i.test(format)) {
          format = format.replace(RegExp.$1, (this.getFullYear() + '').substr(4 - RegExp.$1.length));
      }
      for (var k in date) {
          if (new RegExp("(" + k + ")").test(format)) {
              format = format.replace(RegExp.$1, RegExp.$1.length == 1 ? date[k] : ("00" + date[k]).substr(("" + date[k]).length));
          }
      }
      return format;
  };
  
   print("execute js");
   print(sourceObj);
   var targetObj = {};
   targetObj.params = {};
   targetObj.params.id = sourceObj.uid
  //When the time data is converted, the custom function above is called to extract the time data (hour, minute, and second) for the destination field.
  //HH:mm:ss in the following code indicates the hour, minute, and second, respectively. If the format of hhmmss is used, the value returned to the destination field does not contain colons (:).
   targetObj.params.time = (new Date(sourceObj.time)).format("hh:mm:ss");
   targetObj.headers = {};
   targetObj.headers['X-HW-ID'] = "ApplicationID";
   targetObj.headers['X-HW-APPKEY'] = "AppKey";
   targetObj.body = {};
   return obj;
   }

• Database fields involving multi-level JSON parsing

  If multi-level JSON objects are nested in the source data object fields, use [] to reference each parsed field.

  In the following example script, the root field contains multi-level objects such as id, name, and double. In this case, each parsed field needs to be referenced separately.

  function excute(sourceObject) {
   //Write the script content.
   var targetObject = {};
   targetObject.mqs_id = sourceObject["root.id"];
   targetObject.mqs_name = sourceObject["root.name"];
   targetObject.mqs_double = sourceObject["root.double"];
   targetObject.mqs_date = sourceObject["root.date"];
   targetObject.mqs_boolean = sourceObject["root.boolean"];
   targetObject.mqs_timestamp = sourceObject["root.timestamp"];
   targetObject.mqs_time = sourceObject["root.time"];
   targetObject.mqs_long = sourceObject["root.long"];
   return targetObject; 
   }