2-7-5 Column Maps with Child Query

Processors:

• Execute SQLToColumnMaps - Reads from RDBMS and writes the row data to the FlowFile as a "column map", a List<Map<String,Object>> serialized to a byte array
  • Properties
    • Database Connection Pooling Service: (your instance of DBCPConnectionPool controller service)
    • SQL query:
      SELECT
employees.emp_no as emp_no, employees.birth_date, employees.first_name, employees.last_name,
employees.gender, employees.hire_date, employees.dept_emp.dept_no, employees.departments.dept_name,
employees.salaries.salary, employees.titles.title
FROM
employees.employees, employees.dept_emp, employees.departments, employees.salaries, employees.titles
WHERE
employees.employees.emp_no = employees.dept_emp.emp_no AND
employees.dept_emp.to_date > CURDATE() AND
employees.dept_emp.dept_no = employees.departments.dept_no AND
employees.employees.emp_no = employees.salaries.emp_no AND
employees.salaries.to_date > CURDATE() AND
employees.employees.emp_no = employees.titles.emp_no AND
employees.titles.to_date > CURDATE()
• ExecuteChildQueriesOnColumnMaps
  • Properties
    • Database Connection Pooling Service: (your instance of DBCPConnectionPool controller service)
    • Child query JSON:
      {"primaryKeyColumnName": "emp_no", "childQueries": [{ "query": "SELECT employees.salaries.emp_no as emp_no, employees.salaries.salary, employees.salaries.from_date, employees.salaries.to_date FROM employees.salaries", "propertyName": "salaryHistory", "foreignKeyColumnName": "emp_no" } ]}
• ConvertColumnMapsToJSON - Reads the column map and converts each row to a JSON document
  • Properties
    • (none)
• EvaluateJsonPath - Store values from JSON in FlowFile properties
  • Properties
    • Destination: flowfile-attribute
    • emp.no: $.emp_no (custom property)
  • Settings
    • Automatically Terminate Relationships: failure, unmatched
• UpdateAttribute
  • Properties
    • marklogic.uri: /employees/${emp.no}.json
• PutMarkLogic
  • Properties
    • DatabaseClient Service: MarkLogicClientService – Localhost / Documents
    • URI attribute name: marklogic.uri
  • Settings
    • Automatically Terminate Relationships: FAILURE, SUCCESS