I have a transform component set up that contains a SQL Source query going to a Rewrite Table component. The source query is basically a union on two tables, and then Rewrite Table puts it into a common table. This saves us downstream unioning.
The problem is that when I run this job it creates two views in the matillion schema on those two tables. These views then hang around, which becomes a problem when I want to re-stage the original tables by dropping them and recreating them.
Of course I can do a DROP CASCADE, but I'd rather this component just cleaned up after itself. Is there a blessed way to do this?
3 Community Answers
Laura Malins —
You can delete the views by right clicking on an environment and selecting “Delete Views” however obviously this won’t be done as part of a transformation job. Unfortunately there’s no way to delete the views as part of the transformation job. The closed you could do is run the Transformation Job from an Orchestration Job and once the Transformation Job runs successfully you can run a Delete Tables Component to drop the staging tables and Cascade to drop the views.
How are you creating the staging tables? If it is via Load/Unload components in an Orchestration Job Matillion will create the tables (where applicable) and you won’t need to worry about the views. If you’re staging outside of Matillion, is there a reason you need to recreate the tables rather than simply truncating them?
> Orchestration Job and once the Transformation Job runs successfully you can run a Delete Tables Component to drop the staging tables and Cascade to drop the views
The part of that that I'm not crazy about is that somebody may set up a legitimate view that they want kept around and so `CASCADE` would drop those, too.
> How are you creating the staging tables?
We create these by selecting everything out of our landing schema into the staging schema, table by table. The first part of our pipeline is to move everything from MySQL to our landing schema as is. Then we move those tables to staging with a zap_ prefix and a _new suffix. Then we drop the table with just the zap_ prefix (from the prior day) and rename the _new table to the old table.
In staging we also make a few other schema changes, one of which is to combine these two tables into one.
After that we move everything from staging to our public schema.
Does something in Load/Unload help us do this better?
The RDS Query and Database Query Components from the Load/Unload folder in an Orchestration Job can be used to stage the data from your MySQL db depending on whether it is in the cloud or on premise. These components will automatically truncate the staging tables if it detects the same metadata or drop them if the metadata is different.
I understand what you’re saying about cascade but if you need to drop the table, any views on that table will need to be dropped, whether they are legitimate or just created by Matillion.
I’m happy to set up a call to walk through your use case if you’d like?