Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Transformations: Ability to run Custom SQL; Ability to pass parameters to AgentPlugin

Rajpal Palli User

Request following fetures:

1. Ability to execute SQL with in HVR console: Our use case requires to run Custom SQL query that does JOIN on multiple target tables to fetch data and load in single denormalized target table

 

2. Ability to pass parameters to AgentPlugin: Ability to pass IDs of the records inserted/updated to dbProc who it can perform custom transformations

 

Please sign in to leave a comment.

Comments

1 comment

Date Votes
  • Mark Van de Wiel User

    Hi Rajpal,

    1. We support stored procedures on several platforms (https://fivetran.com/docs/hvr6/capabilities/620#othercapabilities). Could you use that as a wrapper around the SQL you want to run?

    2. You should do state management in the AgentPlugin code. E.g. if you want to transform certain rows then you could add a column to the table "transformed". You default this column to 0 or 'N'. For your load routine you would process all rows with "transformed" set to no, and update the value to yes (e.g. in a stored procedure).

    FYI we recently added an argument to Capture OnCaptureTableEndCycle (https://fivetran.com/docs/hvr6/action-reference/capture#oncapturetableendcycle). With this attribute you can force an AgentPlugin to run on the target based on a table you control. You can store any values you want to use for the execution of the plugin in the table you use to trigger the end of cycle. Then use a similar approach as described above to mark a row as processed.

    Hope this helps.
    Mark.