Can we compare for structural differences only

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.

Appreciate your help and response.

Thanks,
Sri

Hello @srikanthnama and welcome to the KNIME forum

This node could work for you

BR

3 Likes

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.

Appreciate your time and help.

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.

I’ve prepared a simple example with two tables:

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).

The result is as follows:

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…

I hope this helps to start with.
BR

2 Likes

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.

Much appreciate your help.

Thanks,
Sri

1 Like

@gonhaddock Thanks a bunch for your help and I managed to pull this off and created my first workflow!

Couple of follow-ups:

  1. I noticed that Extract Table Spec doesn’t bring the column size or length. Is there a way we can pull this?
  2. 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.

Best,
Sri

2 Likes

Hello @srikanthnama
And thanks for validating the solution.

The Extract Table Dimension node can return ‘Number Rows’ and ‘Number Columns’

It would be better to open a new topic for the specific use cases around DDL.

BR

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.