Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Except Component Problems

This has three parts:

1) The actual description of what the Except component does seems to be backwards. What the help says is "Compares two input flows and outputs any rows from the primary table that do not exist in the comparison table." However, after you choose the primary table from the drop down, the SQL which is generated shows that what gets selected is everything EXCEPT what matches the rows the primary table.

2) After you've put the Except component onto the canvas, hooked up two other components, and set the primary table option, the Except component will validate and you can check the SQL. However, if you then go and change the "primary table" option and re-validate, the SQL doesn't change. You have to delete the component and recreate the connections in order to get the SQL to change.

3) Last night I had an issue in production where it appears that the Except component randomly switched what it considered the "primary table" when it validated. I'm saying this because this is only the 2nd time this process has run in production, and given the issue I had last night (seemingly creating duplicate records because the except was using the wrong primary table), if the component hadn't changed then there shouldn't have been any data the first night it was run.

Looking for some answers/insight into these problems. If one of the components is randomly changing what it does. that's a big problem.

12 Community Answers

Matillion Agent  

David Lipowitz —

Hi Russ,

Thanks for writing in. We haven’t heard of any of our users running into the issues you describe. Can you confirm which version of Matillion you’re running? You can do so under the Help → About menu to the upper right of the canvas.

Regarding the points you mention:

  1. In my own testing of the EXCEPT component, the records returned are those that exist in the primary table but not the secondary. I believe this is the correct behavior and matches the description under the Help tab.
  2. I tried changing the primary table from the original table to the secondary: the SQL changed accordingly as did the resulting dataset.
  3. We’re unaware of Matillion altering the values inside components. Is it possible that this value was left pointing to the second table during testing? Is there anyone else in your organization who might have changed the value in question?

Best Regards,
Dave


Russ Weiher —

Hi Dave -

The version of Matillion I am using is: 1.35.9 (build 206).

1) In terms of correct behavior, when the 'primary table' parameter is selected, should that correspond to the first result set in SQL or the second result set? In ANSI SQL (and in Snowflake SQL), the except operator keeps everything in the first result set that doesn't exist in the second result set. However, when I choose the primary table parameter, it's that table which is then placed as the second result set in the resulting SQL

2) If possible, I would certainly be willing to do a screen share, so that you can see the SQL not being updated in the component first hand.

3) I'm currently the only person with access to Matillion at my company, so no one else could have changed it. Also, as I stated in my first message, if the functionality of that component had been left in a 'testing' state, it wouldn't have worked at all the first time it ran on in production on Friday, because one of the two tables was completely empty.

I know this all sounds strange, and it initially doesn't make sense, but something is definitely going on.

Regards,
Russ


Matillion Agent  

David Lipowitz —

Hi Russ,

I have until the top of the hour at the moment. You can join me here if you’re free: https://matillion.zoom.us/j/288973048

If now doesn’t work, I could do 4pm MDT as well.

Best Regards,
Dave


Russ Weiher —

David -

I will be free at 4:00pm MDT (6:00 EDT).

I'll try the same zoom link, unless you send me a new one.

Thanks,
Russ


Matillion Agent  

David Lipowitz —

Hi Russ,

Great, let’s plan on using this link: https://matillion.zoom.us/j/222272159

Talk to you then.

Best Regards,
Dave


Matillion Agent  

David Lipowitz —

Hi Russ,

Thanks for your time on the call today. I can definitely see the issue you describe. Let’s proceed this way:

  1. Please have your devops team rotate the Matillion log file with this command: sudo logrotate --force /etc/logrotate.d/tomcat8
  2. Once done, please click toggle the Primary Table on the Except component a few times on both of the Transformations you showed me
  3. Finally, please retrieve and forward the /var/log/tomcat8/catalina.out file and send it to support@matillion.com

If you’d note in that email that the attached log is for case 15328, we’d appreciate it. Then we can pick up support on the thread that that email creates.

I will test out the Except clause on a Snowflake instance myself and see if I can recreate on our side.

Best Regards,
Dave


Matillion Agent  

David Lipowitz —

Hi Russ,

I was able to recreate this issue in my Snowflake instance so no need to retrieve your logfile or send it to us. I gave the developers what they need and opened a bug report. Will let you know when I hear about their progress on this.

Best Regards,
Dave


Russ Weiher —

Dave - Log file sent. Definitely looks like some warnings happening in there.

Regards,
Russ


Russ Weiher —

New Ticket ID is 15363.


Matillion Agent  

David Lipowitz —

Hi Russ,

We’re looking into a solution here. In the meantime, and as a work-around, can you please create a SQL component and paste the correct SQL into it? Then you can feed the output from that SQL into the rest of you’re Transformation data flow.

Hopefully that will get you going until we can resolve the bug.

Best Regards,
Dave


Karl Young —

We have also run into this issue (i.e., EXCEPT not changing primary tables, or even changing primary table seemingly at random). Russ, you are not alone!

Our workaround has been what Dave suggested, using a SQL Script Transformation Component and pasting in the correct SQL. This works functionally, but is somewhat unsatisfying.

Will monitor this thread, but would appreciate notification when the Ticket is resolved, if that can be arranged.

--Karl


Russ Weiher —

Karl -

This was why I initially opened this as a community post instead of a straight support ticket. Assuming I wasn't just crazy, I knew that someone else had to be seeing the same issue. Hopefully once the developers get a look at it, we'll get a patch before too long.

Russ

Post Your Community Answer

To add an answer please login