Easymorph best practice / Approach to “ merge into with update/insert” into large table

1

This is more of a best practice and/or EasyMorph approach topic.

Business Workflow

  1. Read from web API source GPS breadcrumb data for many vehicles with each vehicle having many updates
  2. Transform the data to make sense to the business tables
    a. Header table for last known location
    i. One row per asset
    b. Detail table for all breadcrumbs consumed.
    i. On asset many rows
    ii. May have already read the breadcrumbs previously so ignore if the same
    iii. Database table has a unique index to prevent duplicates.
  3. Workflow is executed every 15 minutes

The good news is that I have this already working using the easymorph platform. I learned a great deal of how the platform works but I still to learn more. Hence the reason for the question.

Current Approach

  1. Read from api

  2. Keep/remove columns.

  3. Rename columns.

  4. Rule logic to control country name.

  5. Calculate columns.
    a. Mostly to deal with combining adding some constants

  6. Fix dates to match database columns
    a. Did I mention I hate date logic for all databases. Laughing

  7. Update the asset header table with last know location from ingest.
    a. Works perfectly

  8. For some reason have to adjust dates again after the update

  9. Then iterate through a module (Merge_F_GPS_YARD)
    a. This is the one that is very large and will update the duplicates with just the updated date
    b. Then inserts new rows
    c. To achieve this I used a custom SQL statement this Merge into statement that goes row by row
    d.

    image
    image1222×1821 180 KB

  10. It works and it is fast. So great news. Again, learning the platform and looking learn more.

The Ask

I have created a unique index on the larger table and just wanted to “Export to database” on mass. When I did this, and the entire input table was NEW and not in the table it worked fine. As soon as one row was duplicated, I just wanted easy morph to ignore the error and keep going to the other row. However regardless of what flags I flipped I was not able to get easymorph to insert into the table just the new rows. The whole batch was ignored.

I wanted to use the database engine to just deal with the inserts and have the duplicate rows ignored.

So does the easymorph community have any suggestions for the newbie. Now remember, the export to table has 10’s of millions of rows so trying to get a temp table of potential matches is a costly sql call. So the “merge into sql statement” achieves the goal. On the update it just updates the modified row date and the insert works fine

the Net/net is that I have it working but would love to learn a cleaner more effective approach that leverages the tool more.

So for now, a fool with a tool is still a fool. That fool is currently me.

Thank you in advance.

2

I hope I understood your question correctly.

  • to remove duplicates there is deduplicate action in easymorph, if you want deduplicate with particular logic then use aggregate, use enumerate grouping by combination of fields and sorting or use unpivot and pivot
  • if source table has large amount of data it might be better to create a temp table in server and use bulk insert to the temp table then use the merge query in the server against the temp table (after each merge query runs you can truncate the temp table)
3

Tim,

Check out the "Select matching database rows" action. With its help, you can get a list of IDs that already exist in the database table AND in your EasyMorph table. Then remove them from the EasyMorph table (e.g. using "Keep/remove matching rows"), and export the rest of the rows into the database without a conflict.

4

Ok, that works. Clean and effective.

Less stress on the database.

Thank you.

5

I see your logic here. Not sure it works for this implementation but you have given me some ideas for other workflows.

Thank you.