2-7-4 Use ExecuteSQLToColumnMaps

This example uses Rob Rudin's "rowtodoc" processors to read from an RDBMS in batches, convert to a "column maps" data structure, then convert that to JSON. By streaming batches of documents these processors work around the issues in the built-in processors.

Download Template

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, 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()
• 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