Hi There, Is there a way we can compare just the table/view for structural differences between two environments. I am not looking to compare the data just trying to see any metadata differences exists for ex. column names, datatype, length, Primary Key etc. I don’t want to bring the data as it is time consuming and not the intent of this exercise.
Thank you so much. I am new to this tool and just getting started. Once I configure the Extract Table Spec node, should I be using the Compare Node to find the differences ? Is there a way you can provide a sample workflow to showcase this functionality? It will be very helpful to understand the end-to-end workflow.
Hello @srikanthnama
It’s difficult to provide a sample workflow without a use case or same sample data… so it depends on what differences are you trying to match.
As you can see this is the information that you can get from the table. I’ve introduced one difference in column name (@index0) and format type (@index3)… Then i solved to compare with a full outer join; with matching columns: Column Name, Column Type and Column Index (Position).
Then you can see that only Column 2 and 3 find a match. Compare bounds may require some more work because undefined format type; disaggregate by formats…
Thank you @gonhaddock for taking the effort to create a sample workflow. This definitely gave me an idea. I will use this as a reference to test my use case.
@gonhaddock Thanks a bunch for your help and I managed to pull this off and created my first workflow!
Couple of follow-ups:
I noticed that Extract Table Spec doesn’t bring the column size or length. Is there a way we can pull this?
My another use case is to compare the view definition(the actual DDL) between two environments. I doubt this can be achieved and I do understand this may not be an ideal use case/functionality for KNIME. But just wanted to check.