I have not been able to get a concurrent job to work with snowflake storage, with various errors to snowflake storage (stage not found when it is there, stage not created, IO stream already closed, etc.). Support is unable to resolve as they have pushed me up to 1.62, but I still have the same basic issue. I have tried all kinds of configurations of stage storage. But basically, as soon as I turn the concurrent setting on for a grid variable or table iterator, the job begins failing on these different types of storage errors, depending on the setting I am testing. Was wondering if anyone running Matillion ETL for snowflake has been able to get an iterator to work in concurrent mode?
Hi @eric.coleman ,
I have seen similar errors with concurrent iteration setting on Database Query component using internal stage. It looked like it had something to do with the purge enabled. It used to give the error "stage not found" with the purge enabled. I ended up using external stage with the concurrent setting. The purge can be done outside of the component as well. With the internal stage also, I think with the purge disabled, it might work. Then you may have to remove the files from the internal stage as an additional step. Please give it a try ad see if it works. Also, check if the error shows the same database and schema you are targeting for the stage.
Thank you for your response. I am on version 1.62, I tried setting the load options to 'off' for 'clean staged files', and I set the stage location to an azure external stage we have already set up. It failed on the same error we typically get when trying through an external stage: Exception: java.io.IOException: Stream is already closed. I confirmed it did not delete the files, as it created almost 200 folders on the external stage container. The errors from the logs indicate the stage tables are created in our default database and schema configured in the environment. Any other suggestions you might have are appreciated.
Hey @eric.coleman,
Firstly to directly answer your question, I have set up plenty of iterators to run concurrently using Matillion ETL for Snowflake. Personally I've never encountered an issue so I wonder if I'm approaching it differently to you. Some things I have done which worked well without issue:
- Concurrently iterate over a child orchestration job that contains a SQL component with a COPY INTO statement that points at an external stage (have done this personally with S3 and Azure).
- Concurrently iterate over a child orchestration job that executes a Database Query component that retrieves data from a SQL Server
- Concurrently iterate over a child orchestration job that executes an API Query component that retrieves data from Matillion's API
- Concurrently iterate over a child orchestration job that executes a Xero Query component that retrieves data from various Xero endpoints
In all of these cases, I have not changed from the default load options, other than to sometimes change the "Recreate Target Table" option. So unfortunately I'm not sure why you are seeing these errors.
Is it possible that the network containing your Matillion VM is restricting network traffic in some way?
As an aside, you may also find this blog post interesting regarding how to clean up temporary Matillion stages in Snowflake, as this allows you to disable purging in the load options.
Cheers,
Chris
Hello Chris,
Thank you for the information, we are iterating over a child orchestration job, which is executing a database query component to a SQL server source, which I believe is your bullet 2 above. The problem is intermittent, likely in that some condition occurs during the run that initiates the failure. We use a system identity with an owner grant to the underlying storage account for azure, so network traffic is should not be restricted internally, and permissions are granted. I typically check regularly our azure and snowflake connection in our environment. I always get 'blob storage: success'. It is worth noting that our snowflake concurrent connections is currently set to 4 in the environment, which we are pushing back in testing today back to 1 to see if we can restore just basic functionality even in sequential mode. Any more suggestions you might have are appreciated.
Quick follow up on this issue, as we had a call with Matillion support last week. The issue was due to a couple of layers, the primary of which was that Matillion was removing stages that it was trying to use later. This was apparently fixed at some point in later releases from where we started, and now there is a stage maintenance window in the environment whereby you can add your own stage, dedicated to your staging events, and persistent. You can create this stage using internal or external storage locations. We created a single internal stage, named appropriately, and then pointed each our SQL database query components inside the iterator to use snowflake staging and the stage pointed to the persistent internal stage we just created. This eliminated the staging issue. Which unfortunately exposed what is likely the 'real' error which is column case mismatch issues in the insert from the stage to the snowflake table. While we set QUOTED_IDENTIFIERS_IGNORE_CASE:TRUE for all our sessions in the grid, when you enable concurrency on an iterator, and you set the number of concurrent sessions in your environment to more than one, new sessions are created that do not enforce this option. Thus some of our sessions failed, randomly, depending on whether we had a case mismatch for that statement, and whether that statement was picked up by an additional session, which did not set the QUOTED_IDENTIFIERS_IGNORE_CASE:TRUE option.
We are now trying to pursue a global way to set the connection option QUOTED_IDENTIFIERS_IGNORE_CASE:TRUE. The environment has advanced options for the JDBC driver, but these do not appear to be enforced when using a concurrent iterator. Will continue to pursue a fix for this through support, and post here once I have a working configuration. Hopefully this will help someone.
I have not experienced this error so far. Which matillion component are you using? The connection is getting closed at some point in the pipeline, before completing the read. A few things I would look at -
Is it always successful when you do the iteration sequentially? If it fails in sequential execution, it makes sense to find if this error is thrown from source to stage or stage to Snowflake table?
Are all executions in the iteration using the same azure storage? Is the file/data size a problem?
Hi @eric.coleman,
Thank you for your post. Glad to know that you got to the root cause of the issue. Matillion does session pooling for concurrent iterations and that can cause issues if we set something specific for the individual sessions. For QUOTED_IDENTIFIERS_IGNORE_CASE, we normally set it in the environment for the JDBC connection parameters which is globally applied for the environment. I have not personally faced any issues with this setting for concurrent executions. However, please note that if you are setting this in the already existing environment, it is possible that Matillion will not be able to read the already case sensitive tables and can raise errors.
I believe the error is thrown during the put command from file to stage. The error states it cannot find the stage, or the io stream is already closed, based on whether I am using a snowflake managed staging (internal stage) or azure storage and a configured external stage in snowflake. I believe I have tried every possible combination of storage options. The errors come late in the load, with some of the iterations completing without error. More recently we are getting the same types of failures using the iterator in sequential mode, although this is not as consistent. I am hoping we can get a live call with Matillion support today. They have the latest logs and our job config, but have yet to be able to suggest a possible solution beyond completing the upgrade to 1.62.
I failed to answer the component question. We are using a grid variable iterator over a database query component connected to a SQL server instance. We are using the same azure storage account each time, either through the stage we have created, or through the option of [custom] which creates the matillion-staging container.