I have a scenario to read from an existing table and transform each row (each row is a JSON and I need to flatten it based on some business logic) Can you please suggest an approach? I did try the Python component but we cannot use it in transformation jobs. Is there a way to pull data and transform it using a custom logic?
2 Community Answers
Laura Malins —
Can I ask why you need to do it row by row please? Matillion works best when processing batches of data rather than at a row by row level. If you are able to process batches in one go you can use a transformation job with a table input component to read the data in .You can use a calculator, possibly with some of the Redshift JSON functions to extract the elements you require to flatten and then you can write to to another table using one of the write component.
If this genuinely doesn’t work for your use case, you can use python. You’re right in that you have to do this for an Orchestration job but if you use the Jython interpreter then you can use the database cursor using the context.cursor() command and you can then extract and loop through the rows processing them as required. You can write them back to Redshift using an insert statement in the cursor.