Recipe for "Load As-Is" from Relational

[sammefford]

Create a new cookbook example which demonstrates how to take one SQL query and load the rows "as-is" into MarkLogic. Where possible, align with the concepts discussed in the following blog posts:

• What ‘Load As Is’ Really Means
• Explaining ‘Load As Is’
• Can NoSQL Really Solve the Problem?
• Get the ETL Out of Here

Demonstrate the as-is architecture including with and without grouping on the unique key.

[sammefford]

On way to do this is to fork the cookbook example BulkLoadFromJdbcWithSimpleJoins. That comes with the dataset Employees with Salaries and Titles. The only way to join that data in one query is with repeating values in some columns (the cross product). Using that and grouping on the "employee_id" unique key column, we can take data like this:

employee_id	employee_name	salary	salary_date	title	title_date
123	Sam Mefford	321	4/6/2014	Senior Engineer	4/6/2014
123	Sam Mefford	323	1/1/2016	Senior Engineer	4/6/2014
123	Sam Mefford	325	1/1/2017	Senior Engineer	4/6/2014
123	Sam Mefford	321	4/6/2014	Staff Engineer	8/31/2016
123	Sam Mefford	323	1/1/2016	Staff Engineer	8/31/2016
123	Sam Mefford	325	1/1/2017	Staff Engineer	8/31/2016

And group it into a json document that looks like this:

{ "rows": [
  { "employee_id": "123", "employee_name": "Sam Mefford", "salary": 321, "salary_date": "2014-04-06T00:00:00Z",
    "title": "Senior Engineer", "title_date": "2014-04-06T00:00:00Z", "row_num": 1 },
  { "employee_id": "123", "employee_name": "Sam Mefford", "salary": 323, "salary_date": "2016-01-01T00:00:00Z",
    "title": "Senior Engineer", "title_date": "2014-04-06T00:00:00Z", "row_num": 2 },
  { "employee_id": "123", "employee_name": "Sam Mefford", "salary": 325, "salary_date": "2017-01-01T00:00:00Z",
    "title": "Senior Engineer", "title_date": "2014-04-06T00:00:00Z", "row_num": 3 },
  { "employee_id": "123", "employee_name": "Sam Mefford", "salary": 321, "salary_date": "2014-04-06T00:00:00Z",
    "title": "Staff Engineer", "title_date": "2016-08-31T00:00:00Z", "row_num": 4 },
  { "employee_id": "123", "employee_name": "Sam Mefford", "salary": 323, "salary_date": "2016-01-01T00:00:00Z",
    "title": "Staff Engineer", "title_date": "2016-08-31T00:00:00Z", "row_num": 5 },
  { "employee_id": "123", "employee_name": "Sam Mefford", "salary": 325, "salary_date": "2017-01-01T00:00:00Z",
    "title": "Staff Engineer", "title_date": "2016-08-31T00:00:00Z", "row_num": 6 }
]}

[vivekmuniyandi]

Moving to 4.0.5 as there is no room for this in 4.0.4