[GitHub] [dolphinscheduler] github-actions[bot] commented on issue #12858: [Improvement][Task] SqlTask ​​Change the order of regular replacement ${} and !{}

[GitBox <gi...@apache.org>]

github-actions[bot] commented on issue #12858:
URL: https://github.com/apache/dolphinscheduler/issues/12858#issuecomment-1309941083

   ### Search before asking
   
   - [X] I had searched in the [issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and found no similar feature requirement.
   
   
   ### Description
   
   SqlBinds org.apache.dolphinscheduler.plugin.task.sql.SqlTask::getSqlAndSqlParamsMap(String sql)
   
   Briefly describe the modification content:
   Code to replace !{}: sql = replaceOriginalValue(sql, rgexo, paramsMap);
   Move before the code that replaces ${}: setSqlParamsMap(sql, rgex, sqlParamsMap, paramsMap,taskExecutionContext.getTaskInstanceId());
   
   Briefly describe the business scenario:
   SQL statement: INSERT OVERWRITE TABLE label_1 PARTITION (ds = '${date}') !{sql}
   Parameters: sql = "SELECT user_id FROM mbr_db.share_friend_num_dt_g WHERE ds = '\\${date}' AND group_id = 1 AND num > 0"
   Note: There is also ${date} in the parameter sql
   
   Replace !{sql} first, then replace the SQL statement: INSERT OVERWRITE TABLE label_1 PARTITION (ds = '${date}') SELECT user_id FROM mbr_db.share_friend_num_dt_g WHERE ds = '${date}' AND group_id = 1 AND num > 0
   Re-analyze ${date} to ensure that ${date} in the sql parameter can also be processed.
   
   Conversely, analyze ${date} first, and then replace !{sql}, resulting in ${date} in sql not being processed.
   
   Tested
   
   Old code:
   `
   //new
   
   //replace variable TIME with $[YYYYmmddd...] in sql when history run job and batch complement job
   
   sql = ParameterUtils.replaceScheduleTime(sql, taskExecutionContext.getScheduleTime());
   
   // special characters need to be escaped, ${} needs to be escaped
   
   setSqlParamsMap(sql, rgex, sqlParamsMap, paramsMap, taskExecutionContext.getTaskInstanceId());
   
   //Replace the original value in sql ! {...} , Does not participate in precompilation
   
   String rgexo = "['\"]*\\!\\{(.*?)\\}['\"]*";
   
   sql = replaceOriginalValue(sql, rgexo, paramsMap);
   
   // replace the ${} of the SQL statement with the Placeholder
   
   String formatSql = sql.replaceAll(rgex, "?");
   
   sqlBuilder.append(formatSql);
   `
   
   New code:
   `
   //new
   
   //Replace the original value in sql ! {...} , Does not participate in precompilation
   
   String rgexo = "['\"]*\\!\\{(.*?)\\}['\"]*";
   
   sql = replaceOriginalValue(sql, rgexo, paramsMap);
   
   //replace variable TIME with $[YYYYmmddd...] in sql when history run job and batch complement job
   
   sql = ParameterUtils.replaceScheduleTime(sql, taskExecutionContext.getScheduleTime());
   
   // special characters need to be escaped, ${} needs to be escaped
   
   setSqlParamsMap(sql, rgex, sqlParamsMap, paramsMap, taskExecutionContext.getTaskInstanceId());
   
   // replace the ${} of the SQL statement with the Placeholder
   
   String formatSql = sql.replaceAll(rgex, "?");
   
   sqlBuilder.append(formatSql);
   `
   
   ### Are you willing to submit a PR?
   
   - [X] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org