Best Practices for bulk record updates?

What is the best practice for making bulk record updates?  An example is you have a table for a particular CDT which has say 10,000 rows and say 20 fields.  We need to be able to filter the rows and update values in that subset of rows.  Now throw in the fact that you need to be able to do this for multiple tables across multiple data stores (to minimize dev effort required to do this for all necessary tables).  We can not simply run a manual SQL in our production environment to update records without jumping through hoops for approval and involving many people (against policy).  My company wants a unified app that can make bulk updates to tables in any of our Appian data stores via SQL injection.  Should we:

- Create an app to inject parameterized SQL to any of our databases (hopefully not but this is the request).

- Use a third party ETL tool for bulk changes.

- Use a smart service (if any exists) to select a table, select a column you need to update, give the value you want in the column and the filter you want to update only certain rows.

- Export records into excel, let business make changes, and setup an excel import PM for any data entity.

- "You're doing it all wrong, this is how you should do this...."

What I have come up with as follows is:

Most of our apps have read only grids already where you can filter/view important CDT's.  I could modify existing read only grids to do the following:  Add a row of drop downs (by duplicating existing drop down filters) but instead of using them to filter, this second set of drop downs would be used to set the data in a certain field for all rows.

 

Then sending the updated rows to a write to data store PM would be easy.  What do yo all think?

Thanks!

  Discussion posts and replies are publicly visible

  • I suspect (especially with regards to your first comment) that a solution to conduct the update is not the hard part, but implement the controls/governance and auditing that wraps around who does what will be. If you want a generic app that allows an end-user to effectively run a UPDATE <table> SET <column1=value1, column2=value2, columN=valueN> WHERE <condition(s)> then I think you're limited pretty much to your first option. You could write an Application to construct the SQL and send that to a Stored Proc which effectively processes the statement as dynamic SQL, but (as I've seen in most organisations) you won't get the necessary permission from the CISO (Chief Information Security Office) - running injected dynamic SQL is just going to be forbidden. So maybe hand-cranking the SQL and then using a Application to implement the aforementioned governance/controls/auditing to help with the hoop-jumping WOULD be acceptable?

  • Exactly!  Writing updates to a table is trivial but a unified way of updating any table across any data source is not and injecting SQL is not safe.  I was thinking about a parameterized read only grid that can load data from any datatype.  then have drop down fields where you can select the column you want to update and provide a value to update it with.  You won't be able to submit a request unless you have some filter on the records.  If it's not possible to create a one stop shop, creating this option for each table that needs updating should be easy enough.  what do you think?  isn't at least part of this capability built in to record types?  Thanks!

  • 0
    Certified Lead Developer

    Are you just talking about repeatable bulk reassignments?  Not sure why you would need to go down the route of custom sql execution if its the same business function each time.  Create a Reassignment Tool that meets the business needs so you can search by user, process, task, etc and multiselect/select all and then reassign to a specified user/group.

    If you are talking about a DB Fix Tool that allows for execution of SQL at run time which has not been defined at design time - well then you are pretty much building a SQL Execution tool without the constraints of needing DB access, since t runs under the application schema user....

    If you have SQL to run with a deployment (even if the entire deployment is just SQL), then have it designed at design time and go through Change Control Approval like you would for code, cause it is is code.  If your code needs run time elements extracted from Appian objects (document properties for example), you can build a utility that queries both DB and Appian meta data and compiles the SQL statement to execute (or better yet the DSE update CDT) and execute it at run time.

  • If I understand correctly you're looking to, say, fetch the meta-data about all of the available tables from the Database's internal schema, to provide a drop-down of the available tables, and then, for a selected table, the list of columns and their types, and from the type information you can then display a relevant UI component to set a value. And you can then assemble a SQL statement from that information. But that SQL statement will still be 'text' and would need to be passed a a Stored Proc and run as dynamic SQL, which in effect becomes an injection entry point. So: yes, it can be done. But, as per our previous conversation, I don't think anyone will let you do it.

  • 0
    Certified Lead Developer
    in reply to Stewart Burchell

    Does not have to be a stored procedure, if you know the DB structure you can use the execute SQL node, but its better to have someone define the stored procedure so you are not monkeying around in the DB.

    Appian development should NOT be executing into the DB directly.  Let Appian Application do it with hibernate, the DSE, CDT, etc, or have a DBA/DB Programmer define the stored procedure with the necessary inputs and validations and you can call that.

    Now that is in an ideal world, and in the real world, your Appian Developers are typically doing ltos of stuff int eh DB.  When you get to fixing prod data, then you can be sure you will at some point have a DB execution, either from an Appian PM or a direct SQL script.  But if you need programmatic SQL executed to update data, consider mapping a CDT/DSE to the table and using write to DSE.

  • 0
    Appian Employee
    in reply to Jake

    If I recall correctly the SQL Smart Service Node cannot be parameterised...it requires fixed SQL Statements added to it (I guess for the very reason that it would then become a form of 'injection')

    And John's initial requirement was to be generic..."My company wants a unified app that can make bulk updates to tables in any of our Appian data stores"

  • 0
    Certified Lead Developer
    in reply to Stewart Burchell

    You can do poor man's parametrization with in statements, but generally, you are 100% correct.

    I don't like the requirement to build a DB viewer/update app in Appian.  Appian works best with design time defined DB access (CDT/DS/DSE).  If you want to circumvent security access control of your DB, you are not facing an Appian problem.

  • 0
    Appian Employee
    in reply to Jake

    And I would 100% agree with you. Hence my initial response that perhaps the effort would be better spent in a Governance/Control/Audit application and simply hand-craft the necessary SQL statements.

  • Thanks ALL for the responses so far!  My initial example was a bad one so I updated my post with a better one, along with the approach I've come up with so far.